Reverse a list or range

After completing a list, it is hard to manually reverse all the data. It takes a lot of time and effort. But in excel, we can do it using three commonly used functions. We can use the  INDEX, COUNTA and ROW functions together to reverse a list or string. In this tutorial, we will learn how to reverse a list or string in Excel.

Figure 1: Example of How to Reverse a List or String in Excel

Generic Formula

=INDEX(List,COUNTA(List)+ROW(List) - ROW(),1)

How the Formula Works

Here, the COUNTA function takes the list as an argument and returns the number of non blank items. We add this number  to the first ROW function. It also takes the list as an argument and returns the first row number of the list. The second ROW function returns the current row number where the formula is working.

We subtract this value from the summation of the previous values. All this goes into the second argument of an INDEX function. The first argument is the list itself and the row number is set to 1. The ROW function provides a number that is increasing.

We use this to create a row number that represents the last item of the list and then gets toward the first item. Thus, the last item in the list gets a row number by the first instance. The second to last item gets a row number by the second instance, and so on. This reverses the entire list.

Example

The following example contains a list of Samsung phones in column A.

Figure 2: The List to be Reversed

To reverse it in column B we need to:

• Go to cell B2. Click on it with your mouse to select it.
• Then, we have to assign the formula =INDEX(A2:A7,COUNTA(A2:A7)+ROW(A2:A7)-ROW(),1) to cell B2.
• Lastly, we need to drag the fill handle from cell B2 to B6 to copy the formula to the cells below.

Figure 3: Example of Applying the Formula to the List

Now, the last entry in the list “Samsung Note 7” will now appear in the beginning of the list. This will in turn reverse the entire list.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.