Effective Management Reports? Interview with Rolf Hichert

Professor Rolf Hichert is the foremost specialist for information design for financial professionals in the German speaking world. His seminars have been attended by thousands of CFO’s, financial controllers in Germany, Austria, Switzerland and the UK. Recently my friend Martin from INTALIGN had the pleasure to interview Professor Dr. Rolf Hichert about his view on what makes an effective management report:

Professor Hichert, your extensive research claims management reports are often ineffective and largely misunderstood, mainly because they are simply never read. Yet we all continue to create these complex documents. Why is that?

Hichert: Reading management reports is enormously time-consuming, in a time when our corporate culture is particularly time-poor. Concise messages are buried, or missing altogether, phraseologies can be confusing, and notation is often not uniform. Frequently, those who do understand the reports have had prior knowledge of its contents, so they are simply reinforcing what they already know. Financial controllers in particular are frequently frustrated by what they perceive to be a lack of understanding, and interest in their reports, despite the tremendous amount of work they may have put into creating a very comprehensive document. I liken the experience to a newspaper editor, who writes a long, in-depth story and then complains about lack of interest by his readers.

What is the main objective of a management report?

Hichert: Reports need to convey a comprehensive message, otherwise they function merely as a statistic or a reference book, comparable to a telephone directory. “To report” means that the creator of the report has taken a certain position and has something of value or novel to say. This may be in the form of statements, explanations, conclusions or recommendations. So, according to this definition, many management reports are not actually reports at all, but merely an exercise in pontification.

Who should be recipients of reports?

Hichert: Structured reports are usually directed to the executive level, the managing directors, and board members. We’re all contributing to information overload however, and there is a considerable increase in the tendency to now supply these reports to middle management, and even trickle them down to all the company’s employees. Other business partners such as banks and investors also have access or are supplied with reports on a regular basis.

We hear a lot of managers complain about the volume of management reports – is that a common problem?

Hichert: Criticism about the extent and thickness of management reports probably dates back to the first ever management report itself. I come across many companies in which senior management are buried under monthly reports containing over 100 pages, an unsurmountable monthly feat to read. And then there are organizations where reports contain only 10 pages or less. The reasons for the extent of management reports are varied; if a report is targeted at a large diverse group for example, it inevitably becomes more extensive as it has to cover a wide variety of needs. In addition, volume may vary depending upon the objective of the report – wether it is to provide an overview, or to give full and complete details. I believe that the question of validity centres less around the extent of a report, and more around the structure itself – is it easy to read and does it follow clear, consistent structures?

Do you then recommend using more charts in a management report?

Hichert: We live in a visual world, where a picture is worth 1,000 words. Pictures are much quicker and clearer to describe complex facts, which might otherwise require substantial wording. It is important to note though, that we can over-use charts as well. Many management reports use charts to visualize numbers that could easily be described in two brief sentences or less. If I want to refer to an export portion of 50% for example, I can easily do this in one sentence, I really don’t need to waste space on a pie chart that depicts only two halves. Such ‘business charts’ serve primarily for ‘optical loosening up’ reports that otherwise might only contain tables and texts. Financial analysts typically complain to me that “my boss is a numbers man, he doesn’t like charts, he prefers tables.” If you look at the quality of charts produced, you can understand this notion. Typically these charts have very low ‘information density’ and are weak illustrations, with no clear message, ‘cut off’ axes, and lack a consistent concept of notation and design structures.

How important is the inclusion of strategic aspects into management reports?

Hichert: Naturally, the structure and contents of management reports should be as aligned as possible to the company’s overall targets. The now popular introduction of a Balanced Scorecard into an organization, with the objective of aligning operations with corporate strategies, offers the ideal opportunity to rethink and improve corporate reporting systems.

What are your thoughts on packaging reports  ‘decoratively’?

Hichert: We now have easy access to creative programs which render all of us amateur graphic designers. Too often, though the necessary knowledge about basic information design principles is missing. CD (Corporate Design) guidelines, that are in principle important to unify content typically  don’t address those either. From our research, we know that the simpler the structure, the easier the report is to understand. Our work shows that such graphic elements as colored backgrounds, decorative pictures, pseudo-3-D-display, shades, frames or other design facets which may be inserted without meaning, should be considered as noise. Rather than add to a report, these features ultimately reduce the quality and the message of a report. Decorations that are unnecessary additions can dilute and crowd out the message. The over-use of color is the most common source of error. Color should only be used if it has an assigned meaning. One shouldn’t for example, expect that the reader will understand the use of red and green as traffic light colors, indicating stop and go on projects, if these colors are also used in other areas of the report for purely decorative purposes.

What is your recommendation in regards to how to deal with the display of variances between actuals and targets or plans?

Hichert: Typically, the major portion of a management report should demonstrate substantial deviations between targets and current actual values. This includes different forms of deviations, such as, for example, between previous years or even more importantly, corporate numbers versus industry benchmarks. If deviations are important, then they should also be concisely represented and emphasized through colors, arrows or frames. The more important the deviation, the more the emphasis must be marked. Professional report guidelines should ensure that equally relevant deviations are equally marked and represented. And it should apply not only to charts, but to tables and texts as well. This principle should always be applied to any reports in an organization, in a consistent and uniform manner.

So, the format of reports should be standardized?

Hichert: We strongly recommend employing a consistent uniform design concept which can be easily understood and interpreted without confusion. The key sign of a quality report is its ability to convey a message and explain facts in a clear and simple manner. Its objective is not to be an object of beauty. Today, we rarely see organizations that have mapped out clear guidelines and rules for scaling, usage of color, when to use what chart types, tables or texts. But consider the road map which universally utilizes a single color scheme; a river is always blue, the scale is always on each side, and north is always at the top. Whether in Australia, or Africa, the rules of the map remain the same. With management reports, it‘s usually left to the creator whether turnover figures are shown in blue columns or green lines – rendering it difficult guesswork for the executive who is forced to interpret and understand it. To be fair, cartographers needed many hundred years to develop visualization of roads, cities and to unite standards – so the financial controllers still have some time…

Business Visualization, Recession and Miscast Charts

When the economy is growing and consumers happily spend all their money, “eye-catching”, dubious “professional-looking” charts seem to make sense in the grand scheme of a resource-wasting economy. But under recession, and on the threshold of a long economic Winter, those miscast charts are just bad jokes that should be banished from every book, every magazine, every meeting room.

In a recession, “do more with less” is everyone’s motto. Cut jobs. Cut travel expenses. Make people work harder.

Make people work better. In an information economy, “better” is better than “harder”. Make sure people use their primary tools efficiently. I wrote about how inefficient a beginner Excel user can be. This is one of those hidden costs that no one seems to care about, except perhaps Toyota Motor Corporation CEO Katsuaki Watanabe, who sees PowerPoint as an example of waste.

That’s why Tufte is basically right, and so is Stephen Few and everyone who believes that information visualization is not a futile exercise of impression management with the sole purpose of showing off canned effects in a PowerPoint presentation. Tufte advocates a simple set of rules for better information visualization – the corporate world loves to do exactly the opposite. Until now. But can organizations afford to be inefficient (= lower return on investment) when dealing with expensive data? Can a lean organization leave junk in its management reports and presentations?

I know, the economy will not grow again just because of better business information visualization. But take a visualization rich report like these ones, imagine an eye-catching-flying-3D-pie-charts version and answer this simple question: when in recession, which one would you choose?

Creating rounded corners in Excel Tables

** Please see also the updated article, for XLCubed v7.1 and above: http://blog.xlcubed.com/2012/09/creating-rounded-corners-in-excel-revisited/ **

 

Igor Asselbergs was contemplating the value of round corners in design. Is it eye-candy? Or does it add value to the user experience? He comes to the conclusion that rounded corners clearly make a difference.

image

 

 

 

 

“On the left side, you see one surface divided by a line. On the right side your eye interprets the image as two adjoining boxes.”

The effect can be explained by the Gestalt Law of Continuity. Gestalt is a set of rules based on research into perception psychology, and a very powerful tool for Excel table design. In table design this effect can help us to see the table columns as a unit.

image

 

 

 

 

 

 

 

 

 

Did you ever wonder how to create rounded corners in Excel tables?

The basic idea of rounded corners is putting some shape objects into the corners of the column headers.

Here are the steps to create rounded corners:

  • To show the Drawing toolbar, click on the “Drawing” icon in the main toolbar
  • Go to Drawing Bar > Auto Shapes> Basic Shapes and insert an arc and a rectangleimage

 

 

 

 

  • Right-click the shapes and select Format Auto Shapes > Colors and Lines. Give the arc the fill color of your table headers, and the rectangle the fill color White. Set “no line” for the both. image

 

 

 

  • Right-click the shapes and select Format Auto Shape > Size and assign the arc and the rectangle the size 0.5″x0.5″ and set Lock aspect ratio.
  • Select the rectangle and move it to cell B2 keeping the ALT key pressed. This ensures that shape snaps to the Excel grid. Do the same for the arc so that it overlaps with the white rectangle.image

 

 

 

  • Select the arc and the rectangle and select the right-click command group.
  • To get the corner for the left side copy-paste the shape and go to Draw > Rotate / Flip / Flip Horizontally image

 

 

 

  • Set the Size to 0.18″ if you want rounded corners that have the Excel standard row heightimage

 

 

  • And put them left and right of your header cellimage

 

 

  • Set the Size to to 0.09″ for corners with 50% row heightimage

 

 

  • If your table headers have a different border or fill color, click the corner twice to select the rectangle arc object in the grouped corner object and format the arc with your fill or border colorimage

 

 

 

 

 

 

Graphical Tables – An Alternative to Treemaps

Sean blogged the other day about using a treemap to visualize the drivers of the Australian Inflation. He got inspired to create a treemap by an NYT article that used an interactive version of the following treemap:

NYTTreemap

This chart looks nice on the first view. It makes nice use of muted colors, the shapes look well balanced and certainly the graphic designer did a good job. However, from a data visualization perspective this chart has a couple of flaws.

Ben Shneiderman designed Treemaps to visualize deep directory tree structures.

Ben explained treemaps in an article as:

“Among the growing family of visual analytic tools, treemap are flourishing in organizations that require daily monitoring of complex activities with thousands of products, projects, or salespeople. Tabular reports, bar charts, line graphs, and scattergrams are important tools, but for complex activities where there are numerous sales regions, manufacturing plants, or product lines the hierarchical structures provided by treemaps can be helpful. While tabular displays and spreadsheets can show 30-60 rows at a time on typical displays, the colorful presentations in treemaps can accommodate hundreds or thousands of items in a meaningfully organized display that allows patterns and exceptions to be spotted in seconds.[…] Treemaps are a space-filling approach to showing hierarchies in which the rectangular screen space is divided into regions, and then each region is divided again for each level in the hierarchy.”

The first problem the NYT chart has is that it does not visualize the hierarchy as rectangular areas. The inflation drivers are visualized as asymmetric round shapes. It is difficult to compare the relative size of rectangular shapes but it gets almost impossible for asymmetric shapes. Also does this treemap lack labels for the smaller inflation drivers.

Sean published in his blog post a treemap which does not have the problems mentioned above:image

 

 

 

 

 

 

 

 

 

 

 

Ben designed treemaps to visualize thousands of regions, products, etc ; but the Inflation chart only comprises 20 Inflation Drivers grouped into 7 categories. A simple sorted table would do a better job communicating the numbers as Kaiser Fung from Junk Junks wrote in his post.

Inspired by this post and my comment Sean came up with this graphical sparkline table designed with Excel and MicroCharts.

image

This is already quite an improvement on the treemap, as we can see increasing and decreasing inflation trends and sparklines rather than traffic light colors as in the tree map version. Also it is much easier to read for non expert users.

Some minor things we can improve in Sean’s chart are:

  • We can sort the inflation drivers by Weight, to have the most important ones at the top
  • Changing the area to the sparkline puts emphasis on the trend rather than the absolute value of the values (as the area chart does)
  • Inline deviation charts allow us to visualize the MoM and YoY % changes

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Sharing your Excel dashboard: from paper to the web

You have finished your great looking, very efficient Excel dashboard. Now, how do you share it? How do you make sure the users have a timely access to the latest update? This can be a serious issue, specially if you have a more tech oriented audience, and it must be addressed at the planning stage. Let’s browse the available options.

Good old-fashioned paper

For the tech savvy crowd out there this is something that doesn’t even cross their minds. But let’s face it, top managers are among the most computer illiterate groups in our society. If these are your users just handle them a sheet of paper to keep them happy and forget about those cool interactive charts. Make sure that your design accommodates this kind of use.

Since printed paper has a higher resolution than computer monitors you may want to create smaller charts and a more detailed answer to the question the dashboard is supposed to answer. I always wanted to create a dashboard to be printed in a A3 (or 11 x 17) folded sheet. Maybe one of these days… Remember that if you anticipate that users will print your dashboard you must test it for color and B&W printing.

PDF file

Don’t make the mistake of thinking of a PDF file just as an electronic copy of a sheet of paper. User experience is quite different, and you must prepare for it. Users will be able to zoom, pan, add comments, copy a chart to a PowerPoint presentation, etc. Also if, for example, you have a marketing dashboard and it is used for different markets you can set up a simple macro to print a multiple page PDF, and add hyperlink navigation. It is a rich environment and you should take advantage of it.

Email message

You can send your Excel dashboard as an attachment to an email message. This will allow the users to save it locally and, unlike paper or PDF, you can add interaction, like selecting different markets, time periods or regions. Do your best to know what monitor resolutions your users have, and make sure that the users have security level settings that allow them to run macros (if your dashboard needs it). A cool thing to do with macros is to greet the user with the last sales data and open the dashboard with the correct market for that user.

File size and network security can prevent you from sending your dashboard as an attachment. Current file size may be smaller than you are allowed to send, but if the file is likely to grow (because you are adding more data) at some point you’ll not be able to send it. You can zip it but the problem remains, and some network security settings may prevent the users from receiving zip files.

Online / Intranet

If you choose this option the users will have a known location where they can retrieve the latest dashboard version from. You can add some nice touches, like pushing a new version to the user’s computer as soon as he appears online, or automatically send an email when the dashboard is updated (you can do it from the dashboard itself).

Web publishing

Some time ago, I was reviewing a dashboard tool and, although the product was pretty lame, I found that it had a clear advantage over Excel: it could publish dashboards for online access, using Flash technology. This means that it couldn’t manage real-world data sets, but still…

Finding a way to publish online a fully functional Excel dashboard with minimal impact over the way I’m used to do things is something that I’ve been waiting for quite some time. And if you can link the dashboard to the data set, well, that means heaven: you don’t have to open the file, refresh it and save it again.

I was unaware that this functionality even existed until Andreas told me about XLCubed. Last week I was able to install it and start to play with it. I will not tell you that this is a great product (not yet, anyway). But it is a great idea, and my expectations are high. Over the next posts I’ll tell you all about this process of discovery, but just being able to add “web publishing” to your list of options is already remarkable.

Final thoughts

There is not a single best option to deliver your dashboard. It depends on your audience, file size, update frequency, implemented features, information infrastructure… That said, we know that the future belongs to web enabled applications and web delivery. Web publishing would be my personal choice, undoubtedly, but I’d try to help less tech minded users to feel more comfortable (like adding a visible Print button).

I like to create charts and dashboards, and I do my best to find solutions that answer user’s problems. But if I can publish a dashboard without worrying about access and updates, well, don’t try to find me here. There is a tropical island waiting for me.

Data sources for Excel dashboards: avoid spreadsheet hell

This is the first of two twin posts where we’ll discuss the alpha and omega of Excel dashboards: data access and dashboard publication. These are two weak areas in Excel, and they should be approached carefully when planning for a new dashboard. Let’s start by reviewing the available data access options.

Copy / Pasting data

Are you or some one in your organization populating the spreadsheet manually? Or are you copy/pasting the data into the spreadsheet? This is the simplest method of getting data into Excel, but it can be dangerous. It should be avoided when better options are available.

When you are dealing with some kind of structured data management (like you do when you create a dashboard) you have to plan ahead and make sure that when data changes it doesn’t break your well crafted dashboard. Each function, each chart, must know where the data is and adjust for these changes when needed.

When you are pasting data there is a a high risk of break something. The number of rows or columns in the new dataset may change, and things like a time series chart may not recognize the new time periods and probably you’ll have to update references manually. Again, plan carefully or you end up in a maintenance hell.

External table

You can create a link to an external table in Access, Oracle or other database tool via a standard ODBC connection. This will ensure that the data is correctly funneled into the spreadsheet, but with real-world data it is very easy to have more records than the Excel 2003 limit of 65,536 rows. You’ll be better off if you link not to the raw data itself but to a query/view that aggregates the data (one of the basic rules for dashboard design in Excel is to avoid calculations and derivative data; the data should come from the source already prepared to be displayed).

Once the data is in Excel, there is not much difference between this and the previous option. You still need to use use lookup functions to retrieve the data and use it in report tables and charts, and data integrity is a stressful thing that you must ensure all the time. When possible, use database functions like DSUM instead of lookup functions (there will be a post discussing this).

Pivot tables

For an out-of-the-box Excel installation you may want to consider pivot tables. They are an interesting option for smaller datasets and they have a nicely flat initial learning curve. Please note that pivot tables will make your file size much larger because they store all the data in the spreadsheet, so scalability can become a major issue. Also, they work best with a strict hierarchical data structure. If your data doesn’t fit exactly in this concept this may be a problem. If you have a larger dataset you should consider an OLAP cube instead.

OLAP Cubes

The concept of an OLAP cube can be something scary for the average Excel user, but once you start using them you’ll never turn back. Specially of you are using what Charley Kid calls an “Excel-friendly OLAP cube”.

Unlike the other methods, an Excel-friendly OLAP cube (like XLCubed) will not store the data in the spreadsheet, thus eliminating the need for the usual data refreshing methods (open the dashboard, refresh, save and close). The cube is automatically updated and you can query it using formulas similar to GETPIVOTDATA. This makes a huge impact on the way you work. You get all the benefits of a regular pivot table plus several life-saving extras. The dashboard will be simpler, cleaner and easier to maintain.

Final Thoughts

You have several methods for data management in Excel, and you must decide what is the best method for each specific dashboard. Scalability is always an issue, so be sure your data don’t outgrow your chosen method. An Excel-friendly OLAP cube may require some immediate investment but will save you a lot of hassle in the long run.

Data management in Excel is a critical factor, and it will discussed in detail in future posts.

The next post discusses the other end of a dashboard project: how to make the dashboard available to the users.

Extended Deadline of Excel Dashboard Competition

We will be extending the deadline of our Excel Dashboard Competition by two weeks to 15 June. So you still have a chance of winning the iPhone or the InfoVis workshop.

FlowingData came up with the nice idea of building a visual display or the hundreds of data sets available in the U.S. Census 2008 Statistical Abstract.

I loaded one of the sheets (Patents and Trademarks.XLS) into Excel, enriched
it with MicroCharts, added a detail chart and published it to the Web.

 

 

Applied Gestalt Laws: Table Alignment

Most reports are based on combinations of tabular layouts, so to continue my series about visual design (see my previous post) I will focus on the most common and simple problem to fix: The fundamentals of how to align numbers and text in tables and how to treat their headings.

Here are the rules

  • Right-align a block or column of whole numbers or of whole numbers and text.
  • Left-align a block or column of whole text.
  • Align numbers at the decimal point (or imaginary decimal point).

clip_image001

 

 

 

 

 

 

Seems obvious really but they are so often rarely applied. A Google image search on “excel table” reveals what most Excel users do

….they simply use what Excel defaults to:

clip_image002

 

 

 

 

 

 

…or if people are more adventurous often feel that centered columns would somehow looks better:

clip_image003

 

 

 

 

 

 

…or even worse they apply the Excel Tables styles:

clip_image005

 

 

 

 

 

 

All those habits make the table more difficult to read. To understand why this is the case let’s use the Gestalt Law of Proximity.

clip_image007

 

 

 

 

In the picture above my brain tells me that there are 6 columns of 9 dots in one group. Simply moving the dots of the first row to the left breaks this grouping and differentiates the dots into 2 groups

clip_image009

 

 

 

 

 

This is exactly what happens if you left align column headers on numerical columns: As shown below and the brain does not associate them anymore which is what I want in most cases for headings and numbers.

So Excel Defaults are not right as shown below.

clip_image011

 

 

 

 

 

 

Right aligning the headers brings them together.

clip_image013

 

 

 

 

 

 

The grouping still works even if the shapes have a different width but remain either right or left aligned:

clip_image015

 

 

 

 

 

 

The reason for this is explained by the next Gestalt law of Continuity, the right aligned figures and the left aligned text are perceived as columns

The table below shows this affect with the arrows showing the continuation of the series and the same works with columns of left or right aligned figures or text, we perpetuate the series and perceive the column as one object. Even inserting a row to visually separate the figures and the column headers does not break the grouping…

clip_image017

 

 

 

 

 

 

….what can be explained by the Gestalt law of Closure.

Hence we perceive the columns of numbers and headers still as a unit even though the headers are placed somewhat apart from the figures.

clip_image019

 

 

 

 

 

 

If we now disable the Excel grid lines we end up with table which merely relies upon white space and Gestalt laws to format the table providing clear associations: A first class table.

clip_image020

 

 

 

 

In western cultures we read text from left to right so it makes a lot of sense to left align text columns but not so for numeric columns. The eye has to search for the decimal point to get to the ten, hundred or thousand digit, this makes comparing numbers quite difficult if not impossible when many numbers are involved.

clip_image022

 

 

 

 

 

 

Here the Gestalt Law of Continuity can help, simply right aligning brings all tens, hundred, thousand digits on the same virtual line and makes comparison straightforward and simple.

clip_image024

 

 

 

 

 

 

Unsurprisingly, centering numbers in column causes exactly the same problem as shown below. Another visualization “No No”.

clip_image026

 

 

 

 

 

 

Interestingly, the same rules apply when we move beyond simple text and numbers to MicroCharts such as sparklines, column charts and bullet graphs. Especially when the sparklines contain m
issing values.

clip_image027

 

 

 

 

 

 

Right or center alignment leads to severe difficulty comparing values of the same period in different rows in the table.

clip_image028

 

 

 

 

 

 

If the sparklines have the same amount of data points this is not an issue but in dynamic reports this may not always be the case so its better to be safe than sorry.

clip_image029

 

 

 

 

 

 

When using visual tables another nice trick to is to introduce an axis to a column chart to aid in the visual alignment and to group periods into blocks through the alternate shadings. The column chart above use a column chart to visualize units sold and an area chart for the other measures. The different shading groups the periods into 6 month units and the column bars aids the visual alignment. So to recap, make it easy for people to read your tables by following how your brain inherently processes information as explained through “Gestalt” laws. Here are the rules again

  • Right-align a block or column of whole numbers or of whole numbers and text.
  • Left-align a block or column of whole text.
  • Align numbers at the decimal point (or imaginary decimal point).

I hope that this article has been useful and I look forward to dealing with other visualization techniques in later posts.

Gestalt Laws, Charts and Tables: The way your brain wants them to be

I get asked by a lot of people how I seem to be able to format my charts and tables so that they look good and still convey the information in the most effective manner. I thought I would share my experience through my blog posts.

The first thing to know is that I like to use a set of visual design rules when building charts and tables and I like to understand why the rules make sense. In this series of articles I am going to attempt to explain the rules and the reasons behind them. Most of these rules are simple and are based on a solid academic foundation.

In this blog post I would like to introduce the Gestalt Laws, a set of design rules based on research into perception psychology. In the 1930’s the German Gestalt school of psychology investigated how the brain groups and organizes visual shapes. Following this research the so-called “Gestalt” laws were established. These laws form much of the foundation of the techniques I use in table design and I intend to refer to many of them in this series of articles.

Gestalt Law of Proximity: The brain tends to group items together that are close together in space ie. In the same Proximity.

Gestalt Law of Proximity

In the picture above my brain tells me that there are 6 columns of 9 dots in one group.

Gestalt Law of Similarity: We tend to group objects with similar properties (color, shape, texture).

Gestalt Law of Similarity

In the picture above my brain groups the black and gray dots.

Gestalt Law of Continuity: When something is introduced as a series the brain tends to perpetuate the series

Gestalt Law of Continuity

Gestalt Law of Closure: We tend to complete incomplete objects

Gestalt Law of Closure

The table below applies all of the Gestalt laws above:

Table applying Gestalt laws

· The Gestalt Law of Continuity: The right aligned figures and the left aligned text are perceived as columns

· The Gestalt Law of Proximity: The region labels and figures for Scenario W6000 and Scenario W7000 are grouped by having some extra space between the columns.

· Gestalt Law of Closure: Although we have some space between the column quarter column headers and the figures we perceive them as one unit.

· Gestalt Law of Similarity: Formatting the negative numbers red makes them clearly stand out from positive numbers.

Small charts are beautiful

Well, since you are reading this, I’ll assume that you took the red pill, so let’s keep moving and find out how deep the rabbit hole goes.

We saw that people usually design charts larger than they need to be. Why? Is it because we can’t fit the data into a smaller space? No, it isn’t. It is because in smaller charts there is no room for non-data elements, like title, legend, grid lines. In the dominant “Excel chart defaults” school of thinking data is not a priority.

This is a simple exercise that you can try safely at home and demonstrates it clearly. Start by creating a line chart in Excel, like this one:

Excel line chart

You can see the data, right? Now make the chart smaller:

Excel line chart

Here is a fierce territorial competition, and guess who’s winning? Make the chart a little smaller:

Excel line chart

The title and the legend win, as usual. The data must be here somewhere, but who cares?

This chart size is not large enough. Or so it seems. But what happens when we remove some non-data elements? Since we don’t need the legend, and we can put the title somewhere else, we can remove both:

Excel line chart

We are getting our data back! Let’s just leave the data and a simple grid line:

Excel line chart

I used MicroCharts to display the same data using both a line and a column chart:

Sparklines

With MicroCharts, you can add a “normal band” or a reference line that helps you to understand how the data departs from the expected values.

The above charts show percentage change on previous period GDP at market prices in the US (1980-2009). Here is the same data for some selected countries in the EU:

Sparklines

Michelangelo said: “I saw the angel in the marble and carved until I set him free“. Like him, keep carving your chart until you set your data free. The essence of a chart is the patterns you discover, buried under all the junk. By making your charts smaller you are force to remove that junk.

Finding this “essence” is what sparklines is all about.

The blog of XLCubed