Why is Excel not showing number?

Let us assume the following situation: You have received an Excel file which looks actually quite nice. But you notice something: The rows and column headings missing. That means, the numbers 1, 2, 3 on the left-hand side are gone and the letters A, B, C are also not shown. Here is how to get them back!

Table of Contents Show

The problem: Row numbers and column headings missing
Headings missing on multiple sheets?
Further reading
Post navigation
"Easily make Excel recognize the numbers in your selection"
ASAP Utilities compared to Excel's built in "Convert to number"
When compared to Excel's built-in solution, these are the benefits of ASAP Utilities:
Do you recognize any of these situations?
Bonus tips, also interesting
How much time will it save?

Contents

The problem: Row numbers and column headings missing
How to get missing row numbers and column letters back
Headings missing on multiple sheets?
Further reading
- Also interesting:

The problem: Row numbers and column headings missing

Does your Excel window look like this?

No row numbers and column letters are shown.

The row numbers and column headings are missing. So, there is not written A, B, C and so on above the columns. And 1, 2, 3… is missing to identify the rows.

However, you can still see the cell “address” (here C4, highlighted in yellow), right?

Then, it’s time now to get them back!

Follow these two steps to show row and column headings:

If the column letters and row numbers are missing, go to View and click on “Headings”.

In order to show (or hide) the row and column numbers and letters go to the View ribbon. Set the check mark at “Headings”. That’s it!

Headings missing on multiple sheets?

Maybe you have noticed it: You have to do this for all worksheets again (assuming that headings are missing on multiple worksheets). So, how to get the headings back on all sheets with just a few clicks?

Select all worksheets (hold down the Ctrl key and click on the tabs or alternatively press and hold the Shift key and click on the sheet tabs – that way you also select the sheets in-between). Now, do the steps as shown before: Go to the View ribbon and click on “Headings”.

Do you want to boost your productivity in Excel?

Get the Professor Excel ribbon!

Add more than 120 great features to Excel!

"Easily make Excel recognize the numbers in your selection"

Sometimes Excel fails to recognize numbers properly, which causes unexpected results when you sort, filter, or use formulas.

For example, when you import a file that was created in another program, such as an accounting package, downloaded from a mainframe or copied from a website, then Excel may fail to recognize the numbers.

Some Excel values may then look like numbers, but Excel thinks they are text. These numbers are then usually left-aligned (by default, text is left-aligned and numbers are right-aligned in Excel).

You sometimes may experience that:

A formula isn't calculating your numbers properly, such as the SUM of cells, because the 'text'-number are ignored.
Excel does not allow you to change the number format of selected cells.
The sort order is confusing and incorrect.
You have fractions in your cells that Excel doesn't recognize and can't calculate, such as 1/4.
Excel's built in error checking option and correction isn't available or does not fix the numbers.

But this is easy to fix. Just select the cells and use:
ASAP Utilities » Numbers & Dates » Convert unrecognized numbers (text?) to numbers

This "Convert unrecognized numbers (text?) to numbers" tool quickly fixes the numbers in your selection.
It will:

Remove leading and trailing spaces around cells with numbers.
Remove apostrophes in front of numbers.
If the number format in the cells with numbers is "Text" then it will be changed to "General" in these cells.
Handle non breaking spaces (this so called character, char 160, is commonly used in web pages as the HTML entity,  ).
Remove leading zeros.
Recognize and fix text-numbers that are displayed as fractions (such as 1/4, 1/2, etc.)
Preserve long numbers such as credit cards. Unlike Excel, it will not change the last digit to a zero such as from credit card numbers, which is what Excel's built-in "Convert Text to Number" does.
Empty cells that only contain one or more spaces.
Turn all empty cells into truly empty/blank cells that Excel recognizes as being empty.
Skip cells with formulas or text.

Tip: If your formulas don't give the correct result or don't calculate, then in addition to fixing the numbers, you may also want to verify that the calculation of your workbook is set to Automatic (or press F9 to calculate when calculation is set to Manual):

ASAP Utilities compared to Excel's built in "Convert to number"

Excel (2002+) has built in "Error Checking Options", which can help to detect numbers stored as text and help sometimes to fix them. Numbers that are formatted as text are left-aligned instead of right-aligned in the cell, and are often marked with an error indicator.

If the cells in which numbers are displayed as text contain an error indicator in the upper-left corner (a small green triangle), you can select that cell and then click the error button next to the cell. Then you can choose "Convert to Number" on the pop-up menu.

While this is a handy tool, it doesn't always detect all numbers that are stored as text properly and you cannot choose your own range to run Microsoft Excel's built-in "Convert to Number" tool on.

When compared to Excel's built-in solution, these are the benefits of ASAP Utilities:

You decide which cells to convert and when. It even works in multiple non-adjacent selected ranges.
You can assign your own shortcut to this tool for quick use.
It fixes numbers that Excel can't.

Do you recognize any of these situations?

I copied some numbers on a web page and pasted them onto an excel spreadsheet. When I use the sum formula, or average formula, excel is not recognizing the numbers in the cell, and won't add up my rows. I've tried reformatting the field as a number, but it's not working. If I re-type the numbers in the field, then Excel recognizes them. This is frustrating!
I am trying to find the average for a list of numbers. These numbers were pulled from a database, run through a mail-merge program and then given back to me. They look like normal numbers, and don't seem to have any odd formatting, but the AVERAGE function does not seem to see them, and returns a #DIV/0! error. When I click on the cells as if I am going to change the data, the numbers are suddenly recognized by the formula as if they have changed format somehow. Is there some way I can change the format of these numbers, I don't want to have to double click on thousands of cells to miraculously change them to be recognized....
I have a user with a query from an SQL Database that returns a series of strings that are actually numbers, but Excel treats them as text-strings.
I've copied a column of data (numbers) from a website and pasted it in Excel. However, the numbers are not recognized as numeric values (we can't sum them, etc.). We have tried all of the "easy" solutions such as changing their format to general or number, copying a 1 from another cell in a clean workbook and using the paste special/multiply function... We've tried the text to columns thing, basically have tried everything in the MS KB, but nothing works except for retyping the value in each cell, which is a bummer since we've got about 2000 cells. Any solutions?
Excel will not change the format of my cells. I have copied a table that I found online. The problem after the import is that Excel will not change the format of selected cells. I am trying to change some cells to a number format. The only way it will accept the change is to clear the cell and retype it. There are too many numbers to make that fix acceptable.
I have a bunch of numbers that are preceded with an apostrophe. If I use Excel's Find/Replace and try to replace the apostrophe with nothing, Excel will not recognize it, and nothing will happen. How can we fix this quickly without having to re-enter the numbers?
Among my data I had creditcard numbers and Excel's Error Checking stripped the last numbers from it.
My =VLOOKUP() formulas can't find any matching numbers because of unwanted spaces.
Cells that appear empty aren't because they contain a space.
Excel doesn't recognize some cells as being empty. That happens often with imported data.
I tried to remove the spaces by using Excel's =TRIM() function but it didn't remove the spaces in my case. I found that this can happen with so called "non-breaking" spaces. See also this article from Microsoft that describes how to remove these unwanted spaces.

Just select the cells and then choose the following tool in the Excel menu:
ASAP Utilities » Numbers & Dates » Convert unrecognized numbers (text?) to numbers

We find that this tool is often easier, quicker and also more reliable than Microsoft's advised and built-in methods (text to number/paste special workaround, or the text to columns workaround).
And to increase your productivity you can assign your own shortcut to this tool.

Bonus tips, also interesting

ASAP Utilities » Format » Clean data and formatting...
ASAP Utilities » Numbers & Dates » Convert/recognize dates...
ASAP Utilities » Numbers & Dates » Change values to text values (adding ' in front)
ASAP Utilities Tip: Easily remove certain or several characters at once
ASAP Utilities Tip: Easily remove leading characters
ASAP Utilities Tip: Quickly remove the leading zeros from numbers
ASAP Utilities Tip: Get rid of unwanted spaces in your data
ASAP Utilities Tip: Quickly (un)protect all sheets at once

How much time will it save?

It's guaranteed that you'll save yourself time and effort by using this tool. However, the actual time saved depends on how much you use Excel, the amount of data you are working with and how often you use this particular tool.
You can easily see how much time ASAP Utilities has saved you so far.

Download

In case you don't have ASAP Utilities yet, you can download the free Home&Student edition (for home projects, schoolwork and use by charitable organizations) or the fully functional 90-day Business trial.
Download page