If you make use of an accounting package like Syspro or Pastel where you include budget information, you may come across Excel exports that list each budget month and each actual month in their own columns as shown below.
This is not ideal for Excel and Pivot Tables in particular because we would prefer to have the data be normalised i.e. all the numbers in a single column so that you can slice and dice them at will.
This format is particularly difficult to fix with normal Excel. It involves lots of OFFSETS or else lots of manual copy and pastes. Excel’s free new PowerQuery tool handles it very quickly.
So below is what we want the above to turn into. Every line contains the income statement type, the month it applies to and the budget for the month and actual for the month.
However, we don’t want to take hours to do this and more importantly, as the export report looks the same each time, we want to just re run this process every month.
This is where PowerQuery comes in. Below the steps to turn the column by column export into a database but the real benefit is that once you have done it once, you can re run these steps again and again.
The steps below assume you have basic knowledge of PowerQuery. If you don’t you can look at our PowerQuery online course where you can get up to speed in a couple of hours.
Step 1- Get it into PowerQuery
Depending on your version of Excel you may use the PowerQuery ribbon, but below we click on DATA and then FROM Table/ Range and the PowerQuery window opens with your data in it
Unpivot all the value columns
Next, highlight all the columns with the budget and actual data in, and on the TRANSFORM ribbon choose UNPIVOT COLUMNS,
Almost immediately you will see that it looks more manageable. All the values are in a single column and we have an Attribute column which specifies budget or actual and the month.
This Attribute column however would be better if the dates were split from the type
Split Dates and type
To split this column, just highlight the column, go to the TRANSFORM ribbon, choose SPLIT COLUMN and choose By Delimiter as shown below
Depending on your data you may have different options, but for our data we will use the first space to split the column.
It will now look much better and you can in fact use this in a Pivot Table.
But perhaps you want to go one step further. Ideally the Budget and Actual values should be side by side so that, for example, we can look at Turnover for January and see the actual for the month next to the budget for the month.
Re Pivot The Column
To split the Type column to see them next to each other just highlight the column and on the TRANSFORM ribbon choose to Pivot Column. You will be asked which one is the values column and we have changed it below to the column called Value.
When you click OK you will see the data change into a format that you can work with.
The best thing is that this is re-usable. Next month when you export the updated report, just paste it into the same area and click refresh.
If any of this is relevant to what you do, you must get to know more about PowerQuery (also known as Get & Transform).
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.