Fill all blank cells with same data

Zakky

Board Regular
Joined
Mar 26, 2016
Messages
99
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Zakky

Board Regular
Joined
Mar 26, 2016
Messages
99
Office Version
  1. 365
Platform
  1. Windows
thank you, Joe4. PERFECT!!!
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,694
Office Version
  1. 365
Platform
  1. Windows
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
ABCD
1WidgetsBinHelper Widgets
26130A16130
3A206130
4A306130
56131B56131
6ZB106131
7
Sheet1
Cell Formulas
RangeFormula
C2:C6C2=IF(A2<>"",A2,C1)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,991
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

thank you, Joe4. PERFECT!!!
You are welcome.
It is a cool little trick!
 

Zakky

Board Regular
Joined
Mar 26, 2016
Messages
99
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 23, 2021
Messages
2,694
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Mar 26, 2016
Messages
99
Office Version
  1. 365
Platform
  1. Windows
Alex, thank you for the tip. you are right about me not using the helper column as intended.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,694
Office Version
  1. 365
Platform
  1. Windows
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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,572
Messages
5,770,923
Members
425,652
Latest member
Pemby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top