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!