I am working with a client to create dashboards to summarize data. Data is appended on a weekly basis, so any charts that show trending data must either be manually updated to include the new data (not workable), or the range somehow magically updated. As we are working in Excel 2007, I have used the excellent Table functionality as the ability use table references to automatically update formulas to include the new data works very nicely.
As it turns out, any chart that is based on data within a table, even if the table functionality was added to the data after the chart was drawn, causes the chart to automatically include the new data when the table expands.
When you're looking at the chart, this isn't obvious - the series names for the data don't reflect this - they just get automatically updated with the new cell references:
=SERIES("Series name",Sheet1!$A$69:$A$79,Sheet1!$K$69:$K$79,3 increases to
=SERIES("Series name",Sheet1!$A$69:$A$80,Sheet1!$K$69:$K$80,3 ) when a new row is added.
Even if you try to manually change the series references to a table reference:
=SERIES("Series name",Sheet1!$A$69:$A$80,Sheet1!TableName[TableColumn],3), Excel recognizes the table reference, but then renames the series back again anyway.
I find this kind of behavior annoying for two reasons - I should explicitly chose if I want a chart to expand a range when I have chosen data based on cell references. Secondly I spent a while trying to use the methods you needed to in Excel 2003 to create dynamic charts, as I assumed (I know..) that the chart was not going to update because of the use of absolute cell referencing.
If you want to know more about how you can create dynamic charts without table referencing, nip over to Jon Peltier's website.
If you're intrigued by these posts on dashboards, reporting, and charts in Excel, I have the perfect one-day training for you - Jon Peltier and I are hosting a Dashboards in Excel seminar on June 2nd. For more details, see here.
Showing posts with label Dashboards. Show all posts
Showing posts with label Dashboards. Show all posts
29 March 2010
Dashboard seminar - mark your calendars
I'm sure most readers of my blog will know Jon Peltier's business, and his excellent Excel/charting blog: Peltier Technical Services. Jon and I are teaming up to present a day-long training session on Excel Charting and Dashboarding. This training will take place on Wednesday, June 2, 2010, at the DoubleTree Hotel in Westborough, MA, in the Route 495 corridor near the Mass Pike.
The training balances Jon’s highly technical expertise in Excel charting and deep understanding of VBA, with my detailed understanding of dashboard design and the business decisions that drive these projects.
Tuition for the all-day, hands-on program is $300, with discounts for early registration. This fee includes meals and breaks during the day plus all course materials. Participants need to bring their own laptops.
For more details, or to register for the class, visit the Excel Charting and Dashboarding home page.
Program Outline
Morning Session
Dashboards 1 – Alex
- Introduction
- Data Quality
- Dashboard Design
- Dashboard Elements
Advanced Excel Charting 1 – Jon
- Appropriate Chart Data
- Chart Types
- Formatting
- Combination Charts
- Dynamic and Interactive Charts
Afternoon Session
Advanced Excel Charting 2 – Jon
- Conditional Chart Formatting
- Error Bars
- Custom Chart Types You Didn’t Think Excel Could Make
Dashboards 2 – Alex
- Excel as a Dashboard Platform
- Dealing with Excel Versions
- Sparkline Add-ins
- Wrap Up
Program Details
Wednesday, June 2, 2010
8 am to 5 pm
8 am to 5 pm
DoubleTree Hotel
5400 Computer Drive
Westborough, MA 01581
5400 Computer Drive
Westborough, MA 01581
Tuition includes conference materials, food, beverages
- $300 base price
– $250 before April 15
– $275 before May 8
- $300 base price
– $250 before April 15
– $275 before May 8
23 February 2010
Tableau Public. Cost of raising a child (controlling for inflation)
The Guardian newspaper in the UK often posts interesting data for its readers to mess with and comment on. The latest on the DataBlog ("where facts are sacred") is a data set showing how the cost of raising a child has increased in the UK by 43% since 2003.
The data is from an insurance company, Liverpool Victoria. In neither this data article or the main editorial is the method of data collection described. It's essential to describe this - A lack of visibility into methods, however reliable the reporting source, should quickly lead you to question the findings.
The other issue is that the costs don't seem to have been adjusted to changes in the value of currency (be that through inflation or other methods). Any time monetary values are shown on a time-axis spanning more than a few months (under normal inflation values), the values should be normalized to a single point.
This is my take on the data using Tableau Public, I have presented both the non adjusted costs, and the costs adjusted using the UK's consumer price index. The best normalization probably would be to median wage after tax, as these truly reflect the ability to pay for raising a child, but the CPI will at least give a more balanced view. You can see that the actual increase is about 22% from 2003, and that the only real contributors to this are childcare and education costs because they have increased the most above CPI, and they are the majority of the expenses. The problem with using CPI is that if you used a fine enough detail (e.g. the CPI of providing childcare), the results should, of course, be flat. This is why choosing how to deal with costs and time is far from straightforward.
Concerning my continued engagement with Tableau Public - it took a while to get the charts how I wanted them - I'm still on an enjoyable learning curve with the new software. There are a few bugs to iron out - for example it doesn't handle null values in an expected way (treats them as zero) - maybe that's still higher up on my learning curve..
The data is from an insurance company, Liverpool Victoria. In neither this data article or the main editorial is the method of data collection described. It's essential to describe this - A lack of visibility into methods, however reliable the reporting source, should quickly lead you to question the findings.
The other issue is that the costs don't seem to have been adjusted to changes in the value of currency (be that through inflation or other methods). Any time monetary values are shown on a time-axis spanning more than a few months (under normal inflation values), the values should be normalized to a single point.
This is my take on the data using Tableau Public, I have presented both the non adjusted costs, and the costs adjusted using the UK's consumer price index. The best normalization probably would be to median wage after tax, as these truly reflect the ability to pay for raising a child, but the CPI will at least give a more balanced view. You can see that the actual increase is about 22% from 2003, and that the only real contributors to this are childcare and education costs because they have increased the most above CPI, and they are the majority of the expenses. The problem with using CPI is that if you used a fine enough detail (e.g. the CPI of providing childcare), the results should, of course, be flat. This is why choosing how to deal with costs and time is far from straightforward.
Concerning my continued engagement with Tableau Public - it took a while to get the charts how I wanted them - I'm still on an enjoyable learning curve with the new software. There are a few bugs to iron out - for example it doesn't handle null values in an expected way (treats them as zero) - maybe that's still higher up on my learning curve..
7 February 2010
Data visualization challenge: my dashboard design
Finally we get to the choices I made for my dashboard entry into Chandoo's data visualization challenge. The challenge already directed us to make the dashboard focused on the two year performance of the sales people. I'll break this post into the five or so parts of the (single screen) dashboard.
Easily overlooked, but vital, is the title of the dashboard - what is it, what time period does the data cover? Under the title is the most expensive part of the screen real estate - the primary information must go here. If I'm a senior manager looking for sales person information, my first questions will always be: who sold the most, how did those sales vary over my chosen time period, how much was sold compared to what was expected?
From this display we see immediately who sold the most and the least - give the dollar values, they will be needed - the bar chart gives us information about each person's contribution to the sum. The red markers warn of poor sales performance. The sparklines provide us with time trending information, so often missed from data displays. For data that has some sort of periodicity (as sales data tends to), it can be useful to provide a moving average that better reveals overall trends - for example, the moving average is better at showing that everyone experiences a drop in sales part way through the period, but Hansolo's drop was much more abrupt than James Kirk's.
The Budget/Actual shows that only Hansolo met budget, presumably due to the recovery he experienced in the last six months. By not scaling the bars to be all 100%, we provide additional information about what the sales targets were per sales person. As this makes it difficult to compare sales to target across the sales force, the variance to budget bars clarify this.
The sparklines in the top section are scaled differently to each other - otherwise trends are hidden for the sales people with lower revenue. However it would be easy to predict that the user of the dashboard would want to see the information on one chart. The chart provides this, and an in an effort to minimize colors I added a drop down box to highlight one person compared to the other three. When the manager asks "Why was Hans Solo's performance better than the others?" this chart helps answer that.
I feel that the headlines section is an often overlooked part of a dashboard- 3D pie charts and revving speedometers are sexy, words are not. Often though, pithy statements can make a dashboard much more useful and in 20 seconds can provide you with the most important take-home messages. They are especially great in dynamic dashboards, as long as the information regularly changes.
%20of%20Fullscreen%20capture%201202010%20103140%20AM.bmp.jpg)
Finally we begin to get to the other measures that perhaps (hopefully) help us understand the sales issues. The coloration on the data table (again, it is important to sometimes show values) helps us understand the areas that sales people sold in - James Kirk sold almost exclusively in the south, Luke sold across the country. The map provides this information in a slightly different way - for a given region, who sold the most?
The map also provides information about the states that are in each region - anyway that you can make a dashboard as rich as possible is great, but notice that as this is not the most important information, the region boundaries are just a thicker gray, not a highly colored boundary that detracts from the bars.
The bottom two displays are formatted in the same way, so here is just the company size visualization. The stacked bar shows the proportion of sales to each size company - Chewbacca sells to all sizes, Luke is much more focused on enterprise sales. The bars underneath show for a particular size company, how are the sales distributed - again, important, because even though Chewabacca sells to enterprise, his overall contribution to the sales for that size company is completely minimal. That's it - thank you again Chandoo, for the opportunity to create this dashboard.
If by some amazing chance you've made it all the way through this post and are still reading, I'd like to remind you that Data Driven Consulting can help your organization create actionable, strategic, highly useful dashboards and reports that will make your business more successful.
From this display we see immediately who sold the most and the least - give the dollar values, they will be needed - the bar chart gives us information about each person's contribution to the sum. The red markers warn of poor sales performance. The sparklines provide us with time trending information, so often missed from data displays. For data that has some sort of periodicity (as sales data tends to), it can be useful to provide a moving average that better reveals overall trends - for example, the moving average is better at showing that everyone experiences a drop in sales part way through the period, but Hansolo's drop was much more abrupt than James Kirk's.
The Budget/Actual shows that only Hansolo met budget, presumably due to the recovery he experienced in the last six months. By not scaling the bars to be all 100%, we provide additional information about what the sales targets were per sales person. As this makes it difficult to compare sales to target across the sales force, the variance to budget bars clarify this.
I feel that the headlines section is an often overlooked part of a dashboard- 3D pie charts and revving speedometers are sexy, words are not. Often though, pithy statements can make a dashboard much more useful and in 20 seconds can provide you with the most important take-home messages. They are especially great in dynamic dashboards, as long as the information regularly changes.
%20of%20Fullscreen%20capture%201202010%20103140%20AM.bmp.jpg)
The map also provides information about the states that are in each region - anyway that you can make a dashboard as rich as possible is great, but notice that as this is not the most important information, the region boundaries are just a thicker gray, not a highly colored boundary that detracts from the bars.
The bottom two displays are formatted in the same way, so here is just the company size visualization. The stacked bar shows the proportion of sales to each size company - Chewbacca sells to all sizes, Luke is much more focused on enterprise sales. The bars underneath show for a particular size company, how are the sales distributed - again, important, because even though Chewabacca sells to enterprise, his overall contribution to the sales for that size company is completely minimal. That's it - thank you again Chandoo, for the opportunity to create this dashboard.
If by some amazing chance you've made it all the way through this post and are still reading, I'd like to remind you that Data Driven Consulting can help your organization create actionable, strategic, highly useful dashboards and reports that will make your business more successful.
Subscribe to:
Posts (Atom)
