You can spend years using Excel without realizing some of its biggest time-saving features are hiding in plain sight.Looking back, these are the six tools that would've saved me the most time, clicks, and frustration if I'd discovered them sooner.Open two windows for the same spreadsheet See multiple tabs at once Whenever I needed to compare information across two worksheets—or even between different parts of a large worksheet—I found myself constantly switching tabs or scrolling back and forth, only to lose my place by the time I got back.
It made working on complex workbooks far more difficult than necessary.Then I discovered Excel can open multiple windows for the same workbook.Open your master workbook, navigate to the View tab, and select New Window.
Excel creates a second live view of the same workbook, which you can drag to another monitor or snap to the side of your screen to view two worksheets side by side.This is different from the standard Split tool, which only divides a single sheet into panes.Crucially, these are not separate copies of your spreadsheet—they are simply two different lenses focused on the active file.
Any edits you make in one window apply instantly to the other, removing the risk of a version mismatch.You can open more than two windows for the same workbook by clicking New Window repeatedly.When you're finished, simply close whichever window you're using—there's no need to worry about which window is the "real" workbook.
Press F2 to edit formulas without breaking them Master your Arrow keys One of the biggest annoyances in Excel is editing formulas inside dialog boxes.When you try to move the cursor through a formula using an Arrow key to fix a typo, a cell reference is suddenly inserted instead.It's a small mistake, but it can make editing complex formulas surprisingly frustrating.
Pressing F2 puts the cell or input field into Edit mode, allowing you to move through the formula with the Arrow keys instead of selecting adjacent cells.When you do this, keep an eye on the bottom-left corner of the Excel window.The status bar will switch from "Enter" or "Point" mode to "Edit" mode.
This little keystroke works almost anywhere where you can edit cell references, ensuring you can navigate through your formula strings safely without accidentally changing your cell references.This quick habit makes it much easier to tweak complex formulas without accidentally breaking them.Use the Analyze Data tool for instant insights Let Excel spot the trends People often overlook Excel's native analysis tools, assuming they need Copilot or another premium AI tool.
As a result, they spend hours staring at a massive, newly imported dataset, unsure of where to begin or what trends to look for.Instead of manually experimenting with PivotTables and charts to understand your data, select your dataset, open the Data tab, and choose Analyze Data.In some versions of Excel, this button is located on the Home tab.
Excel analyzes your data and suggests insights in a sidebar that you can insert directly into your sheet with a single click.You can even type natural-language questions into the tool, like "What was the highest-selling category in Q3?" and it will instantly build a custom answer, complete with a chart or PivotTable where appropriate.Treat its recommendations as a starting point rather than a final answer, though—they're often surprisingly good, but you should still sense-check the results before sharing them.
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 Scrape web tables instantly with Power Query Stop copying and pasting from Wikipedia I used to copy tables from websites and spend ages fixing broken formatting, merged cells, and misaligned columns.It was a messy, frustrating process that usually required manually retyping numbers Excel insisted on treating as text.
You can skip the cleanup by copying the target website's URL, heading to the Data tab, and selecting From Web.Excel parses the page and opens a Navigator window displaying every structured data table it detected on that page.From there, you can see a clean preview of the information, select the specific table you want, and choose to load it immediately or open it inside the Power Query Editor to filter rows, remove columns, or otherwise clean the data before it hits your sheet.
This approach provides a repeatable connection that eliminates most of the cleanup work.Make your Power Query data update itself Automate your data refreshes Importing live data from the web is only useful if you're actually looking at the latest version.Forget to refresh the query, and you could easily end up working with outdated figures or delivering an inaccurate report to your team.
Once you pull external data into your spreadsheet using Power Query, you don't have to repeat the import process when the source web page changes.You can configure the query to refresh automatically whenever you open the workbook.To enable this, right-click your query in the Queries & Connections sidebar, select Properties, and check the box next to Refresh data when opening the file.
Once I enabled this in every workbook, I stopped worrying about whether I was looking at yesterday's figures.If you need near real-time data, open your query's Properties dialog and enable Refresh every to update it automatically at regular intervals, such as every 15 or 30 minutes.Link multiple data tables with Power Pivot Connect tables the smart way When your data is split across multiple tables or sheets—like a customer list on one tab and an order history list on another—the obvious fix is to build large lookup formulas to pull everything together.
The problem is that these long formula chains become difficult to maintain and can easily break if someone changes a lookup column or table structure.Power Pivot lets you skip the formulas altogether.First, you need to enable the Power Pivot COM add-in in Excel's Options menu (File > Options > Add-ins > COM Add-ins).
Now that the Power Pivot tab is on the ribbon, you can add the tables one-by-one to Excel's Data Model, open the Power Pivot window, switch to Diagram View, and create relationships between them based on a shared column.Finally, you can create a PivotTable from the Data Model to analyze your related tables together.Instead of filling thousands of lookup formulas down a worksheet, you create the relationship once and let Power Pivot handle the connections.
The result is a cleaner workbook that's easier to maintain and scales much better as your datasets grow.Keep exploring Excel's hidden tools For years, I assumed getting faster in Excel meant memorizing more formulas.In reality, learning the right built-in features made a much bigger difference.
While many of Excel's best tools are hiding in plain sight, others are hidden away from the ribbon, making them even easier to miss.The next time you're working in Excel, take a few minutes to explore the menus and right-click options—you never know which hidden feature will transform the way you work.
Read More
