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! 

Goal Seek: Finding the Perfect Number

Have you ever wondered, "I wonder what X needs to be in order for Y to be 50%?" Good news! Excel thought of that as well! If you know what a certain results needs to be (i.e. profit margin needs to be 35%) but don't know what input value (i.e. Sales) will get you there, then use Goal Seek to find it for you. It sure beats trying to guessing 100 iterations in order get there yourself. Here is how it works.

You will notice on my income statement below that my profit margin (Net Income/Sales) is 26%.

I now want to know what sales need to be in order for my profit margin to be 35% (holding everything else constant). On the Data tab, you will see What-If Analysis. Click on that and select Goal seek.

A box will now appear. In our example we are going to Set Cell C12 (our profit margin), To Value 35%, By Changing Cell C3 (our sales).

Note that the cell you choose for By changing cell needs to be a hard coded value (no formulas). After I press OK, I see that I need to have sales of $623,049 to get to a profit margin of 35%. Pretty neat!

After you show your boss Goal Seek, you are sure to get a promotion, pay raise, and have all of your hopes and dreams fulfilled. Stay tuned for next week's Excel tip!