[Fixed!] AutoFill Formula Is Not Working in Excel Table (3 Solutions)

Get FREE Advanced Excel Exercises with Solutions!

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.


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

Enable Excel Table AutoFill Formula from the Proofing Section While Not Working

Now, if you hit Enter, you will see results like below.

Enable Excel Table AutoFill Formula from the Proofing Section While Not Working

But sometimes, it doesn’t work. You can see this in the picture below. We have inserted the formula and hit Enter. However, the formula did not fill down automatically.

Enable Excel Table AutoFill Formula from the Proofing Section While Not Working

Let’s follow the steps below to get out of this problem.

STEPS:

  • Firstly, go to the File tab in the ribbon.

Enable Excel Table AutoFill Formula from the Proofing Section While Not Working

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

Enable Excel Table AutoFill Formula from the Proofing Section While Not Working

  • Now, select AutoFormat As You Type and check the ‘Fill formulas in tables to create calculated columns’.
  • Then, click OK to proceed.

Enable Excel Table AutoFill Formula from the Proofing Section While Not Working

  • Next, Re-Enter the formula in Cell E5.
=SUM([@[First Month]],[@[Second Month]])

Enable Excel Table AutoFill Formula from the Proofing Section While Not Working

  • Finally, press Enter to see results like below.

Enable Excel Table AutoFill Formula from the Proofing Section While Not Working

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

Use Smart Tag When AutoFill Formula is Not Working in Excel Table

  • Then, hit Enter.
  • You will have a small rectangular box below Cell E5. It is a smart tag.

Use Smart Tag When AutoFill Formula is Not Working in Excel Table

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

Use Smart Tag When AutoFill Formula is Not Working in Excel Table

  • Instantly, your table will autofill the formula in the rest of the cells.

Use Smart Tag When AutoFill Formula is Not Working in Excel Table

Read More: [Solved:] Excel Double Click AutoFill Not Working


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 AutoFill from List 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.

Download Practice Book

Download the practice book here.


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.


Further Readings


<< Go Back to Excel Autofill not WorkingExcel Autofill | 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.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo