Everyone looks at data differently. Some people like having their dates (or names of people) at the top of the document. Others like the dates and the names of people to be on the left-hand side. I fall into the latter when it comes to analyzing trends. My goal is to swap the columns at the top of the excel document with the axis of data points on the left-hand side which are my KPI’s. In this blog post, I am going to show how to transpose this data into excel and how to move columns and rows in excel with a click of a button. All of this can be completed in less than 30 seconds. Let’s get started.
Step 1: Copy the Data
Within the document, you want to copy all the data that you want to switch around. As a quick tip, you can use a command that will select all the cells on the page that have data in it. To do this in Windows, you hit the CTRL + A button to select all the data. Once everything is selected, you will see everything highlighted in a light color, so you can do a quick eyeball check to see if everything is copied. If your document is huge, use the CTRL + Down Key and CTRL + Right Key to see if everything is selected.
If you are using a Mac, you can still use the quick commands by replacing the CTRL buttons with the Command button on your keyboard.
Step 2: Paste Special
With the data selected, you want to go to the cell that you eventually want to paste the data in. In this example, I went a few rows down, and I am still using column A. With the data selected, you want to click on the “paste special” section. As a note, I am using Office 365, so my paste section might look different from what you see. Regardless, you want to look for the part that says special and click on that.
Step 3: Transpose Excel Feature
From here, you want to go to the transpose tool section and check that off. What the transpose function does is it acts as a lookup and reference formula that flips the X and Y axis of a document and categorizes the data so. After that is selected, you want to hit OK.
Step 4: Paste and Set up a Filter
With the cell already selected in step 2, you see the data swapped with the rows and columns. With the data now transposed, you set up a filter at the top. You can do this with a quick command with a CTRL + SHIFT + Right Arrow. You should do this so you can view the data and change it based on the settings you need prefer like alphabetical order, or sorting data from high to low. It’s also a good habit to do so you can view all of your data trends without mixing up different KPI’s.
Conclusion
Moving columns and rows in Excel is pretty simple. Another quick tip that you can do to save space moves the text at the top to go up and down instead of left to right. You can do this if you have long titles as I have in the excel document. To do this, you select the entire header and head over to the home tab. From the home tab, you want to look for the text that is in the alignment section. You will see a section that has a letter A and a letter B that is going up at an angle. Click on this section and change the format of the text to see if it works best for your document.