Hey, friends! In my last post, I shared with you my distaste for jumping from keyboard to mouse to keyboard while working on the computer. This evolved into my love of keyboard shortcuts. I’ve already shared about half of my favorite shortcuts, so today–as promised–here’s the other half! If you haven’t already checked out Part 1 of these awesome shortcuts, check it out here.
When you start working with pivot tables, you’ll learn all about refreshing your data. If you have several pivot tables going at once, you’ll usually want to refresh all of them simultaneously. The best way to do this is use Ctrl + Alt + F5.
Some of you are probably looking at this thinking, “Save? You really need a shortcut to save your file?” If you’re thinking that, then I think it’s safe to assume you’re much smarter than I am, and you’ve never lost hours of work simply because you go so caught up in all of the amazing formulating and pivot-tabling and whatnot that you forgot to actually save your work… and lo and behold, that would, of course, be the day that Excel decided inevitably to crash…
For some reason, I began saving my work a lot more frequently when I began using the Ctrl + S shortcut. I can only recommend that you do the same!
Along similar lines, sometimes we want to “Save As” to save an existing workbook under a new name. The shortcut to do this is as simple as hitting F12.
Create Chart in New Tab
This one is pretty cool—at least I think so. Let’s say you have a summary table that you want to display as a chart or graph. Click anywhere within that table to select a single cell, then press F11. All of a sudden, your data is displayed in a beautiful bar graph in its own tab! You can customize your graph from there.
Edit Cell Contents
Don’t you hate it when you tab out of a cell, only to realize that you have a typo or need to make some change? Use your arrow keys (or Shift + Tab, if you tabbed out of the previous cell) to navigate back to the cell that requires updates. Once you’ve selected the appropriate cell, hit F2. This has the same effect as double-clicking in a cell and allows you to edit the cell contents—all without touching your mouse!
Change Cell Reference Type
The F4 key actually plays a variety of shortcut roles in Excel, but the reason I primarily use it is to change the cell reference type from “relative” to “absolute” or a mixed version. To use F4 for this purpose, you must have your cell contents available for editing—which is another great time to use F2, our Shortcut #21! Once your cursor is blinking within your cell contents, use your arrow keys to navigate over to the cell reference you need to change. When your cursor is within the cell reference, hit F4 to cycle through the absolute/relative/mixed options until you reach the one you need.
Add Line Break in Cell
There are times when you may need to break your text or formula into multiple lines within a single cell. For example, perhaps you are adding a long block of text that overruns the width of your cell. Or, maybe you have a complex formula combination and you want to break it up so that it’s easier to view in the formula bar. This is where Alt + Enter comes into play! Alt + Enter inserts a new line, or line break, into your cell contents.
In Shortcut #14 from Part 1, I told you that I sometimes use Ctrl + Shift + ↓ followed by Ctrl + Shift + → to select an entire data range. I also mentioned that there was an even faster shortcut to accomplish this same thing. This is that shortcut! I love it! Ctrl + * selects an entire data range, no matter how large or small. To use it, select any cell within the data range, hit Ctrl + *, and your entire data range is quickly and efficiently selected!
Open New Workbook
When you’re working in one workbook, use Ctrl + N to open a new workbook.
Select Entire Worksheet
To select all contents within a single worksheet, use Ctrl + A (“A” for “all”!).
Move between Worksheets
To navigate back and forth between worksheets (tabs) within a workbook (a single Excel file), use Ctrl + Page Up and Ctrl + Page Down.
Move between Open Workbooks
To navigate between open workbooks (when you have two or more Excel files open), use Ctrl + Shift + Page Up and Ctrl + Shift + Page Down.
Collapse or Expand the Ribbon
The “ribbon” in Microsoft applications is the toolbar that appears at the top of Word, Excel, Outlook, etc., that contains the various tools available for that particular application. Most of the time, you probably prefer to keep the ribbon expanded so that it’s easily viewable. From time to time, however, it can be beneficial to collapse the ribbon to allow for more viewing space for your project. I do this most often when working from my laptop, which already has limited real estate. Ctrl + F1 allows me to quickly toggle the ribbon between collapsed and expanded. Plus, the more shortcuts you learn, the less often you’ll need the ribbon!
Open Format Dialog Box
You guys, I LOVE this one! Whether I’m formatting numbers, borders, or colors, this one has become a real time-saver. I only recently stumbled upon it–I wish I had found it years ago! Ctrl + 1 opens the “Format Cells” dialog box to allow you to quickly modify the format of your selected cells.
This is another one I use frequently, and it was also a game-changer for me when I discovered it. Let’s say you have a dataset, and you need to enter the same piece of data on each row in one column of your dataset. For example, maybe you need to enter a single date that can’t be formulated based on any other items in that data. Enter that date (or whatever type of data you want to repeat) on the top row of that column (excluding the header row). Select the cell with the new data, use Ctrl + Shift + ↓ (Shortcut #14 again!) to select the entire column, then use Ctrl + D to fill the entire selected column with that single piece of data that you only entered once. It’s like magic!
Bonus! Create an Array Formula
We will touch on array formulas in a different post, so I won’t go into a lot of detail here. Suffice it to say that learning even the basics of array formulas will open the world of data analysis far beyond what is able to be completed without them. To create an array formula, all you need is the magical combination of Ctrl + Shift + Enter. If you’re curious about array formulas and want to learn more in the meantime, visit Microsoft’s support page on the topic here.
That’s a Wrap!
Well, folks, I hope you enjoyed learning some of my favorite shortcuts! If you’ll begin using these, they will quickly become part of your muscle memory as you navigate through your spreadsheets. You’ll be amazed how much time using these shortcuts can save you!
What are your favorite Excel keyboard shortcuts? Which ones did I miss? Please post them in the comments below! I always love to learn new time-saving tricks.