xlStudio

Help

Excel template

Before starting, you need to have an Excel template ready. This can be:

  • The Excel file from which you generate populated copies
  • One of the multiple Excel files from which you collect data


When starting xlStudio, you will first need to select this template

Create files

With xlStudio, you can create personalized forms for distribution to a large number of people. The process is simple:

  • Define variables that you want to pre-fill in each file (e.g. country, store, costs 1/2019) by clicking Add variable
  • Make a list of the files you want to create, each with a specific file name and variable values, by clicking Add file info
  • Link variables to the cells you want to pre-fill by selecting a template cell and clicking Define variable cell
  • Execute by clicking Create files, and select a folder where to place the files

The created files can then be filled in by people, and you can proceed to collect back their input with xlStudio

Loading...

Collect data

With xlStudio you can extract data from a large number of Excel files. The process is simple:

  • Select the cells you want to collect from your Excel template
    • Click Define data cells, for cell values you want to collect on a single row. Use this as the default for most data
    • Click Define constant cells, for cell values you want to be present on every row for each collected file
  • Look at the preview of the collected data
    • Depending on the most natural structure for your data, you can change data orientation by clicking Flip axis
    • You can change header names by clicking on them
    • You can include the file name for each collected value by checking Include file name
    • If you have generated the collected files with xlStudio, you can include your variables by clicking Include variables

  • Execute by clicking Collect data, and select the folder where the files can be found
xlStudio now provides you with a consolidated data table, which you can import to a database or analyze with e.g. PivotTable
Loading...

Support functionalities

Import file info

The list of info for creating files can also be composed in a separate Excel and imported to xlStudio:

  • Organize your file info table in Excel to the same structure as the file info table in xlStudio
  • In Excel, copy (ctrl+c) the values in your file info table (exclude headers)
  • In xlStudio, click Import file info

 

Creating advanced forms and reports with variables and formulas

By linking your Excel formulas to cells you define as variables in xlStudio, you can quickly create forms and reports where a different broad set of figures is shown in each file (e.g. Financials_US.xlsx, Financials_Canada.xlsx, etc…):

  • In Excel, use lookup functions (e.g. VLOOKUP or INDEX/MATCH) to find values (e.g. sales) based on a relevant cell (e.g. country). You can place your background data (e.g. financials per country) on a separate sheet.
  • In xlStudio, create the lookup variables (e.g. country) and link them to the relevant cells in your template.

Now when your create your files, xlStudio will update the lookup formulas for each file and create forms or reports with broadly personalized data. Note, that you can exclude your background data sheet from the resulting files in Settings (left panel) – Created files sheets.

 

Protected sheets

If the template you create files from has password protected sheets with defined variable cells you need to provide the passwords in Settings (left panel) – Created files sheets.

We use cookies to improve our content and marketing. Read our Privacy Policy for details. By continuing to browse you agree to our use of cookies.