Convert Columns of Budgets and Actuals into database

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.

Convert budget and actuals columns into database

The problem with converting columns of Budgets and Actuals

This is not ideal for Excel and Pivot Tables in particular because we would prefer to have the data be normalized 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.

Convert budget and actuals columns into database

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.

Convert budget and actuals columns into database

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

Convert budget and actuals columns into database

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.

Convert budget and actuals columns into database

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.

Convert budget and actuals columns into database

When you click OK you will see the data change into a format that you can work with.

Convert budget and actuals columns into database

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.

Combine Actuals, Forecasts and Budgets into an automated report pack

Budgeting and Forecasting with Excel

Variable moving average formula in Excel