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
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:
- 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!
- Abbott Katz demonstrates the advantages of using Slicers with Pivot Tables on the Data Driven Journalism blog:
- 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.
- 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.
- Jon Peltier shows how to create a heat map with Excel's conditional formatting.
- 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!
Recommended Excel Tools
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.
- Contextures PivotPower Premium Add-in
- Contextures 30 Excel Functions in 30 Days
- Excel Online Course
- Excel Charting Tools
- Excel Dashboard Kits
- Excel Project Management Templates
- Excel VBA School
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.