How to select specific rows and columns using the CHOOSEROWS and CHOOSECOLS Functions

Nov 10, 2022 | Excel Hints and Tips

These functions can be used to select specific rows or columns from the data or even to change the order of the data.  This is far more efficient than if you have been manually copy and pasting row by row or column by column in the past.  You are also less likely to make any errors too. 

These are 2 more of the functions that have been added to Microsoft Excel recently.  They are currently available from the Microsoft 365 subscription only and if they are not with you yet then they should be with you shortly. 

CHOOSEROWS 

The CHOOSEROWS function selects the data from specific rows. 

Structure 

=CHOOSEROWS(array, row number1,[row number2], ……….) 

The array is the range of data.  The row numbers are the row numbers of the rows to be extracted from the array.  There must be at least 1 row number but can many more. 

Example 

Choose Rows

In this example rows 1, 5 and 2 are selected from the data. 

CHOOSECOLS 

The CHOOSECOLS function selects the data from specific columns. 

Structure 

=CHOOSECOLS(array, row number1,[row number2], ……….) 

The array is the range of data.  The column numbers are the column numbers of the columns to be extracted from the array.  There must be at least 1 column number but can many more. 

Example 

Choose cols

In this example columns 1, 5 and 2 are selected from the data. 

Points to note when using CHOOSEROWS or CHOOSECOLS Functions 

The calculation is only entered in the top left cell.  All the other cells populate automatically. 

  • The output is dynamic so if the data in the table changes the summary table automatically updates too 
  • If the range of the data is likely to change then using Excel Tables will save having to adjust the range of the data every time it alters. 

Further Reading

If you’ve enjoyed reading how to use CHOOSEROWS or CHOOSECOLS Functions in Microsoft Excel, there’s some other blogs below that you might find useful:

Want to learn more about Microsoft Excel? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Microsoft Excel Courses I run.