How to Copy in Reverse Order in Excel

How-to basics · Updated November 20, 2026

To copy a list in reverse order in Excel, enter an INDEX formula beside the source list, fill it down, then copy the results and paste them as values. For example, if the source is A2:A10, enter =INDEX($A$2:$A$10,ROWS($A$2:$A$10)-ROWS($B$2:B2)+1) in B2 and fill down to B10. The first result will be the value from A10, and the last will be the value from A2. This makes a reversed copy while leaving the original list untouched.

What You’ll Need

  • A Microsoft Excel workbook. The formula method below uses INDEX and ROWS, which are available in Excel for Microsoft 365 on Windows and in earlier desktop versions.
  • A source list in one column, or a copied table if you need to reverse whole records rather than one column.
  • An empty destination column or range for the reversed results. Leave room for the full output so you do not overwrite existing data.

Save the workbook before making larger changes. If your list contains a header, keep it separate from the data when using the formula method. For a multi-column table, use the helper-column method later in the steps so each row’s related values stay together.

Step-by-Step

  1. Identify the exact source range. In this example, the list is in A2:A10, with no header included. Click the first and last cells to confirm the range and note how many data rows it contains. If your list has a header in A1, do not include that header in the formula’s source range.
  2. Choose a blank destination column. The example uses B2:B10. Make sure all nine destination cells are empty. Keeping the output next to the source makes it easy to compare, but any clear range on the same or another sheet will work.
  3. Enter the reverse-order formula. With the source list in A2:A10 and the first result cell in B2, enter =INDEX($A$2:$A$10,ROWS($A$2:$A$10)-ROWS($B$2:B2)+1) in B2, then press Enter. The formula uses the size of the source list and the current output position to select the source values from bottom to top. B2 should display the value from A10.
  4. Fill the formula down. Select B2, point to the small square at the bottom-right corner of the cell, and drag the fill handle down to B10. You can also copy B2, select B3:B10, and paste. The formula’s source range stays fixed because of the dollar signs; its output-row reference changes as it fills. The final result in B10 should match A2.
  5. Check the order before copying. Compare the first and last results with the source: B2 should equal A10, B10 should equal A2, and the values between them should run in reverse sequence. If the list has repeated values, check a few middle positions as well; identical entries can make the reversal less obvious.
  6. Copy the results as a separate, fixed list. Select B2:B10 and copy. Click the destination cell, then choose Home > Paste > Paste Values. This replaces the formulas with their displayed results. On Windows, you can also use Ctrl+C, then Ctrl+Alt+V to open Paste Special, choose Values, and press Enter. On Mac, use Command+C and choose Home > Paste > Paste Values; keyboard shortcuts for Paste Special can vary with the Excel version and keyboard settings.
  7. Confirm that the copied list is independent. Click a pasted cell and look at the formula bar. A fixed copy should show the value rather than an INDEX formula. If you want the output to update when the original list changes, keep the formulas instead of pasting values.
  8. Reverse a multi-column table without separating records. First copy the entire table, including all columns, to an empty area. Add a helper column beside the copied data, label its header Order, and enter 1, 2, 3, and so on beside the data rows. Select the copied table and helper column, then choose Data > Sort. In the Sort dialog, check My data has headers, select Order under Sort by, and choose Largest to Smallest. The copied rows will be reversed while the values in each row remain together. Delete the helper column afterward if you do not need it. Do not sort only one column of a table, or names, dates, and other related values can become mismatched.

For a quick example, suppose A2:A4 contains Red, Green, and Blue, in that order. The formula results in B2:B4 will be Blue, Green, and Red. Pasting those results as values gives you a standalone reversed list to use elsewhere in the workbook or in another file.

Common Mistakes

  • Including a header in the source range. If A1 says Color and the data is A2:A10, use A2:A10 in the formula. Including A1 reverses the header along with the entries, which can put a label in the data area.
  • Leaving the output reference unchanged. The formula starts with ROWS($B$2:B2). When filled down, the second reference must be able to change to B3, B4, and so on. If you change it to ROWS($B$2:$B$2), every row may return the same position.
  • Copying formulas when you need fixed values. Formula results can change if you edit or remove the source list. Use Paste Values when you need the reversed copy to stay as it is.
  • Sorting only one column of a table. Reversing just the names or dates can break the relationship between columns. Copy and sort the entire table as a range, including the helper column.
  • Sorting the original by mistake. The helper-column approach changes the order of the range you sort. Make a separate copy first if the original order must be preserved.
  • Overwriting cells in the destination. Check that the full output range is empty before filling or pasting. A paste can replace existing content without giving you a clear warning.

Tips

  • Keep the source range absolute. The dollar signs in $A$2:$A$10 lock the source while the formula fills down. If you use a different source range, update both endpoints in the formula.
  • Use the fill handle carefully. Double-clicking the fill handle may fill down automatically if there is a neighboring continuous column. Dragging to the exact last row gives you more control, especially when the adjacent column has gaps.
  • Keep a header above the output. If the source has a header, type a matching header in B1 and fill the formula only through the data rows. That keeps the new list ready for filters or a table.
  • Use Paste Values for a portable result. A pasted-values copy will still work if you move it to another sheet or workbook, because it does not rely on the original source range.
  • Use the sort approach for complete records. For a table with several columns, the helper column is generally the clearest way to reverse row order. It avoids building a separate formula for every column and keeps each record intact.
  • Make a copy before sorting. If preserving the original matters, duplicate the sheet or copy the table to a blank range before sorting. Sorting can be undone immediately with Ctrl+Z on Windows or Command+Z on Mac, but a saved copy is safer.

Troubleshooting

  • Every result repeats the same value. Check the formula in the second output row. If its expanding reference did not change from B2 to B3, fill from the original B2 formula again. Also make sure the second reference in ROWS is not locked at both ends.
  • The result is #REF! or an unexpected blank. Confirm that the source range still exists and that the formula uses the correct first and last source cells. A blank in the source will produce a blank result when that position is reached. If the original data is on another worksheet, select the source range while editing the formula or include the sheet name in the reference.
  • The list is reversed but the header moved too. Exclude the header from the formula range, or, in the table method, make sure the header row is recognized. In the Sort dialog, check My data has headers so Excel does not treat the header as an ordinary record.
  • The table’s columns no longer match. Undo the sort immediately, then select the full table and helper column and sort again. Excel may show an Expand the selection prompt if only one column is selected; choose Expand the selection to keep row data together.
  • Paste Values is unavailable or the output changes later. Make sure you have copied the formula results before opening the paste options. If you pasted normally, select the copied cells again and use Home > Paste > Paste Values. If you actually want a live linked output, do not convert the formulas to values.
  • The source list changes size. If rows are added or removed, update the endpoints of the formula and extend or reduce the output range to match. The example is deliberately written for A2:A10; it will not automatically include new rows outside that range.

Frequently Asked Questions

Can I reverse a list without changing the original?
Yes. Put the formula results in an empty range and, if needed, paste them as values. The source stays in its existing order unless you choose to sort it directly.

Does the formula work with text, numbers, and dates?
Yes. INDEX returns the value from the referenced source cell, so text, numbers, dates, and most other cell values can be returned in reverse order. Formatting may need to be applied separately if the destination does not inherit the appearance you want.

How do I reverse a row that runs from left to right?
Use the same idea with column positions instead of row positions. For a source in A1:J1 and a first output cell in A2, enter =INDEX($A$1:$J$1,1,COLUMNS($A$1:$J$1)-COLUMNS($A$1:A1)+1) in A2 and fill right through J2. The output starts with the value from J1 and ends with the value from A1.

Can I reverse a table with several columns using one formula?
You can build formula outputs for a multi-column range, but for a straightforward copy the helper-column sort is easier to inspect. It reverses whole rows together and works without writing a separate formula for each column.

Will the reversed copy update when I edit the source?
Yes, if you leave the INDEX formulas in place. No, if you paste values; that creates a fixed snapshot. Choose based on whether you need a live result or an independent copy.

Can I use this in Excel for the web or on Mac?
The INDEX formula and fill steps work in current Excel for the web and Excel for Mac. Ribbon labels and keyboard shortcuts can differ slightly, so use the Paste Values command in the Home tab if a shortcut does not match your setup.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.