[Fix]: Formulas Not Copying Down in Excel Table (3 Solutions)

Get FREE Advanced Excel Exercises with Solutions!

To save the consumption of time, you may need to apply the same formula in a number of columns in an Excel table. But sometimes it won’t work for several reasons. Here, in this tutorial, we will show you how to fix formulas not copying them down in Excel.


Fix Formulas Not Copying Down in Excel Table: 3 Effective Ways

In the following sections, we will describe the possible reasons why the formulas don’t copy down in a table and provide 3 possible solutions to these. We will use the Proofing option, Advanced option, and the Formulas tab to accomplish the task. In the image below, we have provided a sample data set where we will calculate the total sales by applying the same formula.

Sample Data

1. Use Proofing Option to Fix Formulas Not Copying Down in Excel Table

To use the Proofing option, follow the outlined steps below.

Step 1: Enter Formula

  • To calculate the total Sales, type the following formula in the first cell of the column.
=[@[Price/Unit]]*[@Quantity]

Effective Ways to Fix Formulas Not Copying Down in Excel Table

  • Therefore, seeing that the formula is not applied to all cells means the formula is not copying down to the cells.

Effective Ways to Fix Formulas Not Copying Down in Excel Table

Step 2: Select the File Tab

  • Firstly, click on the File tab.

Effective Ways to Fix Formulas Not Copying Down in Excel Table

Step 3: Selection of the Options Menu

  • Click on the Home<< More << Options.

Effective Ways to Fix Formulas Not Copying Down in Excel Table

Step 4: Apply the Proofing Option

  • Select Proofing.
  • Then, click on AutoCorrect Options.

Effective Ways to Fix Formulas Not Copying Down in Excel Table

Step 5: Check the Required Option

  • Select the AutoFormat As You Type.
  • Check the box entitled Fill formulas in tables to create calculated columns.
  • Finally, press Enter or click on OK.

Effective Ways to Fix Formulas Not Copying Down in Excel Table

Step 6: Write the Formula

  • Now, type the following formula.
=[@[Price/Unit]]*[@Quantity]

Effective Ways to Fix Formulas Not Copying Down in Excel Table

Step 7: Get the Final Result

  • Press Enter and see that all the cells are filled with the same formula with just one press.

Effective Ways to Fix Formulas Not Copying Down in Excel Table

Read More: How to Rename a Table in Excel


2. Enable Automatic Calculation Option to Disable Formulas Not Copying Down in Excel Table

You can copy down the formula by using the Automatic Calculation option. Simply follow the instructions below.

Step 1: Selection of the Calculation Option

  • Firstly, click on the Formulas.
  • Select the Calculation.

Effective Ways to Fix Formulas Not Copying Down in Excel Table

Step 2: Select a Command

  • Choose the Automatic command.

Effective Ways to Fix Formulas Not Copying Down in Excel Table

Step 3: Insert a Formula

  • To enter a formula, write down the following formula.
=[@[Price/Unit]]*[@Quantity]

Sample Data

  • As a result, you will get all the results at once, as shown in the image below.

Sample Data

Read More: How to Extend Table in Excel


3. Check Fill Handle Option to Turn Off Formulas Not Copying Down in Excel Table

Sometimes, you cannot copy down the formulas if your Fill Handle option is disabled, and you don’t have any idea about that. To enable the Fill Handle option, follow the procedures below.

Step 1: Type a Formula

  • To calculate the Sales, type the following formula.
=[@[Price/Unit]]*[@Quantity]

Sample Data

  • Consequently, the AutoFill Handle Tool didn’t appear, and your formula was not copied through all the cells.

Sample Data

Step 2: Enable Fill Handle Option

  • Go to File.
  • Then, click on the Options.
  • Click on the Advanced.
  • Check the box Enable fill handle and cell drag and drop.

Sample Data

Step 3: Get the Final Result

  • Finally, press Enter, and your AutoFill tool will appear again.
  • Use the AutoFill tool to copy down the formula in the required cells.

Sample Data

Read More: How to Make an Excel Table Expand Automatically


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

Finally, I hope you now understand how to fix formulas, not copy them down in Excel. All of these strategies should be carried out when your data is being educated and practiced. Examine the practice book and apply what you’ve learned. We are driven to continue offering programs like this because of your generous support.

If you have any questions, please do not hesitate to contact us. Please share your thoughts in the comments section below.


Related Articles


<< Go Back to Excel Table | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Bhubon Costa
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo