Contextures

Contextures News

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.

Product Updates

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.

3 Tips for Entering Formulas

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.

There are lots of function tutorials and videos on my website, and a page on getting started with formulas.

Dashboard Webinars

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).

Excel Articles

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.

Garden Season

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:

www.contextures.com/newsletter/excelnews2016/20160517ctx.html

Recommended Excel Products

  • Easy Data Entry: Add a popup list, to your worbook, so you can quickly enter items in a cell. The Data Validation Multi Select Premium (DVMSP) kit helps you set things up. Then, a list appears when you click a cell, so only valid items can be entered. Reduce data entry errors and typos. You can customize the list, to allow single or multiple selection, in specific columns.
  • Excel Chart Tools: If you build complex charts, save time with Jon Peltier's Excel Charting add-in. Don't waste hours trying to format the chart yourself!

___________

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.

  • Full News Package: Weekly newsletter, plus occasional emails so you won't miss out on special Excel deals, free webinars, new products, and other Excel news.

Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.

 

 

 

Search Contextures Sites

Excel Data Entry Popup List

 

 

 

 

Last updated: June 14, 2017 11:49 PM