Search Contextures Sites
Excel Weekly News from Contextures Dec 4, 2012
Pick dates with a scroll bar, link a pivot chart to another table, and other tips, in this week's Excel news from Contextures.
- Debra firstname.lastname@example.org
Choose Report Dates With Excel Scroll Bar
In Excel, you can add a Scroll Bar, and use it to change the values in worksheet cells.
In this example, the Scroll Bar will change the end date in a summary report, so you can scroll through the months, and see the data change. This technique does not require any programming.
Read the instructions, and download the sample file on my Contextures website: Select Date with Excel Scroll Bar
Or, see the overview on my Contextures blog: Choose Report Dates With Excel Scroll Bar
Create Table Combinations With MS Query
What's a quick way to combine the items in two table? For example:
- Table A has 3 items - Sugar, Coffee and Milk.
- Table B has 2 items - Cans and Sticks.
- Table C combines all the items - Sugar - Cans, Sugar - Sticks, etc.
You could do this with programming, but I used Microsoft Query to created the paired items table.
You can read the overview on my Contextures blog: Create Table Combinations With MS Query
Or, read the detailed instructions on my Contextures website: Cartesian Join in Excel Using MS Query
Change Pivot Chart Source Data
If you create a pivot chart, you might spend a long time setting it up, with specific formatting and design settings. Instead of creating a similar pivot chart from scratch, you'd like to copy the existing pivot chart, and link it to a different pivot table.
Unfortunately, there is no built in way to quickly connect a pivot chart to a different pivot table. You can't edit the source data setting.
The good news is that you can follow a few simple steps, to copy or cut an Excel pivot chart and connect it to a different pivot table
Read the steps on my Excel Pivot Table blog: Change Pivot Chart Source Data
Excel 2013 Add-in -- GeoFlow
It's not available yet, but Microsoft previewed a new add-in for Excel 2013 -- GeoFlow -- at the recent SharePoint Conference (SPC). This addin is designed for geospatial analysis, and is integrated with PowerPivot.
Patrick Guimonet was at the conference, and he has posted some videos from Microsoft's demo. Patrick's blog is in French, but the videos are in English, and you can get a peek at the awesome features in this add-in.
I haven't seen a release date listed anywhere, so we'll have to watch for further announcements from Microsoft.
Dashboard Compares You to Others
On his Coding Horror blog, Jeff Atwood shows a dashboard report that his energy company sent to him. In the report, it compares his electricity usage to similar homes and to the most efficient similar homes.
Jeff was motivated by the report, and changed a few things, in an effort to reduce power consumption. You might find some inspiration too, with ideas that you can use in your Excel dashboards.
You can read the article here: For a Bit of Colored Ribbon
Holiday Sale on Excel Courses
From 6 AM on December 3 until midnight on December 4th (tonight!), Chandoo is having a holiday sale on two of his most popular Excel courses. If you've been thinking about signing up, this is the perfect day!
- Excel School Dashboards program -- Discount: $30
- Excel School + VBA + Dashboards program -- Discount: $50
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.