01Drop-down lists (data validation)
- Select the cells that should have a list.
- Go to Data, then Data Validation.
- Under Allow choose List, and in Source type the options separated by commas, for example: Yes,No,Maybe.
- Click OK. A drop-down arrow appears when you select the cell.
Tip For a long list, type the options in a column elsewhere and set Source to that range. It is easier to change later.
02Freeze panes: keep the header visible
- Click the first cell below the header row (if the header is row 1, click A2).
- Go to View, then Freeze Panes, then Freeze Panes.
- To freeze only the top row, choose Freeze Top Row.
03Repeat the header on every printed page
- Go to Page Layout, then Print Titles.
- In Rows to repeat at top, select the header row (for example $1:$1).
- Check Print Preview to confirm every page starts with the header.
Tip If the table is too wide, change Scaling from No Scaling to Fit All Columns on One Page in the print settings.
04Conditional formatting: color cells automatically
- Select the column of scores.
- Go to Home, then Conditional Formatting, Highlight Cells Rules, Less Than.
- Type 60 and choose Light Red Fill with Dark Red Text. Failing scores are now highlighted.
- Data bars turn numbers into small bar charts so you can compare sizes at a glance.
Q&AFAQ
Can a drop-down allow several choices?
Not with Excel’s built-in data validation. Multiple selection needs VBA or a form tool.
The gridlines are missing when I print. What do I do?
On the Page Layout tab, tick Print under Gridlines, or add borders to the table.
Rather have it done for you?
Document and Excel help, from about us$9. Report writing, data organizing and basic Excel work: tables, formulas, pivot tables and charts, usually delivered within a day or two.