Forum Discussion

adstristar's avatar
adstristar
Brass Contributor
Jun 02, 2023

Automatically moving rows in office 365 excel based on rank or largest to smallest of a said column

Hi everyone, I have a table on office 365 Excel and what I am trying to do is to have the rows automatically move up or down in order of rank or a said column with values. So if I have 10 rows in t...
office 365
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Jun 02, 2023

adstristar 

To automatically move rows in Excel 365 based on the rank or values in a specific column, you can use a combination of the RANK function and the SORT function.

Here is how you can do it:

  1. First, ensure that your table has headers and is formatted as an Excel table. Select the entire table range, including the headers.
  2. Go to the "Formulas" tab in the Excel ribbon and click on "Define Name" (or "Name Manager" in some versions).
  3. In the "Define Name" dialog box, give a name to your table range, e.g., "MyTable". Click "OK" to save the name.
  4. Create a new column next to your existing table, where you want to display the ranking. In your example, this would be column 8.
  5. In the first cell of the new column, enter the following formula and adjust it based on your column references:

=RANK([@YourColumn],$YourTable[YourColumn],0)

Replace "YourColumn" with the actual column header in your table and "YourTable" with the name you defined in step 3. The "0" as the third argument in the RANK function indicates ranking in descending order (largest to smallest).

  1. Drag the formula down to populate the remaining cells in the column with the rankings.
  2. Now, go to a different location in your worksheet where you want the sorted table to appear. This can be a separate sheet or a different range on the same sheet.
  3. In the first cell of the new location, enter the following formula:

=SORT(YourTable, YourTable[YourColumn], -1)

Replace "YourTable" with the name you defined in step 3, and "YourColumn" with the actual column header you want to sort by.

  1. Press Enter to apply the formula, and the sorted table will be generated based on the rankings in the specified column.

Now, whenever you update the values in the specified column, the rows will automatically be repositioned based on the ranking.

Note: The SORT function is available in Excel 365 and may not be available in earlier versions of Excel.

The steps were created with the help of AI, all information is untested and without guarantee.

Resources