A few minutes spent trying out the right Excel tools this weekend could save you hours in the weeks to come. These five built-in features tackle the common frustrations of scrolling through huge workbooks, cleaning up pasted data, and finding problematic cells so you can get straight to work on Monday morning.
Use the Name box to navigate large workbooks
Stop scrolling through thousands of rows and columns
Most people who use Excel Name box just at the top left of the grid as a simple status indicator. Essentially, it acts as a quick navigation bar that takes you between large workbooks.
Instead of scrolling through the 50,000 row of the little scrollbar thumbnail and inevitably overshooting your target, you can go directly to the target cell. It’s also a major time saver when you need to select hundreds of rows or navigate between important areas of your workbook, including different worksheets, using named ranges.
-
Click inside Name box to the left of the formula bar.
-
Write your own target cell coordinateas
A650and press Enter it. -
select the entire region by typing a range reference as
D2:J100and by pressing Enter it. -
click on it drop arrow jump directly to any existing table or named range.
Can also create a name box named ranges. choose a cell or rangeclick on it Name boxwrite a nameand press Enter it. You can then return to that place from anywhere in the workbook through the drop-down menu.
Convert raw numbers to faster answers
Creating summary views and visual reports from scratch requires time and patience on your part. Internal Analyze the data 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 quickly identify trends.
-
Open your worksheet and click anywhere inside it data table.
-
Depending on your version of Excel, go to the Home tab and click the Analyze Data button on the far right side of the ribbon.
- View generated summaries, Pivot Tables and charts.
-
click on it Enter it Click the button to add a suggested table or chart to your worksheet.
If you know what you’re looking for, type a plain English query like “most sales by region” or “average price by month” into the search box at the top of the panel.
- 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. includes
Use Ctrl+Shift+V to paste values without unwanted formatting
Pasting data into Excel using the standard Ctrl+V shortcut often pulls mismatched fonts, strange background colors, and garbled formatting. But Ctrl+Shift+V allows you to paste only key values, so you don’t need to spend time formatting data.
This shortcut eliminates everyday hassles like pasting data from a web page without bringing up gray background boxes, extracting lists from emails without messing up your spreadsheet’s font styles, or copying values from another workbook without causing unwanted calculations.
To see the difference, try pasting the same data twice: once normally and once using the shortcut for values only:
-
Select and copy source text or data using Ctrl+C.
-
press destination cell In your Excel worksheet.
-
Press first Ctrl+V to see how the data would normally be pasted into Excel. In my example, the names come in a smaller font, wrap to two lines, and include extra spaces that I don’t want on my worksheet.
-
Now, after pressing Ctrl+Z press to cancel the paste Ctrl+Shift+V just to paste the values and keep the existing format of your worksheet.
Use Special Go to to find problem cells in your sheet
Stop manually hunting thousands of cells
Searching a large worksheet for formulas, hard-coded values, or other special cell types can take longer than necessary. Go specifically instantly finds and selects those cells throughout your spreadsheet, slowly turning a manual audit into a quick audit.
Select the field you want Excel to check before opening the custom Go to. Click a single cell to search the entire worksheet, or highlight a specific range if you want to search just a portion of your data. Then:
-
press F5or go Go to Home > Find & Select > Special.
-
Bassan F5click Special at the bottom of the pop-up window.
-
Select custom category You want to isolate cells that contain formulas, constants, or data validation.
When you click OKeach matching cell on your sheet or range you selected is selected at once. You can then apply the same formatting to all of them, click Enter it to move in each selected cell one by one or enter the value once and press Ctrl+Enter to fill each selected cell at once.
Open a new window to view two parts of the same file
Compare sheets without constantly scrolling back and forth
Cross-checking numbers between individual tabs or remote rows is time-consuming and often error-prone. open a second window of your active file using New Window feature allows you to view two separate areas at the same time without opening separate copies of the workbook.
This setup replaces tasks like comparing the summary sheet with the raw data behind it. In the second window, you can keep your main summary tab visible while scrolling, filtering, or inspecting formula entries in the raw data sheets.
-
You need to review in the workbook, open it Look badge.
-
press New Window to enable a synchronized view of your document.
-
After opening the second window, go back Look icon, click Organize it alland choose the most appropriate layout for your workflow:
- Tiled automatically arranges all open workbook windows on your screen.
- Horizontal stacks windows on top of each other.
- Vertical places the windows side by side, which is usually best for comparing data.
- Cascade Layers windows so you can switch between them quickly.
-
When you edit your data in one window, your changes will be updated automatically per open view.
-
when you finish close additional windows. The rest of the window will continue as a normal workbook view.
You are not limited to two views. If you are comparing multiple sheets or sections, click New Window still Look tab to create another synchronized view.
Continue to optimize your Excel workflow
After you’ve tried these time-saving Excel tips, keep exploring A roundup of lesser-known Excel features from last weekend you haven’t discovered 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.
