Improve Functionality of Large Excel Workbooks with Hundreds of Sheets: Use Query Tables and XLOOKUP instead of INDIRECT

Improving Functionality of Large Excel Workbooks with Hundreds of Sheets: Using Query Tables and XLOOKUP Instead of INDIRECT

Microsoft Excel is a powerful tool for managing and analyzing data. However, when it comes to large workbooks with hundreds of sheets, functionality can become slow and cumbersome. In this article, we will explore how to improve the functionality of large Excel workbooks by using query tables and XLOOKUP instead of the INDIRECT function.

The Problem with INDIRECT

The INDIRECT function in Excel is often used to convert a text string into a reference. However, this function can be volatile and slow down your workbook, especially when used with large data sets. This is because the function recalculates every time there is a change in the workbook, even if the change does not affect the INDIRECT formula. Additionally, INDIRECT can return a #REF! error if the referenced cell or range is deleted.

Using Query Tables Instead of INDIRECT

Query tables offer a faster and more stable alternative to INDIRECT. A query table is a range of cells that is linked to an external data source, such as another Excel file, a text file, or a database. Query tables can be used to import data into Excel and refresh the data with a single click. This can be especially useful when working with large data sets, as query tables do not require the same level of manual manipulation as INDIRECT.

To create a query table, follow these steps:

1. Click on the Data tab in the Excel ribbon.
2. Click on the "From Text/CSV" or "From Other Sources" button in the Get & Transform Data group.
3. Select the data source and follow the prompts to import the data.
4. Once the data is imported, click on the "Load To" button in the Get & Transform Data group.
5. Select "Only Create Connection" and check the box for "Add this data to the Data Model" if you want to use Power Pivot.
6. Click "Load" to create the query table.

Using XLOOKUP Instead of INDIRECT

XLOOKUP is a new function in Excel that offers a more efficient and flexible alternative to INDIRECT. XLOOKUP can search for data in an array or a table, and it can return a value or an array of values. Additionally, XLOOKUP can handle multiple criteria and it does not require the use of helper columns.

To use XLOOKUP instead of INDIRECT, follow these steps:

1. Identify the range or table that you want to search.
2. Use XLOOKUP to search for the data, specifying the lookup value, the search range, and the return value.
3. If necessary, use additional arguments to specify multiple criteria or to return an array of values.

Here is an example of how to use XLOOKUP instead of INDIRECT:

This formula searches for the sheet named "Sheet1" and returns the value in the second column of that sheet. This is equivalent to the following formula using INDIRECT:

In this article, we have explored how to improve the functionality of large Excel workbooks with hundreds of sheets by using query tables and XLOOKUP instead of the INDIRECT function. Query tables offer a faster and more stable alternative to INDIRECT, while XLOOKUP offers a more efficient and flexible alternative to INDIRECT. By using these techniques, you can improve the performance and accuracy of your Excel workbooks.

References

• Import data from external data sources (Power Query)
• XLOOKUP function