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! 

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!

Data Validation's Most Powerful Tool: The Drop-down List!

Last week we talked about Data Validation and the fun and excitement one could have restricting cells with it. Today, we are going to talk about one specific tool in Data Validation, the List option.

This option is great because it limits what the user can input in a cell while giving them options from a predefined list you create. This can greatly reduce input error and can streamline data input. Here is how you do it!

To start, create a list of items in cells A1 through A5, as shown below.

Then, click on cell B1. Open up the Data Validation menu from the Data ribbon. Choose 'List' and under Source type: "=A1:A5"

Now when you select cell B1, a drop down arrow will appear. When you click on it, you should be able to choose from the list you created in cells A1:A5. You can change this list at anytime and it will automatically update in B1.

And there you have it! Be the life of the party this weekend by showing your friends this cool trick. Stay tuned for next's week post!

Data Validation: Limiting Others' Inputs w/o Limiting Your Outputs

Are you ready to learn one of the slickest tricks in Excel? Then read on, my fellow Excelerites! Today we are going to learn about Data Validation.

The overarching goal of Data Validation is to restrict a user to inputting certain items into a cell. For instance, you can restrict the user to only put whole numbers into a cell. 

To begin, select the area that you want to restrict. Next, go to the Data tab on your Ribbon and select Data Validation.

A form will appear. You will notice that it has 3 tabs on it. Under the Settings tab, select the dropdown arrow under Allow.

The default is Any Value. In other words, the user can input any value into the cells. Be adventurous and try experimenting with some of the options. You can start by selecting Whole Numbers. To see if your restriction worked, try inputting a restricted value into your cell. For example, let's say you restrict cell A1 to be only whole numbers between 1-10. If you try to put a 17 in that cell, an error message will appear. 

Speaking of error messages, you can customize your error message.Under the Error Alert tab, you can change the error message Style, Title, and Message. Please note that the Style will determine what type of message and restriction is allowed. For instance, the Stop option will not allow any value outside of the restriction. On the other hand, the Information option will. Both, however, will produce a popup. I created the following error message to pop up if the user tries to put a 17 in cell A1. Because I selected the Information option, the user can still change the cell value to 17 but will get this warning before. 

All of this is very easy to learn. Give it a whirl! Stay tuned for next week's post where we delve into the most powerful part of Data Validation: the List!

Extreme Filters & Sorting

This week's tip is one that many of you may already know a bit about. However, we want to take your knowledge one step further and show you some neat functions.

1) Sort by Color: Let's say your boss goes through a list and highlights all the people he wants to meet with in the next week. What is the best way to sort through a list of over 500 employees? Easy, just filter it by color! First create the column filters.

Next, click on the column filter arrow, find the Filter by Color option, and select the color you wish to filter. If your boss selected multiple colors, you can also select the Sort by Color option on that same drop down menu.

2) Custom Sort: Most of your friends probably only know how to use the simple Sort function. However, that tool is very limited. The Custom Sort tool is much more powerful. You will find it on the Home ribbon - Editing - Sort & Filter - Custom Sort

To get started, highlight the table you would like to sort. Make sure you include the headings. Next, select Custom Sort on the Home ribbon. A box like this will appear.

The Sort By drop down will allow you to select which heading or column you want to sort. You can then select what you want to Sort On (usually just Values) and the Order you want it. For this example, We want to sort by ID Number from Smallest to Largest.

You can even add multiple sorting criteria. You do this by clicking on the Add Level button. Now I can first have it sort by ID Number from Smallest to Largest and within that, sort by Last Name from A to Z. That is pretty neat, huh?!

Now you can sort and filter like no one at work can! This is sure to save you time, organize your data more efficiently, and, most importantly, impress your co-workers. Stay tuned for next week's tip!

The Almighty Alt Key

Now that you have mastered the keyboard shortcuts of last week, let's learn how to literally never use your mouse again. Are you ready for this?

Learning the Alt key shortcuts is relatively simple when compared to the shortcuts we outlined last week. The tough part is the growing pains associated with parting with your mouse. Why is it so easy to learn? Because Excel shows you step by step what keys to press. Let's demonstrate.

Let's say we wanted to change the font size or our cell to 14 from 11.

To get started, press the Alt key on your keyboard. You will notice that numbers and letters will appear on your Ribbon (I told you Excel would tell you what to do).

Next, press the "H" key. This will take you to the Home tab. You could have also pressed the "N" or any of the other letters on the Ribbon to take your to the other tabs. Your ribbon should now look like this:

Note that every item on the Ribbon has a letter or number associated with it. To change the font size, just type "FS", type the number 14, and press Enter. That's it!

You may be thinking, "This takes much longer than using my mouse!" However, once you get accustomed to the Alt key and have your favorite functions down, this will save valuable time. In addition, your hands never need to leave the keyboard again! Be adventurous this week and start learning some of your favorite Ribbon functions. Stay tuned for next week's time saving (or awesome) Excel tip!

The Power of the Keyboard Shortcuts

If you wanted to quickly insert a row, how would you go about doing that? If you are like most Excel users, you will use your mouse to click on the row, right click, and then select "Insert Row". What if you wanted to quickly select all items in a column with 203,453 rows? Would you use your mouse to select and scroll allllllllllll the way down?

STOP THE MADNESS!! Never (at least rarely) use your mouse again! This will not only save you precious time but will also amaze your co-workers as they watch you seamlessly navigate your spreadsheet. Using keyboard short cuts will make you look like a competitive ballroom dancer as you glide through Excel. You will make your mouse-using co-workers look like a 16 year old boy at prom.

Here is a great list that summarizes some of the most useful shortcuts. At a minimum, you should learn the following:

• Select entire row = Shift + Spacebar
• Select entire column = CTRL + Spacebar
• Add row (after selection = CTRL + ALT + "+"
• Delete row (after selection) = CTRL + "-"
• Scroll to bottom of list = CTRL + Down Arrow
• Select all items in a list = CTRL + Shift + Down Arrow 

Once you learn those, try embarking on new and exciting keyboard shortcut adventures. For those who are looking for an African Safari, try learning to navigate the Ribbon using the Alt key (stay tuned for a post on this later).

Using keyboard shortcuts will take some time to learn. At first it will seem like it is taking longer than using the trusted mouse, but once you learn them, you will be shocked by how much time they save. Stay strong!

Welcome to Boston Excel Consulting's blog

Stay tuned for weekly updates on Excel tips and tricks that are sure to impress all of your friends!