How do I reference a larger table in Microsoft Excel to create a smaller table?
A user has a large table of data containing percentages from 0% to 100%. Out of this data he wishes to create a smaller table which contains only data from 75% to 85%. He doesn’t want to copy and paste any data. How can this problem be solved quickly and easily?
We have used the advanced filter feature to find a quick solution because in the end the user wants to somehow filter the data based on certain criteria, isn’t it?
Let’s assume we have the data in column A with a label or header ‘% Marks’ in worksheet cell A1.
We copy the header % Marks and paste it twice in different columns in Excel worksheet cells E1 and F1
Below E1 in cell E2 we enter the criterion ‘>=75’
In cell F2 we enter the criterion ‘<=85%’
Now we place the cursor inside our table in column A
Next we click on data and select ‘advanced filter’ from the ‘sort & filter’ group
In the new advanced filter window that pops up we enter the ‘list range’ if not already entered
We enter the criteria range by selecting it (E1:F2)
We select the option-box ‘copy to another location’ and in the ‘copy to’ text box we enter B1 or it is entered automatically if we select B1
Finally click OK
The filtered data now appears in column B. This is our smaller table and it was created without any ‘copy & paste’ action.
Watch the training video below to learn how to reference a larger table in Microsoft Excel to create a smaller table using the advanced filter feature: