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 it needs to open for input. It does this by looking at your formulas and seeing what cells they take their input values from. If you insert a Text widget into a cell, it is also automatically opened for input on the web page.

  • Cells that are open for user input are called “input cells”.
  • Cells containing formulas are called “output cells”. A cell containing a formula cannot be opened for input.
  • Cells that just contain text are called “label cells”. If you want a label cell or an empty cell to be open for input by the user, you must explicitly change it into an input cell.

To open additional input cells that may not be obvious to SpreadsheetConverter (e.g. for extra lines in address fields), or to prevent certain fields from modification by the user, you need to use one of the two methods that SpreadsheetConverter provides for manual designation of input cells: background color or unlocked cells.

The Settings panel and the Workbook tab

The settings for input cell selection are on the Workbook tab in the Settings panel.

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.

ssc_ribbon

 

Open the SpreadsheetConverter Settings panel

In the SpreadsheetConverter ribbon, select Properties > Show so that the SpreadsheetConverter Settings panel becomes visible.

ssc_panel

Open the Workbook Settings panel

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

workbook_tab

Workbook > Input cells

In the Workbook Settings panel, 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, the following kinds of cells are converted to input fields, open for data entry:

  • Cells that contain a Text widget.
  • Cells that are referenced by a formula.

Using background color to mark input cells

If you need to use other cells in the spreadsheet for input, you can use a background cell color to explicitly open cells 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, e.g. 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”.

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.

Build live website calculators and web forms now!

conversion imageconversion imageconversion image

Just download, install and test the Excel add-on now. Or let us convert a spreadsheet for you, for free.