One of the coolest things about spreadsheets is the ability for it to recognize patterns and apply it over a large range. If you write a formula in a spreadsheet and then copy the formula to other cells, the spreadsheet recognizes the pattern and automatically adjusts the formula.
“Fill down” refers to copying the pattern in the spreadsheet down a particular range in a column. For example if I type the number 1 in a cell and the number 2 in the cell below it and then highlight the two cells to signal the pattern, I can “fill down” to continue the pattern.
After highlighting the pattern look for the square in the bottom right-hand corner of the highlighted range. This is the “fill down” square. Click on the square and hold the click as you drag the pattern down the column. Notice the pattern is extended. You started with 1 and 2. After filling down you now have 3, 4, 5, etc…
This works with other patterns too. Type 1 in one cell and 1.5 in the cell below it. Highlight the two cells to tell the spreadsheet that the pattern is to increase by 0.5. Click and drag the square in the bottom right hand corner to continue the pattern.
Another pattern you might want to use is skipping rows. When highlighting your pattern, highlight a blank row. Click on the fill down square and pull down. The pattern will continue with a blank row between the values.
Make a Calendar
Having a list of numbers quickly is very handy. I use fill down constantly just to get a list of numbers from 1 to 100 in seconds. However, there are so many applications for using fill down. One is to create a calendar. You can not only fill down, you can fill to the right. Type Monday in a cell and Tuesday in the cell to the right of it. Highlight the two cells. Find the fill down square. Drag to the right instead of down to continue the days of the week automatically. In the row below type a 1 where the first day of the month starts. Type a 2 in the cell to the right of it. Highlight the two cells and fill to the right to continue numbering the week.
To continue my calendar I will want to skip a row. This gives me space to type in what I am going to do on those days. In the row below my blank row I want the number to be 7 more than previous weeks number. That formula is =B4+7. A formula always starts with an equals sign. I am telling the spreadsheet that the value in cell B6 is equal to the value in cell A4 plus an additional 7. The formula establishes the pattern, I do not need 2 cells to do this. After pressing enter click back on the cell with the formula (in this case B6). Click on the fill down square and drag to the right. The formula will automatically adjust.
Notice in the screenshot below that in cell B6 I had typed the formula =B4+7. When I clicked on the fill down square and dragged to the right the subsequent formulas adjusted by a column. The next cell has the formula =C4+7 and the next one is =D4+7, etc… The spreadsheet read the pattern as being to add seven to the cell 3 rows up.
Finish the calendar by highlighting the range of cells with the pattern. Also highlight a blank row to give you cells you can type in. Click on the fill down square and pull down. This will finish out the dates on the calendar.
This also works with dates. Type the date into one of the cells. To the right of that cell type the next date. Highlight the two dates. Click on the fill down square and drag to the right to finish out the dates for the week. Skip a row. In the next row, write a formula to add 7 to the previous date. Fill this formula to the right. Highlight the row with the formulas, as well as the blank row below it. Click on the fill down square and pull down to fill down the dates for the calendar.
Let’s pretend you have a spreadsheet with 500 rows. If you write a formula and need the pattern applied to each of the 500 rows you will be dragging for a long time. A shortcut for doing fill down is to double click on the fill down square. This will automatically copy the formula down the column for the entire range. This only works if the columns are adjacent. If I type 1 in one cell and 2 in the cell below it. Highlight the pattern and double click on the fill down square nothing will happen if the columns to the left and right are blank. If the column to the left has 500 rows of data in it and I double click on the fill down square I will instantly have a list from 1 to 500 in the column.
1 thought on “Google Sheets: Fill Down”
You must log in to post a comment.