From Vertical to Horizontal: Transposing your Lists

vtoh1I 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.

vtoh2

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

vtoh3

vtoh4

As always, join us in the following weeks as we take you on another fun-filled Excel adventure!

Group, Don’t Hide, Your Data

Group1Most Excel users know (for better or worse) how to hide rows and columns. However, many times this is a brutish way to hide or organize your data, especially if you want to see that data again. This is where the power of grouping comes in.

Basically, grouping allows you to quickly hide/unhide/organize your data, especially large sets of data. Let me explain by using an example. Let’s say you have the following financial statement.

group2

On this financial statement, you only want to see Total Revenue, Total Expenses, and Net Income. To group (hide), first select the rows you want to group. In this case, rows 9-12. Go to the Data tab -> Group -> Group. A box will now appear to the left of your column.

group3

If you click on the box with the line through it, it will collapse (hide) your columns. Now repeat this process for rows 14-17. Look how clean that looks! Best of all, now you can easily hide/unhide the rows/data!

group4

Also, note that you can group columns as well. And, if you want to go really crazy, you can nest groups. For instance, you can group columns 9-18 then group columns 9-12 and 14-17 like we did above. To ungroup, simply highlight the rows and go to the Data tab -> Ungroup -> Ungroup. Or to eliminate all of your groups (atomic bomb), click Clear Outline.

group5

group6

There you have it! You have just become a more sophisticated Excel user. And, you now know how to better organize your spreadsheets. Good luck, my fellow Excel nerds!

 

How to Fix Errors: Remove #N/A and other Errors from your Spreadsheet

fixerrors1 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.

Although these errors can be useful to detect possible errors, for the most part they cause spreadsheets 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)

fixerrors2

fixerrors3

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.

Number Formatting Pt. 2: Customizing Your Numbers

Nnumber1ow that your boss is thoroughly impressed with your number formatting skills, let’s blow her 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:

numbers2-1

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:

numbers2-2

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

numbers2-3

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?

numbers2-4

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.

numbers2-5

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.

numbers2-6

numbers2-7

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

number1You 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!):

number2

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.

number3

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

number4

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.

number5

Join us for our next post where we will explore more number formatting options!

 

Set Your Cells Apart: How to Use Conditional Formatting

set1Imagine 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.”

set2

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”.

set3

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.

set4

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!