* Desktop App

How can I help? Email: support@analyticsedge.com

The Analytics Edge Desktop App is a standalone application that provides spreadsheet report automation to both Google Sheets and Excel files on Windows or MacOS computers.

More

* Desktop App Orientation

When you open a file, the App interface mimics a typical spreadsheet application, with a menu bar, a strip to display or edit cell contents, a worksheet display area, and a task pane area.

More

* Licenses

The License wizard for the Analytics Edge Add-in for Excel [and the Analytics Edge Desktop App] provide a single location to manage product configuration and licensing. More

* Mode: AE Macro

With this option, you can record a sequence of functions that can easily be edited and refreshed. Macros are created and edited using the Task Pane located to the right of the sheet. More

* Mode: Interactive

Choosing the Mode on the Analytics Edge ribbon lets you decide if you want a macro function or connector function to start an Analytics Edge Macro, a Quick Query, or simply run the function once (Interactive). More

* Mode: Quick Query

Available only for connector queries, selecting this option targets a new, refreshable query to be written to the selected worksheet/cell. After the query runs you will be presented with optional Quick Functions wizard. Intermediate results are visible in the ‘ᴁ Step Results’ worksheet behind the wizard. When you are ready, click the Write to Worksheet button to write the results to the originally selected worksheet/cell location. More

Append

Append a previously saved table, or the contents of a worksheet in the same workbook, to the current table in memory. More

Calculate

This Analytics Edge wizard lets you perform typical math calculations without using formulas, including a number of series calculations across rows or down columns. More

Combine

Combine two sets of data, merging the values from a worksheet or previously named table. This is generally used with two similar data sets, totalling the numbers, retaining the most recent date, and/or preserving a list of category values. More

Compare

This Analytics Edge wizard is used to compare the current table with another, computing the difference between matching rows. More

Convert

This Analytics Edge wizard is used to convert columns from one data type (text, number, or date) into another. It can also convert to a variant of the same type, like converting dates to first of the month. Typically used to force imported data into the correct type (such as dates with odd formats), it can also be used to convert dates and numbers into specific formats for reporting purposes. More

Date Format

Analytics Edge supports Microsoft .NET Framework date format codes which are slightly different from Excel date format codes. The common variants are listed below, but see the references at the end for a full listing. More

Duplicate

This Analytics Edge widget recognizes duplicate rows in your data and lets you choose whether to keep them, remove them or combine them. More

Filter

This Analytics Edge wizard lets you construct one or more filters to be applied to the current table. Filters can be combined using And / Or logic, and you can choose to remove or keep the matching rows. More

Formula

This Analytics Edge wizard inserts a column with an Excel formula, giving you full access to the spreadsheet application’s advanced functions. It is evaluated in the spreadsheet AFTER you write it to a worksheet, so it should be followed by a Write to Worksheet function. More

Group

This Analytics Edge wizard lets you add a category or group column based on the value in one of the other data columns. More

Join

Typical uses include combining name or address components into a single column for uploading into a database. More

Lookup

This Analytics Edge wizard lets you quickly lookup values from another Table or Worksheet that share an index column with the current table. More

Match

This Analytics Edge wizard is used to keep or remove rows based on whether matching row values exist in another table or worksheet. More

Number Format

Analytics Edge supports Microsoft .NET Framework format codes which are slightly different from Excel format codes. The common variants are listed below, but see the references at the end for a full listing. More

Pivot

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. More

Quick Functions

Following a Quick Query, the Quick Functions wizard allows you to PivotArrangeSort or Filter your data as well as provides options to Write the results. More

Rank

This Analytics Edge wizard is used to convert the selected column into a number representing either the rank order or it’s percentile, decile or quartile position. More

Read Google Sheet

This Analytics Edge wizard is used to read the data from a Google Sheets worksheet. It always reads the entire sheet. More

Read Text File

This Analytics Edge wizard allows you to import a text file into a table. It accepts both delimited files as well as fixed-width fields. More

Repeat Macro

This wizard converts the current macro into a Repeating Macro. Repeating macros allow you to load a tables of values from a worksheet to be used as Analytics Edge named ranges while running the rest of the macro. More

Replace

This Analytics Edge wizard lets you replace text in selected columns. It supports the usual * and ? wildcard, as well as regular expressions. More

Run… If…

This wizard allows you to run Analytics Edge macros, Excel VBA macros, or Windows commands from your macro. It also allows your to set criteria (Run If…) to determine whether to run or not. More

Save/Email Excel Workbook

This wizard lets you save an Excel workbook copy or your report, and optionally email it to someone. Email options include SMTP email or Google Gmail. More

Save/Email PDF

This wizard lets you save an Adobe PDF copy of the Excel workbook, and optionally email it to someone. Email options include SMTP email or Google Gmail. More

Sort

This Analytics Edge wizard lets you sort the data using specific sorting rules or generically sort all numeric columns. More

Split

This Analytics Edge wizard lets you split a text column into several columns. You can split on a delimiter, a fixed-width sequence, or into smart name columns. More

Subtotal

This Analytics Edge wizard allows you to subtotal a table based on unique values in selected columns. More

Table Name

This Analytics Edge wizard lets you save the current table, and switch back to it or reference it later in the macro. It can also assign Analytics Edge range names to all the columns. More

Top / Bottom

This Analytics Edge wizard lets you keep or remove a specific number of the top or bottom rows. More

Total

This Analytics Edge wizard is used to create a numeric aggregate row or column, like Total or Average. You can choose to aggregate only the first or last group of numeric rows or columns, and position the results before or after the numeric data, or to remove the original (now aggregated) data completely. More

Transpose

This wizard is used to transpose (swap) rows with columns in the current table. More

Unpivot

This Analytics Edge wizard is used to perform a reverse pivot operation, converting multiple columns of data into two; one with the original column name and the other with the value. More

Update

This Analytics Edge wizard lets you update the current table from entries with matching rows in another table or worksheet. More

Web Request

This function allows you to make a raw web request and parsing the response. Note that this is  a very technical function that could be used in a wide variety of ways, so limited help is available here. Contact support for clarification if needed.

More

Write To Google Sheet

This Analytics Edge wizard is used to write the results of an analysis to a Google Sheets worksheet. It always overwrites the entire sheet. It can can be added as a separate macro function, or as an option from the Write to Worksheet wizard. More

Write to Text File

This Analytics Edge wizard lets you write the current table to a delimited text file, specifying the format to be used for dates. More