Search Contextures Sites


Newsletter Index


Get weekly Excel tips from Debra




30 Excel Functions in 30 Days




Contextures News

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

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.

pivot subtotals video

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.

pivot subtotals video

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

Contextures Products

Save time with our "Done For You" Excel products.


Note: I am an affiliate for the products mentioned in this newsletter, and earn a commission on the sales.




Privacy Policy


Contextures Inc., Copyright 2019
All rights reserved.