How-To

How to Refresh Cell Data After Applying Number Formatting in Excel

excel-2013-icon

There’s a quirk with Microsoft Excel 2010 (and possibly other versions) where custom number formats don’t get applied to existing data. This quick fix can save you from the tedium of re-entering thousands of rows of data.

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:

refresh cell data after after applying number formatting

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.

image

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.

refresh number formatting in excel 2010

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

how to apply custom formatting to existing data

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

updating custom number formatting on many rows

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

number formatting not updating automatically

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

text to columns excel 2010

Choose Delimited and click Next.

fixing number formatting in imported data

Uncheck all the delimiters and click Next.

cell data numbering fix

The number formatting of all the cells will update.

applying custom number formatting mulitple rows

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.


90 Comments

90 Comments

  1. andy  

    Thanks!! This was a huge help.

  2. Dan  

    Whoa! Where have you been all these years? 🙂

  3. Payam  

    This is the best excel trick ever for us working with cvs and barcodes that get the +xx at the end.
    Perfect. Hope you live a good life with many childrens and a goat for the milk. make cheese of the milk and sell it and live an even happier life.
    thanks again. Im very happy, even though i don’t own a goat.

    • Alane  

      LOL best comment ever

  4. TanMiLujan  

    This trick rocks!! Saved me hours of updating!

  5. Patrick  

    This problem has plagued me many times. Easy when there are only a couple cells, but a pain in the butt when there are many…until NOW! Thanks for the hack!!

  6. Noortje  

    THANK you! You just saved me a few hours of tedious work!

  7. zyndarius  

    This was indeed something very helpful.

    Thank you.

  8. TONY  

    HUGE HUGE HELP…..
    MANY MANY THANKS……
    CHEERS.
    TONY

  9. Sharon Jose  

    Thanks a lot for this wonderful trick.

  10. Philip Egan  

    Excellent, thanks so much for sharing! :o)

  11. Gary  

    I’ve been wondering how to fix this for years now, this works perfectly, thanks

  12. Regine  

    WOW!!! Totally amazing…thank you so much for this step by step tutorial. You just saved me hours of mind numbing work. Have a blessed day…

  13. matt  

    This keeps making my excel crash. Any advice?

  14. Amani  

    thanks from my heart , you really help me

  15. Nicki  

    This has saved me hours – thank you so much! You have succeeded where my IT department failed. Over 118000 lines of data for 12 months, and this problem has been recurring. You are literally a life saver!

  16. John  

    THIS IS AMAZING! Genius hack that is a huge time saver.

  17. Peter  

    thanks. What a weird thing – same problem with a formula in an adjacent column using isblank – would not execute until double clicked in each cell in column it was reading from. This was doing my head in. thanks

  18. Rick  

    Thank you for the solution!

    If you want to put this process into VBA code use the following:

    Range(“Whatever range of cells you need”).Select
    Selection.NumberFormat = “Whatever Format you Desire”
    Selection.TextToColumns DataType:=xlDelimited

    Example:

    Range(“C2:C8548”).Select
    Selection.NumberFormat = “[$-409] mmm-yy;@”
    Selection.TextToColumns DataType:=xlDelimited

    This example takes the dates in cells C2 through C8548 formats them to Month – Year (Nov-14) and the performs the TextToColumns fuction in the article above to refresh the cells.

  19. I went through all you suggested but the numbers entered as text will not convert to numbers, and of course autosum does not function. I only have this problem on one sheet other work sheets in the book work fine. It’s annoying cant find the solution to a known problem

    Thank you in advance

    • Suzanne  

      Another hack for that problem is to use the copy values and multiply using 1. To do this enter 1 in any blank cell. Copy the cell using control c and then highlight the area with the numbers reading as text, use paste special and in the options choose value and multiply. This keeps the same number since multiplying by 1, and allows excel to see that it is a number.

      • Hayden  

        Thank you Suzanne!

        For some reason the original hack didn’t work for me suggested by Jack? I followed everything to a tee and excel kept crashing? It frozen and kept saying it’s trying to find a solution to the problem and never recovered. I tried it to a smaller list but will still the same issue.

        I had numbers stored as text and when changing the category back to General or Number it kept coming up with the error of number stored as text. It didn’t happen to all cells only ones where the text used to be say 0.30, 0.40 etc (with the extra 0 at the end). It would still stay as 0.30 after changing to the General category. I had to then go through all of these numbers and click on them to edit then enter again. It would then register as a number again and display as 0.3.

        Only your way as suggested fixed my problem! Thank you!

  20. Raz  

    Wow.. one of the most useful hacks ever.. saved me tons of time and as someone else mentioned you have a good life there with tons of happiness and money and children and goat milk! 🙂

  21. Susie  

    THANK YOU! 🙂 I knew there MUST be an easier way but never bothered to search for one before now. Thank you SO much!!! 😀

  22. wan  

    U r my savior… this trick work like a charm… thanks… 🙂

  23. Vali  

    Nice, thank you!

  24. Naresh Khurana  

    You rock buddy, Many Thanks!!

  25. Great tip, thank you – harbored over this issue countless times, but never again!

  26. You’re legend. It’s such a great help. I had tried many solutions; none worked, but yours.

  27. Matt  

    Great hack. One addition that makes it do more… If you have text masquerading as a formula because the text happens to have “=” in as the first character then this hack can be adapted to make the “forumula” switch to text – tick the Text checkbox in the Text to Colums wizard. Cheers.

  28. Chris  

    Doesn’t seem to work in Excel 2013 when trying to get it to update and recognize the literal value ‘ that had been added to a column of values using formula.

  29. Suzanne  

    Thank you!!! This saved me a huge amount of time as it was a date that was reading as text and nothing I was doing worked.

  30. Sampson  

    THANK YOU SO MUCH!!!

  31. Ken  

    Thank you! This is a great tip. For years I have always copied the range of cells that I want to convert, and pasted them in a text editor. Then I selected all in the text editor, copied, and pasted back to Excel. It works, but with your approach I can do it all within Excel.

  32. Jeffrey Dixon  

    Not to be a downer, and this is indeed helpful, but I find it weird that everybody is raving about a solution that takes opening a menu tab and fiddling with three dialog boxes to implement what Excel should just do automatically – why does Microsoft think you are changing the formatting if not to show the cell content in the new formatting?! If there is some reason why you might want it to apply only to newly entered cell content, I think that must be the exception and not the rule, and the default behavior should be to update. Just saying.

    • Rick  

      Hi Jeffrey,

      You can put this solution into the simple click of a button. If you know how to make a macro you can use the following code that I posted in the comments awhile ago:

      “If you want to put this process into VBA code use the following:

      Range(“Whatever range of cells you need”).Select
      Selection.NumberFormat = “Whatever Format you Desire”
      Selection.TextToColumns DataType:=xlDelimited

      Example:

      Range(“C2:C8548”).Select
      Selection.NumberFormat = “[$-409] mmm-yy;@”
      Selection.TextToColumns DataType:=xlDelimited

      This example takes the dates in cells C2 through C8548 formats them to Month – Year (Nov-14) and the performs the TextToColumns fuction in the article above to refresh the cells.”

      Also if you have the ability you can program in a dialog box to input a column number and execute off of a hotkey you set up.

  33. Joe  

    I had same issue except I was using numbers. the solutions I found had worked for me. I ended up finding the solution. Which was to select a range of cells and format the cells to text. Then paste the numbers in. I then could select the range and change the format.

  34. Ken  

    It would be great if Excel automatically converted the numbers and dates for you in all instances, but it doesn’t. I find myself doing this conversion so often, and this solution works so well that I decided to turn it into a macro. I added to my Excel Addin, and exposed it as a button in the Quick Access Toolbar. I’m going to share it just in case anyone else finds it useful. I use it a lot.

    Before running the macro, each time you must change the format of the cells to the appropriate data type (e.g. Number, Date).
    Another thing to note is that running the TextToColumns macro changes the settings in the UI dialogue. This means you will probably encounter unexpected behavior the next time do something that uses those settings. I added a section of code at the bottom to reset the delimiter to “tab”, which is what I usually use. This can be modified to use other delimiters like “comma”, or this section of code can be removed completely.

    ‘ Converts a value to the data type of the cell.
    Public Sub DataTypeConversion()
    Dim rngToConvert As Range

    On Error Resume Next
    ‘ InputBox will prevent invalid ranges from being submitted when set to Type:=8.
    Set rngToConvert = Application.InputBox(Prompt:=”Select a range of cells to convert the values to the data type of each cell.”, Title:=”Data Type Conversion”, Default:=Application.Selection.Address, Type:=8)
    ‘ Check for cancel: “Object required”.
    If Err.Number = 424 Then
    ‘ Cancel.
    Exit Sub
    End If
    On Error GoTo 0

    ‘ If nothing was selected then exit.
    If rngToConvert Is Nothing Then
    Exit Sub
    End If

    ‘ If more than one column is selected then exit.
    If rngToConvert.Columns.Count > 1 Then
    MsgBox “Only one column can be processed at a time.”, vbExclamation + vbOK, “Data Type Conversion Range”
    Exit Sub
    End If

    ‘ If more than one range is selected then exit.
    If rngToConvert.Areas.Count > 1 Then
    MsgBox “You selected multiple ranges. Only a single range can be processed at a time.”, vbExclamation + vbOK, “Data Type Conversion Range”
    Exit Sub
    End If

    ‘ Use TextToColumns to convert the value in each cell to the cell’s data type.
    rngToConvert.TextToColumns Destination:=rngToConvert, DataType:=xlDelimited, _
    TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True

    ‘ Reset delimiter in TextToColumn dialogue to tab. Optional section.
    Dim ws As Worksheet
    Set ws = rngToConvert.Parent
    Set rngToConvert = ws.Range(ws.Cells(ws.Rows.Count, ws.Columns.Count), ws.Cells(ws.Rows.Count, ws.Columns.Count))
    rngToConvert.Value = “1”
    rngToConvert.TextToColumns Destination:=rngToConvert, DataType:=xlDelimited, _
    TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
    rngToConvert.ClearContents
    rngToConvert.Parent.UsedRange
    End Sub

    • Jeffrey Dixon  

      As for processing ranges / columns one at a time, how about caching them as a collection/array and then running a loop to process them serially?

      Taking it a step further, instead of making it so you have to click a button or enter a hotkey, what about an events macro that detects that you have changed a cell format and automatically runs on the cells that have the format changed? It seems that would functionally emulate the behavior Excel ought to be doing anyway, which would be great as long as it doesn’t cause twitches or glitches or something.

      Is there a reason why an events macro would be impractical?

      • Ken  

        @Jeff: That’s a good idea: to break up the selected range(s) into chunks that Text-To-Columns can process, store them in an array, and process them in a loop. I’ll give that a try.

        Also, using an event macro is a creative idea, but then I think each worksheet for which you want this event to be handled would need an event handler, which turns it into a macro workbook. Instead, I like the idea of using normal workbooks most of the time and using an AddIn with macro abilities to do work on the normal workbook. Perhaps there is a way that I’m not thinking of…

      • Ken  

        @Jeffrey: Thanks for the suggestion. I added the ability for the user to select any number of ranges in any shape or size. At first this seemed like it would be a problem if the user selected an entire ROW, but then I realized that the selection should be limited to the used range in the sheet anyway. Now it’s even simpler for me to convert those unwanted data types. I simply run the macro, press the Select All button in the corner, click OK, and I’m done.

        ‘ Converts a value to the data type of the cell. This approach replaces the need to copy values into a text editor and then paste them back into Excel.
        Public Sub DataTypeConversion()
        Dim rngToConvert As Range

        On Error Resume Next
        ‘ InputBox will prevent invalid ranges from being submitted when set to Type:=8.
        Set rngToConvert = Application.InputBox(Prompt:=”Select a range of cells to convert the values to the data type of each cell.”, Title:=”Data Type Conversion”, Default:=Application.Selection.Address, Type:=8)
        ‘ Check for cancel: “Object required”.
        If Err.Number = 424 Then
        ‘ Cancel.
        Exit Sub
        End If
        On Error GoTo 0

        ‘ If nothing was selected then exit.
        If rngToConvert Is Nothing Then
        Exit Sub
        End If

        ‘ Consolidate intersecting ranges, and limit to used range.
        Set rngToConvert = Intersect(rngToConvert, rngToConvert.Parent.UsedRange)

        ‘ Break range into segments consisting of a single column and a single area, which is the only shape Text To Columns can process.
        Dim rngCol As Range, rngSegment As Range
        Dim colSegments As Collection
        Set colSegments = New Collection
        For Each rngCol In rngToConvert.Columns
        For Each rngSegment In rngCol.Areas
        colSegments.Add rngSegment
        Next
        Next

        Application.ScreenUpdating = False
        ‘ Convert data types in each segment.
        For Each rngSegment In colSegments
        ‘ Entirely blank ranges cannot be processed by Text To Columns.
        If (rngSegment.Count Application.WorksheetFunction.CountBlank(rngSegment)) Then
        ‘ Use TextToColumns to convert the value in each cell to the cell’s data type.
        rngSegment.TextToColumns Destination:=rngSegment, DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
        End If
        Next

        ‘ Reset delimiter in TextToColumn dialogue to tab.
        Dim ws As Worksheet
        Set ws = rngToConvert.Parent
        Set rngToConvert = ws.Range(ws.Cells(ws.Rows.Count, ws.Columns.Count), ws.Cells(ws.Rows.Count, ws.Columns.Count))
        rngToConvert.Value = “1”
        rngToConvert.TextToColumns Destination:=rngToConvert, DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
        rngToConvert.ClearContents
        rngToConvert.Parent.UsedRange

        Application.ScreenUpdating = True
        End Sub

        • Jeffrey Dixon  

          Awesome, thanks for sharing!

  35. Krista  

    Thank you! This was very helpful!!

  36. Jacques Joubert  

    My gratitude.

  37. TA  

    this hint rocks. why cant MS figure this out automatically.

  38. Jeffrey Dixon  

    Here is a simpler macro I just wrote and started using as a kind of “refresh” of selected cells, simulating what would happen if you clicked in each individual cell, moved the focus to the formula bar, and hit enter. I believe this also works for updating the formatting of the displayed cell value.

    Sub RefreshCells()

    ‘ RefreshCells Macro
    ‘ Refreshes selected cells by reentering formula bar text

    ‘ Keyboard Shortcut: Ctrl+r
    Dim formula As String

    For Each cell In Selection
    formula = cell.FormulaR1C1
    cell.FormulaR1C1 = formula
    Next cell

    End Sub

  39. Er  

    Thank you thanks a million man God bless you!

  40. Jeanette  

    THANK YOU! I have struggled with this before. Your fix is brilliant!

  41. Tom  

    Thank you for that! Saved me lots of work

  42. Ethan  

    Thank you so much for this trick. This makes my life 1000X easier!

  43. keriba  

    Thank you
    It really helps me

  44. Lorraine  

    Thank you so much, though I am also angry about this strange problem… I’ve been analyzing 80,000 rows of data for 2 years on a monthly basis using Excel 2010 with NO problems… Now I have experienced this problem for the first time in FORMULAS… the value coming up is the same in all rows (same as whatever initial FILL DOWN cell I choose, or whichever COPY/PASTE cell is at the origin)… It’s incredibly frustrating as it has NEVER happened before, and I can’t spend hours pressing ENTER into every single formula… Worst thing is, my cells are already formatted as General and the calculations are already automatic, so there is NO NO NO possible reason to have to validate the data in each cell one at a time… But thanks to your convert to columns, I can temporarily fix this problem… This would have affected my sales report numbers STRONGLY… so, thanks… but Microsoft, please, WTH…!!!

  45. Justin  

    Thank you so much. This just helped me fix a spreadsheet with 3500 records. Saved me so much time!

  46. Odice  

    I’ve been fighting against this for years. Thanks for the tip, it saved us a lot of time

  47. Roz  

    Thanks. I’d fiddled around for ages trying to find a quick fix

  48. nhe0na  

    Thank for your sharing. You’re awesome.

  49. Misko  

    Extremely lame. There is no REFRESH button in Excel? Like the one in Windows Explorer when you delete a file from folder and then click ‘refresh’…

  50. Atanas  

    God bless you good man.

    I have been using a macro which takes forever.

    Thank you for the tip, best of luck in your endeavors.

    Atanas

  51. Scott Breerton  

    You’ve just saved me about 4 hrs! Many Thanks!

  52. luigii1  

    This worked on my excel. I changed FormulaR1C1 to FormulaR1C1Local

    Sub RefreshCells()

    ‘ RefreshCells Macro
    ‘ Refreshes selected cells by reentering formula bar text

    ‘ Keyboard Shortcut: Ctrl+r
    Dim formula As String

    For Each cell In Selection
    formula = cell.FormulaR1C1Local
    cell.FormulaR1C1Local = formula
    Next cell

    End Sub

  53. Bob richardson  

    Thank you!

  54. Evaldas  

    Five stars discovery!!!!! Thanks!

  55. Amit  

    Nice trick….very helpful. Thanks !!!

  56. Sathish K  

    Excellent Trick.
    Have been doing this manually for ages.. and this saved a lot of time
    Thanks for your tip and keep rocking

  57. Dipen  

    It was frustrating to see the formatting not getting reflected on the selected columns. This post helped me convert my data in the correct format and generate the desired graphs. Thank you for the explanation with image snapshots.

  58. Nawaz  

    HI,

    Thanks alot this solution works for me 🙂

    Regards
    Nawaz

  59. Minh An  

    Thanks. This is best solution I was looking for

  60. Eric  

    Hell yeah! I wish I knew this 10 years ago! Thanks!!

  61. Fulvio Civitareale  

    Thanks!!! Yours post helped me a lot!

  62. Mia Lor  

    Amazing!!! I have been looking for this solution for a long time.

  63. RAM  

    Works Great!! Saved me from the painful job of pressing “enter” in each cell to fix the format in a large data. Thanks.

  64. Excelerated  

    Thank you thank you thank you for the Text to Col trick… can’t tell you how many times I’ve manually clicked on a hundred cells.

  65. DAVID RAJA  

    It works even for formulas also. Thank you very much

  66. Laura  

    You just saved me SO much time. Wish I could go back and save all the time I spent tediously clicking on individual cells before I thought to Google a solution!

  67. Neat trick, that’s why I LOVE internet!!
    Old guy that was born with the RTFM mantra…..

  68. Diggler  

    You just saved me so much time. Thanks!!!!

    • Boater  

      Thanks,
      This applies in a bunch of situations. A recordset was ignoring some cells that clearly had data in them. Applying this fix/hack before creating the recordset did the trick. Apparently ADO gets confused when data formats are inconsistent.

  69. Kay  

    Thank you!!!!

  70. WOW, you saved me SO much time, thank you, thank you, thank you, thank you!

  71. Just had 56,000 rows I needed updated, THANK YOU!

  72. Vivek Wadhwani  

    Thank you much for the detail instruction. I was able to save my 2 hrs.

  73. Ramya Christy  

    Excellent fix! Thanks a ton!

  74. chandra  

    This trick is good for me.
    But i need to do this for multiple columns.
    Do i need to select one column at a time or is there different trick for that?

    • Ken  

      @chandra: You can try the macro I posted in the comments section on October 22, 2015. Check my third post on that day. The way it works is that you will be prompted by the macro to select any range where you want to apply text-to-columns, which can include multiple columns, and even discontinuous ranges. The macro will then loop through the selected columns and areas, and run text-to-columns in the way the original post describes.
      I keep this code in an Addin that I created, and I put button in the Quick Access Toolbar that will run this macro as needed. I use it very often, and it makes it even more convenient to run rather than running text-to-columns manually every time the data conversion is needed.

      There are other comments containing macros as well, but those macros loop through each selected cell individually. That would be fine if only a handful of cells were selected, but it could take a long time if an entire column or more were selected.

  75. Darmella  

    Lifesaver!

  76. Chandra  

    Thanks. This helpsa lot

  77. ChSeEm  

    Thanks for this is a great tip.. Saved me a huge time.

  78. Surender Jain  

    This is a HUGE help! Have been struggling with date values and this solutions works quite well with it as well.

  79. Daniel Luevano  

    +1 nice solution.

  80. Matt  

    Thanks, but this really takes the fun out of pressing F2 enter F2 enter F2 enter a million times until your coworkers start wandering around searching for the source of the noise

Leave a Reply

Your email address will not be published. Required fields are marked *

 

To Top