How’d you like that….displayed!

Today’s blog will show you a really quick and easy way to format your grid to show different display units.

This approach is ideal for dynamic Grids where the size of the values can vary considerably based on the selected filters, or where the user has drilled down to lower levels in the data. For example, if country level numbers are in hundreds of millions, but customer level numbers are in hundreds or thousands, it can be useful to have the ability to quickly change the display units.

Using this method, you can switch quickly to using different formats.  In our example we want to give the user the option to display the measure Reseller Sales Amount as Units, Thousands or Millions.

You can see we have a slicer to the right of the grid giving the user the choice of how to display the figures.

The slicer is based on an Excel range and is not directly linked to the grid.

A couple of things to note are the ‘Update range’ and ‘Activate XL3Link’ slicer settings are checked but more of that later.

‘Enabling Update Range with selection’ means that the slicer choice is written to a cell in the workbook – in our example it’s cell $M$17 (shown in red in screenshot below) – currently Millions are selected.

The other thing to note from the screenshot above are N13:N15.  These are a series of IF statements like below which will determine how the format sheet is updated:

=IF(M13=$M$17,”*”,”NOT SELECTED”)

As M13 is not equal to M17 the Units  row will be set to ‘NOT SELECTED’.

The row containing the ‘*’ is the format which we want to be applied to the Grid.

We now need to reflect that in the format sheet. This is done by first adding three rows for the relevant hierarchy, and setting the Excel numeric format column G to units on the first row, and then thousands and millions on the other two.

We can then set the ‘Member’ cells in column E as a simple formula referencing the table shown above. For example, the Unit’s member incell E50 in the XLCubedFormats sheet is set as =Sheet1!N13.  The same process is followed for Thousands and Millions.

With the format sheet as shown above, based on the user’s slicer selection, all members will then be displayed with the predefined Millions format as ‘*’ is a wildcard and will match on all the members in the Product Categories hierarchy.

The formatting for Units and Thousands will not be applied – unless of course you have a member in your hierarchy called ‘NOT SELECTED’!

As mentioned previously, our Excel slicer is not directly linked to the grid – we need a way to tell the grid to refresh each time the slicer choice changes.

This is where XLCubed’s ActivateXL3Link and XL3RefreshObjects comes into play.

Let’s look at the XL3ActivateLink first.  As you can see from our slicer screenshot above, our slicer is set to activate the XL3Link statement in cell I7.

I7 is set as =XL3Link(XL3Address($K$7),”Set Refresh”,,XL3Address($I$6),TRUE)

As you can see from the screenshot below it sets the target cell I6 to TRUE.

Cell J6 contains the formula =XL3RefreshSheetObjects(I6, “Sheet1”, TRUE).

As I6 is set to TRUE this forces a refresh of all objects in the sheet when the slicer choice changes.

It’s that simple – so the next time your Sales Team want their sales figures displayed as units but the CEO wants to see them expressed as millions impress them with this method!

Creating rounded corners in Excel – revisited

Today we’re revisiting one of our more popular guides, Creating rounded corners in Excel Tables, and have updated it for v7.1. When Igor Asselbergs was contemplating the value of round corners in design, he came to the conclusion that in many cases they added real value to the user experience.

The effect can be explained by the Gestalt Law of ContinuityGestalt 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.

The previous process to create rounded corners in Excel tables required quite a bit of persistence and patience. In Version 7.1, we’ve introduced a feature to enable adding rounded corners in a few seconds rather than several minutes, so while the theory is identical the implementation is much improved. Take this report showing sales KPIs, where we would like to add rounded corners to the header row in the table.

To do this we first highlight the required area:


Then we go to Extras -> Add/Edit Round Corners:


The Colours and Border thickness will be picked up from the selected cells. Select the corners to be made round (in this case the Top Left and Top Right corners):


Click OK to apply the borders

 

To edit existing corners which were created by XLCubed then you can just highlight the cell or range and Go to Extras -> Add/Edit Round Corners. The changes will be applied to the existing corners (or the corners can be removed by unselecting them).

It’s a simple addition to the product which would have saved us quite a bit of time in customer implementations over the years, and hopefully now does the same for our users.

Mind the gap!

Today’s blog is going to show you how to use XLCubed’s custom calculation functionality to create column breaks in a grid.  Imagine that you have a report that shows you Reseller Sales across Product Model Categories over a 12-month time period.

 

 

 

There’s nothing wrong with this report but don’t you think it would be nicer if there was a way to separate out each quarter block ie put in a divider column between March and April, June and July, September and October.  That would make it much easier to read and show clearly where each quarter period started and ended.

So let’s start by creating a custom calculation.  Click the highlighted icon and give your custom calculation a name – let’s call it ColBreak.  It’s connected to the Date.Calendar hierarchy.

 

Now in the Expression area enter  a blank string starting and ending with ” (double-quote).  Click OK.

To insert this into our report we now go to the Hierarchy Editor for Calendar Date – expand the All member and you will see ColBreak.

Drag this across and insert it into the report.  We will insert it after March, June and September and click OK.

 

 

The report now looks like this:

 

Now let’s format this column break so that the we don’t see ColBreak appearing as a column heading.   You need to right-click to get XLCubed’s right-click menu and then choose Format This Member.

We will choose white for the Font colour before clicking OK.

The report now looks like this with clear demarcations between each quarter:

 

 

 

A snappy fix for layout problems in Excel

Have you ever tried copying parts of one workbook to another and been restricted by column widths?  Or maybe you’re almost done with a report layout only to find that the last table you need to add has 4 columns, where there is only room for 3?  Today we’re going to show you how to use Excel’s Camera tool to get around any Excel column width limitations to achieve your dashboard goals!  Here we have an Excel heat Map on a separate sheet in our workbook.

It has been inserted into the dashboard below where the first thing to notice is the workbook’s  variable column widths, in particular columns J and K.  If we had just inserted our heat map as it was, the column widths in our dashboard would determine the width size of the heatmap.    Instead we used Excel’s camera tool to insert our heatmap sized at exactly what we wanted, regardless of the destination sheet’s column widths.

 

We follow these simple steps:

  • select  the heat map in the source sheet
  • click the Camera Tool icon
  •  navigate to the destination sheet
  • click and insert exactly where you want

The Excel Camera Tool is also a great way creating dynamic screenshots of particular groups of data.  The Camera Tool takes a picture of a selected area, and you can then paste that picture wherever you want it. It updates automatically, and because it is a picture rather than a set of links to the original cells, any formatting or data change in the source is automatically reflected in the picture.

The heat map chart source figures have been updated to show Europe’s higher sales – as you can see Europe now has the greater sales:

 

The dashboard heat map has updated automatically to reflect this value change.

 If you can’t see the Camera Tool on your Excel menu you can easily attach it to your Quick Access Toolbar by performing the following steps:

  • Click the File Tab
  • Click Options
  • Choose the Quick Access Toolbar Option
  • In the ‘Choose Command From’ dropdown, select Commands not in Ribbon
  • Find the Camera Tool from the alphabetical list of commands and add it to the Quick Access Toolbar.

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