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 […]

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 […]

Excel User Voice

Excel User VoiceI bet there’s been the odd time you’ve said to yourself “wouldn’t it be nice if Excel had…” Or maybe even “I really hate how Excel….” Well, now you can have your ideas heard by the very people responsible for developing Excel. Excel User Voice is where you can make suggestions for new features you’d […]

Finding File Meta Data Using FileSystemObject

Finding File Metadata Using FileSystemObjectIn a previous post I looked at using the FileSystemObject to search through folders on your computer’s disk and create a hyperlinked list of files. Clicking on the hyperlink would open the file. FileSystemObject (FSO) can also be used to retrieve meta data from a file, like the creation date, last modified date or file […]

Calculating MEDIAN in PivotTables

PivotTable MedianUnfortunately there’s no built in way to calculate the median in a PivotTable. The approach is different depending on whether you’re using a regular PivotTable or a Power Pivot PivotTable. We’ll look at both options in this tutorial. The Data The data we’ll be using in this example is in an Excel Table called Table1: […]

Combine Excel Worksheets with Power Query

Power Query AppendA recurring task in my previous life as an accountant was to analyse actual vs budget figures. However the figures were often in different worksheets, different workbooks or even different systems. And the task of consolidating all the data into one table that was ready to analyse with PivotTables was not straight forward. Not anymore. […]

Excel Slicer Trick

Excel Slicer TrickThat’s a rubbish title, I know. I couldn’t think of a succinct way to describe what I’m about to show you. I’ve combined a few techniques to create this interactive PivotTable and PivotChart using Slicers: Probably the easiest way to understand how I’ve put this together is to download the file, which you can do […]

Excel AGGREGATE Function

Excel Aggregate functionThe AGGREGATE Function is the Swiss Army Knife of functions; it’s so versatile it can replace these 19 Excel functions in one fell swoop: But that’s not reason enough to use it, the function-killer reason to use AGGREGATE over any of the 19 standard functions it can replace is because it is far more powerful. […]

Save Settings in Registry Using Excel

Save Settings in registry Using ExcelIf you want to save data from one Excel session to another, you can store this in the Windows registry. Using the registry is good for saving things like user preferences or program configuration data. There are other means of saving this type of data like external files, hidden sheets and defined names, but I’m […]