Formula Errors

How to Display Excel Prices Automatically When Checkboxes Are Selected

Maira Mehtab Sep 21, 2026 868 views

Question details

The user needs to set up a shopping list or order form in Excel where an item's price is displayed and added to a total only when its corresponding checkbox is checked, showing a blank result otherwise.

Product: Excel
Device & OS: not provided
Scenario: Creating an interactive shopping list where prices dynamically update based on user selections using form controls.
Observed behavior: The current formula fails to display the dynamic price because the checkboxes have not been correctly linked to the worksheet cells to output TRUE/FALSE values.

Before you start

Ensure the Developer tab is enabled in your Excel ribbon, as you will need it to insert form controls like checkboxes.

Solution 1Recommended

Link Checkboxes and Use an IF Formula for Dynamic Pricing

By linking each checkbox to a specific background cell, you can use an IF formula to display the price only when the cell value is TRUE.

Checkboxes in Excel act as independent objects floating above the grid. To make formulas interact with a checkbox, it must be linked to a cell so that checking the box changes the cell's value to TRUE, and unchecking it changes the value to FALSE.

Insert Checkboxes

Go to the Developer tab, click 'Insert', choose 'Check Box' under Form Controls, and draw one next to your first item.

Link the Checkbox to a Cell

Right-click the inserted checkbox, select 'Format Control', and navigate to the 'Control' tab. In the 'Cell link' box, type a cell reference (e.g., G2) and click OK.

Apply the IF Formula for the Price

In the cell where you want the conditional price to appear (e.g., E2), enter the formula: =IF(G2=TRUE, D2, "") where D2 contains the actual base price of the item.

Calculate Extended Prices and Totals

To calculate a quantity-based price, use =IF(G2=TRUE, B2*D2, ""). Copy the formula down your list (remembering to link each new checkbox individually). Finally, at the bottom of your price column, use =SUM(E2:E30) to calculate the total for only the selected items.

Hide Linked Cells for a Cleaner Look: To prevent the TRUE/FALSE text from cluttering your sheet, you can either hide the column containing the linked cells or change the text color to match the background (e.g., white text on a white background).

Efficient Spreadsheet Alternative

Create Interactive Checkbox Forms with WPS Spreadsheet

WPS Spreadsheet provides robust form controls and formula support, making it simple to build interactive lists, calculate dynamic totals, and manage data efficiently.

1. Enable Form Controls: Open WPS Spreadsheet, navigate to the 'Insert' tab, and select 'Check Box' from the Forms section.
2. Link to a Cell: Right-click the inserted Check Box, select 'Format Object', navigate to the 'Control' tab, and link it to a nearby cell.
3. Apply Formulas: Use the =IF() formula referencing the linked cell to dynamically display prices and calculate totals seamlessly.

Fully compatible with Microsoft Excel formulas and form controlsEasily insert and customize checkboxes for interactive listsLightweight application with a clean, intuitive interfaceFree to use for everyday data calculation tasks

microsoft office alternative - wps office

Frequently Asked Questions

Can I link multiple checkboxes to cells at once?

Excel does not have a built-in feature to link multiple checkboxes to separate cells simultaneously. You must link each checkbox to its respective cell individually via Format Control, or use a VBA macro to automate the process.

Why does my IF formula return a #NAME? error?

This error usually occurs if the cell references are mistyped or if text within the formula is missing quotation marks. Ensure your formula accurately points to the linked cell, e.g., =IF(G2=TRUE, D2, "").

How do I calculate the total price only for the selected items?

Once your IF formula populates the prices for the checked items in a dedicated column, simply use a SUM formula at the bottom of that column (e.g., =SUM(E2:E30)) to automatically add up all the displayed prices.

Why does my linked cell show TRUE or FALSE as plain text?

This is the default behavior of linked form controls. The cell evaluates and displays the binary state of the checkbox. You can hide the text by changing the font color to match the background or hiding the entire column.

Excel & Data Problems