Managing Excel files with vLookup across workbooks

This blog is regarding a large Excel file with many vLookup or hLookup formulas referring to other workbooks. In this article, we’re going to explain the problems with these files, as outlined below, and possible solutions.

• New data might be continuously added or modified in your sheets. Your vLookup formulas might not catch newly added rows.
• The file might be slow for processing the vlookup formulas. When a formula, which refers to a different Excel workbook, is executed, that workbook needs to be opened. Opening workbooks is time-consuming, the file freezes for a moment, or its performance reduces considerably.
• A vlookup formula to a different workbook requires the direct path of the destination workbook. However, if you move the destination file to a different directory, the vlookup formulas pointing to that file need to be updated; this might be done manually, which is not an efficient solution, and the risk of human error is high.
• The structure of the destination workbook might change; for example, the name of a sheet might change. If so, the vlookup formulas need to be updated.

Solutions with Excel

We listed some solutions to deal with the outlined problems.

Dynamic vLookup Formulas

Named Tables: Avoid using vLookup formulas that cover a whole column. A parameter in a vLookup formula is the address of the table to be looked up. You might continuously add data to your sheets or delete them that changes the address of the table. To overcome the challenge of tables with dynamic size, you can use a whole column as the table address in your vLookup formula. But this solution is not recommended because it considerably affects the performance since Excel looks up the entire column that probably contains many null cells or invalid data. Instead, you can define a table in your sheet and name it, and then use the table name in your vLookup formula. If you add rows to your sheet, the table covers the new rows automatically, and vLookup formulas referring to the table automatically considers newly added rows.

https://youtu.be/cMWC5CDaPVs

Macro & vLookup: However, if you’d like to share your Excel file, you can’t use tables since shared Excel files don’t support tables. An alternative solution to Excel tables is dynamic vLookup formulas, and macros. A macro/VBA code can determine the address of the table every time some rows are added or deleted. Then the macro updates the vLookup formulas. To make this process more efficient, you can use the Indirect formula in the vLookup formulas. If you or your business team are not familiar with Macro and VBA development, please feel free to visit our Excel Automation services.

Macro, vLookup & External Workbook: Macro/VBA is also beneficial for making vLookup formulas more dynamic. If your vLookup formulas are referring to external workbooks, you’d be required to use the workbook path in your formulas. But using the direct path has challenges. What if you relocate the external workbook to a different directory? Would you manually update the path of the external file for each vLookup formula? The manual update is time-consuming, and the risk of human error is high. That’s why Macro is handy. A custom VBA code can instantly update the path of an external file in all vLookup formulas.

Macro, vLookup & Sheet Name Changes: If a vLookup formula refers to another sheet, it must include the name of that sheet; This is challenging because if the name of the sheet changes, the formula will stop working. Imagine that you have thousands of vLookup formulas, updating them might take much time. As a workaround, a custom Macro, with a dashboard to manage sheet names, paths, and addresses, can quickly update the vLookup formulas.

Performance & External Workbooks: vLookup has a drawback which is affecting the performance of Excel. To calculate a formula referring to an external file, you need to open it. And opening files, especially large ones, takes time and reduces the performance of Excel. The next solutions have a workaround for this problem.

Combining Excel and Database

Instead of storing data in multiple Excel files, you can save all of it in an SQL database. Therefore you do not need to link your Excel files together; they can be linked with a common database. Excel can have two-way communication with this database. Your staff can stay on Excel, but the data is stored on your SQL database. i.e., instead of using vLookups to get data from other workbooks, the data is read from the database.

At the same time, the data can be published from Excel to the database. If a data conflict occurs, a warning is displayed to override data or keep the source data in the database.

Please note ODBC does not support a two-way connection with SQL. You can use Master Data Service or custom query for the connection. If you are not familiar with these solutions, please review our Excel consulting page.

Non-Excel Solutions

Depending on your requirements and needs, Excel might not be a suitable fit for your needs. In this section, we investigate alternative solutions to Excel.

MS Access

An alternative solution is Microsoft Access. In MS Access, you can make a custom database to hold the entire data. You can merge all your Excel files into a single Access database. All your Excel files can be merged into a single Access database. Performance, referring to external files, and name changes won’t be an issue in an Access file.

However, MS Access might not be a suitable solution for your business because of the following reasons

• Switching entirely from Excel to Access might not be friendly for your staff. It’s a significant transition.
• MS Access has limitations compared to other solutions like custom SQL databases, which is discussed in the next solutions.

For more information, please review our MS Access Services.

Custom Database with Web Application as Front-End

Another solution is to move out of Excel and develop a custom database and web application for your business. Although this is not a simple step, a custom database might have a considerable return on investment in the long run. A custom web application is not limited as Excel is, and you will enjoy better experiences managing your data. And it has a better performance compared to Excel.

If you are wondering how to make a custom web application for your business, please visit our custom web application development services. And please feel free to contact us if you have questions regarding this.

Large Excel Files & Performance

If you are getting frustrated with large Excel files, optimizing vLookup formulas is not the only solution. You can try reducing the file size. To shrink the size of workbooks, convert them to Excel binary workbook, XLSB file format. “The XLSB format (also sometimes referred to as BIFF12, as in “binary file format for Office 12″) uses the same Open Packaging Convention used by the Open XML formats and XPS. So it’s basically a ZIP container, and you can open it with any ZIP tool to see what’s inside. But instead of .XML parts within the package, you’ll find.BIN parts as shown to the right.” (Doug Mahugh)

XLSB files might have some unexpected limitations because they’re binary files. Probably the only way to find out if this solution works for you is to try it.

How to convert files to XLSB? Simply save the file as XLSB.