Excel Checklist for Complex Files
October 19, 2021
Things to check in an Excel file, convert measurements, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.
Thank you for reading the news, and you'll get the next email on November 2nd.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
Do you ever have to take over complicated Excel files that were built by somebody else? To prevent those workbooks from becoming your personal nightmare, what signs of trouble do you look for?
Back in 2015, Matthew Kuo posted a list of 8 things you should check, before you start working on the file. I read his article again last week, and its advice is still worth following, even though Excel has had many updates.
The first tool that's mentioned is Inspect Workbook. To see it, click the File tab at the top left of Excel, then click Info.
Hidden sheets can be a nasty surprise, if you don't check for them! In newer versions of Excel you can unhide multiple sheets at the same time, so that's a big improvement.
Also, check for links that might be hidden away in the workbook. Bill Manville's free tool, Find Links, is a great help with that.
Whether you're making supper, or selling "build a storage shed" kits, Excel can help you calculate the total cost, or weight, or calories, based on all the components/ingredients.
Last week, I uploaded a new version of my Calorie Counter Recipe Calculator sample file. You can list all the ingredients for your favourite dish, and see the total calories and nutrients. Or maybe you'd rather not know!
The formulas use the CONVERT function to convert the ingredient measurement, if needed. We use the metric system, here in Canada, but all my old recipes are in Imperial measurements!
You could use similar formulas for hardware kits, if some parts are in inches, and others in centimetres.
You can get the Recipe Calculator workbook, and see details on how it works, on my Contextures site. There are versions for 1 person or 2 people.
Here are some Excel-related links that you might find useful or interesting.
Spreadsheet Day: Sunday Oct 17th was Spreadsheet Day, and the Tiller site had a fact-filled article about the big day. With cake! (Level - All)
Excel Functions: What are your favourite Excel functions? Are they in this list of top functions that Excel bloggers like to use? (Level - All)
Data Cleaning: This team of researchers tested Excel macros for cleaning data, and found that they saved hours of their time, over manually cleaning the data. They would be thrilled with Power Query too, I'm sure! (Level - All)
Also see: Previous Newsletter Issues
We've had unusually warm weather this month, so I walked to the dentist's office last week. Google Maps said it was about 3 km, and would take 35 minutes, and they were right!
I wasn't sure how crowded the sidewalks would be, and here's what the pathway looked like, at 9:30 AM. It's wide enough for a crowd, but only a few other people were using it. It made me think of Robert Frost's poem, The Road Not Taken (poem and guide).
It's not clear if the path is for people, or bicycles, or both, or what the dashed line is for. If you work for the city's planning department, please let me know!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2021/20211019ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: October 17, 2021 3:27 PM