fbpx

Get rid of that ugly formatting with two simple tricks

Share

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

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.

clear-formats-excel-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 on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

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

Chandoo is an awesome teacher
5/5

– Jason

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

100 Excel Formulas List

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

20 Excel Templates

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

Weighted average formula in excel

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

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()

    Selection.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

    Thanks!

Leave a Reply