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.
Cluttered PowerPoint Slides
Cluttered PowerPoint Slides
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! You can’t see what you’re doing on these busy slides. Some people move all the other objects out of the way so that they can see the one they want. Then they make a few changes and then move them all back again in to, hopefully, their original positions.
There has to be a better way. You can use the keyboard shortcuts Tab and Shift+Tab to cycle forwards and backwards through the stack of objects until you can see that the selection handles are around the one you want. Or, you can press ALT+F10 and turn on the…
There’s a control to turn this on and off on the Home tab but it’s easily missed. The Selection Pane lists all the objects on your slide and you use it to identify and work on any object. Just click the Hide All button and then start clicking the eyes to the right to show selected objects, you’ll soon find the one you want. Make your changes, click the Show All button and you’re done. It’s so easy when you can see what you’re doing.
PowerPoint uses its own fiendish naming scheme for all your objects. Name like Rectangle 5, TextBox 4 etc make perfect sense until you have to animate them and then you have real problems trying to identify specific objects. All you have to do is rename them according to their appearance or purpose and you’ll have a much easier time creating animations.
An object named something like “Black Heart” or “Red Star” is a lot easier to deal with than “Heart 4”. To rename an object, click the current name in the pane and replace the text.
There’s nothing wrong with having cluttered PowerPoint slides—you just need to know how to work with them easily.
To see how to create great presentations, come along on our PowerPoint Introduction Course.
Call 020 7920 9500 for full details.
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…