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 Templates to the most current year vintage when your licensed data has been updated.

Creating an Excel Template

  1. Begin by running one of the following reports in ENVISION.

    ENVISION Canada Workspaces
    Canadian tools

    ENVISION USA Workspaces
    USA tools

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

    Results page in menu

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

    Click save report template in gear menu

  4. Give the report a template name.

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

    Customize name and export type

    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.

    Create button

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

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

    Excel icon

  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.

    Click enable editing in Excel

  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:

    Tabs in Excel

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

    Trade area formula

    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 color/colour, moving cells, formatted dollar values, and new sheets that have been added.

    Modified report in Excel

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

    Chart in Excel

  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.

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

  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.

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

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

    Select trade areas

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

    Select benchmark

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

    Run report template button

  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.

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

    Click migrate in gear menu

    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.

    Customize name

  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:

    Variables not migrated

    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 opens in new window.

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

    My Workspaces in menu

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

  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 gear icon and selecting Unmigrated Variables.

    Select unmigrated variables in gear menu