JotForm User Guide / Advanced Features /

How to Set Up the Spreadsheet Widget?

How to Set Up the Spreadsheet Widget?

In situations where spreadsheet data needs to be collected, analyzed, and submitted in a simple spreadsheet format, the Spreadsheet widget is the ideal tool you can use. Your form users can now submit structured data sets that can be used in a spreadsheet - or for any purpose where cell-value data sets are critical.

In this guide, we will show you how to set up the Spreadsheet widget.

1. In the form builder, search for Spreadsheet under the Widgets tab and drag/drop it to add in the form.


2. The Spreadsheet widget has the following options:

Number of rows. To display how many rows you want to add.

Number of columns. To display how many columns you want to add. 

Column Labels. You can use this section to show a different label instead of the letters. This is optional. 

Default values. In this section, you can add a label for each cell in the spreadsheet. 

Read-only. If you to prevent users from editing the cell data. This section is where you will input the cell position. i.e. A3 - to disable Column A, Row 3.


3. The first three options are manageable, so let's start with the Default Values box. Here's an example:


Column D, Row 2 is filled with a default value of $1500. In the settings, you need to add the following:

D2 $1500.00 - Column Letter combines with the Row Number then space then the value.


In the same box, you can also assign a calculation value for a specific cell. The calculation formula will only need to start with the Column Letter and Row Number and followed by an equal sign (=) then the equation of cells.  Here's an example:


The total is placed on Column D, Row 1. The formula is like this:

d1 = a1 + b1 + c1


***Note that the Spreadsheet widget supports Match Functions as referenced from the link below:

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Math#Methods


4. For the Read-Only option. As explained above, this section allows you to set cells as non-editable. This is useful if there is a calculation on some cells and if you want that the result cannot be edited.

So, let's say, based on the above calculation, we want Column D, Row 1 to be read-only. In the widget, just add D1 in the box.



Click the Update Widget at the bottom to make sure that the changes are saved. And that would be all of the configurations.

You can see the demo form here: https://form.jotform.com/52644223831956

Do you have any questions regarding the steps or are you experiencing issues with the widget? Please let us know in the comment box below or create a thread to our support forum

Contact Support:
Our customer support team is available 24/7 and our average response time is between one to two hours.
Our team can be contacted via:
Contact JotForm Support: https://www.jotform.com/contact/

Send Comment

3 Comments...

  • wvffc

    I might be missing something, but in addition to the spreadsheet widget that I have put in my form, I have added other types of short answer, numeric questions, etc. I see the responses easily in the submission responses forwarded to my email, however, when I go to review all the data and add additional formulas to a downloaded version of the compiled results in an Excel document the individual fields where I have asked for multiple data fields to be completed all the values provided are located in one cell per submission and I am seemingly unable to calculate the total values of each fields' data collected in separate cells.

  • gand3rs0n

    Is there any widget that a) supports tabular input b) where I control the input to numeric only for specific columns c) have a prenumbered list?

    So far it seems the spreadsheet, matrix features, and input tables can do A and C and the configurable list can do b and c.

    As a s suggestion all table widgets should have the ability to control exact column data types. The inability to prevent alpha data going into numeric data columns really limits the usability of the form as data is captured into any sort of processing.

  • kstover89

    Hello,

    Is there a way to make the spreadsheet come through on the email submittal smaller in the pdf format so it fits better?

    Also, is there a way to code a total cell to round to the nearest .00? Right now it's pulling out 4 decimal places.

    Thank you,

    Kristin McAnally
    Verity Property Management, Inc.
    kmcanally@veritymgt.com