How to customize a form output using an Excel template

ProntoForms allows you to design and build a custom document template entirely in Excel.

ProntoForms allows you to design and build a custom document template entirely in Microsoft Excel. When a form submission is made, it will generate an XLSX file with your desired formatting and send it to the people and places specified in the data destinations you’ve configured.

By adding simple references to your form questions right in the Excel file, the answers can appear exactly where you need them to. All conditional formatting and formulas will continue to work within the Excel file, allowing you to continue to manipulate data in a familiar way.

Why use custom documents?

The ability to deliver editable documents in formats such as in Excel or Word can be an incredibly useful tool for teams running projects in-progress. Here are some use cases and benefits to using custom documents in ProntoForms:

  • Team collaboration. Give your team with a perfect interface to snapshot data and images from the field and put it into a format that can be easily passed around and manipulated by other team members using readily available Office 365 tools.
  • User experience. Maintain familiarity and reduce friction when introducing a new tool into your toolchain. With custom documents, you can collect data in the ProntoForms app, but display the data in ways your stakeholders are already familiar with.
  • Generate rich content. For example, you can put images captured in the form (like camera images, captured satellite images with sketches) into the Excel document. It is also possible to use graphs and charts and use ProntoForms’ DREL feature to fill it in with data from the form.
  • Branding & differentiation. Easily highlight your brand in fully custom documents. This might be very useful for customer-facing documents such as invoices. You can use your logo, color theme, fonts, etc. Coupling this with ProntoForms’ White Label option lets you create a fully custom solution and differentiate your service from the competitors.
  • Standards and interoperability. Meet industry or government regulations for document formatting. Your customer or third-party partners may require you to provide data in a pre-existing format. For example, some of our customers in the telecom infrastructure industry deliver finished Excel documents straight from ProntoForms to their customers.
  • Multiple outputs. You might need the document an editable document that leverages the formatting and graphing capabilities of Excel then gets processed into a read-only, PDF report for submitting to the customer.

Step 1: Create the form

Create a form from the MANAGE FORMS > Create New Form menu.

You can use the table below as a reference. The most important column is the Unique ID column, as we will use it in the Excel file in Step 2.

Unique IDName/TextTypeData Type
GPSVerify your locationGeo ControlGeo Location
DateDate & Time of inspectionDate/Time SelectorDate/Time
IDVehicle MakeTextboxText
ModelModelTextboxText
YearYearTextboxText
VINVIN #TextboxText
SeatbeltsSeatbeltsButton GroupText
InteriorLightsInterior LightsButton GroupText
InstrumentLightsInstrument LightsButton GroupText
WindowsOperationalWindows OperationalButton GroupText
GeneralCleanlinessGeneral CleanlinessButton GroupText
WindshieldWindshieldButton GroupText
Tail/BrakeLightsTail/brake LightsButton GroupText
TurnSignalsTurn SignalsButton GroupText
SafetyLightsSafety LightsButton GroupText
TireConditionTire ConditionButton GroupText
ParkingBrakeParking BrakeButton GroupText
HornHornButton GroupText
ExhaustExhaustButton GroupText
Free of grease/oil lFree of grease/oil leaksButton GroupText
MarkExteriorDamageTake a picture of the damageCameraImage
NameInspector NameTextboxText
InspectorSignatureInspector SignatureSignature PadSignature

Step 2: Create the Excel document for the template

Create an Excel file and populate the cells with your static data. Use ProntoForms’ Data Reference Expression Language (DREL) feature in any cells where you want ProntoForms to take answers from the form. Use the Unique IDs from the form with the DREL expressions.

Step 3: Create and add an Excel template as a document destination

Open the Form and click the DESTINATIONS tab. From there click the ADD A NEW DOCUMENT. You can find it under the Documents section.

Select Excel Template under the Visual and Printable Documents category.

Under the Document Basics tab, give the document a name, for example, “Vehicle Inspection.”

Under the Configuration tab, click the Choose File button and upload the Excel template you created in Step 2. Add two Cell Range References, one for the %a[MarkExteriorDamage] and another for the %a[InspectorSignature]. This will be used to place the vehicle image and inspector’s signature in the cell range defined. Click the Create button when ready.

Open the form and click the DESTINATIONS tab. From there, click on the Documents to attach field and select the template you just created.

You can now save your form and use a custom Excel document as one of the form’s outputs.

Sample form and template

You can experiment with the Custom Excel Document feature by importing the Simple Vehicle Inspection (with Custom Excel Output) form found in the Forms Library.

This form contains all the resources needed for your exercise, such as the Data Source, Form Image, the Form, an Excel Template, and the Data Destination.

Do you want to learn more about this topic?

To learn more about how to customize a form output using an Excel template, please visit the Custom Excel Document section of our Product Documentation Portal.

We’d also like to invite you to join ProntoForms’ Community where you can connect with other citizen developers. It is the ideal space for you to share your knowledge, learn everything you can about our product, and have fun with a great group of people. 

Related articles
 
 packshot
Take your knowledge of DREL to the next level
 
 packshot
Introducing ProntoForms’ new Microsoft User Group
 
 packshot
ProntoForms and Microsoft Deep Dive: Connected from Office to Cloud