Interesting Use of Advanced Filter in MS Excel

Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010




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.
  1. We copy the header % Marks and paste it twice in different columns in Excel worksheet cells E1 and F1
  2. Below E1 in cell E2 we enter the criterium '>=75'
  3. In cell F2 we enter the criterium '<=85%'
  4. Now we place the cursor inside our table in column A
  5. Next we click on data and select 'advanced filter' from the 'sort & filter' group
  6. In the new advanced filter window that pops up we enter the 'list range' if not already entered
  7. We enter the criteria range by selecting it (E1:F2)
  8. We select the optionbox 'copy to another location' and in the 'copt to' text box we enter B1 or it is entered automatically if we select B1
  9. Finally click Ok
  10. 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:

Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010