Fill all blank cells with same data

[Zakky]

hi all. I get a system report with several columns of data. the first column has data - 6130 widgets - then the cells below are all blank until the next code - 6131 widgets x - then the cells below are all blank until the next code. This is really frustrating because I need the same description in all the cells below after the first cell. Is there a way in Excel (not VBA) to populate the blank cells under the relevant code. this is a big hassle for me as the report only shows the code on the first line and nothing below until the new code. Many thanks in advance.

 

[Joe4]

Check out the "Fill Cells with Value Above Using 'Go To Special' + Formula" section here: How to Fill Blank Cells with Value above in Excel (3 Easy Ways) - Spreadsheet Planet

 

[Zakky]

thank you, Joe4. PERFECT!!!

 

[Alex Blakenburg]

I normally find using a helper column or input & output section works in a more automated fashion.
If you are just putting a pivot or filter over the top, its usually good enough.

Power Query would be another non-vba option

Book1
	A	B	C	D
1	Widgets	Bin	Helper Widgets
2	6130	A1	6130
3		A20	6130
4		A30	6130
5	6131	B5	6131
6		ZB10	6131
7
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=IF(A2<>"",A2,C1)

 

[Joe4]

thank you, Joe4. PERFECT!!!

You are welcome.
It is a cool little trick!

 

[Zakky]

thank you, Alex. I have been a using your solution (helper column) but got fed up of having to type the formula every single time.

 

[Alex Blakenburg]

Haha, I appreciate the update.
In the link Joe provided there were 3 options the 3rd being VBA. I hope you went with the VBA option. I often use a fill down macro that I have stored in my Personal Macro workbook for one off fill down scenarios.

got fed up of having to type the formula every single time.

Just from an spreadsheet technique perspective. Your comment about having to type in the formula each time indicates you are not using the helper column as intended.
Most spreadsheets need to have the data refreshed at regular intervals and this generally means you will get the same number of columns but a different number of rows each time.

You therefore want to set up all calculation columns (including helper columns) 1 column to the right of the number of columns required for the data you are receiving.
eg. source data runs from A:F, then your first calculation column should start at G.
Using those column references as an example ......

• The next time you get new data you simply delete the data in A:F (clear content "do not" delete rows or columns).
• Copy in the new data
• Then just make sure your calculation columns cover all the rows used in Columns A:F. If you use Excel Tables you won't even have to do this part since the Table will copy the formulas down.

For your scenario of filling down information, the above is actually a more automated way than using either the Goto blank cells or Find & Replace methods suggested in the Link.
It is also a much cleaner and efficient spreadsheet design if you don't want to use VBA or Power Query to add information and to rearange data, and you are currently moving columns manually and reentering formulas each time you refresh the data.

 

[Zakky]

Alex, thank you for the tip. you are right about me not using the helper column as intended.

 

[Alex Blakenburg]

I hope it is something you can use. I have seen so many people manipulate columns and recreate formulas which is a serious timewaster and error prone.

Even if you want the columns to appear in a certain order then consider the area to the right which holds formulas to be the output area and refer back to the input array eg =C2 etc to put the input columns in the right order in the output section. Most of the time the output is a pivot table so the order won't matter anyway.
By keeping the calculation columns in a block you only ever have to copy or fill down those columns to the last row of the input data. (and again if you use a table this is automatic)