Function Problems

How to Restore and Update an Excel Workbook with Multiple Years of Data

Amos GikundaAmos Gikunda Nov 10, 2026 868 views

Question details

The user needs to update a complex Excel workbook that aggregates multi-year actual data from various roll-up sheets and templates into a main comparison sheet.

How to Restore and Update an Excel Workbook with Multiple Years of Data
Product
Excel
Device & OS
not provided
Scenario
Updating a complex financial or property data tracking workbook to properly calculate and display selections across a three-year span.
Observed behavior
The main analysis sheet needs to accurately reflect metrics like EGI, OpEx, and NOI based on dynamic property and year selections across multiple data tabs.
Before you start

Before making structural changes, ensure that all source sheets have a consistent column layout to prevent formula mismatch errors.

Solution 1Recommended

Map Data and Expand Formulas for Multiple Years

Identify your roll-up sheets and manually expand your lookup formulas and data validations to incorporate the new years of data.

To properly integrate new years of data into a complex comparison sheet, you must trace the relationships between your source sheets and the main dashboard.

Consistency in the data structure across all sheets is crucial to ensure formulas like INDEX and MATCH can dynamically retrieve values based on user selections.

1
Identify and map source sheets

Locate the source sheets, such as Roll-Up Data, Templates, and Property View. Identify which columns contain properties, years, and specific metrics (like EGI, OpEx, and NOI).

2
Input and format multi-year data

Enter the data for the required multiple years (e.g., 2021, 2022, and 2023) into the appropriate source sheets, ensuring consistent formatting across all new entries.

3
Update lookup formulas

Select the main comparison sheet and update the data ranges in your formulas (such as INDEX/MATCH, XLOOKUP, or SUMIFS) so they span the newly added columns or rows.

4
Expand Data Validation lists

Go to Data > Data Validation, locate the cells containing your year dropdowns, and expand the source range to include the newly added years.

5
Adjust Conditional Formatting

Navigate to Home > Conditional Formatting > Manage Rules. Update the 'Applies to' ranges to ensure the new multi-year data is formatted correctly.

6
Test data combinations

Select various combinations of properties and years on your main sheet and manually verify the results against the source data to ensure accuracy.

Map Data and Expand Formulas for Multiple Years
Future-Proofing Your Workbook: Format your source data ranges as Excel Tables (Ctrl+T) so future data additions automatically expand your formula references.
Advanced Spreadsheet Management

Easily Manage Complex Multi-Year Workbooks with WPS Office

WPS Spreadsheet fully supports advanced array functions and multi-sheet consolidation, making it easy to handle complex roll-ups and financial templates without compatibility issues.

  1. 1. Open your workbook: Launch WPS Spreadsheet and open your complex multi-year workbook.
  2. 2. Audit and update formulas: Navigate to the Formulas tab to trace precedents and seamlessly edit your INDEX/MATCH or XLOOKUP formulas.
  3. 3. Manage dropdowns: Use the Data Validation tool in the Data tab to easily update your property and year selection dropdowns.
  4. 4. Save with full compatibility: Save your updated file in .xlsx format to ensure it remains perfectly functional across all Office platforms.
Fully compatible with Microsoft Excel (.xlsx) formats and complex data structuresSeamlessly supports advanced formulas like INDEX, MATCH, XLOOKUP, and SUMIFSBuilt-in robust tools for Data Validation and Conditional FormattingFree, lightweight, and fast for handling extensive multi-year datasets
microsoft office alternative - wps office

Frequently Asked Questions

Why are my INDEX/MATCH formulas returning #N/A after adding new years of data?

This usually happens because the formula's array reference was strictly defined and not expanded to include the newly added columns or rows. Check the formula and adjust the references, or format your data as a Table to make ranges dynamic.

How can I automatically include new years in my drop-down list?

You can create a dynamic named range using the OFFSET formula, or format your list of years as a Table and base your Data Validation source on that Table column.

Can I use Power Query to consolidate multiple years of roll-up data?

Yes. Power Query can append multiple yearly data tables into a single source table, significantly simplifying the lookup formulas required on your main comparison sheet.

What is the best formula to look up data based on both a property name and a year?

A two-way lookup using INDEX and MATCH is highly effective. Alternatively, you can use XLOOKUP nested within another XLOOKUP to return the exact metric for a specific intersection of a property and a year.