Search Contextures Sites


Newsletter Index


Excel news
by email




30 Excel Functions in 30 Days




Contextures News

Excel Weekly News from Contextures Nov 13, 2012

Link a chart's title to a pivot filter, highlight high/low values in a chart, and other tips, in this week's Excel news from Contextures.

 -  Debra

Product Code Lookup in Date Range

A reader asked me how to pull a discount rate from a lookup table, based on the product code and date range. When a customer places an order, we need to check for any discounts that were available on the product they ordered, and in effect on the order date.
To accomplish this, I used SUMIFS and INDEX/MATCH  formulas, and you can see the SUMIFS example below.

Click here to see the details: Product Code Lookup in Date Range

Link Pivot Chart Title to Report Filter

Instead of showing a static title on a chart, you can link a pivot chart's title to the pivot table report filter cell, so it will show the selected item.
Or, use a fancier formula, to show a different title, if "All" or "Multiple Items" is showing in the Report Filter.

You can read the details here: Link Pivot Chart Title to Report Filter

Update Pivot Table From Text File

If your pivot table is based on external data, such as a text file, be careful when you are setting up the data source. 
Base your pivot table on an external range's name, instead of the range's cell address, to ensure that it updates automatically, when records are added or removed.

Read the details here: Update Pivot Table From Text File

Benchmarking Excel 2013

Charles Williams tests VBA speed in Excel 2013, to see if it is faster than previous versions. He also compares 64-bit VBA to 32-bit VBA.

You can read the speed test results in his article: Benchmarking Excel 2013

Showcase high/low values in Excel charts

In the final part of her series, Teylyn shows how to draw attention to high and low values in Excel charts by using background fill.

See the details here, and download a sample workbook: Showcase high and low values in Excel charts - Part 4

Stephen Few Workshop Review

Recently, The Science Goddess attended a Business Intelligence workshop by Stephen Few, and posted her review of the experience.

One of her comments on the course was about the Excel content:

"I liked that nearly all of his examples were built in Excel. As he pointed out, Excel is not a design tool; however, it is a data tool that nearly everyone has...and if you can make something look good with Excel, then you have no reason not to make your data shine elsewhere."

You can read the first part of the review here, and watch for the rest of the review in her upcoming posts.

Microsoft Business Intelligence Poster

Microsoft has released a giant-sized Business Intelligence Poster that you can download, and of course, Excel is prominently featured.

Joey Blue covers the poster's content on his blog, and includes a link to the download page on the Microsoft web site.

You can see his summary here: Microsoft Business Intelligence Poster

Recommended Product: Excel Dashboard Course

Remember, this is the last day to register for the online Excel Dashboard course, offered by Mynda Treacy at My Online Training Hub.

This comprehensive Excel Dashboard Course will teach you the simple techniques you can apply in Excel to make killer dashboards that will set your skill level apart from the crowd.

You can see the course details and a sample video here: Excel Dashboards Course

I highly recommend this training, to improve your dashboard skills. Go at your own pace -- you'll have 12 months of access to the videos and sample files. Or, go for one of the options that includes downloadable videos, so you can watch them offline, at any time.

Excel Dashboard

Recommended Excel Tools

They aren't free, but these Excel tools are a worthwhile investment, so please 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 the products mentioned in this newsletter, and earn a commission on the sales.

Learn how to create Excel dashboards.


Privacy Policy


Contextures Inc., Copyright 2014
All rights reserved.