How to count blank cells in Excel

2019 3 Tip Friday Top Of Post Images.png

Welcome to your weekly 3 tip Friday update, it’s just a short dose of Excel help before you start the weekend. Each week we cover a tip, a shortcut and a function - making it super easy for you to get better at Excel. 

This week we cover how to insert comments into cells, how to hide and unhide rows and columns using a shortcut, and how to count blank & non-blank cells using functions.

Quick Tip: How to insert a comment in excel

To insert a comment in a cell, right click on the cell and click on Insert Comment:

1.png

You will see a yellow comment box connected to your cell, enter your comment:

2.png

When you are done, click outside the comment box and you will notice that the Comment box will collapse and a red triangle will appear on the top right of your cell.

3.png

This is the default state of the Comment box.

To see your comment, click on the cell and you will see that the Comment box will pop up again.

You can choose to always show the Comment box and not only when you click on it. You can do this by right clicking on your cell and clicking on Show/Hide Comments:

4.png

To edit the comment, right click on the cell with the comment and click on Edit Comment, this will select the Comment box, and the cursor ready to write inside it.

To reposition the Comment box, select the box (just click on it), drag and drop to the desired position.

To delete a comment, right click on the cell and click on Delete Comment.

Read more about this functionality by following this link.

 Keyboard Shortcut : How to unhide rows in excel

 

There’s a quick shortcut to unhide rows in excel, select the rows you want to unhide and press CTRL+Shift+9

To get a full list of Excel shortcuts, check out the Microsoft help article

A couple of useful functions to learn: COUNTA, COUNTBLANK functions

COUNTA functions is used to count all non-blank cells in a range.

Syntax:

value1,value2,value3 … this are the values that you want to count. Value1 is the only mandatory value.

In practice, you would very rarely enter values to be counted in this formula, you would instead select a range:

7.png

This formula will return 4, although you have 5 cells selected, only 4 of them are non-blank.

If your cell has a space, this is a character and will be counted in your formula as non-blank.

In the same way if your formula evaluates to “”, this will be counted as non-blank.

In our example above if you enter the following formula in cell A4 ( this formula returns “”), you will see that the COUNTA formula will now return 5 as it will be counting this cell as non-blank.

Read more about this function here.

COUNTBLANK function does the opposite of the COUNTA function, it will count the blank cells in a range.

The Syntax:

9.png

range – this is the range where you want to count the blank cells.

Let’s use this formula in the example above:

10.png

This formula will return 1, we only have one empty cell in our range.

Now try and enter a space in cell A4. You will notice that the formula will return 0 now, as the space is a character and not counted as a blank cell.

If we enter a formula that evaluates “” (as we did in the example above), we will see again that this cell will no longer be counted as blank.

Read more about this function here.

Get More Exclusive Content

Sent Directly to your inbox with Excel 3 Tip Friday. A weekly dose of Functions shortcuts and tips for getting more out of Excel.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit