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.
This is the first in our occasional series of Mouse Tips. These are the little things in life that make all the difference. Today the topic is non-breaking spaces. Hold your breath!
Text in browsers, documents and emails usually wraps onto the next line at the first available space or hyphen. Which is fine by me. Most of the time, but not always. What do I do if I have text where I need to keep the words together? Like addresses or proper names. Here’s my address: 27 High St, EC1.
I want all of those words to stay together. I don’t want the text to break onto a new line after the “27”. And then have the “High St, EC1” on the next line. Text will always wrap at the first space. Personal names are another bugbear. The name, “Mrs Edna Bloggs” should all read as consecutive words, otherwise it looks silly. You can’t have “Mrs” at the end of one line, and then “Edna Bloggs” at the start of the next line. Numbers and units are another example where the text needs to stay together. 24 mpg should always read as “24 mpg” otherwise it has no meaning.
You often end up re-typing the text so that it reads properly. No, this is where you need a non breaking space.
A normal space is the spacebar. The non breaking space is CTRL+SHIFT+Spacebar. Hold down CTRL and SHIFT together and then press the spacebar. It’s a bit of a pain BUT that text stays together. You’ll have to do this for every space in an address.
If you’re on a Mac, try OPTION+Spacebar.
If you want to see other typographical tricks, come along on our Word Introduction Course.
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…