OverviewStep 1 creating a seriesStep 2 adjust row heightsStep 3 add column totalsStep 4 add a names columnStep 5 add column headersStep 6 add currency formattingStep 7 freeze the column headingsStep 8 add a Salary columnStep 9 add a conditional formulaStep 10 add a COUNTIF formulaStep 11 utilising binary cellsStep 12 applying Conditional FormattingStep 13 adding text cellsStep 14 adding an AVERAGEStep 15 adding calculationsStep 16 adding a Filter
Problem: Fix:
Step 17 add a Pie Chart graphicStep 18 using the IF and CONCAT functionsADDITIONAL Work with timesADDITIONAL PrintingAdvanced Topic VLOOKUPsAdvanced Topics Pivot Tables
Excel training
First written: Feb-2024
last changed Mar-2024

Adding a User Controlled Filter

Here we want to add some controls to the worksheet so that the user can filter to see whatever they need at the time.

  1. Select/Highlight the heading cells on row 1.

  2. From the Editing tab on the ribbon, select Sort&Filter and then Filter

  3. You can now use the new drop down buttons on each column to apply a filter to the data shown - NOTE, this does not change the totals in this case.


This filter applies to all rows - including the totals. You can see this if you were to use the ID filter and see the value "210" which is from the total row.


Add an empty row to above the total row to create a break between the data and the totals.