How to Convert an Existing Excel Sheet into a Quick Form with a Few Select Fields with Background Calculations in Excel

excel2

There’s no need to spend 400-600 dollars on getting your spreadsheet converted to a form and published on a webpage.

In the example above, the first numerical field, “4.25”, is the only editable Field.

  • Create your Form the way you want it to look
  • Go to View > Page Break View > & set it the page to the size of your form
  • Select your Entire Form
  • Right-Click > Format Cells… (or Hit Ctrl + 1)
  • Click on the Protection Tab
  • Make sure Locked is Checked; Uncheck & recheck it just to be sure >OK
  • Ctrl+Select on the individual Cells you want to as your Input Fields that others will be able to type into
  • Hit Ctrl +1 again > Go to the Protection Tab > Uncheck Locked > OK
  • Go to Review > Protect Sheet > Allow all users of this worksheet to: Select Unlocked Cells only
  • Enter & Confirm Password, if desired.
  • OK

People can now only access the data entry fields you created, allowing the Calculations to automatically happen behind the scenes of the form.

*The Subtotal field was simply [cell1]*[cell2], where cell1= Hours & cell2 = Rate
*The Discount Field was formatted as Percentage to automatically put the % at the end
*The Total Field was =[cell3]-([cell4]*[cell3]), where cell3 = Subtotal & cell 4 = Discount %

To Publish it on a web page:

  • Upload your sheet to Google Drive
  • Double Click to Open it
  • Click on ‘Open with Google Sheets’
  • Go to File > Save as Google Sheets
  • Right-Click your Form > Protect Range
  • Click the ‘Sheet’ Tab
  • Check ‘Except Certain Cells’
  • Ctrl + Click on all the cells you want to leave open to data entry
  • Click OK
  • Click ‘Set Permissions’
  • Set to ‘Only You’

You can now go to File > Share and allow anyone to access it and get the shareable link and post it to your own website using your site’s own embed functionality. This free version of WordPress supports limited embed of Google Docs, and I can’t Embed the Link directly here.

If you embed it by the following method, it will be viewable, but not editable

  • Go to File > Publish to Web
  • Click the ‘Embed’ Tab
  • Select Sheet1
  • Click on ‘Publish’
  • Click OK to Publish
  • Copy the Embed code & Paste it on your Webpage

You can then either Turn the Embed into a Link or just make a different link which sends you an email for a request, where you can share the editable link to your Google Sheet directly to the people you need to.


You can also just copy and paste the worksheet into Word and use the same protections, but I feel this is quite the same as just having an Excel version

Another more time consuming option is to save it as a PDF then re-create all of your fields and re-enter all your formulas using Javascript.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s