Close this search box.

Get rid of that ugly formatting with two simple tricks



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.


Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Announcing Power BI Dashboard Contest 2024

Announcing Power BI Dashboard Contest (win $500 prizes!)

Hey there, I have a SUPER exciting announcement! April is about to get a whole lot sweeter with our Power BI Dashboard Contest! Your mission, should you choose to accept it: Craft the most EPIC dashboard for the Awesome Chocolates CEO with sales & financial insights! Winners stand a chance to score up to $500 in Amazon Gift Cards, plus some serious bragging rights!

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