[Solved] Excel Formatting Not Working Unless Double Click Cell

While working on an Excel file, it is a common phenomenon that sometimes cell format doesn’t work unless you double-click the cell. Most of the cases it happens for the Text format. In this article, I will show you the reason the problem of Excel formatting is not working unless you double click cell and ways to fix this problem.

Reason for Excel Formatting Not Working Unless Double Click Cell

Formatting is a special feature of Microsoft Excel that tends to give the user an idea about the type of data (i.e. Number/Currency/Date/Time etc.). You just enter a numerical value in an Excel worksheet and Excel automatically formats it according to the type of the entered data. So, when you intend to enter data in Excel, it is safe to format the column or the row in which you want to enter the data.

But you may find it troublesome when you format a column or a row that already includes data. In this case, your formatting will not work instantly.

Let’s find the cause with proper illustrations.

Let’s say, we have got a dataset of some Employees of an organization, the Start and End times of their work. In the blank cells, the time format should be input.

The steps below are provided for consideration to find the cause of not working or updating the cells.

⏩ Steps:

• First of all, see the advantage of formatting the cells before entering data into them.
• Select the two corresponding columns (Column C & D), go to the Home tab> click the dropdown of the Number Format box> and select Text format.

• So now, these two columns are in Text format. Enter data in these two columns.

We have converted the format of these two columns to text because the main reason behind the cell not being updated is to Text format. When Excel recognizes a numerical value as Number/Time/Date, it updates naturally with any correction or editing in it. But when a cell is formatted as text, Excel can’t recognize it instance and for this, the cell doesn’t work unless you double-click the cell. Actually, double-clicking takes you to the editing mode and pressing ENTER followed by double-click will make the cell format update afterward. To demonstrate this, follow the image below.

Now, we want to convert the Start Time and End Time into a Custom (Time) format. We want it as [h]:mm:ss. So, select the data range> press CTRL+1 to open the Format Cells dialog box> select Custom from Number group> choose the [h]:mm:ss format> click OK.

• But, you won’t find any changes to the cells. It is because as these cells were previously formatted as Text, so Excel can’t take the change for instance.

• But, if you double-click any cell (i.e. cell C5) now to go to the Editing mode and then press ENTER, now you will see the cell working.

• The format has been updated. But you can’t repeat the process if you have got thousands of cells in your worksheet.

So, we should find a quick and automatic process to fix this.

Making Excel Formatting Work Without Double Clicking Cell

This section covers 2 effective ways to fix the problem of Excel formatting not working unless double click. Let’s demonstrate the processes.

1. With Text to Columns Feature

Utilization of the Text to Columns feature is one of the quick hacks to update cell formatting without double-clicking. Just apply it and see the result. But one thing noticeable here is that this feature can’t be applied in the case of two columns. So, you have to apply it column by column.

⏩ Steps:

• First, select a data range of a column (i.e. Start Time) > go to the Data tab> click the Text to Columns feature under the Data Tools group.

• Then, Step 1 of 3 of the Convert Text to Column Wizard command box will appear. Click Finish.

• As a result, the cell format will work instantly. You don’t need to double-click the cell and press ENTER to update this.

• Repeat the steps for the other column also.

2. Copying Blank Cell

Let’s say, now the dataset contains dates in it. The cause of not working the cell formatting is the same as we describes before.

Now, for this dataset, we will copy a blank cell in order to make the cell format work properly.

⏩ Steps:

• At first, select a blank cell (i.e. F5) on the worksheet that doesn’t include any data, blank space, formula, etc.

• The cell reference that you are selecting must result in TRUE by applying the ISBLANK function. Check that like the image below. If the ISBLANK function returns TRUE, then you can consider the cell blank.

• Now, copy the blank cell (i.e. Cell F5) and then select the cells where you want to update formatting> right-click the mouse> select Paste Special…

• Here, from the Paste Special dialog box, select Add from the Operation group> click OK.

• Now, the cell format will change like the image below.

• After that, press CTRL+1 to open the Format Cells dialog box. Choose the Date Format (i.e. Wednesday, March 14, 2012) you want to show in the updated cells> click OK.

• Hence, your problem will be solved. All the cells will be updated according to the new Formatting.

Things to Remember

• While applying the Text to Columns feature, just select one column at a time because Excel doesn’t permit a user to apply this feature for more than one column.
• While copying the blank cell, make sure that this cell returns TRUE for the ISBLANK function.

Conclusion

In this article, I have tried to show you some methods to solve the problem of Excel cells not working unless double click. I hope this article has shed some light on your way to this. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box.

Related Articles

• How to Use Excel Cell Format Formula
• How to Copy Cell Format in Excel

<< Go Back to Excel Cell Format | Learn Excel