如何批量填充Excel中的空白列值？不常用Excel的用户求助
Hey there! No worries at all—Excel can feel pretty daunting when you don’t mess with it day-to-day, and manually filling thousands of rows is totally out of the question. Let me show you two solid, easy-to-follow methods to fix those blank cells in bulk.
方法1：用「定位条件」快速批量填充
This is the quickest method for most basic cases where you want blank cells to inherit the value from the cell directly above them:
- First, select your entire data range (including the blank cells). A fast way to do this is click the top-left cell of your data, then press
Ctrl + Shift + Endto select all connected data. - Hit
Ctrl + Gto open the Go To dialog box, then click the Special button at the bottom left. - In the Go To Special window, select Blanks and click OK. Now all your empty cells are selected (you’ll see them highlighted).
- Don’t click anywhere else! Type
=and then press the↑(up arrow key)—this tells Excel to reference the cell right above the selected blank. - Finally, press
Ctrl + Enterand boom—all those blank cells will instantly fill with the value from the cell above them.
方法2：用Power Query处理（适合重复操作或复杂数据）
If you think you’ll need to do this kind of cleanup regularly, Power Query is a game-changer—it’s built into Excel and makes repetitive tasks a breeze:
- Select your data range, go to the Data tab, and click From Table/Range (make sure to check "My table has headers" if your data has column titles).
- Once you’re in the Power Query Editor, select the column(s) with blank cells. Go to the Transform tab, find the Fill dropdown, and choose Down.
- Click Close & Load and your cleaned-up data will be imported back into Excel, with all blanks filled in perfectly.
A quick note: If your blanks need to be filled with something other than the cell above (like a specific value), you can adjust these methods slightly. For example, in the first method, after selecting blanks, just type your desired value instead of = and the up arrow, then press Ctrl + Enter.
内容的提问来源于stack exchange，提问作者s_khan92
