Situatie
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.
Solutie
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 (A, B, C, and so on) are replaced by the column names you assigned at the start.
This trick only works if your table contains more rows than can fit on your screen. Otherwise, the table headers are always on show, so there’s no need for the spreadsheet headers to repeat those labels.
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.
After pasting a picture in Microsoft Excel, right-click it and select “Place In Cell” to embed the image within the boundaries of the cell.
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 benefit of using the Microsoft Excel clipboard, rather than the Windows clipboard (Windows+V), is that it remains on show in the Excel window until you close it manually. On the other hand, the Windows clipboard disappears as soon as you click away from it.
By default in Excel, worksheet cells display formula results, while the formula bar shows the formula you used to get that result.
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.
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.
Click and drag the three dots next to the right of the worksheet tabs to create more space for their names. 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.
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.
Use the Status Bar for Quick Calculations
One of the simplest yet probably most under-used tools in Excel is the Status Bar, which sits in the bottom-right corner of the Excel window.
Specifically, when you select some data in your spreadsheet, the Status Bar provides calculations for instant analysis without requiring additional formulas.
In this example, the table is sorted by appearances in descending order, and I quickly want to see the total and average goals scored by anyone who has played 100 or more games.
While I could use formulas to generate this information, I don’t want the result to appear in the spreadsheet—my aim here is just to take a glance at the figures for a quick analysis.
If you can’t see these totals in your version of Excel after you select two or more cells containing data, right-click the Status Bar, and check the aggregation calculations you want to display from the six options—Average, Count, Numerical Count, Minimum, Maximum, and Sum.
Leave A Comment?