I had used Excel for years before I stumbled across most of these features. Like many people, I thought I had the application pretty well mastered—shortcuts, formulas, filters, even pivot tables, not to mention the essential Excel functions every beginner should know. But it turns out Excel has way more to offer than what you’ll find in most tutorials.
Chances are you haven’t used many of these either, even if you’d consider yourself an Excel pro. They’re tucked away in menus most people never touch, which is why they slip under the radar. The good news is that you don’t need advanced skills to take advantage of them. Some of these features are just a couple of clicks away, yet they can save you hours once you know where to look.
7
Watch Window
Keep important cells in sight without scrolling
I used to spend way too much time clicking back and forth between sheets, trying to see how a single change in one cell affected multiple formulas. It’s so frustrating, especially when you also have to check changes across different workbooks.
That’s exactly what Excel’s Watch Window is for. It lets you keep certain cells in view, no matter which sheet or workbook you’re in, so that you can track key formulas without endless scrolling.
You’ll find it under the Formulas tab, in the Formula Auditing group. Once you open it, just hit Add Watch and select the cells you want to monitor. The pop-up will show you the sheet name, cell reference, current value, and even the formula for each cell you’re watching.
By default, it floats on your sheet, but you can dock it under the ribbon by double-clicking on the title bar of the Watch Window. Or, if you’d rather keep it separate, just drag it out from under the ribbon and resize it however you like. You’ll find the expander if you hover in the bottom right corner of the window.
Whichever way you keep the window on your screen, you’ll have a constant dashboard for all your important numbers, so you don’t have to jump between tabs anymore.
Create live snapshots of your data
Have you ever considered a better way than copying and pasting as a linked picture to keep a clean, always-updating snapshot of your data? The answer is Excel’s Camera Tool. It creates live pictures of your selected data ranges, meaning the image updates automatically whenever the source data changes.
To use it, you’ll first need to add it to your Quick Access Toolbar:
- Go to File > Options > Quick Access Toolbar.
- Under Choose commands from: select All Commands.
- Scroll down, pick Camera, and add it.
The camera icon will now appear in your quick access toolbar, just before the title of your workbook. To use the tool, highlight the relevant cells, click the Camera Tool, and then click where you want your live picture to appear.
You can even copy the pasted picture into Word or PowerPoint, although it becomes static outside of Excel.
5
Custom Views
Instantly edit your spreadsheet for different audiences
If you’ve ever juggled multiple stakeholders, you know the struggle: marketing wants one set of columns and filters, finance needs another, and your boss just wants a clean version that prints neatly. Typically, that would mean either constantly reformatting the same sheet or creating separate versions for everyone.
With the Custom Views feature, you get one more option. It lets you save different setups—visible or hidden columns, applied filters, and even print settings—and switch between them instantly. So you won’t have to reformat the same sheet every time you meet a different stakeholder, and you won’t have to create separate versions.
You’ll find it under the View tab in the Workbook Views group. Click Custom Views > Add and give your setup a name. Usually, I’d save my unfiltered and plain sheet as a normal view first. Then, I’d add filters or formatting and save that setup. If I need another version, I just repeat the process with different filters or formatting.
Once you’ve saved all the views you need, it’s just a matter of launching Custom Views, picking the one you want, and clicking Show.
4
Goal Seek
Work backwards to find the exact input you need
Most of the time, we use Excel to calculate results from inputs. If you change the numbers on your sheet, you can see how the outcome changes. But what if you know the result you want and need Excel to tell you which input will get you there?
Then, you can use Goal Seek. It is Excel’s built-in reverse calculator, and you can find it under the Data tab by clicking What-If Analysis. Goal Seek allows you to set a target value for a formula and then automatically adjusts one input cell until the formula reaches that target.
For example, imagine you have a sheet with student scores and a final grade calculation, and you want to know what score a student needs on the final exam (e.g., history) to achieve a specific overall average. Instead of guessing and testing different values, Goal Seek can solve the problem, as long as it has to do with number predictions, in seconds. Simply select the formula cell that calculates the overall average, set the target value (e.g., 90%), and choose the input cell (the score for the final exam).
Once you confirm, Excel will calculate the required score, and you can accept or reject the result.
It’s perfect for finding the numbers you need to achieve a specific goal, such as the sales volume needed to break even, the grade required to pass a course, or the budget that will keep you on track.
3
Text to Columns
Split combined data into separate columns
Sometimes, you import data that ends up in a single column when it should be spread across several columns. When that happens, the Text to Columns feature becomes invaluable. It can take the combined text in each column and split it into multiple columns using delimiters, like commas, spaces, and tabs, or fixed widths.
Let’s imagine you have a column of locations that needs to be separated into regions and countries. Select your data column, go to Data > Text to Columns, and specify whether your data is delimited or fixed-width. Excel will show a preview of the split and let you refine the parameters before applying the changes. You can even assign data types to each new column so that dates remain dates and numbers remain numbers.
With just a few clicks, your combined, messy data will become clean, well-formatted data that you can now analyze.
2
Slicers for Tables
Add visual filters to any dataset
Slicers are often associated with PivotTables, but many people don’t realize that they also work with regular Excel tables. Once you convert a range into a table (Ctrl+T or Insert > Table), you can add slicers that provide the same intuitive filtering experience.
To do this, click anywhere inside your table so that the Table Design tab becomes visible, head there, and select Insert Slicer. You can then choose which columns you want to filter by.
Excel will generate slicer panels for each column, giving you buttons you can click to filter your table and refine your view instantly.
The slicers also highlight which filters are currently applied, making it much easier than traditional filtering options to understand and adjust your data at a glance.
1
Evaluate Formula
Step through your calculations to debug complex logic
Very few things are more frustrating than a formula returning the wrong result, and you can’t figure out why. You stare at nested functions, multiple references, and logical operators, trying to trace through the calculation mentally. It’s like trying to debug code without a debugger.
Excel’s Evaluate Formula lets you watch a formula be resolved one piece at a time. Select the cell with the formula, go to the Formulas tab, and click Evaluate Formula in the Formula Auditing group.
The dialog highlights the first part of the expression; click Evaluate to calculate that piece, then continue stepping through until you reach the final result. If the dialog gives the option to inspect a referenced expression, you can step into it (click Step In) to see how Excel evaluates nested pieces.
Each step shows exactly how Excel is interpreting your formula, which makes it much easier to spot where a formula is going wrong. Instead of breaking formulas apart or adding helper columns, you can watch the calculation happen in real-time.
Each of these seven features has the potential to transform the way you use Excel. They are not flashy add-ins or advanced tools reserved for experts, but practical tools built right into the program. Whether it is tracking key values without scrolling, turning messy data into clean tables, or stepping through a complex formula, these features can save you time and frustration every day.
Start with one that fits your workflow, and you may be surprised at how quickly it becomes part of your everyday flow.
