Excel - inserting formula instead of value from sheet to sheet

Question

Excel - inserting formula instead of value from sheet to sheet

Peter Mueller 65

I have the most peculiar problem. I have set up an Excel file with a number of worksheets.

I want to automatically copy the first column from worksheet 1 into all the others. So, I did what I had done countless times before:

1. I went into cell A1 in sheet 2

2. inserted an equal sign (=)

3. went over to sheet 1

4. and clicked on cell A1 in there.

Normally, that means the value (in this case text) from A1 in Sheet 1 appears in A1 in Sheet 2 and so forth. However, in this case instead of the value I get the formula, i.e., =’Sheet 1’!A1.

Any suggestion will be very much appreciated.

Many thanks

0 comments

Answer accepted by question author

Liora D 18,955 Microsoft External Staff Moderator

Dear @Peter Mueller,

I hope you’re having a good day.

Thanks for the clear explanation, and actually, the steps you followed are exactly right. What you’re seeing isn’t Excel behaving differently, but rather Excel not evaluating the formula, so it simply shows it as text.

To answer your question directly: when Excel shows ='Sheet 1'!A1 instead of the value, it means the formula is being treated as text, not executed.

This is usually caused by one of the following, and both are quick to fix:

Check if “Show Formulas” is turned on

If all cells are showing formulas, press `Ctrl + `` (or go to the Formulas tab and toggle it off). This will switch back to normal view.

Check the cell format in Sheet2

If the cells are formatted as Text, Excel won’t calculate the formula.
Change the format to General, then click into the cell (F2) and press Enter again so Excel recalculates it.

Once one of these is corrected, the same formula ='Sheet 1'!A1 will display the value as expected.

If it only happens in certain cells or columns, it’s almost always a formatting issue on those cells.

I hope this information helps point you in the right direction. If you run into any issues while trying the steps, or if something still doesn’t feel quite right, please don’t hesitate to reach out again. I’ll do my best to support you however I can.

Looking forward to hearing back from you with any updates or additional details.

Warm regards,

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Peter Mueller 65 Reputation points

2026-05-18T10:10:23.6133333+00:00

Thank you, the cells were formatted as Text, and changing this to General solved the problem.
Liora D 18,955 Reputation points Microsoft External Staff Moderator

2026-05-18T16:05:25.9+00:00

Hi Peter Mueller,

Thank you for taking the time to come back and share the outcome, I’m really glad to hear that changing the cell format resolved the issue.

If you have any other questions or run into anything similar, feel free to post again, happy to help.

Best regards,

1 additional answer

Your answer

Peter Mueller 65 Reputation points

2026-05-18T10:10:23.6133333+00:00

Thank you, the cells were formatted as Text, and changing this to General solved the problem.
Liora D 18,955 Reputation points Microsoft External Staff Moderator

2026-05-18T16:05:25.9+00:00

Hi Peter Mueller,

Thank you for taking the time to come back and share the outcome, I’m really glad to hear that changing the cell format resolved the issue.

If you have any other questions or run into anything similar, feel free to post again, happy to help.

Best regards,

Answer 1

Excel is showing the formula text instead of the value because the destination cells (or the sheet) are formatted as Text or formulas are not being evaluated.

Change the cell format and re-enter the formula:

Select the cells on Sheet2 where the links should go (e.g., column A).
On the Home tab, set Number Format to General (or any non-Text format).
With the cells still selected, press Delete to clear them.
Click in A1 on Sheet2, type = then click A1 on Sheet1 and press Enter.
Use the fill handle to copy the formula down if needed.

If formulas still appear as text, also check:

That Show Formulas is not turned on (Formulas tab → Formula Auditing → Show Formulas should be off).
That workbook calculation is set to Automatic (Excel menu → Preferences → Calculation → Automatic).

Once the cells are not formatted as Text and formulas are being calculated, ='Sheet 1'!A1 will display the value from Sheet1!A1 instead of the formula string.

References: