Get rid of that ugly formatting with two simple tricks

Excel Howtos , Learn Excel - 8 comments

We are on a tiki tour around NZ. So far we have been to Taupo & Rotorua. And we are doing what you do when you are on a holiday – being lazy, going on walks, swimming in lakes, eating copious amounts of food and getting lost. Of course, all this means, I have very little time to access to internet & my blog. So the updates will be slow for next two weeks. Here is a quick tip (well, two of them) to keep you busy and awesome.

How to remove ugly formatting from your workbooks?

Do you have a colleague or boss (shudder) that loves to apply their special touches to every workbook their mouse lands on? Do you constantly wince and whine when you have to work on that spreadsheet.

Here are two handy ways to restore your data to its original glory.

Clear formats:

Simple, select the data you want formatting gone from, go to Home > Clear > Formats.

And Excel will weave an expelliformat spell at your data and make it clean.

Here is a quick demo.



Find replace formats:

If you are selective about which formatting to reset, you can use Find Replace (Ctrl+H) to do that. Just follow below instructions.

  1. Press Ctrl+H to launch Find Replace
  2. Click on Options button
  3. Click on Format button against Find area.
  4. Now select “Choose from cell” option and point to the cell that contains the formatting you want to reset.find-replace-formats-based-on-cell
  5. Click on Format button against Replace area.
  6. Now select “Choose from cell” and point to a cell that contains the formatting you want.
    1. Pro tip: If you have no cells with default formatting, just click any blank cell, Excel will use default settings.find-replace-formats
  7. Click on Replace All and bingo.

So there you go. Talk to you again from somewhere else.

PS: Here is a pic of all of us enjoying mesmerizing sun set on Lake Taupo. We are off to have fun on Rotorua gondola and luge.


Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.

Related articles:

Written by Chandoo
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

8 Responses to “Get rid of that ugly formatting with two simple tricks”

  1. Ikram Siddiqui says:

    Dear Excel Guru,

    Hope everything is fine with you?

    Can you please help in this Logic, it is a thought only to increase my knowledge SIR?

    Please note that I have been working in Excel file contains two times of our teammates who claims overtime an each calendar month

    My excel file as like this :-

    ROW 1 Days of Month
    ROW 2 Date of Month

    Cell -1 [Time IN(06:00Hrs)], cell -2 [Time OUT(15:30Hrs)] no break in our factory and anything after Eight hours assume as overtime as standard in all across.

    Appreciate if you could help me in providing the best an Exclusive Excel formula to calculate each day overtime excluding staff eight hours regular duty and Friday consider as full day overtime.

    Kindly help me at the earliest convenience.

    awaiting for your expertise.............

    Best Regards / Ikram Siddiqui

  2. Hazel says:

    Never knew i could do this ... many thanks Chandoo you have saved me lots of lost minutes/hours formatting spreadsheets 🙂

  3. Steven says:

    Nice family photos 🙂

  4. Don says:

    How about mentioning the Excel versions covered by this. My Excel 2010 doesn't have these features. I would create a single cell with plain formatting and use the format painter.

    • Roger says:

      Check the right edge of your HOME ribbon. The Editing group is there along with Clear. They moved the group in this video to be after clipboard!

  5. Brano Kollar says:

    Hi. Good tips. I also developed an addin, that Resets the default text alignment (numbers to the right, text to the left, boolean centered). Here is the simple code, that runs from a button in my ribbon:

    Sub restoreHorizontalAlignment(control As IRibbonControl)
    Selection.HorizontalAlignment = xlGeneral
    End Sub

  6. Chirayu says:

    Sub ClearFormats()


    End Sub

  7. Lewis says:

    Hi there,

    I have a quick question and I am sure you have a solution as you are awesome!

    I need to either vlook up or index and match the first non zero amount for each Acc # and ignore all zeros. Sample data set is as follows.

    Date Acc# Trans Check
    01/Jan/2017 5014 42,277 Correct
    01/Jan/2017 5015 42,369 Correct
    01/Jan/2017 5016 42,094 Correct
    1-Jan-2017 1047 - Not this
    5-Jan-2017 1047 - Not this
    15-Jan-2017 1047 42,308 But this


Leave a Reply

« »