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!

Named Ranges

range1

Have you ever looked at a formula and thought, “What in the world does this mean! What items make up this crazy formula?” Or have you ever found your self constantly linking back to a cell and thought, there has to be a better way? If so, you are not alone. That is why named ranges exist! After you use named ranges, you’ll wonder how you ever got through life before.

Microsoft sums up perfectly why you should use named ranges: they make your “formulas much easier to understand and maintain”. Here is how they work…

By default, each cell has a name that corresponds with the column letter and the cell number. To see the cell’s name, look at the top left hand corner of your spreadsheet.

ranges2

You can change the name of that particular cell to any name you’d like (although you should be somewhat descriptive). In this case, I’ll rename the cell “Age”. To change the name, click in the area where the name appears, type the name and hit Enter.ranges3

Now whenever you refer to that cell, you will see “Age” appear in the formula bar instead of the cell reference.

ranges4

This also means that you can easily refer back to the cell by typing simply typing the name of range. No more going back and forth between spreadsheets to refer to cells!

Highlight Every Other Row

highlight1In Excel, just as in life, sometimes you want to set your data apart by highlighting every other row a certain color. You can do it manually, but that would be silly. Keep reading to see how you can perform this task in less than 15 seconds.

Let’s say you have the following set of data.
highlight 2

Highlight the area where you want to apply the formatting (A2:C9) and go to Conditional Formatting on the Home tab and select New Rule.

highlight3

Under “Select a Rule Type:” click “Use a formula to determine which cells to format”. Type in the following formula: =MOD(ROW(),2)=1

highlight4

Click the Format button, go to the Fill tab, choose your color you want to use as the highlight and click Ok

highlight5

Next hit Ok on the New Format menu and BOOM! You have mastered the art of highlighting every other row. Now, go show this to your kids and see how amazing they think you are.

highlight6