Contextures

Contextures News 2021-10-19

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.

Excel Checklist

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.

Inspect Workbook

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.

If the workbook is full of Threaded Comments and Notes (old-style comments), there are macros on my site to print those for you. Check the Threaded Comment Macros page, and the Comment Macros page.

Convert Measurements

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.

formula to calculate multiplier

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.

Excel Articles

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

The Road Not Taken

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!

weekly photo

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.

Debra Dalgleish
dsd @contextures.com
ctxdebra @gmail.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

contextures newsletter info

 

Last updated: October 17, 2021 3:27 PM