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!
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:
There are more details on my website, and ways to get a unique count in earlier versions of Excel.
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.
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).
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)
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)
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.
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: http://www.contextures.com/newsletter/excelnews2017/20171212ctx.html
ddalgleish @ contextures.com
Last updated: December 9, 2017 10:36 AM