Bhubon Costa
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Effective Ways to Fix Formulas Not Copying Down in Excel Table
In the following sections, we will describe the possible reasons why the formulas don’t copy down in a table and provide 3 ways the possible solution 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 applying the same formula.
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]
- Therefore, seeing that the formula is not applied to all cells, means the formula is not copying down to the cells.
Step 2: Select the File Tab
- Firstly, click on the File.
Step 3: Selection of the Options Menu
- Firstly, click on the Home.
- Then, click on the More.
- Finally, click on the Options.
Step 4: Apply the Proofing Option
- Select the Proofing.
- Then, click on the AutoCorrect.
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.
Step 6: Write the Formula
- Now, type the following formula.
=[@[Price/Unit]]*[@Quantity]
Step 7: Get the Final Result
- Press Enter and see that all the cells are filled with the same formula with just one press.
Read More: Use Formula in an Excel Table Effectively (With 4 Examples)
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.
Step 2: Select a Command
- Choose the Automatic command.
Step 3: Insert a Formula
- To enter a formula, write down the following formula.
=[@[Price/Unit]]*[@Quantity]
- As a result, you will get all the results at once as shown in the image below.
Read More: Excel VBA: Insert Data into Table (4 Examples)
Similar Readings
- How to Lookup a Table and Return Values in Excel (3 Simple Ways)
- [Fix] The Pivot Table Name Is Not Valid (7 Causes with Solutions)
- How to Get a Count in Excel Pivot Table Calculated Field
- Pivot Table is Not Picking up Data in Excel (5 Reasons)
- Pivot Table Custom Grouping: With 3 Criteria
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]
- Consequently, the AutoFill Handle Tool didn’t appear and your formula is not copied through all the cells.
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.
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.
Read More: Excel Table Formatting: Problems and Fixes You Need to Know
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.
The Exceldemy staff will get back to you as soon as possible.
Stay with us and continue to learn.
Related Articles
- How to Use Slicers to Filter a Table in Excel 2013
- Navigating Excel Table: Selecting Parts of a Table and Moving a Table
- What is the Difference between a Table and a Range in Excel?
- How to Make Excel Tables Look Good (8 Effective Tips)
- Does TABLE Function Exist in Excel?
- How to Convert Table to List in Excel (3 Quick Ways)
