excelpowerquery

Is there way to autofill in excel where every blank row in a column is filled with the value of the closest non-empty cell above the blank row?


A simplified example of what I am looking for is a quick way (either a tool or a keystroke) to fill a table where every 5 rows of the first column has a different operator name. The fill would place the first name into the first 5 rows of the table, the second name into rows 6 - 10, and the third name into rows 11 - 15. The images below are a visual representation of before and after the fill.

I am aware of different ways to fill the rows one operator at a time in this example, but is there a way to fill all three in one go? This would be very helpful in larger scale situations. Thanks!

Excel table before filling column A Excel table after filling column A


Solution

  • Open PowerQuery (Go to Data on the ribbon, Get Data -> Launch Power Query Editor ...) and use Fill Down ...

    Fill Down

    Result

    Result