Number Formatting Pt. 2: Customizing your Numbers

Now that your boss is thoroughly impressed with your number formatting skills, let's blow his mind with some more!

In this post we will teach you how to customize numbers and dates. With that knowledge, you will be able to customize all of your other values. Let's start with numbers.

Numbers

By default, Excel does not separate thousands with a comma. Instead, number are ugly and look like this:

But wait, you want them to look prettier! You would like them comma separated, negatives to have parentheses, and negatives to also be red. How do we do this?

First, select your numbers. On the Home tab, under Number, click on the drop down arrow shown below:

The following menu will appear. Under Category, select Number. 

From here, you can check the "Use 1000 Separator" box and then select how you would like negatives presented. After you are done, click OK. Don't your numbers look so much better?

Dates

Now on to dates. In the accounting profession, we like our dates formated like this: 04/2013. This, however, is not a default date format. Here is how you can change the date format (or any other value for that matter).

First, select the cell you want to format and click on that same drop down arrow mentioned above. Once the form appears, select Custom.

You will notice a whole host of options. You will notice that as you select each Type, Excel will show you exactly how it would look under the Sample section on the form. However, our date format is not listed. It looks like we will have to create our own. It is simple. Under Type, tell Excel how you would like it formatted. For instance, I want 4/15/2013 to be formatted as 04/2013. So I put "mm/yyyy" under Type. Once you are done, click OK.

And there you have it! Now it is your turn. Mess with each of the categories, and if you are feeling really adventurous, create a custom number format. Ready for your boss to promote you on the spot?

Number Formatting Pt. 1: Phone #'s, Zip Codes and SSN

You have the wonderful task of entering in hundreds of phone numbers into a spreadsheet. Your boss wants all of the phone numbers to be in this format: (617) 555-5555. Before you came to this blog, this would have been a very, very tedious job. Now you will learn how to quickly format phone numbers, zip codes, and social security numbers in a few quick, easy steps.

First, enter you list of numbers without any formatting as show below (notice how by default zip codes that start with a zero only show 4 numbers. Our formatting will fix that!):

Select the numbers you want to format and press Ctrl + 1. Alternatively, you could to to the Home tab, under the Number section click on the drop down menu to change the number format and click on More Number Formats down at the bottom.

You will notice under Category there is a Special option. Click on that and this menu will now appear.

From here, click on the format you want and click OK. And that is it! No more tedious work for you! Now on to the more important duties of your job.

Join us next week where we will continue to explore more number formatting options!

Analyzing Your Data Like a Pro: Data Analysis Pt. 2

Now that you have installed the Analysis Toolpak, let's get down to analyzing your data!

In Column A and B we have the weekly closing stock price of the Dow Jones from 1/1/13 to 6/30/13.

If you wanted to statistically analyze the data, you could go through and manually enter all of the formulas. But who has time for that?! Instead, lets use our new-found friend, Analysis Toolpak.

1. Select all of the data in Column B. It does not matter if you include the heading. Go to the the Data tab and to the far right you will see Data Analysis. Click on that icon.

2. A menu will appear. Select Descriptive Statistics and OK.

3. The next form that appears should already be partially populated with the Input Range. Make sure if you included the heading that you click the "Label in first row". The output is about 2 columns wide and 14 rows. Also, be sure to select the "Summary statistics" box. Once you have made all of the changes, click OK.

4. You are all done! Now all of your statistics are in a single place and all you had to do was click a few buttons. Now the big decision...what to do with all of your extra time...

Analyzing Your Data Like a Pro: Data Analysis Pt. 1

Let's say you have 3,000 rows of data, for instance, historical stock prices. You want to quickly see the following information:

• Average
• Standard deviation
• Confidence level
• Minimum
• Maximum
• Count
• Sum

You could create a separate area on your spreadsheet and manually input all of this information...OR you could have Excel automatically do it for you! In this 2 part tutorial, we'll teach you have to get going on this tool. The first part will teach you have to put the Analysis ToolPak on your Excel. The second part will teach you how to use it.

1) Go to File on the Ribbon and click on Options at the bottom of the left hand column.

2) A menu will pop up. One the left hand column, select Add-Ins

3) At the bottom-center of the page, select Excel Add-ins from the drop down menu and then click Go

4) Once the next menu appears, click the Analysis ToolPak option and then click OK

5) Your new favorite statistics toy is now conveniently located on your Data tabon the far right side! Awesome!

Now that you have the tool loaded, tune in next week where we will teach you some of the cool items you can do with the Analysis ToolPak!

How to Fix Your Problems: Remove #N/A and Other Errors from Your Spreadsheet

If you have eaten food in the last 3 days then chances are you have come across the annoying #N/A or #DIV/0! error in Excel. Most likely you have encountered this error as you copy and paste a formula down a large set of data or if you use more complicated functions such as VLOOKUP. Although these errors can be useful to detect possible errors, for the most part they cause speadsheets to look messy and raise unnecessary questions from end users. Excel has created a very easy way to get around this error. Forget 3 steps, this will only take 1!

Let's say the formula A1/B1 you copied down Column C has some pesky zeros in Column B which inevitably causes the dreaded #DIV/0!. To fix this, try copying and pasting the following formula instead:

=IFERROR(A1/B1, 0)

This formula is saying the following: If A1/B1 causes any type of error, put a 0 there instead of an error message, else, put the result of A1/B1. 

You are not limited to replacing the error with a number. You could also put text: 

=IFERROR(A1/B1, "You cannot divide by zero!")

You can use IFERROR for any formula. For instance, here is how you would use it for a VLOOKUP:

=IFERROR(VLOOKUP($A$1:$B$8, 5, 2), 0)

Take a few minutes this week and apply the IFERROR to your formulas!

Get your mother something special next Sunday by giving her a clean-looking spreadsheet. You are bound to be hailed as the greatest son/daughter in the family.

From Vertical to Horizontal: Transposing Your Lists

I remember in college receiving a set of data going down a column. However, I wanted to present it going across the row. How could I ever do this?! I will tell you how. I started from top and one by one copied and pasted each cell. If you are doing this then STOP THE MADNESS!!! Here is how you can quickly transpose data.

First, select the cells you want to transpose and copy it. Now select the cell where you want to transpose the data. On the Home tab, you will see a Paste button. Click on the down arrow to see more paste options. At the bottom of the the drowdown, you will see a Paste Special. Click on that. 

A form like the one below will show up. Check the Transpose box, click OK, and BAM! You just transposed your first data set.

As always, join us next week as we take you on another fun-filled Excel adventure!

Set Your Cells Apart: How To Use Conditional Formatting

Imagine your boss comes in to your office one day and gives you a giant spreadsheet that goes on for pages and pages and pages. He wants you to highlight every single cell that contains word "NO" and says it is a top priority. There are two ways you can do this. If you prefer to do things the tedious and time-consuming way, you could go through every single sheet and cell in the entire workbook and highlight each and every cell. OR if you like to be quick and efficient, you can use Excel's powerful Conditional Formatting tool. If that way sounds appealing to you, read on! We will explain how to make this daunting and formidable task as easy as 1, 2, 3!

1. Select the cells where you want the formatting applied. On the Home tab, click the Conditional Formatting icon and select "New Rule."

2.Select the option "Format only cells that contain". In the first drop down menu, select Specific Text (or Cell Value, etc). In the third input box, type "No".

3. Select the custom format you want by clicking the Format... button. We selected the font color to be red but you can format it anyway you like. Hit OK.

And there you have it! What could have taken you hours and hours now literally takes you minutes! 

There are many other great ways to format conditionally. Be adventurous and play around with some of the other options and you will be impressing your boss and getting that promotion you deserve in no time!