[From  https://www.groovypost.com/howto/refresh-cell-data-number-formatting-excel/]

How to Refresh Cell Data After Applying Number Formatting in Excel

By Jack Busch

Last Updated on January 23, 2019

Share

Tweet

Share

Email

Comments

When entering numerical data in Excel, it helps to format it according to the type of data it is. Formatting a cell according to its data type—e.g. percentage, currency, date, or text—helps drive consistency and accuracy in displaying and working with your data. If you know from the get-go what format your data should be in, it’s relatively simple to choose a number format for the entire column before entering your data. Just select a column and choose a number format from the Number pane in the Home ribbon:

However, things can become troublesome when trying to apply number formatting to an existing range of data. You can update the format of a cell, but sometimes it won’t automatically refresh with the new formatting. This stubbornness usually happens when choosing a custom number format. You can update it by double-clicking the cell, making no changes, and then press Enter, but this can be very tedious. This process is particularly troublesome when importing significant amounts of data.

If you find yourself in this boat, try this trick:

Start with a range of pre-entered data. In this example, we’re using a column that’s entered as text. We’d like to give it custom formatting, so it looks more like a time from a stopwatch. We want to give it the format: [h]:mm:ss

To do this, start by selecting the column.

Then, click the drop-down in the Number pane of the Home ribbon. Choose More number formats. Or, if you want, choose one of the presets.

Choose Custom and type in the format you want to use for the numbers. Click OK.

Notice nothing has changed, even though it shows “Custom” in the Number Format drop-down.

If you edit the cell and press enter, the new format takes effect. But with hundreds of rows of data, this will take forever.

To speed things up, select the column and go to the Data ribbon and click Text to Columns.

Choose Delimited and click Next.

Uncheck all the delimiters and click Next.

The number formatting of all the cells will update.

This trick is a bit of a hack, but it works. What it does is take all the values from each row and then re-enters them into the cells automatically. For this reason, this trick will not work for cells that are formulas. If you have formulas, pressing F9 should recalculate the sheet and update the number format. But in my experience, I haven’t had this problem with formulas.