Uncheck all the delimiters and click Next. To speed things up, select the column, go to the Data ribbon, and click Text to Columns. But with hundreds of rows of data, this will take forever. If you edit the cell and press enter, the new format takes effect. Notice nothing has changed, even though it shows “Custom” in the Number Format drop-down. Or, if you want, choose one of the presets.Ĭhoose Custom and type in the format you want to use for the numbers. Then, click the drop-down in the Number pane of the Home ribbon. To do this, start by selecting the column. We want to give it custom formatting so it looks more like a time from a stopwatch. In this example, we’re using a column that’s entered as text.

If you find yourself in this boat, try this trick: This process is particularly troublesome when importing significant amounts of data. You can update it by double-clicking the cell, making no changes, and then press Enter, but this can be very tedious.

This stubbornness usually happens when choosing a custom number format. You can update the format of a cell, but sometimes it won’t automatically refresh with the new formatting. However, things can become troublesome when trying to apply number formatting to an existing range of data.

Format date cells in excel