Most of us use the same handful of Excel commands every day, overlooking features designed to make managing our spreadsheets easier. Discover five hidden gems that can change the way you work with Excel this weekend.
Let Excel write your formulas for you
A smarter way to tackle repetitive tasks
You’ve probably never heard of it Formula with example if you’re inclined to use the desktop app, as it’s currently only available here Excel for the web. The good news is that Excel for the web is free to use with a Microsoft account, so anyone can try it out.
If you’ve ever used Flash Fill to separate names or merge text, Formula by example takes the idea a step further. Instead of just filling in static results, it tracks what you type and creates the basic, editable Excel formula needed to complete the rest of the rows. Because the results are provided by formulas, they update automatically when your source data changes. And if your the data is formatted as an Excel spreadsheetThe formula will be filled automatically when you add new rows.
One of my favorite things about Formula by Example is that you can inspect the formula it creates and learn how Excel solves the problem. It’s a great way to discover functions without having to work out the syntax yourself.
If you accidentally decline the Formula by Example suggestion, Excel for the web may not immediately suggest it again. If this happens, refreshing your browser will usually restore the offer.
Find any sheet, table or graph in seconds
Management large workbooks can quickly turn into a tedious game of clicking dozens of identical-looking icons. The Navigation panel– is obtained through Look In Excel for Microsoft 365 on Windows and Mac, as well as in Excel for the web, a tab serves as a searchable directory of important items in your file.
I Use the Navigation Pane every time you open a workbook with more than a handful of sheets, because it’s usually faster than clicking tabs manually. In addition to displaying sheet names, it indexes tables, charts, PivotTables, images, and named ranges, so you can discover and skip to parts of the workbook you might not remember were there.
Typing a few letters in the search box narrows the large workbook down to the relevant components. You can use it to find misplaced diagrams, find hidden cutters, rename confusing objects, or delete unwanted elements directly from the panel without having to hunt through a worksheet or ribbon.
- 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. includes
Select the cells you need with one click
The fastest way to check scattered tables
We’ve all inherited a messy spreadsheet and spent more time than we’d like looking for formulas, hard-coded values, errors, or hidden parameters. But instead of manually scanning thousands of cells, Go specifically allows you to instantly select specific cell types in your worksheet.
Available by clicking F5 > Alt+S or by going Go to Home > Find & Select > Specialthis tool highlights cells based on their contents. These are some of my favorite ways Use Custom Go to when checking a spreadsheet:
- Blanks: Quickly find missing information that needs to be completed.
- Formulas: Select the formula cells to check how the worksheet calculates the results.
- Constants: Identify manually entered values that may have accidentally replaced formulas.
- Errors: Highlight the cells with errors so they can be reviewed and corrected.
- Data Verification: find the cells that contain validation rules easy to miss in a large table.
Many online tutorials recommend using Custom Go to to remove empty rows, but this can remove good data. A row with nine filled cells and one empty cell will still be selected. Instead, use a safer method or with filters, auxiliary column and COUNTBLANK Add a VBA macro to your Quick Access Toolbar to delete empty rows with one click.
Get instant graphs and visualizations without menus
Review graphs, formats, and summaries before analyzing
Building a data visualization it often feels like trial and error, requiring you to dig through ribbon tabs to find the right layout. Quick analysis solves it.
When you select a range of data, press either Ctrl+Q or click the small icon that appears next to your selection. A pop-up window then lets you preview charts, conditional formatting, summaries, and glow lines before applying them. This is useful when you’re exploring unfamiliar information and aren’t yet sure which visualization or summary will convey it best. Simply hover over an option to see how it will appear in your data before making a recommendation.
See how to calculate Excel’s complex formulas step by step
X-ray vision for nested functions and perturbed computation
Looking at a long nested formula written by someone else can be overwhelming, especially when all you see is an error message or incorrect output. Evaluate the formula allows you to pull back the curtain and Follow Excel’s calculation process one step. It’s also a great way to understand formulas you haven’t written, because you can see how Excel evaluates each segment before arriving at the final result.
You can find it by selecting the formula cell and going here Formulas > Evaluate Formula. click Rate it repeatedly watching Excel work in a section with a formula, replacing completed parts with calculated results. Even with complex formulas that require multiple steps, the process shows exactly how Excel arrived at its conclusion and, if something goes wrong, where the logic breaks down.
Take the next step with your spreadsheets
Exploring forgotten features is one of the easiest ways to make Excel feel faster, simpler, and more efficient. Once you’ve tried them, keep the momentum going Excel projects last weekendhere you’ll build a single-function mini dashboard, create an offline password strength checker, and create a digital dice roll.
