It has been a very long time since the last post here on Clearly and Simply: the start of a guest post series by Sheel Bhatiani about how to Expand your reach in Tableau with Parameters.

Ever since I was so snowed under with work that I wasn’t able to do the final editing and formatting of Sheel’s articles. I hope for your understanding.

I know that most of you are eagerly waiting for the guest series to be continued. Agreed, it is long overdue, but please bear with me, I can’t let this one go: Yesterday the Art Newspaper published that the earliest copy of the Mona Lisa has been found at the Prado in Madrid, Spain. Today, Darren Chalk over at The Data Studio published the first article of a series of posts about Art in Tableau.

This reminded me of publicly available data sets to visualize famous paintings like van Gogh’s Self Portrait, Botticelli's The Birth of Venus and – you guessed it – Leonardo da Vinci's Mona Lisa.

Way back in June 2010, I already published an emulation of a piece of art using Tableau: The Tableau Replica of Curtis Steiner’s 1,000 Blocks and – although totally useless for business applications – that post was very well received by my readers.

Thus, I just can’t let this opportunity slip through my fingers and I intersperse this little article before we will continue with Sheel’s next article.

6 Famous Paintings in Tableau

So, here are 6 famous paintings visualized with Tableau Software:

Leonardo da Vinci’s Mona Lisa

Jan Vermeer's Girl with a Pearl Earring

Sandro Botticelli's The Birth of Venus

Vincent van Gogh's Self Portrait 1889

Diego Rodríguez de Silva y Velázquez’s Juan de Pareja

Gustave Courbet's The Desperate Man

The Tableau Workbook

Unfortunately I cannot provide the workbook on Tableau Public, since Public is limited to 100,000 data rows. The data sets for all 6 paintings, however, are exactly 900,000 rows. Thus, I can only offer the screenshots above and the Tableau workbook for free download:

Download 6 Famous Paintings (Tableau Packaged Workbook, 2,146.6K)

To open this workbook you need Tableau Desktop 7.0 (trial version here), Tableau Public or the free Tableau Reader.

Please be advised that Tableau needs a few seconds to render the data points of the view when opening the workbook or after you used the filter to switch to another painting.

The Technique

Not much to say here. The used visualization technique is as simple as can be:

• a XY scatter plot
• a full circle as the shape
• minimized size of the shape
• fixed axes scales and hidden axes headers
• a filter to let the user select one of the paintings
• disable the “Show All Value” in the quick filter

That’s it. As soon as you have your data ready, it takes less than 5 minutes to create this visualization.

The Data Source

The data sets are coming from a website called Traveling Salesman Problem run by the Georgia Tech. You can get tons of example data sets for the TSP there and – surprisingly enough – also the data sets of the 6 paintings used above. They refer to a painting as a Traveling Salesman Problem and they even have the results of the (so far) best routes visiting all data points.

Can you imagine? The shortest route through Mona Lisa? And you thought I would be a data geek!

What’s Next?

The next posts will continue Sheel Bhatiani’s guest post series “Expand your reach in Tableau with Parameters”. Sheel’s next article will come soon. At the same time I am also working on some new Excel workbooks and articles.

Please stay tuned.

To me, one of the most interesting sessions of this year’s Tableau Customer Conference was “Tips & Tricks from the Wild”, where five Tableau champions presented some of their best techniques in Tableau. One of these exceptional Tableau experts is Sheel Bhatiani, Lead Software Developer at Cheyne Capital. I liked his tip so much that I asked if he would be so kind to share some of his tricks here on Clearly and Simply. I am very happy to inform you that Sheel not only agreed to write one guest article here, but rather to publish a whole series of posts on how to “Expand your reach in Tableau with Parameters”. Sheel kicks off his series with today’s article. Enjoy.

Parameters - introduced with Tableau version 6 - are dynamic values that replace constants in calculations. They can be changed by the user of a dashboard or worksheet with an interactive control. This opens a lot of opportunities. Parameters in combination with Calculated Fields enable you to add a whole variety of additional interactivity to your Tableau workbook and dashboard. One of the most interesting things about Parameters is their ability to bring the existing Tableau built-features to the next level.

Today’s first article of my post series here shows the main concept of how to do this and includes a detailed tutorial how to use this for implementing a user-friendly interactive control to change the sort measure and the sort order of a view on a dashboard: Dynamic sorting with Tableau at your fingertips.

Are you ready to expand your reach in Tableau with Parameters? Here you go.

The Background and the Idea

Let’s take the Superstore Sales sample data coming with each Tableau installation and create a very simple view like this:

A list of Customer States visualizing Net Profit and Sales with simple bar charts and Profit per Customer with a color coded tornado chart. You certainly know how to do this. Using the Multiple Marks feature, you put [Customer Name] on the Level of Detail Shelf and [Profit] on the Color Shelf for the tornado. No big deal.

Very often, analyzing data starts with sorting by different measures. And of course, Tableau provides different built-in ways of sorting your views: the one-click-sort using the sort buttons on a worksheet or dashboard or the persistent sort which allows you to sort by data sort order, alphabetic, by field or manually. However, sometimes the built-in features just ain’t enough.

The easiest way is selecting one measure by clicking on the axis (e.g. Net Profit) and then using the sort buttons on the Tableau toolbar. This works well for all three bar charts. However, sorting by Profit per Customer will sort the States by the total net profits. In other words, it will result in the same sort order as sorting by Net Profit. This works as designed, but what if you want to sort the view by profits only or losses only? Something like this:

Of course this is possible with Tableau in general.

First, you need two simple Calculated Fields:

[Profits only] : MAX([Profit],0)

and

[Losses only] : MIN([Profit],0)

Finally you set the sort order of [Customer State] to Sort by Field and select either [Profits only] or [Losses only].

However, this is only possible on the worksheet, not on the dashboard. And it is inconvenient to change the sort measure and sort order of the view this way. Wouldn’t it be nice, if you would be able to change the sort measure and the sort order with a convenient and easy to access interactive control directly on the dashboard?

Parameters and some more Calculated Fields allow you to easily implement such an additional interactive sorting control.

Here is the detailed how-to tutorial:

The basic approach – A step-by-step tutorial

The basic approach needs only one Parameter and one Calculated Field for an interactive dynamic sort feature.

Step 1: Create a Parameter

Right click somewhere on the data window and select Create Parameter. In the following dialogue window, give the new Parameter a meaningful name like “Sort States by”, select String as the data type of the Parameter and enter names for all measures in the list of values table. Since we have only one Parameter to select both the sort measure and the sort order, you have to add two entries to the list for each sort measure and an additional description to represent the sort order. You could simply add “ascending” and “descending” to the measure names or – as shown in the following screenshot – you may also use symbols like triangles (triangle up for ascending, triangle down for descending):

Step 2: Create a Calculated Field for the Sort Measure

Next, right click again on the data window and create a new Calculated Field. The formula is a simple CASE WHEN statement. It evaluates the Parameter created in step 1 and assigns the according (i.e. user selected) measure to this Calculated Field. To switch from the default ascending to a descending sort order, we simply set the measure to its negative value.

The Calculated Field looks like this:

Please be careful: the texts in this Calculated Field used after the WHEN statements have to be exactly the same as you defined them when creating the Parameter (see step 1).

Step 3: Set the Sort Order of the dimension on the Row Shelf

Click on the arrow of the [Customer State] pill on the Row Shelf and select Sort. In the following dialogue window, click on Sort by Field and select the Calculated Field created in step 2 ([State Sort Measure]):

Step 4: Show Parameter Control

Finally, right click on the Parameter [Sort States By] in the data window and select Show Parameter Control.

That’s it.

Only 4 steps and you have an additional drop down list to change the sort measure and sort order of your view in one go.

Here is the interactive version on Tableau Public:

The enhanced version

The idea of the enhanced version is obvious: Instead of having two entries for each measure in the drop-down list of the Parameter, we add another Parameter to select the sort order.

Step 1: Create a Parameter for the Sort Measure

Pretty much the same step as in the basic approach, but only one entry per measure in the list of values:

Step 2: Create a second Parameter for the Sort Order

This is a new step. Create another Parameter, again with data type String, but only with two entries in the list of values: ascending and descending. We call this Parameter [Sort Order]:

Step 3: Create a Calculated Field to transform Text Fields to Numbers

Another additional step is necessary. Since we want to sort the view ascending or descending not only by [Profit] and [Sales], but also by [Customer State Name], we have to transform the names of the state into a number.

Here is one possible way of doing this: A new Calculated Field ([State String Number]) converts the first 4 characters of the state names to a number using the function ASCII. Multiplying the characters by decimal powers and adding them results in a four-digit number which we can use for sorting the state names.

Agreed, this is a brute-force method. However, it works, at least in most cases. Please be advised that this technique will not work 100% correctly, if there are state names starting with the same 4 characters (e.g. North Carolina and North Dakota). But it is close enough for this example.

Step 4: Create a Calculated Field for the Sort Measure

This step is pretty much the same as step 3 of the basic approach, but the calculation is different. It is a CASE statement based on the Parameter [Sort States by] again, but please notice the additional IF-clauses within each WHEN statement. Furthermore we have to use aggregations (SUM and AVG) in order to make the formula work for sorting the state names:

The CASE statement is pretty complex, including IF THEN statements within each WHEN. If you find this too hard to read and understand, there is another way to simplfy this Calculated Field.

Create another Calculated Field and call it - let’s say – [Sort Sign]:

IF [Sort Order] = "Ascending" THEN

1

ELSE

-1

END

You can now simplify your formula of [State Sort Measure]:

The result is exactly the same. However, you transferred the repeated IF THEN statements to another Calculated Field and simplified the CASE statement. This is probably easier to read and understand.

Step 5: Set the Sort Order of the Dimension on the Row Shelf

This one is exactly the same as step 3 of the basic approach.

Step 6: Show Parameter Controls

Again the same procedure as in step 4 of the basic approach, but this time you show both Parameter Controls: [Sort States by] as a compact list again and [Sort Order] as a single value list (radio buttons).

That’s it. Two simple steps more than the basic approach and you are good to go.

Here is the interactive enhanced version on Tableau Public:

The Drawbacks

The technique described above is a workaround. No more, no less.

Like any other workaround, it comes with some limitations and pitfalls. It takes some additional time to implement it (not too much in this case, though) and you have to define the sorting measures in advance. Those are only minor drawbacks from my point of view.

However, you should be aware of a more serious shortcoming of this technique. It only works as long as you or the user of your dashboard doesn’t change the sort order by using Tableau’s built-in sorting. After you selected a field and sorted it with the sort buttons on the toolbar, our dynamic sorting technique does not work anymore.

The root cause for this is the fact that Tableau sets the sort order to manual, if you are using the toolbar sort buttons. In this case, you have to go to the worksheet again and set the sort order of the dimension on the Row Shelf to Sort by Field again.

It goes without saying that the technique described above also stops working if you change the persistent sort on the worksheet to sort by data source order, alphabetic sort, another field or to manual sort.

As long as you are aware of this undesired behavior, you might be ok. However, this could be confusing for a user who does not know about this shortcoming. Even worse, if he has only access to the dashboard, but not to the worksheet (e.g. if he is using Tableau Reader or a dashboard on Server or Public), he can’t do anything about it.

There is one more drawback you should be aware of: using the Calculated Field as the sort criterion results in a more complex query and this may have negative impact on the database performance. If you encounter this problem in your implementation, you could grab the generated query from your Tableau Desktop log file and tune this with your DBA.

What’s next?

This was the first post of a series of articles on how to expand your reach in Tableau with parameters and to spice up your Tableau dashboards with even more dynamic, interactive features than Tableau already provides as a standard.

The next article will show a similar technique to create an interactive hierarchy.

Stay tuned.

Color encoding can be a very powerful technique for data visualization. Heat Maps or Choropleth Maps are classical examples of visualizing data by color encoding.

However, you can add color encoding to almost any kind of visualization. For instance, using colors on bar charts can display additional information of the data and – if used carefully – considerably improve the significance of the visualization without requiring further real estate on a dashboard.

With Tableau Software, color encoding your charts is a piece of cake. Simply drag the dimension or measure to the color shelf and you are done. Microsoft Excel has no comparable built-in functionality. However, this doesn’t mean you can’t use color encoding in your Excel charts. Of course you can.

Today’s post describes different techniques of how to color encode Microsoft Excel bar charts, with or without using VBA. As usual, all described techniques are coming with an example workbook for free download.

The Basic Idea

The basic idea is simple and obvious: Add another dimension or measure to your bar chart by coloring the bars according to the values of another data series.

One practical example would be a bar chart displaying one performance KPI of sales agents (e.g. sales figures, revenues, or the like) using the lengths of the bars and visualizing their sales regions (e.g. north, east, south, west) by coloring the bars with four different colors. More information in the same amount of real estate on your dashboard.

Microsoft Excel does not provide a built-in feature to do this color encoding, but – of course – this does not mean you can’t do it at all. As always, there is more than one way to skin the cat. Here are three different ways of how to accomplish this with Microsoft Excel.

Technique 1 – Several Data Series

I am sure you know this technique already. If you want to color encode categories (like in the example mentioned above), you add as many data series to your bar chart as you need (e.g. four sales regions in our example above) and format them using different fill colors.

A very simple IF-formula makes sure each data series contains the KPI values only if the row (i.e. the sales agent) belongs to the according category (i.e. the sales region) and NV() otherwise.

The result could look like this:

Here is an example workbook for free download:

Download Colored Bar Chart Data Series (Categories) - (Excel 2003 workbook, 79K)

You can use exactly the same technique if you want to color code a measure instead of a dimension. All you have to do is to cluster the values in categories, e.g. by defining value ranges or using percentiles.

You would probably use different hues of the same color instead of different colors, like this:

Here is the according workbook for free download:

Download Colored Bar Chart Data Series (Measures) - (Excel 2003 workbook, 86.5K)

Technique 2 – Change the Fill Color using VBA

You don’t mind using some simple VBA routines in your workbook? Good. A very small VBA sub can automatically change the fill color of the bars. Thus, you need only one data series in your bar chart. The set-up is very similar to technique 1 (color encoding a measure):

• Define value ranges (as many as you like)
• Define the color scale using the fill color of cells
• Use a simple MATCH formula to assign each value to the correct value range
• The rest is done by a very simple VBA routine looping through all data points in a For Next statement and setting the fill color to the according fill color of the range this value belongs to.

Have a look at the worksheet [calculation] and the small VBA routine of this workbook:

Download Colored Bar Chart Fill Color (VBA) - (Excel 2003 workbook, 89K)

The VBA sub “ColorBarChart” is assigned to the spinner on the dashboard, i.e. the coloring changes each time you are switching to another data set. Of course, you could also call this sub from a Worksheet_Calculate or any other event driven procedure.

Technique 3 – Change the Transparencies using VBA

This technique is limited to Excel 2007 or later. Like technique 2, it uses VBA. However, it does not change the fill color of the bars, but the transparencies. The set-up of the workbook is easier than with the other 2 techniques. Like technique 2, you need only one data series in your bar chart. You do not have to define range values, all you have to do is to create a helper column calculating the transparency values with a pretty simply formula.

Here is an example how this would look like:

The VBA procedure is very similar to the one used for technique 2. Instead of changing the property .Interior.ColorIndex, it changes .Fill.Format.Transparency, using the percentage values in the helper column (see above).

That’s it. One helper column and 8 lines of VBA code and you are good to go.

Here is the workbook for free download:

Download Colored Bar Chart Transparencies (VBA) - (Excel 2007 workbook, 28.8K)

Please use this with caution

Color coding is a very interesting way of visualizing data, no doubt about it. However, you should always think twice before using it. From my point of view, color coding sales regions of agents (as shown in the example for technique 1) adds a lot of very useful information. Imagine the bar chart would include more than 10 agents. You could easily see from the colors of the bar that e.g. 5 out of 10 of the top performers are coming from one sales region.

However, human eyes respectively the human brain are not very good in comparing values by color hues. For instance I would not recommend using a bar chart displaying the revenues of your company branches as the bars and their profits as the color. I would use 2 aligned bar charts instead. This makes both KPIs comparable at a glance.

Still, I do believe there are some use cases where coloring bars can be a viable alternative. All you have to do is to thoroughly think about which visualization tells your story best.

A Practical Example – A Color Coded Tornado Chart

The recent post described my “non-entry” to the The Tableau Interactive Sports "Viz" Contest: The History of Premier League Statistics.

This Tableau workbook included a variety of tables and visualizations and one of them used color coded bar charts: Dashboard 2 contained a chart showing the goals of each team at any point in time: Goals for, goals against and goal difference.

A tornado chart is not far to seek for this data set. One bar for each team, goals for on the right of the vertical axis and goals against on the left. This is pretty intuitive, I think.

But what about the goal difference? Sure, the bars leaning to the right are the ones with the better goal difference and vice versa. However, have a look at the example shown above. Imagine you would not have the color coding, would you be able to see at a glance that Aston Villa’s goal difference is worse than the one of Blackburn Rovers or that Liverpool’s goal difference is negative? I do not think so.

Thus, from my point of view this is a very good example of how to effectively use color coding on a bar or tornado chart. Sure, you can always add another bar chart displaying the goal differences, but if you are short of real estate on your dashboard, color coding can be a viable alternative in this case.

The implementation isn’t too difficult. It somehow is a combination of technique 1 and 3: four data series in the chart and a slightly changed VBA routine adjusting the transparencies of all 4 data series. Nothing new under the sun. Here is the example workbook for free download:

Download Colored Tornado Chart - (Excel 2007 workbook, 60.1K)

Acknowledgements

Many thanks go again to my friend Dan L, who was once more kind enough to invest some of his precious time reviewing my ideas and workbooks. A big time thank you very much, Dan. Your feedback is invaluable.

What’s next?

I am planning to have two Tableau articles during the next weeks: a new Tableau Quick Tip and an article on the making of the Premier League Historical Statistics.

If you are more interested in articles on Microsoft Excel, please hang in there. I am also working on a follow-up post to the Pivot Table with texts in the value area and on an article about how to drill-down within an Excel dashboard.

Stay tuned.

Last week, Tableau announced a new visualization contest: The Tableau Interactive Sports "Viz" Contest.

Intrigued with the very attractive prices Tableau announced, I would have loved to contribute a workbook. Unfortunately, I am not allowed to, because the contest is open to US residents only.

If you are a regular reader of this blog, you may have noticed that I love using sports data for my examples. We had several visualizations (Tableau and/or Excel) of Football statistics and even one article using Baseball data:

• FIFA World Cup Statistics with Tableau
• FIFA World Cup Scorers Statistics with Tableau
• The History of FIFA World Cup Host Elections
• Football League Tables (Combine Tables and Charts on Excel Dashboards)
• MLB Salaries (An Underrated Chart Type: The Band Chart)

Since I am so into visualizing sports data, I decided to publish the workbook I would have contributed to the contest: a Tableau visualization of 15 years of historical data of the English Premier League.

Today’s article provides the workbook for direct interaction here or for download from Tableau Public. A follow-up post later this week will describe step-by-step tutorials of some of the most interesting techniques I used for the implementation.

The Idea

Combine Tables and Charts on Excel Dashboards provided 2 Excel workbooks visualizing the fixtures and the tables of the English Premier League and the German Bundesliga, solely based on the match results. This was the ignition spark. The idea of today’s article was providing similar dashboards using Tableau instead of Excel.

I have to admit that I could not fully oblige this requirement. For one single view (the development of the team’s rankings over time) I needed an additional data source. I will go into further details of this issue in the follow-up post.

Still: all other views of the workbook are based on a data source containing nothing else than match results. This proves the flexibility of Tableau when it comes to the requirement of additional calculations based on the underlying data. For more information on the power of Calculated Fields, please refer to this article: Calculated Fields in Tableau.

The Premier League Visualization using Tableau

Here is the Tableau workbook on Tableau Public:

The Dashboards

The workbook consists of 3 different dashboards. Use the tabs at the top to switch between the different visualizations:

1. The first dashboard shows the fixtures of the selected match day, the table at the end of this match day and some additional statistics of the actual date and the to date aggregations.
2. The second dashboard visualizes the most important statistics like the points, the wins, draws and losses and the goals and goal differences per team. Additionally it show the wins, draws, losses from match day one to date on a heat map.
3. The third dashboard allows you to watch the “race”. It shows all teams and their points on the first view and the changes of the ranking in the table over time with arrow shapes in the second view. This dashboard is especially interesting if you are “walking through” a season using the match day slider.

You will notice a fourth dashboard, but this is just the edition notice.

The Interactive Features

The contest is called the Tableau Interactive Sports "Viz" Contest. Thus, I assumed the focus should be on interactivity. Here are the main interactive features of this workbook:

• Select one season from a drop down list (15 seasons, from 1996/97 through 2010/11). All displayed results will be filtered by the selection and show the results of this season.
• Select a match day (1 to 38). The dashboards show the fixtures of this day, a tables and different views visualizing the results after this match day. The slide control and the play buttons allow you to switch to a certain match day or to “walk through” a complete season and watch the changes of the visualizations.
• Sort the teams using the drop down list “Sort by” descending by points, wins, draws, losses, goals for, goals against and goal difference.
• The filter drop down “Table type” allows you to switch from the regular table including all matches to the home table or the away table, only considering the matches played home or away.
• Actions defined on the dashboards 2 and 3 will highlight one team across all views of the dashboard after clicking on a row, e.g. if you click on one team in the bar chart on dashboard 2, this team will also be highlighted on the Win-Draw-Loss chart at the bottom of the dashboard.
• Tooltips provide a variety of additional information when hovering over a view. E.g. if you hover over the Win-Draw-Loss chart on the second dashboard, the tooltips will provide you with detailed information on this particular match, like the opponents and the result. Hovering over the table on dashboard 1 shows a legend explaining the column header abbreviations. 

The Visualizations

The visualization are hopefully self explanatory. Here is just a brief description:

Dashboard 1

The usual suspects. This is what you probably are used to see at the end of any sportscast on Saturday or Sunday night: the fixtures, the results, the actual league table and some additional statistics like the total goals, the goals per match, etc.

Very simple tables, yet the most basic and most important information and overview.

Dashboard 2

Additional visualizations of the most important facts on dashboard 1. The points (visualized with a sorted bar chart), the wins, draws and losses so far (a stacked bar chart) and the goals for, goals against and goals difference (a tornado chart, colored from green to red based on the goal difference). Additionally, the heat map at the bottom of the dashboard visualizes the wins, draws and losses for each team over time.

Dashboard 3

This is not the snapshot-type of dashboard. This visualization reveals its real power when using it as a slide show. With the slider or the play buttons for the match day, you can easily watch how teams catch up or fall behind in the course of time. Furthermore, you can see the development of the ranking of all teams over time on the view at the bottom of the dashboard. A green arrow up indicates an improvement of the rank compared to the previous match day, a red arrow down means the team went down in the ranking and a black arrow to the right shows that the team has the same rank as last week. This way you can easily identify how the ranking of a team developed over time.

The Analytics – What can you see from this Visualization

I definitely cannot provide a description of all the insights Tableau provides here. Thus, here are only three interesting examples of what you can see from the visualizations above (just looking at Manchester United in the last season 2010/2011):

• ManU has never been number one before match day 15. However, from that on, they never left the top of the list again until the end of the season.
• This is somehow surprising, because ManU lost the first match on match day 18 (away at Chelsea). They never lost a match before, but within the first 14 matches, they had 7 draws and only 7 wins.
• ManU won the title, but on the away table, they are only ranked on position 5. Thus, they obviously won the title at home (18 wins, 1 draw, 0 losses).

There is much more to discover within this data. Please have a look for yourself.

Disclaimer

As mentioned above, the workbook is based on the match results and aggregates / calculates the tables and other visualizations from this data. Thus, the dashboards do not reflect any possible point penalties, e.g. Portsmouth's nine point deduction in 2009/2010 is not included. The tables are reflecting the sports, nothing else. They do not necessarily represent the official tables.

A Personal Note

I do not understand why the Tableau contest is limited to US residents. I suspect it may be due to legal issues with price rewarding in the United States (well, this was the explanation Tableau provided with their last contest, at least).

I admit, I do not know anything about US legal requirements for such contests and the price rewarding. However, in my humble opinion, a company with a subsidiary in London should be able to find ways of opening their contests to all of their customers around the world. Especially in view of the fact that Tableau recently hyped their ongoing international expansion.

Come on, Tableau folks, you are better than this.

What’s next?

The Premier League workbook contains a variety of little Tableau tricks and knacks. The next article will describe a selection of the most interesting techniques in detailed step-by-step walkthroughs.

Stay tuned.

The recent post showed a way how to create a Pivot Table lookalike crosstab with texts in the value area.

However, due to the fact that it was restricted to Excel formulas, the approach came with a couple of drawbacks. Using formulas forces you to define the layout and the size of the crosstab in advance in a static structure. It goes without saying that this considerably limits the usability in real life.

Without VBA, there is no way out. However, some VBA helps to overcome almost all of the disadvantages of the formula based approach. Today’s post is the announced follow-up: it describes how to use VBA to emulate a Pivot Table lookalike crosstab with texts in the value area, as always including the Excel workbook for free download.

The Idea and Concept

The basic idea is a fixed and named cell range on a separate worksheet steering the layout of the crosstab, i.e. the definition which data field goes to which part of the crosstab (value area, rows, columns and filter).

Everything else is done by a VBA routine analyzing the raw data and creating and formatting the crosstab. This routine is executed whenever something changes (data, layout definition or filter).

The crosstab will

• show only the relevant category entries in the rows and columns
• display filter, row and column category entries sorted ascending
• display the texts in the value area as values (no formulas)
• include an automatically created dropdown (form control) as the filter input control
• automatically be formatted

The Preparation of the Workbook

Although we will have the VBA routine to do the laborious work, we still need a little bit of preparation of the Excel workbook:

• Bring your data into your workbook. We are using the same fictitious example data of a project risk list again
• Define a name for the cell range containing the data (“myData”)
• Insert an additional worksheet [Control]. Here is an example screenshot:

Columns C4:C7 define the layout of the pseudo Pivot Table. The numbers represent the column numbers within our data table. In the example shown above, the data from column 2 of the data table (the risk description) will be displayed in the value area of the crosstab, the data from column 4 (the impact) will go to the rows of the crosstab, column 5 (the probabilities) will be displayed in the columns of the crosstab and column 3 of the data table (the risk type) will be used as the filter. If you want to change the layout of the crosstab later, we do not have to change the VBA code. Instead, we simply change these 4 values. A combination of 2, 3, 4, 5 will bring the risk types to the rows, the impact to the columns and use the probability as the filter.

In cell C8 we define the width of the columns of our crosstab and in C9 we select the fill color for the row and column headers of the crosstab.

Cell C11 stores the actual selected filter.

• Define names for the range B4:C9 (“myPivotTableDefinition”) and for C11 (“myFilterSelection”) in order to be able to address these cell ranges in VBA.
• Finally insert a new worksheet [Pivot Table Risks] and assign a name to the top left cell of our pseudo Pivot Table (“myPivotTableStart”).
  

The Implementation – The VBA

I think I don’t have a snowball’s hope in hell to describe and explain the VBA code line by line. I did my very best to write readable and comprehensible code (not sure if I succeeded) and added a few comments. Thus, I will restrict myself to a few hints on the general structure of the VBA:

There is only one single module in the workbook containing 2 main functions and 2 main subs:

1. The function UniqueItems returns a list of unique items from an array of values
2. The function FindPosition does pretty much the same as the worksheet function MATCH. We could use Application.WorksheetFunction.Match(..) instead, but the VBA is faster than calling the worksheet function
3. The sub QuickSort sorts an array ascending
4. The sub CreateTextPivotTable - this is the main procedure

The sub CreatePivotTable consists of the following main steps:

• Initialize the data structure and transfer the data and the Pivot Table definition from the worksheets to VBA variables
• Delete the existing Pivot Table, unmerge cells, clear all formatting and delete the existing filter drop down
• Prepare the filter: create a list of unique entries in the data field selected as the filter, add the entry “All” and create a filter dropdown on the worksheet
• Detect the row and column headers by creating lists with unique items from the data fields selected for rows and columns
• Loop through all data rows, detect their position within the crosstab (which row / which column) and calculate the number of entries in each field of the crosstab matrix
• Calculate the maximum number of entries per row category
• Write the crosstab to the worksheet
• Format the crosstab
• Update the named formula "myPivotTable"
• Clean-Up (deallocate the data structure)

The code will always delete the existing crosstab and create a new one from scratch. It has to be updated if

• … the data is changed or new data rows have been added
• … the definition of the layout on worksheet [Control] has been changed
• … the user changed the filter by using the dropdown

The update of the crosstab after changing the filter is managed within the VBA code (using .OnSection of the dropdown). In order to make sure that the crosstab is always up to date after changes of the data or the [Control] worksheet, we call the sub CreateTextPivotTable within the event driven procedure Worksheet_Activate of the sheet [Pivot Table Risks]. Every time the sheet is activated, the crosstab will be updated.

That’s it.

The Pros

• It works (well, you expected that, didn’t you?)
• It is easy to implement and easy to use
• The layout of the crosstab can easily be changed
• It eliminates most of the disadvantages of the static formula approach (see the cons described in the recent post)

The Cons

• VBA necessary
• Some preparation of the workbook required, but by far less efforts than with the formula based solution
• Limited to exactly one filter. There is no option for a crosstab without a filter or more than one filter. This would be possible, of course, but I tried to keep it as simple as possible
• Error handling is next to nothing. For instance, the code will not check if it will overwrite data or formulas. There is no warning. You have to make sure that there is enough empty space right and below of the defined start cell of the crosstab
• The layout definition is flexible, but not very user-friendly. A user form with dropdown lists to change the layout and the formatting would be more convenient

The Performance

If you are using a more complex VBA procedure, the performance of the code is always an interesting topic. I did some stress testing. With a relatively small amount of data (150 data rows in the example workbook), the crosstab updates instantaneously. Using a database of 5,000 rows, the procedure takes 0.6 seconds, with 10,000 rows this increases to 1.2 seconds, with 65,000 data rows it takes even 6.4 seconds. Agreed, 6.4 seconds is everything else than a good user experience.

However, I do not think that matters in this specific case: unlike a real Pivot Table, we are not aggregating data. We are simply rearranging it and showing an enumeration in a tabular crosstab. From my point of view this wouldn’t make sense with a large database of 10,000 rows or more. Thus, performance shouldn’t be a problem.

The Download Link

Here is the Excel workbook for free download:

Download Pivot Tables_with_Texts - VBA Approach (Excel 2003 workbook, 121K)

Please be advised that the workbook is Excel 2003 file format, but I tested the code only with Excel 2010. If you encounter any problems with the file using Excel 2003 or earlier, please leave me a comment.

Acknowledgement

Many thanks go to Ulrik Willemoes, who was once more kind enough to spend some of his precious time reviewing my workbook and discussing it with me. As usual, Ulrik provided a couple of very interesting suggestions how the workbook could be improved. Many thanks, Ulrik!

I decided to save one of Ulrik’s best ideas for a follow-up post. So, if you liked this article, please stay tuned. There will be one more post on this topic coming soon.