4. Formatting

---

4.1. Changing the appearance of text

The commands in the Font group on the Home tab of the ribbon allow you to change the appearance of text on a spreadsheet.

Select one or more Excel cells with text and click on each of the commands in this group to see the effect:

Goal	Command to use	Image of the command
Select a font	Font
Change the font size	Font size
	Increase Font Size
	Decrease Font Size
Put a bold text	Bold
Put text in italics	Italic
Underline text	Underline
Apply borders	Borders
Apply a color to the background	Fill Color
Change the color of a text	Font Color

 If you click on this group's Dialog box launchers, the Format Cells dialog box is opened on the Font tab. There you will find additional commands:

4.2. Alignment group on the Home tab

The buttons in the Alignment group of the Home tab of the ribbon allow you to control the alignment of text within cells:

4.2.1. Vertical alignment

The buttons  allow you to define the vertical alignment of the text within the cell. To check the effect of these buttons, you have to choose a line having a height greater than that of the text written in its cells.

• The first button  justifies the text at the top of the cell
• The second button  allows to vertically justify the text in the center
• The third button  justifies the text at the bottom of the cell

Text aligned at the top
Center aligned text
Text aligned at the bottom

4.2.2. Horizontal alignment

The buttons  allow to justify the text horizontally.

Left aligned text
Center aligned text
Right aligned text

4.2.3. Cell fusion

The buttons for cell merging are:

• The button  merges the selected range of cells into a single cell and centers the content horizontally and vertically
• The button  merges each line of the selected range of cells into a cell for each line without centering the content
• The button  merges the selected range of cells into a single cell without centering the content
• The button  cancels all cell merges in the selected range.

Cell fusion is useful in certain situations:

• When a title concerns 2 or more rows or columns: case of ranges A5:A7 and A8:A10 in the example below
• To write a global title and center it at the top of the page: case of range A2:G2 in the example

NB. If you merge a range of cells that already contain text, then only the contents of the first cell will be preserved.

4.2.4. Other buttons

By default, when I enter a text wider than the width of the column which contains it, then it display continues on the adjacent cell if this cell is empty, otherwise the text is truncated. So that the text does not exceed the limit of its column and continues on a new line, use the Wrap Text button :

The Orientation button  allows you to rotate the text in the cell containing it. This is useful, for example, when we need to write text in a narrow column. We can write it horizontally:

4.3. Change column width and row height

To change the width of a column:

• Move the mouse pointer to the right edge of the column header you want to widen
• When the pointer becomes thus , click and move it without releasing the mouse button
• When the desired size is reached, release the button.

You can also define a width of a specific size:

• Click on a cell in the column you want to resize
• Click on the Format button of the Cells group of the Home tab and choose Column Width. The Column Width dialog box appears.
• Enter the desired column width and press the OK button.

The modification of the row height is done in the same way:

• Either, you click on the edge of the line header and move the pointer until you have the desired height.
• Either, you select the row, you use the Format button and choose Row Height. You then enter the desired height in the Row Height dialog box.

4.4. Change the width of a column so that it automatically fits the content

A situation where we need to do an automatic adjustment of column widths is when we want to print a sheet that has a lot of data so that its layout becomes difficult and we want to reduce the width of the columns as much as possible:

• Click on the column(s) you want to resize
• Click on the Format button of the Cells group of the Home tab and choose AutoFit Column Width.

Do the same to automatically adjust the row height.

An alternative is to select the desired column(s), then double click on the edge of the column header. The double click must be performed while the pointer is of this form

NB. To adjust all columns and rows on a worksheet:

• Use the Select all button to select the entire sheet.
• Double click on a boundary between two column headers. Columns adjust automatically
• Double click on a boundary between two line headers. The lines adjust automatically.

4.5. Copy the width of one column to another column

To copy the width of one column to another column:

• Select the column with the desired width.
• Click on the Copy button of the Clipboard group of the Home tab, or use Ctrl + C on the keyboard.
• Click on the arrow below the Paste button and choose Paste Special.
• Then select Column widths in the Paste Special dialog box and validate with the OK button.

NB. You can use the Format Painter button , but in this case, all of the formatting that will be copied.

4.6. Number and date format

The format application for numbers is useful in several situations:

4.6.1. Thousands separator

If you have a large number, use the thousands separator to make it easier to read.

To format a number with the thousands separator:

• Use the Comma Style button  in the Number group of the Home tab. The number is displayed with thousands separation, and, with two decimal places.
• If you don't want decimal places, use the Decrease Decimal button  in the Number group of the Home tab.
• You can also increase the number of decimal places by using the Increase Decimal button  in the Number group of the Home tab.

NB. You should not add comma yourself using the keyboard to have the requested format. The downside is that Excel may not recognise that you have entered a number therefore you will not be able to use the value in calculations.

4.6.2. Monetary values

The number format is also very useful for displaying currency values. In this case you can just use the Thousands separator button to have numbers with thousands separation and two digits after the commas. If you want to add a currency (French DH (Morocco) for example), proceed as follows:

• Click on the Accounting Number Format button and choose More Accounting Formats....
• In the Format Cells dialog box that appears, select DH French (Morocco) in the Symbol field.

4.6.3. Writing percentages

Regarding the percentages, just write the ratio for example 0.2 (for 20%). Then use the Percent Style button  in the Number group of the Home tab. It is then displayed 20%.

4.6.4. Other formats

You can have other formats using the Format Cells dialog box:

• Select the range of cells for which you want to change format
• Use the Format command and choose Format Cells.... The Format Cells dialog box appears.
• Click on the Number tab if another tab is displayed.
• Choose the Category according to the type of data you have
• You can also choose from the Locale(location) list the one that suits you.
• Finally choose the format that suits you in the Type list

This is particularly useful for specifying a format for dates and times.

4.7. Reproduce formatting

You can copy the formatting from one range of cells to another. To do this, use the Format Painter button of the Clipboard group of the Home tab of the ribbon.

• Select a cell with the formatting you want to copy
• Click on the Format Painter button
• Click the cell or select the range of cells where you want to copy the formatting.

4.8. Quick formatting using styles

Styles let you apply quick formatting to a worksheet.

To apply formatting to a cell or range of cells using styles : Select the relevant cell or range of cells Click on the Cell Styles command in the Styles group of the Home tab of the ribbon Then choose the style that suits you
You can also apply specific formats in the form of a table with, for example, alternating colors : Select a range of cells Use the Format as Table command in the Styles group of the Home tab of the ribbon Then choose the style that suits you

Exercises

Exercise 1 – Formatting

Exercise 2 – Formatting

---