Blog • My Online Training Hub

Power Query Unpivot Scenarios

July 20, 2017 by Mynda Treacy 9 Comments

In this post I’m going to show you how to use Power Query’s Unpivot tool to fix 3 common data layout problems: The Straightforward Unpivot Unpivot Multi-column Data Types Unpivot Nested Column Headers (the worst offender): And to flip things around, literally, we’ll use the Pivot tool to fix Repeating Rows, which is commonly referred […]

Symbols in Excel Slicers

July 11, 2017 by Mynda Treacy 8 Comments

Did you know you can display symbols in Excel Slicers? That’s right, Slicers aren’t just limited to listing boring old items from your PivotTables or Tables. In the animated image below I’ve got a Slicer that displays currency symbols. As you click on a currency symbol in the Slicer, the chart converts to the selected […]

Excel Chart Secondary Axis Alternatives

July 5, 2017 by Mynda Treacy 7 Comments

Sometimes a secondary axis is a necessary evil. After all, most of the time you can’t plot big numbers and little numbers in the same chart without the little numbers getting lost in the scale. For example, let’s say we want to plot our annual sales and profit margins in one chart. Here’s the data: […]

Excel Chart Highlighting

June 29, 2017 by Mynda Treacy 2 Comments

One of the cool features of Power BI is the ability to cross filter and highlight charts. With a simple click on a column or line in one chart we can instantly highlight the selected item in all other charts and tables in our report. In the animated image below you can see that when […]

List Files in a Folder Using FILES XL Macro (XLM)

June 21, 2017 by Philip Treacy 27 Comments

Way back, when Excel 4.0 was around, it used a macro language called XLM. VBA was introduced in Excel 5.0. These XLM macros are still in Excel and you can use them to do certain cool things. One of these macros is FILES. Now I call these macros, but when you use them you just […]

Set Excel PivotTable Default Layout

June 13, 2017 by Mynda Treacy 4 Comments

New in Excel 2016 for Office 365* users is the ability to set a default layout for your PivotTables. Finally! If you’re like many PivotTable users the first thing you do after inserting a PivotTable is waste a minute or so fixing the layout so it’s just how you want. Waste no more, because now […]

Script Lab – Explore the Office JavaScript API & Learn to Build Add-Ins

June 6, 2017 by Philip Treacy 6 Comments

Microsoft recently announced a project called Script Lab, previously known as ‘Add-In Playground’, which aims to help you learn how to write add-ins for Office. Script Lab is actually an add-in itself and runs in Excel, Word or PowerPoint and can be installed from the Office Store. Script Lab will run in Excel, Word, or […]

Excel Dates Displayed in Different Languages

June 1, 2017 by Mynda Treacy 6 Comments

If you share Excel files and reports with users from different countries then you might want to give them a report in their language…and I don’t just mean converting from English to American 😉 It’d be a load of work to create a separate file for each language, so instead we can automate it like […]

Power Query Updates

May 24, 2017 by Mynda Treacy Leave a Comment

The monthly update cycle for Excel’s Power Query sure keeps us on our toes. Power Query Updates for Excel 2010 and 2013 If you’re using Excel 2010, 2013 then you update Power Query by going to the Power Query download page and getting the latest copy and reinstalling it. No need to uninstall first, just […]

Excel Themes

May 17, 2017 by Mynda Treacy 8 Comments

Excel Themes are a bundle of default colors, font styles and Shape effects. There are lots of built in Excel Themes available from the Page Layout menu: Hovering over a theme will give you a preview of the effect on your workbook. You’ll notice that different fonts will have an impact on your workbook layout. […]