How to fill in the blanks in a list of data.

QUICK ANSWER - Read past it for an explanation:-

Select your range of data from the first real value, to the last row that should have a value in it. Now do Edit / Go To / Special / Blanks. Now simply type = (Do not hit enter yet) and then click on the cell immediately above the activecell (The first blank cell) OR just hit the UP Arrow once, and then hit CTRL+ENTER. Your data will have been filled in, but will contain formulas. To get rid of these, simply select the whole range and do Edit / Copy, then Edit / Paste Special / Values.

-----------------------------------

EXPLANATION

CTRL+ENTER is a key combination that can help save you an awful lot of work. One very useful tip using this is for anyone that ever had to use a list of data dumped down from a mainframe that groups the data into logical areas, but only gives you one heading for each of those areas. You then want to use the data in a Pivot table or with SubTotals etc, and cannot do so because you have a lot blank fields. the data in question would look like this:-



But what you really want is to have all those blanks on the left, filled in with the value above it, ie


There are numerous ways of doing this with various formulas, but this can be tedious when all you want is a real quick fix to the data. This is where CTRL+ENTER comes in. CTRL+ENTER when used in conjunction with a group of selected cells (Do not have to be contiguous), will enter the same value/formula that is in the activecell, into every selected cell. If it is a formula, then with an absolute reference it will put the exact same formula in each cell, or with a relative reference it will put a relative formula in each cell.

Assuming your data is as in the table above, with the first entry in your column starting in say A5 with blanks in A6,7,8 etc and then odd values in misc cells as you move down the range, and the last used row being row 1000. Within that range A5:A1000 you have a lot of blanks, and each need to be filled with the value from the next non-blank cell above it.

Select the range A5:A1000 and then do Edit / Go To / Special / Blanks. You will now see that just all the blanks have been selected, within the area you had initially selected. In particular, notice that the activecell is now the first blank within your selection, which in this case will be A6. With all these cells selected, simply type = (Do not hit enter yet) and then click on the cell immediately above the activecell, ie cell A5, and then hit CTRL+ENTER.

Within cell A6 you will now see the formula =A5, and because it is was relative (ie no $ signs around the reference which would lock it down), you will see that in every blank cell that had been selected when you did this, there will now appear a relative reference that equates to =Cell_Above. If you had a blank in cell A51, it will now have =A50 in it. Because it has only done this for the blanks, you will now see that everywhere you had a value initially, below it will be an =That_Cell formula. This will obviously give you the same value, and because every blank cell will refer to the cell above it, you will see that value continue down until it hits the next 'real' value, at which point the cycle breaks and then continues again with the next blank cell.

The last thing you need to do is to 'fix' all those values, so simply copy the range and then do Edit / Paste Special as values and all the formulas will be replaced with hard-wired numbers/values/text etc.

-------------------------------------

USING CTRL+ENTER with Pivot Table data to prevent COUNT being the default action.

Now, earlier in the thread I referred to the use of CTRL+ENTER in a Pivot Table. One of the things that always bugged me was if i was creatinga Pivot table, and then having done so the 'summarize by' calculations always seemed to default to COUNT. This meant that I had to go into the Field settings and change the 'summarize by' back to SUM to get what I wanted for some 90%+ of my data. Turns out that the reason it defaults to COUNT is because there are blank cells in the raw data and therefore it contains non-numeric cells, and it simply assumes that a COUNT is what is required as opposed to a SUM. If there are no blanks then assuming the data is numeric it will default to SUM. Enter CTRL+ENTER!!!!

As before, select the entire range of data (And it may be that you have to do it in a coouple of go's on smaller selections, depending on how many blanks there are), and then do Edit / Go To / Special / Blanks. You will now see that all the blanks within your selection have been selected only. Now simply type 0 and then hit CTRL+ENTER and it will put a 0 in every blank cell. You can now create your pivot table and it will always default to SUM as opposed to COUNT.