[Fixed]: Formula Bar Shows Different Value Than Cell in Excel

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.

Why Formula Bar Shows Different Value Than Cell in Excel?

There are several reasons behind the 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

Formula Bar Shows Different Value Than Cell in Excel: 3 Possible Fixes

Let’s consider a situation where we have an Excel worksheet that contains information about several students of Armani College. The Names 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. We’ll use the Format Cells command, Conditional Formatting, and the ROUND Function to do that. 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, 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.

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, too. 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.

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:

First, select cell E5, and type the ROUND function in that cell. The ROUND function is,

=ROUND(D5,2)

Where D5 is the cell reference, and 2 is the num_digits.

Hence, press ENTER on your keyboard, and 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 given in the screenshot below.

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.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Show Different Value Error.xlsx

Conclusion

I hope all of the suitable methods mentioned above to fix the different values in the formula bar than cell will provoke you to apply them in your Excel spreadsheets more effectively. You are most welcome to feel free to comment if you have any questions or queries.