In effect, we're telling Excel to use "the cell above" to fill in all blank cells.
Data analysis excel missing update#
Because the formula contains a relative reference, Excel will update the address at each location. Now, instead of pressing Return, which would enter the formula in the active cell only, press Control + Return.Ĭontrol + Return enters the same formula into all selected cells at once.
Next, enter a formula that sets the active cell to the address of the cell above it. The active cell will be the basis for all formulas we enter. To do that, make sure the active cell in the selection is one of the empty cells. The key is starting with the right reference. You might think you need something fancy but actually, due to the relative nature of references in Excel, the formula we need is dead simple. To do that, use Control-G, then click Special, select Blanks, and click OK.Īt this point, only empty cells are selected, and I just need to add a formula to pull in the missing values. If I put the cursor into the last column, which contains a full set of values, Control-A will do the trick. To slice and dice data with these tools, you'll want a full set of values in each row.īut entering this kind of missing information manually in a large set of data is tedious work, even using shortcuts.įortunately, as long as the data is well organized, you can use a simple formula to add missing values. This isn't a problem for humans because we can see and understand the overall structure of the data.īut it won't work if you want to analyze the data using filters, or pivot tables, or even conditional formatting. Sometimes you get a set of data that isn't complete because it's been organized like an outline, with main headings and sub-headings appearing just once at the start of a new section. It's a simple technique using a very basic formula, and it's a beautiful example of the power of relative cell references. In this video I'll show you a way to quickly add missing data to a worksheet.
0 kommentar(er)
