Microsoft Excel is a good spreadsheet maker software program. You need to use it to handle knowledge. In relation to sorting knowledge, you may kind it in growing or lowering order through the use of the Kind function. There are some cases when the Kind function fails. For instance, if you wish to reverse an inventory of candidates in Excel in reverse order, you can’t achieve this through the use of the Kind function. In the event you achieve this, it is going to prepare your entire knowledge in alphabetical order (from Z to A or from A to Z). On this case, you need to use different strategies to rearrange an inventory in Excel in reverse order. In his article, we are going to present you all find out how to reverse an inventory in Excel.
Methods to reverse an inventory in Excel
You’ll be able to reverse an inventory in Excel through the use of:
- A quantity record
- The INDEX operate
- A Macro
Let’s see all these strategies intimately.
1] Methods to reverse an inventory in Excel through the use of a quantity record
That is the simplest methodology to reverse an inventory in Excel. You simply should create a brand new column containing the numbers in ascending order and use this column to reverse the record. Let’s see how to do this.
We have now created pattern knowledge and stuffed every row with a unique coloration in order that we may see if the information within the rows stays the identical after reversing the columns. You’ll be able to see the pattern knowledge that we’ve created in Excel within the above screenshot.
Now, create a brand new quantity record within the subsequent adjoining column (see the above screenshot). You need to use this quantity record to reverse the record in Excel. Excel will deal with this quantity record as a reference to show the specified output.
To take action, observe the steps written under:
- Choose your entire vary of information 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 identify of the header (in our case, it’s Quantity Checklist).
- Within the Kind On drop-down, choose Values.
- Choose Largest to Smallest within the Order drop-down.
- Click on OK.
After performing the above steps, the record shall be reversed. That is how one can reverse an inventory in Microsoft Excel by making a reference quantity record.
Learn: Checklist of greatest free Excel Add-ins you have to be utilizing.
2] Methods to reverse an inventory in Excel through the use of the INDEX operate
Let’s see how one can reverse an inventory in Excel through the use of the INDEX operate. Right here, we’ve a pattern record containing the names of the individuals and we wish to reverse this record.
The format of the components to reverse an inventory in Excel through the use of the INDEX operate is as follows:
=INDEX(knowledge vary,ROWS(rows vary))
Let’s see find out how to use the above components. First, choose the column by which you wish to show the end result. Now, copy the header to a cell of that column. We have now chosen column C and typed the components to C2.
In our case, we wish to reverse the record containing the names within the cell vary A2 to A11. Therefore, the components is:
=INDEX($A$2:$A$11,ROWS(A2:$A$11))
Within the above components, INDEX($A$2:$A$11) represents the vary of the cells containing knowledge to be reversed. The $ signal is used to lock a selected cell in Excel. Right here, we’ve locked your entire vary of information. Now, to reverse the record, we’ve to supply a reference cell to Excel. For this, we’ve used the ROWS(A2:$A$11) operate. On this operate, we’ve locked the A11 cell. Now, Excel will use this cell as a reference cell and deal with it as the primary cell relating to reversing the record. So, within the above components, 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 must use the components accordingly. After typing the above components, press Enter. After that, Excel will show the final identify within the focused cell. As you place your cursor on the underside proper aspect of the cell, the white cross will flip right 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 components to the opposite cells.
Above, we’ve seen find out how to reverse a single record in Excel utilizing the INDEX operate. What if you wish to reverse an inventory having knowledge in a number of columns? On this case, you must 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 by which you wish to show the end result. In our case, we’ve chosen columns E, F, and G to show the end result. 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 kind the components. The components to reverse an inventory 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 components:
- INDEX($A$2:$C$11) represents your entire vary of information and the $ signal represents the locked cell. Right here, we’ve locked your entire vary of cells containing knowledge.
- ROWS(A2:$A$11) represents the information within the rows from A2 to A11. Right here, row A11 is locked as it will likely be handled as a reference row by Excel. Once we copy this components to different cells, solely row A2 will change as a result of it isn’t locked.
- Within the COLUMNS($A$2:A2) operate, $A$2 is used as a reference cell. A2 represents column A2. Once we copy the components to the opposite columns, solely the unlocked columns will change.
As described earlier, you needn’t copy the precise components and paste it into Excel. In the event you achieve this, you might obtain an error as a result of your knowledge isn’t the identical as ours. Therefore, kind the components in accordance with your knowledge. After typing the components, press Enter and Excel will show the end result within the focused cell. To show the end result, observe any one of many following strategies:
- First, copy the components to all of the cells within the first column. After that, copy the formulae from every cell of the primary column to the opposite two columns.
- First, copy the components 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 methodology.
Place your cursor on the underside proper aspect of the primary cell. Your cursor will flip from the white cross right 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 components to all of the cells of the primary column.
The record within the first column has been reversed. Now, you must copy the components in all of the cells of the primary column to the opposite columns to reverse the record in respective columns. For this, place your cursor as proven within the above screenshot and drag it in direction of the best.
That’s it. All of the lists within the three columns have been reversed. It’s also possible to reverse an inventory in Excel by making a Macro. We have now described this methodology under.
Learn: Methods to get real-time Foreign money Trade Charges in Excel sheet.
3] Methods to reverse an inventory in Excel through the use of a Macro
In Excel, you may create a Macro to run an motion or a set of actions as many instances as you need. In the event you use this methodology, you needn’t kind the components each time you create a brand new record in a spreadsheet. Merely, choose the vary of information and run the Macro. After that, Excel will routinely reverse the record. You’ll be able to run this Macro to reverse an inventory with knowledge in a single column and in a number of columns.
First, you must allow the Developer tab in Excel. After that, choose the Developer tab and click on Macros. A brand new window will open. Write the identify of your Macro and click on Create. In our case, we’ve given the identify ReverseList to Macro. This motion will open Microsoft Visible Primary. Within the Visible Primary Window, you will notice the next textual content:
Sub ReverseList() Finish Sub
The ReverseList is the identify of the Macro within the above textual content.
Now, copy the code written under and paste it into the Visible Primary window.
Dim firstRowNum, lastRowNum, thisRowNum, lowerRowNum, size, rely As Integer Dim showStr As String Dim thisCell, lowerCell As Vary With Choice firstRowNum = .Cells(1).Row lastRowNum = .Cells(.Cells.rely).Row Finish With showStr = "Going to reverse rows " & firstRowNum & " by " & lastRowNum MsgBox showStr showStr = "" rely = 0 size = (lastRowNum - firstRowNum) / 2 For thisRowNum = firstRowNum To firstRowNum + size Step 1 rely = rely + 1 lowerRowNum = (lastRowNum - rely) + 1 Set thisCell = Cells(thisRowNum, 1) If thisRowNum <> lowerRowNum Then thisCell.Choose ActiveCell.EntireRow.Minimize Cells(lowerRowNum, 1).EntireRow.Choose Choice.Insert ActiveCell.EntireRow.Minimize Cells(thisRowNum, 1).Choose Choice.Insert Finish If showStr = showStr & "Row " & thisRowNum & " swapped with " & lowerRowNum & vbNewLine Subsequent MsgBox showStr
You need to copy the above code between Sub and Finish Sub in Visible Primary (see the above screenshot). Click on on the Save button to avoid wasting this Macro and shut the Visible Primary window.
Now, choose your entire vary of information that you just wish to reverse, then go to “Developer > Macros.” Choose the Macro ReverseList (the identify could also be completely different in your case) and click on Run. Within the affirmation immediate, click on OK. After that, Excel will reverse the record.
Hope this helps.
Learn: Methods to Lock and Shield Cells in Excel from modifying.
How do you modify the route of an inventory in Excel?
You’ll be able to change the route of an inventory in Excel by making a reference quantity record after which sorting it in lowering order, through the use of the INDEX operate, and by making a Macro. Out of those three strategies, the primary one is the simplest methodology to reverse an inventory or change the route of an inventory in Excel.
How do I reverse rows and columns in Excel?
You’ll be able to reverse rows and columns in Excel by making a reference quantity record. You need to create this reference record individually for each rows and columns to reverse them in Excel. We have now defined find out how to use the quantity record to reverse an inventory in Excel above on this article.
Learn subsequent: Microsoft Excel On-line ideas and tips that can assist you get began.
