Chapter 6 · 8 levels
Tables, validation and formatting
Know what kind of data each column holds, filter and sort without breaking rows, turn highlight rules into formulas, use Excel Tables, and check entries against validation rules.
StartA sheet that other people type into, filter and read needs more than correct formulas. Someone has to decide what each column is allowed to hold, how the rows get sorted and filtered without scrambling them, which cells light up red, and what happens when a new row arrives at the bottom. That’s the work in this chapter.
You start with the vocabulary analysts use for columns: discrete and continuous numbers, nominal and ordinal categories, and the IDs that only look like numbers. Then you filter Harbor & Pine order lines with a criteria column, pick the right multi-level sort, and rebuild conditional formatting rules (greater than, below, above average, and a “Use a formula” rule with mixed references) as TRUE/FALSE formulas you can check row by row. After a quiz on Excel Tables and structured references, you write data validation rules as formulas and run them over submitted rows. The boss level replays an order-entry log and finds every row a validated form would have refused, and why.
Some of this lives in dialogs the browser grid doesn’t have: the Sort box, slicers, the Conditional Formatting and Data Validation menus. Each lesson gives you the exact Excel steps, and the exercise checks the formula that sits underneath them.
Levels
- Quiz: what kind of data is in each column?
from Ashley Miller, Data Analyst
variable types, discrete, continuous, nominal, ordinal, tidy data
- A filter you can check, written as a formula
from Andrew Ramos, Marketing Manager
autofilter, advanced filter, and, or, criteria column, freeze panes
- Quiz: sort by category, then by revenue
from Shirley Chen, Buyer
sort, multi level sort, sort dialog, shortcuts
- Highlight rules, one TRUE or FALSE per row
from Anna Chen, Sales Manager
conditional formatting, highlight rules, above average, data bars, icon sets, absolute references
- A "Use a formula" rule across a whole grid
from Mei Gonzalez, Buyer
conditional formatting, formula rules, mixed references, average
- Quiz: what an Excel Table does when a row arrives
from Samuel Cooper, Accountant
excel tables, structured references, named ranges, slicers, calculated columns
- Validation rules for the product import
from Rachel Ramirez, Merchandise Planner
data validation, text length, decimal range, date validation, isnumber, len, and
- Boss: replay the order-entry log through the new rules
from Jennifer Castillo, Support Team Lead
data validation, custom validation, list validation, ifs, conditional formatting, mixed references