Lattice instead of numbers in Excel. What to do if lattices are displayed instead of numbers in Excel

It often happens that when entering data into Microsoft Excel, special characters, such as pound signs, are displayed instead of certain numbers. This circumstance prevents the normal operation of the electronic document, so you need to know how to fix this problem. This article presents several effective ways to quickly resolve the problem.

Reasons for the appearance of lattices

Lattice cells appear when the number of characters entered in them exceeds the limit. At the same time, the program remembers the data that you entered, but it will not display them correctly until the extra number of characters is removed. If when entering numbers in a cell Excel 2003 exceeded the number of 255 units, it will display octothorp instead of numbers. This is what a lattice is called in a programming language.

In the same way, the text will show itself if you enter it in a cell of a more recent version. The maximum number of letters allowed in an Excel 2007 field is 1024. This is typical for Excel products prior to 2010. Newer versions no longer provide for a limit. Also, the reasons may be:

• the presence of grammatical errors in the text or invalid characters;
• erroneously calculated amounts;
• incorrect application of formulas and incorrect calculations in cells;
• failures at the program level (this is determined in the following way: if when you hover over a cell, everything is displayed correctly, and when you press “Enter”, the value turns into an octotorp, then it’s still an extra number of characters).

Pay attention! The appearance of bars in Excel fields may be the result of an incorrectly set keyboard layout.

Also, a similar problem may appear if the wrong cell names were selected for summarizing the data. You can solve the problem with displaying personal data using several effective methods.

The solution

Simply deleting an extra number of characters is not enough. You will have to use methods that make incorrect characters disappear. Let’s move from simple to complex.

Method 1: manually expanding the boundaries

To expand the borders in Microsoft Excel, it is enough to stretch them manually. This is a reliable and simple way that will help solve the problem even for beginners who first used the functionality of the office application.. Follow instructions:

1. In the Microsoft Excel window that opens, click on the cell in which the bars appeared.
2. Move the cursor to the right border, where the cell name is set. The cell borders can also be stretched to the left, but in this direction, the cells located in front will be shifted.

3. We are waiting for the cursor to take the form of a double-sided arrow. Then click on the border and drag until the position until all the characters appear.
4. At the end of the procedure, all lattices will be displayed in the form of previously entered numbers.

This method works for all versions of Excel.

Method 2: Decreasing the Font

The first solution to the problem is more suitable for those cases when only 2-3 columns are occupied on the sheet and there is not much data. But in order to fix special characters in the e-book on a large scale, you need to use the instructions below.

1. We select a cell or a range of cells in which we want to visualize numerical data.

2. We make sure that we are in the “Home” tab, if not, then click on it at the top of the page. In the “Font” section, we find its size and reduce it until the required number of characters is displayed in the cells in the required digital format. To change the font, you can simply enter the estimated size in the appropriate field.

On a note! When editing the font and changing the format, the cell will take on the width that corresponds to the longest numeric value that is written inside it.

Method 3: autowidth

Changing the font in cells is also available in the way described below. It involves selecting the width using the built-in tools of Microsoft Excel.

1. You need to highlight the range of cells that need formatting (that is, those that contain invalid characters instead of numbers). Next, right-click on the selected fragment and in the pop-up window find the Format Cells tool. In earlier versions of Excel, the menu may change the location of the tools.

2. In the window that appears, select the “Alignment” section. We will work with it in the future, then put a tick in front of the entry “Auto-fit width”. It is located below in the “Display” block. At the end, click on the “OK” button. After the actions taken, the values ​​decrease and acquire a format corresponding to the size of the window in the e-book.

This technique is very convenient and is distinguished by its efficiency. You can design an Excel sheet properly in just a matter of seconds.

Pay attention! All editing methods are valid only if you are the author of the file or it is open for editing.

Method 4: Changing the number format

This method is only suitable for those who use the old version of Microsoft Excel. The fact is that there is a limit on the introduction of numbers, as mentioned at the beginning of the article. Consider the fix process step by step:

1. Select the cell or range of cells that needs to be formatted. Next, right-click on them. In the list of functions that appears, find the “Format Cells” tool, click on it.
2. After we click on the “Number” tab, we see that the “Text” format is set there. Change it to “General” in the “Number Formats” subsection. To do this, click on the latter and confirm your action by clicking on the “OK” button at the bottom of the formatting window.

Pay attention! In updated versions of Excel, the General format is set by default.

After this restriction is removed, all numbers will be displayed in the desired format. After the done manipulation, you can save the file. After reopening, all cells will be displayed in the correct form.

You can change the number format in another convenient way:

1. To do this, enter the Excel spreadsheet file, where numerical values ​​​​are indicated incorrectly, go to the “Home” tab to the “Number” section.
2. Click on the arrow to bring up the drop-down list and change the set mode from “Text” to “General”.
3. You can format one of the cells, in which there are a number of grids, in a single order, without resorting to choosing formats for the entire sheet. To do this, click on the desired window, click the right mouse button.
4. In the pop-up window, find the Delimited Format tool, click on it.
5. Further, all parameters must be changed as described in the previous method.

On a note! To quickly switch to cell formats, just use the key combination “CTRL + 1”. It is easy to make changes here, both for one specific cell and for the whole range.

To make sure that the actions performed are correct, we recommend that you enter text or numeric characters in large numbers. If, after the limit was exhausted, the gratings did not appear, respectively, you did everything right.

Method 5: Change the cell format

It is possible to change the cell format for the correct display of characters using several tools that are used by default in the Microsoft Excel spreadsheet. Let’s look at this method in more detail:

1. First, select the problematic cell, then right-click on it. A menu pops up in which you need to click “Format Cells”. Cell formatting is performed only in the “Numeric” form, if the workbook contains numbers.

2. In the “Number” block that opens, from the list, select the format that the entered value in the cells will correspond to. In this example, the “Money” format is considered. After selection, we confirm our actions by clicking on the “OK” button at the bottom of the settings window. If you want a comma to appear in the numbers, you must click on the “Financial” formatting option.

3. If you don’t find a suitable formatting option in the list, try returning to the Home page and going to the Number section. Here you should open the list with formats, and at the very bottom click on “Other number formats”, as shown in the screenshot. By launching this option, you will move to the already familiar settings for changing the cell format.

If none of the methods helped, you can try entering values ​​not in a cell, but in a line located under the control panel of the Microsoft Excel e-book. Just click on it and start entering the necessary data.

Conclusion

In most cases, displaying grids instead of numeric or alphabetic expressions in Microsoft Excel cells is not a mistake. Basically, such display of characters depends solely on user actions, so it is important to pay attention to compliance with the limit when using older versions of the spreadsheet.