Excel how-to guides
12 tasks that come up on Excel job tests, each with the ribbon path and keyboard shortcut for Windows Excel, a before and after grid, and the mistakes that cost points.
| Task | What you learn |
|---|---|
| remove duplicates in Excel | Delete repeated rows with the Remove Duplicates command, choose which columns decide what counts as a repeat, and check the result before you trust it. |
| make a pivot table in Excel | Summarize a long table by category with Insert, PivotTable: put a field in Rows, put a number field in Values, and refresh when the data changes. |
| highlight cells with conditional formatting | Color cells automatically when they meet a condition, using Home, Conditional Formatting, either a ready-made rule or a formula rule for whole rows. |
| freeze panes in Excel | Keep a header row or an ID column on screen while the rest of the sheet scrolls, using View, Freeze Panes. |
| sort by multiple columns in Excel | Order a table by one column and then break ties with a second, or a third, using the Sort dialog and Add Level. |
| filter data by a condition in Excel | Show only the rows that meet a condition, such as region equals East or amount over 500, with the Filter button or the FILTER function. |
| use Text to Columns in Excel | Split text in one column into separate columns at a comma, space, tab or other delimiter, with the Text to Columns wizard on the Data tab. |
| combine first and last names in Excel | Join a first-name column and a last-name column into one full name, with the right spacing, order and handling for missing parts. |
| split full names into first and last name in Excel | Break a Full Name column into First and Last columns with a formula that keeps working, and handle middle names that break the simple version. |
| use VLOOKUP between two sheets in Excel | Look up a value on one sheet and return a matching field from a table on another sheet, with the table range locked so the formula can be filled down. |
| Absolute vs relative references in Excel | A relative reference moves when you copy a formula, an absolute one stays fixed, and a mixed one locks only the row or the column. |
| AutoFit column width in Excel | Resize columns to fit their contents so nothing is cut off and numbers do not turn into hash marks. |