Excel - how to automatically fill blank fields

**bankcott** · 05-15-2013, 02:50 PM

Hi - (Please see attached document 1). I use a system which produces excel spreadsheets similar to this example (although much larger than this!). This makes it extremely difficult to carry out sorts and to tidy up the spreadsheet - generally I have to do this manually. I am hoping that there is a way that I can easily add in the data as per document 2 in red. Any suggestions?

Much appreciate any advice, thank you.

Ps I am a total excel beginner!

**Ron Coderre** · 05-15-2013, 03:47 PM

Using your posted workbook: Book1.xlsx

• Put headings in Row_2
A2: First
B2: Last
C2: Category
D2: Amount
E2: Ref

• Select A2:E16
• Data.Filter
• Select the Amount dropdown filter and UNcheck blanks
• Home.Find&Select.GoTo_Special...Check: Blanks
• Type: =
...Press the UP arrow one time
...Hold down CTRL while you press ENTER
• Select the Amount dropdown filter and check: Select All

You can replace the formulas with hard coded values by selecting the range...Copy...Paste_Special.Values

Is that something you can work with?

**bankcott** · 05-15-2013, 04:06 PM

Fantastic, you are wonderful! You have no idea how many hours I have wasted doing this manually! I actually found that I can miss out the applying the filter and it still allows me to enter the data.

Thank you so much - top service.