In the Find and Replace dialog box that opens up, enter – (dash) in the ‘Find what’ field.One of the benefits of using Find and Replace it works when you have blank cells that you want to fill, and you can also use it to find and select cells that have specific values.įor example, suppose you have a dataset as shown below where instead of the blank cells, we have dashes instead. Now, you can use the formula to get the value from the above cell and fill in the blank cells in Excel. This will select all these blank cellsīy the end of these steps, you will have all the empty cells selected. Hold the Control key and press the A key (or Command + A if you’re using Mac).This will find all the cells that are blank and you will also see a list of these cells addresses. In the Find and Replace dialog box that opens up, click on the ‘Find All’ button.Hold the Control key and press the F key (or Command + F if you’re using Mac).Let me show you how this would work taking an example of blank cells only.īelow are the steps to select all the blank cells using Find and Replace: With this method, you can select blank cells or you can select cells that have any specific text/value in it. This method works similar to Go To Special, but with an added advantage of being able to select cells based on the value. In such a case, you can also use the Find and Replace method. The above (Go To Special) method works well when you only have the blank cells that you want to fill-down.īut what if you get a dataset where the cells are not really blank (but may have a dash in it or some text such as NA). Fill Cells with Value Above Using ‘Find and Replace’ + Formula In case there are space characters, the ‘Go-To’ feature will not consider these as blank cells and these wouldn’t be selected. Note that for this method to work, the cells need to be completely empty. Once you have all these blank cells filled, remember to convert the formulas to values. The above steps would apply the same formula (which is simply to refer to the cell above) to all the selected blank cells. Hold the Control key and press the enter key (Command + Enter if you’re using a Mac).This will select the cell right above the active cell by default, this will enter the equal to sign in the active cell only Now that you have all the blank cells selected, the next step is to fill all these blank cells from the value above.įollow the following steps to use a formula to copy the value from the cell above:
The above steps would select all the blank cells in this dataset.
EXCEL 2016 FOR MAC FILL COLUMN HOW TO
How to Fill Blank Cells with Value above in Excel Fill Cells with Value Above Using ‘Find and Replace’ + Formula.Fill Cells with Value Above Using ‘Go To Special’ + Formula.How to Fill Blank Cells with Value above in Excel.
EXCEL 2016 FOR MAC FILL COLUMN FULL
Directly below the list, add the full range again – for example 1-10.We’re going to do this by adding the full range of numbers again, sorting and removing duplicates – end result = a complete range of numbers. Instead of manually going through to find the missing numbers and filling them in (which would be horrible with a larger data set). The following process shows how to fill missing numbers in a range.įor example, if we had an excel table where the numbers 3, 4, and 6 were missing.
0 kommentar(er)
