Clearly and Simply

Category: Data Analysis

Filter Excel Tables by Double Clicking
How to set the active cell’s value as the filter criteria in Microsoft Excel Tables by double clicking

In a post on the Excel Hero LinkedIn Group Charles asked today:

“[…] I wish to click on a cell with a purchase order number and have that action filter the list based on that purchase order number.
What could be a strategy please? […]”

In one of the answers to Charles’ question, Craig pointed to the most obvious solution: right click and select Filter | Filter By Selected Cell’s Value. Well, I know this is available in Excel 2010, but I can’t tell you no lies: I don’t even remember if this feature was available already in 2003 and earlier.

Anyway. Although at least Excel 2010 provides a built-in feature for filtering by the value of the active cell, Charles' idea is very interesting and reminded me of a post we had here last year: Sort Excel Tables by Double Clicking.

The article provided a small VBA code snippet to sort data tables by double clicking into any given column. The technique used back then for sorting can easily be adjusted to implement Charles’ original idea: filter data tables by the value of the active cell after double clicking and remove the filter criteria with another double click.

The implementation is simple: all you need is to define a name for the range where your data is (e.g. “myData”) and a very small piece of VBA code (24 lines of code) in the event driven Worksheet_BeforeDoubleClick event procedure.

The code
- checks if the the user double clicked inside of the data
- detects the active column
- checks if the auto filter is set and sets it, if not
- checks if a criteria is defined already and if so, removes the criteria or otherwise sets the criteria to the value of active cell
If you are interested in the details, have a look at the workbook provided for download below. If you have any questions, please leave me a comment.

A simple and effective way of setting / removing filter criteria by double clicking. The technique is not limited to one column. You can also select several columns to filter your data.

Here is the workbook for free download:

Download Filter Excel Tables by Double Clicking (Microsoft Excel 2003, 93.5K)

You can even combine the filtering by double clicking with the sorting by double clicking technique, if you change the VBA to sort only if the user double clicks on the header of a column. A simple IF clause and a combination of the two code snippets will do the job.

Stay tuned.
November 2, 2012
O’zapft is!

The Development of the Beer Prices at the Oktoberfest – a Tableau Visualization and Analysis

At this moment Munich’s mayor Christian Ude opens the 179th Oktoberfest in Munich with the traditional shout “O’zapft is” after tapping the first barrel of beer.

Prior to every Oktoberfest we have a reoccurring heated discussion on the beer price. And – except for the breweries and the tent hosts – we all agree that this year’s “drastic” rise of the beer price is inacceptable.

No one really takes this discussion too seriously, but we are having it every year.

So, today’s opening of the Oktoberfest is a good opportunity to have a closer look at the prices and the price development of beer at the Oktoberfest. This article provides an interactive Tableau visualization (beer prices since 2002), followed by an analysis of the price trend since 1952.

(more…)

September 22, 2012
Emulate Excel Pivot Tables with Texts in the Value Area using VBA

How to create a Microsoft Excel Pivot Table lookalike Crosstab with Texts in the Value Area using VBA

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.

(more…)

June 30, 2011
Emulate Excel Pivot Tables with Texts in the Value Area using Formulas

How to create a Microsoft Excel Pivot Table lookalike Crosstab with Texts in the Value Area using Array Formulas

Pivot Tables are probably the most powerful built-in data analysis feature of Microsoft Excel. With only a couple of mouse clicks, you can summarize and analyze your data from different perspectives. Well, I do not want to carry coals to Newcastle. I am sure you know all about the power of Pivot Tables.

However, there is one thing you can’t do with Pivot Tables: create a cross-tabular view with texts in the value area.

A real-life example should help to understand what I am talking about: Let’s assume you are managing a big project and you have a list of all existing project risks. The table includes the descriptions of the risks and you classified each of them using different categories like the risk impact (high, medium, low), the probability (very high, high, medium, low) and maybe the type of the risk, the subproject or the name of the responsible project member. If it is a large project with a considerable number of risks, you may be interested in a cross-tabular view of your risk list with e.g. the impact in the rows, the probabilities in the columns and maybe the option to filter the view by a third category like the risk type.

At first sight this looks like the perfect use case for Pivot Tables, but a Pivot Table aggregates data, i.e. Pivot Tables always have numerical results in the value area. In other words, all you can show in the value area is the count of risks for each combination. But what if the count is not enough and you prefer seeing the description of all risks for all combinations of impact and probability?

Today’s post is the first of two articles showing how to emulate a Pivot Table with texts in the value area. There are several ways of doing this, but there are 2 general approaches: with or without VBA. Today's article demonstrates a way to achieve this with Excel’s array formulas only. The next article will provide a VBA based solution which overcomes most of the weaknesses of today’s formula based approach.

As usual the post includes an example Excel workbook for free download.

(more…)

June 27, 2011
Sort Excel Tables by Double Clicking

How to sort Microsoft Excel Tables by double clicking on any cell

One of the most basic techniques in data analysis is sorting. And for sure it is one of the most helpful. If you are exploring a large data set for the very first time, I am pretty sure, sorting the data by certain fields is one of the first things you do.

Very often, you do one sort after the other. Let’s say you are looking at a large table of several hundreds of products and all their performance measures, like the unit price, the margin, the order quantities, etc. If you want to get an overview of the product portfolio, what do you need to know in the first step? The most expensive and the cheapest products, the products with the highest margins, the best selling products, etc.

So, what do you do? Sorting, again and again, right?

There is no data analysis or BI tool that doesn’t provide a sorting functionality. However, is it easy and fast to access? And even if it is? Couldn’t it be faster?

Today’s article provides a technique to sort Microsoft Excel tables by simply double clicking on a cell of the data table. As usual, including the workbook for free download.

Agreed, the time savings are humble, but give it a try. I guarantee, chances are high that you will love the feature.

(more…)

May 16, 2011