Category Archives: Analytics Edge Basic Add-in

! Keep it Free and Unrestricted !

The Analytics Edge Basic Add-in is free, as is the included Free Google Analytics Connector. There are no account restrictions, no query restrictions, and the product is updated, maintained and supported. To keep it that way, I need your help to SPREAD THE WORD!  Twitter, Facebook, Reddit, at conferences, in meetups, or just mention it over a beer with colleagues.

Please take a few seconds to add a comment and rating to the product listing in the
Google Analytics Partner Listings.

leave-a-commentFirst, make sure you are LOGGED IN (upper right corner).

Just below the listing is a Write a comment link,
and make sure your select a Rating!

A quick rating is fine, but if you can add a few words about what you like (and don’t like) about the product, it all helps.

Keep it Free! Keep it Unrestricted!

 

 

01 Formatting Notes

Once installed and registered, the Analytics Edge Basic 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

 

Optional Free Trials

The Analytics Edge Basic 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.

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.

 

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.

 

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.

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

Adding Queries

With the Analytics Edge Basic 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.

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.

* Analytics Edge Basic 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!

ribbon

Installation problems
Руководство к Analytcs Edge на Русском

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.

register

Help Is Just a Click Away

help-and-supportClick 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, post to the Analytics Edge Simply Free support forum for assistance. A button is provided right in the ribbon bar for your convenience. Note: email support is NOT provided for the free products.

query-commentBasic Add-in Operation

The Analytics Edge Basic Add-in 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.

See the Results

The results are automatically written to the worksheet, overwriting any previous content. Note that you can format the results and 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.