Skip to content
Archive of posts tagged excel

Align Primary and Secondary axes

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 […]

List all folders in a Microsoft Outlook account

Recently, I wanted to create a list of all the folders in my MS Outlook PST file together with the size of each folder. Outlook provides that information through the user interface. Unfortunately, it shows the result in a modal dialog with no way to save the information elsewhere. So, I decided to check if […]

Geocoding with Excel and VBA

Before one can position an address (a street address or a town itself) on a map, it must be converted to geographic coordinates (latitude and longitude). Then, that latitude and longitude is mapped to a particular location on the map. Figure 1 shows several thousand U.S. cities and towns on a Mercator projection map. Well, […]

Extract the last token in a cell

There are times when one wants to extract the last part of a string, say the file name from a string that contains the filename including the path. This short note describes a few ways to do that. The example we will use is the following. Cell A1 contains the value c:\x\yz.jpg. What we want […]

Cardinal (i.e., real) numbers and a log scale for a Column chart x-axis

The x-axis of a Column chart can contains either category values (the x values are equidistant) or dates. Neither of these allows for a truly numeric x axis. This tip shows how to simulate a column chart that has cardinal numbers on the x axis (cardinal numbers contain a sense of ‘distance’ – e.g., 97 […]

Banding rows in an Excel Table

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/0120-1%20banded%20rows.shtml Tushar Mehta

Introducing TM Retro Slicer beta 1

Those who have used an Excel 2010 slicer may have wanted to see that capability in earlier versions of Excel. I like what Microsoft did with slicers in 2010 and 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 […]

Introducing TM Tornado

Tornado diagrams graphically display the result of single-factor sensitivity analysis. This lets one evaluate the risk associated with the uncertainty in each of the variables that affect the outcome. Single-factor analysis means that we measure the effect on the outcome of each factor, one at a time, while holding the others at their nominal (or […]

Visualize Data with Shapes on a Map

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/13-03%20Shapes%20on%20a%20Map.shtml Tushar Mehta

Insert a screenshot with Office 2010

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