How do I autofill formulas in Excel?

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.

fill_formula_problem

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.

fill_formula_solution_1

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

fill_formula_solution_2

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?

 

Comments

topicTopics
academics study skills MCAT medical school admissions SAT expository writing college admissions English MD/PhD admissions strategy writing LSAT GMAT GRE physics chemistry math biology graduate admissions academic advice ACT interview prep law school admissions test anxiety language learning premed MBA admissions career advice personal statements homework help AP exams creative writing MD study schedules test prep computer science Common Application summer activities history mathematics philosophy organic chemistry secondary applications economics supplements research 1L PSAT admissions coaching grammar law psychology statistics & probability legal studies ESL CARS SSAT covid-19 dental admissions logic games reading comprehension engineering USMLE calculus PhD admissions Spanish mentorship parents Latin biochemistry case coaching verbal reasoning DAT English literature STEM excel medical school political science skills AMCAS French Linguistics MBA coursework Tutoring Approaches academic integrity chinese letters of recommendation Anki DO Social Advocacy admissions advice algebra art history artificial intelligence astrophysics business cell biology classics diversity statement gap year genetics geometry kinematics linear algebra mechanical engineering mental health presentations quantitative reasoning study abroad technical interviews time management work and activities 2L DMD IB exams ISEE MD/PhD programs Sentence Correction adjusting to college algorithms amino acids analysis essay athletics business skills careers cold emails data science dental school finance first generation student functions graphing information sessions international students internships logic networking poetry resume revising science social sciences software engineering tech industry trigonometry writer's block 3L AAMC Academic Interest EMT FlexMed Fourier Series Greek Health Professional Shortage Area Italian Lagrange multipliers London MD vs PhD MMI Montessori National Health Service Corps Pythagorean Theorem Python Shakespeare Step 2 TMDSAS Taylor Series Truss Analysis Zoom acids and bases active learning architecture argumentative writing art art and design schools art portfolios bacteriology bibliographies biomedicine brain teaser campus visits cantonese capacitors capital markets central limit theorem centrifugal force chemical engineering chess chromatography class participation climate change clinical experience community service constitutional law consulting cover letters curriculum dementia demonstrated interest dimensional analysis distance learning econometrics electric engineering electricity and magnetism escape velocity evolution executive function freewriting genomics harmonics health policy history of medicine history of science hybrid vehicles hydrophobic effect ideal gas law immunology induction infinite institutional actions integrated reasoning intermolecular forces intern investing investment banking lab reports linear maps mandarin chinese matrices mba medical physics meiosis microeconomics mitosis mnemonics music music theory nervous system neurology neuroscience object-oriented programming office hours operating systems

Related Content