Taking a few minutes to try out the right Excel tools this weekend could save you hours in the weeks to come. These five built-in features address common frustrations like scrolling through huge workbooks, cleaning up stuck data, and finding problem cells, so you can get right to them on Monday morning.
Use the name box to navigate large books
Stop scrolling through thousands of rows and columns
Most people who use Excel deal with name box at the top left of the grid only as a simple status indicator. It actually also works as a quick navigation bar that takes you through large workbooks.
Instead of dragging a small scroll bar thumbnail past the 50,000th row (and inevitably overshooting its target), you can jump directly to the destination cell. It’s also a big time saver when you need to select hundreds of rows or jump between important areas of your workbook (including different worksheets) using named ranges.
-
Click inside the name box to the left of the formula bar.
-
Write your target cell coordinateas
A650and press Get into. -
Select an entire region by typing a range reference as
D2:J100and pressing Get into. -
Click drop down arrow to jump directly to any existing table or named range.
The name box can also create named ranges. Select a cell or rangeclick name boxwrite a nameand press Get into. You can then return to that location from anywhere in the book via the drop-down menu.
Convert raw numbers to answers faster
Creating summary views and visual reports from scratch requires time and patience that you may not have. the incorporated Analyze data The feature scans your active sheet and generates summaries and visualizations. This tool is especially valuable when you inherit an unknown spreadsheet with thousands of rows and need to spot trends quickly.
-
Open your worksheet and click anywhere within your data table.
-
Depending on your version of Excel, go to the Home tab and click Analyze Data at the far right of the ribbon.
- Review the generated summaries, pivot tables and graphs.
-
Click Insert to add a suggested table or graph to your worksheet.
If you know what you’re looking for, type a message in plain English into the search box at the top of the dashboard, such as “top sales by region” or “average cost per month.”
- 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.
Use Ctrl+Shift+V to paste values without unwanted formatting
Pasting data into Excel using the standard Ctrl+V shortcut often results in mismatched fonts, strange background colors, and broken formatting. But Ctrl+Shift+V allows you to paste only the underlying values, so you don’t need to spend time reformatting the data.
This shortcut solves everyday annoyances like pasting data from a web page without bringing gray background boxes, pulling email lists without breaking your table’s font styles, or copying values from another workbook without generating unwanted calculations.
To see the difference, try pasting the same data twice: once normally and once using the values-only shortcut:
-
Select and copy your source text or data wearing Ctrl+C.
-
Click on your destination cell in your Excel spreadsheet.
-
First, press Ctrl+V to see how the data would normally be pasted into Excel. In my example, the names arrive in a smaller font, fit on two lines, and include extra spaces that I don’t want on my worksheet.
-
Now after pressing Ctrl+Z To undo the paste, press Ctrl+Shift+V to paste just the values and keep the existing formatting of your worksheet.
Use Go to Special to Find Problematic Cells on Your Sheet
Stop manually searching through thousands of cells
Searching for formulas, hardcoded values, or other specific cell types in a large spreadsheet can take much longer than it should. Go to special Instantly finds and selects those cells throughout your spreadsheet, turning a slow manual review into a quick audit.
Before opening Go to Special, select the area you want Excel to check. Click a single cell to search the entire worksheet, or highlight a specific range if you only want to search part of your data. So:
-
Press F5or go to Home > Search and select > Go to special offer.
-
If you pressed F5click Special at the bottom of the popup window.
-
Choose the specific category you want to isolate, such as formulas, constants, or cells that contain data validation.
when you click OKEvery matching cell on your sheet or within your selected range is selected simultaneously. You can then apply the same formatting to all of them, press Get into to move through each selected cell one by one, or enter a value once and press Ctrl+Enter to fill each selected cell at the same time.
Open a new window to view two parts of the same file
Compare distant sheets without having to constantly scroll back and forth
Cross-checking numbers between separate tabs or distant rows takes time and often results in errors. Open a second window of your active file using the New window The feature allows you to view two separate areas at the same time without opening separate copies of the book.
This setting transforms tasks such as comparing a summary sheet to the raw data it contains. You can keep the main summary tab visible while you scroll, filter, or check formula entries in the raw data sheets in the second window.
-
In the workbook you need to review, open the View eyelash.
-
Click New window to start a synchronized view of your document.
-
After opening the second window, return to the View tab, click Organize everythingand choose the layout that best suits your workflow:
- tiling Automatically arranges all open workbook windows on the screen.
- Horizontal stack the windows on top of each other.
- Vertical Place windows next to each other, which is often better for comparing data.
- Waterfall Layer windows so you can quickly switch between them.
-
When you edit your data in a window, you changes will be updated automatically in each open view.
-
When you’re done, close any additional windows. The remaining window will continue as the normal workbook view.
You are not limited to two views. If you are comparing multiple sheets or sections at once, click New window again from the View to create another synchronized view.
Continue Optimizing Your Excel Workflow
Once you’ve put these time-saving Excel tips to the test, continue exploring with Last weekend’s roundup of little-known Excel features you may not have discovered it yet. Together, these tools can help you spend less time searching through spreadsheets and more time getting things done with Excel’s built-in functions.
