Mastering Microsoft Excel’s tools is a never-ending process, even for those who have used the program at work or at home for decades. With this in mind, here are some of my favorite Excel hacks I’ve picked up over the years that you can take away and use to speed up your spreadsheet workflow.
You Don’t Need to Freeze the First Row of a Table
If you’re working with an Excel sheet containing lots of rows of data, you might be tempted to freeze the first row via the View tab on the ribbon, so that when you scroll down, the headers remain visible. However, you don’t need to take this step if you format your data as an Excel table. To do this, first, make sure your data has column headers in the top row. Then, select your data, and in the Insert tab on the ribbon, click “Table.” Now, ensure the information in the Create Table dialog box is correct, and click “OK.” Finally, select any cell in the table, and scroll down. Notice that the column headers are replaced by the column names you assigned at the start.
Copy and Access Multiple Items at the Same Time
Most people know about the famous keyboard shortcuts for copying (Ctrl+C) and pasting (Ctrl+V) items in Excel. However, fewer people know that you can copy several items from various sources at the same time, and paste them into a spreadsheet with a single click.
Let’s say you’re creating a player profile spreadsheet for your favorite soccer team. For each player, you want their ID, a photograph, and a link to their online bio. Even though the player IDs are on a separate worksheet, their photographs are in a Microsoft Word document, and their online bio URLs are accessed in a web browser, you can go ahead and copy each item one by one.
Then, head back to the spreadsheet where you want the copied items to go, and click the Clipboard launcher icon in the Home tab on the ribbon to see the items you just copied in the Clipboard pane. Notice, also, how each item has an icon next to it to remind you of its original location. Now, select the cell where you want the first item to be pasted, and click the item in the Clipboard pane. Repeat this process for the other items you copied. You can then click “Clear All” in the Clipboard pane, before moving on to the next batch of items you want to copy and paste. The Microsoft Excel clipboard can hold up to 24 items. This means that when you copy a 25th item, it will replace the oldest item on the clipboard.
View All Formulas Used in Your Spreadsheet
By default in Excel, worksheet cells display formula results, while the formula bar shows the formula you used to get that result. However, you can also display the formulas in the cells themselves. To do this, press Ctrl+(the grave accent key, which is usually in the upper-left corner of the keyboard). When you use this keyboard shortcut, you can quickly see which cells contain formulas, understand how the calculations are made, and check for any errors. What's more, with this option activated, selecting a cell containing a formula will color the cells it references, making troubleshooting more straightforward. Press Ctrl+ again to return your spreadsheet to the default state.
See All Worksheets Tabs With a Right-Click
One of the more frustrating tasks in Excel is navigating the tab manager at the bottom of the screen, especially if you have many worksheets within your workbook. Indeed, clicking the tiny arrows in the bottom-left corner of the Excel window to scroll through the tabs takes up too much of your precious time. One way to speed up this process is to press Ctrl+Page Down to move to the next tab, or Ctrl+Page Up to jump back to the previous tab. However, my preferred method is to right-click the tab navigation arrows. Doing this launches the Activate window, and you can then double-click the sheet you want to navigate to (or use the arrow keys and press Enter). This tool is particularly useful if you’ve taken the time to give your worksheets short but descriptive names, rather than sticking with the default Sheet1, Sheet2, Sheet3, and so on.
For more information on solutions for running your businesses’ technology more efficiently, visit our website or contact Megan Meisner at mmeisner@launchpadonline.com or 813 448-7100 x210.
This was originally posted by HowtoGeek.