Addressing Five Common Challenges in Excel: Best Practices for Financial Modeling and Analytics

Excel has become an integral tool for millions of individuals and businesses across a wide range of fields. However, our favorite office staple can also cause frustrations. Fortunately, several best practices can mitigate some of the common challenges, thereby improving efficiency and productivity.

In a recent video interview with my colleague Antony Walker , we discussed how to address the following 5 common challenges in Excel:

• Pulling data from numerous files and sources
• Running scenarios through a large number of files
• Slow files that keep crashing
• Getting lost in countless tabs
• Managing the risk of error

Challenge #1: Pulling data from numerous files and sources

Dealing with numerous files and sources often leads to disarray, as it becomes increasingly difficult to consolidate and reconcile data, for instance consolidating financials across a portfolio of investments, or making sure that ERP data is consistent with 3rd party database.

Excel Power Query is a free game-changing big data tool that can help with data preparation, transformation, and analysis.

Power Query provides a seamless platform available for free in Excel that saves time, automates processes and enhances accuracy so that data manipulation and analysis are streamlined and efficient.

Power Query can connect to a wide range of data sources, including Excel files, text or CSV files, files from the cloud and online database. Some common uses of Power Query include:

• Modifying the layout of data
• Connecting to central data sources
• Merging related tables
• Combining multiple files

You can watch the video below if you're curious to learn more about this wonderful tool that can literally change your life (it certainly changed mine).

Challenge #2: Running scenarios through a large number of files

With the recent increase in interest rates, we've seen clients eager to assess the impact of increasing cost of capital or cap rate assumptions across their entire portfolio. One of them required running 3 different scenarios across 70 different Excel models.

VBA remains the tool of choice to automate edits across a large number of Excel files.

We used VBA to automatically generate the 210 Excel files (70 project models x 3 scenarios per model). We then leveraged Power Query to pull the data across all files, and finalized the analysis in core Excel thanks to Power Pivot.

To run more complex scenarios, it is worth creating a master input table summarizing key assumptions across all projects , again leveraging VBA to push such assumptions across all files in the portfolio, and Power Query / Power Pivot to consolidate the data and finalize the analysis.

For more dynamic and interactive dashboards, use slicers and control forms to visualize the impact of your key metrics while toggling between various scenarios and assumptions. Power BI is also fantastic to create online dashboard and visualize your data.

You can watch the video below if you want to learn more about the differences between Power Query, Power Pivot and Power BI.

Challenge #3: Slow files that keep crashing

A crash typically occurs with large files or running intricate calculations. Other factors such as working on shared networks, via VPN or virtual machine can make things worse. That said, certain best practices within your file can help.

Be aware that SUMIFS can be pretty slow to compute, and using direct links instead can speed up your workbook significantly. Software can be used to automatically convert your SUMIFS or INDEX MATCH into direct links.

Avoid volatile functions like OFFSET which may literally force your entire workbook to recalculate whenever you edit any cell.

Whenever you edit a cell, Excel normally only recalculates dependent cells whose formulas refer to it. However, certain functions called volatile must be recalculated all the time regardless of their precedents. In turn, any dependent cells referring to such volatile cells will also be recalculated, so the matter is much worse when such volatile functions are used near your input - in particular people sometimes use OFFSET to choose between scenarios: it is a recipe for disaster. The image below illustrates how all the values in the output section will be recalculated every time as they all depend on the OFFSET formulas used for the retained scenario in cells G8:G11.

Challenge #4: Getting lost in countless tabs

Navigating through countless tabs in Excel can also be challenging, especially when working with large spreadsheets.

With a proper navigation structure, you're only ever two clicks away from wherever you want in your workbook

A useful tactic to improve navigation is the inclusion of a Table of Contents with links to each tab, as well as links back to the Table of Contents on every tab. This solution gets you anywhere in your workbook in only two clicks, enhancing ease of navigation and reducing the time spent searching for specific sheets or data points.

Challenge #5: Managing the risk of error

We love Excel for how flexible it is, but the flip side of the coin is that every single cell can technically contain an error, so mitigating the risk of error is fundamental to working in Excel.

Centralize your error checks to manage the risk of error

• Lock non-input cells and use data validation to prevent data entry errors.
• Use a single formula per row or column to facilitate maintenance and review, as you can review a single column or row instead of your entire model, and you can drag your formulas across when making updates.
• Centralize all your error checks in one cell on each tab and have a master check sheet capturing potential errors from all tabs can also be an effective strategy. If the central check goes off, use conditional formatting on every tab to make it immediately visible from anywhere in the model.

Conclusion

The mastery of Excel is not just about understanding formulas and functions, but also about effective spreadsheet management. Embrace these tips to improve your productivity and get the most out of Excel. You can watch me discuss these tips in greater deal with Antony Walker in our recent video interview: https://www.cohnreznick.com/insights/5-common-challenges-excel-financial-modeling