Excel: Swapping (reversing) the Axis of a Table Data

Standard

Data preparation (or call it pre-processing) is an essential and time-consuming part of any data analytic’s project. To that end, I was working on a data set needed some changes before I could plot it on an effective data visualization. Here’s what I did:

My Challenge:

I was working on a data set that looked like this:

Date Abu Dhabi, United Arab Emirates Adalaj, Gujarat, India Addison, TX
1/1/2013 1 4 2
1/2/2013 1 4 2
1/3/2013 1 4 3
1/4/2013 3 3 3
1/5/2013 2 2 4
1/6/2013 2 3 4
1/7/2013 2 3 3
1/8/2013 2 2 4
1/9/2013 2 2 3

BUT: I wanted my data to look like

Date 1/1/2013 1/2/2013 1/3/2013 1/4/2013 1/5/2013 1/6/2013 1/7/2013 1/8/2013 1/9/2013
Abu Dhabi, United Arab Emirates 1 1 1 3 2 2 2 2 2
Adalaj, Gujarat, India 4 4 4 3 2 3 3 2 2
Addison, TX 2 2 3 3 4 4 3 4 3

What did my real data looked liked?

it has 380 columns and 500+ Rows and so MANUAL copy pasting was NOT an option!

Excel 2010 Solution:

It’s so simple!

Step 1: Select the data > COPY (Shortcut: ctrl + c)

Step 2: Switch to a new/different excel sheet

step 3: Paste Special > Transpose (T)

excel paste special transpose swap axis data

So After doing this, This is how the Input & output looks:

excel paste special reverse axis

Conclusion:

In this post, We saw how to swap or reverse the axis of a table data in Excel 2010.

What do you think? Leave a comment below.