Learn MS Office Software Training with Free Lessons & Tips

INDEX function is a powerful tool that allows you to retrieve values from a specified range or array based on row and column numbers.

INDEX(array, row_num, [column_num])

As it requires row_num and column_num, it is mostly used with MATCH function (used to calculate the position number of a value in the given range) for more flexibility and dynamic lookups.

It can also be used with other functions like SUM, AVERAGE, MAX, or MIN to perform calculations on specific data points.

For example, if you have to calculate sum of first five values in a given range A1:A10, use can be written as:

=SUM(INDEX(A1:A10, 1):INDEX(A1:A10, 5))

Feel free to contact me if you want to master INDEX function.

As of my last update in April 2023, in Microsoft Excel for Office 365 and Excel 2019, each worksheet contains: - **1,048,576 rows**- **16,384 columns** (column XFD is the last column) This size is consistent across recent versions of Excel, starting from Excel 2007. It's important to note that while Excel can technically handle over a million rows and more than sixteen thousand columns, working with datasets near these limits can significantly impact performance, depending on the system's hardware specifications.

1. Select the cells that you want to contain the lists.
2. On the ribbon, select DATA and choose. Data Validation.
3. In the dialog box, set Allow to List.
4. Select the Source field and type the text or numbers for the drop-down list, separate by commas. ...
5. Select OK.

Create a PivotTable to analyze worksheet data
Select the cells you want to create a PivotTable from. ...
Select Insert > PivotTable.
This creates a PivotTable based on an existing table or range. ...
Choose where you want the PivotTable report to be placed. ...
Select OK.