TIMING

COURSE FEE

£110+VAT. (click here for information on the Flexiticket discount scheme - fees as low as £61.67+VAT per place). The courses ‘Why Power Query is the most important Excel development ever’, 'Good Spreadsheet practice', ‘50 Advanced Techniques for Creating Inspirational Sreadsheets‘ and 'More Power to your Pivot', if booked individually are available to IT Faculty members  for £60+VAT each.

COURSE CODE

OC06B0

SPEAKER

Simon Hurst

OBJECTIVES

To enable attendees to use the Excel Power Query tools to make their use of Excel more reliable, more efficient and more automatic.

Please note this is not a ‘hands-on’ course, but will provide practical advice, demonstrated through examples and with comprehensive documentation. The Examples will be based on the latest version of Excel but where an earlier version (2010 onwards) differs substantially this will be highlighted on the course.

 Our online Excel courses are based on the use of Excel for Windows. Although many of the techniques addressed are applicable to all editions, in particular Excel for Mac, might lack some, or all, of the features covered.

WHO SHOULD ATTEND?

Anyone whose use of Excel goes beyond the basics and ventures into the use of lookups and conditional sums. Anyone who works with external data or data stored in Excel worksheets and hasn't yet explored the Power Query tools in detail. Even if you don't work with external data, this course should help you to create spreadsheets that are more reliable and more automatic.

This course is suitable for the levels “General User to Developer” under the ICAEW IT Faculty Spreadsheet Competency Framework. For more information visit www.icaew.com/en/technical/information-technology/excel/spreadsheet-competency-framework.

Our online Excel courses are based on the se of Excel for Windows. Althought many of the techniques addressed are applicable to all editions of Excel, some editions, in particular Excel for Mac, might lack some, or all, of the features covered.

TOPICS

1. An introduction to the Power Query/Get & Transform tools
2. Power Query techniques that make Excel more dynamic and automatic
3. VLOOKUP() RIP – why Power Query replaces all Excel lookup functions
4. Think in more dimensions – escape the cell straitjacket
5. Use Power Query to solve the insoluble
6. Using Power Query to replace PivotTables
7. Troubleshooting Power Query problems
8. Case study – one-click management accounts: complete automation with Power Query

VENUE

This course will be available recorded online. You will be emailed the link to join two days before the course date.

DISTRICT

Southern