Spreadsheet Fundamentals 3: Formatting
"If a tree falls in a forest and no one is around to hear it, does it make a sound?" While formatting won't make your analysis any better, it makes your work "viewable." There are infinite ways you can create better formatting, but this lesson will only focus on the basic shortcuts you will need.
When you're copying a cell in Excel, you have tons of options on what to paste. A shortcut you should get extremely familiar with is CTRL + ALT + V in Windows and CMD + OPTION + V in Mac, which opens up a prompt for you to paste what you have copied (paste special). You can paste just the values (V) or the formula (F) or even link to the cell you copied. The way you can choose each of the prompts in here is to look for the underlined letter in the prompt and to type that accelerator key to make the selection.
Since this lesson is on formatting, the shortcut to get familiar with is CTRL + ALT + V —>T (on Windows) or CMD + OPTION + V —> T (on Mac), which pastes the exact format of the cell you copied. On a Mac there’s an even quicker shortcut which is CMD + SHIFT + V, which directly pastes the value you just copied. This includes the font color, font size, the cell color, if the cell is bolded, underlined, italicized, and more. There are a bunch of ways to copy and paste formats, but I fundamentally this is the best because it makes it easier to memorize all the other options for pasting.
This trick speeds up your formatting 100x in a spreadsheet because once you have a few cells with your desired formatting, you can copy that formatting to any other cell, instead of repeating the formatting process over from scratch.
Height Rows and Width
Changing the height and width of the rows and columns is also important for formatting because it helps with spacing when working in a grid. For example:
- Change height to automatically fit: ALT—>H—>O—>A (Windows)
- Change height to a specific size: ALT—>H—>O—>H (Windows)
- Change width to automatically fit: ALT—>H—>O—>I (Windows)
- Change width to a specific size: ALT—>H—>O—>W (Windows)
On a Mac, I have found that the fastest way to do this without a mouse is to press CMD + SHIFT + ? to access the search bar, and then start typing “Width” or “Height” and hitting enter.
Height Rows and Width
The last important item (in my opinion) is to change the color of the font and the cell.
Change color of font: ALT—>H—>F—>C (Windows) and then use ARROW KEYS and ENTER to select your color. This one is easy to remember because H stands for Home and FC stands for Font Color
Change color of the cell: ALT—>H—>H (Windows) and then use ARROW KEYS and ENTER to select your color. This one is slightly harder to remember, but it's used frequently enough that it will be memorized fairly quickly.
With these shortcuts, you can create nice headers and breaks in your spreadsheets! For example the below are just cells with different colors and widths to break the spreadsheet into different sections.
There's obviously unlimited possibilities in Excel, but I think these are the basics that can help you put together a well formatted spreadsheet very quickly. In my opinion, as you're learning, you want to lock down 80% of the most used formulas and functions and leave the remaining 20% of obscure formulas and functions for a later time.