How do you transpose a column into a row with a formula in Excel?

It's easy to copy and paste data to turn vertical columns into horizontal rows. But sometimes you may want to automate this task. You can use array formulas to transpose lists of data.  Transposing a range means turning a row into a column, and turning a column into a row.  In other words, it rotates the orientation of the data by plus or minus 90 degrees. You can reverse the order or keep it the same.Here's how to do both:


How to transpose a column Into a row with the same order:

These functions will transpose a column list, CList, into a row.  To keep the data in the original order, create a named range called RList referring to the cells in a row that is to contain the transposed data.  Then use the following array formula: 


=OFFSET(CList,COLUMN()-MIN(COLUMN(RList)),0)

How to transpose a column Into a row with the REVERSE order:

To reverse the order, create the named range "RevRList" then enter this formula:

=OFFSET(CList,MAX(COLUMN(RevRList))-COLUMN(),0) 

Personal Online Finance Tracker Excel Spreadsheet Template

I'm going to try and post more templates seeing how my apartment search spreadsheet has been such a big hit. With economy still in the crapper and questionable job security many Americans, including myself, have turned towards the internet for a source of additional income. How do you make money online? Well, this blog, as well as several other blogs that I have created are a great place to start. Then there are online surveys, selling stock pictures, writing articles, and even getting paid just to receive a couple junk emails a day. So how do I organize all of these methods in order to keep track of my finances and maximize my time? Excel spreadsheets of course!

As you can see, I started this money making endeavor in December of 2009. I've listed across the top row all of my sources of income, followed by the total and a few statistics. Here are the formulas I used:

Total: =SUM(C2:Q2)
Days: =IF(--TEXT(TODAY(),"yyyymm")>S2,DAY(DATE(2010,2,0)),DAY(TODAY()))
Ave/Day: =R2/T2
Ave/Blog: =U2/4

This formulas are pretty straight forward:

Have you ever wondered how much money the ads displayed on this site bring in? Well, now you will know. Google Adsense plus Kontera Ads = $50 over 6 months, or about 15% of my total online revenue.

Survey sites, including MySurvey and Opinion Outpost have netted over $150, almost half of all my online earnings.

Writing articles for WikiNut has been fun but not very profitable- yet. Sometimes it takes time to build up an audience of followers and dedicated readers. I remain optimistic on this one for the future.

You can sell pictures online at sites like Dreamstime to try and make a few bucks.

I recently started reading emails for a few cents a day with sites like Send Earnings and Inbox Dollars. They won't help you get rich quick but if you keep at it you can have a nice, steady growth.

My projected earnings for the year right now are around $700. You won't get rich quick but you can easily make some extra spending money. Every little bit helps in this economy!

How do you assign a letter grade to a number in an Excel spreadsheet?

A frequently asked question is how do you assign a letter grade to a numeric value.  This is actually very simple.  First, you need to create a defined name called "Grades" which refers to the array:

={0,"F";60,"D";70,"C";80,"B";90,"A"}

Next, use the VLOOKUP function to convert the number to the letter grade:

=VLOOKUP(A1,Grades,2)

In this case, A1 is the cell that contains the numeric value.  You can add entries to the Grades array for other grades like C- and C+, just make sure the numeric values in the array are in increasing order.

Also, I am currently planning for my honeymoon so what I have turned to in order to keep my notes and thoughts organized? Excel spreadsheets of course! So stay tuned for my Honeymoon/vacation planning/budgeting Excel spreadsheet.

How to find the most common name in an Excel spreadsheet?

How do you find the most common name or string in a range in an Excel spreadsheet? The following array formula will return the most frequently used entry in a range, where Rng is the range containing the data:
 
=INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng,Rng),0))