In this week’s roundup, let me know if you like the version of Excel that you’re using at work, get productivity tips, add a sneaky filter to a pivot table, and more.
We’re on our summer schedule now, so the next roundup will be published on Monday, August 3rd.
1. Excel Version Survey
In the latest collection of Excel tweets, on my Excel Theatre blog, someone said, “We just got upgraded to Excel 2013 at work and I hate everything.”
What do you think about the version of Excel that you’re using at work? Here’s a 3-question survey, and I’ll share the results in the next Excel Roundup.
2. Copy the Numbers Only
On this blog last week, I showed how to use the built-in Excel tools to select specific things. For example, if a column has numbers and some blank cells, use the Go To Constants command to select only the numbers, and ignore the blanks, text, or other types of data. There’s a short video too, and a sample file you can download, to follow along.
3. Productivity Tricks and Shortcuts
Chandoo shared 25 shortcuts and tricks to boost productivity, and #22 is one that I use frequently – press the F4 key to repeat the last action. It doesn’t work for everything, but it’s a real timesaver!
4. No Add-ins With Mac Excel 2016
The Mac version of Excel 2016 has been released, but if you’re using add-ins, Jon Peltier warns that you shouldn’t upgrade yet. Even though VBA is supported, some features have been disabled, like customizing the Ribbon. So, if you’ve got the Mac version of Jon’s charting utility, stay with Excel 2011, at least for now.
5. Pivot Table Trick
Bill Jelen shows how to make an AutoFilter work on a pivot table, so you can use its features, such as filter for the top 5 values. Remember, if the grand total or subtotals are showing, they’ll be included in the results. You could hide them before filtering.
6. Excel Announcements
- Excel Dashboard Course: Mynda Treacy has opened registration for her acclaimed Excel Dashboard course, and you’ll get 20% off, if you sign up by July 30th. The course is a great investment, and you can read my review for the highlights, and more information.
- Free Dashboard Webinars: Mynda is also offering two free one-hour webinars — 1) How to Build Excel Dashboards, and 2) Dashboards with Power Query and Power Pivot. Get the details, and sign up for a date and time that is convenient for you.
- Power Query Training — The August 12th class has sold out, so Power Query experts Ken Puls and Miguel Escobar have opened registration for another August session of their online Power Query course. There are 2 days of live online training (4 hours per day). Then attend a live online follow-up Q&A session, a week later. The class is very popular, so register early!