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!
In addition to all the free
Excel tips and tutorials, there are other Excel tools that you can
invest in. To learn more about the products listed below, click
on the links to take a look at their features, and decide if they're
right for you.
Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.
Contextures Inc., Copyright ©2017
All rights reserved.