Microsoft Excel: Thirteen Double-Click Tricks

Share this content

By David H. Ringstrom, CPA
The impact of single-clicking on a menu or object in Microsoft Excel is generally intuitive, but double-clicking sometimes reveals hidden shortcuts. In this article I'll discuss a baker's dozen of techniques where double-clicking can save you time and effort.
1. Skip the OK button: Unless you're clicking a checkbox, double-click on your choice within any dialog box in Excel to select that option and click OK simultaneously. For instance, press Ctrl-1 to display the Format Cells dialog box. Double-click on any format within the Number tab to apply the formatting and close the dialog box in one step. 
2. Maximize file windows: Double-click on the title bar of Excel's Open or Save As windows to see more files and folders. Double-click the title bar again to restore the default window size. You can double-click on Excel's title bar or a worksheet window as well.
3. Split a worksheet: Double-click either of the split handles in Figure 1 to view two sections of the same worksheet at the same time. Double-click on a split to remove it.
Figure 1: Double-click the slit buttons to view two sections of a worksheet simultaneously.
4. Fill handle: Just about everyone knows that you can grab the little black notch in the right-hand corner of a cell and drag down to fill a range of cells. Double-click this Fill Handle instead to copy data or formulas down until a blank cell is encountered in the adjacent column. In Excel 2010 and later, this feature copies down to the bottom of the contiguous block of cells, meaning it can skip past blanks in the immediately adjacent column.
5. Lock the Format Painter: Most users click once on the Format Painter to transfer formatting from one cell or drawing an object to another. To apply the same formatting to a number of non-contiguous cells, double-click the Format Painter and then format as many cells as you wish. Press Escape once you finish with the Format Painter.
6. Rename a worksheet tab: Although you can right-click on a worksheet tab and choose Rename, double-click the tab to immediately edit the name.
7. Adjust column widths: Double-click the right edge of any column on the worksheet frame to automatically resize that column to fit the width of the longest cell entry. Or, select two or more columns and double-click the right edge of a column to resize multiple columns at once.
8. Collapse the ribbon: Double-click any ribbon tab in Excel 2007 and later versions to collapse or expand the ribbon interface, thereby giving you more (or fewer) rows onscreen.
9. Navigate a worksheet: Double-click any border of a worksheet cell to move your cursor in that direction until it encounters a blank cell or the worksheet frame.
10. Edit a cell: Double-click any worksheet cell to edit its contents in place rather than moving your mouse to the formula bar.
11. Identify Precedent Cells: This technique requires that you first turn off the Allow Editing Directly in Cells option under the Advanced section of the Options window in Excel 2007 and later. In Excel 2003, this option appears on the Edit tab of Excel's Options window and is labeled Edit Directly in Cell. In either case, once you turn the option off, double-clicking a worksheet cell will highlight the precedent cells that a given formula refers to. 
12. Close Excel: Double-click the Excel logo in the top left-hand corner of any version in Excel (or the Office button in Excel 2007) to exit the program. You may be prompted to save any open workbooks.
13. Pivot Table Drill-Down: Double-click on any numeric value within a pivot table to create a new worksheet that reveals the underlying records that comprise that amount. 
Related articles:
Read more articles by David Ringstrom. 
About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at [email protected] or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.



Please login or register to join the discussion.

These were a lot of fun to try out!

Thanks! It was a fun article to write.

I like to know double click shortcut on pivot table without using mouse

There's not a built-in keyboard shortcut for this but you can roll your own. Click on a value cell within within the pivot table. Use the Macro Recorder on Excel's View tab to record a macro and create a keyboard shortcut of your choosing. All you'll record is the actual double-click action with your mouse and then immediately stop recording. When executed correctly you'll have a one line macro that you can play back with a keyboard shortcut of your choice in any pivot table. Record the macro to your Personal Macro workbook. If you're new to macros, you can watch a free recording of a one hour webinar that I presented to help you get up to speed.

Thanks for your tips, David. Years ago, I used to use a formula to balance bank accounts in excel. First, I will download the bank account into excel. Secondly, I will copy and paste the date and checks from check register on the next column. Third, I will enter a formula to read the check numbers and compare them to the check number on register. If the amount was the same, it will zero  out on the next column (as cleared checks.) Then, I will enter a formula for outstanding checks. I cannot remember what formulas, I used to use now. Could you give me any clues?  Lilian

Lilian, I'm sorry it's taken me so long to run across your comment. The SUMIF function would be ideal for performing the analysis that you describe. I'll try to write up the technique in an article soon, and when I do, I'll post a link here.

But beware the horrible result of accidentally double-clicking the edge of a cell. You are suddenly jumped elsewhere, usually the edge of the spreadsheet. This can be a nightmare if you were editing somewhere in the middle of a huge spread.

I'm just now catching up on some old comments. Yes, I agree that this can be disconcerting if you're next expecting it. It can be a useful technique though.