Excel Guides

How to remove hash marks displayed instead of cell contents in Excel

If you are seeing hash marks (#) instead of cell contents in Microsoft Excel, it is likely that the cells are formatted as text. When cells are formatted as text, anything you type into the cells will be treated as text, even if you type numbers or dates. This can cause problems when you try to use the cells in formulas or sort them, because Excel expects numbers or dates in those cases.

There are a few ways to fix this. The first is to select the cells that are displaying hash marks and format them as General. You can do this by selecting the cells, then going to the Home tab on the ribbon and clicking the General button in the Number group. This will remove the text formatting from the cells and allow them to be used in formulas and sorted correctly.

If you want to keep the cells formatted as text but still be able to use them in formulas or sort them, you can use the VALUE function. For example, if cell A1 contains the text "10", you can use the formula =VALUE(A1) to convert it to a number. This will allow you to use it in other formulas or sort it correctly, but it will still be displayed as text in the cell.

You can also prefix your cell values with an apostrophe ('). This tells Excel to treat the value as text, even if it looks like a number. For example, if cell A1 contains the number 10, you can type '10 into the cell instead and it will be treated as text. This can be useful if you need to enter a lot of data that includes both numbers and text values.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.