In this week’s roundup, create a rolling total, chart tricks, unmerge cells, clean data, and much more.
1. Rolling Total
Instead of an overall running total for a list, you could create a rolling total – use a formula to show the sum of the previous 12 months in each row. Download the sample file to see how it works.
2. Fun With Charts
To make things easier for your, or the people who use your reports, Mike Alexander shows how to set up a check box to turn the chart labels on or off. The technique doesn’t use macros, and you can download his sample file to see how it works.
On his latest podcast, Chandoo talked about creating animated charts. To get some great ideas, listen to the podcast, and download the sample file.
3. Unmerge Cells
Merged cells can cause problems, especially if you’re trying to sort or filter data. Sumit Bansal shows how you can find merged cells, and highlight them, using built-in Excel commands.
If you just want to unmerge some cells in a range, select all the cells, then click the Merge & Centre command. Don’t worry – it won’t merge all the cells together – unless there’s only one cell with data.
4. Show the Achievement Gap
In a report to her superintendent, the Science Goddess had to show the achievement gap — the difference in achievement levels between populations of students. It took her a while to find the best way to represent the data in a chart, and you can see that the results were worth the effort.
5. Data Cleanup
Faced with a messy list of telephone numbers, Dave Bruns used nested SUBSTITUTE functions to strip out all the spaces, hyphens, periods, parentheses, and commas. Then, he formatted the results with the Telephone Number format, to create a clean and easy-to-read phone list.
There are more data cleanup techniques, along with other tips for data analysts, on the Investintech blog. One tip shows how to bring together data from several CSV files, using either Power Query, or Ron de Bruin’s free RDB Merge add-in.
6. Lookup in a Structured Table
On this Daily Dose of Excel blog, Dick Kusleika showed how to create a lookup formula that works with structured table references. He even made a video! Originally, he used VLOOKUP, but he has now switched to INDEX/MATCH instead, thanks to some tough love from the commenters. The Internet can be a rough place!
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.