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 firstname.lastname@example.org
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
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
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
Here are a few more Excel articles that I read this week, that you might find useful:
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.
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!
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.
Contextures Inc., Copyright ©2019
All rights reserved.