How to prevent a "REF" error when deleting columns that contain a formula somewhere in the spreadsheet

Question

How to prevent a "REF" error when deleting columns that contain a formula somewhere in the spreadsheet

Anonymous 200

I want to change these time columns to seconds and then format the cells to custom mm:ss and then delete the original column so that I have the column now with minutes not seconds. So for Average agent interaction time I start by inserting a new column to the right and then I use the formula =A2(606024) to get minutes then I drag down the formula. Now I want to delete the original Average agent interaction time column that contains the time in seconds since I only want minutes. However, I am having difficulty doing this; returns a "REF" error. I want to do this across all "time" columns. I do not want any of the cell formula to recognize if I delete a column. This is a large data set btw. User's image

2 answers

Your answer

Answer 1

Hendrix-C 8,560 Microsoft External Staff Moderator

Hello @Carrie Ward,

Thank you for reaching out to Microsoft Q&A.

The reason Excel returned error #REF! is because it's impossible to overwrite a cell with a formula that depends on its own value. For example, you have the time value in cell A2 as 100, in cell B2 you use a formula A210 that calculates with the value in cell A2. When you copy the formula in B2 to replace cell A2, the value 100 is gone and B2 has no value of A2 to calculate A210. It will lead to the cell return error #REF!.

If you want to replace time in the original column from seconds to minute in mm:ss, you need to have two blank columns:

Column 1: calculate the data in column A into mm:ss

User's image

Column 2: copy the value calculated in mm:ss and paste its value only (right click and choose Values instead of using ctrl V)

User's image

Then you can copy back to replace the data in the original column.

User's image

I hope the above information is helpful for you. If there's anything else I can help you with, please feel free to reach out again.

Looking forward to your response.

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.

Anonymous 200 Reputation points

2025-09-02T11:52:10.6566667+00:00

This is perfect! However, I need to be able to see the formulas when I click on a cell and this is just showing the time in minutes which is what I needed but is there a way for myself and others the formula of how I arrive at the given times in minutes?
Anonymous 200 Reputation points

2025-09-02T11:59:33.7033333+00:00

Also, how do I get the cell to read in this format showing time as 12:06:29 AM
Hendrix-C 8,560 Reputation points Microsoft External Staff Moderator

2025-09-02T16:27:57.44+00:00

Hi @Carrie Ward,

Thank you for your response.
We will provide the guidelines for each of your requests:

1-About the formula:
Yes, you can see the formulas for calculating time into minutes. However, you will need to have two different columns, one column is for the original numbers, and the other is for the time in minutes (like I demonstrate below). If you want to delete the original data as you mentioned in the first question, the calculation will return #REF! because the formula depends on the values of the original data. That's why you must keep them if you want to have the second column with both number in minutes and the formulas.
2-About time formatting
For the time in the cell showing time as 12:06:29 AM, you need to click on that cell > Custom > More number format > choose the format with h:mm:ss AM/PM

Hope this will help. Please feel free to reach out again if you have any other questions.

Looking forward to your response.
Anonymous 200 Reputation points

2025-09-02T18:17:26.0666667+00:00

As usual, perfectly stated for my understanding! You should feel proud of helping others since you are so good at it!
Anonymous 200 Reputation points

2025-09-02T18:34:33.7466667+00:00

Hey when I right click on 04:26 and select format cells to custom then the hr:mm:ss AM PM it does not change
Hendrix-C 8,560 Reputation points Microsoft External Staff Moderator

2025-09-02T19:01:29.8566667+00:00

Hi @Carrie Ward,
The reason it does not change is because of the formula I used. As you can see in the previous reply, I used the formula =TEXT(A2/86400,"mm:ss"). With this formula, I will apply the results' format in the cell constantly as mm:ss and cannot be changed via custom formatting.
If you need flexibility with the data and not using mm:ss format constantly, only use the formula =A2/86400 for calculation. Then you can select the cell and go to Custom > More number format > choose the format that meet your requirements. For example: mm:ss as showing minutes:seconds, h:mm:ss AM/PM to show 12:04:06 AM or h:mm:ss to show all hours, minutes and seconds.
If you have any other questions, please don't hesitate to ask.
Anonymous 200 Reputation points

2025-09-02T20:18:34.8033333+00:00

Okay I did that and this is what I want but I don't want it showing like this. I want to see what is in E2 12:04:29 where =D2/86400 is??
Hendrix-C 8,560 Reputation points Microsoft External Staff Moderator

2025-09-02T20:40:26.48+00:00

Hi @Carrie Ward,
Please let me know if I've misunderstood your request. From your sharing, you want to show the formula inside the cell when you click into it instead of the results. Unfortunately, Excel does not have a built-in setting that automatically displays the formula inside the cells itself when you click it. By design, when you click into the cell, the cell still shows the result and you can check the formula in the formula bar above the spreadsheet.
If you want to see the formula inside the cell, you have to do it manually in two ways:

1-Click into the formula in the formula bar or double click into the cell. This way you can see only in each cell.

2-Using the "Show formulas" mode in Excel by going to Formulas tab > choose Show formulas or pressing Ctrl + ~. This will display all formulas directly in their cells across the sheet.

Answer 2

Good day @Carrie Ward,

Hope things are running smoothly on your end.

Following up on the support thread we've been working on. Do you have any concerns about this problem? If you are still facing the same issue or need assistance with anything else, kindly respond to this email, and I will be happy to help.

We want to make sure everything is working as expected and that your experience remains uninterrupted.

Thank you for your patience and understanding throughout the troubleshooting process.

I look forward to hearing from you soon.

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.

Share via

How to prevent a "REF" error when deleting columns that contain a formula somewhere in the spreadsheet

2 answers

Your answer