Summary
Excel has been around for decades; somewhere along the way, Microsoft buried a handful of genuinely brilliant features so deep in menus that most people never stumble onto them. These aren’t obscure formulas or power-user shortcuts either. They’re built-in tools that could save you hours if you just knew where to look. Camera Tool Not on the ribbon, but three clicks from your toolbar Of everything Excel hides, the Camera tool is probably the most underused. It lets you take a live snapshot of any range of cells, formatting and all, and paste that snapshot anywhere else in your workbook, in another workbook, or even into Word or PowerPoint. The catch, however, is that unlike a regular screenshot, this image stays dynamically linked to the source data. Change a number in the original cells, and the snapshot updates automatically. This makes it perfect for building dashboards, since you can pull together snapshots of tables and charts scattered across different sheets and arrange them cleanly on one summary page. Using it is simple too: just select the range you want, click the camera icon, then click wherever you want the live image to appear. Microsoft never gave it a spot on the ribbon, so you do have to add it yourself. Head over to File, then Options, then Quick Access Toolbar, and switch the Choose commands from dropdown to Commands not in the ribbon or All commands. Scroll down to Camera, hit Add, then OK. Once you’re done, you’ll have a tiny camera icon sitting in your toolbar ready to go. Flash Fill Ctrl+E does what a formula would take five minutes to write Flash Fill is one of those features that feels like magic the first time it works. Instead of writing a formula to split, combine, or reformat text, you just type one or two examples of what you want, and Excel figures out the patterns and fills in the rest of the column for you. If it doesn’t kick in automatically, you can trigger it manually by pressing Ctrl + E on Windows or Command + E on Mac. You can also just head to the Data tab and click Flash Fill. Want to pull first names out of a column of full names, standardize phone numbers, or merge first and last names into one cell? Type the result for the first couple of rows, and Excel will often show you a preview of the rest before you even ask. Sometimes, Excel needs a little bit more context to understand the pattern, so if the first guess is off, filling in a few more examples will usually get it back on track. Analysis ToolPak It just isn’t turned on by default If you’ve ever needed to run a regression, an ANOVA, or generate a histogram and assumed you needed separate statistics software, Excel already has you covered. The Analysis ToolPak is a free add-in bundled with every copy of Excel that adds 19 data analysis tools directly to the Data tab. This includes everything from correlation and t-tests to moving averages and Fourier analysis. It just isn’t enabled by default, so most people never see it. To enable it, follow these steps:
- Head over to File, then Options, and click Add-ins.
- At the bottom, make sure the Manage box says Excel Add-ins and click Go.
- Check the box next to Analysis ToolPak, and click OK.
- A new Data Analysis button will appear on the far right of the Data tab. On a Mac, you’ll find the same checkbox under the Tools menu instead of File Options. Keep in mind that this add-in doesn’t work in the web version of Excel, so you’ll need the full desktop app if your school or work forces you into the browser version. Watch Window One floating panel tracks cells across sheets and workbooks Large spreadsheets often create an annoying problem: a formula’s result might depend on cells that are scrolled far off-screen or sitting on a completely different tab. This makes it tedious to check whether everything is up to order. The Watch Window solves this by letting you pin specific cells to a small floating panel that always shows their sheet, name, current value, and formula, no matter where you scroll. You’ll find it under the Formulas tab, in the Formula Auditing group, or by using the shortcut Alt, M, W. Select the cell you want to track, click Watch Window, then Add Watch, confirm the reference, and it starts monitoring immediately. It even works across multiple worksheets and separate open workbooks at once, so you can keep tabs on totals from different sheets in a single view. When you’re done, just select the entry and click Delete Watch to stop tracking it. For anyone auditing a complex model or double-checking calculations before sharing a file, this small panel beats endless scrolling and jumping to the home screen again and again. Excel has been hiding more than you’d think Buried in menus, not because they’re weak Each of these tools has technically been sitting inside Excel for years, sometimes over a decade. Microsoft just never bothered to put them where you’d naturally find them, which is exactly why so few people ever use them. Now, that could be because Excel is so full of tools that Microsoft decided that only the most frequently used ones get the limelight, and that’s fair. But for anyone using Excel beyond basic calculations and tracking, these can be a lifesaver.