* Analytics Edge Core Add-in

Welcome to the Analytics Edge Core Add-in! Get ready for some simple Excel report automation. Just register the add-in, add an account, and start making your queries! More

* Register the Core Add-in

Before you can use an Analytics Edge add-in, it needs to be registered. In Excel, on the Analytics Edge ribbon bar, click the Register button. Note: the button will change to “License” if the add-in is already registered. More

1 Core Add-in Orientation

The Analytics Edge Core Add-in is designed to make it easier and a lot faster to create, edit, and refresh a custom Excel report. It is a bit different from other tools, so it is worth taking a minute to see how it works. More

2 Your First Macro (video)

Most people use Analytics Edge to download data from one of the connectors into Excel. This video shows how to create a macro that can contain multiple queries. More

Adding Connectors

Connectors are installed from inside the Add-in itself — in Excel, on the Analytics Edge ribbon bar, click the License button. All paid connectors are available free for 30 days, no payment required. More

Append

appendThis Analytics Edge wizard makes it easy to merge two tables together, keeping all of the rows and columns of the original tables. More

Append To Worksheet

This Analytics Edge wizard is used to append the current table of data to the end of an existing worksheet. More

Arrange

This Analytics Edge wizard is used to rearrange and/or rename columns in a table. More

Calculate

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

Combine

combineThis Analytics Edge wizard is used to combine two sets of data, merging the values from a worksheet or previously named table. More

Compare

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

Convert

convertThis Analytics Edge wizard is used to convert columns from one data type (text, number, or date) into another. More

Copy to Google Sheets

A new option for Analytics Edge is to copy the results to a Google Sheets sheet. This option comes with some limitations, identified below. The option is available in both the quick query Functions wizard, as well as in the Write to Worksheet macro wizard. Functionality is identical; click the Select/Modify button to launch the Google Sheets wizard. 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

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

Edit Mode Help

The Core Add-in has an ‘Edit Mode’ that shows you where your Quick Queries and any cell references have been placed (for start and end dates, for example). When Edit Mode is ON, the add-in places Excel comments on all the referenced cells. More

Filter

filterThis 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

add a calculated column to ExcelThis Analytics Edge wizard, used at the end of a macro, allows you to populate a column with an Excel formula, giving you full access to Excel’s advanced functions. It must be the last transformation in the sequence. More

Functions Wizard

Quick Queries in the Analytics Edge Core Add-in allow you to make several transformations to your data before you write it into your worksheet. More

Join

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

Lookup

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

Match

matchThis 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

Options

This wizard lets you configure Excel Pivot Tables refresh options and Email server settings. More

Pivot

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

Rank

rank order data in ExcelThis 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 Text File

readtextfileThis 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

Read Worksheet

readworksheetThis Analytics Edge wizard allows you to read an Excel worksheet into a table. The worksheet can be in any open workbook, and it is possible to read part of a worksheet if desired. More

Repeat Macro

repeatmacroThis 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

replaceThis Analytics Edge wizard lets you replace text in selected columns. It supports the usual * and ? wildcard matching. More

Run Macro Function

run-macro-functionAnalytics Edge macros that start with an underscore (e.g. “_setup”) will NOT run when you click the Refresh All button, nor during a Scheduled Refresh. More

Save/Email PDF

save-email-pdfThis wizard lets you save an Adobe PDF copy of the Excel workbook and optionally email it to someone. More

Schedule Manager

This Analytics Edge utility is part of the Core Add-in, and is used to manage all of the Analytics Edge scheduled refreshes. More

Schedule Refresh

schedule-refresh-2This Analytics Edge wizard helps you to schedule unattended refreshes for the workbook you have open. More

Sort

sort3This Analytics Edge wizard lets you create one or more sorting rules which will be applied in sequence to order your data. More

Split

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

Subtotal

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

Table Name

table nameThis Analytics Edge wizard lets you save the current table for reference later in the macro, such as with appending or comparing tables. More

Task Pane

taskpaneThe “heart” of the Analytics Edge Core Add-in is the Task Pane, which opens automatically to let you create, run and edit Analytics Edge macros. More

Top / Bottom

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

Total

totalThis Analytics Edge wizard is used to create an aggregate row or column, like Total or Average. More

Unpivot

unpivotThis 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

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

Write to Current Cell

The Core Add-in includes this shortcut menu item that mimics the functionality of the Basic Add-in. At any time during macro development, the current active table in memory can be quickly written to a specific cell on a specific worksheet. More

WriteToTextFile

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

WriteToWorksheet

This Analytics Edge wizard is used to write the results of an analysis to a worksheet. It can either overwrite an entire sheet, or be written to a specific location. It can also optionally copy the results to a Google Sheets file as well. More