Excel 2016

Home/Excel 2016

Free yourself from using formulas

  Power Query is the greatest addition to Excel in 10 years, it's amazing at extracting and transforming data ready for you to use in a report. In this two minute video we show you how easy it is to extract the data you need without using any formulas! Find out more Extract and clean [...]

How to pull exchange rates from a web page into Excel or Power BI

This 3 minute video shows how easy it is to link to web page data with Excel. In this scenario it's connecting to the Reserve Bank of Australia exchange rates web page. Get & Transform is built into Excel 2016. It was previously known as Power Query and is still a free download for Excel [...]

Excel and Power BI – From the Microsoft Studios

Excel and Power BI are amazing analysis and data visualisation tools. Knowing what's possible is just the beginning. You can see the possibilities in this video recently recorded at the Microsoft Studios in Seattle. The speed of change and capabilities of Excel and Power BI are dizzying. The benefits of learning these technologies will save [...]

Excel or Power BI – where to start?

Excel or Power BI - where to start? So... Excel has Power Query and Power Pivot built in. Power BI uses the same technology.  So which one should you learn first? I say which should you learn "first" because if you learn one it's a relatively small step to learn the other 2 Questions: 1. What's your aim? [...]

3 Easy Steps to Manage your Data Fields

3 Easy Steps to Manage your Data Fields Want to control which data fields to keep in Power Query when removing other columns? When using the ‘Remove Other Columns’ transformation in Power Query (‘Get & Transform’ in Excel 2016+) the query editor hard-codes the remaining column names in the Advanced Editor. This is fine if your [...]

Excel’s 3 Best Kept Secrets

What are Excel's 3 Best Kept Secrets? Excel has seen great advancement in the last 10 years and every user who spends their days re-organising data can benefit hugely from Excel's 3 best kept secrets. 1. Power Query If you or members of your team use Excel a lot then you are missing out massively [...]

Dynamic Data Validation with Tables in Excel

Dynamic Data Validation with Tables in Excel Why is Excel returning an error message? Why doesn’t my formula work? What did I do wrong? The answer to these common questions may surprise you. Many spreadsheets I’ve come across have a common problem – inconsistent data entry. If someone enters “United States of America” [...]

Get & Transform to the rescue!

Get & Transform to the rescue! Is it a bird? Is it a plane? No it's way better than that, it's Excel! Get & Transform, also known as Power Query, gives Excel users super powers. Here's the latest scenario where it has helped out and also is an opportunity for me to demonstrate a method [...]

Training 100 Chevron staff in modern Excel and Power BI

Training 100 Chevron staff in modern Excel and Power BI I am halfway through delivering training in Power Query, Power Pivot and Power BI to 100 Chevron staff. I love sharing my knowledge on this topic and seeing how people suddenly see what's possible with Modern Excel. You can see attendees' eyes look up and [...]

Introducing Power Pivot – Power Query (Get & Transform) – Video Introduction Part 3

Introducing Power Pivot  In this video I introduce Power Pivot and how you can use it to join multiple tables of data together and display your data in a Fiscal Year format. In part 1 I demonstrated how Power Query can quickly consolidate all of the files in a folder and bring that data [...]

Power Query (Get & Transform) – Video Introduction Part 2

Power Query (Get & Transform) - Part 2 In part 1 I demonstrated how Power Query can quickly consolidate all of the files in a folder and bring that data into Excel. In this 6 minute video I follow on from that introduction and add an extra transformation step to get the dates I need. Once [...]

Power Query (Get & Transform) – Video Introduction Part 1

Power Query (Get & Transform) Part 1 Power Query is amazing at linking to data sources and cleansing / transforming that data as it's loaded into Excel (or Power BI Desktop). Watch this 4 minute video and see how you can consolidate all of the files from a folder instantly using Power Query. Build the transformation steps [...]