No Excel user can think about using an Excel spreadsheet without using Excel formulae. So, if the formulae in the spreadsheet doesn’t work in Microsoft Excel, it can create unnecessary harrasment for the users. Without the proper functionality of formulae in Excel, all the calculations needs to be done manually. If you are dealing with a big workbook with lots of calculations in it, there isn’t any workaround for formulae. As this directly affects the your productivity, you should fix the problem, first.
Fix 1 – Set the Excel to auto-calculate
Excel automatically calculates the formulae from the given data of the cells. But, sometimes, it may be set not to calculate automatically.
Step 1 – Open the workbook in Microsoft Excel.
Step 2 – Next, go to the “Formulas” tab.
Step 3 – In the “Calculation Options” and choose the “Automatic” mode.
Once you have set the calculation options to automatic mode, Excel will calculate all the data in the cells using the provided formulae.
Now, if you want to automate the calculation in all the workbooks in MS Excel –
Step 1 – Launch the MS Excel application.
Step 2 – Next, go to the “File” tab. There, click “Options“.
Step 3 – When this loads the Excel Options window, click the “Formulas” section.
Step 4 – On the opposite part, you shall find the ‘Workbook Calculation’ tab. Choose the “Automatic” mode in there.
Step 5 – Finally, click “OK” and this change will be saved in MS Excel.
All the spreadsheets you create, edit, or modify, the calculations will be done automatically using the formulae.
Fix 2 – Check the cell format for formulae
All the cells that are formatted as texts, can’t be used to calculate using Excel formulae.
Step 1 – You have to revert this change. Just select the affected cell in the workbook.
Step 2 – Next, go to the “Home” tab. There, in the ‘Numbers’ section, toggle the “Numbers” from the drop-down list.
After reverting this change for the cell, you can use the formulae to calculate the cell data.
Check if this saves the change in MS Excel.
Fix 3 – Validate the formula
Are you using the wrong formula in the workbook? Before going ahead with other solutions, make sure to verify the formula. in Microsoft Excel. Check individual cells containing the formula in Excel and test the range of the formula.
Additionally, if you are not sure about a formula, you can just google it as well.
Fix 4 – Hide Forumale
There is an option in MS Excel that lets you show/hide the formulae in the spreadsheet. In this operation, Excel actually shows you the formula you are using, instead of showing the actual result.
Step 1 – To fix this, select the cell that is showing the formula.
Step 2 – Next, go to the “Formula” tab.
Step 3 – Toggle the “Show Formulas” option and disable it.
Alternatively, you can use the Ctrl+’ shortcut key to tweak the view of the formula in the MS Excel worksheet.
These fixes should help you solve the Excel formulae issue.