Lec 01: Sorting, Filters & Database Functions
- #excel-data-analysis
- #sorting
- #autofilter
- #advanced-filter
- #wildcards
- #subtotal
- #database-functions
- #filter-function
- #absolute-reference
- #conditional-formatting
Lec 01: Sorting, Filters & Database Functions
Part of: Excel for Data Analysis Key concepts: Sorting, AutoFilter, Advanced Filter, Criteria Range, Wildcard, SUBTOTAL, Database Function, DAVERAGE, DCOUNT, DSUM, FILTER, SEQUENCE, Absolute Reference, Conditional Formatting
What this note coversThe class workbook
Sorting .xlsx. It is the base dataset of the whole course: later workbooks (dates, IF statements, lookups, pivots) reuse the same 200-row employee table, so learn its columns now. The sheets areemployees,Sorting,Sorting 2,Filter,Filter 2,Filter 3,Filters advanced,Filters advanced 2andSheet1. Rows 2 to 201 hold the data; header in row 1.
1. The employee dataset
Every sheet from employees to Filters advanced 2 carries the same table in A1:J201 (200 employees, 10 columns). The only differences are the row order and what has been done on top of it (sorting, filters, extra columns).
| Column | Header | Type | Notes |
|---|---|---|---|
| A | Employee ID | number | 10002 and up |
| B | First Name | text | |
| C | Last Name | text | |
| D | Branch | text | 16 branches (Eilat, Haifa, Tel Aviv, Tiberias, ...) |
| E | Department | text | 9 departments (Sales, Computing, Warehouse, ...) |
| F | Birth Date | date | stored as a serial number |
| G | Hire Date | date | stored as a serial number |
| H | Seniority | number | years at the company |
| I | Position | text | Supporter, Employee, Team Leader, Branch Director, CEO, ... |
| J | Monthly Salary in NIS | number |
First rows of the employees sheet, copied from the workbook (the two date columns are left out here):
| Employee ID | First Name | Last Name | Branch | Department | Seniority | Position | Monthly Salary in NIS |
|---|---|---|---|---|---|---|---|
| 10002 | Lilach | Argov | Eilat | Computing | 6 | Supporter | 4095 |
| 10013 | Tamir | Pinchasof | Tiberias | Planning | 17 | Department Director | 4365 |
| 10020 | Danny | Tobias | Metula | Bookkeeping | 3 | Assistant | 4770 |
| 10031 | Pini | Etzioni | Rishon Lezion | Sales | 4 | Supporter | 4950 |
| 10033 | Svetlana | Pinchasof | Haifa | Human Resources | 24 | Supporter | 4005 |
Dates are numbersExcel stores a date as a serial number (days counted from a fixed start). The scratch cells
N12 = 36526andN13 = 21916on every sheet are the serials of 1 January 2000 and 1 January 1960. The class used them to type date thresholds as plain numbers in filters. Dates get a full treatment in Lec_03-Calendars & Date Calculations and Lec 04.
Conditional formatting already on the table
All the sheets carry the same Conditional Formatting rules, which is why the data looks colour-coded. They are the reason the class can later sort and filter by colour.
| Applies to | Rule | Colour |
|---|---|---|
A2:J201 |
whole row where $E2=$E$204 (Department equals the typed word in E204, here computing) |
orange fill |
H2:H201 (Seniority) |
greater than 24 | green |
J2:J201 (Salary) |
less than 5000 | red |
J2:J201 |
between 5000 and 10000 | yellow |
J2:J201 |
greater than 10000 | green |
The first rule is worth studying. It is a formula-based rule: $E2=$E$204 has a mixed reference ($E2: column locked, row free) so the test slides down the rows, and an absolute $E$204 so every row is compared with the one input cell. Change the word in E204 and a different department lights up. The label next to it, sen over 25 in H204 with 25 in H205, is a leftover note for the seniority rule (the stored rule is "greater than 24", which for whole years means 25 or more).
Common mistakeIf you forget the
$on the column (E2instead of$E2) the rule shifts sideways as well and colours the wrong cells. Mixed references are explained in section 7.
2. Sorting
Sheets: Sorting, Sorting 2.
Sorting reorders whole rows by the values of one or more columns (Data > Sort). Key ideas:
- Select a cell inside the table (or the whole table) so Excel moves complete records and never just one column. Sorting a single column on its own scrambles who earns what.
- Multi-level sort: sort by a main key, then by a second key within ties, and so on. The levels are applied in the order listed.
- You can sort by values, cell colour, font colour or icon. Colour sorting works with the conditional formatting above.
The Sorting sheet was saved with a four-level sort on A2:J201:
| Level | Sort on | Sort by | Order |
|---|---|---|---|
| 1 | Seniority (H) |
values | smallest to largest |
| 2 | Monthly Salary (J) |
cell colour | red on top |
| 3 | Monthly Salary (J) |
cell colour | yellow next |
| 4 | Monthly Salary (J) |
cell colour | green last |
Reconstructed from the workbookThe sort settings are read from the saved sort state in the file. The colours are identified from the colour definitions attached to each level (red, yellow, green fills). The class demonstration itself (what was clicked) is not in the file.
So employees are ordered by seniority first, and inside each seniority value the low-paid (red) rows come before mid-paid (yellow) before high-paid (green). Sorting 2 holds the same table in a different row order (it starts with employee 10137 instead of 10020), i.e. a second practice copy.
Sorting by colour needs three levelsExcel can put only one colour on top per level. To rank red, then yellow, then green you add one level per colour, exactly as the sheet does.
3. AutoFilter
Sheets: Filter, Filter 2, Filter 3.
The AutoFilter (Data > Filter, drop-down arrows on the header row) hides the rows that do not match. Hidden rows are not deleted; clear the filter and they return. It covers A1:J201 on all three sheets.
Filter types by column data type:
| Column type | Filter options |
|---|---|
| Text | equals, begins with, ends with, contains, custom |
| Number | equals, greater than, between, top 10, above or below average |
| Date | before, after, between, this month, grouping by year |
| Any | filter by cell colour or font colour |
Filter 2 was saved in a heavily filtered state. Reading the saved filter settings, four columns were filtered at the same time:
| Column | Filter | Meaning |
|---|---|---|
| E Department | custom: contains e or begins with a |
Excel allows two conditions in one column, joined by And/Or |
| F Birth Date | greater than 24108 | born after 1 January 1966 (24108 is that date's serial number) |
| H Seniority | above average | dynamic filter, recalculated against the column average |
| J Salary | filter by cell colour | one of the conditional-formatting colours |
How filters combineFilters on different columns are ANDed (a row must pass every column's filter). Inside one column you can choose And or Or between two conditions. For anything more elaborate (OR across different columns) the AutoFilter is not enough: that is what the Advanced Filter in section 5 is for.
Calculating on filtered data with SUBTOTAL
On Filter 2 the class added two totals under the table:
| Cell | Formula | Result | Meaning |
|---|---|---|---|
J204 |
=SUBTOTAL((9),J20:J45) |
61047 | sum of the visible salaries in J20:J45 |
H206 |
=SUBTOTAL(1,H20:H45) |
19.2 | average of the visible seniorities in H20:H45 |
SUBTOTAL takes a function number first, then the range:
| Number | Function |
|---|---|
| 1 | AVERAGE |
| 2 | COUNT |
| 3 | COUNTA |
| 4 | MAX |
| 5 | MIN |
| 9 | SUM |
Plain SUM and AVERAGE add up everything, including rows a filter has hidden. SUBTOTAL ignores rows hidden by a filter, so the figure changes as the filter changes. That is its whole purpose.
Common mistakes
- Using
=SUM(...)under a filtered list and trusting the number: it still includes the hidden rows.- The function numbers 101 to 111 are the variants that also ignore rows you hid by hand; 1 to 11 only ignore filtered-out rows.
- The range
J20:J45in the class file is just the block of rows visible in that filtered view. If the filter changes and new rows appear outside it, the formula does not grow by itself. Point it at the whole column range (J2:J201) for a stable total.SUBTOTALignores otherSUBTOTALcells inside its range, so grand totals don't double count.
Reconstructed from the workbookThe explanation of function numbers 101 to 111 and the "does not grow" comment are standard Excel behaviour, not text written in the workbook. The two formulas and their results are verbatim.
4. Wildcards
Reference text typed beside the Filters advanced 2 sheet (cells around P196 to R200):
| Pattern | Meaning (the class's own wording, tidied) |
|---|---|
* |
any number of characters (including none) |
? |
exactly one character |
a* |
starts with a |
*a |
ends with a |
*Haifa* |
contains Haifa |
a??? |
starts with a and has exactly three more letters after it (a four-letter word) |
Wildcard patterns work in AutoFilter text filters, in the Advanced Filter criteria, in database-function criteria, and in functions such as COUNTIF. They are not case sensitive.
Wildcards only work on text
*and?match text. Typing1*to find numbers starting with 1 will not match real numbers. Also, to look for a literal asterisk or question mark, put~in front (~*).
5. Advanced Filter
Sheets: Filters advanced and Filters advanced 2.
The Advanced Filter (Data > Advanced) is a filter driven by a criteria range that you build on the sheet in advance. Task text in the workbook gives the two steps:
- Create the criteria.
- Choose the headers to filter (the columns you want in the output).
5.1 The criteria range
A Criteria Range is a small block of cells whose top row is a copy of the exact column headers from the data table, with the conditions written underneath. The data table is A1:J201 (the "list range"). The layout rules:
- Headers must match the data headers exactly. A task note on the sheet says it has to be the same format, and that the filter would not work if the Branch header was not formatted as in the data (a hint that the class copied the headers rather than retyping them).
- Conditions on the same row are ANDed.
- Conditions on different rows are ORed.
The class drew all three patterns on Filters advanced 2 (cells I209 to L219), using Monthly Salary, Branch and Seniority:
AND: salary above 10000 and branch Eilat and seniority under 15, all on one row:
| Monthly salary in NIS | branch | seniority |
|---|---|---|
| >10000 | eilat | <15 |
OR: each condition on its own row, in a staircase:
| Monthly salary in NIS | branch | seniority |
|---|---|---|
| >10000 | ||
| eilat | ||
| <15 |
Mixed: "from Eilat OR (salary above 10000 and seniority under 15)":
| Monthly salary in NIS | branch | seniority |
|---|---|---|
| >10000 | <15 | |
| eilat |
Reading a criteria rangeRead each row as one complete test (AND across its cells), then OR the rows together. A blank cell in a row means "no condition on that column".
A blank row in the criteria range matches everythingIf the range you give Excel includes an empty row, every record passes, because an empty row is an empty set of conditions. Select only the rows that contain conditions.
5.2 The worked example on Filters advanced
Criteria range Q236:R239, output headers T236:X236:
| Branch | Monthly Salary in NIS |
|---|---|
| eilat | >9000 |
| Tel Aviv | >9000 |
*t* |
>9000 |
The output headers are First Name, Last Name, Branch, Seniority and Monthly Salary in NIS. First rows of the result stored in the sheet (22 rows in all):
| First Name | Last Name | Branch | Seniority | Monthly Salary in NIS |
|---|---|---|---|---|
| Hodaya | Argov | Tiberias | 11 | 9576 |
| Vadim | Dean | Kiryat Shmone | 26 | 9342 |
| Shaked | Zichroni | Kiryat Shmone | 7 | 9612 |
| Yuval | Zichroni | Kiryat Shmone | 12 | 9459 |
| Tal | Dean | Tel Aviv | 9 | 12060 |
Each row repeats >9000 because the salary condition must apply to every branch alternative: AND inside a row, OR across rows.
Reconstructed from the workbookI recomputed this from the data: employees with salary above 9000 whose branch is Eilat, Tel Aviv or contains a
tnumber 22 and their salaries sum to 252495, matching the stored result. The row*t*already covers Eilat and Tel Aviv (both contain a t), so the first two rows add nothing here; the class most likely put them in to show the wildcard row coexisting with exact names.
5.3 Copy to another location
In the Advanced Filter dialog you choose Filter the list, in-place (hides rows like AutoFilter) or Copy to another location. The second option writes the matching rows, with only the columns whose headers you placed in the output row, to a separate block. That is what the class did: the extract block starts at the headers in row 236 (T236:X236), and the sheet stores Criteria and Extract as hidden defined names pointing at those ranges (Filters advanced!$Q$236:$R$239 and $T$236:$X$236).
A second exercise on Filters advanced 2 has criteria I222:K228 (headers Branch, Department, Seniority) and an extract with Employee ID, First Name, Last Name, Branch, Department and Seniority in N222:S222:
| Branch | Department | Seniority |
|---|---|---|
| m* | Planning | >=8 |
| j* | Planning | >=8 |
| *a | Planning | >=8 |
| m* | Sales | >=8 |
| j* | Sales | >=8 |
| *a | Sales | >=8 |
This shows how to write "branch begins with m, or begins with j, or ends with a, and department Planning or Sales and seniority at least 8". There is no single-cell way to say OR inside one column in a criteria range, so every combination gets its own row (3 branch patterns times 2 departments = 6 rows).
Criteria rows multiplyOR across two columns (here branch pattern and department) forces you to write every pairing. That is the weakness of the criteria range, and a reason the newer FILTER function (section 6) is more convenient.
Reconstructed from the workbookThe stored output block on this sheet has 38 data rows. It does not look like the six-row criteria above applied to Branch (my recomputation gives a smaller list), and it matches roughly the Planning/Sales seniority-8-or-more set, so the extract was probably made with an earlier version of the criteria. The criteria text itself is verbatim; trust the rule, not that stored list.
6. Database functions
Sheet: Filters advanced, columns AC to AG.
Database Functions (the D-functions) compute a statistic over only the records that satisfy a criteria range, using the same kind of criteria range as the Advanced Filter. They all share three arguments, which the class summarised on the sheet in the form "d average (from data, field, criteria)":
| Argument | Meaning | Example |
|---|---|---|
| database | the whole table including the header row | A1:J201 |
| field | which column to calculate on, given as its header cell (or its number) | J1 |
| criteria | the criteria range, headers plus conditions | AC236:AE238 |
DAVERAGE
=DAVERAGE(A1:J201,J1,AC236:AE238)
Result: 8116.862068... (average salary). Criteria range:
| Position | Seniority | Hire Date |
|---|---|---|
| Support | <12 | >1/1/2000 |
| assistant | <12 | >1/1/2000 |
Meaning: average monthly salary of employees whose position is Support or assistant, with seniority under 12 and hired after 1 January 2000. The two position words are on different rows (OR); the seniority and hire date conditions are repeated on each row (AND).
Text criteria are "begins with"A text condition
Supportmatches any value that begins with those letters, so it catchesSupporter.assistantlikewise catchesAssistantandAssistant Director, and capitals do not matter. I checked this against the data: counting those matches reproduces the stored result of 8116.86 over 29 employees. To match a word exactly, write it as="=Support".
DCOUNT
=DCOUNT(A1:J201,A1,AC242:AD245)
Result: 33. Criteria range (<> means NOT):
| Last Name | Branch |
|---|---|
| <>Guri | Safed |
| <>Guri | Karmiel |
| <>Guri | Raanana |
Count of employees in Safed, Karmiel or Raanana whose last name is not Guri. The class note says the field argument "doesn't matter in this case". It matters only in that it must be a column of numbers: DCOUNT counts numeric cells, and Employee ID (A1) is numeric in every record, so it counts records. For a text column use DCOUNTA.
DSUM
=DSUM(A1:J201,J1,_xlnm.Criteria)
Result: 252495, which equals the sum of the 22 salaries in the advanced-filter output above. Here the criteria argument is not a typed address but the hidden name Criteria that Excel creates for the Advanced Filter's criteria range (Q236:R239). The label "Sum / Dsum" sits beside it. In practice, give the criteria as an ordinary range like the other two functions: it is clearer and also works on other sheets.
Database-function pitfalls
- The criteria headers must match the data headers exactly, character for character.
- The database range must include the header row.
- A blank row inside the criteria range makes the function ignore the criteria (everything matches).
- A formula placed inside the criteria range area, or the result cell inside the database, causes circular or wrong answers. Keep inputs and outputs apart.
Comparison
| Tool | Hides or copies rows | Returns a single number | Updates automatically | Typical use |
|---|---|---|---|---|
| AutoFilter | hides rows in place | no (use SUBTOTAL) | needs reapply on new data | quick exploration |
| Advanced Filter | hides, or copies to a new place | no | no (re-run it) | complex OR/AND criteria, extract to a new block |
| Database Function | no | yes | yes | one statistic over a criteria set |
| FILTER | spills matching rows into cells | no (returns a table) | yes | modern replacement for most filtering |
7. The FILTER function
Sheet: Filters advanced, Q263 and Q287.
FILTER (Excel 365 and 2021) returns the matching rows as a live table that spills into the cells below and to the right. The class's note: "* means AND, + means OR".
=FILTER(A2:J201,(E2:E201="Sales")*(H2:H201>10))
Task text: filter from the data, in the sales branch AND seniority greater than 10 (the text says "branch" but the condition is on the Department column E). It sits in Q264 and spills into Q264:Z285: 22 rows by 10 columns.
| Employee ID | First Name | Last Name | Branch | Department | Seniority | Position | Monthly Salary in NIS |
|---|---|---|---|---|---|---|---|
| 10042 | Sharon | Shabbat | Acre | Sales | 32 | Supporter | 4545 |
| 10068 | Pini | Philosof | Raanana | Sales | 17 | Head of | 3735 |
| 10009 | Hanney | Hakim | Tiberias | Sales | 38 | Department Director | 6255 |
| 10011 | Pini | Turgeman | Kiryat Shmone | Sales | 16 | Employee | 7326 |
(Birth and hire dates are also spilled; they are left out of this extract.)
Second exercise, Q287: work as team leader AND earn above 8000.
=FILTER(A2:J201,(I2:I201="Team Leader")*(J2:J201>8000))
It spills into Q288:Z301 (14 rows):
| Employee ID | First Name | Last Name | Branch | Department | Seniority | Position | Monthly Salary in NIS |
|---|---|---|---|---|---|---|---|
| 10017 | Lilach | Levy | Beer Sheba | Planning | 4 | Team Leader | 8505 |
| 10076 | Bilha | Belkin | Modiin | Computing | 6 | Team Leader | 9639 |
| 10153 | Hanney | Warburg | Hadera | Accounting | 11 | Team Leader | 9094 |
Why the multiplication works
Each comparison, for example E2:E201="Sales", produces a column of 200 TRUE/FALSE values. Multiplying two such columns turns them into 1 and 0 and gives 1 only where both are true: that is AND. Adding them (+) gives 1 or 2 where at least one is true: that is OR. FILTER keeps the rows where the combined result is non-zero.
=FILTER(A2:J201,(E2:E201="Sales")+(E2:E201="Warehouse"))
Reconstructed from the workbookThe
+(OR) formula above is an illustrative example written from the class's "plus means OR" remark; it is not in the file.
Common mistakes
#SPILL!: the cells where the result wants to land must be empty. The result area is sized by the data, so leave room.- Do not include the header row in the first argument (
A2:J201, notA1:J201), otherwise the header row is tested too and the ranges no longer line up.- The condition ranges must be the same height as the data (
E2:E201againstA2:J201).- An optional third argument sets what to show if nothing matches; without it you get
#CALC!.- Mixed text and number criteria: write the text in quotes, numbers without.
8. Absolute and mixed references (Sheet1)
Sheet: Sheet1, a 20 by 20 multiplication table. It is the class's exercise for locking references and also uses SEQUENCE to avoid typing the headers.
| Cell | Formula | What it does |
|---|---|---|
B1:U1 |
=SEQUENCE(,20,1,1) |
spills 1 to 20 across a row (rows empty, 20 columns, start 1, step 1) |
A2:A21 |
=SEQUENCE(20,,1,1) |
spills 1 to 20 down a column |
B2 (copied to U21) |
=B$1*$A2 |
multiplies the column header by the row header |
SEQUENCE(rows, columns, start, step) leaves out an argument by writing nothing between the commas. The multiplication-table core is a corner of the grid:
| 1 | 2 | 3 | 4 | 5 | |
|---|---|---|---|---|---|
| 1 | 1 | 2 | 3 | 4 | 5 |
| 2 | 2 | 4 | 6 | 8 | 10 |
| 3 | 3 | 6 | 9 | 12 | 15 |
| 4 | 4 | 8 | 12 | 16 | 20 |
| 5 | 5 | 10 | 15 | 20 | 25 |
The three reference types
Absolute Reference is the idea that a $ freezes part of an address when the formula is copied:
| Style | Example | When copied | Use |
|---|---|---|---|
| Relative | B1 |
row and column both move | ordinary running formulas |
| Absolute | $B$1 |
neither moves | a single input cell, such as a rate or threshold |
| Mixed, column locked | $A2 |
row moves, column stays | headers down the left side |
| Mixed, row locked | B$1 |
column moves, row stays | headers along the top |
In =B$1*$A2: B$1 always reads from row 1 (the top header) but follows the column, and $A2 always reads from column A (the side header) but follows the row. One formula, copied across 400 cells, fills the whole table. Press F4 while the cursor is on a reference to cycle through the four styles.
Check the table by handThe cell in column
K(header 10) and row 3 (header 2) must equal 20, and it does. A multiplication table is a useful self-check for any mixed reference.
Sheet1 also has conditional formatting on B2:U21: values under 50 get one colour, values from 100 to 200 another, and values over 200 a third (note that 50 to 100 is left uncoloured).
The same idea on the employee data: a bonus column
On Filters advanced, two extra columns sit beside the table:
| Cell | Formula | Meaning |
|---|---|---|
K2 |
=IF(J2>$K$204,$L$204,$L$205) |
if salary is above the threshold, pay the first bonus, otherwise the second |
L2 |
=K2+J2 |
salary plus bonus |
with the inputs typed once in a small block: K204 = 10000 (salary threshold, labelled "Salary greater"), L204 = 500 ("Give") and L205 = 1000 ("Else, give"). Salary above 10000 earns a bonus of 500, everyone else 1000. Because the three inputs are locked with $, the formula can be filled down all 200 rows and changing one input updates every row. Row 2, for example: salary 4095 plus bonus 1000 gives 5095, as in the sheet. The IF function itself is taught in Lec_02-IF Statements & Logical Functions.
Common mistakeTyping
10000and500directly into the formula ("hard-coding") works once but hides the assumptions. If the threshold changes you must edit 200 formulas. Put inputs in cells and lock them.
Stray cells to ignore
Filters advanced also contains J202 = SUM(J1:J201) (1629264, the total salary bill) and a stray XFD2 = SUM(A2:XFC2), a sum across an entire row to the last column of the sheet. They are leftovers, not part of the lesson. Avoid whole-row sums: they are slow and sensitive to anything accidentally typed in the row.
Summary
- The dataset (
A1:J201, 200 employees) is reused all course. Know its ten columns. - Sorting reorders whole rows; multi-level and colour sorting need one level per key or colour.
- AutoFilter hides rows; filters on different columns combine with AND. Use SUBTOTAL (not SUM) to total only the visible rows.
- Wildcards:
*any run of characters,?one character,~to escape them. - Advanced Filter: criteria range with exact headers; same row means AND, different rows mean OR; can copy results to another place with chosen columns.
- Database functions (
DAVERAGE,DCOUNT,DSUM) take database including headers, field and criteria range; text criteria mean "begins with". - FILTER spills matching rows; multiply conditions for AND, add for OR.
- Absolute and mixed references (
$B$1,B$1,$A2) let one formula be copied across a grid;SEQUENCEbuilds the headers.
Related notes
- Next: Lec_02-IF Statements & Logical Functions (the bonus column uses
IF), Lec_03-Calendars & Date Calculations, Lec 04 - Later uses of the same employee data: Lec_06-Lookups with XLOOKUP and INDEX MATCH, Lec_07-Pivot Tables
- Rest of the course: Lec_05-Time Functions & Protecting Cells, Lec_08-VBA Macros