Calculation Issues

How to Link Worksheets and Create Calculation Formulas in Excel

Nimra MalikNimra Malik Nov 10, 2026 869 views

Question details

The user needs to learn how to pull data from one worksheet to another, perform mathematical calculations on that data, and set up variance flags based on the results.

How to Link Worksheets and Create Calculation Formulas in Excel
Product
Excel / WPS Spreadsheet
Device & OS
not provided
Scenario
Organizing complex data across multiple worksheets, pulling matching records, and analyzing numerical differences with conditional flags.
Observed behavior
Needs a reliable method to dynamically link tables, execute mathematical operations across sheets, and conditionally flag data variances.
Before you start

Ensure that both your source and destination worksheets are located within the same workbook to prevent broken links. Identify a unique common identifier (like an Employee ID or Product Code) that exists in both sheets to match the data accurately.

Solution 1Recommended

Use VLOOKUP and IF Formulas to Pull Data and Flag Variances

Use lookup functions to retrieve data from a source sheet, perform basic subtraction for the variance, and use an IF statement to create a dynamic variance flag.

Linking worksheets requires referencing cells or ranges from another sheet. Using VLOOKUP (or XLOOKUP) allows you to match records dynamically, which is much safer than direct cell references if your data order changes.

Once the data is pulled into your destination sheet, you can use standard arithmetic operators to calculate the variance and nest an IF function to display text warnings.

1
Pull data using VLOOKUP

In your destination worksheet, select the cell where you want the data to appear. Type `=VLOOKUP(A2, SourceSheet!A:D, 2, FALSE)`, replacing 'A2' with your lookup value, 'SourceSheet!A:D' with your source data range, and '2' with the column index containing the data.

2
Calculate the variance

In the adjacent column, create a formula to calculate the difference. For example, type `=C2-B2` to subtract the target value from the actual value.

3
Create a variance flag

In the next column, use an IF function to flag significant variances. Type `=IF(D2>100, "High Variance", "OK")` to automatically label any variance greater than 100.

Use VLOOKUP and IF Formulas to Pull Data and Flag Variances
Use Structured References: Formatting your data as a Table (Ctrl + T) makes cross-sheet formulas easier to read and automatically updates the formulas when new rows are added.
Advanced Spreadsheet Management

Easily Link Sheets and Calculate Data with WPS Spreadsheet

WPS Spreadsheet provides robust functions like VLOOKUP, XLOOKUP, and IF statements to help you seamlessly link data across multiple worksheets and automate complex variance calculations.

  1. 1. Open your workbook: Launch WPS Spreadsheet and open the workbook containing your datasets.
  2. 2. Initiate the formula: Click on the destination cell where you want to pull data or calculate a variance and type `=` to begin your formula.
  3. 3. Select cross-sheet references: While typing the formula, simply click on the tab of your source sheet and select the required cells. WPS will automatically insert the correct sheet reference (e.g., `Sheet1!A1`).
  4. 4. Apply logical functions: Combine your references with the `IF` function from the 'Formulas' tab to set up automated variance flags for your data.
Fully compatible with Microsoft Excel (.xlsx) formulas and file formatsSupports advanced lookup, logical, and dynamic array formulasUser-friendly interface for managing large datasets across multiple sheetsFree to use with fast and lightweight performance
microsoft office alternative - wps office

Frequently Asked Questions

How do I do a simple cell reference to another worksheet?

To reference a specific cell in another sheet, type `=` in your destination cell, click the tab of the source sheet at the bottom of the screen, and click the cell you want to link. Press Enter, and your formula will look like `=SheetName!A1`.

Why is my VLOOKUP returning an #N/A error when pulling data across sheets?

The #N/A error typically occurs when the lookup value cannot be found in the first column of your source table. Ensure that the values match exactly, check for hidden leading/trailing spaces, and verify that your lookup range is locked with absolute references (e.g., $A$1:$D$100).

Can I link data between two completely separate Excel files?

Yes. With both files open, start your formula and click the cell in the other workbook. The formula will include the file name in square brackets. However, it is highly recommended to keep linked sheets within the same workbook to prevent broken links if files are moved or renamed.

How do I highlight my variance flags with colors?

Select the cells containing your IF formula variance flags, navigate to the Home tab, and click 'Conditional Formatting'. Choose 'Highlight Cells Rules' > 'Text that Contains', and set it to highlight specific flag text (like 'High Variance') in red.