To make it easier to use an electronic form or calculator, you may want to hide certain rows in the spreadsheet until a specific condition is met. In a survey, you may even want to hide entire worksheets unless they are relevant. 

To hide rows or worksheets in a spreadsheet, use the Hide Rows/Sheets widget. First you specify what parts of the spreadsheet to hide, then you designate the condition that will trigger the display of the selected rows or sheets. Follow the step-by-step instructions below to use the Hide Rows/Sheets widget.

Sample webpage: Hide Rows Sheet

Sample spreadsheet:  Hide_rows_sheets.xlsx

 

Inserting the Hide Rows/Sheets widget

Step 1

Click on the Hide Rows Sheet icon in the Widgets tab of the Settings panel.

step1

Step 2

Click the Add new button.

addnew

Step 3

Select Hide Sheet to hide whole worksheets or Hide Rows to hide rows.

Step 4

For Hide Sheet, an option named Sheet to hide will appear where you tick the checkboxes corresponding to the sheets you want the browser to hide when a condition is true.

hidesheetstep234

For Hide Rows, an option named Rows to Hide will appear where you need to choose which rows you need to hide in the browser. Click on the Select Rows button and use the dialog box named Select Row range to select the range of rows to hide when a condition is true.

addrow-1

Step 5

In the Controlling Cell option, select the cell which will control if the rows or worksheets are hidden or visible. If the value of the controlling cell is non-zero or True, the condition is fulfilled and the controlled rows or sheets are hidden.

The controlling cell may contain a checkbox widget, a textbox widget or any other widget where values can be entered.

A default value for the controlling cell can be set from the link. Read more below.

step-234         hidesheetstep234

Step 6

To remember what these widget properties do, give them a descriptive name that tells you what you intended with the setup.

addrowstep6

Step 7

If you don’t want the controlling cell to be visible, use the Calculated Cell widget in the settings panel of the Spreadsheet Converter and check Make hidden field.

step5

Step 8

Convert the spreadsheet and test the form or calculator.

Using the link for Remote Control

The value of the controlling cell can be assigned a default value from the link, e.g. “../form.htm?hide_prices=true”.

The autosave process will normally save the latest value for all input fields in Browser Storage. This autosaved value will override the default value in the link. If hide_prices in the example above is unchecked on a form, it will appear as unchecked the next time the same user opens the same form. If you want the value in the link to override the autosaved value, you have to disable Browser Storage on the Workbook tab.

Editing and removing the “Hide Rows Sheet” widget.

Step 1

Click the Hide Rows Sheet widget.

step1

 

Step 2

In the list of hidden rows/sheets, look for the name you provided earlier while hiding rows or sheet. If you want to edit the settings, click the name. If you want to remove the hide rows sheets properties, click the “dustbin” button next to the name.

editstep2

 

Step 3

To edit a setting, click the corresponding “pencil” button.

edit_hrstep4

To change the sheets to hide, just check or uncheck the sheets accordingly.

edit_hsstep4

 

Step 4

After you click the “pencil button” beside the Rows to Hide option, the Select Row range dialog box appears. Select the rows to hide in the worksheet if the condition is true.

edit_hrstep5

 

 

After you click the “pencil button” beside the Controlling cell option, the Select controlling cell dialog box will appear to let you select the cell which will make the worksheets or rows hidden or visible.

 

edit_hsstep5

 

Step 5

Change the name of the widget properties, if required.

editstep6

 

Step 6

Click the Update button.

editstep7

 

Step 7

Convert the spreadsheet and test the form or calculator.