Category: Excel12

Minimum and maximum values of numeric data types

Posted on by Tushar Mehta

There has been many an occasion when I have wanted programmatic access to the maximum or minimum or smallest value of a data type. Some programming languages have built-in support through names like MaxInt. VBA, unfortunately, is not one of them.

I decided to “translate” the documentation defining the different data types into code. The functions Max{datatype}, Min{datatype}, and Smallest{datatype} return the appropriate value. The Max and Min functions should be self-evident. The Smallest function returns the smallest non-zero value supported by the data type. Since this is simply 1 for all data types associated with integers (Byte, Integer, Long, LongPtr, LongLong, and Currency), Smallest is defined only for data types associated with real numbers (Single, Double, and Decimal).

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/1003%20MinMaxVals.shtml

Tushar Mehta

Use an Excel chart to show a time snapshot and trace a path

Posted on by Tushar Mehta

These Excel charts were inspired by Hans Rosling’s TED presentation on Religion and Babies (http://www.ted.com/talks/hans_rosling_religions_and_babies.html). He is absolutely great at engaging the viewer with his ability to bring data to life.

One of the things he did in his presentation was show the equivalent of an Excel bubble chart. He showed how different countries measured over the years. He also created a trail showing how a country progressed over time.

I decided to do the same with an Excel bubble chart – and implement both capabilities, i.e., the time snapshot and the time trail tracing the path, without any VBA code! The example I used was data from one of a series of seminars I had taught to healthcare executives (check this to know more about it). They participated, in teams, in a real-time, interactive, web-based simulation. In the simulation each team made decisions about how much of their limited resources to invest in (1) product development and operations and (2) marketing and sales. Their profitability depended both on their own decision and also their competitors. The simulation typically lasted 10 to 12 periods. The scroll bar in each chart controls the period shown or the latest period, as appropriate. The checkboxes control which teams have their performance history traced in the chart.

While I implemented the solution in Excel 2010, it should work in Excel 2007 and Excel 2003, though, in all fairness, I haven’t verified the older versions.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/data_visualization/9e%20Bubble%20Chart%20by%20Period.shtml

Tushar Mehta

TM Excel Dynamic Path Navigator

Posted on by Tushar Mehta

One of the features that consumers may find useful is retracing their path as they navigate through different workbooks, different sheets, and different windows. This navigation would be equivalent of a web browser’s Previous and Next feature. While Excel does not support this natively, the code in this note enables this capability.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/1110%20Workbook%20Navigation%20Retrace.shtml

Tushar Mehta

Align Primary and Secondary axes

Posted on by Tushar Mehta

There are instances when there are data series plotted on both the primary and secondary axes. For example, suppose we want to plot the two series A and B in Figure 1, with the elements in column B as the x-axis values. The A series will be a column cart on the primary axis and the B series will be a line chart on the secondary axis.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/data_visualization/06%20Visual%20Effects.shtml

Tushar Mehta

Highlight row and column of active cell

Posted on by Tushar Mehta

By default, when the user selects a cell, Excel highlights the row and column by changing the color of the associated row and column headers. This tip shares multiple ways to highlight the row and column in more obvious ways as well as a way to highlight the cell in a specific column in the same row.


The emphasis is on the use of conditional formatting to accomplish the task. The minimal VBA code required to make it work is the same single executable statement for all of the different highlighting options!

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0121%20highlight%20row%20and%20col%20of%20selected%20cell.shtml

Tushar Mehta

Introducing TM Retro Slicer

Posted on by Tushar Mehta

Those who have used a slicer in Excel 2010 slicer may want to see that capability in earlier versions of Excel. I like what Microsoft did with slicers in 2010 and have wanted to extend that capability backwards. For those who haven’t used a slicer, it is a filter on a particular pivot field shown as an independent object.

So, with TM Retro Slicer, one can create a slicer in any version of Excel from 2003 to 2010.

TM Retro Slicer works with versions of Excel from 2003 to 2010. Once enabled, a slicer will be visible on the worksheet.

Switching worksheets will hide the slicer and it will reappear when the worksheet is reactivated.

The slicer will remain across a workbook close and re-open.

For more on this shareware solution, please visit http://www.tushar-mehta.com/excel/software/retro_slicer/index.htm

— Tushar Mehta

TM Custom Radar Chart add-in

Posted on by Tushar Mehta

A Radar Chart, also known as a Spider Chart, visually compares several entities (products, organizations, investment opportunities, or even people) on multiple dimensions. For example, a manager of a diagnostic imaging (radiology) center might want to compare her facility with the competition on dimensions related to patients such as Time To Appointment, Report Turnaround Time, No Show Rate, and Wait Time.

Or, one might want to compare 6 products A, B, C, D, E, and F on 6 different attributes: Aesthetic Appeal, Compatibility, Strength, Market Size, Durability, and Reliability. The Custom Radar Chart lets one easily compare all the products along each of the dimensions on interest. This is the example used in the documentation. With the axes normalized, the resulting chart would be

For more on this shareware product, please visit www.tushar-mehta.com/excel/software/custom_radar/index.htm

— Tushar Mehta