Accustomed to the existing order of things, we think little about the fact that someone can live according to other laws. In fact, this is not the saying of the Dalai Lama, but a description of one of the office problems that Excel regional settings may cause. Its essence lies in the fact that in different countries, even using the same Arabic numerals, it is customary to write down the numbers in slightly different ways. A striking example of this difference, which usually leads to problems, is the sign that separates the fractional part of a number from the whole. In Russia, and indeed in the former post-Soviet space, it is always a comma, but in the countries of the West - a point.
Point value in Excel
Let's look at what undesirable consequences a “wrong” or, more precisely, incorrect separator can lead to in a system. In most cases, a number containing a point will be interpreted by Excel as a date, and, accordingly, instead of “25.11”, you will receive in the cell “25. November”.
When working with a large amount of data in a table, you may not immediately notice such a conversion. Moreover, not every number can be so distorted. For example, "25.13" will remain unchanged. As you probably already guessed, the conversion will affect only those numbers where the numbers from 1 to 12 will be before or after the point. It is this range that is used to indicate the months of the year and, therefore, it is perceived as a date. If such numbers fall into one or two per hundred, then all this will manifest itself only at the stage of calculation.
Replace the point
There are several options for how to put commas in Excel. We will consider only the simplest and most practical that any user can use, regardless of the level of computer literacy.
How to replace a dot with a comma in Excel using the parameters of the program itself? To do this, go to the “Parameters” section of the table editor and go to the “Advanced” menu. In this section, you will need to uncheck the box in the "Use system separators" item. By unblocking the following two points in this way, you can change the separation signs to the ones necessary for working with your data.
How to replace the dot with a comma in Excel using the operating system settings? In the Windows Control Panel, you will need to find the "Language and Regional Standards" section. In the window that opens, on the "Format" tab, switch to the "Advanced Options" submenu and on the "Numbers" tab change the separators of the integer and fractional parts to the necessary ones.
How to replace a dot with a comma in Excel using the Search and Replace function? This menu is probably familiar to most users and its use will not require additional comments. The only important point that needs to be made is the need to first select the editable area. If you do not perform this operation, Excel will change all available points on the sheet and, eliminating the discrepancy in one part of the table, you can get it in another.
Another old but proven way is to preliminarily replace separators in any simple text editor. A regular “Notepad” from the standard Windows programs can perfectly cope with such a task. The mechanism of actions is similar to the last described method and consists in simple replacement of characters with the ones needed by your spreadsheet editor.
Differences in the office suite menu
In the office suite from Microsoft, with each new version, the arrangement of menu items changes slightly. Excel 2013 was no exception. In addition to design changes that affected the color schemes and the appearance of the icons, the Parameters item was shifted on the command ribbon. He moved from the bottom of the main settings drop-down menu to its side.
In all other respects, user actions in Excel 2013, using the tips from the previous section, are completely similar to working with earlier versions. As soon as you open the settings menu that has shifted to the side, you will be taken to the familiar interface.
To summarize, then all versions of the office, with different colors for each program, contain the options item in the sidebar. These include the 2013 version and all subsequent, more modern options.
Underwater rocks
Now, understanding how to replace a dot with a comma in Excel, we will deal with the features of the methods described. Changing the operating system or software settings, of course, can help in case of a one-time need, but constantly working in this mode is not very comfortable.
Much preferable from the point of view of practicality is a one-time data replacement in the required area using the search menu. In this case, you do not risk distorting your data in other tables and can visually control the process of changing fractional separators.
Text format and calculations
In addition to everything described, there is another interesting feature of the Excel spreadsheet editor. Text entered by copying may in some cases not be recognized as digital information. The cell in which the digit was saved as text will be indicated by a green triangle in the upper left corner. Outwardly, everything looks decent, the numbers are not distorted, but when you try to make calculations, the data from such a cell will not be taken into account.
Thus, a correctly looking table will produce an incorrect final result if calculations are made in it. This problem is solved by regular Excel tools. By selecting a cell with a green corner, a pop-up exclamation mark in a yellow triangle can be found nearby. Clicking on it will bring up a small context menu. Selecting the upper option "Convert to Number" in it, you can change their presentation without losing data.
After such a simple operation, all calculations will be performed correctly. To speed up, you can select not one such cell, but several adjacent ones and change their parameters at once using the context menu.
Finally
In this article, we presented quick and really working ways to adjust the presentation of the type of digital data in the Excel program. Putting them into practice will help save your nerves and time while working in spreadsheets.