Microsoft Excel: Formatting

Connect with me: Youtube | LinkedIn | WhatsApp Channel | Web | Facebook | Twitter

• Download PDF
• To access the updated handouts, please click on the following link: https://yasirbhutta.github.io/ms-excel/docs/formatting.html
• Youtube Playlist to learn excel

• Slides

• Microsoft Excel: Formatting
  • Module 2: Formatting cells and ranges
  • Formatting Commands in Excel
    • Font
    • Font Size
    • Bold
    • Italic
    • Underline
    • Strikethrough
    • On Windows (Excel Desktop App):
    • On Mobile (Excel App):
    • Borders
      • Apply a predefined cell border [Windows]
      • Remove a cell border [Windows]
    • Fill Color
    • Font Color
    • Data Alignment
      • Align text in a cell [Windows] [^11] [^12]
    • Merge \& Center
      • Merge adjacent cells [Windows]
      • Merge adjacent cells [Android]
      • Unmerge cells [Windows]
      • Unmerge cells [Android]
    • Wrap Text
      • Wrap text automatically - Windows
      • Enter a line break - Windows
    • Format Painter
    • Cell Styles
      • Apply a cell style
      • Remove a cell style from data
    • Number Format
      • To Change a Cell’s Number Format
        • Windows
      • Working with the Comma Style
        • Windows
      • Format cell with currency symbol Rs
        • Windows
      • Applying the percentage style - Windows
      • Formatting Dates - Windows
    • Format as Table
      • Apply Table Style
      • Remove Table Style
  • Card View
  • Insert \& Delete
    • Format Cell Size
      • Set a column to a specific width - Windows
      • Column width to automatically fit the contents (auto fit) - Windows
      • Match the column width to another column - Windows
      • Change the width of columns by using the mouse - Windows
      • Set a row to a specific height
        • Windows
      • Change the row height to fit the contents - Windows
    • Clear
    • Rotate data in cell - Windows
    • Increase/Decrease Indent
    • Add or remove a sheet background - Windows
      • Add a sheet background - windows
      • Remove a sheet background - Windows
    • Gridlines on a Worksheet
      • Show or Hide gridlines on a worksheet
      • Hide gridlines on a worksheet
      • Show gridlines on a worksheet
      • Change the color of gridlines in a worksheet
  • Additional Topics
    • Adjust Text in Column
    • Fit more text in column headings
  • True/False (Mark T for True and F for False)
  • Multiple Choice (Select the best answer)
  • Exercises
  • Review Questions
    • Windows only
  • References and Bibliography

Important: Keyboard shortcut keys can only be used in Windows, not in Android.

Module 2: Formatting cells and ranges

• Changing the font, font size, and font color
• Aligning text
• Applying borders and shading
• Merging and splitting cells
• Number format
• Wrap text and Cell Style

Formatting Commands in Excel

Font

• A font is a collection of characters, symbols, and glyphs that share a common design and are used to display text in a specific style. Fonts can be used for various purposes such as creating logos, designing websites, and publishing books.¹
• There are many different types of fonts available, each with its own unique personality. Some fonts are more formal and elegant, while others are more casual and fun. Some fonts are designed to be easy to read, while others are more decorative.

• Here are some examples of different types of fonts:

  • Serif fonts have small decorative lines at the ends of their strokes. They are often considered to be more formal and elegant than sans-serif fonts. Examples of serif fonts include Times New Roman, Georgia, and Garamond.
  • Sans-serif fonts do not have the decorative lines at the ends of their strokes. They are often considered to be more modern and casual than serif fonts. Examples of sans-serif fonts include Arial, Helvetica, and Futura.
  • Script fonts resemble handwritten text. They are often used for invitations, greeting cards, and other formal documents. Examples of script fonts include Brush Script, Calligraphy, and Freestyle Script.
  • Decorative fonts are designed to be eye-catching and decorative. They are often used for headlines, logos, and other graphic design elements. Examples of decorative fonts include Comic Sans, Trajan Pro, and Papyrus.

Fonts are used in Excel to change the style and size of text and characters in a document. Changing the font of your numbers and text in an Excel worksheet can help your document to look more appealing and, at times, make it easier for the reader to digest the information.

Excel provides users with a pre-selected font that they may use when creating workbooks. A default font can play an important role in your document’s appearance and reader comprehension

Font Size

-The font size or text size is a number that measures the size of characters (such as letters, numbers, and punctuation marks) on a screen or printed page 1. In computing and web design, font sizes can be defined in units of pixels (px), points (pt), or ems (em).²

• In Microsoft Excel, changing the font size of your numbers and text can help your document to look more appealing and make it easier for the reader to digest the information.

Bold

• In Microsoft Excel, bold is a font style that makes text appear thicker and darker than the surrounding text.
• It is used to emphasize important information or to make headings stand out in a table or spreadsheet.

Keyboard shortcut: Ctrl+B.³

Italic

• In Microsoft Excel, Italic font style is a type of text formatting where the letters are tilted to the right. It is used to emphasize important information or to make headings stand out in a table or spreadsheet.

Keyboard shortcut: Ctrl+I.³

Underline

• In Microsoft Excel, underline is a font style that adds a line below the text.
• The underline command is used to draw attention to specific text or data in a table or spreadsheet. It can be used to indicate headings, subheadings, or important information that needs to be highlighted. Underlining can also be used for emphasis when bold or italic formatting is not appropriate.⁴

Keyboard shortcut: Ctrl+U.³

Strikethrough

• In Microsoft Excel, strikethrough is a font style that adds a line through the middle of the text.
• The strikethrough command is used to indicate that certain information is no longer relevant or has been deleted. It can also be used to show completed tasks in a table or spreadsheet.⁵

Keyboard shortcut: Ctrl+5.³

To apply a specific font in Excel on both Windows and the mobile app, follow these steps:

On Windows (Excel Desktop App):

1. Open Excel and select the cell(s) where you want to apply the font.
2. In the Home tab, locate the Font group.
3. Click the Font drop-down menu to view available fonts.
4. Select the desired font from the list.
5. You can also adjust the font size, bold, italic, or underline from the same section if needed.

On Mobile (Excel App):

1. Open Excel on your mobile device and open the spreadsheet.
2. Select the cell(s) where you want to change the font.
3. On your Android phone, you can expand the ribbon by tapping the edit icon (which is usually located in the top). You can collapse the ribbon by tapping the down arrow Down arrow. read more …
4. Tap Font to open the font options.
5. Choose the desired font from the list.
6. You can also change the font size, bold, or italic here.

Borders

• In Microsoft Excel, cell borders are lines that surround a cell or range of cells. They are used to visually separate data in a table or spreadsheet and make it easier to read and understand.
• Cell borders that you apply on cell in sheets to appear on printed pages.

• By using predefined border styles, you can quickly add a border around cells or ranges of cells. If predefined cell borders do not meet your needs, you can create a custom border.⁶

• Video: How to: Apply cells border -Excel Mobile App

Apply a predefined cell border [Windows]

1. On a worksheet, select the cell or range of cells.
2. On the Home tab, in the Font group, To apply a new or different border style, click the arrow next to Borders , and then click a border style.

Remove a cell border [Windows]

1. On a worksheet, select the cell or range of cells that you want to remove a border from.
2. On the Home tab, in the Font group, click the arrow next to Borders , and then click No Border.

Fill Color

• n Microsoft Excel, Fill Color is a feature that allows you to add or change the background color.
• It is used to highlight data in cells and make it easier to read and understand. ⁷

Font Color

• In Microsoft Excel, Font Color is a feature that allows you to change the color of the text in a cell or range of cells.[^10]

Data Alignment

• In Microsoft Excel, data alignment refers to the position of data within a cell or range of cells.

Align text in a cell [Windows] [^11] [^12]

1. Select the cell or range of cells that contains the data that you want to reposition.
2. On the Home tab, in the Alignment group, do one or more of the following:
   • To change the vertical alignment of cell contents, click Top Align , Middle Align , or Bottom Align .
   • To change the horizontal alignment of cell contents, click Align Text Left , Center , or Align Text Right .

Merge & Center

• Merge and Center is a feature in Microsoft Excel that allows you to combine two or more cells into one cell and align the contents of the merged cells in the center.
• This feature is useful when you want to create a label that spans multiple columns or rows, or when you want to merge cells containing text or numbers into a single cell.

• You can’t split an individual cell, but you can make it appear as if a cell has been split by merging the cells above it. ⁸

• Video: Merge & Center and Wrap text in excel mobile app

Merge adjacent cells [Windows]

1. Select two or more adjacent cells that you want to merge.
2. On the Home tab, in the Alignment group, click Merge and Center.

Merge adjacent cells [Android]

1. Select the cells to merge.
2. Tap Merge & Center.

Unmerge cells [Windows]

1. Select the merged cell. When you select a merged cell, the Merge and Center button also appears selected in the Alignment group on the Home tab.
2. To split the merged cell, click Merge and Center .

Unmerge cells [Android]

1. Select the Merged Cell
2. Tap again Merge & Center.

Wrap Text

• In Microsoft Excel, Wrap Text is a feature that allows you to display the contents of a cell on multiple lines, rather than one long line.
• This feature is useful when you have lengthy text in a cell that is difficult to read or when you want to fit more text into a smaller space.
• By enabling Wrap Text, you can ensure that all the content is visible without having to adjust the column width or are left with text overflowing into neighboring cells. [^14] [^15]

Wrap text automatically - Windows

1. In a worksheet, select the cells that you want to format.
2. On the Home tab, in the Alignment group, click Wrap Text.

Important: Data in the cell wraps to fit the column width. When you change the column width, data wrapping adjusts automatically.

Enter a line break - Windows

To start a new line of text at a specific point in a cell, double-click the cell, click the location where you want to break the line in the cell, and then press ALT+ENTER.

Format Painter

• The Format Painter is a useful tool in Microsoft Excel that allows you to copy formatting from one cell or range of cells and apply it to another cell or range of cells.
• Use Format Painter to quickly apply the same formatting, such as color, font style and size, or border style, to multiple pieces of text or graphics. ⁹

Notes:

You can use Ctrl+Shift+C to copy a format, and Ctrl+Shift+V to paste a format. While the cursor does not change to a paintbrush, you can repeatedly paste formatting to multiple areas without re-copying.

Cell Styles

• A cell style is a defined set of formatting characteristics, such as fonts and font sizes, number formats, cell borders, and cell shading. To apply several formats in one step, and to make sure that cells have consistent formatting.

Apply a cell style

Remove a cell style from data

Number Format

• Excel provides a variety of number formats that you can apply to the values (numbers) you enter in a worksheet to make the data easier to interpret.
• These number formats include:
  • currency, accounting, percentage, date, time, fraction, and scientific, as well as a few special formats.
• Video: Format numbers as Text in Microsoft Excel
• Video: Change Date Format

To Change a Cell’s Number Format

Windows

1. Select the cell(s) containing the value(s) you want to format.
2. Choose a format from the drop-down list in the Number group on the Home tab.

Working with the Comma Style

Windows

1. Select the cells containing the numbers you want to format.
2. From the Home tab, click the Number dialog box launcher in the bottom-right corner of the Number group.
3. The Format Cells dialog box appears, with the Number tab on top.
4. In the Category list, select Number.
5. Select the Use 1000 Separator (,) check box.
6. Click OK.

Format cell with currency symbol Rs

Windows

1. Select the cells containing the numbers you want to format.
2. From the Home tab, click the Number dialog box launcher in the bottom-right corner of the Number group.
3. The Format Cells dialog box appears, with the Number tab on top.
4. In the Category list, select Currency.
5. From the symbol list select Rs Urdu.
6. Click OK.

Applying the percentage style - Windows

1. Select the cell(s) containing the value(s) you want to format.
2. Click on Percent style in Number group of Home tab.

Formatting Dates - Windows

1. Enter a date into a cell.
2. From the Home tab, click the Number dialog box launcher.
3. Click Date in the Category list
4. Select a format for the selected cells.
5. Click OK

Format as Table

• Microsoft Office Excel provides numerous predefined table styles (or quick styles) that you can use to quickly format a table.
• If the predefined table styles don’t meet your needs, you can create and apply a custom table style.

Apply Table Style

Remove Table Style

Card View

• Video Tutorial: How to: use a cards view in excel - Excel Mobile App

Insert & Delete

Format Cell Size

Set a column to a specific width - Windows

1. Select the column or columns that you want to change.
2. On the Home tab, in the Cells group, click Format.
3. Under Cell Size, click Column Width. In the Column width box, type the value that you want.

Column width to automatically fit the contents (auto fit) - Windows

1. Select the column or columns that you want to change.
2. On the Home tab, in the Cells group, click Format.
3. Under Cell Size, click AutoFit Column Width.

Match the column width to another column - Windows

1. Select a cell in the column that has the width that you want to use.
2. On the Home tab, in the Clipboard group, click Copy, and then select the target column.
3. On the Home tab, in the Clipboard group, click the arrow below Paste, and then click Paste Special.
4. Under Paste, select Column widths.

Change the width of columns by using the mouse - Windows

• To change the width of one column, drag the boundary on the right side of the column heading until the column is the width that you want.

Set a row to a specific height

Windows

1. Select the row or rows that you want to change.
2. On the Home tab, in the Cells group, click Format.
3. Under Cell Size, click Row Height.
4. In the Row height box, type the value that you want.

Change the row height to fit the contents - Windows

1. Select the row or rows that you want to change.
2. On the Home tab, in the Cells group, click Format.
3. Under Cell Size, click AutoFit Row Height.

Clear

Rotate data in cell - Windows

1. Select the cell or range of cells that contains the data that you want to reposition.
2. On the Home tab, in the Alignment group
3. To rotate the cell contents, click Orientation , and then select the rotation option that you want.

Increase/Decrease Indent

1. Select the cell or range of cells that contains the data that you want to reposition.
2. On the Home tab, in the Alignment group
3. To change the indentation of cell contents, click Decrease Indent or Increase Indent

Add or remove a sheet background - Windows

• In Microsoft Office Excel, you can use a picture as a sheet background for display purposes only.
• A sheet background is not printed on paper.

Add a sheet background - windows

• Click the worksheet that you want to display with a sheet background. Make sure that only one worksheet is selected.
• On the Page Layout tab, in the Page Setup group, click Background.
• Select the picture that you want to use for the sheet background, and then click Insert.

The selected picture is repeated to fill the sheet.

Remove a sheet background - Windows

• Click the worksheet that is displayed with a sheet background. Make sure that only one worksheet is selected.
• On the Page Layout tab, in the Page Setup group, click Delete Background.

Gridlines on a Worksheet

• Gridlines are the faint lines that appear around cells in Excel. They are used to distinguish cells on the worksheet.

Video: Excel Gridlines - Show, Hide, Change Color

Show or Hide gridlines on a worksheet

Hide gridlines on a worksheet

• Select one or more worksheets.
• On the View tab, in the Show/Hide group, clear the Gridlines check box.

Show gridlines on a worksheet

• Select one or more worksheets.
• On the View tab, in the Show/Hide group, select the Gridlines check box.

Change the color of gridlines in a worksheet

• Click the Microsoft Office Button, and then click Excel Options.
• In the Advanced category, under Display options for this worksheet, make sure that the Show gridlines check box is selected. In the Gridline color box, click the color you want.

See also:

• Show or hide gridlines on a worksheet - Microsoft Support
• Change the color of gridlines in a worksheet - Microsoft Support
• Print gridlines in a worksheet - Microsoft Support

Additional Topics

Adjust Text in Column

See also:

• Change the column width and row height - Microsoft Support
• Change the column width or row height in Excel - Excel Help & Training

Fit more text in column headings

See also:

• Fit more text in column headings - Microsoft Support

True/False (Mark T for True and F for False)

Multiple Choice (Select the best answer)

What is the purpose of a cell format?

1. To change the appearance of a cell
2. To protect a cell from being changed
3. To add a formula to a cell
4. None of the above

Which of the following is NOT a valid font style in Excel?

1. Bold
2. Italic
3. Underline
4. None of the above

Which of the following is NOT a valid number format in Excel?

1. Currency
2. Date
3. Percentage
4. Text
5. None of the above

What is the primary function of the Format Painter tool in Excel?

1. To copy and paste formatting from one cell or range of cells to another.
2. To apply a specific font style to a selected range of cells.
3. To adjust the alignment of text within cells.
4. To change the border style of selected cells.

What is the advantage of using the Format Painter tool compared to manually applying formatting to multiple cells?

1. It saves time and effort when applying consistent formatting to multiple cells.
2. It allows you to apply a wider range of formatting options than are available in the Format Cells dialog box.
3. It ensures that all formatting is applied differently to all selected cells.
4. It allows you to preview the formatting changes before applying them to the selected cells.

What happens to the contents of the other cells when you merge two or more cells?

1. The contents of the other cells are copied to the merged cell.
2. The contents of the other cells are moved to the merged cell.
3. Merging cells only keeps the upper-left value and discards other values
4. The contents of the other cells remain unchanged.

What is the purpose of using Merge and Center in Excel?

1. To combine two or more cells into a single cell.
2. To center the text in a cell.
3. To do both of the above.
4. Neither of the above.

What is the purpose of wrapping text in Excel?

1. To make text fit within the width of a cell
2. To change the font of text
3. To add a border to cells
4. To protect cells from being edited

What happens when you wrap text in Excel?

1. The text is moved to a different cell.
2. The text is hidden.
3. The text is displayed on multiple lines within the same cell.
4. The text is deleted.

What is the effect of wrapping text on the row height?

1. The row height decreases.
2. The row height increases.
3. The row height remains the same.
4. The row height varies depending on the amount of text in the cell.

Which of the following is the default alignment for text in Excel cells?

1. Left
2. Right
3. Center
4. Justify

Which of the following number formats is used to display numbers with decimal places?

1. General
2. Number
3. Currency
4. Accounting

Which of the following number formats is used to display numbers as percentages?

1. General
2. Number
3. Currency
4. Percentage

Which of the following number formats is used to display negative numbers in red?

1. General
2. Number
3. Currency
4. Accounting

What is the default font in Excel?

1. Arial
2. Calibri
3. Times New Roman
4. Courier New

How can you make a font bold in Excel?

1. Use the keyboard shortcut Ctrl+D.
2. Use the keyboard shortcut Ctrl+B.
3. Use the keyboard shortcut Ctrl+I.
4. Use the keyboard shortcut Ctrl+U.

Which of the following font sizes is best for body text?

1. 10
2. 12
3. 14
4. 16
5. 18

Exercises

Review Questions

1. What is the purpose of formatting in Excel, and why is it important?
2. What is the difference between cell formatting and cell value?
3. How do you apply formatting to a range of cells?
4. How do you clear formatting from a range of cells?
5. Explain the purpose of the Format Painter tool in Excel and how to use it.
6. Explain the purpose of the “Fill Color” and “Font Color” options in Excel and how to use them effectively.
7. How do you change the text color in a cell?
8. Describe the difference between cell formatting and cell style in Excel.
9. How can you apply a font style to multiple cells at once?
10. Explain how to apply bold, italic, and underline formatting to text in Excel.
11. Describe the process of applying borders and shading to cells in Excel.
12. What is the difference between a custom number format and a built-in number format?
13. What are the different types of number formats in Excel?
14. How can you adjust the alignment of text within a cell (e.g., left, right, center, top, bottom) in Excel?
15. How do you apply number formatting to display negative numbers in red or parentheses in Excel? ***
16. How can you format numbers to show a percentage sign?
17. How do you display currency symbols in a cell?
18. How do you display dates and times in a specific format?
19. How can you use the Format Painter tool to copy font formatting from one cell to another?
20. What are the different types of alignment options in Excel?
21. How can you wrap text in a cell?
22. How do you center text in a cell?
23. How do you center text horizontally and vertically in a cell?
24. What is the purpose of the “Merge & Center” command in Excel, and how does it affect the formatting of cells?
25. How can you add borders and shading to cells?
26. How can you quickly remove all formatting from a cell?
27. How can you change the horizontal and vertical alignment of a cell?
28. What are the different border styles available in Excel?
29. How do you add a border to a cell?
30. How do you change the border style and color?
31. How do you remove a border from a cell?
32. How do you add a background color to a cell?
33. What are cell styles?

Windows only

• What is the keyboard shortcut for opening the Format Cells dialog box?
• What is the keyboard shortcut for applying the Format Painter tool?
• How can you rotate text?
• What is the purpose of the Font dialog box?
• How can you access the Format Cells dialog box in Excel to apply custom formatting?

References and Bibliography

Enter and format data - Microsoft Support

1. WhatTheFont ↩

2. Font size - Computer Hope ↩

3. Keyboard shortcuts in Excel ↩ ↩² ↩³ ↩⁴

4. Understanding Underlines In Excel ↩

5. The Only Guide You’ll Need For Excel Strikethrough - simple sheets ↩

6. Apply or remove cell borders on a worksheet - Microsoft Support ↩

7. Add or change the background color of cells - Microsoft Support ↩

8. Merge and unmerge cells - Microsoft Support ↩

9. Use the Format Painter ↩