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.

Excel 2016 Font group on the Home tab

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 Excel 2016 Font
Change the font size Font size Excel 2016 Font size
Increase Font Size Excel 2016 Increase Font Size
Decrease Font Size Excel 2016 Decrease Font Size
Put a bold text Bold Excel 2016 Bold
Put text in italics Italic Excel 2016 Italic
Underline text Underline Excel 2016 Underline
Apply borders Borders Excel 2016 Borders
Apply a color to the background Fill Color
Excel 2016 Fill Color
Change the color of a text Font Color
Excel 2016 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:

Excel 2016 Format Cells Dialog Box Font Tab

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:

Excel 2016 Alignment group on the Home tab

4.2.1. Vertical alignment

The buttons Excel 2016 Vertical Alignment 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 Excel 2016 Vertical Alignment Buttons Top justifies the text at the top of the cell
  • The second button Excel 2016 Vertical Alignment Buttons Center allows to vertically justify the text in the center
  • The third button Excel 2016 Vertical Alignment Buttons Bottom justifies the text at the bottom of the cell
Text aligned at the top Excel 2016 Alignment Top
Center aligned text Excel 2016 Alignment Center
Text aligned at the bottom Texte aligné en bas

4.2.2. Horizontal alignment

The buttons 4.2.2. Horizontal alignment The buttons allow to justify the text horizontally. allow to justify the text horizontally.

Left aligned text Excel 2016 Left aligned text
Center aligned text Excel 2016 Center aligned text
Right aligned text Excel 2016 Right aligned text

4.2.3. Cell fusion

The buttons for cell merging are:

  • The button Excel 2016 Merge And Center merges the selected range of cells into a single cell and centers the content horizontally and vertically
  • The button Excel 2016 Merge Across merges each line of the selected range of cells into a cell for each line without centering the content
  • The button Excel 2016 Merge Cells merges the selected range of cells into a single cell without centering the content
  • The button Excel 2016 Unmerge Cells 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

Excel 2016 Fusion 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 Excel 2016 Wrap Text button:

Excel 2016 Wrap Text

The Orientation button Excel 2016 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:

Excel 2016 Rotate Text Up

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 Excel 2016 Mouse Pointer Column Header, click and move it without releasing the mouse button Excel 2016 Change column width
  • 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.

Excel 2016 Change column width

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 Excel 2016 Mouse Pointer Column Header

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.

Excel 2016 Select All Button

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.

Excel 2016 Paste Special Column width

NB. You can use the Format Painter button 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.

Excel 2016 Example Comma Style

To format a number with the thousands separator:

  • Use the Comma Style button Excel 2016 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 Excel 2016 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 Excel 2016 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.

Excel 2016 More Accounting Formats

4.6.3. Writing percentages

Regarding the percentages, just write the ratio for example 0.2 (for 20%). Then use the Percent Style button Excel 2016 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

Excel 2016 Format Cells dialog box Number tab

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 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
Excel 2016 Cell Styles command

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
Excel 2016 Format as Table command

Exercises

Exercise 1 – Formatting

Exercise 2 – Formatting


<< 3. Manipulation of cell ranges

5. Work with series >>