Md. Abdur Rahim Rasel
While working with a large Microsoft Excel dataset, sometimes we see that the formula bar shows different values than cells. It occurs for several reasons. Today, in this article, we’ll learn three quick and suitable ways how to fix the Excel formula bar shows different value than cell error effectively with appropriate illustrations.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Why Formula Bar Shows Different Value Than Cell in Excel
There are several reasons behind formula bar showing different value than cells. One of the main reasons is the Conditional Formatting Feature. If your cells are pre-formatted by the conditional formatting feature then you might not have the same value in your cells that are showing in the formula bar. Take this for an example given below in the picture. As the cell D5 is conditionally formatted, the value is different from the formula bar value.
Another reason is the binary storage of the cells which limits the decimal places that you insert into the formula bar. Therefore when the formula bar is showing 3.45875434799543, Cell D5 is showing only 3.458754348
3 Suitable Ways to Fix the Formula Bar Shows Different Value Than Cell Error in Excel
Let’s consider a situation where we have an Excel worksheet that contains information about several students of Armani College. The Name of the students, their identification number, and their securing CGPA are given in columns B, C, and D respectively. From our dataset, we will learn how the formula bar shows different values than cells. To do that, we’ll use the Format Cells command, Conditional Formatting, and the ROUND Function also. Here’s an overview of the dataset for today’s task.
1. Make the Column Wider to Show Value in Cell in Excel
While the cell width is smaller than the cell’s value length, then the formula bar shows a different value than a cell. Let’s follow the instructions below to fix this problem!
Steps:
- First, place your cursor on the right side of column D.
- Hence, a two-directional array will appear in front of you, then press double click on column D and you will be able to see the value which has been given in the below screenshot.
Read More: [Fixed!] Excel Formula Bar Not Showing Text (3 Quick Solutions)
2. Use the File Option to Fix Formula Bar Shows Different Value Than Cell
We can solve our problem by using the File ribbon option. This is an easy and time-saving way also. Let’s follow the steps below to fix this problem!
Step 1:
- First, press on the File ribbon.
- After clicking on the File ribbon, a new window pops up. From that window, go to,
More → Options
Step 2:
- After pressing on the Options menu, a dialog box named Excel Options will appear in front of you. From that dialog box, firstly, select the Advanced Secondly, check the Set precision as displayed box from the While calculating this workbook option.
- After that, a Microsoft Excel warning box showing Data will permanently lose accuracy will appear in front of you. Press OK, from that Microsoft Excel warning box.
- Again, press the OK option, and you will be able to solve the problem.
Read More: [Fixed] Excel Formula Bar Not Showing Cell Contents
3. Insert the ROUND Function to Solve Formula Bar Shows Different Value Than Cell Error in Excel
In this method, we will apply the ROUND function to fix the problem that shows the different values in the formula bar than cells. Let’s follow the instructions below to learn!
Step 1:
=ROUND(D5,2)- Where D5 is the cell reference, and 2 is the num_digits.
- Hence, press ENTER on your keyboard, you will get 22 as the output of the ROUND function.
Step 2:
- Now, autoFill the ROUND function to the rest of the cells in column E.
- After completing the above process, you will solve the problem that the formula bar shows different values than cells which have been given in the below screenshot.
Read More: How to Remove Decimals in Excel Formula Bar (5 Methods)
Things to Remember
👉.Press Ctrl + 1 simultaneously on your keyboard to pop up the Format Cells dialog box.
👉 You have to press double-click on the left button of your Mouse to widen a column.
Conclusion
I hope all of the suitable methods mentioned above to fix the different values in the formula bar than cell will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
