Contextures

Contextures News 2020-09-22

Excel Check Box Trick

September 22, 2020

Dynamic lists, check box trick, 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 October 6th.

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

Dynamic Lists - FILTER

In Excel 365, you can use the new FILTER function to create dynamic lists, based on your criteria. To see 3 easy FILTER examples, watch my new "Get Started" video.

In the video, the first example creates a unique list of cities in the selected region, sorted A-Z. If no cities are found for that region, the cell shows two hyphens.

  • =SORT(UNIQUE(FILTER(Sales_Data[City], Sales_Data[Region]=B4, "--")))

Another example shows how to change that formula, and use 2 criteria - region and price

  • =SORT(UNIQUE(FILTER(Sales_Data[Product],
    (Sales_Data[Region]=B4) * (Sales_Data[SalePrice]>B7)
    , "--")))

See more FILTER function examples, and get the sample file, on my Contextures site.

Check Box Trick

There's an Excel Order Form tutorial on my Contextures site, with written steps and a video that show how to set it up from scratch. There's a new feature now - a "Bill To" check box.

After you fill in the Ship To section, click the check box to add a check mark, and a macro copies the shipping address to the Bill To section. If you clear the check box, the Bill To section is cleared too.

Get the full setup details, and the completed Excel file on the Order Form page of my Contextures site.

check box on order form runs a macro

Excel Articles

Here are a couple of Excel-related articles that you might find useful or interesting.

Spreadsheets: The Toronto Excel User Group is hosting a free online event on Oct. 14th, to celebrate Spreadsheet Day (Oct. 17th). There's a great lineup of speakers, and "lots of surprises" (Level - All)

Ignite 2020: Microsoft Ignite is online this year, and it's free! Sessions start today (Sept 22) and go till Sept 24th. Check the session catalog for Power BI and Excel topics. (Level - Int/Adv)

Also see: My Excel Products || Previous Issues

Fall Colours

Happy first day of Autumn (or Spring)! Some trees are already showing their fall colours, but most are still green. Our little yew tree is covered with bright red berries this year -- usually it just has a few. And yes, those pretty berries are edible, but the bad news is that the seeds inside them could kill you!

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20200922ctx.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

 

Last updated: October 7, 2020 4:13 PM