Most of us use the same handful of Excel commands every day, overlooking features designed to make our spreadsheets easier to manage. This weekend, explore five hidden gems that can change the way you work with Excel.
Let Excel write your formulas for you
A smarter way to solve repetitive tasks
You’ve probably never heard of Formula for example if you tend to use the desktop app because it is currently only available on Excel for the web. The good news is that Excel for the web can be used for free with a Microsoft account, so anyone can try it out.
If you’ve ever used Flash Fill to split names or combine text, Formula for example takes the idea one step further. Instead of simply filling in static results, it watches what you type and generates the underlying editable Excel formula needed to fill in the remaining rows. Because the results are formula-based, they are automatically updated if the source data changes. And if you data is formatted as an Excel tableThe formula will also auto-populate when you add new rows.
One of my favorite things about Formula for Example is that you can inspect the formula it creates and learn how Excel solved the problem. It’s a great way to discover functions without having to work out the syntax yourself.
If you accidentally reject a Formula suggestion, for example, Excel for the web may not offer it again right away. If that happens, refreshing the browser usually brings up the suggestion again.
Find any sheet, chart or graph in seconds
Manager big books It can quickly become a tedious game of clicking through dozens of identical-looking tabs. He Navigation panel—accessed through the View tab in Excel for Microsoft 365 on Windows and Mac, as well as in Excel for the web, serves as a searchable directory for the important items in your file.
Yo use the navigation panel every time I open a workbook with more than a handful of sheets because it’s usually much faster than clicking through tabs manually. In addition to displaying sheet names, it indexes tables, charts, pivot tables, images, and named ranges, so you can discover and jump to parts of a workbook that you may not even remember were there.
By typing a few letters in the search box, a large book is reduced to the relevant components. You can use it to find misplaced charts, locate hidden slicers, rename confusing objects, or remove unwanted items right from the dashboard without searching in the spreadsheet or ribbon.
- SW
-
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, 1TB of OneDrive storage, and more.
Select exactly the cells you need in one click
The Fastest Way to Audit Messy Spreadsheets
We all inherit a messy spreadsheet and spend more time than we’d like searching for formulas, hard-coded values, errors, or hidden settings. However, instead of manually scanning thousands of cells, Go to special allows you to instantly select specific types of cells in your worksheet.
Available by pressing F5 > Alt+S or going to Home > Search and select > Go to special offerThis tool highlights cells based on what they contain. These are some of my favorite ways to use Go to Special when auditing a spreadsheet:
- Blanks: Quickly find missing data that needs to be filled in.
- Formulas: Select formula cells to see how a spreadsheet calculates its results.
- Constants: Identify manually entered values that may have accidentally replaced formulas.
- Errors: Highlight cells that contain errors so they can be reviewed and corrected.
- Data validation: Locate the cells that contain validation rules that would otherwise be easy to miss in a large spreadsheet.
Many online tutorials recommend using Go to Special to delete blank rows, but this can delete good data. A row with nine full cells and one blank cell will still be selected. Instead, use a safer method with filters, an auxiliary column and COUNT BLANK, or add a VBA macro to your quick access toolbar to remove empty rows with a single click.
Get instant charts and visualizations without menus
Preview charts, formats and totals before confirming
Building data visualizations It often feels like trial and error, requiring searching through ribbon tabs to find the right layout. Quick analysis solve this.
When you select a data range, press Ctrl+Q or click the small icon that appears next to your selection. The pop-up window allows you to preview charts, conditional formatting, totals, and sparklines before applying them. It’s especially useful when you’re exploring unknown data and you’re not yet sure which visualization or summary will best communicate it. Simply hover over an option to see how it will look in your data before confirming.
See how Excel calculates complex formulas step by step
X-ray vision for nested functions and broken calculations
Staring at a long nested formula that someone else wrote can be overwhelming, especially when all you see is an error message or an incorrect end result. Evaluate formula allows you to draw the curtain and See how Excel processes the calculation. one step at a time. It’s also a great way to understand formulas you didn’t write, because you can see how Excel evaluates each segment before arriving at the final result.
You can find it by selecting a formula cell and going to Formulas > Evaluate formula. Click Assess repeatedly to watch Excel work through the formula one section at a time, replacing the completed parts with your calculated results. Even with complex formulas that require multiple steps, the process shows exactly how Excel reached its conclusion and, if something is wrong, where the logic failed.
Take the next step with your spreadsheets
Exploring overlooked features is one of the easiest ways to make Excel seem faster, simpler, and more capable. Once you’ve tried them, keep the momentum going with Excel projects from last weekend.where you will create a mini data dashboard with a single function, create an offline password security checker and create a digital dice roll.
