Contextures News 20200512

Excel Troubleshooting Tools

May 12, 2020

Validation troubleshooting, fancy slicers, 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.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Validation Troubleshooting

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.

new sheet with list of worksheets and info

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.

new sheet with data validation documentation

There are more troubleshooting tips on the Data Validation Tips and Quirks page.

Excel Courses

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!

On Contextures

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

colour bands by date

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.

pivot table shortcut

Excel Articles

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?

Also see: My Excel Products || Previous Issues


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!

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser:
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

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: July 19, 2022 9:40 AM