The secret to mastering Excel isn’t about knowing the most complex formulas—it’s about choosing the quickest, most efficient tool for each task. Here are five features I use daily to cut down on tedious spreadsheet work, keep my data clean, and—most importantly—save time.
Go To Special: Instantly locate content-specific cells
Before using this feature, I wasted hours manually searching large datasets for specific cells based on their content, like blanks, formulas, or conditional formats. Go To Special eliminates this manual search, letting me find and select cells instantly. This means I can make deletions, updates, and checks in seconds.
To use this feature, first, select the range you want to search, or select any cell to search the whole worksheet. The fastest method is to press F5 (or Ctrl+G) to open the Go To Dialog box, then press Alt+S to launch Go To Special. Alternatively, you can use the ribbon: in the Home tab, click “Find And Select,” then click “Go To Special.”
Here’s what the Go To Special dialog box looks like:
These are the options I use the most to save hours every week:
|
Go To Special option |
What it does |
How and when I use it to save time |
|---|---|---|
|
Blanks |
Selects all blank cells in the worksheet or range |
I type NA into the first cell, then press Ctrl+Enter to apply the same value to the remaining selected blanks. I also sometimes color-fill the blank cells yellow to make them stand out. |
|
Conditional Formats |
Selects all cells in the worksheet or range with conditional formatting rules applied to them |
It’s easy to forget which cells have conditional formatting, so I use Go To Special to verify which ranges are actively using formatting rules and quickly delete or modify them. |
|
Constants |
Selects all cells in the worksheet or range containing hard-coded text, numbers, logicals, and errors, not formulas |
After selecting all constants, I color-fill them blue to clearly show where people can enter data. |
|
Errors |
Selects all cells in the worksheet or range containing a formula that returns an error |
After running the command, I press Enter to jump between error cells. Then, I can easily diagnose and fix them much more quickly. |
|
Notes |
Selects all cells in the worksheet or range with a note attached to them |
When someone shares an Excel workbook with me, the first thing I do is use Go To Special to identify all the cells containing notes, so I don’t miss any important details. |
Custom number formatting: Enhance data without disrupting values
The battle between presentation and calculation is a huge time sink, mainly because manually adding text to numbers can break formulas and lead to errors. Custom number formatting is my way to avoid this. It separates the number’s display from its true underlying value, meaning my data remains numerical while still appearing exactly as I want it.
To use custom number formatting, select the cells you want to format, and press Ctrl+1 to launch the Number Format dialog box. Alternatively, click the dialog box launcher button in the bottom-right corner of the Number group in the Home tab.
Then, open the “Number” tab, and select “Custom” from the Category list. The code goes in the Type box.
The key to custom number formatting is understanding that the code is divided into four sections, separated by semicolons (;):
POSITIVE;NEGATIVE;ZERO;TEXT
The first argument dictates how positive numbers are formatted, the second dictates how negative numbers are formatted, the third dictates how neutral numbers are formatted, and the fourth dictates how text is formatted.
For example, typing:
#.00;(#.00);-;"TXT"
means that positive numbers display with two decimal places, negative numbers display in parentheses, zeros as a dash, and all text as “TXT.” Remember: changing how the values appear doesn’t change their true underlying value.
If you leave out any arguments by adding a semicolon but not typing any code, the relevant cells in your spreadsheet appear blank.
Here are three ways I use custom number formatting to save reporting time and improve visual clarity:
|
Custom number format use case |
Custom number format code |
Time-saving outcome |
|---|---|---|
|
Add a text suffix (such as cm) |
0.0″cm” |
Displays the number with one decimal place, then adds the text “cm” after the number |
|
Apply conditional color changes |
[Green]0.0;[Red]0.0;;[Blue] |
Displays positive numbers in green with one decimal place, negative numbers in red with one decimal place, zeros as blanks, and text in blue |
|
Add directional arrows to percentages |
[Green]0.0%▲; [Red]0.0%▼ |
Displays increases as a green percentage value with an up arrow and decreases as a red percentage value with a down arrow |
Text within your custom code (like “cm”) must always be placed in double quotes (“…”).
PivotTable grouping: Categorize data
There are often several ways to perform the same task in Excel, but the key is finding the quickest one. For example, I know I can use functions like TEXT, IF, or XLOOKUP to convert data into categories like months, quarters, age brackets, or number ranges. However, the PivotTable grouping tool is a much more efficient way to complete this task without the need for formulas.
Once you’ve built your PivotTable, right-click a cell containing the value you want to group, and click “Group.”
Excel adjusts the Grouping dialog box based on the field’s data type:
|
Data type |
Action in the Grouping dialog box |
Time-saving result |
|---|---|---|
|
Dates |
Deselect “Days,” and choose the larger period you need (like “Months,” “Quarters,” or “Years”). |
The daily data is instantly converted into higher-level time series for trend analysis. |
|
Numbers |
Define the Starting At, Ending At, and By fields. For example, to group people aged 18 to 52 into five-year periods, type 15 into the “Starting At” field, 55 into the “Ending At” field, and 5 into the “By” field. |
The data is grouped numerically without the need for complex formulas. |
|
Text |
Before clicking “Group,” select the individual items in the PivotTable you want to combine by pressing Ctrl as you click them. |
Individual text items are grouped into high-level summary groups. |
Here’s how my PivotTable looks after grouping people by age.
Right-click fill handle dragging: Generate tailored sequences
While the standard left-click drag of the fill handle is well-known for extending simple sequences, the right-click drag of the fill handle lets me create customizable data patterns. This feature is the quickest way to populate date series and numerical patterns without using formulas.
The process is simple and requires only a starting value. Enter the first item of your series, such as a starting date or number. Then, select the cell and, using your right mouse button, click and drag the fill handle down the range you want to fill.
When you release your mouse, a context menu appears. There, choose the pattern you need.
6 Cool Right-Click Tricks in Microsoft Excel
Don’t let your right mouse button go to waste!
Here are some of my favorite patterns:
|
Action |
Menu option to select |
Time-saving benefit |
|---|---|---|
|
Skip weekends |
Fill Weekdays |
This fills a sequence of dates but skips Saturdays and Sundays—the perfect option for project planning and work scheduling. |
|
Create custom sequences |
Series |
This opens the Series dialog box, where you define the type of sequence, step value, and stop value, meaning you can create the sequence from a single value. |
|
Copy values |
Copy Cells |
Instantly copy the cell contents without copying any sequence or series—quicker than Paste Special. |
Custom lists: Automate repetitive text entry
Rather than wasting time manually entering the same sequence of text—like categories for my personal budget or family members for my Christmas card list—into an Excel spreadsheet, I create custom lists to eliminate this repetitive process.
First, press Alt > F > T to launch the Excel Options window. Then, in the left-hand menu, click “Advanced,” and scroll to the “General” section. There, click “Edit Custom Lists.”
Now, in the Custom List window, click “New List,” and in the List Entries field, type your items in the desired order, pressing Enter after each one.
Alternatively, if you’ve already typed your custom list into cells, activate the “Import List From Cells” field, select the cells containing the list, and click “Import.”
Once your list is complete, click “Add” to add it to the Custom Lists field, then click “OK.”
Now, type the first item in your sequence into any cell, and click and drag the fill handle to populate the entire list. If you drag the fill handle beyond the number of cells needed to accommodate the list, the sequence restarts.
You can use your custom list to sort data by selecting the data you want to sort, clicking “Sort” in the Data tab, and choosing your list from the Order drop-down menu. This lets you sort your data by priority or a non-alphabetical category.
If you enjoy the massive time savings from these methods, don’t stop there—there are many other ways to speed up your Excel workflow, like learning the keyboard shortcuts for executing complex tasks and navigating the interface, using the name box for quick navigation, and freezing panes to avoid overscrolling.
- OS
-
Windows, macOS, iPhone, iPad, Android
- Free trial
-
1 month
Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.