Excel Double Click Tricks
Table of Contents
Copying a Formula Down the Column
This the best one. I wasted long years of my life dragging formulas down the column until I discovered that all you had to do was double-click the corner of the formula cell.
I did’nt know whether to laugh or cry. It’s so easy. Hover your mouse over the lower right-hand corner of the cell and wait for the Fill Handle (black cross) to appear. Then double-click to copy the formula down the column.
So long as there are no blank cells in the previous column your formula copies all the way down to the end. The end of what? The end of the previous column, that’s why it’s a really bad idea to leave a blank column between your last data column and the column used for the formulas.
This double-click to copy down method is so much better than dragging that it’s usually easier to delete your blank column, do the copying and then insert the blank column afterwards.
Adjusting Column Widths
This method also works with row heights but it’s dealing with column width problems where it really comes into it’s own.
We are all familiar with the column width problem where you have the hash signs displayed in your cells. The last thing you want to do is start dragging the column wider, instead hover your mouse over the right-hand edge of the column until the double-headed arrow appears and then double-click to apply an automatic column width. This is based on the longest entry in the column.
Remember, work on the columns and not the cells containing the hash signs. If you have several columns to adjust, which is often the case, then select them first. Click on the first column letter and then drag across to select the others. Double-click between any two columns in your selection and all the columns are done in one go.
Using the Format Painter
Most of the MS Office applications have a Format Painter control which you can use to copy all the formatting from one place to another. That’s ALL the formatting in one go. But just once.
What is not so obvious is that when you double-click the Format Painter you can repeat your formatting by continuing to click on additional items. The Format Painter stays on until you deliberately cancel it by pressing the ESC key or single-clicking the control.
To use the Format Painter in Excel, apply formatting to a single cell or a range of cells and then either click (to paste the formatting once) or double-click. Now, select another cell or range and all the formats (fill colours, borders, fonts, number formats etc.) are copied from the source to where you have just clicked. As you do this you will see a paintbrush image attached to your mouse pointer.
The Format Painter control is found on the Clipboard group (on the extreme left-hand side) of the Home tab. In older versions of Excel the Format Painter tool is on the Standard Toolbar.
I laugh at myself every time I use the Format Painter as I used to think that the brush image meant “Paste” and I could not seem to get Copy and Paste to work properly. Then I learned Ctrl+C and Ctrl+V but I still used to copy my formats using the menu; Edit, Paste Special, Formats. Really sad.
Movement and Selection
Double-click the lower border and you move down, left border and you move left, right border to move right etc. You always stop at the first blank cell.
Instead of moving the current active cell, to select the range of cells down or across, hold down the SHIFT key as you double-click one of the borders. You expand your current selection down or across to the first blank cell.
Using the Keyboard
You can do all of this with the keyboard if you prefer. To move the active cell, press the END key and take your finger off it. Excel is now in END mode. Now, press any one of the four arrow keys to move in that particular direction.
Practice using the END key sequence a few times and then you can bring your SHIFT key into play to make selections. Hold down the SHIFT key and keep it held down as you press any one of the END and arrow key sequences.
More Arrow Key Shortcuts
- Move one cell up, down, left, or right in a worksheet.
- Ctrl+Arrow key moves to the edge of the current data region in a worksheet.
- Shift+Arrow key extends the selection of cells by one cell.
- Ctrl+Shift+Arrow key extends the selection of cells to the last nonblank cell in the same column or row as the active cell, or if the next cell is blank, extends the selection to the next nonblank cell.
- Left or Right arrow key selects the tab to the left or right when the ribbon is selected. When a submenu is open or selected, these arrow keys switch between the main menu and the submenu. When a ribbon tab is selected, these keys navigate the tab buttons.
- Down or Up arrow key selects the next or previous command when a menu or submenu is open. When a ribbon tab is selected, these keys navigate up or down the tab group.
- In a dialog box, arrow keys move between options in an open drop-down list, or between options in a group of options.
- Down or Alt+Down arrow key opens a selected drop-down list.