This is a **curated list of errors and common problems** that every newcomer in Excel is likely to face.

These** errors can potentially slow or even stop your work**, or simply just **act as a hindrance** to smooth functioning. To overcome these problems is the first step to advancing your Excel skills.

This article will illustrate the **Top 20 Common Excel errors** that you might face, or are currently facing and **how to tackle them**.

We have divided these problems into three categories for easier navigation and understanding:

### Formula Issues

### Visual and Navigation Issues

### Operational Issues

Let’s look at each of these errors one by one and learn how to fix errors in Excel!

**Formula Issues**

#REF! error stands of reference. Excel will display **#REF!** error when the cell that is referenced to in a formula** does not exist** or **is invalid**. This is usually the case when:

- Referred sheet, column, or row has been
**deleted** - Formula contains an
**incorrect or invalid**cell reference

**Example 1: **

Watch the **YouTube video** to learn how to fix #REF error in Excel and if you like it give it a thumbs-up!

In the table below, you will spot multiple #REF! error within formulas used in the cell.

This has happened because you have deleted a range that contains an explicit cell reference within the formula used.

To get rid of this error message we have to select the cell(s) with this error, use the Find & Replace dialog box and do the following:

**Find What:** #REF!

**Replace With:** (Leave this blank)

Press OK and it will clear the #REF error in Excel within the formula.

### Let’s look at the step-by-step tutorial below to understand how to remove #REF in Excel.

**Download workbookReplace-the-REF-error-2.xlsx**

**STEP 1:** To check the cell containing the cell, simply click on the cell and press F2.

Here, since you have used an explicit cell reference and it was deleted, Excel is returning a #REF error.

**STEP 2:** Highlight the table containing the errors.

**STEP 3:** Press **Ctrl + H** to open the **Find & Replace** dialog box.

**STEP 4:** Under Find What, input **#REF!** and leave Replace as **blank**. This is done to replace all the #REF! error with a blank.

**STEP 5:** Click on Replace All.

This is how your replaced data will look like:

Let’s look at another example when this error occurs due to copy-pasting the formula from other cells.

**Example 2:**

In the table below, you have sales data for different customers for 4 quarters and a sum formula used to calculate the total sales. The formula used to calculate the total sales value is **=SUM(B4, C4, D4, E4)**.

If you try and **delete Column E** (Quarter 4), the sum formula will change to =SUM(B4, C4, D4,#REF!) and return an error – #REF.

This error is caused because the formula to calculate the total sales uses explicit cell reference. When one of the cell references used in the formula is deleted (here cell E4), Excel is unable to calculate the value and returns an error.

A simple fix to this problem is to use a range instead of an explicit cell reference. Let’s look at the step-by-step tutorial to learn how:

**STEP 1: **Use formula **=SUM(B4: E4) **in cell F4 and copy-paste the formula below to cells F5: F11.

**STEP 2: **Now delete **Column E** to get the total sales for only 3 quarters.

If you change the formula from **=SUM(B4, C4, D4, E4) **to **=SUM(B4: E4)**, you will no longer to vulnerable to #REF in Excel. This formula recalculates the total sales value by removing the deleted cell.

Hence, it is advised to use range while writing a formula instead of an explicit cell reference.

Let’s take a look at another example when the error occurred due to VLOOKUP containing an invalid cell reference.

**Example 3:**

In the table below you have quarterly and total sales for different customers and using the VLOOKUP formula, you have tried to find out the total sales for the customer name mentioned.

The formula used to find the total sales for customers mentioned in cell H4 is **=VLOOKUP(H4,$A$4:$F$11,7,0).**

If you look into the formula used in detail, you will see that the value used to indicate the column index number is incorrect.

The arguments for a VLOOKUP function is:

**Lookup_value**= The value you want to look up in the first column of the table.**Table_array**= The table from which you need to retrieve the data.**Col_index_num**= The column number in the table array from which matching value should be returned.**Range_lookup**= Value should be 1 if you want an approximate match or 0 if you want an exact match of the return value.

Excel is returning an error in this formula because VLOOKUP is looking to return a value from the 7th column but the reference $A$4:$F$11 contains only 6 columns.

To fix this error, use the formula **=VLOOKUP(H4,$A$4:$F$11,6,0).**

Excel displays an #VALUE! error when the **variable provided in the formula is not a supported type**. This Excel error can occur because of the following **reasons**:

- Cell is left
**blank**or contains**hidden spaces**; or - Cell contains
**text instead of the number**; or **Date is treated as text**, etc.

**Download workbookValue-Error.xlsx**

**Example 1: **

Here, we are trying to** calculate the total sales amount** by adding the sales achieved in both regions on different dates.

As you can see when** cell D7 adds B7 and C7, it returns #VALUE!** error. This is because cell B7 **contains text instead of a number**.

Let’s fix this!

Change the **text nil to number 0**.

You can also **use the function SUM** instead of using the addition operator (+). As **formulas with operators will not calculate cells with tex**t and instead display VALUE error.

If you **use functions** they will simply **ignore text values and calculate everything else**.

**Example 2:**

Sometimes, VALUE error will be displayed when the** cell contains hidden spaces**. These **spaces will look like the cell is blank** but in fact, it contains a space instead.

In this example, you can see even though cell **B7 looks like it is a blank, value is cell D7 is displaying an error**. This is because cell** B7 actually contains hidden space**!

There can be **numerous cells that may contain hidden spaces** and it may be **difficult to spot** and remove them.

So, follow these **steps below to find extra spaces and remove** them!

**STEP 1:** Select the range that contains hidden spaces.

**STEP 2:** Press** Ctrl + F** to open the Find & Replace dialog box and select **Replace** tab.

**STEP 3:** Type in an **extra space in Find what** field and keep **Replace field blank**.

**STEP 3:** Press **Replace All** button. This is **remove all the hidden spaces** in the selected cells and **leave them blank**.

All the errors will now disappear!

**Example 3:**

In this example, we are trying to **add duration to the start day of the project** in order to get the project’s end date

Here, cells C7 and C11 are displaying #VALUE! error as** Excel cannot recognize the value as a date**. This is because date is separated using the **decimal points as delimiters**.

Let’s change the delimiter from **decimal point (.) to hyphen (-)**!

**STEP 1:** Select the cells containing dates.

**STEP 2:** Press** Ctrl+H** to open Find & Replace dialog box.

**STEP 3:** Type** decimal point in Find What** field and** hyphen in Replace With** field.

**STEP 4:** Press **Replace All** button.

This will replace decimal point with hyphen. Excel will now **treat them as dates** and make your** formula work perfectly**.

This error in Excel occurs when you attempt to** divide a number** with **zero**, any value **equivalent to zero,** or a **blank cell**.

**download excel workbook** Div-error.xlsx

**Example 1:**

The Excel DIV 0 is a **common and simple error that can easily be dealt with**. To **correct** this error, all we have to do is make sure that the **cell reference provided does not belong to an empty cell**.

In the example below, the formula in column C is a simple dividing formula. But, cells B9 and B12 have **blank cells** respectively, and hence result from the **division in column C returns the #DIV/0** Error.

Now if we simply **correct our mistakes by editing the cells in column B**, the errors will disappear.

Like so!

**Example 2:**

In most cases, the **referenced cell should actually contain the value 0 or be empty**. A **simple solution** to this will be to **use an IFERROR formula** and get rid of the error message.

We can design the IFERROR formula in such a way that it **either returns 0 or any value as desired if the result of the original formulas occurs to be an error**.

**What does it do?**

It returns a value that you set if a formula has an error

**Formula breakdown:**

**=IFERROR(Value, Value if Error)**

**What it means:**

**=IFERROR(The Formula, What do you want to show if The Formula has an error?)**

If you have a calculation that results in an error like, #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, then you can **clean it up by using the IFERROR function** which allows you to **replace the error it with a custom text**.

In this example, cell B9 contains 0, and cell B12 is empty. The corresponding cells C7 and C12 are thus displaying #DIV error.

Let’s wrap the division formula around the **IFERROR formula** and see what happens.

**=IFERROR(A7/B7,”-“)**

Here, we specified in the formula that if the** value in cell C7 returns an error**, it will be** replaced by our custom text – hyphen (-)**.

Apply the same formula to the rest of the cells by dragging the lower right corner downwards.

Hence as cells **B9 and B12 had blank or 0 values**, the corresponding **cells in column C** should produce an error but IFERROR changes it to **display hyphen (-)**.

This error occurs when the **range provided in the formula is not valid** and you have provided an **incorrect character instead of the required character** in the range.

This can be mainly due to **two reasons** :

- Wrong input instead of a
**colon (:)** - Wrong input instead of a
**comma (,)**

**download excel workbook** Null-error.xlsx

**Example 1:**

Sometimes in a formula, where there is** supposed to be “:”(colon), you might have entered space**. This can be the result of the error that we are getting as shown below.

In the formula bar, we can see that instead of **=SUM(A7:A13), it is entered as =SUM(A7 A13)**, which is the cause of the error.

So to correct this, we have to **replace that space with a colon(:)**.

Like so! As the typo has been corrected, the **error has been removed** and the formula is returning the correct value.

**Example 2:**

Sometimes in a formula, where **there is supposed to be “,”(comma), you might have entered space**.

In this example, we are trying to **calculate the average speed** in cell B11 based on the 3 entries mentioned in rows 7, 8, and 9. The formula used is:

**=AVERAGE(C7,C8 C9)**

This can lead to an error that we are getting as shown below:

Here, **instead of =AVERAGE(C7,C8 C9)** you should **enter the formula as =AVERAGE(C7,C8,C9)**.

There **should have been a comma between C8 and C9** but instead, there has been a **typing error and instead of the comma, space was inserted**.

Simply **removing the space and adding the comma** rectifies this error.

There are various formulas in Excel that **populate results in multiple cells**. It is termed as actually** spilling the data into the neighboring cells**.

The different scenarios when you can encounter this error are:

- Spill range isn’t blank
- Spill range contains merged cell
- Spill range is too big
- Spill range in a table

Excel is unable to fix these errors and display the output. Since the formula cannot populate all the cells it is supposed to, the first cell where the formula is entered shows the **#SPILL! **Error.

**download excel workbook** SPILL-error.xlsx

**Example 1: Spill range isn’t blank**

You want to find a unique list of customer names stated in column A using the UNIQUE function in Excel. But, Excel returns a #SPILL! error instead.

This is because the **output range already contains data** and Excel cannot overwrite it and populate the desired result.

**Solution:**

Simply, clear any contents from the output range and you are good to go!

The spill error can have multiple causes. To understand the root of this problem, click on the** small yellow! warning sign** next to the error. It shows the** cause of the problem**.

Most commonly it is caused because there is a **cell containing some value**, but it needs to be populated by the formula. **Removing the value** in all such cells **will remove the “blockage”** and, in turn, the error.

Simply, clear any contents from the output range and you are good to go!

There can be times when you get the** SPILL error** but you are **unable to spot the cell that contains unwanted data**.

Click on **Select Obstructing Cells** and it will take you to the cell that is creating the problem.

This is** useful when you have hidden spaces** and you are unable to find them.

**Example 2: Spill range contains merged cells**

SPILL error can also occur when the result range contains the** merged cells**.

In this example, you can see the** spill range i.e. B7:B15 is empty** but still, Excel returns **#SPILL error**.

To be sure of the** reason**, let’s click on the** yellow warning sign** and see what it says – Spill range has merged cell.

Now, click on** Select Obstructing Cells** and it will take you there.

Go to **Home** >** Merge & Center** to **unmerge the cells**.

The result is now visible and the error disappears!

**Example 3: Spill range is too big**

This error occurs when the **output range is too big** and the** result goes beyond the boundary in Excel**. There **isn’t enough space** to display the result.

In this example, we are trying to calculate the **discount given to the customers based on the sales amount**. Column C is used to get 5% of the sales amount.

As you can see, Excel is again and producing a** SPILL error** and this time it is because the** range is too big**.

Here, Excel is trying to **multiply each cell in Column B by 5%** and produce a** million results**. It will **spill the result in Column C** starting from cell C7 but it will **reach the end of Excel.**

Hence, the error!

To solve this, simply **replace the column with the relevant range **and **copy the formula** to the output range.

**Example 4: Spill range in a table**

Excel tables **do not support spilled array formulas**. If you try to insert an array formula in an Excel table, it will return a #SPILL error.

In this example, we are trying to** sort the amount mentioned in Column A in ascending order** using the SORT function. It will **return a dynamic array** as a result in column B.

Unfortunately, we are getting a #SPILL Excel error. This is because** range A7:B15 is in fact saved as a table** in Excel and dynamic functions do not work within Excel tables.

We can easily **convert it into a range** for our SORT function to work properly.

To do so:** Right Click** on any cell in the Table range > Select **Table** > Select** Convert to Range**.

This will** convert the table to range** and now all array **functions will work perfectly** as shown below!

The #NAME? Error is a **fairly common** one that even seasoned Excel professionals can face. It occurs when there:

- Spelling error in
**Formula name** - Spelling error in
**Cell Range** - Spelling error in
**Named Range** - Text entered without quotes

**download excel workbook**Name-error.xlsx

**Example 1: Formula Name**

When entering the** name of the formula if the name is misspelled**, then that can lead to the occurrence of this error.

In the above example, you can see that** instead of writing =LEN(A7), we have typed =lan(A7)**. There is an error in the formula name, leading to the problem.

Now you can see, **simply correcting the name removes the error displayed**.

The **best way to not get #NAME excel error** is to** choose the formula in the drop-down list while typing** the name of the formula. This makes sure that the formula name is not misspelled.

**Example 2- Cell/Range name**

When entering a** formula that references a certain cell or range**, **misspelling the name of the cell or range can lead to this error.**

Here we can see that the **name of the cell is misspelled**. Instead of **=LEN(A7), it is typed as =LEN(AA)**.

To rectify this, simply type in the **correct cell name**.

A **better way of avoiding** the error is to **select the cell when referencing** in the formula. This will lead to always entering the correct cell name.

NAME error in Excel** can occur when entering a range**. Check and correct the range name or select the entire range when entering the formula to rectify the error and prevent it.

**Example 3- Named range**

There can be particularly important** ranges in your workbook that you have assigned a name** to. When referencing this range in a formula, **misspelling it can lead to the #NAME error in Excel**.

Here we can see that the range referenced to in the **formula is spelled agee when the correct name is Age**.

**Correcting the name of the range or simply selecting the range** when referencing it in the formula will **correct this error**.

**Example 4- Text without quotes**

Wherever a** text is entered into a formula**, it should be **entered in quotes**. When a **text value is not confined between quotes**, **Excel reads it as either a formula or a named range**/table.

If there is **no formula or named range in your workbook that matches the text written** in the formula, Excel will **return the #NAME?** Error.

Here we can see the **text entered in the formula is not in quotes** and hence Excel is returning it as an error.

Now that the **text is written in quotes**, Excel is returning the correct value of the formula.

Excel shows the #NUM! Error when** values in formulas are invalid**. It basically means that the **calculation cannot be performed due to limitations or errors**.

This can be due to the fact that the **number is too big or small, impossible calculations**, or when the **data type is not supported** in the argument.

**download excel workbook**Num-error.xlsx

**Example 1 – Number size**

Excel has a **limitation to the size of the number** that it can display. If you enter a **number is too large or too small**,you will get** Excel #NUM Error**.

As you can see, in **cell C7** we are trying to **multiply 5 by itself 500 times**. The result will be a **really large number** that Excel is unable to display. Hence. it **returns NUM** Excel.

In cell C10, we are trying to **divide 1 by 10^750**. Since this will be a **very small number** Excel cannot perform calculations and hence returns #NUM error.

Excel is **not able to show these values** because of its inherent limitations. As shown, other smaller values are calculated and shown in C8 and C9.

**Example 2 – Impossible calculations**

Excel returns the **#NUM! Error** when it is faced with a** calculation that is impossible** to do.

As you can see, the** square root or log of the positive numbers has results** but the **square root or log of the negative numbers is impossible to calculate**.

In this example, we are trying to find the **square root of -3**.

Since we are trying to calculate the square root of a negative number, we will get #NUM error in Excel. In this particular case, you can **rectify this issue by taking the absolute value** of the number.

ABS function **returns the absolute value of a number**. It converts negative numbers to positive numbers and keeps the positive numbers unaffected.

Including the **ABS function** into the formula **uses the absolute value of the number** in column A, so now **Excel can calculate the number** and return a valid answer.

**Example 3 – IRR function not working**

Excel might show the #NUM! error when using IRR function because of either one of the two reasons:

- Cash flow
**does not contain at least one negative and one positive value**. - Formula
**can’t find a result**.

In this example, **Excel results a #NUM error instead of proving a value**.

This is because the **cash flows provided in the range (B7:B15) are positive** and **IRR requires at least one negative value** as the initial cost of business.

Initial payments are provided as **negative value indicating outflow of cash** and income payments are represented by positive values. Once you have incorporated this, the function will provide the correct result!

IRR function can also display #NUM! error when the** function iterates** and** can’t find a result after several tries**.

To overcome this, you can **increase the maximum number of iterations** that Excel performs to get a result.

To increase the number of iterations:

**STEP 1:** Go to **File > Options > Formulas** to arrive at the window below

**STEP 2:** Under the **Calculation options** group, check the **Enable iterative** calculation box.

**STEP 3:** In the **Maximum Iterations box**, **type the number of times you want Excel to recalculate**.

*The higher the number of iterations, the more time Excel needs to calculate a worksheet. Increasing this can help give a result to your calculation.*

**STEP 4:** In the **Maximum Change** box,** enter the amount of deviation that you are willing to accept** between the calculation results.

*The smaller the number, the more accurate the result. Increasing this can help give an approximate answer to your calculations.*

**Visual and Navigation Issues**

Sometimes in Excel, especially when you **open a file on which you have worked before**, there may be some **cells that are filled with “#”**** signs.**

Excel #### error is a very simple but common issue that can easily be solved. There are** three simple ways** to tackle this problem.

**download excel workbook** Hash-Error.xlsx

**Method 1:**

Taking your **cursor to the divider between two column** heads, the cursor will **change into a dragger**. **Dragging the column head divider will change the width** of the column.

Now you can increase the width to** fit all the characters** of the cell, or to any width that you want.

**Method 2:**

Simply **double-clicking on the rightmost edge of the column** head, that contains the cell filled with # marks, will **autofit the column** so that it** perfectly adjusts and shows all values** in cells.

It will **adjust to the length of the longest cell** in the column.

Upon applying the above steps, your values will then be visible as shown.

**Method 3:**

Another way to overcome this problem is** auto-shrinking text to fit** in the column width. To accomplish this, follow the steps mentioned below:

**STEP 1: ****Select the column** that shows #### error.

**STEP 2: **Click on the **Home** Tab.

**STEP 3**: Click on **expand icon** at the bottom right corner of the Alignment group.

**STEP 4:** Check the **Shrink to fit** box in the Format Cells dialog box.

Upon applying the steps, the **size of the text will be reduced to fit in the column width** and it will look like this:

A common issue faced while operating on Excel is that your **worksheet tabs (**shown at the bottom left) **are not visible to you**.

This is a **simple settings problem** that can be easily undone.

**download excel workbook** Worksheet-tabs.xlsx

**Problem 1:**

Ever encountered this situation where you open your workbook and said “Where are my worksheet tabs ??”.

A common issue faced while operating on Excel is that all the **worksheet tabs, which are shown at the bottom left of the window, are not visible to you**.

This is a **simple settings problem** that can be easily undone.

**Solution:**

Just follow the steps below to learn how to display the missing worksheet tabs:

**STEP 1:** Go to **Files**

**STEP 2:** Click on **Options**

**STEP 3:** Click on **Advanced**

**STEP 4:** Under the Display options for this workbook, check **Show sheet tabs**.

Now your **sheet tabs will be available**! Now you can **easily navigate through the entire workbook**, and work on all sheets.

This is what to do when worksheet tabs go missing and you are unable to switch between different tabs!

**Problem 2:**

It can also be that some of your **worksheets might be hidden**, hence you will not be able to see them in the worksheet’s tab.

Here, we actually have **two worksheets, but one of them is hidden**.

To show the worksheet tabs:

**STEP 1: Right-click** on any visible tab and select **Unhide.**

**STEP 2: Select the tabs you want to unhide** and press on **OK**.

Now **both the worksheets will be visible** to you on the worksheet’s tab.

**10. Break Line displayed on the Worksheet**

While working on Excel, sometimes you may** see solid or dashed break lines added** and the **worksheet is divided into multiples pages**.

The **solid lines are manually inserted ones** whereas the **dashed line is automatically generated**.

These are basically **page breaks inserted in Excel for printing purposes**! They may be useful when you are trying to print a document but other times are just **visually distracting**.

You may want to **remove these lines before presenting** your worksheet to someone!

In this article, we will cover the **different ways to remove lines** from the worksheet:

**Solid Page Break Line****Dotted Page Break Line**

In this screenshot, we have **two solid lines inserted** in the worksheet.

These are **manually added page breaks** and can easily be removed by following the **step-by-step tutorial** below:

**STEP 1:**** Select the cell** just below the page break line.

**STEP 2:** Go to the **Page Layout** tab.

**STEP 3:** Click on **Breaks** in the Page Setup group.

**STEP 4:** Click on **Remove Page Break**.

This will remove the page break between row 9 and row 10!

Now, follow the same steps to remove the solid line between rows 15 and 16.

When you select the **Page Break Preview** in Excel, the **dotted and solid lines** dividing the worksheet into different pages become visible.

Now when you go to **View > Normal**, the page preview is returned to normal but the lines still visible.

To remove the solid lines, follow the steps mentioned below. But, this will not remove the dashed lines.

To remove the dashed line, follow the steps below:

**STEP 1:** Select the **File** tab.

**STEP 2:** Click **Options** from the menu on the left side

**STEP 3:** In the dialog box, click on **Advanced** from the left panel

**STEP 4:** Under display options for this worksheet, **uncheck Show Page Breaks**.

This will remove the dashed lines from the worksheet as well!

**11. Clicking Enter key goes to the cell underneath**

In general, when you **press enter in Excel** it **solidifies the work done** in a cell but it automatically** takes you to the cell below the one you were working on**. If this is a** hindrance to you, you can easily change** it.

**Solution:**

To alter the direction in which Excel files after pressing enter, follow these steps:

- Click on the
**File tab**on the ribbon above

- Click
**Options**from the menu on the left side

- In the dialog box, click on
**Advanced**from the left panel

- Under Editing Option, Select
**After Pressing Enter Move Selection,**and from the drop-down menu the choose the direction in which you want Excel to move.

Now Excel will move in the direction you want after pressing enter. Now you try it!

**12. Show column headers on each page when printing**

If you have a **large data file with headers on the top**, you can **freeze** the columns to easily view the entire table without losing sight of the headers.

But the** freeze feature will not be applied when you try to print** the file.

To print** Excel header row on every page**, you can try one of the following methods as per your requirement:

**Repeat row on every page****Repeat column on every page****Print row numbers and column letters**

**Example 1: Repeat row on every page**

If you have a large worksheet that spans over multiple pages, you can easily **print the first row of the data as headers in Excel on every page**.

Follow the steps below to print a row of every page:

**STEP 1:** Go to Page Layout tab > Select Print Tiles

**STEP 2:** In the Page Setup dialog box, select **Sheet**.

**STEP 3:** Select the collapse dialog box and pick the row you wish to repeat.

**STEP 4:** Click **OK**.

**STEP 5:** Press Ctrl + P to open Print Preview.

As you can see we are on page 5 but the 1st row is still visible!

**Example 2: Repeat Column on every page**

When your Excel worksheet has multiple columns and is spread across several pages, you can fix the first column.

Follow the** steps below** to fix the first column when printing:

**STEP 1:** Go to** Page Layout** > Select **Print Tiles**

**STEP 2:** In the Page Setup dialog box, select **Sheet**.

**STEP 3:** Select the **collapse dialog box** and pick the column you wish to repeat.

**STEP 4:** Click OK

And it’s done! Column A is still visible when you go to page 2.

**Example 3: Print row numbers and column letters**

Excel denotes the worksheet’s columns as letters (A, B, C, etc) and rows as numbers (1, 2, 3, etc). But when you try to print your worksheet, it is not visible at all.

You can easily print numbers and column letters by following the steps below:

**STEP 1:** Go to** Page Layout** > Select **Print Tiles**

**STEP 2:** In the Page Setup dialog box, select **Sheet**.

**STEP 3:** Check **Row and column headings**

**STEP 4:** Click **OK**.

**STEP 5:** Press** Ctrl + P** to view Print Preview.

The row numbers and column letters will be displayed in the print preview section!

**Operational Issues**

**13. Not using Quotation Marks for Text in Formulas**

When writing formulas, Excel will **identify a text only if it’s written within quotation marks**. Excel e**xtracts anything written within the quotation mark as text** and discards the quotation mark.

Let’s use a formula to use to help better understand the use of quotation marks while writing a formula!

**Download workbookQuotation-Mark.xlsx**

In this example, we have tried to **insert text like “Email ID of” and ” is”** but Excel is returning an **error**. This is simply because Excel will** not consider text if it is not provided within quotation marks** in a formula.

Now,** put them within quotes** and try using this formula again.

And Voila, the error is resolved and we can now insert custom text within the formula easily.

**14. How to Use Numbers as Column Headings**

You may have faced a situation when you need to** use numbers as column headings in the table**. But, when you put in digits as heading, **Excel will treat it as a number and not text**.

As a result, when using** auto sum or other similar features the heading will also be included** with other values.

**Download workbookNumber-as-Column-Header.xlsx**

This is **not the result you were expecting**! Let’s **fix it** by forcing Excel to consider the number as text.

To do that, simply **add a ‘ symbol in front of the number** and it will be treated as text.

See, it is that simple! Try it now!

**15. Unable to Directly Edit the Cell**

If you are unable to edit data entered in your Excel worksheet and you are not sure how to fix it, you have come to the right place. There are **several ways in which you can easily enable editing** cells and learn how to edit cells in Excel.

**Excel file is read-only****Excel worksheet is locked****Editing in cell disabled in Excel**

**Example 1: Excel file is read-only**

You may **not be able to edit any cell** in Excel because the** Excel worksheet has been saved as read-only**. This will force Excel to** keep the worksheet in view mode and you can make no changes** in the file.

To fix this, simply **press the Enable Editing **button on the top of the Excel worksheet.

On you press that button, you can easily edit any cell in Excel.

**Example 2: Excel worksheet is locked**

Another reason why you may not be able to edit cells is that your **Excel worksheet has been locked**. You have to **unprotect your worksheet first** and then you can make changes in your file.

To make changes in Excel, follow the steps below:

**STEP 1:** Go to the **Review** tab

**STEP 2: **Click on **Unprotect Sheet**

**STEP 3: Enter the password** in the unprotect sheet dialog box and click **OK**.

*The password for this worksheet is “myexcelonline”.*

This will unprotect the sheet and you will be able to make the edits you want.

Can’t remember the password that you had used to lock your Excel file? **Click here** to learn how to unprotect active worksheets using macros in Excel.

**Example 3: Editing in cell disabled in Excel**

You might **not be able to directly edit the contents** of a cell by pressing the **F2** key.

This might be because it has been** disabled in Excel. **You can easily enable or disable this function by following the steps below:

**STEP 1:** Click on the **File tab**

**STEP 2:** Click **Options** from the menu on the left side

**STEP 3:** In the dialog box, click on **Advanced** from the left panel

**STEP 4:** Under Editing option, check **Allow editing directly in cells. **Click **OK** at the bottom.

That’s it! Now you can enable the editing cell feature in Excel. To disable Edit mode, clear the **Allow editing directly in cells** check box.

**16. Unable to use the Fill Handle**

Excel has many** tools that can automate your work** and make it much easier and faster. One of these** essential tools is the fill handle**. It helps** fill in a value in the adjoining cells in sequential order** of data.

If you are** unable to use this feature**, it may have been disabled or the **filled values are not as per your pattern:**

**Fill Handle Excel – not visible****Not selecting all values****Auto Fill options**

**Download workbookFill-handle.xlsx**

**Example 1: Fill Handle not visible**

If you are** unable to use this function**, you can simply fix it using the steps below:

**STEP 1:** Select the **File** tab.

**STEP 2:** Click **Options** from the menu on the left side

**STEP 3:** In the Advanced category, under Editing options, select the** Enable fill handle** **and cell drag-and-drop** check box.

Once you have enabled this, you can easily use Excel Fill handle feature!

**Example 2: Not selecting all values**

It can even be that you have** not selected the entire range that has your sequential values**. **Just selecting the last cell will fill only that value in the cells** where you drag the fill handle in Excel.

For example, if we drag the fill handle after only selecting cell B8, the entire range is filled only with the value “1002”.

**Selecting the entire range** and then using the fill handle will **fill the new cells in sequential order**.

**Selecting B7 and B8 will help Excel recognize the pattern** and correctly fill the subsequent cells.

**Example 3: Auto Fill options**

Suppose you have** entered the date** and you want to **use the fill handle to get only weekdays**. If you **use the fill handle like before**, it will just** add the next dates in adjacent cells**.

You can **use the AutoFill option** to get dates that are only falling on weekdays!

**STEP 1:** Select the entered values.

**STEP 2:** Go to the **bottom right of your selection**, you can** drag the fill handle **to fill dates below.

This will fill the dates one after the other!

**STEP 3:** Click on the **Fill Handle button** at the bottom right.

**STEP 4:** Select **Fill Weekdays**.

This will just **include the weekdays and ignore the weekends** like 4-Jan-20 and 5-Jan-20.

You can use other options as well like Fill Formatting only, Fill Months, Flash Fill, etc.

**17. Not able to understand the inner workings of a formula**

Excel provides a lot of functions at your disposal for efficient working and data management. Formulas can be **used widely in Excel to overcome or calculate all sorts of different informational** data.

But these formulas can be **tough to decipher at first, especially the working** of a formula. Here is how you can better understand a formula:

**Solution:**

When you enter a formula in the cell, you can **press Ctrl+A to enter the function arguments** window. Thi**s shows a detailed explanation of each segment of the formula** so that you know which data to feed in which segment and get accurate results.

To better understand this, let us take the example using the VLOOKUP formula. Here, we need to **extract the salary of Rachael in cell D15**.

**download excel workbook**Understand-Excel-Formula.xlsx

Let’s open the arguments wizard for the VLOOKUP formula.

**STEP 1: **Enter the VLOOKUP function: **=VLOOKUP(**

**STEP 2: **Press **Ctrl+A** or click on the **fx button** in the formula bar to **open the arguments wizard** window.

This is how the formula wizard looks like.

It **shows all the arguments** in sequential order. It then **explains the use of the function **and the **details of the argument** selected.

**STEP 3: **Select the **first argument i.e. Lookup_value**. Here, cell C15 contains the name, Rachael.

You can also see the **description of the first argument** is written at the bottom of the dialog box.

**STEP 4: **Select the **second argument i.e. Table_array** and **select the table range** from where you can search i.e. **A7:C13**.

**STEP 5: **Select the **third argument** i.e. **col_index_num** and enter the column number from which to extract the data. In this example, it should be 3.

**STEP 6: **Lastly, select the** fourth argument** i.e. **range_lookup**. Since we want an exact match type **FALSE or 0**.

After entering the details, the wizard would look like below. It would even show a** preview of the argument values and the answer below** it if the inputs were correct.

**STEP 7: **Press **OK**.

The** formula will be entered in cell D15** and it will show the result, i.e., the **salary of Rachael – $16,540**.

Ctrl+A opens the Function Arguments window immediately after typing the name of the function.

Now that you can easily understand each formula, have fun working on Excel!

**18. Cells showing formulas instead of calculated values**

While working on an Excel worksheet with a lot of formulas in it, it can become **challenging to keep track of all your formulas**.

It may happen that the **formulas entered in each cell is displayed on the worksheet instead of the value/result**.

You can easily reverse this and display the result instead of formula. Let’s see how it can be done!

**download excel workbook**Showing-Formula.xlsx

**Solution:**

**Ctrl + `** is the shortcut for the formula view, pressing it can change it back to normal view and show your calculated values.

Now you have your** cells showing calculated values instead of formulas** applied. Try it yourself!

**19. Formula does not update when value changes**

Excel provides** dynamic formulas that update their calculated value as soon as the source data is altered.**

An issue faced by people can be that the **cells are not updating automatically** ,i.e, they are showing the old values even when the dependent cell’s values have been changed. Here is how you fix this issue.

**Solution:**

The issue that you are facing is most likely caused by an accidental change in the **calculation setting from Automatic to Manual**. Steps to change this are:

Go to **Formula Tab** > **Calculation Options** >** Automatic**.

Now your formulas will be getting updated which changes in the source data. Now you try it!

**20. Formula not working due to text format in cells
**

Sometimes even when have **entered a number in the cell, it is stored as text** and thus produce **error while computing** in Excel.

**download excel workbook**Formula-not-working.xlsx

In this example, cell F7 uses** sum function to calculate the total amount** for all 4 quarters. Even though there are figures in range B7 to E7 the **calculated amount in cell F7 is 0**.

This is because each of these **number are actually stored as text**.

**Solution:**

**Select the cells** that are the source for the formula calculation. Click on the** yellow triage icon** and select **Convert to text.**

This will **convert the text to number** and now the calculated amount will also display the correct value!

It is that simple! Now you try it!

### Conclusion

This completes our tutorial on the **Top 20 common errors like REF error, NUM error, Excel #### error, etc. that we can encounter while working on Excel**.

Now that these common issues are out of your way**, we hope that your Excel operations have improved**. If you want to continue this journey of improvement, you can** further check out our blog as well as our YouTube** channel. They offer a ginormous amount of content, a huge library of information provided to you for free.

See you next time!

Make sure to download our FREE PDF on the** 333 Excel keyboard Shortcuts here:**

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.