Contextures News

Build Complex Formulas Fast

May 3, 2016

A quick way to build complex 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.

AutoCorrect Formula Entry

On Chandoo's blog, there was another set of Excel Tips last week. There were some good tips in the list, but my favourite tip was by Wyn Hopkins, down in the comments section. He suggested using an AutoCorrect entry, so it's easier to create INDEX / MATCH formulas. (You can read more about that powerful duo on my website.)

I almost ignored this tip, because I thought, "Excel already shows us the formula syntax. Why would I need this?" Fortunately, I read it carefully, and realized that it could be a daily time saver.

The tip didn't describe how to create an AutoCorrect entry, so here's how to do that, if you can't remember:

• First, copy Wyn's formula -- itt has placeholders for the cell references:
  =INDEX( DblClk_to_Select_Column_to_return, MATCH( DblClk_Single_Lookup_Cell, DblClk_Lookup_Column, 0),0)
• In Excel, click the File tab (or Office circle) at the top left of the screen
• Click Options, then click Proofing, and click the AutoCorrect Options button.
• In the Replace box, type a short code that Excel can "AutoCorrect" for you -- I used fnim (short for Function Index Match). Don't use a real word, because Excel will change this, every time you type it.
• Click in the With box, and press Ctrl + V to paste the formula
• Click Add, then click OK, twice, to exit.

After you add an AutoCorrect entry, here's how to use it:

• Select the cell where you want to enter the formula
• Type your code (fnim), and end with a space character
• The formula with placeholders will appear in the cell
• Double-click on each placeholders, then click on the cell or range for that reference.

You could add other AutoCorrect entries too, if you frequently build complex formulas. Then, you just have to remember to use them!

Excel Articles

Here are a couple of Excel articles I read recently, that you might find useful.

Power User Tips -- On the ComputerWorld blog, Sharon Machlis posted 11 handy tips for Excel Power Users, from the recent Data Insights Summit. Tip 4 is for Excel 2013 or later, and you won't need tip 6 if you put your pivot table in Outline or Tabular layout. (Level - All)

Evil Functions -- Charles Williams is an expert on Excel calculations, and he thinks the INDIRECT function is evil, because it's slow and fragile. Do you agree? You can use it to create dependent drop down lists, so it can be good, if used carefully. (Level - Intermediate/Advanced)

More Excel Articles -- Visit my Excel website for many more tutorials and videos.

Starting Out

Yesterday was our wedding anniversary, and we look just the same as we did many years ago, when we were just starting out. Well, we might have changed a bit! But we still have fun, and Mr. D still gets that "what just happened" look on his face, occasionally. ;-)

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/20160503ctx.html

Select Your Contextures Excel News Package

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.
If you use the same email address that you used previously, you will be automatically moved to the new list, and removed from the old one.

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

• Basic News package: Weekly newsletter only.

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!

• See more recommended Excel products

________________________

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