Spreadsheet Roundup 2020-05-22

by Debra · Published May 22, 2020 · Updated May 21, 2020

Here is a Spring 2020 roundup of selected spreadsheet articles that I’ve read recently. You’ll find interesting tips, useful sample files, and new ideas for your own spreadsheets.

For Excel tips and tutorials, visit my Contextures website. And a bit of Excel humour, go to my Excel Theatre blog, to see what people are saying about spreadsheets.

Spreadsheet Tips

Excel Tips — Watch Excelling in Excel, a mini course from the University of Edinburgh. There 3 videos – data organization, tips and tricks, data visualisation..

Excel Skills: Take a look at the free 4-week online Excel course, from the University of Colorado – Everyday Excel Part 2. It covers advanced data management, Excel for financial applications, and more.

Excel Resources: There’s always something new to learn in Excel, so check out Rick and Jordan’s list of 100+ best Excel websites and resources.

Excel Problems: According to this survey, we spend 30 minutes a day sorting out computer problems. The biggest headaches? Spreadsheets and presentations – even though 41% say they’re spreadsheet experts! Does that sound like your co-workers? And if you want to avoid a few headaches, Bill Jelen (Mr. Excel) shares easy, but not obvious, solutions to Excel problems.

Working with Data

Copy Paste: Could you get through the day in your spreadsheets, without the copy and paste commands? Those computer commands were created by the late Larry Tesler, when he worked at Apple. Read Luke Dormehl’s tribute to this computer pioneer.

Dashboard: Excel MVP, Ingeborg Hawighorst, built an interactive dashboard that shows the development of the Covid-19 infection in New Zealand. She started with Excel and Power Query, then switched to Power BI. Learn how she built the dashboard, and you can access the live dashboard at this link.

Clean Your Data — The worst part of working with data is cleaning it up. To help save time, use these data cleanup techniques, from the Investintech blog.

Power BI: If you’re working in Power BI, Eugene Meidinger explains when to use its languages, DAX and M, in this short and clear article.

Dashboards: Robert Mundigl shows how to use table Slicers on an Excel dashboard, with a simple example file to download. You can download the fancy dashboard workbook too, to see how it works.

Macros

Be Careful: On the Trustwave blog, Diana Lopera explains how the recent Excel 4.0 macro spam attempts work. As always, be careful with unknown Excel files — just like VBA macros, these 4.0 macros won’t work when macros are disabled.

Programming: If you’d like to get started with Excel macros, the Think Like a Coder animated video series, by TED-Ed, is a fun way to learn some of the key concepts. Eight of the 10 episodes are available on YouTube, and kids might enjoy the series too.

Video – LET Function

In this video, Mynda Treacy shows clear examples for the new Excel LET function (currently in beta), with a handy troubleshooting tip at the end. Define names in a formula, assign a value to each name, then use those names in the calculation.