Functions Wizard

Functions Wizard

The Analytics Edge Standard Add-in allows you to make several transformations to your data before you write it into your worksheet. Once your query has been created, a Functions Wizard pops up offering you the ability to Pivot, Arrange, Sort or Filter your data as well as options for when you Write To Worksheet.

Pivot Function

Columns to Keep – select and rearrange the columns you want to keep in the resulting table. Select ‘– No Pivot‘ to leave the data as-is.

Pivot to Columns – select a column to pivot, so a new column will be created for each unique value in the selected column.

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.

Arrange Function

No Change – do not change the order of the columns.

Arrange By Name – Use this option to rearrange the columns based on the names of the columns. This is useful when you get imported data that may have additional, missing, or out-of-order columns.

Sort Selected – Use this option tab to sort the columns by the names of the columns themselves. Select the columns you want sorted

Sort Function

Select Columns and Order – create one or more sorting rules which will be applied in sequence to order your data. If two rows are equal in value for the first rule, the next rule is used to determine the order. You can specify as many rules as required.

Sorting Rules – you can change the order or remove any rules.

Filter Function

Add Filter Rule – construct one or more filters to be applied to the current table. Filters will be combined using AND logic, and you can choose to Keep or Remove the matching rows. While typically used to remove unwanted data from a report, it can also be used to selectively keep rows of interest.

Filter Rules – you can change the order or remove any rules.

Write to Worksheet Function

Do not include header – check this to prevent the column names from being included in the first row of the results written.

Do not clear cell formatting – this option will only change the cell values and not affect any cell formatting in place. Note that this can cause problems if the format of the cell does not match the data (numeric versus text versus date).