When spreadsheets are converted to web pages, all cells are locked for input except those required to use the form or calculator. For forms, you may want to unlock additional fields for input, e.g. text fields that aren’t used in any calculations.

SpreadsheetConverter can automatically guess what cells in your worksheet you want to open for user input on the converted web page. It does this by looking at your formulas and seeing what cells they take their input values from.

  • Cells that are open for user input are called input cells.
  • Calculated cells that contain formulas are called output cells.
  • Cells that contain text are called label cells.
  • Cells that are empty are not open for input by default.

If you want to use an empty cell for user input, or a cell containing text, you must explicitly open the cell for user input. There are several convenient ways to do this:

  • automatic detection of input cells
  • using widgets to open cells for input
  • using a special background color for input cells
  • unlocked cells are input cells

The settings for input cell designation

The settings for input cell selection are on the Workbook tab in the task pane.

Switch to the SpreadsheetConverter ribbon

In Excel, look for the SpreadsheetConverter tab at the right end of the menu ribbon. Click the tab to switch to the SpreadsheetConverter ribbon.

Screenshot of the Excel menu with the SpreadsheetConverter choice

Open the SpreadsheetConverter task pane

In the SpreadsheetConverter ribbon, select Properties > Show so that the SpreadsheetConverter task pane becomes visible.

Open the Workbook tab

In the SpreadsheetConverter task pane, select the Workbook tab so that the Workbook Settings become visible.

workbook_tab

Workbook > Input cells

On the Workbook tab in the task pane, locate the Input Cells settings.

Screenshot of the Workbook settings for Input cells

Automatic detection of input cells

The first time you convert a particular spreadsheet, try using automatic detection first. Select Auto as the selection method.

With automatic detection, cell types are assigned like this:

  • A cell that contain a Text widget becomes open for user input.
  • A cell that contains a formula always becomes an output cell since allowing user input would overwrite the formula.
  • An empty cell that is referenced by a formula is opened for user input. Example: If A1 is empty and B1 contains =A1+1 then A1 will become an input cell by default since the formula appears to require user input, and B1 becomes an output cell in order to protect the formula.

Using widgets to open cells for input

Some input cells may not be obvious to SpreadsheetConverter’s automatic detection. If you are designing a form, you may want certain cells in the form to contain plain text input, e.g. address fields that you just want forwarded with the form when it is submitted.

There are also situations when you want to prevent certain fields from modification by the user, e.g. if you want to show the interest rate you are using for a loan but not allow the user to change it.

Two widgets make it easy to explicitly open or lock cells for input:

  • If you insert a Text widget into an empty cell, it is automatically opened for input on the web page.
  • If you insert a Utility widget into a cell, it is automatically locked for user input.

Using a special background color for input cells

If you need to open a large cell range in the spreadsheet for input, it may be tedious to insert many Text widgets. In this case it’s much quicker to use a special background cell color for the cells you want to open for input from the user. During conversion, SpreadsheetConverter locks all cells except the cells with the selected background color.

Begin by giving all input cells in the spreadsheet the same background color, in this example yellow. Ensure that you use a background color that is not used elsewhere in the workbook. You must background color all input cells, also the ones referenced by your formulas, since they will not be opened automatically.

Before conversion, select Cell color for Input Cells selection on the Workbook tab. Then click on one of the cells that has the selected background color.

input_color

If you don’t want the input cells to actually use this background color on the web page, check the checkbox Ignore background color for input cells and make them white. You will still see the assigned color in Excel, but in the web page all the input fields will have a white background.

Unlocked cells are input cells

If your spreadsheet uses Excel’s built-in protection mechanism for locked cells, you can also use it to explicitly unlock input cells. You must unlock all input cells, also the ones referenced by your formulas, since they will not be opened automatically.

Select Unlocked for Input Cells selection on the Workbook tab. Remember to unlock the worksheet before conversion!

Learn more

Some of our tutorials partly focus on opening cells for input:

Try this Excel add-in now!

Click on Download to install and test this Excel add-in for Windows.

Click on Upload to let us convert a spreadsheet for you for free.