Contextures

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.

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.

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

Sprouts

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: 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.

Debra Dalgleish
dsd@ contextures.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

 

Related Links

Createsa list of sheets

Data Validation Tips and Quirks

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: May 11, 2020 10:00 AM