Several easy methods to fill cells without text in Excel

Today you will learn a simple method how you can select all cells that do not contain any values ​​in Excel and fill them in one go.

When there are many empty cells in the table, it is quite logical to ask whether to fill them or not to fill them. There are several reasons for this. One of them is that the table has a much more attractive appearance, and it is much easier to figure out what data in it means what. After all, there is no abundant clutter with repetitive values. But there may be additional difficulties when the user tries to organize all this data. If there is a need to reduce several tables into one, difficulties can also arise.

In this case, you must fill in each empty cell. There are a number of methods that allow you to solve this problem as soon as possible. And we will show you one of them, which can be used in any version of Excel starting from 2010.

Therefore, the answer is: “Of course, fill it out.” And now we’ll figure out how to do it.

How to select cells without values ​​in them

The first step in filling empty cells in Excel is to select them. If the sheet is large, and there are a huge number of them, and they are all unevenly distributed, manually filling them with the necessary color is a rather difficult task. But there is a simple life hack with which you can select empty cells as quickly as possible:

1. Select the appropriate columns.
2. Press the key combination Ctrl + G or press the F5 button (Fn + F5 in some laptop models). After that, the “Transition” window will open before the user’s eyes.
3. At the bottom of it is a button labeled “Select” (on the left).

Attention! If the law of meanness worked, and you forgot which key combination to press in this case, you can find this item on the “Home” tab, in the “Editing” section (in the menu that appears, find the “Find and select” button). After clicking on it, the item “Select a group of cells” will become visible, which allows you to do the same.

This command gives you the ability to highlight cells based on their content. Since in our case nothing is written in them, we do not write any text, formula or numbers in a special field. Instead, you must specify the item “Empty cells” and fix the result by pressing the “OK” key.

Everything, the first point of the task is completed.

How to apply a formula to fill cells

Selecting cells is almost half the battle. After that, they can be filled with values ​​located above or below. Also, if necessary, you can prescribe special content in them.

If you need to implement the first paragraph, you will need to enter a simple formula in one of the highlighted cells. After that, just copy it to all the others. To achieve this goal, follow the instructions:

1. Use the above procedure to select all cells that do not contain any values.
2. Using the F2 key (on some laptops – Fn + F2) you can start the input mode. The standard way is also possible – to place the cursor in the formula bar. In our case, cell C4 will be used for this task.
3. Write the formula input sign (=).
4. Click on the cell above or below the one you want to fill. You can also click the up or down arrow.
5. Using the Ctrl + Enter combination, the formula is copied into each empty cell.

Due to the fact that each previously empty cell now contains a formula, we advise you to replace it with some values ​​later. Otherwise, at the slightest permutation in the table, complete chaos can occur. Similar difficulties may also arise when entering updated information.

Filling empty cells with a specific value

There are two simple methods to solve this problem.

1 method

1. Select empty cells.
2. Enter a value in the active cell using the F2 button.
3. Enter the text or numeric value you want.
4. Fill in all other cells with the Ctrl+Enter combination.

Done.

2 method

1. Select a range containing empty cells.
2. Open the Find and Replace dialog box using the Ctrl+H combination or the corresponding menu item on the ribbon.
3. Open the “Replace” tab.
4. Leave the first input field empty (because the value of the cell to be replaced is entered there), and enter the required text in the second field, as shown in the screenshot.

After that, you need to click on the “Replace All” button.

Using a special add-on

Many people find the use of formulas a rather unpleasant process and want to get rid of such methods. This can be done with the helpful , developed by Ablebits. This utility allows you to copy a value that is above or below in a fully automatic mode.

How to use the add-on?

1. Download it and install it on your computer. After that, the “Ablebits Utilities” tab will appear on the Excel ribbon.
2. Select the desired range of cells.
3. Click on the “Fill Blank Cells” button on the “Ablebits Utilities” tab.
4. After that, the add-on will show which columns are selected using a special window.
5. Deselect those columns that do not have empty cells.
6. Select an action from the drop-down list in the lower right corner. If you want to fill empty cells with the value in the cell above, you must select the “Fill cells downwards” option. If you want to copy the value from the cells below, there is an option “Fill cells upwards”.
7. Actions are applied by clicking on the “Fill” button.

Done.

We see that all methods are very simple and require literally a few minutes to implement this task.

In principle, if you follow this logic, you can perform similar actions in spreadsheets of older versions. With the only exception that there access to these functions will be slightly different.