Convert a Data Table from Cross-tab to Tabular Format

Cross-tab format is great for summarizing data, but not so great for working with it. Today’s post provides an overview of a VBA procedure that converts data from cross-tab to tabular format.

Overview

A cross-tab report summarizes data in a categorical table, with rows representing an item, while columns represent a category associated with that item. This permits the display of multiple data points on individual rows. In the following figure, a cross-tab format is used to display a company’s expenses for each month over a calendar year.

Data in cross-tab format.
Data in cross-tab format.

As opposed to cross-tab–which can show multiple data points on individual lines–a tabular report presents an individual row for each data point. The following example shows data in a tabular format.

Data presented in tabular format.
Data presented in tabular format.

There are many situations where you may receive a cross-tab report. Here are two common ones:

  1. Financial tables presenting monthly/quarterly/annual data: Each row will have a account description to in the left-most column, with amounts for each month in the following columns.
  2. Survey data: A name (or unique identifier) will be in the left-most column with answers to responses being in the following columns.

You may rather have this information in tabular format. This permits you to do further analysis with SUMIF formulas, pivot tables, and Excel’s other reporting tools. The script discussed in this post will help you convert cross-tab data table to tabular format.

Example File

Macros must be enabled upon opening.

Convert to Tabular

The conversion process is completed by two subroutines: one to pass parameters to the main procedure, and the main procedure which does the actual conversion. Let’s start by looking at the main procedure.

Main Procedure

We can explain what this does by looking at the cross-tab data in two sections:

Schematic showing cross-tab fixed data and data points.

  • Section 1 (red): This “fixed” data must be present for each row after conversion to tabular format.
  • Section 2 (green): Individual data points that will be provided rows.

The procedure will loop through each data point (green) and apply the “fixed” data along with each data point to an individual row.

Trigger Procedure

A single-line trigger procedure is used to call the main Tabulate_Data subroutine and pass custom parameters.

Parameters

The following parameters can be used to customize how the procedure behaves:

  • tableSheet: A required worksheet object identifying the workbook and worksheet (scope) where the cross-tab data exists. In the example file, ActiveWorkbook.Sheets("Interface") is the worksheet argument used.
  • fixedRange: A required range of columns that specify where the “fixed” data that should be copied for each data point in the cross-tab table. In the explanation above, this is the data in the red rectangle. This argument must be passed as a Range object made of columns only. In the example file, Range("A:B") is passed.
  • tabularRange: A required range of columns that specify where the cross-tab data points exist. In the figure above, this refers to the green rectangle.┬áThis argument must be passed as a Range object made of columns only. In the example file, Range("C:N") is passed. This is identified in the tabular data under “Tabular Value.”
  • headerRow: A required Long value to specify the row number that contains the table header. This will be presented in the tabular data under “Tabular Field.”
  • retainZero: An optional Boolean (True/False) to tell the procedure to ignore or copy zeros. True will create tabular rows for zero, whereas false will skip and delete. The default value is False.
  • retainBlanks: An optional Boolean (True/False) that behaves similarly to retainZero, but will add/remove blank values for the tabular data. True will create tabular rows for blank values, whereas false will skip and delete. The default value is False.
  • lastRow: An optional Long value to specify the final row of cross-tab data. A value of 10, for example, will run the operation through the 10th row of the cross-tab table. A value of zero will run through the cross-tab rows until an empty row is reached (in the left-most column). The default value is zero.

Other Notes

  • This procedure will not work properly if the cross-tab table contains any formulas. Please copy/paste values prior to running the operation.
  • Starting in 2016, Excel began offering a built in “unpivot” tool which accomplishes much of what this procedure does. You can read more about the unpivot tool at the Excel University blog. While the functionality of the procedure in this post is identical to the unpivot tool, the VBA solution does offer options for customization, such as including calculations or complex conditions.
  • The mechanics of this procedure largely revolve around four basic VBA concepts. If you’re interested in learning more about how this works, you should read about Do loops, For loops, If statements, and the Range object.

Leave a Reply