[Fixed!] AutoFill Formula Is Not Working in Excel Table (3 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.


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

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


Similar Readings


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.


Further Readings

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo