Excel mastery cheat sheet

01

Secret keyboard shortcuts

Often overlooked, always faster

Formula bar

Ctrl+Shift+U

Expand or collapse the formula bar instantly

Dialog edit mode

F2

Use arrow keys in a formula dialog without selecting cells

Repeat last action

F4

Reapply any format, insert, or edit in one keystroke

Jump to active cell

Ctrl+Backspace

Snaps view back to your active cell after scrolling

Cycle selection corners

Ctrl+.

Jumps between all four corners of a selected range

Quick copy sheet

Ctrl + drag tab

Instantly duplicates a sheet — no right-click needed

Named ranges list

F3

Inside a formula, shows all named ranges for insertion

Expand / collapse groups

Shift + scroll

Toggle grouped rows, columns, or PivotTable fields

02

Dynamic data prep

Office 365 — charts that auto-update as data changes

FILTER =FILTER(Table, Table[Dept]=X)

Dynamic table — chart shrinks or grows with a dropdown selection

FILTER — AND =FILTER(A, (C1)*(C2))

Multiply criteria brackets for an AND condition across multiple columns

SORT + FILTER =SORT(FILTER(...), i, order)

Wrap FILTER in SORT to keep chart data automatically organized

Dynamic headers =TRANSPOSE(UNIQUE(Table[Year]))

Horizontal headers that update as new years appear

03

McKinsey-style in-cell bars

Professional visuals in under 90 seconds, no chart tool needed

Live preview — =REPT("—", value/10)

Q1

82

————————

Q2

65

——————

Q3

91

—————————

Q4

48

————

Basic formula

=REPT("—", Value)

Any character works — dashes, circles (●), or symbols from the emoji picker (Win + .)

For large numbers

=REPT("—", Value/100)

Divide by a scale factor so bars stay a manageable length

Styling tip

Because bars are text, apply any font color, size, or weight via standard cell formatting to match a corporate theme — no special chart formatting required

04

The Economist design secrets

Replicate clean, high-end chart aesthetics

Titles on top

Place category labels directly above bars instead of the default left-axis position — more compact and easier to read at a glance

Linked text boxes

Select chart → Insert → Text Box → click formula bar → type = → click your title cell. The box moves with the chart and updates automatically

Dumbbell charts

Use a Scatter Plot to show before/after change. Add Error Bars set to "Minus" with "No Cap" to draw a horizontal connecting line between the two dots

Reverse axis order

Right-click axis → Format Axis → check "Categories in reverse order" if your bars appear opposite to the table order

Stacked labels with line breaks

In Custom Number Format, press Ctrl + J to insert a line break — combine with Wrap Text to stack "Day" over "Month" and save horizontal space

05

Custom number formats

Ctrl+1 → Number → Custom — display rules the ribbon can't touch

The syntax — up to 4 sections, separated by semicolons

Section 1

Positive

Section 2

Negative

Section 3

Zero

Section 4

Text

Omit sections you don't need — a single section applies to all values. Wrap a colour name in [brackets] at the start of any section.

Number placeholders

0

Shows digit, or 0 if empty

#

Shows digit, hides leading zeros

,

Thousands separator

.

Decimal point

Trailing , after the format divides by 1,000 — two trailing commas divides by 1,000,000

Date & time codes

d / dd

7 / 07

ddd / dddd

Wed / Wednesday

mmm / mmmm

May / May

yy / yyyy

26 / 2026

ddd, mmm dd, yyyy

Cell still behaves as a real date — formulas work normally

P&L colours — green positive, red negative

[Green]$#,##0.00;[Red]-$#,##0.00;$0.00

Eight built-in colour names: Black, Blue, Cyan, Green, Magenta, Red, White, Yellow

Thousands shorthand — show 1,500,000 as 1,500k

#,##0,"k"

Each trailing comma after the format divides by 1,000. Add "M" with two commas for millions

Add literal units — number stays a real number

0.0" kg"

Wrap any text in double quotes to append it as a suffix (or prefix). The cell value stays numeric — SUM and formulas still work

Hide a cell's display — value stays active

;;;

Three semicolons make the cell appear blank while preserving the underlying number. Useful for heat maps and in-cell chart labels

Prefix text inputs automatically

;;;"Note: "@

@ is the placeholder for text entries. Section 4 only applies when the cell contains text, not a number

Stacked axis labels — save horizontal space

In the Custom format Type field, press Ctrl+J to insert a line break. Combine with Wrap Text to stack "Mon" over "Jan" in a single axis label without widening the column