Share via

How do I rank based off a certain table, then copying and pasting it below automatically?

Anonymous
2025-05-10T09:16:06+00:00

I'm trying to rank based off the "total points". Formula on M4 is =RANK(L4,$L$4:$L$11) and I just paste on the cells below until M11. And it ranks the group perfectly.

But if i were to paste it on the second group, it'd rank the cells based off the points on group 1. So how do I do this automatically? Without having to change the cell referencce one by one for each group later on (I'm planning to make 64 groups). Thanks!

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2025-05-10T10:16:04+00:00

    Do you have Microsoft 365, Office 2024 or Office 2021?

    If so, clear M4:M11, then enter the following formula in M4:

    =RANK(L4:L11,L4:L11)

    The formula result will automatically spill to M4:M11.

    Copy M4, then paste to M15, M26 etc.

    If you have an older version:

    Clear M4:M11.

    With M4:M11 still selected, enter the formula

    =RANK(L4:L11,L4:L11)

    and confirm it by pressing Ctrl+Shift+Enter to turn it into an array formula.

    Copy M4:M11, then paste to M15, M26 etc.

    1 person found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest