Excel has been this constant presence throughout my academic and professional career, and I've had to help a lot of people with varying levels of Excel proficiency. Through blogging, I want to keep a running list of the most common questions I get asked about Excel, so without further introduction, here is the first of many “How do I…in Excel” series!
How do I take a formula that I just created and fill it to the last row of data?
You might come across a situation when you're working with a large data set and you need to add additional columns to your data set for further analysis. This additional column might be dependent on existing data points in the data set so this additional column might just be cell referencing the existing data points. The below is an example of this situation.
Let's talk about the structure of the data. Each row in this data represents a different week (in this example, there are 12 of them) and each column, starting in Column B and ending in Column H, represents a day of the week. To contextualize this example - let's say that this is a daily expense tracker. So, for example, cell B3 through cell H3 represent the expenses incurred during Week 1.
What we want to do is sum the daily expenses for each week so that we get a sense of what the weekly expenses are. To do this using an Excel function, use the =SUM formula. As we see in the example above, we type =SUM in cell I3 and then highlight the range of cells that we want summed (B3 to H3). The output of this function represents the sum of the daily expenditures in Week 1 (i.e. our weekly expenditure).
How do I apply the formula to each week without having to rewrite the =SUM formula for each row (i.e. each week in our dataset)? There are two ways to do this!
1. Click into the cell that contains the formula. You'll notice the lower right-hand corner of the cell has a small square - double click on this square. This will auto-fill the =SUM formula all the way to the bottom of your dataset. This method will always work provided that the column you added is directly to the right of another column in your dataset that is complete. In the example below, the formula in the added column auto filled to the very bottom because the column directly to the left of it (i.e. the 'Sunday' column) is filled all the way to the bottom of the dataset.
2. For those that don't want to use your mouse, you can use a combination of navigation shortcuts and keyboard shortcuts to copy the formula to the last row of the dataset. Copy the formula (CTRL + C) > Arrow over to a column that is fully complete > Go to the last row of that column (CTRL + Down Arrow) > Arrow over to the column that you want to fill in > While holding SHIFT + CTRL, press the Up Arrow > Paste the formula (CTRL + V).
Try either method (or both) the next time you run into this situation! Method 2 seems longer because of all the keystrokes, but as you build the muscle memory, it might even be a faster way to fill data than Method 1.