Excel Troubleshooting Tools
May 12, 2020
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
Someone needed help with data validation last week, and they had a complex assortment of rules set up on multiple worksheets. Fortunately, there a few troubleshooting tools on my website. First, there's a macro that creates a list of sheets, and it shows how many data validation cells are on each sheet.
Next, you can go to any sheet that has data validation cells, and run another macro, to list all the data validation rules and cells on that sheet.
There are more troubleshooting tips on the Data Validation Tips and Quirks page.
NOTE: If you have a copy of my Excel Tools add-in, it has a command to create a list of all the data validation on the active sheet. You'll also have the option to document just the selected cells, instead of the whole sheet.
Here are 3 great Excel learning opportunities for you -- don't miss out!
1. Online Courses - Mynda Treacy has extended her sale period, so you can get a 20% discount on Mynda's Excel courses, such as Power Query, Power BI, Excel Expert and Excel for Finance. The sale ends this Thursday, May 14th, at 8 PM Pacific Time.
2. Excel Tips by the Experts - To raise funds for Covid-19 relief efforts, 25 Excel experts have contributed lessons to this Excel Tips and Tricks course. There's something for all skill levels, and the cost is only $10-$20. Registration ends on Monday, May 18th.
3. New Excel Features - To learn about one of the powerful new features in Excel, register for Jon Peltier's webinar, Excel is Evolving Are You? How to Use Dynamic Arrays. The free one-hour session starts next Wednesday, May 20th, at 11 AM Eastern time. There might be a limit on the meeting size, so if you're interested, sign up soon!
Colour Banding: Last week, I shared a tip on creating colour bands in a table, based on dates. For more details on how the formulas and conditional formatting work, see my Contextures Blog post: Create Colour Bands in an Excel Table
Mouse Wheel: On my Pivot Table blog, see more details about last week's tip for using the mouse wheel to quickly show and hide Excel pivot table details.
Here are a few Excel-related articles that you might find useful or interesting.
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. (Level - Intermediate)
Fancy Slicers: Recently, someone asked me about Mike Alexander's Excel blog, Bacon Bits. Unfortunately, it's shut down now, but you can still find its content on the WayBack Machine. One of my favourite posts was Getting Fancy with Your Excel Slicers. The sample file is still there too - click the link near the end of the post. (Level - All)
Excel Humour: See what people said about Excel recently. What snacks do you eat while doing your Excel work at home?
It snowed here yesterday - not much, but not the kind of weather we want in mid May! Our planting season starts this weekend, when we celebrate Victoria Day, so I hope it warms up by then.
My "stay home" experiment last week was sprouting chick peas. The sprouts did well, and I've planted a few in the garden, under a good layer of soil, and a couple of plastic covers. Do you think they'll survive the cold weather, and eventually grow bean pods? Some people eat the sprouts too, but I wasn't brave enough to try that!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20200512ctx.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: May 11, 2020 10:00 AM