Search Contextures Sites
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 firstname.lastname@example.org
Product Code Lookup in Date RangeA 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 FilterInstead 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 FileIf 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.
Recommended Excel ToolsThey 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.
- 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
- Excel Pivot Table Intro Course
Note: I am an affiliate for the products mentioned in this newsletter, and earn a commission on the sales.
Contextures Inc., Copyright ©2014
All rights reserved.