Excel Cheat Sheet

I have learned a great deal about Excel in the last few months and would like to share my cheat sheet. Save it Word format and when you are wondering what was that shortcut to put borders around a cell, just do a quick look up CTRL+F "border". Fast and easy!

CTRL Shortcuts

CTRL + A – Select all
CTRL + B – Bold
CTRL + C – Copy
CTRL + X – Cut
CTRL + V – Paste (Also useful: ALT E, S – Paste Special: T – format, V – Value, E – Transpose)
CTRL + D – Fill down (copies value from the cell above)
CTRL + F – Find
CTRL + G – Go to (useful for lists)
CTRL + H – Replace
CTRL + I – Italic
CTRL + K – Insert Hyperlink
CTRL + N – New Workbook
CTRL + O – Open file
CTRL + P – Print
CTRL + R – Fill right
CTRL + S – Save workbook
CTRL + U – Underline
CTRL + W – Close window
CTRL + Z – Undo
CTRL + Y – Redo
CTRL + 1 – Format window
CTRL + 5 – Strike-through
CTRL + 7 – Hide/show toolbar
CTRL + 9 – Hide row
CTRL + SHIFT + 9 – Unhide row
CTRL + 0 – Hide column
CTRL + SHIFT + 0 – Unhide column
CTRL + ~ - Show formulas/values
CTRL + ‘ – Copy above formula
CTRL + ; - Insert today’s date
CTRL + SHIFT + : - Insert current time
CTRL + [ - Precedents
CTRL + [ - Dependents
CTRL + Enter – Fill selection with entry
CTRL + Pg dn – Next worksheet/tab
CTRL + Pg up – Previous worksheet/tab
CTRL + SHIFT + Pg dn – Group with next tab(s)
CTRL + SHIFT + Pg up – Group with previous tab(s)
(ALT + E + U – Ungroup tabs)
CTRL + \ - In a selected row, highlight the cells that do not match the formula or value in the active cell
CTRL + / - Select the array containing the active cell
CTRL + SHIFT + * - Select the current region around the active cell (the data area enclosed by blank rows and blank columns)
CTRL + SHIFT +O (letter O) – Select all cells that contain comments

ALT Shortcuts

ALT + tab – switch program (hold ALT down while tapping Tab to select out of all open windows)
ALT + ‘ – Display style
ALT + T + O – Options menu (use Tab to move between tabs)
ALT + F, A – Save As
ALT + F + D + A – Send file as an attachment
ALT + L, G, ALT + R – Save with ‘Read-only’ recommendation (use this combination when the Save window is open)
ALT + D + P – Insert pivot table
ALT + D + F + F – Insert/remove Autofilter
ALT + D + L – Data validation (useful to create a drop-down menu referencing a list of values)
ALT + T + G – Goal seek function
ALT + T + I – Add-Ins menu
ALT + E + S – Paste Special box (T – Format, V – Value, E – Transpose)
ALT + W + S – (Un)split panes
ALT + W + F – (Un)freeze windows
ALT + E + M – Move worksheet
ALT + E + L – Delete worksheet
ALT + V + Z – Change view sizing
ALT + O + C + A – Autofit column width
ALT + O + R + A - Autofit row width
ALT + O + H + R – Change tab name
ALT + I + R – Insert row
ALT + I + C – Insert column
ALT + I + E – Insert copied cells
ALT + I + W – Insert worksheet
ALT + E + A + F – Clear format only (content will stay)
ALT + E + A + C – Clear content only (format will stay)
ALT + E + A + A - Clear all (content and format)
ALT + F + V – Print preview
ALT + E + U – Ungroup tabs
ALT + T + P + P – (Un)Protect worksheet
ALT + T + B + A – Share workbook
ALT + I + S – Insert a symbol
ALT + D + E – Convert text to columns box
ALT + N – stay with Delimited category (useful for breaking a string of data from 1 cell into a few cells) and go to next window.
ALT + C – to choose Comma as the separation point for data within cells
ALT + M – to choose Semicolon as the separation point
ALT + S – choose Space as the separation point
ALT + O – choose a custom character as the separation point
ALT + D + E + W – Choose Fixed Width category (useful to break the contents of the cells according to predetermined width, i.e. if there is a space in the beginning of every cell)

Formatting Shortcuts

ALT + ‘ – Display style
CTRL + 1 – Format box
CTRL + SHIFT + F – Format font/size
SHIFT + CTRL + ~ - General format
SHIFT + CTRL + ! – Number format
SHIFT + CTRL + @ - Time format
SHIFT + CTRL + # - Date format
SHIFT + CTRL +$ - Currency format
SHIFT + CTRL + % - Percentage format
SHIFT + CTRL + ^ - Exponential/scientific format
SHIFT + CTRL + & - Outline border
SHIFT + CTRL +_ - Remove border
ALT + O + D – Conditional formatting

Columns and Rows

SHIFT + Spacebar – Highlight row
CTRL + Spacebar – Highlight column
SHIFT + ALT + → - Group rows/columns
SHIFT + ALT + ← - Ungroup rows/columns
CTRL + Minus sign – Delete selected cells
SHIFT + CTRL + Plus sign – Insert blank cells

Function Key Shortcuts

F1 – Excel help menu
F2 – Edit cell
F3 – Paste name
F4 – Anchor cells (in formula)
F5 – Go To (useful for lists)
F6 – Next pane in split mode
F7 – Spell check
F8 – Anchor a cell and use arrows to highlight an area
F9 – Recalculate workbook
F10 – Activate menu bar
F11 – New chart
F12 – Save As
SHIFT + F2 – Insert a comment
SHIFT + F3 – Insert function
SHIFT + F4 – Find next
SHIFT + F5 – Find
SHIFT + F6 – Previous pane
SHIFT + F8 – Add to selection
SHIFT + F9 – Calculate active sheet
SHIFT + F10 – Display shortcut menu
SHIFT + F11 – New worksheet
CTRL + F3 – Name a cell
CTRL + F4 – Close window
CTRL + F9 – Minimize window
CTRL + F10 – Maximize window
CTRL + F12 – Open file
ALT + F1 – Insert chart
ALT + F2 – Save As
ALT + F4 – Close program
ALT + F8 – Macro box
ALT + F11 – Visual Basic Editor window

Navigation

ALT + ↓ - Drop down menu
ALT + Tab – Switch between two last active windows (hold ALT down while tapping Tab to choose from all open windows)
CTRL + Pg Up/Dn – Switch worksheets
CTRL + Tab – Switch workbooks
CTRL + Arrow keys – Go to the end of contiguous range
SHIFT + Arrow keys – Select a cell range
CTRL + SHIFT + Arrow keys – Highlight contiguous range
CTRL + SHIFT + End – Highlight range
Home – Move to beginning of row
CTRL + Home – Move to cell A1
Enter – Move to cell below
SHIFT + Enter – Move to cell above
Tab – Move to the cell to the right; also to move in progressive fashion in any menu
SHIFT + Tab – Move to the cell to the left; also to move to in the reverse direction in any menu
Backspace – Delete cell and get inside the cell
F2 – Edit/highlight dependent cells
When inside cell:
ALT + Enter – Start new line in the same cell
SHIFT + Arrow keys – Highlight within cells
F4 – Anchor cells in a formula
F7 – Spell check
CTRL + F3 – Name cell
SHIFT + F3 – Display “Insert Function” box
ALT + “=” – Sum all cells above
CTRL + ‘ – Copy formula from cell above
SHIFT + CTRL + “ – Copy value from cell above
F9 – Recalculate all workbooks
ALT + T + U + T – Trace immediate precedents (display linking arrows)
ALT + T + U + D – Trace immediate dependents (display linking arrows)
ALT + T + U + A – Remove tracing arrows
CTRL + [ - Highlight precedent cells
CTRL + ] – Highlight dependent cells
SHIFT + CTRL + { – Highlight all precendents (direct and indirect)
SHIFT + CTRL + } – Highlight all dependents (direct and indirect)
F5 + Enter – Go back to original cell

Formulas:

=abs() – Returns the absolute value of a number
=sumproduct(array 1,array 2) –Multiplies corresponding components in the given arrays, and returns the sum of those products
=sumif(range, logical test) - Sums the values in a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent) that meet criteria that you specify.
=countif(array) - Counts the number of cells within a range that meet a single criterion that you specify
=Exact (value 1, value 2) – Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise
= trim(“text”) – Removes all spaces from text except for single spaces between words
=len(text) – Returns the number of characters in a text string
=upper() – Converts text to uppercase
=lower() – Converts text to lowercase
=proper() – Converts text to proper case
=vlookup(“What you are looking for”, “Where it might be located”, “If found, what column would you like it to reference”, “If the exact value is not found, do you want to look for the next closest thing?”) – Vlookup formula (useful for searching data in vertical orientation)
=hlookup(“What you are looking for”, “Where it might be located”, “If found, what row would you like it to reference”, “If the exact value is not found, do you want to look for the next closest thing?”) – Hlookup formula (useful for searching data in horizontal orientation)
=concatenate() or & - Concatenate (bring 2 items together)
=if(logical test,”value/text if true”, “value/text if false”)