MicroCharts for Excel 2000, XP and 2003

Neil Scanlon

Description

MicroCharts for Excel 2000, XP and 2003
This tutorial explains how to start using MicroCharts to help you visualizes time series
data with Microsoft Excel 2000, XP and 2003. The best way to understand MicroCharts is
to start using it. The following examples shows how easy it is to incorporate MicroCharts
in your Excel reports.
Introduction
MicroCharts enables you to understand complex relationships in your data with
sparklines. Invented by information design guru, Edward Tufte, sparklines are tiny charts
with an intensity of visual distinctions comparable to words and letters. When placed in an
Excel cell, sparklines dramatically improve data visualization for fast, effective parallel
comparisons.

A quick example:
The following shows a typical budget report:

Add a column with sparklines and you can immediately compare the categories over time
whilst still having the detailed data available: BonaVista Systems MicroCharts for Excel 2000, XP and 2003

In a classical budget report chart, you only see one chart at a time. This makes it difficult
to identify trends and similarities. Sparklines help you compare trends and patterns at a
glance using minimal screen space.

A single cell showing one figure consumes 1000 pixels of screen real estate. A sparkline
consuming the same amount space could visualize 20 values! Since MicroCharts are
created from fonts, you can adjust the size without losing quality.

MicroCharts and Excel Dashboards ...

Sujets

• Xpress
• Dialogue territorial
• Dashboard Confessional
• St. Petersburg
• Hit-parade
• Cellieu

Informations

• Publié par: Udwue
• Nombre de lectures: 298
• Langue: English
• Poids de l'ouvrage: 1 Mo

Extrait

MicroCharts for Excel 2000, XP and 2003 This tutorial explains how to start using MicroCharts to help you visualizes time series data with Microsoft Excel 2000, XP and 2003. The best way to understand MicroCharts is to start using it. The following examples shows how easy it is to incorporate MicroCharts in your Excel reports. Introduction  MicroCharts enables you to understand complex relationships in your data with sparklines. Invented by information design guru, Edward Tufte , sparklines are tiny charts with an intensity of visual distinctions comparable to words and letters. When placed in an Excel cell, sparklines dramatically improve data visualization for fast, effective parallel comparisons. 

A quick example: The following shows a typical budget report:

 

  Add a column with sparklines and you can immediately compare the categories over time whilst still having the detailed data available:

oCharts ems Micrts ayStsoBaniVl 2000, for Exce00 3PXa dn2 

 

© BonaVista S ystems

 

page 2

A single cell showing one figure consumes 1000 pixels of screen real estate. A sparkline consuming the same amount space could visualize 20 values! Since MicroCharts are created from fonts, you can adjust the size without losing quality.   MicroCharts and Excel Dashboards MicroCharts is perfectly suited for management dashboards and makes Excel a first choice dashboard tool. You can build your Excel dashboard with MicroCharts’ bullet graphs and sparklines. Stephen Few invented the bullet graph as a replacement for dashboard gauges and speedometers. Its linear design provides a rich display of key performance figures in the smallest of spaces.

In a classical budget report chart, you only see one chart at a time. This makes it difficult to identify trends and similarities. Sparklines help you compare trends and patterns at a glance using minimal screen space.

ed:    present sio tfnemrtaoi n M ciorhCrastf roonaVista Systems dna3002 teL   c s’ xc E 2el0,00P  X  sotaodrnotsd mere tompaashbwo dtil woh ofni eltow hteraorh uc m

page 3

 

a “conventional” dashboard  

A dashboard built with MicroCharts and Excel The second screenshot shows you a dashboard built with Excel and MicroCharts. When you have a look at the dashboard, even before you start to interpret what this dashboard contains, you can see that a lot more information is available in a format that you can analyze.    

© BonaVista S ystems

 

B

BonaVista S ystems MicroCharts for Excel 2000, XP and 2003 Getting Started Prerequisites This tutorial is based on the data of the worksheet Tutorial - Getting Started.xls found in the <Your Program Drive>:\Program Files\BonaVista Systems\MicroCharts\Samples directory. The data set is the actual performance measures for the last 12 months of an airline company. You could also do the tutorial with any other that shows a set of measures or categories over time.

The first step in adding the MicroCharts is to select the data series:

Having selected the required data, select the Insert MicroCharts option on the MicroCharts toolbar:

  This will display the Chart Type dialogue, from which you can select the type of MicroChart you want to insert:

  The dialogue lists the main chart types on the left hand side. When you select one of the main chart types a number of preconfigured chart appears on the right of the dialogue.

© BonaVista S ystems

 

page 4

detarof alumliw bel ns iteer d: ht eiMrcCoahtr sand their  associ

  You now have inserted MicroCharts Line charts. The MicroCharts formula {CMicroLine} is located in the cell N1. It stores all MicroCharts parameters. In order to edit and format the MicroCharts you can double-click or right-click to display the Right Mouse Button menu with the additional MicroChart Options (Format Chart, Chart Types, etc):

  By default the source data is the area you selected when you first started and the charts will be inserted at the end of each row, with the formula being inserted at the top of this column. The MicroCharts formula stores the chart format settings and optionally returns an axis or a label. To accept these defaults click OK , the MicroCharts will then be inserted:

© BonaVista S ystems

page 5

To change the chart type right click on any of the charts and select the Chart Type menu option to display the Chart Type dialogue:

ctrae icrmfo sathtiwmos eb ep tsready formatted dec ahtr sra elare   P 0320 urignfco ,0002 le dna PX artsroCh Exc forS sysiatM cietsmBVano l cotaoi nna dot position where oy  w ot unoc mrifhe tou se rctada eui lagoned  shtayedisplallo to o trahc na noitpk iccld diA . OK   example  For our ht eiLens lece t ot bm toats he tolai.eugt fod ehp ex helrief a bepra npataoilpnath n he Ws.ngtiet detceles era ese

2 dn 300 ,00a PXiMrcCoahyStsme sExcel 20rts for aniVts aoB

© BonaVista S ystems

page 6

 

  You now have a MicroCharts Line chart with a normal band. You may wish to further format the chart to fit in with other aspects of the report you are creating, for example colours, cell sizes etc. To format the chart either right-click a chart cell and select the Format Chart menu option or double-click the formula or the charts to quickly display the Format Chart dialogue:

  This dialog box allows you to fine-tune your chart formatting. It’s content is dependent on the type of chart you are editing. Select the Periods and Scale tab:

  ’ Select the Line option on the left and then the ‘ Line with Normal Band on the right and click OK to apply:

BonaVista S ystems

MicroCharts for Excel 2000, XP and 2003

  We want to change the aspect ratio (width vs height) of the chart so that it fills the cell a little more. On this tab set the Aspect ratio to 2 and click OK ( If you want the chart to automatically fit into the cell, un-check Aspect ratio):

You now have created a Micro line charts with a normal band and an aspect ratio of 2. You can further modify the charts size by changing the font size for the cell.

© BonaVista S ystems

 

page 7

 2003 fos Exr oCcrrthaPX ,dna  lec0002oBana SyVists Mistem

  This is because MicroCharts is based on a special font. If you increase the font size the selected chart will increase in size accordingly:

  Now that you have created a simple report you can go on to combine the various chart types and formatting options to produce concise and informative dashboards and reports.

page 8

 

© BonaVista S ystems

BonaVista S ystems MicroCharts for Excel 2000, XP and 2003 A more advanced example – Building Graphical KPI Tables Prerequisites  This tutorial is based on the data of the worksheet Tutorial – KPI Table.xls found in <Your Program Drive>:\Program Files\BonaVista Systems\MicroCharts\Samples. This tutorial explains how you can visualize time series KPI data with MicroCharts and how you can build KPI graphics like the bullet graph and KPI Icons. The basis of the tutorial is a collection of actual and target performance measures for the last 12 months (November 1, 2004 through the current date of November 15, 2005) for a fictional airline:

To illustrate how you might use MicroCharts when presented with data like this we will build a table to incorporate some of the key features that MicroCharts has to offer. We start with a table where the KPIs are grouped into meaningful groups like this:

© BonaVista S ystems

 

page 9

BonaVista S ystems MicroCharts for Excel 2000, XP and 2003 The columns refer to the source table as defined: Column Description Nov 05  Actual for Nov 05 PY%  % of Actual for Nov 05 compared to Actual for Nov 04 YTD  Actuals Year to date Target%  Actual for Nov 05 / Plan for Nov 05 * 100%

  Adding the sparklines Whilst this has started to give structure to the data, a sparkline can be added to help summarise some of the source data and present it as trendline. Begin by selecting the source data:

Now using the MicroCharts Toolbar, select the Insert MicroCharts option, this will display the Chart Type dialogue:  

Select the Line option and then the Line with Normal Band detailed option (as shown above). Click OK and the Source Data and Chart Location dialogue is displayed:

© BonaVista S ystems

 

page 10

rcCoahtrtsme siMcel 2000s for ExstViSya naBo and, XP3  200

page 11

 

© BonaVista S ystems

 

Once you have selected the required destination cells for the charts and the formula click OK and the charts will be inserted:

By default the Line charts would be placed at the end of the selected rows but we want them to be placed on the KPI summary sheet, so go to the sheet where you want the charts to appear and select the appropriate location: