Monday, September 16, 2013

Remove All Formatting in Excel

Let’s say you have a spreadsheet that someone built for printing or looks.  It might have gridlines, background colors, special fonts, and the like:


All you want out of this is some nice rows and columns for analysis, and the formatting is not helpful for this purpose.  How do you quickly simplify this data without updating every single column or cell?  First, select the entire worksheet (or whatever area you want to update).  Under the home tab (far right side), select Clear Formats:


Once you’ve done that, your data will be ready for use:


There’s only one drawback to this approach that I’ve found so far.  If you have date fields in your data, you’ll have to re-apply the date format to the affected columns/fields.  Otherwise, the column will display the date serial number because it has no format assigned.  Other than that, this method works great for quickly removing formatting so that you can focus on the data.