How to create advanced Microsoft Excel spreadsheets

Learn how to control Excel worksheets with spin buttons, scrollbars, option buttons, and checkboxes.

By Helen Bradley, PC World |  Software, Excel, how-to

Each checkbox control is independent of any other checkbox controls on the worksheet, so the user can select or deselect a checkbox in isolation, without initiating a change to any other checkbox. You can set up a checkbox control to be checked, unchecked, or 'Mixed' (a gray fill that disappears as soon as the user clicks the checkbox the first time). In practice, you'll probably need to use only the Checked and Unchecked settings. Each checkbox should be linked to its own 'Cell link' cell. It will return a value of False if it is unchecked, True if it is checked, and #N/A if you set it up as Mixed.

You can test the value that a checkbox returns by using an IF function to make a calculation depending on its state. An invoice, for example, might contain a checkbox with the label 'Include State Tax'. If you link the checkbox to cell A7, and if you type the formula =IF(A7,InvoiceTotal*0.085,0) into any cell in the worksheet, that cell will contain the value from the cell named InvoiceTotal multiplied by 8.5 percent (to calculate state sales tax) if the spreadsheet user checks the checkbox, and it will contain the value of 0 if the user leaves it unchecked.

You're on Your Way to Becoming an Excel Power User

Microsoft Excel's Form Controls provide a flexible set of tools that you can harness to make data entry easier and more interactive. These controls let a user click to select items rather than having to type the value into a cell manually. They can help reduce errors, and speed up routine data entry.

Would you be interested in seeing even more Excel power-user tips? Hit the comments and let us know!

  Sign me up for ITworld's FREE daily newsletter!
Email: 
 


Originally published on PC World |  Click here to read the original story.
Join us:
Facebook

Twitter

Pinterest

Tumblr

LinkedIn

Google+

SoftwareWhite Papers & Webcasts

Webcast On Demand

HP DevOps KnowledgeVault

Sponsor: HP

See more White Papers | Webcasts

Answers - Powered by ITworld

Join us:
Facebook

Twitter

Pinterest

Tumblr

LinkedIn

Google+

Ask a Question