Keyboard

No mouse needed.

Anywhere

←↑↓→
Move the selection around the page
Enter
Open whatever is selected
1…9
Go to a section along the top (or, in a workbook, to one of its sheets)
CtrlQ
Leave an open workbook for its list. Works while in the sheet; asks first if you have typed anything
Alt→
Next section (Alt ← for previous), or next sheet in a workbook. Also Alt PgDn and Alt PgUp
CtrlPgDn
Next section or sheet, as in Excel. Needs focus mode
CtrlHome
Top of the page (Ctrl End for the bottom)
S
Start, or continue after your last solved challenge
X
A random function drill (on the Drills page)
G
Jump into the spreadsheet
F
Focus mode: full screen, browser shortcuts handed to the page
?F1
Show or hide this list

On a challenge

CtrlEnter
Check your answer; once it is right, go to the next challenge. In an exam, the next question. Works while in the sheet
Ctrl/
Show a hint. Works while in the sheet
C
Check your answer
H
Show a hint
AltR
Reset the sheet. Works while in the sheet
AltP
Previous question in an exam (Alt N for the next). Works while in the sheet
B
Begin a practice exam

Moving and selecting in a spreadsheet

←↑↓→
Move one cell
Ctrl↓
Jump to the edge of the data
Home
Start of the row (Ctrl Home for A1, Ctrl End for the last cell)
CtrlG
Go to a cell or range: type it in the name box. Also F5
AltPgDn
Next sheet of a workbook with several (Alt PgUp for previous). Its sheets take over the tabs along the bottom. Mid-formula, select cells there to use them
Shift↓
Select a range (add Ctrl to reach the edge of the data)
ShiftSpace
Select the row (Ctrl Space for the column)
CtrlA
Select everything in use
EnterTab
Move down or right. With a range selected, travel through it
Esc
Cancel a copy, or leave the sheet for the page

Writing a formula

=
Start a formula (or just type)
Tab
Complete the suggested function name
←↑↓→
Pick a cell; hold Shift for a range
F4
Lock the reference: A1, $A$1, A$1, $A1
F2
Edit a cell, or switch the arrows between picking cells and moving the text cursor
Alt=
AutoSum the numbers above
Ctrl'
Copy the formula from the cell above
Enter
Confirm. Esc cancels
CtrlEnter
With a range selected, fill every cell with what you typed

Editing a spreadsheet

CtrlC
Copy (Ctrl X to cut)
CtrlV
Paste. Add Shift to paste values only
CtrlD
Fill down: from the cell above, or the top row through a range
CtrlR
Fill right
Delete
Clear the selected cells
CtrlZ
Undo (Ctrl Y to redo)
Ctrl[
Go to the cells a formula uses (Ctrl ] for cells that use this one)
Ctrl`
Show formulas instead of results

Formatting

CtrlB
Bold (Ctrl I italic, Ctrl U underline, Ctrl 5 strikethrough)
CtrlShift1
Number with two decimals and thousands commas
CtrlShift4
Currency
CtrlShift5
Percentage
CtrlShift3
Date (Ctrl Shift 2 for time, Ctrl Shift 6 for scientific)
CtrlShift~
General: remove the number format
CtrlShift7
Border round the selection (Ctrl Shift − removes borders)

On a Mac, use ⌘ wherever Ctrl is shown and Option wherever Alt is shown (Ctrl Space is taken by macOS). For number formats on a Mac use Control+Shift, as Excel does: ⌘+Shift+3, 4 and 5 are macOS screenshots. Ctrl+PgUp/PgDn, Ctrl+W, Ctrl+T and Ctrl+N belong to the browser; focus mode hands them to the page in Chrome and Edge.

Blog

The Excel formulas every analyst uses

The handful of functions that cover most of the work in a finance or operations job, with an example of each.

Excel has hundreds of functions, but a small set does most of the daily work. Learn these well and you can answer most questions a manager will ask of a spreadsheet.

Totals and conditions

SUMIFS adds up the rows that meet one or more conditions. Sales in the North region for one product:

=SUMIFS(D2:D500, B2:B500, "North", C2:C500, "Widgets")

The first range is what to add. After it come pairs: a range to test, then the condition it must meet.

COUNTIFS counts rows the same way, without a range to add: =COUNTIFS(B2:B500, "North", E2:E500, "Late") counts late orders in the North. AVERAGEIFS works like SUMIFS but returns the average.

IF makes a decision: =IF(D2>1000, "Large", "Small"). Keep each IF short. If you are nesting more than two, IFS or a lookup table is easier to read.

Lookups

Pulling a value from another table is the next most common task: a price for a product code, a manager for an employee ID.

=XLOOKUP(A2, Products!A:A, Products!C:C, "Not found")

If your colleagues use older versions of Excel, INDEX with MATCH does the same and works everywhere:

=INDEX(Products!C:C, MATCH(A2, Products!A:A, 0))

The 0 in MATCH means exact match. Forget it and you get the same silent errors as an approximate VLOOKUP.

Dates

Reports run by month, so date functions come up constantly.

  • EOMONTH gives the last day of a month: =EOMONTH(A2, 0) for this month, =EOMONTH(A2, -1) for last month. Good for grouping and for due dates.
  • NETWORKDAYS counts working days between two dates, skipping weekends: =NETWORKDAYS(A2, B2). Add a range of holiday dates as a third argument.
  • TEXT turns a date into a label: =TEXT(A2, "mmm yyyy") gives "Jan 2026".
  • YEAR and MONTH pull out the parts so you can filter or total by them.

Cleaning text

Data exported from other systems is rarely tidy. A few functions fix most of it:

  • TRIM removes extra spaces, the most common reason a lookup fails on data that looks right.
  • LEFT, RIGHT and MID take characters from a code: =LEFT(A2, 2) gives the country code from "GB-10442".
  • TEXTSPLIT splits on a separator: =TEXTSPLIT(A2, "-") puts "GB" and "10442" in two cells.
  • PROPER fixes capitals: "jane SMITH" becomes "Jane Smith".
  • TEXTJOIN puts pieces back together with a separator: =TEXTJOIN(", ", TRUE, A2:C2).

Common questions

Which Excel functions should I learn first?

SUM and IF, then SUMIFS and COUNTIFS, then XLOOKUP. Those cover totals, decisions, conditional totals and lookups, which is most of what day-to-day reporting needs.

What do Excel tests for jobs usually ask?

Most cover lookups (VLOOKUP or XLOOKUP), conditional totals (SUMIFS, COUNTIFS) and IF, often on a small table of sales or staff data. Being quick and accurate with those matters more than knowing rare functions.

Do these formulas work in Google Sheets?

Almost all of them do, with the same names and arguments. For splitting text, Google Sheets has its own SPLIT function, which works the same way for simple cases.

Try it yourself

Practise in the course

Not affiliated with Microsoft. Excel is a trademark of Microsoft Corporation.