Contextures

Contextures News 20180605

Excel Fancy Drop Downs

June 5, 2018

Styles list, customized drop down lists, 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: You'll get the next newsletter on June 19th -- we're on the summer schedule now.

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

Fancy Drop Downs

To make data entry easier, you can create drop down lists in worksheet cells. To keep the lists from getting too long, add a fancy feature -- dependent drop down lists.

Select an item from the first drop down, and the second drop down list is automatically customized -- it only shows items from that category.

dependent drop down lists

  • The main drop down (Product Type) is a basic list, created with Data Validation. It's based on a named range - Produce.
  • The dependent drop down (Item) uses a formula as its source: =INDIRECT(B3)

The INDIRECT function creates a reference to a range, based on what you selected in cell B3.

  • In cell C3, the drop down will show items from the named range, Fruit.
  • In cell C4, it will show items from the named range, Vegetable

Get the sample file from my Dependent Drop Downs page, and watch my new step-by-step video, that shows how to set this up.

See the Styles

There are lots of built-in Excel styles (pivot tables, tables, slicers), and you can create custom styles too.

To quickly list all the pivot table styles in a workbook, with a colour sample, download my Style Macros workbook, and run the macro. It adds a new sheet to the workbook, with the list of style names and the header and inside border colours.

There are more style macros on my website too. For example, list all the pivot tables, with their style info.

pivot styles list

Excel Articles

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

Data Viz - New free online course, Data Visualization for Storytelling and Discovery, by Alberto Cairo. Runs 4 weeks, June 11 - July 9, 2018. Lessons are recorded, so watch any time, but try keep up with the weekly schedule. (Level - Int/Adv)

Budget - On the TechRepublic blog, Susan Harkins shows 3 ways to highlight key information in an Excel budget worksheet. She uses the Subtotal feature in the final example. I'd use a pivot table instead! There are a couple of budget workbooks on my site, if you'd like more examples.(Level - All)

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

Computer Games

I cleaned more shelves in my office, and found this Rogue disk -- it was one of my favourite games from our first Macintosh computer, long ago. Don't tell anyone, but I spent way too many hours wandering through those dungeons, looking for potions and magic wands! The graphics weren't fancy, but it was fun. Do you have any memories of old-style computer games?

weekly photo

That's it for this week! If you have any comments or questions, send me an email.

NOTE: For the online version, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2018/20180605ctx.html
I'll also post any article updates or corrections there.

Debra Dalgleish
dsdalg @ gmail.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.

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: June 1, 2018 2:27 PM