Contextures

Contextures News

Excel Art and Advanced Formulas

Dec 12, 2017

Advanced formulas, unique counts, 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. This will be the final newsletter for 2017, and I'll see you again in January.

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

Unique Count

In a pivot table, you can quickly see a summary of your data. Use the Sum, Count, or other functions, to see totals for each region or product. For example, what was the total sales amount for each region, and the count of their orders?

But how can you show a distinct (unique) count? For example, if there were 100 orders in a region, how many different products were sold? In Excel 2013 and later, follow these steps:

  • Check the box to "add this data to the Data Model", when you create the pivot table
  • Then, add Product to the pivot table's Values area
  • Right-click a Product value cell, click "Summarize Values By", click More Options
  • Scroll to the end of the functions list, and click Distinct Count

There are more details on my website, and ways to get a unique count in earlier versions of Excel.

unique count in pivot table

Advanced Formulas

Ben Collins is a Google Sheets expert, and he's offering a free course in Advanced Formulas. Many of the Google Sheets functions are almost identical to the Excel functions, such as VLOOKUP, INDEX and MATCH, so you can use some of Ben's examples to build advanced formulas in Excel too.

You'll also learn about functions that Excel doesn't have, such as QUERY and FILTER. It's interesting to see what the competition can do, that Excel can't.

I'm not sure how long Ben is offering the course at no cost, so if you're interested, sign up soon.

Google Sheets Filter function

Your Feedback

Last week, I linked to a Bloomberg article about Excel complaints. Todd L hates the way that Excel messes up some number when you import them, such as zip codes that start with a zero. Todd supports this fix request on UserVoice, and you can vote for it too, if you'd like that behaviour changed. Almost 1200 votes, and the last response I see from Microsoft was 2 years ago.

Jeff Weir also has an Excel complaint - problems with Excel tables on protected sheets. There is a UserVoice request to fix that too. To add more drama to his request, Jeff rewrote the Christmas Carol. It's entertaining, but needs an illustration or two, like these public domain ones (British Library).

Excel Articles

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

Excel Art - A few years ago, I heard about Tatsuo Horiuchi, who creates amazing art in Excel. He's still at it, and getting even better! Watch the short video, to see what he does. Thanks to Wendy N, for the link. There's more Excel art on the Datalabs site, created from charts. And Andy Pope has a free tool for converting a normal BMP image into an Excel scatter chart. (Level - All)

Dashboards - A quick reminder that registration for Mynda Treacy's dashboard course closes this Thursday, Dec. 14th. Click here for the Excel Dashboard Course or the Power BI Course

Formatting - Chandoo shows 5 of his favourite conditional formatting tips, ranging from simple highlighting, to complex formula rules. Download his sample workbook too. There are more conditional formatting examples on my website. (Level - Intermediate/Advanced)

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

Happy Holidays

Happy holidays, if you're celebrating this month! We'll have our family here for Christmas Eve and morning, so things should get exciting. Long, long ago, I was lucky enough to meet Santa, and we posed for a photo. Ask your parents about those olden days, before colour had been invented.

And if you want a Christmas tree, but without the falling pine needles, you can download my Excel Christmas tree file. Enjoy your time off work, and watch for my next newsletter in January.

weekly photo

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

NOTE: If you have trouble with the images or links in this email, paste this URL into your web browser, to see the online version: https://www.contextures.com/newsletter/excelnews2017/20171212ctx.html

Debra Dalgleish
ddalgleish @ contextures.com

Debra Dalgleish

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.

 

Search Contextures Sites

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: December 9, 2017 10:36 AM