Microsoft Excel is a nice spreadsheet maker software program. You should utilize it to handle knowledge. When it comes to sorting knowledge, you’ll be able to type it in growing or reducing order through the use of the Kind characteristic. There are some cases when the Kind characteristic fails. For instance, if you would like to reverse a list of candidates in Excel in reverse order, you can not accomplish that through the use of the Kind characteristic. Should you accomplish that, it can organize your complete knowledge in alphabetical order (from Z to A or from A to Z). On this case, you should use different strategies to organize a list in Excel in reverse order. In his article, we are going to present you all how to reverse a list in Excel.

How to reverse a list in Excel

You may reverse a list in Excel through the use of:

A quantity list
The INDEX operate
A Macro

Let’s see all these strategies in element.

1] How to reverse a list in Excel through the use of a quantity list

That is the best technique to reverse a list in Excel. You simply have to create a new column containing the numbers in ascending order and use this column to reverse the list. Let’s see how to do this.

We have now created pattern knowledge and crammed every row with a totally different coloration in order that we might see if the info in the rows stays the identical after reversing the columns. You may see the pattern knowledge that we have now created in Excel in the above screenshot.

Now, create a new quantity list in the following adjoining column (see the above screenshot). You may have to use this quantity list to reverse the list in Excel. Excel will deal with this quantity list as a reference to show the specified output.

To take action, observe the steps written under:

Choose your complete vary of knowledge together with the header.
Choose the Knowledge tab.
Click on Kind.
The Kind dialog field will seem. Within the Kind by drop-down, choose the title of the header (in our case, it’s Quantity List).
Within the Kind On drop-down, choose Values.
Choose Largest to Smallest in the Order drop-down.
Click on OK.

After performing the above steps, the list might be reversed. That is how one can reverse a list in Microsoft Excel by creating a reference quantity list.

Learn: List of greatest free Excel Add-ins you have to be utilizing.

2] How to reverse a list in Excel through the use of the INDEX operate

Let’s see how one can reverse a list in Excel through the use of the INDEX operate. Right here, we have now a pattern list containing the names of the individuals and we wish to reverse this list.

The format of the method to reverse a list in Excel through the use of the INDEX operate is as follows:

=INDEX(knowledge vary,ROWS(rows vary))

Let’s see how to use the above method. First, choose the column in which you need to show the consequence. Now, copy the header to a cell of that column. We have now chosen column C and typed the method to C2.

In our case, we wish to reverse the list containing the names in the cell vary A2 to A11. Therefore, the method is:

=INDEX($A$2:$A$11,ROWS(A2:$A$11))

Within the above method, INDEX($A$2:$A$11) represents the vary of the cells containing knowledge to be reversed. The $ signal is used to lock a explicit cell in Excel. Right here, we have now locked your complete vary of knowledge. Now, to reverse the list, we have now to present a reference cell to Excel. For this, we have now used the ROWS(A2:$A$11) operate. On this operate, we have now locked the A11 cell. Now, Excel will use this cell as a reference cell and deal with it as the primary cell when it comes to reversing the list. So, in the above method, solely the worth with out the $ signal will change and the remainder of the values will stay locked.

In your case, the handle of the cells may differ from ours. Therefore, you’ve gotten to use the method accordingly. After typing the above method, press Enter. After that, Excel will show the final title in the focused cell. As you place your cursor on the underside proper aspect of the cell, the white cross will flip into a Plus signal. Now, press and maintain the left click on of your mouse and drag it to the underside. This motion will copy the method to the opposite cells.

Above, we have now seen how to reverse a single list in Excel utilizing the INDEX operate. What if you would like to reverse a list having knowledge in a number of columns? On this case, you’ve gotten to use yet one more operate, COLUMNS. Let’s see how to do this. We have now created pattern knowledge with three columns.

First, choose the columns in which you need to show the consequence. In our case, we have now chosen columns E, F, and G to show the consequence. Now, copy the header to the primary row of the E, F, and G columns. Choose the primary cell (after the header) of the primary column and sort the method. The method to reverse a list with knowledge in a number of columns in Excel through the use of the INDEX operate is as follows:

=INDEX($A$2:$C$11,ROWS(A2:$A$11),COLUMNS($A$2:A2))

Within the above method:

INDEX($A$2:$C$11) represents your complete vary of knowledge and the $ signal represents the locked cell. Right here, we have now locked your complete vary of cells containing knowledge.
ROWS(A2:$A$11) represents the info in the rows from A2 to A11. Right here, row A11 is locked as will probably be handled as a reference row by Excel. After we copy this method to different cells, solely row A2 will change as a result of it’s not locked.
Within the COLUMNS($A$2:A2) operate, $A$2 is used as a reference cell. A2 represents column A2. After we copy the method to the opposite columns, solely the unlocked columns will change.

As described earlier, you needn’t copy the precise method and paste it into Excel. Should you accomplish that, you could obtain an error as a result of your knowledge isn’t the identical as ours. Therefore, kind the method in accordance to your knowledge. After typing the method, press Enter and Excel will show the consequence in the focused cell. To show the consequence, observe any one of many following strategies:

First, copy the method to all of the cells in the primary column. After that, copy the formulae from every cell of the primary column to the opposite two columns.
First, copy the method to all of the cells of the primary row. After that, copy the formulae from every cell of the primary row to the remaining rows.

Let’s see how to do this. Right here, we’re utilizing the primary technique.

Place your cursor on the underside proper aspect of the primary cell. Your cursor will flip from the white cross into a Plus icon. Now, press and maintain the left click on of your mouse and drag it in direction of the underside. This motion will copy the method to all of the cells of the primary column.

The list in the primary column has been reversed. Now, you’ve gotten to copy the method in all of the cells of the primary column to the opposite columns to reverse the list in respective columns. For this, place your cursor as proven in the above screenshot and drag it in direction of the suitable.

That’s it. All of the lists in the three columns have been reversed. It’s also possible to reverse a list in Excel by creating a Macro. We have now described this technique under.

Learn: How to get real-time Foreign money Change Charges in Excel sheet.

3] How to reverse a list in Excel through the use of a Macro

In Excel, you’ll be able to create a Macro to run an motion or a set of actions as many instances as you need. Should you use this technique, you needn’t kind the method each time you create a new list in a spreadsheet. Merely, choose the vary of knowledge and run the Macro. After that, Excel will robotically reverse the list. You may run this Macro to reverse a list with knowledge in a single column and in a number of columns.

First, you’ve gotten to allow the Developer tab in Excel. After that, choose the Developer tab and click on Macros. A brand new window will open. Write the title of your Macro and click on Create. In our case, we have now given the title ReverseList to Macro. This motion will open Microsoft Visible Fundamental. Within the Visible Fundamental Window, you will notice the next textual content:

Sub ReverseList()
Finish Sub

The ReverseList is the title of the Macro in the above textual content.

Now, copy the code written under and paste it into the Visible Fundamental window.

Dim firstRowNum, lastRowNum, thisRowNum, lowerRowNum, size, depend As Integer
Dim showStr As String
Dim thisCell, lowerCell As Vary
With Choice
firstRowNum = .Cells(1).Row
lastRowNum = .Cells(.Cells.depend).Row
Finish With
showStr = "Going to reverse rows " & firstRowNum & " through " & lastRowNum
MsgBox showStr

showStr = ""
depend = 0
size = (lastRowNum - firstRowNum) / 2
For thisRowNum = firstRowNum To firstRowNum + size Step 1
depend = depend + 1
lowerRowNum = (lastRowNum - depend) + 1
Set thisCell = Cells(thisRowNum, 1)
If thisRowNum <> lowerRowNum Then
thisCell.Choose
ActiveCell.EntireRow.Reduce
Cells(lowerRowNum, 1).EntireRow.Choose
Choice.Insert
ActiveCell.EntireRow.Reduce
Cells(thisRowNum, 1).Choose
Choice.Insert
Finish If
showStr = showStr & "Row " & thisRowNum & " swapped with " & lowerRowNum & vbNewLine
Subsequent
MsgBox showStr

You may have to copy the above code between Sub and Finish Sub in Visible Fundamental (see the above screenshot). Click on on the Save button to save this Macro and shut the Visible Fundamental window.

Now, choose your complete vary of knowledge that you really want to reverse, then go to “Developer > Macros.” Choose the Macro ReverseList (the title could also be totally different in your case) and click on Run. Within the affirmation immediate, click on OK. After that, Excel will reverse the list.

Hope this helps.

Learn: How to Lock and Defend Cells in Excel from enhancing.

How do you modify the course of a list in Excel?

You may change the course of a list in Excel by creating a reference quantity list after which sorting it in reducing order, through the use of the INDEX operate, and by creating a Macro. Out of those three strategies, the primary one is the best technique to reverse a list or change the course of a list in Excel.

How do I reverse rows and columns in Excel?

You may reverse rows and columns in Excel by creating a reference quantity list. You may have to create this reference list individually for each rows and columns to reverse them in Excel. We have now defined how to use the quantity list to reverse a list in Excel above in this text.

Learn subsequent: Microsoft Excel On-line suggestions and tips to make it easier to get began.

Source link

How to reverse a list in Excel