Spreadsheet Roundup 20191127

by Debra · Published November 27, 2019 · Updated November 26, 2019

Here is a 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.

Functions

Formulas: Here’s an interesting interactive lesson on reading spreadsheet formulas, with helpful tips for understanding how they work
Modelling: To learn about the latest trends, listen to the financial modelling podcast, hosted by Matthew Bernath. There are 39 episodes so far, and the site has blog posts too.
Formula Coach: Have you tried Microsoft’s Excel Formula Coach? You can enter numbers online, and it builds a formula that you can paste into your workbook. I found PMT, PV and FV coaches, and there’s a spot to suggest other functions.

Charts and Data Analysis

Charts: Michelle Rial makes fascinating charts, to amuse and/or inspire you. If you think you’re too old to learn new spreadsheet things, check out the “Is it too late to start?” chart, about halfway down the page.
Data Viz Tips: On GitHub, MaryJo Webster has a Data Journalism page, with a long list of training resources. Do a search for “Excel” on that page, to find Excel tip sheets and other Excel resources. Who could resist “Torturing Excel into doing statistics”?
Good Charts: Ideas from chart expert, Alberto Cairo on how to prepare and interpret charts. Good tips at the end of the article.
VisComm: Download the papers and case studies presented at the recent Visualization for Communication (VisComm) workshop.
Data Prep: Detailed steps from Lisa Charlotte Rost, show how to prepare your data for analysis and charts. Data preparation takes work, but it’s worth it!

Spreadsheet Tips

What’s New: Watch 13 recordings from Excel sessions at this year’s Microsoft Ignite event. See what Office Scripts can do, use Excel with Visio and Flow, or learn 5 pivot table hacks. And for a quick overview of what’s new, check the list of Excel announcements from Ignite.
Drop Downs: On the Design Smarts site, Josh Wayne explains the pros and cons of using drop down lists. He’s talking about web forms, but the design ideas are good if you’re using data validation drop downs in spreadsheets too.
Shortcuts: Dick Kusleika shows how to use a keyboard shortcut to open Excel, or any other program, if you have its icon on your Windows Taskbar. It only works for icons 1 to 9 though.

_________________