In this week’s roundup, bad dashboards, new Workday function, calculation speed and much more.
1. Project End Dates
Calculate project end date with the WORKDAY.INTL function (in Excel 2010 or later). It’s an improved version of the older WORKDAY function — you can set your own weekends (non-work days), instead of defaulting to Saturday and Sunday. See how it works, and download my sample file for testing.
2. Calculation Speed
Does it slow things down if a formula refers to a full column, such as =COUNTA(A:A)? Charles Williams tested a few scenarios, and shared his results. The good news is that it’s usually okay – but read his article for details on the situations to avoid.
And while you’re on Charles’ website, take a look at his article posted one day earlier, on shutting things down, to help with calculation speed.
3. Ignore Errors With Aggregate
If a range of cells contains error values, those can affect formula results. Doug Jenkins wanted to ignore errors, and find the minimum and maximum values from a range where some cells showed #N/A, and he found a couple of solutions.
His examples show that in any version of Excel, you can ignore those errors with ISNUMBER nested in an array function. For Excel 2010 and later versions, use the AGGREGATE function, with one of the options that ignores errors.
4. Get Data From an Add-In Sheet
Dick Kusleika wanted to see a list that is on a worksheet in one of his Excel add-in files. Add-in sheets are hidden, so you have to go through a few steps to see them. Instead, Dick used a command in the Immediate window, to show the list items.
And thanks to Lori’s helpful comment on his blog post, he learned a quicker and easier way to get that list. How did anyone get work done before the Internet was created?
5. Power Query
Ken Puls shows how to identify duplicate items in a list, by using Power Query. There are a few easy to follow steps, and the result shows all the records, in the original order, with the duplicates marked.
6. Essential Excel Tricks
Do you agree with this list of 7 essential Excel tricks for office workers, by Abby Wolfe? Trick #5 (Power View) seems like an odd choice – I’d pick data validation over that feature. Long ago, I listed 14 basic Excel skills, but that was before all the Power BI features were added.
7. Data Viz — Good and Bad
Data visualization is usually a serious topic, but the Excel TV team, and their knowledgeable and hilarious guest, Mike Alexander, have some fun things to say about these bad data viz examples. For instance, don’t make graphics that have ice cream sandwiches yelling at people!
To see how good data visualization is done, visit The Science Goddess’ blog. She did a data viz workshop for educators, and you can download her training materials.
8. 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.
________________
