In this week’s roundup, prevent duplicate totals, build an Excel dashboard, formatting nit-picks, and much more.
1. Create Subtotals in Excel
If you use the Subtotal feature in Excel, and add two or more layers, you might end up with duplicate grand totals. While working on a project recently, I figured out what was causing those duplicates, and how to prevent the problem. There’s a video too, that shows the steps.
The first two rounds of this year’s ModelOff have been completed, and the finals will be held in London, on December 3-4. The judging panel was announced last week, and congratulations to Roger Govier, who is one of the three judges. You can see some of Roger’s excellent work on his Sample Files page on the Contextures website.
3. Power Query
Ken Puls shows how you can merge data from two tables in Power Query, combining two columns to make a unique key. It sounds complicated, but looks surprisingly easy!
4. Office 2016
If you’re using Excel 2016 on the Mac, the Excel team shows 8 of the new features and shortcuts that you can use. On the list are Pivot Table Slicers and the Analysis ToolPak. (Wow, glad I’m using Excel on Windows!)
If you’re using Excel 2016 for Windows, is it faster than the previous version? Dick Kusleika, from Daily Dose of Excel, finds that his macros run much slower in Excel 2013 than they did in Excel 2010. To prove it, you can download his sample file, click the Test button, then open the CSV that the macro creates. It shows the time taken to get to each step. (And if you were a fan of the old TV series, Taxi, does this remind you of Jim’s driving test? — “What does a yellow light mean?”)
5. Build a Dashboard
Ben Collins gives detailed instructions for the dynamic, interactive dashboard that he built in Excel, to show U.S. historical political data. He even describes features, like a radar chart, that he decided not to include. There are links to the source data, so you can follow along, and build your own dashboard. Make sure that you have an “I Love Spreadsheets” mug though, before you get started.
But remember, a workbook can’t tell you everything. Dick Taylor takes a look back at how he got started using Excel, and some spreadsheet limitations.
6. Excel Formatting
For Excel 2016 for Windows, another chapter from Curtis Frye’s new book, “Microsoft Excel 2016 Step by Step” is available for free download. This chapter covers formatting, and there might be a few tips that you hadn’t discovered yet, such as how to change the default font for a workbook. If you’d like to buy a copy of the book, you can get it on Amazon.
Meanwhile, on the Consultants Mind blog, there are some Excel formatting nit-picks. Are you guilty of any of those, and what would you add to the list? I’ve seen “twerked” words, but never heard them called that!
7. 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.