Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. What is the shortcut to ONLY select the visible cells? Guess, then click 'See Answer' as it can be done.
2. How do you copy from Excel (see 1 below) and paste into Word (2) WITHOUT seeing the filter button, gridlines, comment notes and more?

Guess and then view answer to see how it can be done!


Counting Coloured Cells in Excel

A common request is the ability to count the number of cells that are red, yellow, blue or any other color. In Excel you can use colors in some tools (Data Sort, Data Autofilter) but there is no formula that looks at the colors of the cells. There is some VBA code that you can create to allow for this but we prefer to avoid VBA code if possible.

There are a couple of possibilities.

If you are using Conditional Formatting to create the colors, then on a separate section of the spreadsheet mimic the conditions you set up in the conditional formats but have the result be a word (perhaps the name of the color ) or a number. Now you can use a COUNTIF to count how many times a certain color appears. You can then hide this section to make it look seamless.

If you are manually going in and changing the colors based on something other than a formula, you could create some columns/ rows to help make this easier. These can be used to input a word or number that means the same as the color. You could then use conditional formatting to  change the relevant cells to the color you want based on the word/ number in the helper cell. This way you only enter the ‘color word’ once, but you have the benefit of colored cells and the ability to count them.

If you have long lists with colors on them, use the AutoFilter to show certain colors. You could get the results from the bottom left of the Excel screen which tells you how many have been filtered, or else in an empty column copy and paste a meaningful word into the cells. You can then redo this for the various colors and use COUNTIF to count.