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.
- Press Ctrl+H to launch Find Replace
- Click on Options button
- Click on Format button against Find area.
- Now select “Choose from cell” option and point to the cell that contains the formatting you want to reset.
- Click on Format button against Replace area.
- Now select “Choose from cell” and point to a cell that contains the formatting you want.
- Pro tip: If you have no cells with default formatting, just click any blank cell, Excel will use default settings.
- Pro tip: If you have no cells with default formatting, just click any blank cell, Excel will use default settings.
- 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.
8 Responses to “Get rid of that ugly formatting with two simple tricks”
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
Never knew i could do this ... many thanks Chandoo you have saved me lots of lost minutes/hours formatting spreadsheets 🙂
Nice family photos 🙂
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.
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!
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
Sub ClearFormats()
Selection.ClearFormats
End Sub
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!