MyPage is a personalized page based on your interests.The page is customized to help you to find content that matters you the most.


I'm not curious

Excel formula is not working - Easy fix

Published on 20 July 15
533
0
1

At times, people face some serious issues while working with Excel. One of a major issue is, the function stop calculating the digits, which suddenly stop user's work. This problem usually occurs when there is an issue in the formatting of excel sheet's cells or in the Excel Calculation option setting.

Let's discuss about the calculation issues of Excel and discuss some solution of the same problem:

Possible causes of Excel calculation issue:

1. Cells are formatted as text

Explanation: A text on a cell looks like a formula, but it doesnât work as a calculator.

Cause: This problem occurs when the excel is formatted a text and do not use any formula. This could happen due to the Text format, or usually happen when when data are directly imported from a CSV or Notepad file.

Solution: In order to fix Excel issue, you just need to change the format of the cell(s) to General or to some other format. You need to force Excel to reconsider the content to start formulas working as before. In order to do this follow the following two ways.

Option 1: Select the affected cell (you can do it by pressing a mouse button or F2) and immediately exit again. This is basically for a single formula, in case you are working with multiple formulas, then this method will be time-consuming. For multiple formulas you can use option 2 instead.

Steps:

1. Select the cell that contains the affected formula.

2. Edit menu and choose Clear, and then Formats. This will remove any formatting applied to the cell.

3. Now, press F2. This will put the cell into edit mode.

4. Immediately press Enter. This will force Excel to re-evaluate the cell context.

Option 2: You can use 'Find and Replace' (press Ctrl + F) option. Choose to replace '=' with '='. This will force Excel to refresh the formulas and start calculating as before.

2. The workbook is set to 'Manual Calculation' mode

Explanation: All the formulas are written in a correct manner, but still not delivering the correct results.

Cause: This usually happens when the workbook is set as 'Manual calculation' mode. Manual mode means that the Excel will not automatically update all formulas, whenever there is a need of applying any formula to the workbook, user have to manually force Excel to do so.

Solution: In order to do that, you can either force a manual recalculation, or turn the Excel on 'Manual Calculation' mode.


Option 1: You can force a manual recalculation, by pressing the Calculate in the bottom from the left most corner of Excel, or by simply pressing F9.

Option 2: You can switch back to Automatic calculation by going to Formulas â Calculation options.

Steps to set your workbook as Automatic Calculation mode:

Excel 2010 or Excel 2013:

1. Go to the File tab, and select Options

2. Now, from the pop up list, choose the option Formulas

3. Choose the option Automatic, under the Calculation options, and click OK

Excel 2007:

1. Go to Excel Options by selecting main Excel menu

2. Choose the option Formulas from the pops up list

3. Choose the option Automatic, from the Calculation options, and click OK

Excel 2003:

1. Go to top of the Excel window and choose Tools from the drop down menu, and then select Options.

2. Tap the Calculation tab

3. Choose Automatic, under the Calculation, and press OK

These are the main cause of the Excel formula issue and you follow the above mentioned solution to resolve them. In case your problem is not is not resolved by by either method, you can opt a professional tool like this, which can help you to fix all the Excel related problems.



At times, people face some serious issues while working with Excel. One of a major issue is, the function stop calculating the digits, which suddenly stop user's work. This problem usually occurs when there is an issue in the formatting of excel sheet's cells or in the Excel Calculation option setting.

Let's discuss about the calculation issues of Excel and discuss some solution of the same problem:

Possible causes of Excel calculation issue:


1. Cells are formatted as text



Explanation: A text on a cell looks like a formula, but it doesnât work as a calculator.

Cause: This problem occurs when the excel is formatted a text and do not use any formula. This could happen due to the Text format, or usually happen when when data are directly imported from a CSV or Notepad file.



Solution: In order to fix Excel issue, you just need to change the format of the cell(s) to General or to some other format. You need to force Excel to reconsider the content to start formulas working as before. In order to do this follow the following two ways.

Option 1: Select the affected cell (you can do it by pressing a mouse button or F2) and immediately exit again. This is basically for a single formula, in case you are working with multiple formulas, then this method will be time-consuming. For multiple formulas you can use option 2 instead.


Steps:

1. Select the cell that contains the affected formula.

2. Edit menu and choose Clear, and then Formats. This will remove any formatting applied to the cell.

3. Now, press F2. This will put the cell into edit mode.

4. Immediately press Enter. This will force Excel to re-evaluate the cell context.


Option 2: You can use 'Find and Replace' (press Ctrl + F) option. Choose to replace '=' with '='. This will force Excel to refresh the formulas and start calculating as before.


2. The workbook is set to 'Manual Calculation' mode



Explanation: All the formulas are written in a correct manner, but still not delivering the correct results.

Cause: This usually happens when the workbook is set as 'Manual calculation' mode. Manual mode means that the Excel will not automatically update all formulas, whenever there is a need of applying any formula to the workbook, user have to manually force Excel to do so.



Solution: In order to do that, you can either force a manual recalculation, or turn the Excel on 'Manual Calculation' mode.


Option 1: You can force a manual recalculation, by pressing the Calculate in the bottom from the left most corner of Excel, or by simply pressing F9.
Option 2: You can switch back to Automatic calculation by going to Formulas â Calculation options.


Steps to set your workbook as Automatic Calculation mode:

Excel 2010 or Excel 2013:

1. Go to the File tab, and select Options

2. Now, from the pop up list, choose the option Formulas

3. Choose the option Automatic, under the Calculation options, and click OK

Excel 2007:

1. Go to Excel Options by selecting main Excel menu

2. Choose the option Formulas from the pops up list

3. Choose the option Automatic, from the Calculation options, and click OK

Excel 2003:

1. Go to top of the Excel window and choose Tools from the drop down menu, and then select Options.

2. Tap the Calculation tab

3. Choose Automatic, under the Calculation, and press OK


These are the main cause of the Excel formula issue and you follow the above mentioned solution to resolve them. In case your problem is not is not resolved by by either method, you can opt a professional tool like this, which can help you to fix all the Excel related problems.

This blog is listed under Development & Implementations and Data & Information Management Community

Related Posts:
Post a Comment

Please notify me the replies via email.

Important:
  • We hope the conversations that take place on MyTechLogy.com will be constructive and thought-provoking.
  • To ensure the quality of the discussion, our moderators may review/edit the comments for clarity and relevance.
  • Comments that are promotional, mean-spirited, or off-topic may be deleted per the moderators' judgment.
You may also be interested in
Awards & Accolades for MyTechLogy
Winner of
REDHERRING
Top 100 Asia
Finalist at SiTF Awards 2014 under the category Best Social & Community Product
Finalist at HR Vendor of the Year 2015 Awards under the category Best Learning Management System
Finalist at HR Vendor of the Year 2015 Awards under the category Best Talent Management Software
Hidden Image Url

Back to Top