Counting individual cells based on their value is simple in Excel, but what if you want to count the number of red-filled cells? Or the number of yellow-filled cells? If you have an entire row or column of cells that share the same fill color, counting them can be tricky, but it’s not impossible! In this article, we’ll go over how to figure out how many filled cells are within a row or column.
Step 1: Select All the Data
First, you need to select all of the data that you want to count. To do this, click on the cell in the upper-left corner of your data range and drag your mouse down to the bottom-right corner. This will highlight all of the cells in your range. Click on Home > Conditional Formatting > New Rule. In the first box (condition), enter Cell Value Is and then select Blank. In the second box (format), choose a font color that is different from any other colors you have used.
In my example, I selected green because it is very different from black or blue text with white backgrounds. Next, uncheck Show Text because we don’t want it to show up when counting our cells. In the last box (select format only), deselect All formats, and instead select Font Color. Now we’re ready to count! Press OK and return to your spreadsheet.
This time, instead of using conditional formatting rules, change the fill color for cells that contain blanks. Select those cells with your mouse cursor (it should turn into an arrow) and then press Ctrl+1 on Windows or Command+1 on MacOS. You’ll see a dropdown menu with lots of different fonts and colors. Scroll through the list until you find one that’s unique enough to be able to tell apart from others on your worksheet, like turquoise or pink. Click on it once and you’ll notice that the selected cells are now filled with that new color; repeat this process until all empty cells are filled in some way.
Then, navigate back to your Conditional Formatting Rules window by pressing the gear icon in the toolbar at the top left of your screen. Highlight your Cell Value Is rule and scroll down to where it says Format only. Choose both Font Color and Fill. Now go back to your spreadsheet and make sure everything looks right. If you didn’t check off Show Text, you should see a red X over every blank cell so you know which ones still need filling out.
Step 2: Apply Conditional Formatting
You can use conditional formatting to quickly identify cells that meet criteria you specify. For example, you can find all cells that contain a number greater than or equal to a certain value, or all cells that are blank. To apply conditional formatting, follow these steps:
- Select the cells you want to format.
- On the Home tab, in the Styles group, click Conditional Formatting.
- Click Highlight Cell Rules, and then click Greater Than.
Step 3: Delete Unwanted Columns
- To delete a column, click on the header of the column you want to delete. Right-click and select Delete. You can also select multiple columns by holding down the Shift key while clicking on each column header, then right-clicking and selecting Delete.
Step 4: Sort from A-Z First, Then Colors
Sorting your data from A-Z first is important because it will help you keep track of which cells have been counted and which ones haven’t. To do this, click the Sort & Filter button on the Home tab. Then, click Sort A to Z. Next, count the cells with each fill color. To do this, click the Conditional Formatting button on the Home tab. Then, click Color Scales. Finally, select the color you want to count.
Step 5: Expand All Rows into Individual Text Rows
If you have a lot of data in your spreadsheet, you may want to expand all of the rows into individual text rows so that you can more easily see what each cell contains. To do this, select all of the cells in your spreadsheet and then click on the Data tab at the top of the screen. Next, click on the Text to Columns button and choose the Delimited option.