Formatting Tables in v7

We’ve had some great feedback from our Newsletter announcing the release of v7.  A number of users have asked how we created the example in the Newsletter:

So,  today we’re going to show you how to achieve some of the formatting that is now available when using SQL tables in v7.

Let’s create a SQL table from Grids & Tables tab.  You’ll see the Create connection window:

Click Connect and you will see the databases you have access to….we’ll create our query based on Bicycle Sales and the fctData view.

 

Our SQL query returns the following data which is great but clearly is not that easy to read.

Let’s format this table.  We’ll get rid of any borders currently set on the workbook by going to the format sheet and using Format Cells on the default cell format cell as below:

 

Back to the table, right-click and refresh table.

 

Now for the actual formatting of the table. Let’s format entries in the first column cPOS. Right-click on Car and Bike Stores, right-click and select Format Column and let’s set the font to be bold, size 12 with a double border on both top and bottom.

Now the second column cProduct. Again right-click Format Column and set the top border to be double, bottom border thick and the font italic.

 

We now go to Properties tab and on Appearance tab set Sections as below:

Check the box ‘Use columns as sections’, the column count is 2 in our example and Display style is set as ‘Sections in separate rows’.

We’ll also hide the first row of the workbook showing the table column headings.
The report now looks like the screenshot below which is much easier to read.

 

Number Formats

One of the main reasons we use Excel is to analyse and display our data, for either our own consumption, or to show to others. In both cases, we want our data to be easily readable, and any important patterns to be immediately obvious.

We use colours, borders and other formatting to highlight important characteristics of our data, and to de-emphasize those features that should stay in the background (see  The Dashboard Squint Test for more). In just the same way, we can use number formats to highlight numbers that are unusual in some way, decrease the focus on uninteresting numbers, or to remove excess detail. Here we recap the essentials of numeric formatting in Excel.

The basics

To apply or change a number format, select the cell or range that needs to be altered, then either:

  • make basic changes (add or remove decimal places, use percentages and so on) using the Number button group on the Home tab (in Excel 2007 and newer),
  • make more advanced changes by right-clicking on the range and selecting the Format Cells option,
  • if you prefer keyboard shortcuts, you can show the format cells dialog by using the Ctrl+1 keyboard shortcut.

From the dialog, you can select some common and very useful formats, including:

  • Number: this allows you to customise the number of decimal places and whether to show thousand separators
  • Date and Time: for formatting dates and times, allowing a variety of shorter and longer options
  • Percentage: format the numbers as percentages, with the desired number of decimal places
  • Custom: allows you to specify your own custom formats (see below)

Simple custom formats

We’ll first go through some simple examples, including some of the standard formats mentioned above, so that in the next section we can build up more complex ones. To enter these formats, follow the steps listed above, then select Custom from the list on the left of the dialog.

  • To show a custom number of decimal places using a number format, write the number you want to show using zeros, for example 0.00 to show your number using 2 decimal places
  • To include digits only if they exist, use a # sign instead of the zero. For example, to only include the part of the number before the decimal if greater than 1 (or less than -1), use #.00
  • To include a thousands separator in the number, use #,###. We use the # symbol to avoid forcing Excel to display unnecessary zeros
  • To format numbers as percentages, just place a % after the format. For example, using the 0.0% format will cause 0.2534 to display as 25.3%
  • To give the numbers a colour, put the name of the colour in square brackets before the format, like this: [Red]0.0

More advanced formats

The formats that we have used so far only use one format for all numbers. In fact, Excel lets us specify four formats: one for positive numbers and one format for negative numbers, one for zeros and one for text – all in one cell. To do this, we use the semicolon to separate the different formats. For example, to format only negative numbers as blue, we can use 0.0;[Blue]-0.0;; In this example, because we have left the other sections blank, zeros will appear as empty cells on the worksheet.

If we combine the pieces of information from the last paragraph, we can find another useful format. Any cells with the ;;; format applied will hide any data in the cell. This can be useful if you want a formula in a particular cell, but don’t want to hide an entire row or column for it.

Another common case is where you have large numbers, but don’t need to see all the digits. In this case, it can be useful to just emphasize the important part of the number, by using this format: 0, This format will round to the nearest thousand, and remove the excess zeros. You can extend this to millions by using another comma, and it’s even possible to include an indicator that the number is shortened like this: 0,,”M”

There are many more special formats available, including changing the boundaries between the semicolons and date and time formats. Have a look at the further reading, below, for more information.

Number formats in XLCubed

XLCubed, being tightly integrated with Excel, allows you to specify number formats in two ways.

  1. For XLCubed value formulas, for example XL3Lookup and XL3ValueRankLookup, the number format of the containing cell can be modified in the way described above. In the same way as any normal Excel-based formula, the format is preserved when the value changes due to changes in your data.
  2. For XLCubed Grids, apply the format as above, then use the right-click menu, and choose Apply Format to Data. This asks XLCubed to maintain the format on the entire slice of your data. More detailed instructions can be found here.

Table of reference

ExampleFormatResult
0.23560.000.24
0.2356#.00.24
123567901235679
1235679#,###1,235,679
0.25340.0%25.3%
435[Red]0.0435.0
500.0;[Blue]-0.0;;50.0
-500.0;[Blue]-0.0;;-50.0
6541.21;;;
15984.1250,16
654915165.5150,,”M”655M

Further reading

Hermann Grids, An optical Illusion Best Avoided

An interesting optical illusion is the so-called Hermann Grid illusion: the effect of seeing gray dots at the intersections of a black grid on a white background or a white grid on a black background.

image_thumb3

 

 

 

 

While it’s an interesting optical illusion, it’s something we should avoid in management reporting:

image_thumb16

 

 

 

 

 

 

 

 

 

Tables formatted with medium or thick black or gray borders tend to produce Hermann Grids. Just scan the table above and you should see the gray dots in the grid intersections.

To avoid this unpleasant and distracting effect, and to maximize the data-ink ratio follow this simple but very effective table design rule:

  • Avoid using dark and heavy grids
  • Use light gray grids instead

image_thumb19

 

 

 

 

 

 

 

 

 

Above is the same table with light gray borders. This eliminates the Hermann Grid illusion and – by de-emphasizing the grid –  puts more emphasis on the numbers.

Here are some images I found on Google Image Search that show how popular it is to put your data behind Hermann Grids:

image_thumb8

 

 

 

 

 

 

 

 

 

 

image_thumb11

 

 

 

 

 

 

 

 

 

 

So I hope you are with me – get rid of the heavy grids and free your data!

Win Lose Charts – English Premier League 2007 – 2008

With hindsight, it’s easy to look at it with hindsight” – Glen Hoddle

Little did we suspect midway through the season that the Premier League would turn into a 2 horse race. Poor starts by both Man Utd. and Chelsea were transformed into a consistent run of good form which would extend the drama to the very last day of the season.

With one game left to play, the top two were level on points with Manchester having a superior goal difference. If Chelsea could achieve a better result than Manchester, then they would clinch the league in the final game…

The English Premier league currently has 20 teams requiring a total of 380 games per season. The results of sporting leagues are usually displayed in a league table format. During the course of a season we see teams occupy different table positions. The standard table format however leaves out the important historic story of the league.

This is how soccerstats.com shows the final league table.

image

 

 

 

 

 

 

 

 

 

 

 

 

 

The final 8 games are summarized as a colors encoded Win Lose chart. The table found at the web site above provides helpful links to each team’s performance and a snapshot in time regarding table position for each month of the league. It is also possible to rank the teams with respect to their home or away performance and overall is a very powerful tool for analyzing statistics of the league. Some historical information is also given with the results for the last 8 games being displayed in the last column.

Statistics are like miniskirts; they give you good ideas but hide the important things.” – Ebbe Skovdahl

However, there are a number of things about the table in that I think could be improved and all of them are centred on the “Last 8” column. The table author is attempting to describe win-lose information graphically and has dedicated approximately one quarter of the width of the table to it. With a quarter of the table dedicated to it, we should expect more than 8 out of 38 games to be described. The author has chosen to invent a chart type which encodes the results using colored squares with more empty space than data. The whole season can easily fit into this width if we choose an appropriate display technique such as a win-lose chart.

My least favorite aspect of the “Last 8” column is the non-standard use of a 1 dimensional plot. I come from a physical science background and so I am used to diagrams telling me certain things in certain ways. One of these things is (in the absence of an axis or some other visual guide) that time should go from left to right! In this 1D plot the knowledge of which way time is going is fundamental. The links at the top of the same table used to break the table down into months, go in chronological order from left to right. This sets up my brain to expect the rest of the table to behave in the same way. So why don’t the last 8 games do the same? Unless you actually have some knowledge about how the season ended, you might not actually realize that time is going from right to left. I happened to know that Liverpool and Arsenal didn’t lose their final games of the season. This made me double check what the table was actually telling me. Without some knowledge of how the Premiership ended I would have interpreted the information presented wrongly. The fact that time is going from right to left isn’t wrong. The fact that the rest of the table is telling me to expect it to go from left to right is.

“Well, Clive, it’s all about the two M’s. Movement and positioning” – Ron Atkinson

So what improvements could we make to the standard table format to get more information into the table? To tell the story of the 2007-2008 league we need to include the historical context.

The Excel table below uses sparklines to summarizes the season for each team in terms of relative positions in the table and a win lose chart for the entire season.

image

 

Now we get a feeling for the true drama that occurred during the season. The Position column shows how the each teams position changed over the course of the league. Those of us that followed the 2007-2008 Premiership will be able to look at the performance curves and remember the situation for a given time. For example:

  • The lowest point of the season for Man Utd ,in every sense, was when they lost to Man City
  • The departure of Jose Mourinho after 8 games was a catalyst for change in Chelsea’s fortunes

“I never make predictions, and I never will” – Paul Gascoigne

It is interesting to directly compare the performance of teams together. Adding an interactive Bumps Chart in allows us to see how Man Utd and Chelsea faired over the season. To compare two team click the sparklines in the ranking table or click the data label on the Bumps Charts.

image

 

In the next post I will talk about the techniques used to produce the ineractive ranking table within Excel. Until then why not check out a live web version, published from Excel to our website.

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.