Excel study tips: Creating lists with validation

Working with spreadsheets can make accounting tasks much more efficient and reliable, once we have learnt to use the required functionality. 

However, in order to achieve that we have to combine accounting knowledge with spreadsheet skills and for many of us, bringing both areas together is a real challenge. In this article we’re going to use data validation to create lists and thereby control the information that is contained within cells.

The context for this article is a children’s home and its monthly petty cash record. The issues are that the information is produced by a number of non-accounting staff who have a range of Excel skills, and that the current template is designed to fulfil internal needs as opposed to accounting requirements. Therefore, we’re going to amend the template to try to overcome these issues by building in some restrictions.

Issues in a month-end spreadsheet report

Download the raw data to have a go as you read through.

You can see in the spreadsheet the complete record for September and the range of descriptions used over the course of the month. There are also a number of spelling mistakes, a range of formats, like hyphens, and the inconsistent use of capital letters. The information needs to be analysed into accounting categories so it can be posted to the accounts, however the descriptions used to do this don’t match the accounting categories.

A further problem is that the inconsistencies in the raw data means the sorting function wouldn’t work properly. Manually analysing the information at month end is a time-consuming task but could easily be done by staff at the point they record the petty cash transactions, if they knew the accounting categories.

This is where providing a list starts to solve our issues.

Fixing the spreadsheet issues

Copy and paste the first eight rows into sheet 2, insert a new column for the accounting category and adjust the column widths:

The source data for lists can be created in a number of ways but for transparency we’re going to create the list of options so it is visible to the users. Mine is in column I:

We’re also going to name our list of options so that it is easier to use when we create the list. Highlight the cells and name them in the ‘name box’ which is under the Ribbon on the left hand side of the screen:

I named my data Options. A relevant name is always more memorable but mustn’t include spaces or numbers. Clicking on the drop-down arrow will always show any named data in a spreadsheet:

Free Excel webinar

Learn how to present effectively in Excel from expert Deborah Ashby. To view the recorded webinar please register your details below

View webinar

Setting up data validation

The list can now be created by using the data validation tools in the Data ribbon. Click into cell D4 then select data validation:

This brings up the ‘data validation’ box which currently shows any value is allowed in D4:

Validating the data will restrict what can be entered into a cell. For example, specifying that data must be a whole number, decimal, date or within a certain the length of text. In this case we want to restrict what is entered into D4 to one of the options in the list we’ve created. Therefore, select ‘list’ from the drop down choices and type in =Options for the data source:

A drop down arrow now shows on D4 and when opened, reveals the list of options for the accounting categories:

‘YP – other’ can now be selected for the description ‘GM Haircut’.

Naming the source data gives flexibility to add or amend information within the source data. For example, maybe it would be clearer to write Young People’s instead of YP. Rather than having to start again we can simply amend the source data in cell I9 and the change will be reflected in the list in D4:

Note that the exception to this is adding extra data at the bottom of the list as this will not be automatically incorporated.

We now have a list that means that the petty cash transactions can be categorised consistently in a way that will allow them to be analysed for accounting purposes.

However, if you remember, the staff who complete the spreadsheet are non-accounting professionals who have a range of Excel experience. Therefore, they may not notice the drop-down arrow and try to type into D4 instead. This will result in a standard error message:

Therefore it will be a good idea to provide some training on how to use the new template, and to use the built-in data validation messages.

Personalising the data validation error messages

Click into D4 and bring up the data validation box. Select the ‘error alert’ tab and add some text to explain that information in this cell can only be selected from the list:

Now if staff try to type into D4 themselves they will receive the customised message:

It’s never nice to be on the receiving end of an error message though, regardless of how polite it is, but we can supply an initial instruction so that hopefully the error message is redundant.

Creating initial instructions for Excel cells

Click into D4 and bring up the data validation box again. Now select the ‘input message’ tab and add some text to explain to staff what is required when they input data:

The instruction is now shown when the cell is selected:

Finally we can drag the data validation down column D so that the list and its associated messages are shown in all the cells. Note cell D4 should be blank before you drag the formatting.

The completed exert now looks like this:

How to clear your data validation settings

The last thing to note is that if you need to start again or make an amendment then you can simply clear the data validation by highlighting the required cells, bringing up the data validation box and selecting ‘clear all’:

Read more tips on Excel here

Browse the full range of AAT study support resources here

Gill Myers is a self-employed accounts consultant. She has taught AAT qualifications since 2005 and written numerous articles and e-learning resources.