Contextures News 20180123

Excel Quick Symbols

Jan 30, 2018

Quickly enter special symbols, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!

Quick Symbols

If you frequently use symbols in your worksheets, create AutoCorrect entries, to save time.

This animated screen shot shows the steps - type a short code, copy and paste a symbol from the Math tab, then use the short code on your worksheet, any time you need it. (Unfortunately, those Math shortcuts don't work in Excel - that's why we copy them to the first AutoCorrect tab.)

Here's another way to save time with AutoCorrect - Create complex formula entries

Interactive Dashboards

Join Mynda Treacy for a free one-hour webinar, and learn how to build interactive dashboards in Excel, with or without Power BI tools. The live webinars are only available until Thursday, Feb. 8th, so click here to register today.

If you want to learn even more, Mynda offers full dashboard courses too. You can get 20% off if you register by Thursday, February 8th. Click here for the Excel Dashboard Course or the Power BI Course

COUNTIF 255 Limit

Thanks for your solutions to last week's COUNTIF formula that showed an error because of the 255 character limit. Nobody understood Microsoft's workaround though, so I'm glad it wasn't just me!

A few people used COUNTIFS to check the columns where the text was broken into chunks. Suresh suggested checking the text length first, and using COUNTIF if possible.

=IF(LEN([@Item])<256, COUNTIF([Item], [@Item]),   COUNTIFS([Item_P1], [@[Item_P1]], [Item_P2], [@[Item_P2]]))

Anyway, it's much more complicated than it should be, so I'll stick with my SUMPRODUCT formula.

=SUMPRODUCT(--([Item]=[@Item]))

• Checks each row [Item] to see if it's equal to the entry in the current row [@Item]
• The result is TRUE or FALSE for each row (highlighted in the screen shot below -- I pressed F9 to evaluate that section of the formula)
• The two minus signs (double unary) change TRUE to 1 and FALSE to 0
• SUMPRODUCT adds up the 1s and 0s.

Excel Articles

Here are a couple of Excel articles that you might find useful or interesting.

Comments - On the Excel Esquire blog, Ben J. Kusmin shows how to find and review comments in Excel files. There are comment macros on my site, including one to number and list all comments, and a comment lister in my Excel Tools add-in. (Level - Intermediate)

Problem Solving - Mike Girvin (ExcelIsFun) has an interesting new "Excel and Business Math" series on YouTube. This video shows the 5 key steps for solving math problems in Excel. (Level - All)

Also see: My Excel Products || Excel Events || Previous Issues || Excel Humour

Follow the Instructions

The snow is back again, so it seems like a good time to knit something. Long ago, I managed to knit entire sweaters, but my attention span is shorter now -- I stick to small projects, like this kitchen cloth. Also, nobody has to wear my mistakes, if I don't follow the instructions exactly. There are many articles that claim knitting is similar to programming. What do you think?

That's it for this week! If you have any comments or questions, send me an email.

NOTE: For the online version, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2018/20180130ctx.html
I'll also post any article updates or corrections there.

Debra Dalgleish
ddalgleish @ contextures.com

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.