17 Amazing Excel Features Hiding In Plain Sight

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

Watch on YouTube & Subscribe to our Channel

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.

Right-click Tab Navigation

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.

Sheet Number in Status Bar

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.

Status Bar Calculations

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%.

Zoom to Fit Selection

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.

Expand Formula Bar

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.

Autofit Columns and Rows

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.

Name Box Range Selection

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.

Zoom Out to See Named Ranges

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.

Select Object Tool

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.

Lock Drawing 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.

Edit Mode and F2 Shortcut

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.

Fill handle

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.

Format Painter 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.

Ribbon buttons

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.

How to Copy Excel Worksheet - Ctrl Drag Shortcut

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.

Double-Click to Hide Ribbon

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.

Keyboard Shortcuts in Screentips

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!

2 comments

Your email address will not be published. Required fields are marked *

  • 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!

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter