Top Nav

How to Refresh Cell Data After Applying Number Formatting in Excel

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 fairly simple to choose a number format for the entire column before entering your data. Simply 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 formatting of a cell, but sometimes it won’t automatically refresh with the new formatting. This usually happens when choosing a custom number format. You can update it by double-clicking the cell, making no changes, and then pressing Enter, but this can be very tedious. This is particularly troublesome when importing large 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 is a bit of a hack, but it works. Basically, 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.

More Reading:

, , ,

71 Responses to How to Refresh Cell Data After Applying Number Formatting in Excel

  1. andy October 30, 2013 at 11:31 am #

    Thanks!! This was a huge help.

  2. Dan February 20, 2014 at 7:14 am #

    Whoa! Where have you been all these years? 🙂

  3. Payam April 9, 2014 at 1:53 am #

    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.

  4. TanMiLujan August 29, 2014 at 11:21 am #

    This trick rocks!! Saved me hours of updating!

  5. Patrick September 10, 2014 at 3:35 pm #

    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 October 6, 2014 at 2:40 am #

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

  7. zyndarius October 16, 2014 at 5:52 am #

    This was indeed something very helpful.

    Thank you.

  8. TONY October 16, 2014 at 4:21 pm #

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

  9. Sharon Jose November 13, 2014 at 10:16 pm #

    Thanks a lot for this wonderful trick.

  10. Philip Egan December 4, 2014 at 3:09 am #

    Excellent, thanks so much for sharing! :o)

  11. Gary December 17, 2014 at 9:02 am #

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

  12. Regine March 4, 2015 at 8:29 am #

    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 March 11, 2015 at 5:34 pm #

    This keeps making my excel crash. Any advice?

  14. Amani March 16, 2015 at 3:06 am #

    thanks from my heart , you really help me

  15. Nicki March 17, 2015 at 9:32 am #

    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 March 20, 2015 at 8:37 am #

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

  17. Peter March 23, 2015 at 4:30 pm #

    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 March 29, 2015 at 2:56 pm #

    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. William April 9, 2015 at 5:45 am #

    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 September 2, 2015 at 8:45 am #

      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 June 15, 2016 at 9:46 pm #

        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 April 22, 2015 at 12:13 am #

    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 May 8, 2015 at 8:39 pm #

    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 June 1, 2015 at 8:20 pm #

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

  23. Vali June 8, 2015 at 11:47 pm #

    Nice, thank you!

  24. Naresh Khurana June 18, 2015 at 3:48 am #

    You rock buddy, Many Thanks!!

  25. Dennis June 20, 2015 at 6:54 am #

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

  26. Piseth July 10, 2015 at 12:11 am #

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

  27. Matt July 21, 2015 at 2:12 am #

    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 August 3, 2015 at 7:54 pm #

    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 September 2, 2015 at 8:46 am #

    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 September 18, 2015 at 10:50 am #

    THANK YOU SO MUCH!!!

  31. Ken October 2, 2015 at 7:33 am #

    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 October 19, 2015 at 1:03 pm #

    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 October 21, 2015 at 3:09 pm #

      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 October 19, 2015 at 4:55 pm #

    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 October 22, 2015 at 9:58 am #

    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 October 22, 2015 at 10:48 am #

      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 October 22, 2015 at 11:54 am #

        @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 October 22, 2015 at 1:05 pm #

        @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 October 22, 2015 at 2:25 pm #

          Awesome, thanks for sharing!

  35. Krista October 28, 2015 at 6:58 am #

    Thank you! This was very helpful!!

  36. Jacques Joubert November 12, 2015 at 1:59 am #

    My gratitude.

  37. TA November 16, 2015 at 10:21 am #

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

  38. Jeffrey Dixon November 18, 2015 at 7:54 am #

    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 November 19, 2015 at 8:10 am #

    Thank you thanks a million man God bless you!

  40. Jeanette December 2, 2015 at 4:48 pm #

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

  41. Tom December 11, 2015 at 12:34 pm #

    Thank you for that! Saved me lots of work

  42. Ethan December 11, 2015 at 1:07 pm #

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

  43. keriba January 13, 2016 at 11:47 pm #

    Thank you
    It really helps me

  44. Lorraine February 1, 2016 at 3:48 pm #

    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 February 11, 2016 at 11:51 am #

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

  46. Odice February 23, 2016 at 5:57 am #

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

  47. Roz March 8, 2016 at 6:53 am #

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

  48. nhe0na March 15, 2016 at 12:35 am #

    Thank for your sharing. You’re awesome.

  49. Misko March 24, 2016 at 8:06 am #

    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 March 25, 2016 at 11:00 pm #

    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 April 4, 2016 at 5:42 am #

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

  52. luigii1 April 21, 2016 at 6:24 am #

    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 May 26, 2016 at 7:40 pm #

    Thank you!

  54. Evaldas June 3, 2016 at 1:10 am #

    Five stars discovery!!!!! Thanks!

  55. Amit June 8, 2016 at 5:47 am #

    Nice trick….very helpful. Thanks !!!

  56. Sathish K July 13, 2016 at 1:37 am #

    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 August 29, 2016 at 2:36 am #

    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 September 6, 2016 at 6:16 am #

    HI,

    Thanks alot this solution works for me 🙂

    Regards
    Nawaz

  59. Minh An September 29, 2016 at 12:14 am #

    Thanks. This is best solution I was looking for

  60. Eric October 14, 2016 at 2:52 pm #

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

  61. Fulvio Civitareale November 8, 2016 at 4:48 am #

    Thanks!!! Yours post helped me a lot!

  62. Mia Lor November 8, 2016 at 12:00 pm #

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

  63. RAM November 10, 2016 at 12:12 pm #

    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 November 23, 2016 at 12:29 pm #

    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.

Leave a Reply