Top 20 Common Excel Problems Solved | MyExcelOnline

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

1. #REF! Error

#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: 

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

#ref! error in excel

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 WORKBOOK

 

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

STEP 5: Click on Replace All.

Top 20 Common Excel Problems Solved | MyExcelOnline

This is how your replaced data will look like:

Top 20 Common Excel Problems Solved | MyExcelOnline

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).

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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:

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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).

Top 20 Common Excel Problems Solved | MyExcelOnline

 

2. #VALUE! Error

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 WORKBOOK

 

Example 1:

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

#value error in excel

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

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

Top 20 Common Excel Problems Solved | MyExcelOnline

 

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!

#value! error in excel

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

All the errors will now disappear!

Top 20 Common Excel Problems Solved | MyExcelOnline

 

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.

#value error

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

STEP 1: Select the cells containing dates.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

STEP 4: Press Replace All button.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

 

3. #DIV/0 Error

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 

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.

#DIV/0!

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

Like so!

Top 20 Common Excel Problems Solved | MyExcelOnline

 

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

=IFERROR(A7/B7,”-“)

#DIV Error in Excel

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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 (-).

 

4. #NULL! Error

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

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.

#null error in excel

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(:).

Top 20 Common Excel Problems Solved | MyExcelOnline

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:

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

 

5. #SPILL! 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

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

Solution:

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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!

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

 

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

The result is now visible and the error disappears!

Top 20 Common Excel Problems Solved | MyExcelOnline

 

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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!

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

 

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.

#spill excel

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

 

6. #NAME? Error

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

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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).

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

 

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.

#name error in excel

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

Top 20 Common Excel Problems Solved | MyExcelOnline

 

7. #NUM! Error

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

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

#num! error

 

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

 

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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!

Top 20 Common Excel Problems Solved | MyExcelOnline

 

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

 

Visual and Navigation Issues

8. Excel #### error

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.

Excel #### error

DOWNLOAD EXCEL WORKBOOK

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

 

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

 

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.

How to correct a ##### error

STEP 2: Click on the Home Tab.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

 

9. Worksheet Tabs not Visible

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

DOWNLOAD EXCEL WORKBOOK

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.

excel unhide all

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

Top 20 Common Excel Problems Solved | MyExcelOnline

STEP 2: Click on Options

Top 20 Common Excel Problems Solved | MyExcelOnline

STEP 3: Click on Advanced

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

To show the worksheet tabs:

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

 

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.

break line

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

DOWNLOAD EXCEL WORKBOOK

 

Solid Page Break Line

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

STEP 2: Go to the Page Layout tab.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

STEP 4: Click on Remove Page Break.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

 

Dotted Page Break Line

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

To remove the dashed line, follow the steps below:

STEP 1: Select the File tab.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

 

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

Top 20 Common Excel Problems Solved | MyExcelOnline

  • Click Options from the menu on the left side

Top 20 Common Excel Problems Solved | MyExcelOnline

  • In the dialog box, click on Advanced from the left panel

Top 20 Common Excel Problems Solved | MyExcelOnline

  • 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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

DOWNLOAD EXCEL WORKBOOK

 

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

STEP 4: Click OK.

Top 20 Common Excel Problems Solved | MyExcelOnline

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!

excel header row

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

STEP 1: Go to Page Layout > Select Print Tiles

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

STEP 4: Click OK

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

 

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

STEP 1: Go to Page Layout > Select Print Tiles

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

STEP 3: Check Row and column headings

Top 20 Common Excel Problems Solved | MyExcelOnline

STEP 4: Click OK.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

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

Top 20 Common Excel Problems Solved | MyExcelOnline

 

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 extracts 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!

Top 20 Common Excel Problems Solved | MyExcelOnline

DOWNLOAD WORKBOOK

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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 WORKBOOK

 

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

How to edit cells in Excel

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

Top 20 Common Excel Problems Solved | MyExcelOnline

 

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

To make changes in Excel, follow the steps below:

STEP 1: Go to the Review tab

Top 20 Common Excel Problems Solved | MyExcelOnline

STEP 2: Click on Unprotect Sheet

Top 20 Common Excel Problems Solved | MyExcelOnline

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

The password for this worksheet is “myexcelonline”.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

 

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 WORKBOOK

 

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

 

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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”.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

STEP 1: Select the entered values.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

This will fill the dates one after the other!

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

Top 20 Common Excel Problems Solved | MyExcelOnline

STEP 4: Select Fill Weekdays.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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. This 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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

STEP 1: Enter the VLOOKUP function: =VLOOKUP(

Top 20 Common Excel Problems Solved | MyExcelOnline

 

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

 

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

Top 20 Common Excel Problems Solved | MyExcelOnline

 

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

 

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

Top 20 Common Excel Problems Solved | MyExcelOnline

 

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

STEP 7: Press OK.

Top 20 Common Excel Problems Solved | MyExcelOnline

 

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

DOWNLOAD EXCEL WORKBOOK

Solution:

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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.

Top 20 Common Excel Problems Solved | MyExcelOnline

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

Top 20 Common Excel Problems Solved | MyExcelOnline

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!

HELPFUL RESOURCE:

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

Microsoft Excel Training

You can learn more about how to use Excel by viewing our FREE Excel webinar training on FormulasPivot TablesPower Query, and Macros & VBA!

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn