Category Archives: Analytics Edge Standard Add-in

Formatting Notes

Once installed and registered, the Analytics Edge Standard Add-in can be used to quickly refresh multiple queries in your workbook. Simply select a destination cell and make a query, then repeat. To refresh all of the queries, click the Refresh All button on the Analytics Edge ribbon.

Some things worth pointing out:

Formatting will be preserved. If you format the cells in Excel, a refresh should not change the format (including conditional formatting) you have done. Note: if the data changes from text, date or numeric to a different type, the cell formatting may be confused by the new data and you may need to reset it to General.

Table formatting will be preserved. If you format a range as an Excel table, a refresh should preserve the table formatting EXCEPT: added columns, rows or calculated values will be lost. The table will be auto-resized to the data coming from the query.

Existing data blocks will be cleared. Analytics Edge looks for an empty row or column to determine the limits of the existing data ‘block’ or table in the worksheet. Everything inside that block will be cleared before the new data is written to the worksheet.

No merged cells. Analytics Edge does not support writing data to merged cells in Excel. Trying to do this will cause an error.

notes

Refreshing Queries

Refresh All Queries

Click the Refresh All button on the Analytics Edge ribbon to refresh all of your queries in the workbook at once.

refresh-all-queries

Refresh Current Query

click image to enlarge

click image to enlarge

To refresh only one query, select the worksheet cell where the query is based and click the Refresh – Current menu item from the drop-down below the Refresh All button.

Refresh Selected Queries

click image to enlarge

click image to enlarge

If you want to select which queries to refresh, click the Refresh – Selected menu item from the drop-down on the Refresh All button. In the dialog that opens, select the queries you want to refresh and click OK.

Auto-Refresh Pivot Tables

click image to enlarge

click image to enlarge

Microsoft Excel does not automatically update Pivot Tables in your workbook when you bring in new data, forcing you to manually refresh them from the Data ribbon. Analytics Edge has an option (turned on by default) that will trigger Pivot Table refreshes automatically whenever an Analytics Edge Refresh occurs. You can turn this feature off from the Refresh – Options menu item.

Moving a Query

Move a Query in the Same Workbook

To move an Analytics Edge query, simply move the comment to another location in the same worksheet, or another worksheet in the same workbook.

Move to Another Workbook – Cut and Paste

To copy queries between workbooks, select the worksheet cell where the query is, then select Edit Query – Cut from the Analytics Edge ribbon.

remove-a-query

Then select the new location and select Edit Query – Paste from the Analytics Edge ribbon.

Edit a Query

To edit a specific query, click on the cell where the query is located (remember a comment was placed in that cell so you could find it easily). Click on the Edit button on the Analytics Edge ribbon bar. The original query wizard will open with all of your previous settings.

edit-a-query

Make changes as desired and save the query. See the new results in your worksheet.

Delete a Query

To delete an Analytics Edge query from your worksheet, you must select the cell where the query is located, then either delete the comment or select the Edit QueryCut menu item from the Analytics Edge ribbon bar.

remove-a-query

Copy a Query

Copy in the Same Workbook

To make a copy of an Analytics Edge query, simply copy the comment to another location in the same worksheet, or another worksheet in the same workbook.

Copy to Another Workbook

To copy queries between workbooks, select the worksheet cell where the query is, then select Edit Query – Copy from the Analytics Edge ribbon.

edit-copy

Then select the new location for the copy and select Edit Query – Paste from the Analytics Edge ribbon.

Adding Queries

With the Analytics Edge Standard Add-in and optional Connectors, you can automate the download of data from a number of sources directly into Microsoft Excel worksheets. You can mix queries from multiple sources, and use different accounts for each query if you want; Analytics Edge makes it easy to refresh them all with a single click.

Here is a simple example of adding 2 Google Analytics queries to a worksheet. [images of Basic Add-in shown; Standard Add-in works the same]

Configuration

setup-google-analytics-account

click the image to enlarge

If you haven’t already logged in to the Google Analytics connector, you need to set up an account to use (see Accounts).

Make Your First Query

google-analytics-query

click the image to enlarge

Click on the Excel worksheet cell where you want your query results, and create a new Google Analytics query (see Your First Query).

See The Results

google-analytics-query-results

click the image to enlarge

The results will appear in the worksheet at the cell you had selected. Note there is a comment on the cell to remind you of the location of the query. Do NOT remove the comment or you will delete the query.

Make Another Query

click image to enlarge

click image to enlarge

Select a different cell to place the second query.

Create the new query and see the results in your worksheet.

Optional Free Trials

The Analytics Edge Standard Add-in comes with two free connectors by default, but you can also install free trials of many others with a simple click. You can start as many free trials as you want, but remember: the clock starts ticking right away so make sure you have to to evaluate them all.

ribbon-free-trials-available

Once the connectors have been installed, restart Microsoft Excel to activate them. They will automatically self-register for a fully functional trial period, and you can start using them right away.

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

 

 

* Analytics Edge Standard Add-in

Love the product? Leave a comment in the Google Analytics Partners Gallery or share

Share on LinkedInTweet about this on TwitterShare on FacebookPin on PinterestShare on Google+Email this to someone

Your first step is to Register the add-in you have installed. Just start Excel and click the Register button on the Analytics Edge ribbon!

Installation problems?

Click the Activate 30-Day License to start your free trial period. No payment is necessary and the product is fully functional. OPTIONAL: Enter your email address to stay informed of updates to the product and make it easier to reset or transfer your product licenses after computer updates.

Help Is Just a Click Away

Click the HELP! button on any wizard or on the ribbon bar menus for additional information about any specific function or wizard feature. If the information available doesn’t answer your question, you can look in the Analytics Edge Simply Free support Forum for assistance, or send an email to [email protected]

query-commentStandard Add-in Operation

The Analytics Edge Standard Add-in works very much like the Analytics Edge Basic Add-in; it allows you to assign queries to cells in your worksheets. Simply select a cell and open a Connector wizard. The results of the query will be written into the worksheet starting with the selected cell. A comment will be placed on the cell as a reminder of the query location.

Note: the comment contains a pointer to the query. Copy, delete or move the comment within the same workbook and you will copy, delete or move the query.

The Standard Add-in also allows you to Pivot your results, Arrange the columns, Sort and Filter the results before Writing to a Worksheet. When your Connector query is finished, the Standard Add-in will open a Functions Wizard allowing you to make these transformations to your data.

A temporary worksheet (ᴁ Step Results) highlighted in green will appear with the interim results, so you can see the impact of your selections on a sample of the results. When you click the Write to Worksheet button, the temporary worksheet will disappear and the transformed results will be written to your worksheet.

Formatting your worksheet

The results are written to the worksheet, overwriting any previous content. You can format the results and subsequent refreshes will not alter the formatting — you are free to put your own personal look to the data. CAUTION: if you change the query, you will be asked if you want to clear the formatting; this is useful if you want to insert a column and do not want to use the  previous formatting in the cells.

Refresh All, Current or Selected

Click Refresh All to re-run all the queries in the workbook. To refresh only the query for the selected cell, select the drop-down menu below the Refresh button and pick Current. To refresh several queries at once, pick Selected from the drop-down menu.

Edit the Query at Any Time

You can modify the query by simply selecting the cell and clicking Edit. The previous settings are automatically inserted into the wizard, and you can make changes or cancel.

cut-copy-paste-queriesCut, Copy and Paste

If you want to delete, copy or move any of the queries, simply delete, copy or move the comments. If you want to copy or move the query to a different workbook, the Edit Query button has a drop-down menu with Cut, Copy and Paste functions that work across workbooks.

Sync Comments and Queries

If you copy or move any queries, or if you insert columns or rows causing the queries to move, the comments will be out-of-sync with the new locations. Simply click on one of the query cells to re-synchronize them.