How to Fix

How to fix the #REF! error - Excel formula
Ever been caught off guard by the infamous #REF! error popping up in your worksheets? Fear not, because today, we're diving headfirst into the world of fixing #REF! errors in Excel.
So, what exactly causes this pesky problem? Well, it's often the result of invalid references lurking within your formulas. Get ready to conquer those errors like a pro. Let's learn how to fix #REF! error in Excel together!
1. For demo purposes, I created four columns with the same data.
2. In the last column of E, I used some different formulas to get the total of all four columns.
3. If I delete this column D, then you will notice that the #REF! error has occurred in rows 2, 3, and 5.
4. This means, a #REF! error occurs because a referenced cell, row, or column has been deleted.
5. To fix this, either restore the deleted cells/rows/columns or adjust your formula to reference existing cells.
6. For example, If we are sure that we have deleted the correct column then in row 2, I will delete the #REF! error manually, and this will show us the correct total.
7. Similarly, if I change the formula in row number 3 from my existing formula it will also fix the #REF! error.
8. For row number 5, I will fix #REF error by changing the formula or deleting the #REF! from the formula.
9. Now, a question in your mind can arise, why do rows number 4 and 6 not show the #REF! error.
10. The answer is in the used formula for these rows.
11. In row no 4, I used absolute cell references with dollar signs to prevent cell references from changing when rows or columns are deleted.
12. In row no 5 the formula is =SUM(A6:C6) to ensure the range remains fixed even if rows are deleted.
13. It is always recommended to promptly resolve #REF errors by using the Undo button for accidental deletions and ensuring correct references in formulas to prevent further confusion and save time.
Please do not forget to like this video.
Make sure to subscribe to this channel,
Press the bell icon to get updates on new tech-support videos.
Thank you for watching this video.