One of the issues I come across frequently in class are people who have to work with large amounts of data. That often means a Lot of scrolling and selecting of lists of data and I have often wondered how I could make this easier for people. I have found an answer with structural references. In this example, I have decided to use the SUMIFIS function to illustrate this.
The advantages of using structural references are:
- Less scrolling and re-selecting..you enter the headings once and that’s it.
- If you add/change data in the table, it’s automatically picked up by the function – you don’t have to go back and re-select.
- When you look at the formula, it’s easier to follow (both for you and for others)
You can view the video below and the file is here: Structural References practice file
[fvplayer src=’https://s3-eu-west-1.amazonaws.com/the-excel-expert/structural_references.mp4′]
Instructions
- Convert your list to a table (click in list, Ctrl and T)
- You will now see a new Ribbon
- On the left, rename the table – in the video below I have chosen sales
- Set up your parameters. In this example I used the following:
| Product | County | Classification |
| Carrots | Galway | Services |
- As always, click where you want the answer to go.
- Start typing the following: =sumifs(
- Then type in Sales (the table name) . This will now appear in a dropdown list
- Double click on Sales, type [
- You will now see a list of the headings e.g. Product, County, etc
- Double click on Product
- Click on I4 (the cell containing Carrots). Press ,
- Repeat these steps for the County and Classification.
- Press ) at the end to complete the function.