I've been teaching Excel for over 15 years, and I'm always on the search for tips and features that will help save time. Here are 17 of my favorites that are hiding in plain sight. These tips will help you navigate and utilize Excel more efficiently.
Video Tutorial
1. Right-click Tab Navigation
If you have a workbook with a lot of sheets in it, it can take a lot of work to scroll back and forth using the sheet navigation arrows at the bottom left corner of the window. Instead right-click on the arrows . This action opens a list of all sheets, allowing you to quickly find and double-click to jump to the desired sheet.
2. Sheet Number in Status Bar
Right-click on the status bar and click the option to add the sheet number to the sheets. This helps you and your coworkers know which sheet number you're on, making communication easier.
3. Status Bar Calculations
The status bar is useful for quick calculations. Select a range, and you'll see stats about the cells in that selection. Right-click the status bar to see additional metrics and add them as needed.
4. Zoom to Fit Selection
If you want to focus on a specific range in your spreadsheet, select that range, click on the zoom slider in the bottom right corner and double-click Fit Selection. To zoom back out, double-click 100%.
5. Expand Formula Bar
For long formulas, click the expand button on the formula bar. If the entire formula is still not visible, drag the bar down to expand it further. Collapse it by clicking the button again or using the keyboard shortcut Ctrl+Shift+U.
6. Autofit Columns and Rows
To autofit all columns or rows, click the Select All button in the top left corner, then double-click any column or row border to autofit all.
7. Name Box Range Selection
Use the Name Box to select a range quickly. For example, to select from B4 to E500, first select B4, then type E500 in the name box, hold Shift, and press Enter.
8. Zoom Out to See Named Ranges
Zoom out below 40% to see named ranges appear on the sheet. Undoubtedly, this will help with navigation and finding out where named ranges exist on the sheet.
9. Select Objects Tool
When working with multiple shapes, use the Select Objects tool (found under Find & Select on the Home tab) to draw around and select shapes easily. Remember to press Esc to return to normal mode.
10. Lock Drawing Mode
When drawing shapes, right-click and choose Lock Drawing Mode to keep adding the same shape without reselecting the tool each time. Press Esc to exit this mode.
11. Edit Mode and F2 Shortcut
Use F2 to toggle between Edit and Enter modes. This allows you to use arrow keys to navigate within a formula or select cells when editing a formula.
12. Fill Handle
Use the fill handle to copy formulas or data down a column. Place the cursor on the fill handle in the bottom right corner of a cell, and double-click to copy down the column.
The following post has more tips and tricks for the fill handle: Copy Dates & Fill Series of Numbers with the Fill Handle in Excel
13. Format Painter Lock Mode
Double-click the Format Painter button to lock it. You can then click any cells to apply the same formatting. Press Esc to exit lock mode.
14. Tiny Ribbon Buttons
The small buttons on the ribbon open detailed options. For example, clicking the button in the Font group opens the Format Cells window, where you can make more precise formatting changes.
15. Ctrl+Drag to Duplicate Sheets
Hold the Ctrl key, then drag a sheet tab to the right to create a duplicate. This also works with multiple sheets selected.
16. Double-Click to Hide Ribbon
Double-click any tab on the Ribbon to hide the Ribbon and gain more grid space. Click a tab to temporarily show the ribbon, then click back on the grid to hide it again. Double-click the tab again to bring it back permanently.
17. Keyboard Shortcuts in Screentips
And lastly, hover over ribbon buttons to see their keyboard shortcuts in screentips. For example, the shortcut to toggle filters on and off is Ctrl+Shift+L.
These features can significantly enhance your Excel efficiency and productivity. Which one was your favorite? Let us know in the comments below. If you enjoyed this post, consider sharing it with your colleagues and friends. Thanks for reading!
I’ve been intensively using Excel since it exists, after Visicalc Lotus 123 and others and knew most of those tools thank you for the reminder anyway
I have a spreadsheet that has 86 tabs and counting. I’ve been looking for an easy and faster way to scroll through the tabs. That was number one on this list, so thanks!
please tell me you’re not adding a new sheet each week/month!
if you are, then you should be recording your data on one sheet and using that to view your time-based data (I know, easy for me to say when I know nothing about what you’re doing)
if you’re not, then this macro may be useful, it produces a hyperlinked list of all your sheet tabs:
Sub SheetList()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For i = 1 To Sheets.Count
With ActiveCell.Cells(i, 1)
.NumberFormat = “@”
.Hyperlinks.Add Anchor:=.Cells(), Address:=””, SubAddress:=Sheets(i).Name & “!A1”, TextToDisplay:=Sheets(i).Name
End With
Next i
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
End Sub
enjoy
Superb contents
I use Excel constantly and only knew about half of these tips. I’ve just forwarded this post to my entire department and urged them to read it. Thank you!
PrecisionTechSolutions uncovers 17 amazing Excel features hiding in plain sight. Their detailed exploration reveals hidden tools that can enhance productivity and efficiency for all Excel [email protected]
(281)721-9422
102 E. Walker Road, Ste 102B League City, TX 77573