Clearly and Simply

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.

Display all Fields of a Row in large Excel Tables

How to simplify the exploration of large Excel tables by showing the entire data record of the active row in additional text boxes

From time to time most of us have to work with very wide tables in Microsoft Excel. Tables with 30, 40 or even more columns, too wide to be visible on your screen at a glance.

When investigating this data, it is often the case that you would like to see some values from columns that do not fit on the screen at the moment. E.g. you would like to see the sales and the profit at a glance, but the columns are so far from each other that they are not visible at the same time.

Sounds familiar? What are you doing? Scrolling back and forth? Hiding and unhiding columns? A second window? Whatever technique you are using, I would assume you are sometimes wishing for something more convenient. A feature that shows the entire data record of the active row without scrolling or any other additional actions.

Today’s post provides such a feature to improve the navigation within wide data tables. Two additional textboxes automatically show the values of the invisible columns for the active row. No additional clicks necessary. Simply navigate through your worksheet and click on any cell in your data table and the textboxes will automatically update without obscuring the active row.

As usual, the article provides the Microsoft Excel workbook for free download.

Charts in Excel Table Tooltips

How to display a Chart in the Tooltips on Microsoft Excel Tables visualizing the selected value within the context of the data distribution

The recent post Tooltips on Microsoft Excel Tables showed a way of how to create tooltips in Microsoft Excel tables displaying additional information on the selected cell / value and some aggregated measures for the data in the active column, like sum, average, standard deviation and others.

If you are particularly interested in setting the selected value into the context of the distribution of the entire data, you may want to have a visualization in your tooltips. In other words a chart as a tooltip on a numeric data table. Sounds like turning the whole tooltip idea and concept upside down, doesn’t it? However, from time to time this can be a great alternative for adding more analytical power to your Excel tables.

Today’s post provides a short tutorial how to create a tooltip displaying a chart. As usual, including the Microsoft Excel workbook for free download.

Tooltips on Microsoft Excel Tables

How to display Tooltips on Microsoft Excel Tables including additional information on the selected cell and aggregation results for the entire column

Tooltips are a very helpful feature when exploring and investigating data. On charts and on plain data tables. When referring to tooltips, I am talking about additional details on the data currently selected or hovered over using the mouse. Tooltips could include other dimensions and measures, calculations or aggregated information like totals or averages.

Tableau Software provides very powerful and easy to use tooltips. If you are working with Tableau, I once again recommend having a look at Andy Cotgreave’s great Tableau tooltip articles on the Data Studio (Tableau Tooltips: Conditional Formatting or Charts inside a tooltip? Yes, we can.). Thanks again for sharing, Andy.

Compared to Tableau, Microsoft Excel’s tooltips functionality is next to nothing. We already had a post showing how to improve tooltips on Excel charts: Better Chart Tooltips with Microsoft Excel 2010.

However, tooltips can be very helpful in plain data tables as well. Sure, Excel automatically displays some calculated aggregations of the selected range in the status bar. Yet, this is not enough. Your eyes always have to jump down to the status bar to see the information, the displayed quick calculations are limited and inflexible and you always have to select the range first. If you are working with larger data tables, you may wish for tooltips providing additional information on the selected value (like the percentage of total, the rank, etc.) and on aggregations across the entire column (like sum, average, etc.).

Today’s post provides two slightly different approaches how to create better tooltips on Microsoft Excel tables. As usual including the Excel workbooks for free download.