Office hacks: Create a shopping list with automatic calculation

When we produce in Excel with automatic calculation of the shopping list, not only want to check a certain product, the color of the cell where the product will change, but also to be able to automatically calculate the price of the selected goods (Figure 1). To achieve such an effect, just use the check box, formatting conditions, plus SUM function, you can easily complete.


1. Insert the check button

First, the style of the table settings, such as the cell fill color, font color, etc.. Then, select the “Development Tools” tab, click “Controls → Insert → Checkbox (form control)”, respectively, inserted into the “Options” in each cell, delete the text content, only retain the check box. Then, drag and drop the first checkbox to the middle of the corresponding cell, and then drag and drop the last checkbox to the middle of the corresponding cell; select all checkboxes, switch to the “Format” tab, set all checkboxes to left-aligned, horizontally centered, vertically distributed (Figure 2). 2.


2. Set the check box

Insert column F as a secondary column. Right-click the first check box in column A, select “Set control format”, in the pop-up window of the “Control” tab, “cell link” select “$F$2”. At this point, when the check box is checked, the corresponding cell in column F will display the value “TRUE” (Figure 3). Other check box control of the cell link and so on.


3. set the cell fill color

selected A2: E2, switch to the “Start” tab, click “Style → Conditional Formatting → New Rule”, in the pop-up window, select “use the formula to determine the format of the cell to be set “, in the “format for the value of this formula”, select “$ F $ 2”; then click “Format”, set the font to “white”, set a color in the fill, and so on for other cell line settings. In this way, when the check box is checked, the corresponding row of cells will change the fill color and font color (Figure 4).


4. statistical calculation

In the need to display the statistical results of the cell (such as C20), enter the formula “= SUMPRODUCT ((TRIM (F2:F18) = “TRUE”)*D2:D18*E2:E18)”, you can check the statistics according to the (Figure 5).


Finally, the auxiliary column F is hidden. In this way, with automatic calculation function of the shopping list is completed.


Leave a Comment