««Excel: millimeters, square meters, and other units of measure use

7:26 am April 8, 201713055

use millimeters, square meters, and other units of measurement the automatic insertion of the euro sign in the relevant cells in Excel, use currencies, is not a problem – for Excel for Yes the practical Euro-button that does it for you. Need other units, such as square metres, cubic metres or kilograms, need to go another way.

one first of all: direct typing of the unit in the cell does not lead to the goal. Because if you give a “25 kg”, Excel displays so, but no longer as a number to see the content. The result: You can no longer use the value in a formula. Excel then displays the error value #ZAHL! instead of the result map.

some people solve this problem so that it right creates a cell for the unit next to the cell that contains the value. This is rather impractical, because you must always move then this cell if you ensure and the correct optical distance to the number field is hard to get to grips.

as you integrate the unit with a cell

that is a method for the cell formatting, so the setting that you format by right clicking on a cell or a range of cells with the entry cells reach of the context menu.

Excel Kontextmenü formatieren
context menu of Excel format

in custom you can enter here any character string, which influences the representation of the cell value. The value itself will not change. The result of a formula that used this cell does not change this so.

the simplest form of a formatting of unit of measure you can see an example of a weight value: use following format


so that a value “1.23” than “1, 23 kg” is displayed. The cell value in standard version is displayed instead of the spider monkey and this includes the string in quotation marks. The “kg” is too close to the number that you then you insert just a space before:


this method works naturally as with all other units, because Excel the character in quotation marks Yes just “dumb” adds,

so you combine units and decimals

as mentioned, the standard format is used with @ easy. Maybe you are but affects the output of the numerical value, using some fixed number of decimal places, round the value to a specific Kommanstelle or populate from the left with zeros.

you want to achieve for example that always two digits appear after the decimal point, then use instead of the @ the string 0,00. adopted, they would represent areas, then the whole formatting would look like as below. You can reach the superscript 2 on the PC using the key combination [AltGr + 2] .

0.00 "m square"

examples of cell values and your appearance with this formatting:

Excel zeigt Quadratmeter an
Excel shows you see square meters of

in the last example, that Excel rounds if necessary on the last displayed number. ”

you want to fill a certain number of digits before the comma with zeros, then put those zeros left before the comma. Would you perhaps fill up on five points and have no decimal places, then you write

00000 "m square"

the method also works with all other units that you can imagine because the part inside the quotes from Excel is not interpreted, but simply played back, as you type it.

you want to give your table with just a few clicks a great design? Here is how to do this: table format and visually appealing design

similar tips