Formatting & Navigation

Freezing & Splitting panes

Freeze Panes: Alt + W, F, F

Place the cursor one to the right and below where you want to freeze

Split Panes: Alt + W, S

Splits the worksheet into two panes, and you can use F6 to jump back and forth between, allowing you to create formulas that spill over on each
Can also use Shift + F6

Entering & Editing Data

Click F2 and you enter a cell

(This didn’t work?) Ctrl + N = go to the front of the formula / pressing home will take you to the front
Ctrl + arrows allow you to go element by element in the formula
Ctrl + shift allows you to highlight each element. Ctrl + down arrow highlights all in that direction
Press escape it will take you out and you will exit

Hit F2 to get in the cell and then when you’re trying to get out into your canvas and add more cells to the formula then you can add them to the existing formula

Alt + H, O, I (autofit column)

Alt + H, E gets you to “clear” options (see image below)

Shift + F2 = Add a comment to your specific cell. Esc gets you out
Fill to the right: Start on the column you want to copy over to the other columns (So Shift + arrow key), then you use Ctrl + R to copy those contents over. You can do this down by doing Ctrl + D. The image below shows the outcome. To do the down successful, you first need to fill right.

^Have to practice this for it to be intuitive

Columns & Rows

Adding rows & columns: Alt + I, R (row) or C (column)
or Ctrl Shift + after you highlight a row or column. So Ctrl spacebar for column and shift spacebar for row
To delete columns and rows: Alt + H, D, R or C

Alernatively, Ctrl - after highlighting either a column or a row

Alt + E, S, T does paste special for format. Ctrl 1 opens the cell editing box
Alt + H, O, I to auto fit the column
To paste special, you have to first Ctrl + c, then highlight the area you wish to past then E, S, T

Paste Special

You can convert positive numbers to negative numbers by highlighting the positive data you wish to convert and then use Alt + E, S, S and the last s = subtract, and it will convert your + into -

Similarly you can use the multiply function we previously discussed to multiply a set of positive numbers by a negative one

Ctrl + x = cut
Transpose, ships your vertical to horizontal and vice versa

Naming Cells

Can go to the name box and rename a cell (Ctrl F3). The downsides of that are it can confuse in different sheets.

Anchoring/Fixing Cells

F4 is the anchoring signal.
$C$ = absolute
$C1 = column
C$1 = row

Grouping

Alt + A + G + G = grouping selected rows or columns (so ctrl space to highlight columns and shift space to highlight rows)
Alt + A + U + U = ungrouping
Once grouped, you hide/unhide columns and rows by:

Hide: Alt + A + H
Unhide: Alt + A + J

Once grouped you can also use the Alt + shift + arrows keys to expand or contract your grouping.
You can unhide and hide non-grouped rows and columns through the Alt + H + O + U
If you wish to just group the column next to it: Highlight the column (ctrl + space) and press Alt + Shift + right arrow key
Group b/c you give visual signals and you won’t forget it’s hidden

You would use this when you want to add the same info to each sheet. To ungroup you would press Ctrl + PgUp

Meaning, anything I do in one sheet will be done in the other

Auditing Cells

Can do so by pressing F2
Ctrl [ or Ctrl ] will take you to precedent (former) and dependent cells (latter)
Alt + M + P connects the cells via arrows to the precedent cells.
Alt + M + D shows the dependent path
To take them all away: Alt + M + A + A
If in other sheets, the arrow would show you

Go To Special

Hit F5 then S, an that will take you to the special box below where you can choose what you would like to see highlighted
Why is this helpful:

To check for hard coded and formula success
And to quickly format (second image)
“Blanks” or “Comments” in a big sheet would be very useful

The status bar on the bottom can also give you information and you can right-click to change what it shows you

Conditional Formatting

Dynamic Headers

Quiz