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 text in italics||Italic|
|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 :
You can also apply specific formats in the form of a table with, for example, alternating colors :