Archive for Excel

Excel Keeps Crashing? Check your VBA code

I use Excel 2013 64 bit and sometimes Excel just keeps crashing on me.  It used to happen to me a lot more in the past, but after I discovered what was causing the repeat behaviour I have been able to greatly reduce how often this occurs.

First a description of the problem

excel has stopped working2If this happens to you then you will already be aware of this scenario.  You are inside your workbook doing something important (it always happens when you are doing something important), and then the screen background greys out, and you get a message saying Microsoft Excel has stopped working.  It then “pretends” to be looking for a solution prior to telling you that it can’t fix it and you have to close the program.

And if your experience is anything like mine, once this starts to happen with a workbook, it keeps on happening over and over with seemingly no way out.

So what causes this repeat behaviour?

Well there are 2 main causes for this problem.

Read More

Power Pivot Calendar Tables

Level: Beginners

Technically you don’t need a calendar table to use Power Pivot, however in all but the most basic use cases you really should use one.  Reasons to use a Power Pivot calendar table include:

  • A calendar table allows you to use the filtering power of Power Pivot to make your reports fast and snappy.
  • It allows you to filter your reports on attributes such as Year, Month, Quarter and any other aggregation of time you like.
  • It allows you to create your own customer view of time that may be different to a standard calendar (such as a 4/4/5 calendar) and/or create your own definition of what is a financial year.

And indeed in some situations you simply must have a calendar table – specifically if you want to use the inbuilt time intelligence functions in Power Pivot (like year to date measures, same period prior year etc). Now there are other ways to create formulae that calculate time intelligence results other than the inbuilt functions, but generally the DAX is a bit more complex.  More on that later.

Rules for a Calendar Table

Here are the mandatory rules of a date table if you want to use time intelligence functions.  You don’t have to follow these rules if you don’t need/want to use the in built time intelligence functions, but it is still good practice particularly if your data is at a “day level” of granularity.

  • You must have a date column in your calendar table
  • The dates in this column must be in a contiguous range that covers the entire period of your data:
    • no missing dates.  It doesn’t matter if you don’t work weekends, you MUST include ALL DATES in the calendar table including weekends.
    • no duplicate dates
  • The calendar table must be marked as “date table” specifying the date column when asked in the dialogue box if you are using a non-date column. (This step is not required in Power BI as you can’t use a non-date key, only Excel).image
  • The date calendar reportedly needs to run through to the end of the last year used in the data model (although in my experience this makes no difference).

Note: It is not mandatory for your calendar table to be at the “day” level of granularity  it could be week, month, hour, minute or what every else you want  but you can’t use the inbuilt time intelligence functions unless it is at the “day” level.

Additional Features of a Good Calendar Table

Read More

How to Copy and Load VBA for Excel

I love VBA for Excel. VBA can automate tasks that you use often, making the process faster, easier and more accurate to execute. I like writing VBA for myself to solve my own problems, I like sharing what I write with others, and I like taking the VBA that others write and use it myself.

Now that I am teaching a lot of students how to use Power Pivot, I am coming across a lot of burgeoning Power Pivot users that could benefit from VBA but have exactly zero experience in how to copy and load VBA code that is made available by others for you to use. As Chip Pearson pointed out to me, you have a responsibility to ensure you have the rights to use the code. The purpose of this post therefore is to provide step by step instructions on how to take someone else’s VBA code (that you have permission to use) and get it working on your PC – zero VBA experience assumed.

The steps I will cover in this post are:

  1. Turn on the Developer Menu
  2. Set up your personal.xlsb workbook so that you have somewhere to save your VBA code
  3. Copy your first VBA code into your personal workbook
  4. Add the code to the quick launch menu

I am writing this post using Excel 2013 as the tool, but the process is essentially the same for other versions too (although the user interface may be slightly different).

Turn on the Developer Menu

Read More

Start a Second Instance of Excel

Excel is a bit like a drug for me – the more I use Excel, the more I need to use Excel.  I work on a lot of Power Pivot workbooks these days and sometimes these workbooks need to run a refresh or recalc that can take a bit of time.  Increasingly I am finding that I am just sitting there waiting for 1-2 mins but instead I would like to be doing something else in Excel, even if it is just updating my time sheet or expenses etc.

I found this solution I am posting about here while I was working on my Power Query Documentation Excel Workbook (read about that here). Power Query is a “modal” window, and hence you can’t do anything else in Excel while a Power Query Workbook is open.  I needed a solution so I could do 2 things in Excel at the same time.

The solution to both of these problems is to simply start a second instance of Excel in parallel.  There are different ways to do this in each version of Windows.  If you are running Windows 8 like I am, then this is what you need to do.

The Adhoc Solution

  1. Click the Windows button on the Task Bar.
  2. Type “run” and hit enter
  3. In the run dialog box, type “excel /x” and press enter


This will start a second instance of Excel on your PC.  If you have a personal.xlsb workbook for your VBA code, you will get the following message.


You can click any of the buttons to dismiss this window and they all do slightly different things.  For the purpose of running a second instance, any of these options will work fine.

Create a Shortcut

Read More

Excel is the Swiss Army Knife of Business Intelligence

I was recently asked by an Industry Excel expert what made Excel such a compelling product for Business Intelligence, Reporting and Analysis.  As many people know, I have worked in IT as well as commercial business roles over the years, so I have seen ‘both sides’ of the story and hence I am qualified to understand the things that can go wrong between these departments.

Big complex software projects (like an SAP implementation) necessarily require specialist IT staff to work with commercial business staff to develop people, process and technology solutions that will solve business problems.  These projects are highly complex, expensive, time consuming and risky by their very nature.  It is not unusual for such a project to last 3-5 years and cost 10s of millions of dollars – more in very large organisations.  Traditional BI projects are a bit like this.  They are complex, expensive, and often fail to deliver on the user expectations.

Why Big BI Projects are so hard

When business people work with specialist IT people, there is an enormous communication overhead between them. I have seen plenty of great people in IT and in commercial roles, and they almost always want the same thing – a great outcome for the business.  But the problem is that people are different; especially IT and commercial people are often very different.   There is a very good reason why some people are successful working in Sales and other people are successful working as a programmer.  The skill set between these people are fundamentally different.  So it is inevitable that when these people work together on a project, there will be misunderstandings, mistakes and rework.  All of this comes at a cost of time and money, and in the fast paced business world some things are just not viable if they have to go through this cycle.  Unfortunately in traditional BI projects, there is no choice but for IT people to work for commercial people, because the technical side of the projects are so complex that there is no other way.

Often it is not worth the cost

Business people will always have things that are a priority to them but are subordinate to the greater needs of the business/others/competing departments etc. So when a business person says  “I need ABC report to analyse this business opportunity by no later than XYZ”, sometimes/often the timeline simply can’t be met or the cost of doing so is prohibitive.

In a world of competing business priorities, successful business people find ways to get the important things done – they don’t sit around discussing why things could not be done due to lack of resources, competing priorities etc.

Enter Excel – the Swiss Army Knife of Business

I think about Excel as being the Swiss Army Knife of business.   Excel is one of those universal tools that allows any business person to get on with important priorities without having to call in IT to do the work.  In fact in my experience IT people know very little about Excel – the skill is all with the business users.

modern excel sml

Excel can be learnt and used by almost anyone for any purpose, and often the cost/benefit ratio is completely unsurpassed by any alternative (eg long slow expensive IT lead BI project) to the point where it is not worth taking your Excel built solution to the next level. In a world of rapidly changing business challenges, it is the speed to value that counts.  If you can’t turn around the analysis quickly and affordably, then the opportunity will most likely simply pass you by.

And then there is Modern Excel

Rob Collie uses the term “Modern Excel” to describe the new features that Excel has delivered over the last 5 or so years.  These new capabilities include Power Pivot, Power Query, but also the evolution of Pivot Tables, Slicers and into the future will include the still developing tools like Power Map, Power View etc. There is no other BI tool that can deliver a speed to value ratio that Modern Excel can deliver.

If you want to take advantage of Modern Excel, you will definitely need to learn some new skills such as how to write DAX, or you may have to bring in some people for a short period of time to break the back of the work to get you going, but these are small one off investments that have immediate payback in a rapidly changing business environment. Once you have these new skills dialled up, you will deliver speed to value like never before.

Create Incremental Backups when using Power Query

Edit 16 May 2015: I now know the exact cause and cure for this problem.  See this post here This backup tip is still useful though.
This is a follow up to this post where I talked about Power Query and warned to take frequent backups when editing your Power Query Workbooks.  The issue is that Power Query is a bit flaky, and hence it is well advised to create incremental workbook backups with different file names in case bad things happen.

Anthony Newell kindly posted an idea (and some code) that he used to automate the process of taking incremental backups.  I took his code as inspiration, and then made quite some changes to meet my needs.  I am sharing this new code here for those that want to use it.  It is equally as useful for any workbook that you have invested time in, and you can’t afford to lose.  It doesn’t have to be a Power Query workbook.

What the Code Does

  • First it checks if there is a “version sheet” in the active workbook.  If not, it creates this version sheet.  The version sheet contains the current version number and the location of your backups.  You can manually change the default backup location in this sheet (but it always saves this as a sub folder of the original file location).
  • It then saves the active workbook with its current name in the current active folder (no version number added yet).  I have done it this way because I don’t want my active workbook file name changing all the time.  What I want is a backup that I can recover later if needed.
  • It then saves a copy of the active workbook in the archive folder with the current version number appended on the end.
  • It then closes the version with the version number added and reopens the original file so that you are working on the original file without the version number.
  • Finally it increments the version number on the version page.

Read More

Fill Table with Last Survey Result

I have come across this problem a few times recently. There was no immediately obvious solution that worked at first. I knew what I wanted to do but I couldn’t get it to work – but now I have a solution and I want to share it for the benefit of others.

First, let me explain what I mean by “Last Survey Result”

The scenario I am talking about here is one where there is an event that occurs infrequently in time – say once every couple of months, and you want to refer to the value of the last event in your DAX formulae until there is a new event to replace it. So one example is an “Audit” or “Survey” which was a case with Satoshi (participant at my recent Brisbane training course) or it could also be the “last handicap the golfer had” which was the problem that Michael had (participant at my last Sydney Power Pivot course). In both of these scenarios, we want to be able to refer to the “last value” of the event (survey or golf handicap) and keep referring to that value from the last recorded event until that value is replaced with a new value at some point in the future. So time marches on, but I still want to see that last value in my pivot tables for the current date. This is also referred to as “events in progress” by The Italians.

Why is this such a hard problem?

Before I answer this, let’s talk about a scenario that IS NOT a problem. Let’s take a simple Adventure Works data model. In this case you have a number of lookup tables sitting up top, and each lookup table is connected to the data table below (in this case the data table is sales)

aw dm

Read More

Power Query Performance Improvements – Test Drive

Microsoft has just released the March ‘15 update to Power Query.  On the official blog, MS called out 3 things:

  1. Performance improvements
  2. Microsoft Dynamics CRM Online connector
  3. New transformations

Performance improvements?  You have my attention.  I have found that Power Query (and Power Pivot for that matter) are both slow to load Excel files.  The comparison in speed between a CSV and XLSX of the same data is significant (XLSX is much slower). It seems to me that Power Query wants to “read” the whole file before it starts importing, but with CSV is just starts at the top and gets on with it – well that’s the way I rationalise it anyway.   So I decided to test out the claimed performance improvements in the new release and post the results here.

My Test Data Consolidates 44 Separate Excel Workbooks

I wrote a Power Pivot dashboard and reporting tool for a customer that allows them to analyse key business metrics using time series trend reports etc.  The source data for this report is a standardised stand alone weekly financial report that is produced in Excel.  So in any 1 week, you get a snapshot of what happened that week – which is great – but it doesn’t help you if you want to compare weeks or trends over time. Read More

I’m Speaking at the PASS BA Conference

PASS_BAC15_Speaking_250x250I’m really excited to be speaking at this year’s PASS Business Analyst Conference in San Jose California.  I will be sharing what I have learnt in my time working as BI Director at The Coca-Cola Company about how to share Power Pivot (and other Excel) Workbooks with people that simply want to consume the data.

Part of the problem of many workbook sharing tools is (in my view) the default settings are far too focused on giving a “Full Excel” experience instead of providing a “Web Browsing” experience.  People that want to consume the labours of our Power Pivot Workbooks don’t care about Power Pivot or Excel, they care about the numbers and the insights.

The techniques I have learnt and will share at the conference will show how any Excel user with access to SharePoint Enterprise (or a cloud hosted equivalent) can deliver a positive web browsing experience while still having the full Power Pivot interactive capabilities.  When you configure your shared workbooks for the consumer of your data rather than an Excel User, they can simply focus on the numbers and insights and get on with business.

Why you should attend this year’s conference

Read More

Use Power Query to Create Power Query Documentation

I wrote a blog post on last week on how to create a standard Power Pivot calendar using Power Query – from scratch.  I was doing some research this morning on how I could extend this concept to create a 4/4/5 Calendar (which is often used in the Retail Industry).  I was looking at this on-line reference documentation to find a function for a particular problem, and it was really hard to find what I was looking for.  There is no index at the top of the page, just a very long page of sections and formulae.

Then I had a brilliant thought – I wonder if I could use Power Query to download the Power Query documentation?  I tried it out, and the answer is a resounding yes.  This is what I did.

I copied the link to the web page.  This is the one I was using

I then opened a blank Excel workbook and went to the Power Query tab and inserted the above link in the box.


The Power Query Navigator returned a long list of formula categories, so I knew I was onto something.

Read More

How to Correctly Install Power Pivot for Excel 2010

As most subscribers of my blog would already know, I am a professional trainer of Power Pivot here in Australia. I do a lot of work for Corporations at their own premises with their own PC equipment. Not every company has made the move to Excel 2013, and in fact I sometimes find companies have to move from Excel 2007 to 2010 just to prepare for this training. There are a couple of traps when installing the Power Pivot for Excel 2010 Plug-in (and the Power Query Plug-in for that matter) and hence I am blogging these instructions to help people and companies get it right the first time. The main trap is getting the wrong version. But first a comment about 32 bit vs 64 bit.

32 bit vs 64 bit Power Pivot Versions

64 bit computers have been around for many years. The 32 bit architecture is quite old now and 64 bit is a lot more powerful. There are actually 4 technical layers that can be configured with either 32 bit or 64 bit, but each layer depends on the previous layer.

Let me explain. Starting at the bottom and working up, if you ever install 32 bit, then you can’t install 64 bit at a higher layer. But the reverse is not true. You can normally install 32 bit on top of a 64 bit layer (except for – the plug-in itself).

4 layers

Let’s discuss each layer

Read More

Power Query Issues with Power Pivot

Edit 16 May 2015: I now know the exact cause and cure for this problem.  See this post here

PhilC at asked me for advice on the best way to use Power Query with Power Pivot.  If you have come to this post via Google, it is probably because you have had issues and are looking for answers.  If you are reading this post for any other reason, then hopefully it will save you some pain by following my advice on how to minimise these Power Query issues with Power Pivot.

Quickly – what is Power Query

Power Query is the best thing to happen to Excel since Power Pivot!  Basically it is a lite extract, transform and load (ETL) tool for Excel users.  It allows us Excel users to grab data from almost anywhere, shape it into a form that can be used in Excel (increasingly Power Pivot), load it into your workbook and then away we go.

So what’s the problem?  Let me explain

Here is the scenario.  You grab some data using Power Query and then load it into Power Pivot.  You start building out your measures and adding value to your Power Pivot data model.  After investing many hours, you realise that you really need 1 more column of data from your Power Query table.  You go back to Power Query, add the new column of data, save the workbook and “BAM” you get the following error message.

We couldn’t refresh the table YourTableName from connection YourPQConnection. Here’s the error message we got: OLE DB or ODBC error: The query YourPowerQuery or one of its inputs was modified in Power Query after this connection was added.


When you first see this message, you can feel your stomach sink through the floor.  You have put hours into your data model and how you have to “start again”.

How to fix this problem after it happens

Read More

VBA to Insert a Power Pivot Table

When Power Pivot was originally released by Microsoft, it was introduced as a Plug-In to Excel 2010.  After the second version of the Plug-In was built and released, the Microsoft Build team moved its focus to writing the product into the 2013 release of MS Office.  Unfortunately they ran out of time to write in all the features into the Office 2013 version – consequently there are a number of features that are actually better in Excel 2010 plug in version than the Office 2013 version.

In 2010, it is easier to edit your measures

right clickIn Excel 2010 you can right click on a measure in the Field List Window and select “EDIT”.  In 2013 you have to open the Manage Calculated Fields dialog and then find the measure you want to edit.

And 2010 is easier to insert a Power Pivot Table

missing button In Excel 2010 there is an “insert pivot table” button on the Power Pivot tab in Excel.  Just click on this button to create a new Pivot Table that is connected to the data model.  No such button exists in Excel 2013 (unless you go into the Power Pivot window.

The alternative to navigating to the Power Pivot window is you have to use 8 clicks to insert a table (Shown below). Read More

Power Pivot Forum now supports DAX Formatter

Well Power Pivot Forum has been live for 8 months now and has over 100 users, more than 400 posts and many thousands of page views. The Power Pivot forum continues to grow as new users are finding the need for help with their DAX problem.

Recently one of the forum members (xChillout) suggested that I take advantage of the API written for DAX to improve the experience. Basically this API allows you to send a DAX Formula over the Internet direct to DAX Formatter. DAX Formatter then runs its magic over the code, validates it is correct (or incorrect), and then sends a nicely formatted version of the DAX code back to the sender.

I have now integrated this into the forum so that anyone reading DAX Code posted by others will find it easier to read and understand.

So now at Power Pivot Forum you can type in some DAX code like this

DAX Wrappers

And DAX Formatter will send it back like this.

Read More

What is Power Pivot and Why You Should Care

I have been meaning to write this post for a long time – today is the day!  The answer to the question “What is Power Pivot?“.  You may also have landed here by searching for “Use this workbook’s Data Model”.  Often the first time people discover Power Pivot is when they see this message in Excel 2016.


Let me start with what Power Pivot is not

I have heard lots of people describe Power Pivot as “Pivot Tables on steroids”.  This is not a helpful description because it suggests that Pivot Tables are somehow changed in the process – this is not true – there is no change to how a Pivot Table works.  If you want to find out what Power Pivot really is, then read on.  But first let me clarify what a Pivot Table is.

What is a Pivot Table

A Pivot Table is a visualisation tool that can aggregate data from a data source (historically a single Excel data table) and display that data in a way that helps the reader make sense of the data.  Let’s look at an example

Source Data Table

Here are a few rows of a simple Excel Table (ie a data source).  Note there are more rows in this table that are not shown here to save space.  This is my data source.


The trouble with source data like this is it is hard to find insights.  Eg which country has the highest sales this year?  It is hard to read typically because there is so much data and it is at a level of detail that makes it hard to consume.  So we need a way to aggregate, summarise and display the data so we can make sense of it.

Pivot Table

Here is a Pivot Table that uses the above source data.  The Pivot Table is used to summarise and present the data to the user in a way that makes sense.


Further, you can use inbuilt Excel visualisation tools to make the data even easier to read, as can be seen below.

pivot visual

In fact Microsoft Excel is the world’s most commonly used tool for visualising data.  It has had years of development and is a very mature, flexible and stable product that EVERYONE knows how to use.   Excel is a GREAT TOOL for visualisation.

So what’s the problem?

Read More

2015 Australian Training Dates – Power Pivot for Excel

I have now locked all the training dates for my Power Pivot for Excel Australian Training public classes.  You can find full details of the dates here.

live training Australia

Why you should attend my Power Pivot for Excel Training

  • I am affiliated with Rob Collie from in the USA and I deliver training material developed by Rob that is targeted specifically with the Excel user in mind.  Rob was the Program Manager at Microsoft that worked on the first version of Power Pivot.  His unique insights on how the tool was designed to work makes this training course more targeted than most.
  • I have a background working for Coca-Cola and have years of experience both using data and building data tools.  I know what users think they need, and I know how to structure data to give them exactly what they really need.

Why you should register now

  • There are discounts for early bird registration as well as for multiple registrations from one company at a single session.
  • Everyone is busy working on what is important as well as what is urgent.  Learning a new skill to use a tool like Power Pivot will make you more efficient (save you time) and more effective (get better business results).  It is not urgent, but it sure is important.

Block some time in your calendar now so that you make sure you get the benefits from this training and can start to Excelerate your business results.

For more details

A full course overview is available here.  You can then click through for your location, find a date that works for you and click through to register.

Should I delete or re-import a column in PowerPivot?

One thing I learnt in my early days of PowerPivot was that you should never delete an imported column from a table in PowerPivot.  The rationale was that PowerPivot compresses the columns during import, so if you delete them after import, you will lose some of the compression benefits. However I am a sceptical type and I need proof before I believe such things.  You see when I delete a column from PowerPivot, something seems to happen in the background.  I wondered if this was PowerPivot running the compression process again in the background to re-optimise the data.

Enough said – time for a test.  I set about testing this theory in both Excel 2013 and Excel 2010.

Methodology for the test

Read More

Problems Importing Access and Excel 2010 into SQL Server 2012?

Edit 10 Dec 2015: This blog post was written to solve a problem with SQL Server import drivers, however the process of addressing the issue should also work (in reverse) for people that are running 32 bit Office and want to install 64 bit Power BI Desktop.  If this is you, read on and you will see the process to fix the problem.

Since becoming a PowerPivot Professional, I have broadened my skill base to include the use of SQL Server.  I very quickly found that MS Access has a 2GB file size limit and hence I installed SQL Server on my PC.  It was a bit scary to start with, but once you get it installed and get started it is actually a fabulous way to go.  I would never go back now.  Anyway, I have had A LOT of issues with file compatibility when importing Access and Excel 2010 files into SQL Server.  It took me months to find a solution, mainly because I actually didn’t understand what the problem was – until now.

First an explanation of the problem – Office 64bit

Any serious PowerPivot user will already know that you really need Office 64 bit version.  PowerPivot will work with 500k – 1m rows of data in the 32 bit version (more or less, depending on your data) but if you want to get serious (ie many millions of rows of data), then you need to move to 64 bit.  The reality is that Excel with PowerPivot is THE ONLY Office program that needs 64 bit.  Now the problem is that there are lots of plugins and software that are compiled to work only with the 32 bit version.  That is why IT departments hate 64 Bit Office so much.

So the problem is as follows:

Read More

How to Bulk Refresh PowerPivot Workbooks on your PC

There are a number of different ways that you can refresh PowerPivot workbooks automatically on a schedule. The main ones I know of are:

  1. Using Power BI
  2. Using SharePoint Auto Refresh
  3. Using some sort of custom built desktop automation tool

The problem with Power BI and SharePoint

The problem with Power BI is that you need to have a subscription for every user that wants to consume the reports – and it is not cheap. I cover the downsides of Power BI in this post about how to access PowerPivot on iPad. SharePoint is a good solution if you have SharePoint Enterprise and you want to share your files in SharePoint. Many people don’t have access to SharePoint and/or need to refresh files for distribution in other ways. So that brings me to option 3 – desktop automation.

How to bulk refresh powerpivot workbooks on your PC

I have been a closet VBA coder for many years. In fact I first taught myself “really bad coding” using Lotus 123 Macros. Over the years I have accumulated code that I reuse for new purposes. At some stage about 10 years ago, I created a VBA workbook that will list the contents of a folder into a workbook. As I was thinking about the need to refresh PowerPivot Workbooks on a PC, I realised that I could reuse this code as the core of the project. Now I don’t profess this to be the best code, or even good code for that matter. But I can tell you this has been working successfully for me for some time.

How the refresh process works

Read More

How to access PowerPivot on iPad


PowerPivot on iPad – great experience!

One thing I find when working with my customers and meeting PowerPivot users is that a lot of people want to know how to access PowerPivot when they are mobile.  Most frequently people want to access PowerPivot reports on iPad, however in many cases just presenting data for PC users in an ‘easy to consume’ and ‘easy to control’ manner is also important.  Well there are 3 ways that you can set up to allow iPad (and PC users for that matter) to access PowerPivot reports from a web browser, and it doesn’t have to be difficult or expensive if you do it the right way.  The three options are:

  1. Microsoft Power BI in the Cloud
  2. An On Premise instance of SharePoint Enterprise Edition
  3. A Cloud based SharePoint Enterprise Service purchased as Software as a Service (SaaS).

I will cover each of these in turn leaving what I consider to be the best until last.


First of all, a definition of the term Power BI

Read More