There is a list of numbers in column A in an Excel worksheet. I would like to display these numbers in column C in order.

Column C should update automatically when column A is modified, without needing to manually reorder anything.

  • I dont think this is possible in Excel, at least not without using a Macro. Filtering is always done manually. – Kevin Anthony Oppegaard Rose Jun 13 at 10:20
  • 1
    @KevinAnthonyOppegaardRose It definitely is possible ;-) See my answer. – robinCTS Jun 13 at 10:47
  • 1
    @robinCTS thanks :) Learn something new everyday. Nice solution! – Kevin Anthony Oppegaard Rose Jun 13 at 12:03
  • @KevinAnthonyOppegaardRose If you haven't done so yet, check out the even better solution, courtesy of AFH. – robinCTS Jun 13 at 12:06
up vote 3 down vote accepted

EDIT: Thanks to AFH, the formula can be simplified to just the SMALL() function!


This can be done with a very simple normal formula in a Table:

Worksheet Screenshot

The formula in C2:C8 is:

=SMALL(A:A,ROW()-ROW($1:$1))


A Table is required so that entering a value just below the bottom of the table, i.e. in A9, will cause the formula in column C to be extended downwards.

The Table can be dispensed with if only existing data will be modified, or if you are comfortable extending the formula by hand, or if you are prepared to pre-fill it past the current end of the data.

  • 1
    Nice answer, but I found that =IFERROR(SMALL(A:A,ROW()-1),"") works equally well. What's the advantage of adding MATCH() and INDEX()? And ROW($1:$1) always returns 1, so why not use the literal, which can be incremented if there are more header lines? – AFH Jun 13 at 11:37
  • @AFH Good catch!. There's no need for the INDEX()/MATCH() at all and there's no advantage to having them. (I'm so used to more complex formula where they are required, that I forgot that for this simple case they weren't needed.) The reason for using ROW($1:$1) is precisely so that if more header rows are added (or rows inserted above the Table) after the formula has been entered, the Table won't break and you don't need to adjust a literal value to fix it. It makes the Table pretty much bullet-proof. I've updated the answer with an even simpler version. I hope you don't mind. – robinCTS Jun 13 at 12:01
  • Let us continue this discussion in chat. – robinCTS Jun 13 at 13:58

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.