Contents
While working with spreadsheets, a situation may arise when you need to calculate how many cells are currently filled and how many are empty. To do this, there are several functions at once, each of which has its own characteristics of work.
It may also be necessary to fill all empty cells with a specific value that is in the top cell or in the user’s head. How can this be done? Now you will learn 4 cool ways with which you can make this task a reality.
COUNT, COUNT, COUNTBLANK function
This is the simplest function that has only one argument – a range of values. The figure below shows an example of how this feature works in practice.
In this screenshot, line 9 is taken, in which the calculation of values uXNUMXbuXNUMXbis only for those students who have already passed the exam. If we use the function SCETZ, then it is possible to determine the values of only those exams that students have begun to take. And finally, the COUNTBLANK function will only detect those exams that the students have not even started taking.
How these features work
The mechanism of these functions is very simple. The first (that is, CHECK) can only be used with numeric values. That is, there should not be any letters. To use it, it is enough to specify only the range of cells where the number of cells will be determined. So, the formula shown in the screenshot below finds out the number of cells with numerical values. That is, how many cells with numbers are in the specified range (in our case, B2:B6).
Unlike the function CHECK, formula SCHETZ defines all cells that contain at least some value, including text. Here, too, it is enough to enter one argument – the range. So, this formula finds all cells that contain at least one value that are located in the range B3:E5.
=SCHÖTZ(B5:E5)
If you need to determine the number of empty cells in the table, then you need to use the function COUNTBLANK. Its syntax is the same as the previous ones, but the purpose is different. In our example, it counts the number of empty cells located in the range B2:E2.
Each of these functions can also be used as part of other formulas. But sometimes a situation arises when you need to quickly fill empty cells with certain values. Let’s look at what needs to be done in order to implement this task.
How to fill empty cells
There are quite a few ways to make empty cells quickly filled with the necessary information. Some of them are semi-automatic, some are fully automatic, and all you have to do to run them is write a macro and run it.
Method 1: Fill empty cells with a given value
An Excel user can fill empty cells with a specific value, for which it is enough just to use the built-in functions of this program. Moreover, no special knowledge is required to use this method. Follow these steps:
- Open the sheet where you want to fill in empty cells.
- Select the required column.
4 - Go to the Home tab and select Find and Select. In the pop-up menu, you need to select the option “Select a group of cells”. After that, a dialog box will appear in which you need to select the “empty cells” radio button (this item is indicated in yellow in the screenshot).
5 - Then all cells that do not contain any values will be highlighted. Now it’s enough just to start writing (without pressing anything before) what you need, and then press the key combination Ctrl + Enter.
6 7
With this method, you can fill any number of empty cells with the desired value. As you can see, even no formulas were needed.
Method 2: Fill empty cells with the value in the cell above
If the cell above already has the correct value, and now it needs to be transferred to all empty ones, then there is nothing complicated here either. Excel provides the ability to do this. Just follow these steps:
- Open a worksheet that has empty cells that need to be filled in.
- Select the range where you want to paste the information from the cell above into all the empty ones.
8 - Now find the Find and Select button again and select the same Select Group of Cells dialog box. And again you need to click on the radio button next to the “Empty cells” item.
- Now open the formula bar, press the formula input sign (=) and click on the cell whose value we need. After that, press the Ctrl + Enter combination again.
To make it clearer, here are some screenshots for clarity.
Method 3: Using a Macro
A macro is a set of commands designed to achieve a specific goal. It allows you to further perform the desired sequence of actions literally in one click. Filling empty cells is no exception. The macro given by us also knows how to determine the active range, which then fills with the values from the cell above.
How to implement this method in practice? Follow instructions:
- Launch the document you are going to work with and open the desired sheet.
- Press the key combination Alt + F11 to launch the macro development environment.
Next, you need to insert the following code into the field.
Sub FillBlankCells()
Static rngUR As Range: Set rngUR = ActiveWorkbook.ActiveSheet.UsedRange
Dim rngBlank As Range: Set rngBlank = rngUR.Find(«»)
While Not rngBlank Is Nothing
rngBlank.Value = rngBlank.Offset(-1, 0).Value
Set rngBlank = rngUR.Find(«», rngBlank)
Wend
End Sub
We see that the above code fragment is very small. As a result, we will get the following result in the VBA editor.
Next, we need to save our macro. To do this, press the key combination Ctrl + S. After that, a warning will appear, which you can ignore. So feel free to click “No”. Then you need to set the key combination by which the macro will be activated. To do this, go to the menu “Developer” – “Macros”. After that, a window will appear in which you need to select the macro that will be used, and then click on the “Parameters” button. Then another window will open in which you need to specify the letter that will be pressed along with the Ctrl key in a small box.
Everything, now we can safely say that Excel is configured to fill all empty cells of the desired range with values from the cell above. Next, just select the desired data and press the correct key combination.
When using macros, keep in mind that the applied changes can no longer be automatically undone. Just manually make the cells empty again.
Method 4: Using the Power Query Add-in
Power Query is a set of very useful features that first appeared as an add-in back in Excel 2013. Starting from 2016, it is already built into the program and is presented as a “Download and Convert” group on the “Data” tab.
This add-in allows you to perform a huge number of useful actions with information: download a huge set of it from the Internet, load it from files of almost any type, delete unnecessary lines, company technical information, and perform many other transformations, including inserting information into empty cells.
This addon has a huge number of different advantages, such as:
- The ability to process a huge amount of information very quickly at once. It is even more convenient than macros for this purpose, since the latter can slow down the program quite well.
- If the original information changes, simply update the original request. If you use the above methods, you have to carry out all the transformations again.
Before you can use this functional add-on, you must do one of the following:
- Give a name to the range through the “Formulas – Name Manager” tab
- Format it in a “smart table”. To do this, you need to press the key combination Ctrl + T.
13
After we perform any of these actions, you need to go to the “Data” tab and click on the “From Table / Range” button. If older versions of Excel are used, then you need to find a separate tab called Power Query.
Next, we will see a window called the Query Editor. We need to select the desired column and open the “Transform” tab. There is an item “Fill – Fill down”.
Everything, there is only a little left – unload the table back into the document using the command Home – Close and load – Close and load into ….
If the original table has undergone some changes, then you just need to go to the “Data” tab and click on the “Update All” button. After that, the information will be automatically updated.
Conclusions
Now you can determine the number of empty (as well as filled with numbers or letters) cells, as well as insert the required value there. You can show imagination and use formulas. For example, when using method 1, you can specify some formula, after which it will be automatically inserted into all other cells.
The macro can also be modified to fit your needs. Professional skill with any tool (and Excel certainly is) implies the ability to be creative in the process. So we wish you creativity and ease of use of this and other Excel tools.