RSS

How do I flip a column in Excel 2007, 2010 and 2013?

25 May

In this article, I’m going to explain how to flip a column in Excel 2007, 2010 or 2013 (using screen shots from Excel 2013). Flip a column is what I searched for – you might have asked how to reverse a column or put a column in reverse order.

Note: I was a bit surprised by the solution to this one, but I’ve asked the experts and it is the only way to do this. If you have a better and simpler way (that doesn’t involve macros or coding) please pop a comment on this article or contact me!

Why would I want to put a column in reverse order?

I’m doing my accounts at the moment. I have a spreadsheet of my bank transactions which runs from the newest at the top to the oldest at the bottom. My list of invoices runs from the oldest at the top to the newest at the bottom. If I want to compare them, I want them to both be the same way around, but I need the bank transactions to be in exactly the same order, just the other way around.

Will using Data > Sort flip my column?

The usual way to change the order of columns in Excel is to use the Data > Sort function. However, if you sort by transaction date, it won’t necessarily sort it into the same order the other way around.

For example, we have a set of bank transactions which I’ve named in alphabetical order down the client column to make it easier to see what happens next:

1 spreadsheet

If I highlight all of the columns, go to the Data tab and choose to sort by date, older to newer, I get this result, which is NOT in exact reverse order:

1a spreadsheet sorted by date

So, what’s the solution? I was a bit surprised when I searched and searched for the answer, but it is the only way to do it …

So, how do I flip my columns so they’re in exact reverse order?

To do this, we need to create an extra column to sort by, and then reverse sort by that rather than date or any other column.

First, create a new column called sort and fill it with numbers from 1 to whatever your total number of rows is:

2 sort column

Expert tip: rather than typing these numbers manually, if you have a lot of rows, and to avoid errors, you can create a quick formula to insert the numbers automatically. Type 1 in the first row then the formula you can see below in the next row, where you get the F2 by clicking on the cell containing the 1:

2a sort column

Then, copy the cell with the formula (right-click, copy), highlight the rest of that column down to the last row and right-click, paste. This will give you the same effect. Note, though, when you sort by this column, the numbers will turn into rows of #####, BUT the sort will still work OK.

Once you have your additional Sort column, you are ready to reverse your columns.

Highlight all of the data and, in the Data Tab, choose Sort:

4 sort data

In the Sort Dialog Box, choose to sort by the Sort column, and from Largest to Smallest (i.e. the reverse order to its current order):

5 sort data

Press OK and hooray – your spreadsheet is sorted into exact reverse order. Just delete the now-redundant Sort column (highlight, right-click, delete):

7 delete sort column

and here’s your bank transactions in reverse order – you have flipped the column!

6 data sorted

This article has explained the (slightly surprising) way to flip the columns in an excel 2007, 2010 or 2013 spreadsheet, not using data sort, but another message. If you need to reverse the order of your columns exactly, then this is the way to do it.

If you’ve found this article helpful or if you have a better solution, please do post a comment below, and if you think others would find it useful, please share it using the sharing buttons below the article. Thank you!

Other useful posts on Excel on this blog:

How to view two workbooks side by side in Excel 2007 and 2010

How to view two pages of a workbook at the same time

How do I print the column headings on every sheet in Excel?

How to print the column and row numbers/ letters and gridlines

Freezing rows and columns in Excel – and freezing both at the same time

 
6 Comments

Posted by on May 25, 2016 in Excel, Short cuts

 

Tags: , ,

6 responses to “How do I flip a column in Excel 2007, 2010 and 2013?

  1. Sandy Millin

    May 27, 2016 at 6:25 am

    Hi Liz,
    Here are a couple of things which might make this process easier, though I’m not sure if I completely understand what you wanted to do.

    An alternative to using a formula in the ‘sort’ column:
    Use the ‘fill handle’. For example, if you put 1 in the first cell, 2 in the second cell, select them both, then click the little square in bottom right-hand corner and drag it down, it will automatically continue with 3, 4, 5, etc. You can do this with any increment, so typing 3, 6, then using the fill handle will continue it with 9, 12 etc. It works with any kind of numbered sequence – dates, times, etc. To repeat the same data in a series of cells, type e.g. 2, 2, in the first two cells, then use the fill handle. 2 should appear in all of the cells.
    For more, see here: https://support.office.com/en-us/article/Fill-data-automatically-in-worksheet-cells-74e31bdd-d993-45da-aa82-35a236c5b5db

    My biggest Excel discovery of this year has been the ‘format as table’ function. Here’s an introduction to how to do it: http://www.dummies.com/how-to/content/how-to-apply-a-table-style-to-an-excel-2010-table.html This automatically creates headings with drop-down buttons that allow you to sort by any column, as well as filter to only show you certain parts of the document. I can’t work out what else to Google to get more useful guides to it, so if you want a Skype chat at some point I can talk you through it 🙂

    Hope that helps!
    Sandy

    Like

     
    • Liz Dexter

      May 27, 2016 at 6:41 am

      Thank you for thinking so much about this! I probably should have offered Fill rather than the formula for populating the numbers, I didn’t want to put too much detail there (but that’s an idea for another post!). The table thing s good and thank you for mentioning it. Unfortunately, it wouldn’t solve this particular problem as if you look at the first two images, sorting doesn’t keep the rows in the exact same order, just reversed. Which sometimes you need to do. When that doesn’t matter, it’s fine and very useful, though.

      Like

       
  2. Sandy Millin

    May 27, 2016 at 6:26 am

    Looking back again at what you wanted to do, the other option is to sort the date column by Z>A instead of A>Z. Does that solve the problem?

    Like

     
    • Liz Dexter

      May 27, 2016 at 6:33 am

      No, that doesn’t work, see the very first example in the article. It doesn’t sort the rows in the exact same order in reverse like the numbers method does, and while sort is fine for most things, sometimes it needs to be more refined than that (sorting by column x then column y doesn’t help when the order is based on the time the payments came in and that’s not available as data!) Hope that makes sense and thank you for thinking hard about this!

      Like

       
  3. Aisha

    February 7, 2017 at 4:18 pm

    This article tells you how to flip rows NOT columns. To flip columns would mean you need you headings (Date, Type, Client, In, Out) to be presented the other way around (Out, In, Client, Type, Date). It might be best to change the name of the article.

    Like

     
    • Liz Dexter

      February 7, 2017 at 4:21 pm

      Thank you for your comment. However, as it makes it clear in the article, I am explaining how to flip the contents of a column so it reads in the opposite direction down the column. For example, the first sub-heading reads “Why would I want to put a column in reverse order?”

      Like

       

I love hearing from my readers - do please leave a comment!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: