28 min read

Data Validation

Restrict a cell to a list of departments so typos cannot break your budget totals.

Why Data Validation on Expenses

Engish instead of English breaks filters and SUMIF. Data Validation limits input to a drop-down list — stopping typos before they reach formulas and PivotTables. When department heads log purchases on NorthgateBudget.xlsx, James Okonkwo locks column B on Expenses to the five Northgate departments only.

On Expenses, select B2:B50 (department column for future rows). Data tab \to Data Tools group \to Data Validation . On the Settings tab, Allow: choose List . In Source, type English,Mathematics,Science,Humanities,Arts — or select Budgets! A 2: A 6 for a live link. Click OK . Each cell now shows a drop-down arrow with only valid departments.

Linking the list to Budgets! A 2: A 6 means: Add a sixth department on Budgets \to the Expenses drop-down updates after you refresh One master list — no retyping comma-separated names On Data Validation \to Settings, click the Source box. Click the Budgets tab, select A2:A6 . Press Enter, then OK . Do not include the header: source A1:A6 adds Department as a valid pick — corrupting expense totals. Use A2:A6 — data rows only.

On the Error Alert tab of Data Validation: Style: Stop — blocks invalid typing. Title: Invalid department . Message: Choose a department from the list. Prevents *Engish* instead of *English* from breaking pivot tables. On the Input Message tab, set Title: Department and Message: Pick from the list — do not type free text. A yellow note appears when a cell is selected — helpful for colleagues who rarely use Excel.

After validation, test by typing Sci manually — Stop should block it. If Excel allows it, check that Allow: List is set and the workbook is not in a protected state that bypasses validation. Validated department names in column B align with =SUMIF and PivotTable filters on NorthgateBudget.xlsx .

Practice

Where is Data Validation?

Why Data Validation on Expenses

Engish instead of English breaks filters and SUMIF. Data Validation limits input to a drop-down list — stopping typos before they reach formulas and PivotTables. When department heads log purchases on NorthgateBudget.xlsx, James Okonkwo locks column B on Expenses to the five Northgate departments only.

Create a Drop-Down List

On Expenses, select B2:B50 (department column for future rows). Data tab \to Data Tools group \to Data Validation . On the Settings tab, Allow: choose List . In Source, type English,Mathematics,Science,Humanities,Arts — or select Budgets! A 2: A 6 for a live link. Click OK . Each cell now shows a drop-down arrow with only valid departments.

Source from the Budgets Sheet

Linking the list to Budgets! A 2: A 6 means: Add a sixth department on Budgets \to the Expenses drop-down updates after you refresh One master list — no retyping comma-separated names On Data Validation \to Settings, click the Source box. Click the Budgets tab, select A2:A6 . Press Enter, then OK . Do not include the header: source A1:A6 adds Department as a valid pick — corrupting expense totals. Use A2:A6 — data rows only.

Error Alert and Input Message

On the Error Alert tab of Data Validation: Style: Stop — blocks invalid typing. Title: Invalid department . Message: Choose a department from the list. Prevents *Engish* instead of *English* from breaking pivot tables. On the Input Message tab, set Title: Department and Message: Pick from the list — do not type free text. A yellow note appears when a cell is selected — helpful for colleagues who rarely use Excel.

Test the List

After validation, test by typing Sci manually — Stop should block it. If Excel allows it, check that Allow: List is set and the workbook is not in a protected state that bypasses validation. Validated department names in column B align with =SUMIF and PivotTable filters on NorthgateBudget.xlsx .

Practice

Where is Data Validation?