When trying to flip data, be it a column, row, or table in Excel, you would probably find that there’s no built-in option to do this.

If your data is arranged in alphabetical order, it would of course be easier to just use the *Sort *feature to reverse the order.

However, for an unsorted list, reversing the order is not that simple.

In this tutorial we will show you different ways to flip data in different situations:

- When you want to flip a column
- When you want to flip a row
- When you want to flip a table
- When you want to switch rows and columns

Table of Contents

## How to Flip Columns in Excel

Flipping a column may seem like a one-click task, but if it consists of an unsorted list of items, flipping a column is not just about re-sorting the items.

Let us look at three ways to flip a column in Excel:

- Using a
*Helper*Column - Using a Formula
- Using VBA

To demonstrate the three methods we will use the following list of items:

Using each method, we will show you how to flip the above list of items, so that they are displayed in the reverse order.

### Using a Helper Column to Flip a Column in Excel

The first method requires the use of an additional “*Helper*” column which will contain numbers 1,2,3… in ascending order.

This column will work as a sort of *‘index’ *column, with each number serving as an index for the corresponding list item.

Once this helper column is ready all that we need to do is select both columns and sort the helper column in descending order!

Here are the steps to make this happen:

- If the column next to the one that you want to flip is not empty, then add a blank new column next to it (by right clicking on the column heading and selecting
*‘Insert’*). - Fill this empty column (column B in our case) with numbers. Start by typing 1 in the first cell, 2 in the second cell and then using the fill handle (or
*flash fill*) to create a series of numbers that end at the row corresponding to the last item of the list.

- Select both columns (columns A and B in our case) and navigate to
*Data*->*Sort*from the main menu.

- This will open the
*Sort*dialog box. - Click on the dropdown arrow next to ‘
*Sort by*’ and select the column that you just added and filled with numbers (the*‘Helper’*column in our case).

- Under
*‘Order’*, select ‘*Largest to Smallest*’. This will make sure that the selected columns are sorted by the*Helper*column in descending order.

- Click OK.

You should now get both columns reversed (or flipped).

Once your intended column is flipped, you can go ahead and delete the *Helper *column (column B).

You can use this same technique to flip a whole table.

### Using a Formula to Flip a Column in Excel

If you want to get the job done in fewer steps, you could use formula instead.

The formula to flip a column in Excel involves the use of the INDEX and ROWS functions.

Let us first individually look at what each of these functions does.

#### The INDEX Function

The INDEX function is used to find the value at a given index of a given range. The syntax for the INDEX function is:

INDEX(array,row_num, [column_num])

Here,

*array*is an array or range of cells from which we want to select a value to be returned*row_num*is an integer that specifies the index of the value (within*array*) that we want returned by the function*column_num*is an integer that specifies the column number (within*array*) from which we want to return a value. This parameter is optional.

The INDEX function simply selects the value from within the *array *that falls in the row and column corresponding to *row_num* and *column_num*.

If we omit the *column_num* parameter, then the INDEX function returns the value in the *array *that is at index *row_num*.

For example, the following formula, when applied to our sample data list will return the value at the 5^{th} index of the list (or the 5th item):

=INDEX(A2:A7, 5)

#### The ROWS Function

The ROWS function simply returns the number of rows in a given range. The syntax for the ROWS function is:

ROWS(array)

Here, the *array *is an array or range of cells for which we want to find the number of rows.

For example, if we apply the ROWS formula to our sample data list, it will return the number of rows in this list:

=ROWS(A2:A7)

#### Using INDEX and ROWS Functions to Flip a Column in Excel

To flip the column in our sample data set, we need to follow the steps shown below:

- If the column next to the one that you want to flip is not empty, then add a blank new column next to it (by right clicking on the column heading and selecting
*‘Insert’*). - Now type the formula: =INDEX($A$2:$A$7,ROWS(A2:$A$7)).
- Copy the formula down to the rest of the cells in the column using the fill handle.

You should now get the same list as your original column, but in reverse order.

This method helps you flip the column, while still retaining the original column as it is.

So it’s quite useful when you want to make sure the original list remains untouched.

Once you get the flipped list, you can copy the list and Paste it as the value in place of the original column (column A in our case) if you need to.

**Explanation of the Formula**

The ROWS function performs the main task of working out the indexes.

Notice we specified the starting cell reference for the ROWS function as a *relative reference *(A2), while we specified the ending cell reference as an* absolute reference* ($A$7).

This makes sure that each time the formula is copied, the ending reference stays the same while the starting reference keeps changing.

This means that in the first cell, the formula is ROWS(A2:$A$7), which returns the value 6 (since there are only 6 rows in the range).

This makes the INDEX function return the item at index 6 of our list, which is the last item.

In the second cell, the formula is ROWS(A3:$A$7), which returns the value 5 (since there are only 5 rows in the range).

This makes the INDEX function return the item at index 5 of our list, which is the second to last item.

This goes on till the last cell, the formula is ROWS(A7:$A$7), which returns the value 1. This makes the INDEX function return the first item in the list.

In other words, the ROWS function basically acts like our *Helper *column (from our first method).

It creates a decrementing counter for the INDEX function, which keeps moving from the first to the last item in the list.

### Using VBA to Flip a Column in Excel

The third way to flip a column in Excel is to use VBA code. Here is the code that we will be using to accomplish this:

'Code developed by Steve Scott from SpreadsheetPlanet.com

Sub FlipColumn()

Dim rng As Range

Dim result As Range

Dim start_index As Integer

Dim end_index As Integer

Set rng = Selection

start_index = 1

end_index = Selection.Rows.Count

Do While start_index < end_index

val1 = Selection.Rows(start_index)

val2 = Selection.Rows(end_index)

Selection.Rows(end_index) = val1

Selection.Rows(start_index) = val2

start_index = start_index + 1

end_index = end_index – 1

Loop

End Sub

Copy this code into your Developer window.

To run this code, simply select the column that you want to flip and then run this macro by following the steps shown below:

- From the
*Developer*menu, select*Macros*.

- This opens the
*Macro*dialog box. Select the Macro named*‘FlipColumn’*from the list under ‘*Macro Name*’.

- Click
*Run*.

You should now find your selected column flipped.

**Explanation of the Script**

The above script takes the selected column and first counts the number of cells in it. The code stores the number of cells in a variable called *end_index*.

end_index = Selection.Rows.Count

In the same way, it stores the starting index of the list (which is 1) in a variable called *start_index*.

start_index = 1

Next, the code takes the first and last cells of the list, and interchanges them. It does this by using the *start_index* value to locate the first cell in the list and the *end_index* value to locate the last cell in the list.

val1 = Selection.Rows(start_index)

val2 = Selection.Rows(end_index)

The code repeatedly performs this action, each time increasing the *start_index* and decreasing the *end_index *values, until both become equal.

By doing this, we are shortening the list under consideration at each loop iteration until there are no more items left.

Do While start_index < end_index

val1 = Selection.Rows(start_index)

val2 = Selection.Rows(end_index)

Selection.Rows(end_index) = val1

Selection.Rows(start_index) = val2

start_index = start_index + 1

end_index = end_index - 1

Loop

This is how it gives us a column with all its data reversed.

## How to Flip a Table in Excel

If, instead of a single column, you want to flip a whole table of columns, then all you need to do is extend the above techniques to a *set *of columns.

For example, let’s say you have the following table consisting of two columns that you want to flip.

To flip this table, you can use the first method to flip a column (using the *Helper *column), after you’ve filled the helper column with number indexes, simply select all the columns of your table along with the helper column.

When you sort the selected table by the *Helper *column, Excel might display a *Sort Warning* dialog as shown below:

Simply check the *‘Expand the selection*’ option and then click the *Sort *button. Your entire table should now get flipped.

After this, you can go ahead and delete the *Helper *column.

## How to Flip / Switch Columns and Rows

You might have come across this tutorial looking for a way to flip or in fact switch rows and columns.

In other words, you might want to convert your rows to columns and columns to rows. In that case, you will need to use a special Excel feature that does just that.

Let us see how to apply this feature to the following table and flip its rows and columns:

Excel’s *Transpose *feature lets you transpose an array, table or range of cells. To apply this feature to your data, follow the steps outlined below:

- Make sure you have a blank area of cells where you can place your flipped table.
- Select the table that you want to flip rows and columns for.
- Copy the table by using the keyboard shortcut CTRL+C or right-clicking on your selection and selecting
*Copy*. - Select the top-left corner cell of your blank area of cells.
- Right click on this cell and select
*Paste Special*from the context menu that appears.

- This opens the
*Paste Special*dialog box. Check the box next to*‘Transpose’*.

- Click OK.

You should now get a flipped version of your original table, where the rows and columns are switched.

## How to Flip Rows in Excel

Now let’s look at a case where we want to flip a row in Excel.

Say you want to flip the row shown below:

Flipping a row in Excel is different from flipping a column. This is because, unlike columns, Excel does not provide any feature to sort rows.

So, you will need to perform a few additional steps.

To flip a row in Excel, you need to use a combination of tricks that you learned in this tutorial.

Since Excel does not support the sorting of rows, you will need to first transpose the row into a column (using the *Transpose *feature), and then flip it like any column.

Once done you can transpose the flipped column back into a row.

So the steps to flip a row are as follows:

- Select the row that you want to flip.
- Transpose this row into a column by copying it (pressing CTRL+C), right-clicking and selecting
*Paste Special*from the context menu. Next check the box next to*‘Transpose’*. - Your row should now have turned into a column.

- Next, insert a
*Helper*column containing numbers 1,2, … in sequence.

- Select both columns and navigate to
*Data*->*Sort*. - Select your helper column name from the ‘
*Sort by*’ drop down list. - Select ‘
*Largest to Smallest*’ from the ‘Order’ drop down list. - Click OK.
- Your column data should now be flipped.

- Finally, transpose the column back to a row by repeating step 2.

- Delete the
*Helper*row.

You should now get your row back, but with its data flipped.

In this tutorial, we showed you different ways to **flip data in Excel**.

It might have come across as lengthy, but we tried to include different kinds of situations that you might come across and how to flip your data in each situation.

You can select the method which best serves your requirements.

We hope the tutorial was helpful for you.

**Other articles you may also like:**