Mursalin Ibne Salehin
In this article, we will provide three solutions to the ‘autofill formula is not working in excel table’ problem. Excel tables benefit us greatly when we need to apply a formula to a certain column. You just need to type the formula and hit Enter to autofill the whole column in an excel table. But sometimes, this doesn’t work. We will demonstrate three solutions that you can use when the excel table autofill formula is not working.
Download Practice Book
Download the practice book here.
3 Solutions to AutoFill Formula Not Working in Excel Table
To explain the solutions, we will use a dataset that contains information about sales of two months of some sellers. We will add up the total sales using the SUM Function.
1. Enable Excel Table AutoFill Formula from the Proofing Section While Not Working
In this first solution, we will check the proof section. But we need to first demonstrate the problem. Suppose, we have inserted a formula to calculate the total sales in Cell E5.
=SUM([@[First Month]],[@[Second Month]])
Now, if you hit Enter, you will see results like below.
But sometimes, it doesn’t work. You can see this in the picture below. We have inserted the formula and hit Enter. But the formula did not fill down automatically.
Let’s follow the steps below to get out of this problem.
STEPS:
- Firstly, go to the File tab in the ribbon.
- Secondly, select Options from there. The Excel Options window will open.
- Thirdly, select Proofing.
- After that, select AutoCorrect Options. It will open the AutoCorrect window.
- Now, select AutoFormat As You Type and check the ‘Fill formulas in tables to create calculated columns’.
- Then, click OK to proceed.
- Next, Re-Enter the formula in Cell E5.
=SUM([@[First Month]],[@[Second Month]])
- Finally, press Enter to see results like below.
Read more: Fix: Excel Autofill Not Working
2. Use Smart Tag When AutoFill Formula Is Not Working in Excel Table
You can also use the smart tag when the autofill formula in the excel table is not working. This is a quick and easy solution to this problem.
Let’s pay attention to the steps below.
STEPS:
- In the first place, select Cell E5.
- Now, type the formula:
=SUM([@[First Month]],[@[Second Month]])
- Then, hit Enter.
- You will have a small rectangular box below Cell E5. It is a smart tag.
- After that, click on the smart tag. A drop-down menu will occur.
- Select ‘Overwrite all cells in this column with this formula’ from the drop-down menu.
- Instantly, your table will autofill the formula in the rest of the cells.
Similar Readings
- How to Apply AutoFill Shortcut in Excel (7 Methods)
- Perform Predictive Autofill in Excel (5 Methods)
- How to Use VBA AutoFill in Excel (11 Examples)
- AutoFill Not Incrementing in Excel? (3 Solutions)
3. Apply Fill Handle to AutoFill Formula in an Excel Table
The Fill Handle is a common tool when we need to autofill blank cells very quickly. We often use this in our dataset. But, we can also use this to autofill formulas in an excel table.
Let’s observe the steps below to learn this solution.
STEPS:
- In the beginning, select Cell E5 and type the formula:
=SUM([@[First Month]],[@[Second Month]])
- After that, press Enter.
- Now, put the cursor on the small green rectangular box in the below-left corner of Cell E5. The cursor will change into a plus sign.
- Now, double-click on the Fill Handle to see the results.
- Alternatively, you can use the keyboard shortcut to fill it down automatically.
- To do so, select the cells where you want to apply the formula.
- Press Ctrl + D to see results like below.
Read more: How to Use Autofill Formula in Excel
Table AutoFill Formula Not Working: Why Does Occur?
There are many reasons behind this problem. By default, the autofill table formula is active.
- Sometimes, when we change the formula in a cell in the column, the autofill table formula feature is disabled.
- Again, if you insert a formula and hit Enter, then undo it. Then this problem might also occur.
- It can also occur if you select ‘Undo Calculated Column’ from the smart tag icon.
Conclusion
We demonstrated 3 easy ways to solve the ‘AutoFill Formula Not Working in Excel Table’ problem. I hope these solutions will help you to perform your tasks perfectly. Furthermore, we have also added the practice book at the beginning of the article. Download it to practice more. Last of all, if you have any queries or suggestions, feel free to ask in the comment section.
