If you have a big amount of data on Excel and you need to put it in another format, there are many ways to do it, but none of them are simple and easy as the VLOOKUP function that Microsoft added to its latest version of the software, Excel 2013. However, this powerful but simple solution needs to be reversed and transposed in order to work properly. In this article we will show you how to reverse and transpose Excel data with this powerful but simple solution.
Why Use Power Query?
- Power Query is a free Excel add-in that can be used to save time when dealing with data in Excel.
- Power Query can be used to quickly reverse and transpose data in Excel with just a few clicks.
- This powerful but simple solution can be used to quickly change the order of your data, or to create new columns and rows from existing data.
- Power Query is easy to use and can save you time when working with large amounts of data in Excel.
- To use Power Query, simply select the data you want to reverse or transpose, then click the Power Query button on the ribbon. 6. In the next window, select Transform and choose Reorder Columns or Reorder Rows depending on what you need to do with your data.
- Finally, click Finish, and voila! Your data will now be reversed or transposed as needed!
Example of Changing a Date Column
If you have a date column in your Excel data that you need to reverse, you can use a simple formula to do so. In the cell where you want the reversed date to appear, type =REVERSE(cell reference). For example, if your date is in cell A1, you would type =REVERSE(A1).
This will reverse the order of the date so that it appears as mm/dd/yyyy instead of yyyy/mm/dd. You can also transpose the column by typing TRANSPOSE() in the same cell. You may need to copy this command down so that it applies across all cells with dates in them. However, there are a few other ways to make these changes without relying on formulas.
If you only need to reverse or transpose one row at a time, select the row or rows first before selecting Edit > Select All > Sort Columns > Choose which columns to sort on from the Sort dialog box (under Arrange By) then choose Descending from Columns heading under Order By tab. Next click OK and finally Edit > Select All > Copy to move these changed rows into another area on your spreadsheet.
Another option is turning off gridlines by going into Page Layout tab under View and choosing Gridlines from the drop-down menu.
Check None and hit Apply to apply this change across all of your sheets. To transpose a column, go into Insert tab under Tables and click Table. Drag this table down to the desired location and make sure that it lines up with the rest of your data.
When you right-click on an empty space inside this table, click Table Properties > Cell Styles > Apply Style Across Selection -No Headers And no First Row Title Cells From Cell Styles Menu, scroll down until you see Formatting options then choose different colors for alternating rows in both First Row Titles And Header Rows along with Second Row Titles And Header Rows (if applicable). Finally press Ok. Now every alternate row will be formatted differently than the others while still maintaining column orientation.
Notes on Reversing Columns
If you have a lot of data in your Excel spreadsheet, you may want to reverse the order of the columns. This can be helpful if you want to make sure the data is displayed in chronological order. To do this, simply select the column that you want to reverse, then click on the Data tab and select Sort. Then, choose whether you want to sort by ascending or descending order. Finally, click on the OK button. You should now see the reversed column order!
You may also want to transpose your Excel data from rows to columns. Doing so will allow you to view different aspects of your data at once. For example, when looking at an organizational chart with names in rows, it’s hard to tell who reports up or down from one another unless they are highlighted as such. Transposing them would enable easier identification and visual distinction of where people fall within an organization.
To do this, first select all the cells that you would like transposed (i.e., A1-A10). Next, go back into Sort under Data and choose either Sort A – Z or Sort Z – A. Click on OK when finished.
Notes on Transposing Rows into Columns
To transpose means to take the horizontal row data and switch it so that it becomes vertical column data. This can be useful when you want to make a quick comparison or sort your data in a different way. To do this, select the cells you want to transpose, then click Copy. Next, go to the cell where you want the transposed data to start, and click Paste Special.
In the Paste Special dialog box, click Transpose, then click OK. Your data will now be reversed! We hope these simple solutions help. If you have any questions about Excel or would like some Excel training, please contact us at (707) 332-3881. We hope these simple solutions help. If you have any questions about Excel or would like some Excel training, please contact us at (707) 332-3881. We hope these simple solutions help. If you have any questions about Excel or would like some Excel training, please contact us at (707) 332-3881.