Microsoft Office Training London 020 7920 9500
Our Excel double click tricks are some of those little things that make your life so much easier. You probably know most of them already. Or do you? I think that anyone who uses Excel regularly should know them. Have a quick wizz through and see if there’s anything that you might find useful.
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 didn’t 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. For me, this the best ever Excel double click trick.
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.
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.
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.
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.
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.
Double clicking on a normal Excel cell doesn’t do very much. It just changes the mode from Ready to Enter so that you can edit the cell entry. But double clicking on a Pivot Table cell is something else. Double clicking on a Pivot Table cell means Show Details, it’s the shortcut alternative to choosing Show details in the right-click menu.
Here’s my Pivot Table report and I want to query one of the numbers in the report. Pick a number and double click. Instantly, you will see a complete open-item analysis of that number extracted onto a separate sheet. Now you can see exactly how that total is broken down.
If you find that Show Details is not working for you then you should check to see if it’s turned on. Right-click any cell in your Pivot Table and then click Pivot Table Options. Click the Data tab and then check the Enable show details option.
To get the best out of Excel Pivot Tables you’ll need to have a good, working knowledge of how to arrange your source data and what kind of reports you can easily generate. Come along on our dedicated Excel Pivot Tables course and discover what they can do for you.
Call 020 7920 9500 now for details.
The SUMPRODUCT function is one of those hero functions that, once discovered, you wonder how you ever managed without. It does not just do “what it says on the tin”, there’s a lot more to it than that. In particular, it’s one of the most powerful and flexible filter functions in Excel. And so much better than SUMIF or SUMIFS.
It’s easy to calculate someone’s age from their date of birth if you know about Excel’s DATEDIF function, unfortunately it’s easy to miss this function as it is not documented. Excel will not help you fill in the DATEDIF function interval values, you need to see the list here.
Complicated text formulas using either ampersands and the CONCATENATE function are the bane of our life. Not any more! Excel new text functions will really help us nail those text formulas. We’ll be looking at the CONCAT function and the TEXTJOIN function.
Usually the formulas you need for percentages and differences are quite straightforward: divisions for percentages and a minus sign to take one value from another. But there are pitfalls for the unwary which we shall explore.
I think the Excel FILTER function does the filter job better than AutoFilter. It’s a live formula and an extraction, you don’t have to filter your data in place. There’s no need for that clunky Advanced Filter…
You can use the Excel UNIQUE function to extract a list of unique or distinct items from a range of Excel cell values. It’s really easy. See how to sort the results automatically when the data changes…