Formula Errors

Fix Excel Formulas Working on Some Sheets but Not Others on Mac

Maira Mehtab Sep 27, 2026 869 views

Question details

User experiences inconsistent formula results when copying and pasting worksheets, where formulas calculate correctly on the original sheet but fail or return wrong data on others.

How to Fix Excel Formulas Working on Some Sheets but Not Others on Mac

Product: Excel (Microsoft 365)
Device & OS: Mac
Scenario: Duplicating or copying and pasting worksheets that contain specific formulas to reuse the layout or data structure.
Observed behavior: Formulas return different or incorrect results on the newly copied sheets due to shifting cell references, varying sheet names, or formatting mismatches.

Before you start

Before troubleshooting, temporarily unhide any hidden rows, columns, or sheets to properly inspect all formula ranges, and ensure your Microsoft 365 application is up to date.

Solution 1Recommended

Lock Cell Ranges with Absolute References

Prevent formulas from shifting incorrectly when copied to new sheets by using absolute references to lock specific rows and columns.

When you copy a formula in Excel, the cell references shift automatically based on the new location (relative reference). If your formula needs to point to a fixed data table or a specific cell (like a tax rate), you must convert it to an absolute reference.

Select the formula cell

Click the cell containing the formula on your original, working worksheet.

Edit the cell reference

Click inside the Formula Bar at the top of the screen and highlight the cell reference you want to lock (e.g., A1).

Apply absolute formatting

Press Command + T (on Mac) or F4 (on Windows) to add dollar signs to the reference, changing it to $A$1. This locks both the row and the column.

Apply and test

Press Enter to save the formula, then try copying and pasting the formula or the worksheet again to verify it works.

Lock Cell Ranges with Absolute References

Keyboard Shortcut Tip: You can repeatedly press Command + T (Mac) to cycle through different reference types: absolute ($A$1), mixed row ($A1), mixed column (A$1), and relative (A1).

Solution 2

Create a Clean Workbook Template

Avoid errors caused by repeatedly copying and pasting heavily formatted sheets by saving a master template instead.

Solution 3

Verify Sheet Names and Data Formatting

Ensure the referenced data ranges on the new sheets match the exact structure and formatting of the original sheet.

Use WPS Spreadsheet to Manage Complex Formulas Across Sheets

WPS Office offers a powerful, free Spreadsheet tool that handles absolute and relative formula references seamlessly. It provides a highly compatible and intuitive interface for managing multi-sheet workbooks without calculation errors.

1. Open your workbook: Launch WPS Spreadsheet and open your existing .xlsx workbook.
2. Lock formula references: Select your formula cell in the editing bar and press F4 to quickly toggle absolute references (e.g., $A$1).
3. Duplicate sheets safely: Right-click the sheet tab at the bottom and select 'Move or Copy' to duplicate the sheet while preserving internal formula structures.
4. Evaluate complex formulas: Go to the Formulas tab and click 'Evaluate Formula' to step through calculations and troubleshoot any remaining errors.

100% compatible with Microsoft Excel (.xlsx) formats and formulasEasy-to-use formula auditing tools to track errorsSupports cross-sheet and 3D formula referencing seamlesslyLightweight and runs smoothly on both Mac and Windows devices

microsoft office alternative - wps office

Frequently Asked Questions

Why do my Excel formulas change when I copy them to another sheet?

By default, Excel uses relative cell references. When you paste the formula to a new location, the references shift based on the new position. To prevent this, you must use absolute references by adding dollar signs (e.g., $A$1) before copying.

How do I copy a worksheet without changing the formulas?

If your formulas only reference cells on the same sheet, right-clicking the sheet tab and choosing 'Move or Copy' > 'Create a copy' usually preserves the internal relationships. However, if they reference other sheets, you must ensure those references are absolute before duplicating.

What does the #REF! error mean when copying sheets?

The #REF! error occurs when a formula refers to a cell or sheet that is no longer valid. This often happens because the target cell was deleted, or a relative formula was copied so far down or across that its references shifted completely off the spreadsheet grid.

How can I quickly compare formulas on two different sheets?

Go to the View tab in Excel and click 'New Window' to open a second instance of your workbook. Then click 'Arrange All' to view both sheets side-by-side. You can press Control + ` (tilde) to display the underlying formulas instead of the calculated results for easy comparison.

Excel & Data Problems