In this week’s roundup, take the pivot table survey, learn new chart tricks, see what’s new in Power BI Desktop, and much more.
We’re on our summer schedule now, so the next roundup will be published on Monday, August 31st.
1. Pivot Table Survey
In the latest collection of Excel tweets, on my Excel Theatre blog, someone said, “How do I make a pivot table? I don’t have time to try a bunch of stuff and see what works!” And, at the other end of the pivot table scale, someone else said, “if it doesn’t have a pivot table then I’m not interested.”
Do you use pivot tables? Do you love them or hate them? Here’s a 3-question survey, and I’ll share the results in the next Excel Roundup.
2. Hour and Minute Drop Downs
For a time entry worksheet, you can prevent invalid time entries with drop down lists of hours and minutes. This technique uses data validation, and no macros. However, as Jeff Weir pointed out in the comments, someone could still find a way to enter invalid times.
Remember, data validation can be defeated, even by a simple copy and paste, so don’t depend on it if you’re building nuclear missiles, or anything critical! My goal, usually, is to make a workbook easy to use, and idiot resistant (not idiot proof). How about you?
3. Power BI Desktop Updates
SQL Server MVP Reza Rad gives all the details on what’s new in Power BI Desktop 2.0 GA. It’s an easy-to-read summary of the changes, with lots of screen shots that show the changes.
If you’d rather see the changes in a video, watch this 40-minute overview from the Power BI team.
Rob Collie has written a great article on how an Excel pro can get started with Power BI Desktop, so check that out if you’re not sure where to start. Rob has a few minor complaints about Power BI Desktop, and read the comments to, to see other people’s concerns.
4. Chart Tricks
I found a couple of article with chart tricks this week. Jon Peltier explains how to plot values along the axis of a chart, in a single line, similar to the markings on a thermometer.
On her blog, Stephanie Evergreen shows how to create a slopegraph, which is based on the Excel line chart type.
5. 40 Greatest Excel Tips
Bill Jelen (Mr. Excel) has just finished his latest book, Mr. Excel XL – The 40 Greatest Excel Tips of All Time. Bill sent me an advance copy, and it’s a beautiful book, packed with great tips and colour photos, and a nice touch of humour. He even included 40 of my favourite tweets, from the weekly collections on my Excel Theatre website.
You can hear Bill talking about his book, and many other Excel topics, on John Michaloudis’ latest podcast.
6. Excel Problems
Excel is a great application, but things can go horribly wrong, as I’m sure you know. After working with a nightmare workbook, Melissa P. Esquibel shared the biggest lessons that she learned from the experience.
Becky Collins uses Excel too, and doesn’t mention any nightmares, but does have “huge enormous spreadsheets full of superfluous data”. Don’t we all? Becky shares her top 10 annoyances, and I hate it when Excel forgets what I copied too.
7. Prepare for September
September is just around the corner, and school will be starting again soon, so Tracy Duncan has 8 Excel tips and tricks for teachers and students.
Or maybe you’ll be looking for a new job in the fall. If you are, Lauren Miliotis explains how to use Excel when you’re looking for a new job.