Excel Formula Quick Tips
May 17, 2016
Quick tips for entering formulas, and more, in this week's Excel news. If there are topics that you'd like to see covered in future newsletters, please let me know.
- Debra - ddalgleish @ contextures.com
Visit my Excel website for many more tutorials and videos.
It took longer than I expected, but the major updates to two of my Excel add-ins are finally finished.
Pivot Power Premium (PPP) -- In Version 4.0, the Pivot Table Builder tools have been added, and a Show SEL As command. Read the Quick Start guide, to see how you can export your preference settings, before installing the new version.
Pivot Table Builder (PTB) -- In Version 2.0, many new features were added, such as calculated fields, and storing captions for the fields. Store multiple layout tables, then build or update pivot tables from those stored settings. (Note -- you don't need this add-in if you have the PPP add-in)
To check your version, look on the Contextures button on the PPP tab or the PTB tab of the Excel Ribbon. To get the new version, use your original download link, or email me if you can't find your link.
Here are 3 quick tips, for all those times when you're entering a formula in Excel.
1. To start the formula, type an equal sign, and start typing the name of a function. A popup list will appear, showing any functions that matches what you've typed. When the function that you want is highlighted, press the Tab key, to enter it in the cell, along with its opening bracket. That's a timesaver for longer function names, or the ones that you can't remember how to spell, like CONCATENATE.
2. Before you start typing any of the arguments, press Ctrl+Shift+A to put all the arguments into the cell. The first one is highlighted, so just click on the range that you want to refer to. Then, double-click on the next argument name, and select its range on the worksheet.
3. And finally, if that function popup gets in your way, point to it (anywhere except the function name or bold argument name). When the pointer changes to a 4-headed arrow, drag it out of the road. That tip popup is helpful most of the time, but can be a nuisance if you're trying to click on a column heading.
If you want a a great introduction to Excel dashboards, attend one of Mynda Treacy's free Excel Dashboard webinars this week. You'll pick up some amazing tips and skills during the 1 hour presentation.
Also, Mynda has temporarily opened registration for her highly-rated Excel dashboard course. And don't miss the 20% early bird discount -- sign up by Thursday May 19, at 8 PM (Pacific time zone).
Here are a couple of Excel articles I read recently, that you might find useful.
Table Formatting -- Do you use conditional formatting to make your Excel tables look nice? Hui shows how to use Slicers and conditional formatting to quickly change their appearance. It's an interesting technique, but might slow things down, for a big table. To keep things speedy, create Custom Styles instead, like you do for Pivot Tables.(Level - Intermediate)
List Everything -- If you have two lists, such as products and colours, how can you create a new list with all the possible combinations? Ken Puls shows how to do that with Power query. And if you don't have that installed, I explain how to use Microsoft Query instead. (Level - Intermediate)
More Excel Articles -- Visit my Excel website for many more tutorials and videos.
We have a long weekend coming up, for Victoria Day. It's the official start of garden season, so we'll be planting flowers, tomatoes, and a few herbs. We'll buy the plants mid-week though -- you don't want to be in a garden centre on the weekend!
In the meantime, the forget-me-nots and dandelions are doing nicely, and adding a bit of colour to the garden. They're blissfully unaware that the end is near.
NOTE: If you have any problems with the links in the email, you can see this newsletter on my website -- copy this link and paste it into your browser:
Choose Your Excel News: Get the full Contextures news package, or the basic one. To change, click a link below, and use the sign-up form on that page.
Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.
Last updated: August 21, 2020 1:00 PM