Mouse Training Blog - Mouse Training London Ltd

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.

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.

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…

You can use Excel’s ROMAN function in a formula whenever you need to express normal, Arabic numbers as Roman numerals. And you can use the ARABIC function to convert them back the other way…

Generating random text or Lorem Ipsum for Word and PowerPoint. Do you ever need filler or placeholder text for Word documents or PowerPoint text boxes? It’s quite handy when you want to plan the design and layout of your document…

Do you ever have to deal with those cluttered PowerPoint slides? You know; the ones crowded with layers of overlapping shapes and text boxes—and you need to change the one in the middle of the pile…

I must say that I quite like Keynote, and prefer it to PowerPoint. But if there’s one thing that gets my goat, it’s those pesky text boxes! You can’t seem to resize them properly. What you need is an 8-handled text box…

Text in browsers and documents usually wraps onto the next line at the first available space or hyphen. Which is fine most of the time, but not always. What if I have text where I need to keep the words together…