| Concept | Description |
|---|---|
| Sorting and Filtering | |
| Never Sort a Single Column Alone | Sorting one column without expanding the selection scrambles rows silently and often unrecoverably |
| Multi-Level and Custom Sorting | Levels apply in order; custom lists give a meaningful order such as Kharif before Rabi |
| AutoFilter | Ctrl+Shift+L toggles filters, and the value checklist doubles as an audit of distinct values |
| SUBTOTAL and AGGREGATE | SUM ignores filters entirely; SUBTOTAL with a 100-series code counts only visible rows |
| Advanced Filter | A criteria range expresses OR across columns, with same-row conditions combining as AND |
| Dynamic Arrays | |
| FILTER, SORT, and UNIQUE | One formula returns many rows, spilling into adjacent cells and updating with the source |
| AND and OR in Dynamic Arrays | Multiplying conditions gives AND, adding them gives OR, using TRUE and FALSE as one and zero |
| Conditional Formatting | |
| Conditional Formatting Presets | Highlight rules, top and bottom rules, data bars, colour scales, and icon sets |
| Formula-Based Rules | A formula rule formats a whole row based on one column's value |
| The Mixed Reference in a Row Rule | Pin the column with a dollar and leave the row relative, matching the first row of the selection |
| Managing Rule Precedence | Rules apply top-down with Stop If True; fragmented ranges after copying slow the workbook |
16 Sorting, Filtering, and Conditional Formatting
Before any model is fitted, someone has to look at the data. Which farms yielded worst. Whether the stressed ones cluster in one district. Whether a value of 118 quintals per hectare is a genuine outlier or a typo for 11.8.
Sorting, filtering, and conditional formatting answer those questions in seconds without a single formula, which is exactly what makes Excel worth opening first.
16.1 Sorting
One warning before anything else, because this mistake destroys data silently and cannot always be undone once the file has been saved.
Never select a single column and sort it. Excel will offer to expand the selection. Accepting keeps each row intact. Declining sorts that one column while leaving all other columns where they were, which scrambles the correspondence between farm IDs and their measurements. There is no error message and no visual sign, because every cell still holds a valid value. The rows simply no longer mean anything.
Select a single cell inside the data and let Excel detect the range, or convert the range to a Table first, where the problem cannot occur.
Single-column sort. Click any cell in the column, then Data, then A to Z or Z to A.
Multi-level sort. Data, then Sort, then Add Level. Levels apply in order, so sorting by District then by YieldQtPerHa descending groups the districts and ranks farms within each one. Up to 64 levels are allowed; more than three is usually a sign the question needs a PivotTable instead.
Custom order. Alphabetical is wrong for seasons: Kharif, Rabi, Summer is the meaningful order, and alphabetically Kharif comes before Rabi only by luck. In the Sort dialog, set Order to Custom List and type the sequence. The list is saved in Excel and becomes available to every workbook on that machine.
Sorting left to right. Sort, then Options, then Sort left to right, which reorders columns rather than rows. Occasionally useful for reshaping a wide layout.
Blank cells always sort to the bottom regardless of direction. Numbers stored as text sort separately from real numbers, which is another way that particular defect announces itself.
16.2 Filtering
Ctrl + Shift + L toggles filter buttons on the header row. Tables have them already.
Each column’s dropdown offers a checklist of its distinct values, which doubles as a quick audit: a District filter listing seven entries where five districts exist has shown a spelling problem without any formula being written.
Beyond the checklist, the dropdown offers typed filters that adapt to the column’s data:
| Column type | Available filters |
|---|---|
| Text | Equals, begins with, ends with, contains, does not contain |
| Number | Equals, greater than, between, top 10, above average, below average |
| Date | Today, this month, last quarter, between, all dates in a period |
Above average deserves a mention. It computes the mean of the visible values and filters to those exceeding it, with no formula and no helper column.
Filters combine across columns with AND logic. Filtering District to Guntur and Status to Stressed returns farms that are both.
To clear one column’s filter, open its dropdown and choose Clear Filter. To clear everything, Data then Clear.
16.2.1 Counting What a Filter Shows
SUM and AVERAGE ignore filtering entirely. A filtered sheet showing 12 Guntur farms still returns the total of all 60 from =SUM(J2:J61), which is a common and expensive misreading.
SUBTOTAL respects the filter:
=SUBTOTAL(109, J2:J61) Sum of visible rows only
=SUBTOTAL(101, J2:J61) Average of visible rows
=SUBTOTAL(103, A2:A61) Count of visible non-empty cells
=SUBTOTAL(104, J2:J61) Maximum of visible rows
=SUBTOTAL(105, J2:J61) Minimum of visible rowsThe function numbers come in two ranges. The 1 to 11 series ignores rows hidden by a filter but includes rows hidden manually. The 101 to 111 series ignores both. Use the 100 series.
AGGREGATE goes further, adding the ability to skip error values, which matters on a column where a few lookups failed:
=AGGREGATE(1, 6, J2:J61) Average, ignoring errors and hidden rowsIts first argument selects the function (1 for average, 9 for sum), the second sets what to ignore (6 for errors, 7 for hidden rows and errors both).
16.2.2 Advanced Filter
The standard filter cannot express OR logic across different columns. Farms that are either in Guntur or rainfed, rather than both, need Advanced Filter.
Build a criteria range somewhere above or beside the data. Its first row repeats the column headers exactly. Conditions on the same row combine with AND; conditions on different rows combine with OR.
District |
Irrigation |
YieldQtPerHa |
|---|---|---|
| Guntur | ||
| Rainfed | ||
>45 |
That range selects farms in Guntur, plus all rainfed farms, plus all farms yielding above 45, since each condition sits on its own row.
Then Data, Advanced, set the List range to the data and the Criteria range to the block just built. Copy to another location writes the result elsewhere rather than hiding rows in place, which is usually what is wanted. Unique records only additionally removes duplicates from the output, making it a quick way to extract a clean list of distinct combinations.
16.3 Dynamic Array Functions
On Microsoft 365 and Excel 2021, filtering became a formula. One formula returns many results, spilling into the cells below and to the right, and updating automatically when the source changes.
=FILTER(FarmData, FarmData[District]="Guntur")
=FILTER(FarmData, (FarmData[District]="Guntur") * (FarmData[Status]="Stressed"))
=FILTER(FarmData, (FarmData[District]="Guntur") + (FarmData[Irrigation]="Rainfed"))Multiplication between conditions gives AND. Addition gives OR. This is the same arithmetic on TRUE and FALSE as 1 and 0 described in Essential Formulas and Functions.
A third argument covers the empty case, which otherwise returns #CALC!:
=FILTER(FarmData, FarmData[YieldQtPerHa]>60, "No farms above 60")Sorting and deduplication work the same way:
=SORT(FarmData, 10, -1) By the 10th column, descending
=SORTBY(FarmData, FarmData[YieldQtPerHa], -1)
=UNIQUE(FarmData[District]) Distinct districts
=UNIQUE(FarmData[[District]:[CropCode]]) Distinct combinations
=COUNTA(UNIQUE(FarmData[District])) How many distinct districtsThey nest, which is where they get genuinely useful. The top five yielding stressed farms, live, in one cell:
=TAKE(SORT(FILTER(FarmData, FarmData[Status]="Stressed"), 10, -1), 5)A #SPILL! error means something is occupying the cells the result needs. Clear them and the formula completes. On Excel 2019 and earlier these functions return #NAME?, and the equivalent work is done with Advanced Filter or a PivotTable.
16.4 Conditional Formatting
Formatting driven by cell values, so that patterns become visible without reading individual numbers. Home, then Conditional Formatting.
Highlight Cells Rules covers the obvious comparisons: greater than, less than, between, equal to, text that contains, a date occurring in a period, and duplicate values.
Top and Bottom Rules covers top 10 items, top 10 percent, above average, below average. Both the count and the percentage are editable, so top 10 can become top 5.
Data Bars draw a proportional bar inside each cell, turning a column of numbers into a bar chart in place. For comparing 60 yields at a glance, this beats any actual chart.
Colour Scales map values onto a two or three colour gradient. Useful for NDVI, where a red to green scale matches how the index is normally read.
Icon Sets attach arrows, flags, or traffic lights to value bands. Use sparingly; three icons plus a colour scale plus data bars on one sheet is unreadable.
16.4.1 Formula-Based Rules
The built-in rules format the cell being tested. A formula rule can format an entire row based on a value in one of its columns, which is what makes conditional formatting genuinely useful.
Select the data range starting from the top-left data cell, then Conditional Formatting, New Rule, Use a formula to determine which cells to format.
=$K2="Stressed"Every row whose Status column reads Stressed is formatted.
The mixed reference is the entire trick. $K pins the column so every cell in the row tests against column K, while 2 stays relative so each row tests its own value. Written $K$2, all 60 rows would test the single cell K2 and would either all format or none. Written K2 with no dollar, each column would test a different column’s value.
The row number in the formula must match the first row of the selection. Select A2:K61 and the formula references row 2. Select A1:K61 by mistake and every rule is off by one row.
Further examples:
=$H2 < 0.5 Low NDVI
=$J2 < AVERAGE($J$2:$J$61) Below the overall mean yield
=AND($E2="Rainfed", $J2>45) Rainfed farms that still did well
=COUNTIF($A$2:$A$61, $A2) > 1 Duplicate farm IDs
=$G2="" Missing rainfall16.4.2 Managing Rules
Conditional Formatting, then Manage Rules, lists every rule on the sheet with its range and its precedence.
Rules apply top-down, and Stop If True halts evaluation when a rule matches, which is how overlapping rules are kept from fighting.
Two habits prevent trouble. Check the Applies to range after any copy, paste, or row insertion, since Excel fragments rules into dozens of near-identical entries covering odd slices of the sheet, which slows the file noticeably. And keep the rule count low; a sheet with 40 conditional formats opens slowly and communicates less than one with three.
Rules can be removed from a selection or the whole sheet through Clear Rules.