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 Close You've probably never heard of Formula by Example if you tend to use the desktop app because it's currently only available in Excel for the web.
The good news is that Excel for the web is free to use with a Microsoft account, so anyone can give it a try.If you've ever used Flash Fill to split names or combine text, Formula by Example takes the idea a step further.Rather than just filling in static results, it watches what you type and generates the underlying, editable Excel formula needed to complete the remaining rows.
Because the results are powered by formulas, they update automatically if your source data changes.And if your data is formatted as an Excel table, the formula will also automatically fill down 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 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 by Example suggestion, Excel for the web might not offer it again straight away.If that happens, refreshing your browser usually brings the suggestion back.
Navigate giant workbooks without endless scrolling Find any sheet, table, or chart in seconds Close Managing large workbooks can quickly turn into a tedious game of clicking through dozens of identical-looking tabs.The Navigation Pane—accessed via 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 of the important elements in your file.I use the Navigation Pane whenever I open a workbook with more than a handful of sheets because it's usually much faster than clicking through tabs manually.
As well as showing sheet names, it indexes tables, charts, PivotTables, images, and named ranges, so you can discover and jump to parts of a workbook you might not even remember were there.Typing a few letters into the search box narrows a large workbook down to the relevant components.You can use it to find misplaced charts, locate hidden slicers, rename confusing objects, or remove unwanted items directly from the pane without hunting through the worksheet or ribbon.
Microsoft 365 Personal 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.$100 at Microsoft Expand Collapse Select exactly the cells you need in a single click The fastest way to audit messy spreadsheets Close We've all inherited a messy spreadsheet and spent longer 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 lets you instantly select specific types of cells across your worksheet.
Available by pressing F5 > Alt+S or by going to Home > Find & Select > Go To Special, this 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 completed.Formulas: Select formula cells to check how a worksheet is calculating its results.
Constants: Identify manually entered values that may have accidentally replaced formulas.Errors: Highlight cells containing errors so they can be reviewed and fixed.Data Validation: Locate cells containing validation rules that are otherwise easy to miss in a large spreadsheet.
Many online tutorials recommend using Go To Special to delete blank rows, but this can remove good data.A row with nine populated cells and one blank cell will still be selected.Instead, use a safer method with filters, a helper column, and COUNTBLANK, or add a VBA macro to your Quick Access Toolbar to remove empty rows with a single click.
Get instant charts and visualizations without the menus Preview charts, formatting, and totals before committing Close Building data visualizations often feels like trial and error, requiring you to dig through ribbon tabs to find the right layout.Quick Analysis solves this.When you select a range of data, either press Ctrl+Q or click the small icon that appears beside your selection.
The pop-up window then lets you preview charts, conditional formatting, totals, and sparklines before applying them.It's especially handy when you're exploring unfamiliar data and aren't yet sure which visualization or summary will communicate it best.Simply hover over an option to see how it'll look on your data before committing.
Watch Excel calculate complex formulas step by step X-ray vision for nested functions and broken calculations Close Staring at a long nested formula that someone else wrote can be overwhelming, especially when you only see an error message or an incorrect final result.Evaluate Formula lets you pull back the curtain and watch Excel process 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 heading to Formulas > Evaluate Formula.Click Evaluate repeatedly to watch Excel work through the formula one section at a time, replacing completed parts with their calculated results.Even with complex formulas that require several steps, the process shows exactly how Excel reached its conclusion and, if something is wrong, where the logic broke down.
Take the next step with your spreadsheets Exploring overlooked features is one of the easiest ways to make Excel feel faster, simpler, and more capable.Once you've tried them, keep the momentum going with last weekend's Excel projects, where you'll build a mini data dashboard with a single function, create an offline password strength checker, and make a digital dice roller.
Read More
