Excel Roundup 20151109
In this week’s roundup, find errors, lock slicers, plan for the holidays, use keyboard shortcuts, and much more.
1. Hide Formula Results Based on Date
In a list that has dated items, you can hide formula results based on those dates. In my activity tracking file, only the items from today or earlier are shown, and the result in other rows is an empty string, so the cell looks blank. You can download the sample file, to see how it works.
2. Holiday Planner
If you like to plan ahead, you’re already thinking about the upcoming holiday season. To help you stay organized, I’ve updated my Excel Holiday Planner Template, and you can download the new version.
Even if you don’t plan for the holidays, you’ll get some Excel ideas from the template, such as creating a Gantt chart from an hourly schedule, and comparing prices at different stores, to decide where to shop first.
3. Excel in the Classroom
A teacher in Australia used Excel with her Year 3 students, who are learning to collect, organize and represent data. They sorted all the clothing items in the school’s Lost and Found basket, created tables, and showed the results in charts. A new generation of Excel lovers in the making!
4. Excel Improvements
The Microsoft Excel team has a UserVoice site, where you can suggest improvements for future versions of Excel. Then, other people can vote for your suggestions, and they promise to “do our best to respond to every suggestion that gets at least 20 votes.”
Ken Puls would like to have an improved PivotTable Field list, with more room to move the fields around. You can read about it on his blog, then vote for his suggestion.
And if you use pivot tables, and wish they had extra features, like storing your preferred settings, my Pivot Power Premium add-in will save you hours!
5. Shortcuts and Slicers
Are you a mouser or a keyboarder? Somehow, I missed International Keyboard Shortcut Day last week, but you can read about it on Dick Kusleika’s Daily Dose of Excel blog. If you missed it too, perhaps you could use keyboard shortcuts for one hour while you’re working in Excel this week.
I don’t think that keyboard shortcuts work too well with Slicers, but Mike Alexander gives a quick and easy tip for locking your Slicers, so people can’t move them around on the worksheet. I’d never noticed that setting either.
6. Checking for Errors
Your spreadsheets don’t have any errors, I’m sure, but if you inherit one from someone else, you might want to check for problems before you start using it. On the Journal of Accountancy blog, you’ll find a few tips to help you get started with debugging that workbook.
7. Excel Courses
- If you use Excel for data analysis, there is a new free online course from edX — Statistical Thinking for Data Science and Analytics. It starts December 14, 2015, and runs for 5 weeks. The course is led by 6 professors from Columbia University, and you’ll need to invest 7-10 hours effort per week. The prerequisites are high school math, and some exposure to computer programming.
- Ben Kusmin has released a new New York CLE-accredited course – Excel Essentials for Busy Lawyers (beginner/intermediate).
8. Excel Humour
We’ve seen Excel music videos before, but this is a first – an Excel poem, from Brian Bilston’s Poetry Laboetry. (Thanks to Mark Lowe!)
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.