Excel Troubleshooting Challenge
December 1, 2020
Excel for the holidays, troubleshooting challenge, 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 in two weeks, on December 15th.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
The holiday season might be different this year, but I hope you find things to celebrate!
If you'd like a bit of help from Excel, there are planners, Advent calendars, Christmas trees, and other sample workbooks on my Contextures site.
I've updated the Excel Holiday Planner for 2020, so get the new version, if you've downloaded it in past years.
Here's an Excel troubleshooting challenge for you, and thanks to Alex Blakenburg, who sent it. His solutions are below the screen shot.
This screen shot shows the sample data, and you can click here to download the sample file.
There is a formula in column C, so you can test with empty strings: =IF($C$1="x","","test")
For these solutions, add a new column in the table, with one of the following formulas. That column can be filtered for FALSE, to find rows where there is a problem with the codes.
Older Versions: In older versions of Excel, without the new spill functions, you can use this formula in cell G4. It uses cell references, but you could use table references instead:
The formula finds the first code in the row, and compares its count, to the count of all the non-blank code cells in that row.
Excel 365: In Excel 365, you can use this spill function formula:
The formula creates a unique list of non-blank codes. The count of those codes should be 1.
Here are a couple of Excel-related articles that you might find useful or interesting.
YouTube: If you're looking for Excel videos on YouTube, use the Search box on your favourite channel pages, like Contextures, instead of the general search box at the top. This video shows the steps. (Level - All)
Spreadsheets - The Essential Spreadsheets guide, on the University of York site, shows how to do things in Excel and Google Sheets, with written steps, videos and sample files. It's interesting to see the comparisons! (Level - Intro/Int)
At the beginning of last week, we had a record-setting snowfall for November 22nd, with 19.4 cm piling up in our back yard. It was the day before our current lockdown, so people were frantically doing their errands, despite the weather. Fortunately, we were able to stay home and enjoy the winter wonderland from indoors. The snow is gone now, but there's more to come, I'm sure!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20201201ctx.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: November 30, 2020 2:43 PM