In this week’s roundup, number formatting trick, pivot chart layout, spreadsheet comedy, and much more.
1. Pivot Charts
When you create a pivot chart, all of the columns might be the same color, even if you added multiple fields. To show columns in different colors, move one of the fields to the Columns area — it’s called the Legend (Series) area, when a pivot chart is selected.
2. Create Multiple PDF Reports
If you need to send reports to multiple people, Doctor Moxie shared code to do that quickly. The code filters the list, and creates a PDF file from the filtered data.
When a PDF file won’t do, you can use my sample code to extract each person’s data to a separate sheet, using an Advanced Filter.
3. Power Query Calendar
It’s a new year, so Ken Puls shows how to create a custom calendar in Power Query. It breaks the weeks into 13 chunks of weeks, and Ken uses a 445 pattern in his example – each quarter has a month with 4 weeks, then another month with 4 weeks, and a final month with 5 weeks.
Maybe I should use Ken’s calendar – it took me a week to notice that I had hung a 2015 calendar on my office wall!
4. Pivot Table Number Formats
In Strategic Finance magazine, Bill Jelen shows a simple trick that you can use when applying number formats in a pivot table. When you use Bill’s technique, that number format stick, even after you refresh the pivot table. And thanks, Bill, for mentioning my Pivot Power Premium add-in as another way to apply formats!
5. Amsterdam Summit
Registration is now open for this year’s Amsterdam Excel Summit, on May 26th. There is also an Excel Charting and Dashboard Master Class on the following day – May 27th. There is an impressive list of experts who are participating, and an interesting tentative program.
6. Spreadsheet Comedy
Also, I enjoyed this spreadsheet comedy routine by Matt Parker, with its “column to column hilarity”. Matt shows how everything boils down to a spreadsheet. “Would you believe that there are some people out there who don’t use spreadsheets recreationally?” You can visit his website too, to get the Excel photo converter that he mentioned. Don’t use a photo that’s too big though, or the Excel file will be really slow. (Thanks Dave Peterson)
7. Excel Humour
Dilbert featured spreadsheets this week – Three Problems With Spreadsheet – and it is all too true! You can click the arrow to see the next couple of days – they’re pretty funny too. (thanks David Walton)
Finally, for a bit more 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. There is a quick survey there too – which format do you prefer for the list of tweets? Based on the votes, I might get rid of one format.
Get Weekly Excel News By Email
To get Excel news and tips by email, add your name for the Contextures Excel newsletter.
________________
