Search Contextures Sites
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 firstname.lastname@example.org
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.
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.
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.
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 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.