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)