ICAEW.com works better with JavaScript enabled.
Exclusive

Using Data Types in Excel for easy-to-create management accounts

Author: Simon Hurst

Published: 17 Nov 2020

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

Simon Hurst explores the benefits of using Excel Data Types for easy-to-create management accounts.

The potential of Data Types and where to find them in Excel

When I first read about the ability to create Excel Data Types using Power Query, I probably over-estimated its potential. Having thought about it a bit more and experimented with creating Data Types, I have downgraded my initial assessment from earth-shattering to quite interesting. However, it's certainly still worth investigating. Note that this feature is currently available to Office Insiders as a Preview so, depending on the upgrade channel you have chosen, it might not yet be available in your version of Excel.

For those who have not yet come across Data Types in Excel, these are a relatively recent addition to the Data Ribbon tab. The new Data Type group includes two built-in Data Types: Stocks and Geography. The basic idea is that you can designate the contents of a cell, or perhaps more usefully an entire Excel Table column, as being one of the available Data Types. Then, for the built-in Data Types, Excel will check the cell entry or entries and, if it can identify them as being a valid item within that Data Type, will allow for further values or columns of values to be filled in by looking the values up online:

First image

Several Excel Community Archive articles cover Data Types in more detail but, at the time of writing, it is not possible to search for any of them.

The new Data Type preview feature allows a Power Query query to be used as the source of Data Type values. The creation of such a Data Type is straightforward. Within the Power Query editor, you select multiple columns and use the Transform Ribbon tab, Structured Column group, Create Data Type option. This collapses the selected columns into a single column. Once the query with the collapsed column is loaded to a worksheet table, the column behaves in a similar way to one of the built-in Data Types allowing the user to choose which of the collapsed columns to display:

Data types 2
The above example is based on extracting columns from an Excel worksheet and then reorganising them so that they can be used as part of the Data Type. Here is the original worksheet:
data types 3

Given that the process of creating our Data Type is so straightforward, we'll also cover a couple of other useful Power Query techniques. First of all, we need to click on any cell in our data and choose the Data Ribbon tab, Get & Transform Data group, From Table/Range option. This will convert our data into an Excel Table and ask whether our Table has headers. We will leave this option set to no.

Here is our data in the query editor. Our first task is to sort out the headers which are not currently in an ideal format. We want to do this in a way that makes the process as automatic as possible, rather than just treating the first row as a header row and changing the column names manually. Accordingly, we will use the Transform Ribbon tab, Table group, Transpose option:

transpose
This is our query with the rows and columns transposed:
Fill down
With our query transposed, we can right-click in our first column heading and use Fill, Down to copy the Actual and Budget headings to the rows beneath them that contain null. We can then use the Home Ribbon tab, Transform group, Use First Row as Headers option to promote our first row to become the table headers:
use first row as header
We want to combine Column1 and Account to form the column headings for our Excel Data Type so we select the two columns, right-click in either column header and choose Merge Columns. We have set the Separator to Custom and entered the dash as our custom separator. We leave the name of the new column at the default of Merged, because one of our columns is already called Account. We can then rename our Merged column heading to Account after the Merge step is complete:
merge columns
In order to allow our Excel Data Type to access our budget and actual columns, we will use the dropdown of the Use First Row as Headers option to select the antithetical Use Headers as First Row. We can then use Transpose, before resetting our headers using Use First Row as Headers:
use first row as headers
Next, we can change our column data types to Text for our first two columns and Whole Number for all the others. We can change individual column data types by clicking on the data type icon at the left-hand side of the column header. To change the data types of multiple columns, select all the columns required using Control+Click or Shift+Click, then right-click in any of the selected column headings and choose Change Type:
change type
Lastly, we select all of our columns and use the Transform Ribbon tab, Structured Column group, Create Data Type command to collapse all of our value columns into a single Account column:
data type
This collapses our individual columns into a single, structured, Data Type column and we can then use Close & Load to load this single-column Table into an Excel worksheet:
close and load
When we load a query with a Data Type column to our worksheet, Excel recognises the data column and displays the Data Type symbol. We can then click in the column to display the list of available items and we just have to choose whichever of our actual or budget columns we want to display to add it to our Table:
finished
In addition, we can refer to our Data Type column items from a cell outside our Table, and choose which of the available Data Types to use:
finished individual cell

Excel community

This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.

Excel polaroid
Topics
Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250