Excel Roundup 20160229
Happy Leap Year Day! According to my calculations, the next time that Leap Year Day falls on a Monday will be in 2044. By then, Excel will be 58 years old. Do you think that Excel will still be around, or will it have retired early?
This week, cool tricks with the new TEXTJOIN function, tell a story with your charts, create a calendar for Power Query, and more.
1. Count Unique Items
Roger Govier explains how to set up a pivot table in Excel 2013 or later, so you can show a Distinct Count. For example, if there were 100 orders, how many distinct (unique) products were sold?
2. Making Sense of Charts
The Science Goddess used a new type of chart at a recent education workshop, and was fascinated by the participants’ reactions. She also collected their feedback in a unique way, to take a look at her blog post, to learn what happened.
Mynda Treacy shows a few alternatives to using radar charts. For example, you can use data bars, bar charts or Sparklines.
In another charting article, Ann K. Emery asks if you’ll present the data as-is, or if you’ll tell a story. She shows four examples of how to use a storytelling approach in a chart.
3. Resource List
If you’re in the mood to learn a few new things, Ben Collins shares his list of Data Analytics resources. There are several sections, including Excel, SQL, statistics and Data Visualization.
4. Calculating Dates
Your need calendars when you’re working with Power Query, and Bob Phillips shows how to calculate all the bank holidays for a specific year, and how to calculate the trickiest holiday of all – Easter.
(If you need to calculate Easter dates in Excel, there’s a page on my website.)
Chris Webb has found an interesting use for the new TEXTJOIN function in Excel 2016. He created a formula that shows a comma delimited list of the items selected in a Slicer.
In a short video, Mike “ExcelIsFun” Girvin shows how to use TEXTJOIN to return multiple values for a lookup, in a single cell. In the data, there are three items with the same invoice number, and the formula returns all three, separated with semi-colons. Very cool!
6. Excel Humour
Finally, for a bit of spreadsheet humour, you can see what people are saying about Excel, in my weekly collection of tweets. Here’s one of my favourite tweets from this week’s collection.
Get Weekly Excel News By Email
To get Excel news and tips by email, add your name for the Contextures Excel newsletter.