Excel: Swapping (reversing) the Axis of a Table Data
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)
So After doing this, This is how the Input & output looks:
Conclusion:
In this post, We saw how to swap or reverse the axis of a table data in Excel 2010.
Related articles
How to add secondary axis in a chart in Excel 2010? (parasdoshi.com)
Found something interesting by exploring a “List of companies by revenue” Data Set: (parasdoshi.com)