#246 Cleaning Up Your (Excel) Act: Part 1

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.

Probably, the most common problem are numbers with no delimiting characters to help you read them. Let’s take a look at those first two columns of numbers. Which would you rather try to read?
 OR

Fortunately for you and anybody reading your spreadsheets you can fix this by applying some simple custom formatting options.

Custom formatting

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.

If you’re interested in learning more about custom formatting, you can get a quick explanation or all the gory details.

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.

Advertisements

One thought on “#246 Cleaning Up Your (Excel) Act: Part 1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s