Search Contextures Sites

 

Contextures
Newsletter Index

 

Contextures
Excel news
by email

 

 

 

30 Excel Functions in 30 Days

 

 

 

Contextures News

Excel Weekly News from Contextures Dec 11, 2012

Change chart title with scroll bar, budget template, and more tips, in this week's Excel news from Contextures.

 -  Debra ddalgleish@contextures.com

Change Chart Title With Excel Scroll Bar

Last week, I shared a tip for adding a Scroll Bar, and using it to change the values in worksheet cells.

This week, I've added a chart to the example, and clicking on the scroll bar will automatically change the chart's title.

pivot subtotals video

Click here to see the details: Scroll Bar Changes Excel Chart Title

For details on setting up the scroll bar, see the overview on my Contextures blog: Choose Report Dates With Excel Scroll Bar

Budget Template Update

I've updated the Excel budget template on my website, and you can download the sample file to see how it works. It won't teach you how to set up a budget, but you can change the budget categories in the sample file, to adapt it for your own needs.

There is conditional formatting in the reports, to show which months are actual numbers, and which are forecasts.

pivot subtotals video

It's a very simple budget layout, where you can enter forecast and actual numbers, then see the year to date totals, and the variance.

To see the screen shots and to download the sample file, click here: Excel Budget Template

Flexible Links to Pivot Table Data

To extract data from an Excel Pivot Table, you can use the GetPivotData function.

Then, you can replace the default text values in the formula with cell references. This makes the formula flexible, and it will show a different result, based on the values in the linked cell.

Read the steps on my Excel Pivot Table blog: Flexible Links to Pivot Table Data

Click here for more information on the GetPivotData Function.

Create Dynamic Charts with Non-Contiguous Data

On Chandoo's blog, Sajan shows how to create a dynamic chart from non-contiguous data. He also uses the N function in an interesting way -- it creates an array from the results of an OFFSET function.

To read the details, click here: Creating Dynamic Charts with Non-Contiguous Data

Get to the Point

If you're working on Excel dashboards, you'll find Dr. Nicolas Bissantz's latest blog post interesting. He compares dashboards to pictures, and discusses what you can see up close, and what you can see when looking from further back.

"If all image components are treated equally, it is hard to see the message. Today, we'll talk about the punctum - in short, the point - and how to get to it."

Does everything in your dashboard look the same, or does the key message stand out?

He sums it up at the end of his article: " In management reports, we don't need "mega" anything. Punctum from afar; explanatory details up close."

To read the details, click here: Punctum: In short...to the point!

Add Spacer Column in Pivot Table

On his Bacon Bits blog, Mike Alexander shares a tip for adding spacer column in a pivot table.

http://www.contextures.com/images/news/pivotaddblankcolumn.png

Mike created a calculated field, and added that to the pivot table, then formatted the new field. If you don't want to create a calculated field, you could just add another copy of an existing value field. Then, change its heading, number format, and fill colour.

You can read the article here: Add Column Spacing In A PivotTable

And for the PowerPivot version, see Ken Puls' blog: Creating a Spacer Column in a PowerPivot PivotTable

Free Floating Data With Camera Tool

In his Spreadsheet Journalism blog, Abbott Katz shows an interesting use of the camera tool, using it as labels on an interactive map. You can select either Temperature or Humidity from a drop down list, and that information is displayed.

To read the details, click here: The Camera Tool, Part 1: Picturing Free-Floating Data

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.