Flash Fill, Text to Columns, named ranges, Paste Special and the everyday tools that make Excel work faster.
12 articles
To AutoFill a series in Excel, type the first one or two values, select them, then drag the fill handle (the small square at the bottom-right of the selection) down or across. Excel continues the pattern: 1, 2, 3, dates, weekdays, months or Q1, Q2. For exact control, use Home > Fill > Series.
To trace precedents in Excel, click the formula cell and choose Formulas > Trace Precedents. Blue arrows point from every cell the formula uses. Trace Dependents shows the opposite: which formulas use the selected cell. Evaluate Formula then steps through the calculation one part at a time, so you can see where a wrong result comes from.
A custom number format changes how a number looks without changing its value. Select the cells, press Ctrl+1 (Mac: ⌘1), choose Custom and type a code in the Type box, such as 00000 for leading zeros, #,##0,"K" for thousands or 0 "kg" for units. Formulas still use the real number underneath.
The Excel shortcuts worth learning first are Ctrl+Arrow to jump to the edge of your data, Ctrl+Shift+Arrow to select it, Ctrl+D to fill down, F4 to add $ signs, Alt+= for AutoSum and Ctrl+Z to undo. On a Mac, most use ⌘ instead of Ctrl, but number formats use Control and F4 becomes ⌘T.
To fill blank cells with the value above, select the column, press F5, click Special, choose Blanks and click OK. Then type =, press the up arrow and press Ctrl+Enter (Mac: Control+Return). Every blank now copies the cell above it. Finish by copying the column and pasting it as values, so the formulas become fixed text.
Flash Fill fills a column by copying a pattern from examples you type. Type the result you want in the first cell next to your data, press Enter, then press Ctrl+E (or Data > Flash Fill) and Excel fills the rest. It works for splitting, joining and reformatting text, but the results are fixed values that don't update when the source changes.
Go To Special selects every cell of one kind at once, such as blanks, formulas, constants, errors or visible cells only. Press F5 (or Ctrl+G), click Special, choose the type and click OK. Select a range first to search only that range. It's also on Home > Find & Select > Go To Special.
To unhide rows in Excel, select the rows above and below the hidden ones, right-click a row number and choose Unhide. To unhide everything, select the whole sheet first. To hide rows, select them and press Ctrl+9 or right-click and choose Hide. Hidden rows still count in SUM; use SUBTOTAL(109,...) to total only the visible ones.
A named range is a name, like TaxRate or Sales, that stands for a cell or range. To create one, select the cells, click the Name Box, type the name and press Enter. Then use it in formulas, such as =SUM(Sales) or =B2*TaxRate. Names are absolute by default, so they don't move when you copy a formula.
To print an Excel sheet that fits the page, go to File > Print and change No Scaling to Fit All Columns on One Page, or set Width to 1 page in Page Layout > Scale to Fit. Switch to Landscape for wide tables, and use Page Layout > Print Titles with $1:$1 in Rows to repeat at top so headers print on every page.
Every cell in Excel is locked by default, but locking only works once the sheet is protected. To lock formulas but let people type inputs, select the input cells, press Ctrl+1 (Mac: ⌘1), untick Locked on the Protection tab, then go to Review > Protect Sheet. Locked cells can then be viewed but not edited.
Text to Columns splits the text in one column into several columns. Select the column, go to Data > Text to Columns, choose Delimited, tick the separator (such as comma or space), pick a destination and click Finish. The result is fixed values; if the source data changes, run it again or use a formula such as TEXTSPLIT or LEFT with FIND.