Create Excel Spreadsheets

Do you spend countless hours day after day, month after month, putting together the same spreadsheets for reporting, projections, proposals, and more - with only a little bit of the information changing?  We can help!  We’re excited to announce that we have just launched support for Microsoft Excel spreadsheets!  You can easily generate spreadsheets that use calculations, charts, and pictures.  You can even export them to a PDF.

Below we’re going to show you how to setup a quick spreadsheet that is a proposal for customer acquisition that would be sent to a prospective client.  To start, we’re going to put in a little information about the customer, then we will enter some basic calculation parameters (current customers, growth rate, etc) and then our spreadsheet will calculate growth over the next year and even provide us with a nice chart at the bottom.

Note: To add merge fields to your spreadsheet, use the format: {$initial_users} or {$InitialUsers}  Make sure to use unique names for each field name. Do not use numbers on their own (eg '123') or include spaces, punctuation, special characters, or hyphens in the field names to ensure an easy integration process.


Once we’ve got our spreadsheet all setup, we going to do a quick test and make sure everything is working correctly.  We’ll just enter some basic information that is easy to double check the calculations.


As you can see, we’ve exported the spreadsheet to a PDF so that it is clean and professional looking when we send it off to our prospective client.  As you can see, the values in our calculations automatically calculated and the graph was updated accordingly.

Have more questions? Submit a request

27 Comments

  • Avatar
    Daniel Yeoman

    Can we restrict portions of pages or tabs from being converted into a PDF?

  • Avatar
    Jeremy Clarke

    Hi Daniel,

    Yes, you can hide sheets by right-clicking the name of the sheet and choose "hide".  If you choose to export to PDF, then it will export all the active worksheets (non-hidden)

    Edited by Jeremy Clarke
  • Avatar
    Alex Melzer

    For us it has worked to hide the tabs/worksheets in the excel file that we didn't want to print/merge. Also, changing the print setup of the file had an effect on the output, so you should be able to limit the output by limiting the printable area in the worksheets.

  • Avatar
    Jeremy Clarke

    Gregor - thanks for the suggestion!

  • Avatar
    James Lancaster-Peat

    Jeremy is it possible to merge a table in excel? Because the tablerow property won't work.

  • Avatar
    Jeremy Clarke

    Hi James,

    We now support {tablerow} loops in Excel documents!

    Edited by Jeremy Clarke
  • Avatar
    Bran Kop

    Is it possible to run Excel download macro to download text files from various web sites based on the latest timestamp, process them, convert to pdf and upload to the respective web sites?

  • Avatar
    Jeremy Clarke

    Hi Bran,

    Unfortunately, we do not allow macros to be run, but we might be able to come up with another solution. Please create a support ticket and describe in detail what you're wanting to do.

    Thanks!

  • Avatar
    Christopher Brown

    How do we insert a loop row into the Excel spreadsheet? (For example, if we're using Knack.) Would something like this work with Zapier also?

  • Avatar
    Jeremy Clarke

    Hi Christopher,

    You'll want to use the {tablerow} loop as described here: https://support.webmerge.me/hc/en-us/articles/206526116-Loop-through-a-list-of-items

    Zapier might work, but you need to format the data using JSON and send that over to WebMerge.

    Thanks!

  • Avatar
    Julio Vasconcellos

    The excel resulting from my merge is not recognising the number format as currency (for calculation). If I enter the cell (in the merged excel file) and hit enter it recognise as currency and the calculation works properly. What should I do to make it recognise it automatically during the merge?

  • Avatar
    Jeremy Clarke

    Hi Julio,

    Try using the "number" modifier for your merge field to ensure the data coming into the Excel doc is a number (without currency symbols or commas), like this: {$Amount|number}

  • Avatar
    James Pullman

    Does it support .xls files or only .xlsx?

  • Avatar
    Jeremy Clarke

    Hi James,

    Unfortunately, we only support .xlsx files

  • Avatar
    Maxim Sullivan

    Hi, I want a customer filled out web form (ninjaform) to add lines to an Excel spreadsheet.
    Is it possible to update a spreadsheet and add a row for each entry?
    Or would a new spreadsheet be created each time?

  • Avatar
    Jeremy Clarke

    Hi Maxim,

    Unfortunately, a new spreadsheet would be created each time. I suggest you use Zapier to save the data in a Google Sheet instead :)

  • Avatar
    Daryl Kelly

    When creating the excel document, what dimensions does the excel page need to be to accurately convert to a pdf?

  • Avatar
    Jeremy Clarke

    Hi Daryl,

    The best is 8.5x11 but you should be able to use any dimensions by setting the Page Layout and the Print Area. Please create a ticket if you're still have trouble and we'll help you from there.

    Thanks!

  • Avatar
    User_flyhouse User_flyhouse

    Is there a way to have each index of an array create a new sheet in the same workbook instead of a new row?

  • Avatar
    Jeremy Clarke

    Hello,

    Unfortunately, we don't have a way to create a new sheet, but what you can do is add all the sheets to your template, then reference the specific fields like this:

    Sheet 1:
    {$People.0.Name}
    {$People.0.Email}

    Sheet 2:
    {$People.1.Name}
    {$People.1.Email}

    Sheet 3:
    {$People.2.Name}
    {$People.2.Email}

  • Avatar
    Sales Engineer

    Hi Jeremy, can we create columns in an Excel output where each column corresponds to a row of a merged table? Thanks!

  • Avatar
    Jeremy Clarke

    Hello,

    Unfortunately, we don't have a way to dynamically create new columns, but you could hard-code the merge fields in each column like this:

    {$products.0.name} {$products.1.name} {$products.2.name}

  • Avatar
    Quentin Labarre

    Hi Jeremy,

    Is it possible to set the following predefined parameters for each excel outputs generated through the app ?
    Set Personalized margins :
     * Top and bottom : 0.4cm
    * Left and right : 0.6 cm
    * Header and footer : 0.8cm
    And also  Set "Page width" to 1 page and dynamically set "Page Height" based on the number of pages you get in your output (for example 6 pages if creating a 6 pages invoice)
    Thanks

    Edited by Quentin Labarre
  • Avatar
    JONATHAN TACK

    Hello, is it possible to make Excel automatically merge-and-left justify cells, or just cascade the formatting of the merged (brought together) cells ? need to make a long list wrap in a cell, but the cells need to be brought together to do so.

  • Avatar
    Jeremy Clarke

    Hi Jonathan,

    There isn't a way for us to automatically merge the cells, but you can merge the cells in your template. Will that work for you?

  • Avatar
    JONATHAN TACK

    i can merge the cells in the Excel template, but then when Webmerge puts in the data using {$tablerow}, the subsequent Excel cells will not merge (combine). This may be a moot point as i may have a workaround in Excel itself... but now that I have your ear, will Excel fire a .xlsm macro during the Webmerge process? Or, do you know how to make Excel conditionally hide blank rows? I just need to hide certain rows that are blank

    Edited by JONATHAN TACK
  • Avatar
    Jeremy Clarke

    Jonathan,

    You can use {tableif} to hide rows. More info: https://support.webmerge.me/hc/en-us/articles/206526076-Conditional-Sections-if-else-

    We allow you to use macro files, but you can only export the macro file (not a PDF) because we don't process the file on our side. We just populate the merge fields and deliver that file.

Please sign in to leave a comment.
Powered by Zendesk