The Basics Of Excel Formatting Shortcuts
Formatting is a common task in Excel. So what are some good formatting shortcuts to increase your speed?
First, memorize these shortcuts for opening up the Format Cells dialog box (shown below):
Format Cells Dialog Box (keyboard): Ctrl+1
Format Cells Dialog Box (hybrid): Right Click+F*
(*Note: this won’t work if you have copied something into the clipboard since at that point “F” will activate the paste Formulas option instead).
You can switch between tabs in the dialog box with your mouse or by typing the first letter for tab (e.g., “F” for Font).
Second, there are some common formats with easy shortcuts:
Bold (keyboard): Ctrl+B –or– Ctrl+2
Italics (keyboard): Ctrl+I –or–Ctrl+3
Underline (keyboard): Ctrl+U –or– Ctrl+4
If you want to copy existing formats from a cell (or range of cells), you have several formatting shortcut options:
First, use the Format Painter — it’s the icon below. Select the range with the desired format, then click the Format Painter icon, then click the target range. To paint multiple ranges with the formatting, double click on the icon (instead of single-clicking), then select each target one at a time — and then hit Escape when done.
Second, use Paste Special by copying the formats. Copy the range with the desired format, select the target range and then paste the formats by right clicking and typing “R” for Formatting (see below).
Paste Format (hybrid): Right Click+R
In order to paste into multiple sections, select the next range and hit F4 to repeat the action (hit Escape when done to “release” the clipboard and get rid of the blinking border around the copied range — as you would normally do when done pasting). Hitting F4 is faster than Right Click+R — however it’s still generally slower than Format Painter for multiple targets.
Read more about Paste Special Excel Shortcuts.
Make Your Own Formatting Shortcuts
For other formatting options that I use frequently, but have no quick default formatting shortcut, I put them on the QAT (Quick Access Toolbar) where they are (a) always visible (no matter what tab you’re on) and (b) accessible by using Alt shortcut sequences. This is a hack that allows you to create your own formatting shortcuts. See below for my QAT (which I modify from time to time depending on my project). Here it’s easy for me to format my cells yellow and place a border around them by clicking those options.
When you type Alt, the keyboard shortcuts appear on the QAT (as they do on the Ribbon) — see below. The numbers on the far left are easily accessible by the left hand. Since I like my numbers formatted with a comma and no decimals, I put those options on the far left of the QAT (comma and decimal reduction). That way, I can simply type Alt+1-2-2 to get the number formatting I want (if you know a faster way to achieve this formatting, please let me know). You can put whatever you want on the left side of the QAT to create some quick formatting shortcuts (or any other shortcuts, for that matter).
Those are some of my favorite formatting shortcuts and tips. I hope they make you faster at Excel.
Check out my Excel Shortcut System for a more comprehensive program for learning and implementing shortcut strategy.
What are your favorites, and do you agree or disagree?