Posted on Leave a comment

Cell color based calculations

There’s no built in function in Excel that allows you to make calculations based on the cell color. However, there is a simple method with no VBA needed that allows calculations based on cells’ color.

Step 1 – Select cells of the same color

On the ribbon go to the Home tab and click Find and select drop-down button. Select Find. You can also use CTRL+F keyboard shortcut. The Find and Replace dialog box appears. In the dialog box click Options button.

Now click Format drop-down and select Choose Format From Cell option. The dialog box disappears. Point and click the cell with desired color eg. red. The cell’s format is grabbed and displayed in the dialog box preview.

Click Find all button. At the bottom of the window you can notice a list of all red cells. Click that list and use CTRL+A keyboard shortcut to select all cells. All red cells are highlighted. Now you cal close Find and select dialog window.

Step 2 – Define name

On the ribbon go to the Formulas tab and click Define name icon.

The Define name dialog box appears. In the Name texbox type name for the selected cells eg. Red. Click Ok button to close the dialog box.

Repeat the first and second step to define names for all the colors used in your table.

Step 3 – Calculations

In the example I will calculate Sum of numbers in all red cells. You can use the defined name in many other formulas as well.

Click an empty cell and type =sum( . Now type the name of the color and close prentices. Press Enter to confirm the formula. Notice that in the formula you used the defined name for cells region instead of referring to each cell separately.

Leave a Reply

Your email address will not be published. Required fields are marked *