Occasionally you may want to filter or compare your survey data based on a custom filter that isn't already included in your Excel export. We'll explain how to add new columns and use those in pivot tables to summarise data by the contents of that new column, and compare results between the items in the column.

A good example of where you may wish to add a custom column is when grouping respondents into age brackets. Age is one of the most frequently used demographics to target survey audiences. It allows us to see how attitudes and behaviours change from generation to generation - and that's the key part: generations. You probably want to analyse your responses by age groups rather than individual ages, and this article will help you add your own age brackets to your data set. We will be working in the Master Data sheet, which you can read more about here.

Nationally Representative (NatRep) age brackets are a commonly used quota in Attest surveys to capture a fair representation of the UK's working population, so we'll use this as an example in this article.

It's important to note that there are no overlaps between the ages of each range - the brackets here run from 18-24 and 25-34 (as opposed to 24-34).

If you are using your own age brackets, you should also consider that the smaller your ranges, the fewer respondents will appear in each, so ensure your brackets are wide enough to include a robust number of respondents to draw conclusions; typically, we would recommend you don't group your data into more than 5 or 6 groups (depending on the sample size).

You first need to create a new column (right click and select insert) in the Master Data sheet and give it an appropriate name.

Next, create a new sheet called age_brackets and enter three columns similar to those below that will fit your needs. This will allow you to change your age brackets quickly.

We'll use the Min column and a lookup to retrieve the Name of your age bracket with the VLOOKUP formula. You can find more detailed notes on how this formula works at the bottom of the page. It's important that this is in ascending order - this will be explained later*. Note that Max is not used in the lookup, but can help to keep things clear.

Back in the Master Data worksheet, we can use the following formula in the Age Bracket column you created to look up the Name of the bracket.

Dragging down the formula gives us the correct age brackets:

This column can then be used to filter your Master Data sheet for a filtered view of a group of respondents, or you can use the new column in a pivot table to enable you to compare how each age bracket respondent to each question. Read on for more about using the new custom columns in pivot tables.

Lookup value $B2: the lookup is based on the age column

Table array age_brackets!$A$1:$C$6: this is the worksheet and cells where we created with age bracket names, and the age limits.

Col index num 3: the column we want to retrieve (Name) is in the third column of the array we selected.

*Range lookup TRUE: this means the age we are looking up does not have to match an exact value that we've put in the age_brackets table, but will use the closest match, or the last number it "went past" from top to bottom. This is also why age_brackets should have been created in ascending order.

Keep in mind that VLOOKUP will always use the first column of your array to try and find your lookup value, and the column you return always counts from the first column in your array.

The official documentation for VLOOKUP can be found here.

If you've not created any pivot tables in your export yet please follow this guide to set up a pivot table ready for the custom columns to be inserted.

In the sheet containing the pivot table we need to refresh the pivot table to generate a new Field Name for our new column. To do this, navigate to the Pivot Table Analyse tab on the ribbon at the top of the page and select Refresh.

This will reprocess the data and our new Age Bracket Field Name will populate in the Field Names list. From here it can be dragged into the Columns area to generate a pivot table that shows the split of answers between each age band, just as you would for any other demographic profile.

For more help using your Excel export, don't hesitate to reach out to our team of in-house analysts using the in-platform live chat.