Sometimes you will end up with a worksheet that will look something like the one shown in the following Figure.
Filling blank cells with data is common. In our example, an entry in column A applies to several rows of data. This type of data arrangement is easy to understand. For example, you can find easily Jane’s Jan, Feb and March months’ sales. But if you sort this type of list, the blank cells will mess things up, and you will not be able to tell who sold what. See this figure sorted according to Amount.
We shall explain here x ways to solve this problem.
If your data is small, you can enter the missing cell values manually or by using this commands: Home ➪ Editing ➪ Fill ➪ Down (or the Ctrl+D shortcut).
For example, select cell A6 and choose Home ➪ Editing ➪ Fill ➪ Down or press CTRL+ D, A6 will show “Jane”. Then select cell A7 and press CTRL+ D or choose Home ➪ Editing ➪ Fill ➪ Down. Cell A7 will be filled with “Jane”. Fill the rest blank cells in this way.
If your data is large, it is not possible to fill blanks manually. Here’s a better way:
Step 1: Select the range that has the blank cells (A5:A16, in our example).
Step 2: Choose Home ➪ Editing ➪ Find & Select ➪ Go to Special. The “Go to Special” dialog box will appear.
Step 3: Select the “Blanks” option and click OK. This action selects the blank cells in the cell range A5:A16.
Step 4: In the formula bar, type an equal sign (=) and then the cell address of the first cell with data in the column (=A5, in our example), and press Ctrl+Enter. You will find the following result.
Step 5: If you select the recent filled blank cells, they will show “Formula” in the formula bar rather than data. But we want data in those cells. See the following figure.
Step 6: Reselect the original range and press CTRL+ C to copy the selection.
Step 7: Choose Home ➪ Clipboard ➪ Paste ➪ Paste Values to convert the formulas to values.
After you complete these steps, the gaps are filled in with the correct information.
Happy Excelling 🙂
- How to Merge Multiple Cells in Excel at Once
- Selecting Non-Adjacent or Non-Contiguous Cells in Excel (5 Simple Methods)
- How to Make All Cells the Same Size in Excel
- How to shift cells in Excel
- How to Select Multiple Cells in Excel (7 Easy Ways)
- How to Shift Cells Down in Excel (5 Easy Ways)
- Add/Remove Gridlines in Excel (5 Simple Ways)
- What is an Active Cell in Excel?
- What is cell in Excel definition