Using Excel Templates

Within ENVISION you can work with Excel templates to allow for greater customization of your report output. You can take an existing output and customize it in Excel, then use it as a template for running further reports. Also, easily migrate any existing Excel template to the most current year vintage when your licensed data has been updated.

Creating an Excel Template
Running a New Report Using an Excel Template
Migrating an Excel Template - new functionality

 

Creating an Excel Template
1. Begin by running one of the following reports in ENVISION.

ENVISION Canada Workspacesmceclip1.png

 ENVISION USA Workspaces
mceclip2.png


2. Once the report has run, open the Results page. 

results.JPG

3. Click the gear icon cog.png for the report you just ran and select Save Report Template.

rt.JPG

4. Give the report a template name.

5. In the Export type list, select either Batch or Side-by-Side.

mceclip7.png

Report Types:

  • Batch Reports: Outputs are formatted reports, one per data slice. For example, if you had run a report on three areas, you would have an output file for each of the three areas. 
  • Side-by-Side: Output is a single formatted report with each data slice side-by-side. For example, if you had run a report on three areas, the data for all of them would be combined into one single file, with the data for each area beside the next.

Note: Not all reports support the Side-by-Side option.  If you are downloading a number of different reports at once and Side-by-Side is not an option, please run the reports individually.

6. Click Create.

mceclip0.png

7. In the side panel, click on My Data and then Report Templates.

23.GIF

8. For the report template you created, click the Excel icon in the download column.

mceclip0.png

9. Open the downloaded file in Excel.

Note: If the Excel sheet shows all zeroes in any of the ‘formula’ worksheets, it is in Protected View. Click Enable Editing at the top of the Excel worksheet to continue.

mceclip0.png

10. Make all your changes in the Excel template from the data on the ‘Trade Area/Customers/Target Group_formula’ worksheet. Do not adjust either of the 'Details' pages.

Note: Do not change the positions of the first two worksheets, referenced by numbers one and two in the screen capture below. It is important that these worksheets ('Trade Area/Customers/Target Group' and 'Report Details') remain in the position in which they were created in order for the template to operate correctly.

The first tab of the Excel ENVISION output should look similar to the following:

mceclip5.png

You can change a number of things in Excel, including:

  • The sort order
  • Moving fields
  • Reformatting fields
  • Hiding/removing fields
  • Column headings
  • Branding (e.g. colors/colours)
  • Adding charts
  • Adding new worksheets

Tip: When copying and pasting individual fields in Excel, ensure that you copy the formulas. The formulas are all found on the 'Trade Area/Customers/Target Group_formula' worksheet.
For example, when moving a value from one cell to another, you may not be able to simply copy and paste. Instead, copy the formula (as shown below) and paste it into the cell as desired. 

mceclip12.png

Tip: If you are an experienced Excel user, you may want to consider copying and pasting the variable codes, and using a VLOOKUP to pull the descriptions and data from the first worksheet, to populate another.

Below, is the modified report to be uploaded back into ENVISION. Note some of the changes, including the colour, moving cells, formatted dollar values, and new sheets that have been added.

mceclip6.png

Data can be moved into a second sheet to create a chart. For example:

csect.JPG

11. When you are finished making changes, save the file as an .XLTX (Excel template) file.

12. In the side panel, navigate to My DataReport Templates.

23.GIF

13. To upload the template that you created, click Upload.

mceclip2.png

14. Once you see the status message in the bottom-right corner indicating your report has been uploaded, you can begin to use it.

 

Running a New Report Using a Template
1. In the side panel, navigate to My DataReport Templates.

23.GIF

2. Select the template you wish to use and click Run.

mceclip0.png

3. Select one or more Trade Areas. You can also use the search bar to easily filter your areas.

mceclip2.png

4. Select the Benchmark. You can also use the search bar to easily filter your areas. 

mceclip3.png

5. Once you have made your selection, click Run Report Template.

mceclip4.png

 6. Navigate to the Results page and click the name of the bolded report. It will automatically download the Excel output. The resulting report will be formatted to reflect the changes you made in the Excel template that was previously uploaded.

 

Migrating an Excel Template
1. In the side panel, navigate to My DataReport Templates.

23.GIF

2. Locate the template that you would like to migrate to the latest workspace vintage. Click the gear icon mceclip6.png and click Migrate.

mceclip7.png

Note: The migration process will always update the template to the latest workspace year available in ENVISION regardless of when it was created.

3. Add a Name for your report template and click Migrate.

mceclip8.png

4. If any variables from the original template are not found in the latest vintage of data, a pop-up window will appear listing these missing variables. Click Download to output a list of all variables that were not migrated. 

For example:

mceclip0.png

Note: Data are updated annually and variable lists can be subject to change. New variables may be added and others dropped from the variable roster according to changes in the applicable source data or survey.  For a full list of new or dropped variables for your licensed data please refer to the “Variables Change List” document included with the raw data delivery, or, if you do not receive the files, please contact Support.

5. To access the migrated Excel Template, navigate to the current year workspace by clicking My Workspaces.

mceclip9.png

6. Once you are in a current year workspace, use the side panel to navigate to My DataReport Templates.

23.GIF

7. Your migrated Excel template(s) will be listed. You also have the option to download a list of all unmigrated variables for each Excel template by clicking the gear icon mceclip1.png and selecting Unmigrated Variables.

mceclip2.png

Powered by Zendesk