Excel Weekly News from Contextures Oct 1, 2013

Set up an Advanced Filter + more Excel tips

In this week's Excel news, you'll see how to fill empty pivot table cells with zeros, and other tips. Thank you for reading the Excel news!

 -  Debra ddalgleish@contextures.com

Conditional Drop Down Lists

With Excel's data validation feature, you can create drop down lists on a worksheet. In this sample file, select an item in the first list, and the second drop down changes, to show only the items related to that selection. You can also add a third drop down, that show items based on the first two selections.

Click here to see the details, and download the sample file: Conditional Drop Down Lists in Excel

Find MIN IF and MAX IF From Excel Pivot Table

In Excel, you can use array formulas to find MIN IF and MAX IF. For example: What was the lowest total order price for the Carrot Bars product? What was the highest price?

An easier way to find the lowest and highest values for a specific item, is to use a pivot table. It automatically creates a list of all the products, and you can select MIN, MAX, or other functions that you want to see in the totals.

Click here to see the details, and to watch the video: Find MIN IF and MAX IF From Excel Pivot Table

How to Set Up an Excel Advanced Filter

The AutoFilter feature is quick and easy to use, and works well for most of the filters that you need to apply. It does have some limitations though, and for complex filters you can use the Advanced Filter command.

It's a little trickier to set up, but worth the effort. You can see the setup steps in my short video, and read the detailed instructions, to help you get started. You'll learn a couple of AutoFilter tricks too!

Click here to see the details, and watch the video: How to Set Up an Excel Advanced Filter

More Excel Tips

Here are a few more Excel articles that I read this week, that you might find useful:

  1. If you're an Excel expert, and have very quick hands on the keyboard, you might be an ideal candidate for the Excel Financial Modeling World Championship. There's a $30K prize, if you're interested!
  2. Abbott Katz demonstrates the advantages of using Slicers with Pivot Tables on the Data Driven Journalism blog:
  3. For a humorous peek at what other people are saying about Excel, read this week's collection of Excel tweets, on my Excel Theatre blog.
  4. Charlie Kufs starts his 6 part series on How to Write Data Analysis Reports. You can read the whole report here, with fewer cat pictures.
  5. Jon Peltier shows how to create a heat map with Excel's conditional formatting.
  6. If you need to create a list of dates that are business days, Mike Alexander shows the step by step details.

Video: Calculate Rank in Excel

To see where each score ranks in a list, without sorting them, you can use Excel's RANK function. Rank from high to low, or low to high, and see where any ties occur.

For written instructions, see Calculate Rank in Excel. To see the steps in action, please watch this short video tutorial.

Trees Turning Colours

Fall is here in Canada, but we haven't had any overnight frost yet. Still, the trees are starting to change colours, and you can see some in this picture that I took on the weekend. In another week or so, it should look even lovelier!


Contextures Products

Save time with our "Done For You" Excel products.


Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.




Privacy Policy


Contextures Inc., Copyright 2019
All rights reserved.