Cleaning Up Your (Excel) Act
All those beautiful spreadsheets you see don’t always start out that way. What? You’ve never seen a beautiful spreadsheet? Well, beauty is definitely in the eye of the beholder and this week we’ll see a super quick and easy way to beautify your spreadsheet data. (Feel free to forward this to any ugly spreadsheet offenders you know–the spreadsheet is ugly not the person!)
Often we don’t have any control of the data we get in Excel. This is the type of ‘messy’ data you might encounter. Things like incorrect capitalization, unnecessary extra spaces and incorrect or missing formatting all contribute to the mess.
Fortunately for you and anybody reading your spreadsheets you can fix this by applying some simple custom formatting options.
There are a few predefine custom formatting options already available in Excel. If none of them meet your needs, you can create a new one.
NOTE: Custom number formats are stored in the active workbook and are not available to new workbooks that you open. If you want custom formats to be available in a new workbook, you can create a template.
How It Works
1. Select the cell or range of cells you’d like to format.
2. From the Format menu, click on Cells..
3. Now on the Number tab, select the Custom category to scroll through the built-in custom options.
Here are a few more examples of what you can do with custom formatting:
Next week we’ll tackle cleaning up the rest of that data and show you how to deal with those messy text cells by converting the text to the proper capitalization, removing excess spaces and more.