Pivoting Repeating Columns Into Rows

Some Excel spreadsheets may contain repeating columns, such as sales figures for a series of years. There may even be repeating column groups, such as both budget and actual figures for a series of years. You can use the pivot option to transform these columns or groups of columns into rows.

Procedure: How to Pivot Columns Into Rows

  1. When uploading data or connecting to data on the main metadata screen, on the ribbon, click Pivot.

    The Pivot Columns to Rows dialog box opens.

  2. Set the Pivot Type option to Repeating column.
  3. In the First column drop-down box, select the first column in the range of repeating columns.
  4. In the Last column drop-down box, select the last column in the range of repeating columns.
  5. In the Column Title for Pivoted Data, type the new column title that reflects the numeric cell that you are describing.
  6. In the Title for Pivoted Key field, type the new column title that represents the repeating columns that you are pivoting into rows.
  7. Leave the Formula for Pivoted Key field value unedited. This value is automatically generated by the wizard, it should not be changed.

    An example of the completed configuration for pivoting columns is shown in the following image.

  8. Click OK.

    The repeating columns now display as rows. The Pivot button turned into the Remove Pivot button, allowing you to quickly revert your pivoting changes.

Procedure: How to Pivot Column Groups into Rows

  1. When uploading data or connecting to data on the main metadata screen, on the ribbon, click Pivot.

    The Pivot Columns to Rows dialog box opens.

  2. Set the Pivot Type option to Repeating group of columns.
  3. In the Number of groups field, specify the number of groups of columns that you are pivoting.
  4. In the Column Title for Pivoted Data, type the new column title that will be used for all the columns across the repeating groups.
  5. In the Title for Pivoted Key field, type the new column title that represents the repeating columns that you are pivoting into rows.
  6. Edit the automatically generated formula in the Formula for Pivoted Key field by clicking the ellipsis button. Make sure there are no repetitive alphanumeric values in the Pivoted Column field.

    An example of the completed configuration for pivoting groups of columns is shown in the following image.

  7. Click OK.

    The repeating groups of columns now display as rows. The Pivot button turned into the Remove Pivot button, allowing you to quickly revert your pivoting changes.

WebFOCUS