While working with Microsoft Excel after applying a formula we drag it down to fill the cells with the same formula. But sometimes the fill handle tool copies the same value to other cells which is not our desired result. This happens due to the calculation is changed into the manual format. Excel has some built-in features to auto-calculate formulas. In this article, I am going to share with you how to make Excel auto-calculate formulas.
In the following article, I am describing 4 easy and simple methods to make auto-calculate formulas in a workbook.
Suppose we have a dataset of some Products, Sales of January, and Sales in February.
Now let’s calculate the Total Sales for January and February.
After that, if we drag down to fill the cells you will see the same value in those filled cells. This happened due to “Auto Calculate Formulas” not being enabled.
Sometimes you might find that when we change a cell value excel does not change the value in the final formula output. Now we will make Excelsheets auto-calculate formulas so that this thing won’t happen.
1. Using Excel Options to Make Excel Auto Calculate Formulas
The simplest way to achieve auto-calculate formulas in a cell is to use options of excel options.
Steps:
- Go to the “File” option.
- Choose “Options” from the left pane.
- In the new window select “Formulas” and then click the “Automatic”.
- Press OK to continue.
- Now if we drag down the cells you will see the column have auto calculate formulas. This easy we can accomplish our target.
2. Applying Formula Feature for Auto Calculating Formulas in Excel
You can use the formulas options from the Home tab to auto-calculate formulas.
Steps:
- Click the “Formulas” tab.
- Select “Automatic” from the “Calculation Options”.
- Finally, if we pull down the “fill handle” you will get the desired output because of enabling the “Automatic” options for formulas.
Read More: [Fixed:] Excel Spreadsheet Formulas Not Updating Automatically
3. Use Calculate Now Option to Auto Calculate Formulas
In those previous methods, we calculated the values by changing the options. In this method, I will show you an easy process so that you don’t have to change any options. Suppose in your workbook you have transferred the calculation options to manual. Using the “Calculate Now” simply change the mode to auto in Excel.
Steps:
- Select the cells where the final output stands with formulas. Here I have selected cell (F5:F12).
- Click the “Calculate Now” icon from the “Formulas” option to get an immediate result.
- Here we have successfully got our desired output in the same column.
4. Applying Keyboard Shortcut to Make Excel Auto Calculate Formulas
Everyone likes shortcuts to solve problems at a glance. Using a keyboard shortcut you can create auto calculate formulas.
Step 1:
- Here I have chosen cell (F5:F12).
- Hit the F9 button from the keyboard.
- Thus we get out precious output by changing the calculate formulas to auto mode.
In some cases, you might need to change the cell value. But you won’t find the output in the final output column with the changed value. In those cases follow the steps below to solve-
Step 2:
- Press F9 to refresh the dataset.
- This way by refreshing the dataset we can get out the desired output.
Things to Remember
- Pressing F9 from the keyboard refreshed the cell. So whenever you change values in a cell don’t forget to refresh by pressing F9.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, I have tried to cover all the simple methods to make auto calculate formulas in a workbook. Take a tour of the practice workbook and download the file to practice by yourself. Hope you find it useful. Please inform us in the comment section about your experience. Stay tuned and keep learning.
Related Articles
<<Go Back to Excel Auto Calculate | How to Calculate in Excel | Learn Excel
