The new FILTER function is now available for Excel 365 users only. This new function is very powerful and convenient to track down the records in a Data Table. Sometimes a person or an entity may have several transactions in a table. One often filters the data manually, but what happens when you have to change the filter criteria and see what the transactions? Lookup functions are only good to look up a single item or data value so that will not meet our needs in this situation. Our L&OD Excel Level 2 course covers Filtering Data, and Excel Level 3 covers Data Validation (to create drop lists).
Scenario:
In our example (Figure 1), there are multiple sales transactions from sales reps. The objective is to see all the transactions for a Rep using their unique RepIDs.
Objective:
Hint: Use a drop list to enter a RepID (instead of manually typing the ID).
Use the new FILTER function to dynamically extract all the transactions for a particular Rep, and if you choose a different RepID selection, the FILTER function should automatically query the data table and display all the transactions for the RepID selected.
Steps:
- Convert the data range into a table (this step is not required, but it is useful when adding new data rows to the transaction table)
- Create a drop list for the RepIDs (Data Validation); Create the Headers for the FILTER output (Same as the headers in the Data Table)
- Enter a formula with a FILTER function to display all the relevant sales transactions for the RepID selected
Formula Syntax: =FILTER(Table1,Table1[RepID]=Ref)
** Ref is the cell reference where you enter the RepID
In our example, the RepID is entered in Cell K4, using a drop list** Table1 is the named data source with all the transactions
In our example, the Table1 is the data table from A3:I23, with the headers in Row 3Note: No spaces in the formula but you may type in upper or lower case.
** In this example, two transactions were extracted and displayed by choosing the RepID.
Leave a Reply