Convert Regular PivotTables to GETPIVOTDATA Formulas – PART 3

This is Part 3 of a project where I attempt to create a macro to automatically convert a regular pivot table to formulas; similar to the way you can convert an OLAP pivot table to CUBE formulas. So far, I was able to create a macro that outputs Cell Referenced GETPIVOTDATA formulas for the target pivot table. My last objective is to see if I can bring the Page Fields along for the ride. That is to say, I would like to auto-create slicers to interactively alter the values returned by the GETPIVOTDATA formulas.

The screen capture below shows how Page Filters can be converted to Slicers that work with the auto-generated GETPIVOTDATA formulas.


You can try it for yourself a sample file.

Download Sample File

All the code for the macro is documented in the sample file, so I won’t bore you with the details. I will say that the tricky part of dynamically creating slicers is avoiding duplicates. Excel seems to freak out if you create a duplicate slicer. So each time the macro is run, we need to check for existing slicers. If existing slicers exists, we copy the slicer and point the copy to our pivot table. Otherwise we can create a new slicer with no issues.

By the way, I need to mention that I made heavy use of the excellent set of articles from Jan Karel Pieterse to get a head start on programming slicers. Check out Jan Karel’s site for all kinds of useful brain candy.

Well that’s it for this little side project of mine.

I would say that this macro has moderate usability. If you need a dashboard model fast, you can create a pivot table, run this macro, and voila! You’ve got yourself a set of formulas that can be moved, copied, and adjusted to create an interactive reporting model pretty quickly.

Convert Regular PivotTables to GETPIVOTDATA Formulas – PART 2

Last week, I embarked on a journey to see if I could build a macro to automatically convert a regular pivot table to formulas; similar to the way you can convert an OLAP pivot table to CUBE formulas. I was able to create a macro that outputs hardcoded GETPIVOTDATA formulas for the target pivot table.

In this post, I’ll show you a macro that improves the output to include cell references instead of hard-coded values. With this macro, you can point to a pivot table and fire the code to get a new sheet with a formula driven version of the pivot. Read more

Convert Regular PivotTables to GETPIVOTDATA Formulas – PART 1

One of the cool things about PowerPivot and other OLAP pivot tables is that you can convert the entire pivot table into a series of CUBE formulas. With just a single click, Excel will replace your PivotTable with formulas that call back to the Data Model. Converting your pivot tables into formulas basically gives you a pivot table that can be taken apart. You can do things like insert rows and columns, add your own calculations between data items, combine the data with other data on a dashboard, and you can modify the report in all sorts of ways by simply moving the formulas around. Read more

Excel 2016 Function Changes

Hi there! I know it’s been a while since my last post. I’ve been updating several books for the Office 2016 release. I thought I’d pop up and provide at least a glimmer of useful info. I’ll attempt to get back to regular blogging in the upcoming weeks.

Since I’m knee-deep in updating content on formulas, I’d like to share a few Function changes (as far as I can tell) introduced with Excel 2016.

New Excel Functions in 2016

As far as I can tell, only 5 new functions will be introduced in Excel 2016.

These forecasting functions are no doubt included as a necessary part of the new Forecast Sheet functionality.

You’ll find these functions in the Statistical formula category. Read more

Understanding Office 365 Plans

To my dismay, I’m constantly being asked which version of Office 365 is the best for Excel analysts. Like everyone else, I typically provide a semi-vague answer which was most certainly pulled from some website somewhere. Well, I recently stumbled on this nifty TechNet site that outlines the differences between the various versions of Office 365.


Unlike the generic comparisons we typically see, Read more

Too Black for Excel

You know what bothers me? As you can tell from the ambiguously racist title of this post…black font.

I know it sounds strange, but the default font in Excel is somehow too black. The contrast of the white background and the really dark black font color annoys me. In a time when we’ve moved away from the standard VB colors, why does Excel default to RGB(0, 0, 0)?


Lately, I’ve made it a point to tone down the default black color in my spreadsheets. Although the difference is subtle, I think my reports look better both on the screen and in print form.


For example, in the screenshot below, Read more

Using the Immediate Window to Get a List of File Names

Here’s a trick to get a quick list of files from a certain directory.

Open the Excel or Access Visual Basic Editor and then press Ctrl+G to see the Immediate Window.

Enter the following syntax and then press Enter. Be sure to change the directory (in red) to the path of your directory.

File = Dir(“C:\MyDirectory\“): Do Until File = “”: Debug.Print File: File = DIR(): Loop


Your reward will be a list of all the files in that directory. You can now copy this list and use it as you see fit. Read more

Ten Simple Tricks to Speed up Your Excel VBA Code

I’m currently on a project to improve the performance of a particular workbook.

Part of this task involves speeding up a few of the macros in the workbook.

While I’m thinking about this stuff, I’d like to share a few simple tips for speeding up your Excel VBA code.

Some of you will know all of these and more. So feel free to comment and share any other performance tips you may have.


Halt Sheet Calculations Read more

VBA to See Who Last Updated a Workbook

Have you always wanted to prove that your co-workers are morons intent on making you look bad? Well today, I’ll share a trick that will make all you paranoid schizophrenics very happy.


A friend of mine asked if there is a way to easily see who last updated a particular workbook.

Apparently, he works in an environment where multiple people are editing workbooks on a share drive.

Of course, he knows that the workbook properties will show who last saved changes, but he wants to see that info as soon has he opens the file.

My answer to him was to hijack the Excel Title Bar. The Title Bar usually only shows the name of the workbook. But with a bit of simple VBA, Read more

Excel Power BI Boot Camp in May

Hi Folks. I’ve been busy wrapping up the final touches to a book that will come out in May.

I don’t have time for a full post, but there’s always time for a crass commercial message.


I wanted to give you a heads up on our new BI training scheduled for May in Dallas Texas.

This 3-day event is aimed squarely at Excel analysts who find it increasingly necessary to become more efficient at working with the new Microsoft BI tools like Power Pivot and Power Query. 

Bill Jelen (MrExcel) and I will guide you through the mysterious world of Microsoft’s new BI Tools from a business analyst’s point of view, introducing you to the rich set of tools and reporting capabilities that can be leveraged to more effectively synthesize data into Business Intelligence Dashboards. Read more