How do I autofill formulas in Excel?

By Enrique

ExcelExcel has been a through line of my academic and professional career. It is a skill that, once mastered, helped me in many spheres of my life. I've had to help a lot of people with varying levels of Excel proficiency. In this post, we’ll talk about how to autofill formulas in Excel. If you’d like to learn about additional strategies in Excel, you should read other posts in this 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 wherein 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!

Method 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.


Method 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.

Enrique graduated with a BS in Finance and Statistics from New York University's Stern School of Business and earned his MBA from Columbia Business School, graduating with Dean's Honors with Distinction. Enrique has been running a data analytics consulting startup out of the Columbia Startup Lab Incubator since he completed his MBA. 

Enrique has years of experience tutoring MBA students in various subjects including accounting, statistics, business, and Excel. He is a CFA Charterholder, so he is comfortable helping with anything finance-related as well. Witnessing his students achieve academic and professional success makes Enrique's role as a tutor extremely fulfilling.

Work with Enrique!

Curious to learn more about business tutoring? Read some of our other blog posts below!

Solving tough algebra problems with Excel’s Goal Seek feature

4 Excel Quick Tips to Navigate Data Faster

Business School Admissions: You Got In! Now What?



academics study skills MCAT medical school admissions SAT expository writing English college admissions GRE MD/PhD admissions GMAT LSAT chemistry math strategy writing physics ACT biology language learning graduate admissions law school admissions test anxiety MBA admissions homework help creative writing AP exams MD interview prep summer activities history academic advice philosophy study schedules career advice premed personal statements secondary applications ESL PSAT economics grammar law organic chemistry statistics & probability admissions coaching computer science psychology SSAT covid-19 legal studies 1L CARS logic games USMLE calculus dental admissions parents reading comprehension Latin Spanish engineering research DAT excel political science verbal reasoning French Linguistics Tutoring Approaches chinese DO MBA coursework Social Advocacy academic integrity case coaching classics diversity statement genetics kinematics medical school skills ISEE MD/PhD programs algebra athletics business business skills careers geometry mental health social sciences trigonometry work and activities 2L 3L Anki EMT English literature FlexMed Fourier Series Greek IB exams Italian PhD admissions STEM Sentence Correction Zoom amino acids analysis essay architecture art history artificial intelligence astrophysics biochemistry capital markets cell biology central limit theorem chemical engineering chromatography climate change clinical experience constitutional law curriculum data science dental school distance learning enrichment european history finance first generation student fun facts functions gap year harmonics health policy history of medicine history of science information sessions institutional actions integrated reasoning intern international students investing investment banking mathematics mba meiosis mentorship mitosis music music theory neurology phrase structure rules plagiarism poetry presentations pseudocode quantitative reasoning school selection sociology software software engineering teaching tech industry transfer typology units virtual interviews writing circles