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 Save yourself some time 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." If you have lots of rows of data, rather than selecting them manually with your mouse, activate the top-left cell (in my case, cell A1), press Ctrl+Shift+Right to select the whole row, and then press Ctrl+Shift+Down until all your data is selected.
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.Copy and access multiple items at the same time Work with data more intelligently 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.Related The Microsoft Office Clipboard Can Do More Than You Think Copy more than one item simultaneously.
Posts 2 By Tony Phillips 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.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 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.
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.View all formulas used in your spreadsheet Check your work By default in Excel, worksheet cells display formula results, while the formula bar shows the formula you used to get that result.
Related The Beginner’s Guide to Excel’s Formulas and Functions Everything you need to know about Excel's engine room.Posts 2 By Tony Phillips 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.
Microsoft Office 2024 Gain permanent access to Word, Excel, PowerPoint, and OneNote with the Office Home 2024 bundle.It isn't a one-to-one replacement for a Microsoft 365 subscription, as it lacks some apps and does not include feature updates, but it's a one-time purchase, so it's ideal for those who just need the basics.$150 at Microsoft $150 at Amazon Expand Collapse See all worksheets tabs with a right click Hide nothing 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.
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.
Related Why You Should Always Rename Worksheets in Excel Aid navigation and accessibility.Posts 1 By Tony Phillips Use the status bar for quick calculations It's there for a reason! 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.Related How to Customize and Use the Status Bar in Excel The status bar at the bottom of Excel's window can tell you all kinds of interesting information.
Posts Specifically, when you select some data in your spreadsheet, the Status Bar provides calculations for instant analysis without requiring additional formulas.Subscribe to the newsletter for practical Excel and spreadsheet tips Unlock deeper Excel mastery - subscribe to the newsletter for more practical tips, shortcuts, and real-world spreadsheet workflows that sharpen your formula checks, data handling, and layout techniques so you can work with confidence.Get Updates By subscribing, you agree to receive newsletter and marketing emails, and accept our Terms of Use and Privacy Policy.
You can unsubscribe anytime.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.
So, as soon as I select the goal totals for the relevant players, the average, count, and sum of the selected cells appear in the Status Bar.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.What's more, I can click any of these calculations in the Status Bar to copy the values to my clipboard, ready to be pasted elsewhere as necessary.
You're becoming an Excel master! If you're keen to save time when using Microsoft Excel, as well as using the hacks I've shared with you in this guide, make the most of the name box to speed up navigation, and personalize the Quick Access Toolbar to perform your most-used commands in an instant.
Read More
