When I worked as a data processor, my coworkers were always impressed by how quickly I could get my Excel work done. Many years of trial and error (and sometimes accidentally stumbling upon tools I didn’t know existed!) led me to my favorite methods for speeding up my workflow.
Use the Name Box for Navigation
While Microsoft Word uses bookmarks as waypoints in a Word document, Excel follows the same principle but calls them “names,” accessed through the name box in the top-left corner of your workbook. Every cell already has its own bookmark—or name—which you can see by selecting any cell and looking at the name box.
To jump to a cell within the active worksheet, type the column-row reference, such as E7, into the name box and press Enter. This is particularly handy if you have a large spreadsheet, saving you from scrolling up, down, left, and right each time you want to navigate to a distant location within a worksheet.
If you have other active tabs in your workbook, you can jump to any cell within those worksheets by typing the tab name, followed by an exclamation mark, and then the cell reference.
For example, typing:
Sheet2!G5
into the name box would take me to Sheet 2, cell G5.
What’s more, while cells already have their own names, you can change what they’re called by selecting the cell and typing a new name in the name box. You can also do this for a range of cells. For example, you might have an important data range in your large workbook that you need to view often. Giving it a memorable name makes it easier to jump to at any point.
As soon you name some data, a drop-down arrow appears next to the name box. Click this arrow to see all the named items in your worksheet, saving you from having to remember them.
Excel doesn’t like spaces in names, which is why I used an underscore (_) in the example above. Alternatively, you could type the name without a space, like “TotalPrices”.
Finally, the process for naming formatted Excel tables is slightly different—you have to use Excel’s table naming tool instead. Don’t worry, though—it’s just as straightforward. Select any cell within one of those tables, and type the name into the “Table Name” field in the Properties group of the Table Design Tab.
Although the process differs slightly for naming a formatted table, the steps you need to take to navigate to it are the same as jumping to a cell—simply type the table name in the name box, and press Enter, or click the name box drop-down menu to see a list of all named items.
The ability to freeze panes in Excel is one of the best time-saving tools of them all. Keeping important rows or columns, like the headings, visible while you scroll through large spreadsheets saves you from having to constantly scroll back up or across to remind yourself of what data you’re looking at.
In other words, freezing panes in Excel keeps your data in context, so you always know exactly what the data represents in an instant.
To see the freezing options, click “Freeze Panes” in the View tab.
Click “Freeze Top Row” or “Freeze First Column” to lock either the first row or column. Just remember that clicking one of these two commands deactivates the other.
On the other hand, you can freeze the first few columns or rows (if, for example, you have more than one header column or row) by selecting the first column or row you don’t want to freeze and clicking “Freeze Panes.” So, if you want to freeze rows 1 and 2, select row 3 and click “Freeze Panes.”
Finally, to freeze rows and columns together, click the highest and left-most cell you don’t want to freeze, and click “Freeze Panes.” In this example, I want to freeze row 1 and column A, so I’ll select cell B2, and click “Freeze Panes.”
To unfreeze any frozen panes in your worksheet, click Freeze Panes > Unfreeze Panes. Pressing Ctrl+Z (undo) will not cancel the freezing action.
Excel’s data forecasting tool lets you predict future trends based on historical data in just a few clicks, saving you from having to make those forecasts manually.
In this example, I am tracking the growth of my eucalyptus tree, and I want Excel to forecast the growth for the next few months.
To do this, I will select the range of cells containing the dates and the existing data, and click “Forecast Sheet” in the Data tab.
The Create Forecast Worksheet dialog box shows you a preview forecast. You can also change the end date using the field beneath the chart, and click “Options” to refine how the forecast is generated, such as by setting a confidence interval or choosing whether to automatically include seasonality within the forecast.
After you click “Create,” Excel opens a new sheet containing the forecasted data in table and chart form.
Excel is infamous for offering so many tools that, sometimes, finding the right ones can slow your workflow. If you find you’re often using the same buttons and commands, one way to quicken things up is to customize the Quick Access Toolbar (QAT).
Yes, you could learn and use power-user Excel keyboard shortcuts, but there are so many to learn that this could actually end up losing you time.
This is why adapting your QAT is the way forward. The QAT is in the top-left corner of your Excel window. If all you can see is the Excel logo and the name of your workbook, it means you don’t have the QAT activated.
To fix this, click the down arrow on the right-hand side of an open tab, and click “Show Quick Access Toolbar.”
The QAT will then appear next to the Excel logo, with some default buttons, like AutoSave and Save.
You can now tailor the QAT to contain the commands you use most often. For example, you can easily remove a button from the QAT by right-clicking the relevant icon and clicking “Remove From Quick Access Toolbar.”
Similarly, right-clicking an icon on the ribbon, and selecting “Add To Quick Access Toolbar” is a quick way to add a button to your QAT. Alternatively, click the “Customize Quick Access Toolbar” down arrow, and either choose one of the options available in the drop-down list, or click “More Commands” to see and add other actions.
As well as using these tools to speed up your workflow, making small adjustments to your Excel workbook—like compressing images, simplifying formulas, and limiting volatile functions—will keep your Excel file running smoothly and save it from grinding to a halt.
