Short answers to the Excel questions people ask most often, from counting unique values to adding leading zeros. Each answer links to a full guide on this site where there is one.
Formulas
What is the order of operations in Excel?
Brackets first, then powers (^), then multiplication and division, then addition and subtraction, left to right. So =2+3*4 is 14, and =(2+3)*4 is 20. See order of operations in Excel.
What does the ^ symbol do in a formula?
It raises a number to a power: =2^3 is 8 and =A2^0.5 is the square root of A2. The POWER function does the same.
How do I multiply many numbers?
=PRODUCT(A2:A20) multiplies every number in the range. To multiply a whole column by one number, such as 1.18 for GST, see the maths on a selection macro.
How do I apply a formula to an entire column?
Enter it in the first cell and double-click the fill handle (the small square at the bottom right of the cell). In an Excel Table, a formula in one cell fills the whole column by itself.
How do I copy a formula without the cell references changing?
Use $ to lock a reference, such as $A$2, or copy the formula text from the formula bar. More ways in copy formulas without changing references.
How do I combine text from two cells, like PGD and 2013?
=A2&" "&B2 returns PGD 2013. For a whole range with a separator, use TEXTJOIN. Example: combine first and last name.
How do I count the cells in a column until the first empty cell?
=MATCH(TRUE,ISBLANK(A1:A1000),0)-1. In Excel 2019 or older, press Ctrl + Shift + Enter.
Counting, lookups and duplicates
How do I count values without counting duplicates twice?
In Excel 365: =COUNTA(UNIQUE(A2:A100)). In older versions: =SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100)), as long as there are no blank cells.
How do I fill in the rate automatically when I type an item's name?
Keep a price list and use a lookup: =XLOOKUP(B2,Items!A:A,Items!B:B), or VLOOKUP in older versions. See Excel lookup formulas.
What can XLOOKUP do that VLOOKUP can't?
Look to the left, return a message when nothing is found, find the last match and survive inserted columns. See XLOOKUP vs VLOOKUP.
How do I find the last occurrence of a value?
=XLOOKUP(E2,A2:A100,B2:B100,,0,-1): the -1 searches from the bottom up.
How do I return the column header where a value appears in a row?
=INDEX($B$1:$F$1,MATCH(H2,B2:F2,0)). See INDEX MATCH.
Is there a formula to remove duplicates?
In Excel 365, =UNIQUE(A2:A100) returns each value once. For other methods, see remove duplicates in Excel.
How do I keep only values that appear once (removing the original too)?
=UNIQUE(A2:A100,,TRUE). The TRUE returns only the values that occur exactly once.
Data entry and formatting
How do I fill serial numbers quickly?
Type 1 in the first cell, hold Ctrl and drag the fill handle down. Or use =SEQUENCE(100). See create serial numbers in Excel.
How do I add leading zeros, like 00045?
=TEXT(A2,"00000") gives five digits as text. To keep them as numbers, use the custom number format 00000. See the TEXT function.
How do I replace 0 with a blank without changing 10 or 20?
In Find and Replace (Ctrl + H), find 0, leave Replace with empty, click Options and tick Match entire cell contents. Or hide zeros with the number format 0;-0;;@.
How do I make cells red when a target isn't met?
Home → Conditional Formatting → Highlight Cells Rules → Less Than, and enter the target. For whole rows, see highlight a row based on a value.
How do I shade alternate rows?
Format the data as a Table (Ctrl + T), or use the rule =MOD(ROW(),2)=0. See highlight alternate rows.
How do I type a fraction like ½?
Type 0 1/2 (zero, a space, then 1/2). Excel stores 0.5 and shows ½ with a Fraction format.
What is the shortcut for strikethrough?
Ctrl + 5. More in 200+ Excel shortcuts.
How do I add tick boxes for a checklist?
In Microsoft 365, select the cells and choose Insert → Checkbox. For other versions, see add a checkbox in Excel.
Sheets, files and protection
How do I insert several rows at once?
Select as many rows as you want to insert (click the row numbers), then press Ctrl + Shift + +. Or use the insert rows macro.
How do I rename all sheets in one go?
With a macro: see rename every worksheet.
How do I show the file or sheet name in a cell?
=CELL("filename",A1) returns the full path, file name and sheet name once the file is saved. See get the sheet name in Excel.
How do I stop people copying or changing my cells?
Review → Protect Sheet and untick Select locked cells. See protect data in an Excel workbook.