This Analytics Edge wizard is used to pivot a column of data similar to Excel’s PivotTable. If you are pivoting by date, you can also select the date range and automatically fill in any missing values.

Pivot to Columns – select a column to pivot — a new column will be created for each unique value in the selected column. Select by position (column letter) or By Name.

Keep Columns – arrange the columns you want to keep in the resulting table.

Ignore Case – when combining duplicate rows, ignore differences in upper or lower case.

Ignore empty cells – if the pivoted column contains blank cells, ignore them and do not create a column with a blank name.

Cell Values – select a column to be used for the values in the cells in the pivoted table.

Aggregation – select the aggregation to use when there are duplicate values for a specific cell position.


Fill empty cells with zero – by default, cells with no data will be empty. This options fills them with 0.

Pivot By Date Options

When pivoting by date, extra options appear.

Display format – select or enter code to determine how dates will be display in the column names. Note that column names are always written into the spreadsheet as text. Use the See Codes button to open online help document describing the available date codes.


First date in the data – start at the earliest date in the source data.

Specific date – select or enter a specific date to use.

Rolling periods – set a duration (ending at the End date) of rolling periods to display. Rolling periods can start and end in mid-calendar period — e.g. a rolling week is 7 days ending at the end date selected, regardless of which day of the week it is.

Calendar periods – set a duration (ending at the End date) of calendar periods. Calendar periods always start at the beginning of a calendar period, regardless of when they end, so 1 calendar period may be a partial period.


Last date in the data – use the latest date in the source data.

Specific date – select or enter a specific date to use.

End of period – use the end of the selected period.

Fill in missing values – for the date range and step size selected, fill in all of the columns even if there is no data.

Blog article: Using the Pivot Function