Formatting means determining the appearance of content. Usually formatting does not change the value of a cell, but the appearance or information that is displayed in addition – such as the euro symbol € next to a number.
To format a cell, it must be selected in advance. If a whole cell range is selected, the format of all corresponding cells will change.
Important formatting is located in the Start tab in the Alignment, Number or Styles sections.
By default in Excel, all numbers are right-aligned and all text is left-aligned. This also applies to numbers that Excel mistakenly misinterprets as text. To change the alignment, we click the Centered button in the Alignment area of the Home tab.
Then the contents of the cell center themselves.
Note: Content can also be centered vertically
We set the colors in (almost) the same way as we set the alignment. We navigate to the Home tab in the Font area and click the Fill Color button. Now the cell will be displayed in yellow.
To use other colors, we select the small arrow next to the paint bucket.
Here we choose a suitable color for our cell, which will also be the default color for the paint bucket from now on.
Clicking the Font Color button changes the font color of the selected cell.
The font color can also be varied by clicking on the arrow next to the icon.
This is the result:
Note: in this way cells can get a frame, be displayed in italics or the font can be enlarged. Just try it out.
Our cell contains the value 100. It is often advisable to provide values with units (€. %, km, etc.) so that the reader also understands what is being calculated in this table. We should avoid the mistake of writing 100 km in a cell, because Excel will misinterpret this entry as text and will not be able to separate the number 100 with the unit km and calculate distances. For this reason we use number formatting: they show corresponding units and allow further calculations.
We select the cell and click the Accounting Number Format button in the Number area of the Home tab.
The result: our number 100 now looks like this: 100,00 €.
To remove the decimal places, we click on the “Remove decimal place” button until our number is displayed without decimal place.
The result: 100 €
Special rule Format percentages: Excel quickly understands that percentages are meant. If there is a percent sign % next to a number, the number is automatically formatted as a percent. Reminder: Percentages are a simpler way of writing decimal numbers.
0,8 = 8/10 = 80/100 = 80%. Or in simpler terms: 100% = 1.
If we enter the value 80%, we mean the value 0.8, but if we first enter the value 80 and then click on the Percent Format button in the Number section of the Start tab, we get the value 8000% (because 80*100=8000).
Insert your own formatting
In this example, we format the number 120.30 to the unit “km” to end up with the custom format “120.30 km”.
To insert custom formatting, we first select the cells to be formatted and then navigate to the Start tab and click on the small arrow in the Number area as shown in the screenshot below. Alternatively, we press the CTRL and 1 keys on the keyboard simultaneously.
The Format Cells dialog box appears.
In the Numbers tab, in the Category area, we select the Custom option.
Under Type we can specify our own formatting. For our example we insert the following line:
0.00 “km “Note: 0.00 means that two decimal places are displayed. The entry “km” after the numbers signals Excel that the letters in the quotes are to be displayed after the number.
And this is the result: