Power Query Reformats Ugliest Report Ever

Ugly Excel ReportIn this tutorial we’re going to reformat Excel reports with Power Query. Why? Because external systems can create some pretty ugly reports. Here’s one from a popular Australian accounting system, MYOB: It’s not just ugly because of how it looks. It’s also ugly because the layout prevents you from (easily) doing any further analysis of […]

Power Query Version Compatibility and Installation

Excel Power QueryWhen you know what Power Query is capable of you’ll want to get your hands on it immediately. It’s available in 32-bit and 64-bit, but unfortunately it’s not available in all versions of Excel. I’ve put together a comparison table to help you identify Power Query version compatibility. Power Query Version Compatibility Table How to […]

Transpose Data in Excel

Transpose data in ExcelThere are 3 ways you can transpose data in Excel (not including VBA). Download the workbook and follow along Download the Excel Workbook. Note: this is a .xlsx file please ensure your browser doesn’t change the file extension on download. 1. Copy > Paste Special > Transpose The downside of Paste Special > Transpose It’s […]

Excel Multi-cell Array Formulas

Multi-cell array formulasExcel Multi-cell array formulas are a single formula which returns multiple values and is entered into multiple cells. Hence ‘multi’ in the name. Let’s look at an example, say we want to return a list of numbers 1 through 10 in cells A1:A10. Step 1 – very important; first select cells A1 to A10 Step […]

Excel ROWS and COLUMNS Functions

Excel ROWS and COLUMNS Functions      You’ve probably seen a formula like this before: =VLOOKUP($B16,$B$4:$D$13,COLUMNS($B4:C4),0) Or this: =INDEX($C$3:$H$8,ROWS($A$3:A4),COLUMNS($A$1:B1)) And you may have even emailed me to ask “what is the ROWS/COLUMNS function doing in this formula”. The short answer is it’s returning a number, or in multi-cell array formulas, a series of consecutive numbers. Why do you use […]

Use Excel Slicer Selection in Formulas

slicer selection in formulaSlicers are a great tool for incorporating interactivity into your reports but we don’t always want to analyse our data with PivotTables and PivotCharts. Nor is our data always in the perfect format for PivotTables. So, let’s look at how we can use the Excel Slicer Selection in formulas which will enable us to create […]

Creating Excel Charts from Multiple PivotTables

Manual Chart TableIn an ideal world our data will be in one table so we can easily analyse it in a PivotTable and PivotChart. However sometimes the data we want to display in a chart is split across multiple tables, and this is a PivotChart showstopper. Remember Pivot Charts are monogamous in that they only display data […]

Error Handling in VBA

Error Handling in VBAIf VBA can’t execute a statement (command) then a run-time error occurs. By default Excel deals with these, so when a run-time error occurs, you’ll see a default error message like this: But you can change this and instruct Excel to allow your code to deal with run-time errors. NOTE : I’m going to use […]

Excel Advent Calendar 2015

Excel Advent CalendarAround this time last Year I created an Advent Calendar in Excel: To Count Up or To Count Down? Now, for me an Advent Calendar had always counted down to Christmas day, but to my surprise some people counted up, or rather counted the dates. That is they read the numbers on the calendar as […]
Show Hide
Excel Dashboards Webinar Registration

Excel Pivot Cache

Excel Pivot CacheNote: this does not apply to Power Pivot PivotTables. When you create a PivotTable Excel takes a copy of the source data and stores it in the Pivot Cache. The Pivot Cache is held in Excel’s memory. You can’t see it but that’s the data the PivotTable references when you build your PivotTable, or change […]