facebook google plus twitter
Webucator's Free Introduction to Microsoft Excel 2013 Tutorial

Lesson: Formatting Microsoft Excel Worksheets

Welcome to our free Introduction to Microsoft Excel 2013 tutorial. This tutorial is based on Webucator's Introduction to Microsoft Excel 2013 Training course.

In this lesson, you will learn about formatting your worksheets.

Lesson Goals

  • Learn the various ways you can format Microsoft Excel worksheets.
  • Select a range of cells within a worksheet.
  • Hide worksheets.
  • Add color to workbook tabs.
  • Add themes to worksheets.
  • Add bold, italics, and underlining to text.
  • Add borders to cells.
  • Change text and cell colors.
  • Change the font and font size.
  • Align text.
  • Wrap text.
  • Indent text.
  • Merge and center text.
  • Change number formats.
  • Change date formats.
  • Show decimals.

There are many types of formatting that can be applied to Microsoft Excel worksheets. The most commonly used formatting commands show up on the HOME tab in three groups:

  1. The Font Group. The font group commands change the appearance of text within a cell or of the cell itself.
  2. The Alignment Group. The alignment group commands change the position of text within a cell or cells.
  3. The Number Group. The number group commands change the format of numbers and dates within a cell.

Formatting changes can be applied to a whole worksheet, a range of cells within a worksheet, individual cells, and sometimes even text within a cell.

The Font Group

Bold, Italicize and Underline Text

To bold text in Microsoft Excel:

  1. Select the cell or cells in which you wish to bold the text.
  2. On the HOME tab, in the Font group, click the Bold command.Bold Command in Font Group of Home Tab

To italicize text in Microsoft Excel:

  1. Select the cell or cells in which you wish to italicize the text.
  2. On the HOME tab, in the Font group, click the Italic command.Italic Command in Font Group of Home Tab

To underline text in Microsoft Excel:

  1. Select the cell or cells in which you wish to underline the text.
  2. On the HOME tab, in the Font group, click the Underline command.Underline Command in Font Group of Home Tab

Add Borders to Cells

To add borders to cells in Microsoft Excel:

  1. Select the cell or cells to which you wish to add borders.
  2. On the HOME tab, in the Font group, click the arrow to the right of the Borders command.Borders Command in Font Group of Home Tab
  3. Select the type of border you wish to add from the drop down menu:Border Drop-Down List

Change Text and Cell Colors

To change the color of text in cells in Microsoft Excel:

  1. Select the cell or cells in which you wish to change the color of the text.
  2. On the HOME tab, in the Font group, click the arrow to the right of the Font Color command.Font Color Command in Font Group of Home Tab
  3. Select a color from the drop down menu:Color Menu

To change the fill (i.e., background) color of cells in Microsoft Excel:

  1. Select the cell or cells in which you wish to change the fill color.
  2. On the HOME tab, in the Font group, click the arrow to the right of the Fill Color command.Fill Color Command in Font Group of Home Tab
  3. Select a color from the drop down menu:Color Drop-Down Menu

Set Font and Font Size

To change the font of text or numbers in cells in Microsoft Excel:

  1. Select the cell or cells in which you wish to change the font.
  2. On the HOME tab, in the Font group, click the arrow to the right of the Font command.Font Command in Font Group of Home Tab
  3. Select a font from the drop down menu:Font Drop-Down Menu

To change the size of the font of text or numbers in cells in Microsoft Excel:

  1. Select the cell or cells in which you wish to change the font size.
  2. On the HOME tab, in the Font group, click the arrow to the right of the Font Size command.Font Size in Font Group of Home Tab
  3. Select a font size from the drop down menu:Font Size Drop-Down Menu

Selecting Ranges of Cells

To select a range of cells in Microsoft Excel:

  1. Click on a cell in one of the corners of the range of cells you wish to select:Selected Cell
  2. Hold the left mouse button down and drag horizontally and vertically until the range you wish to select is highlighted:Selected Range
  3. Release the click.

You can select all the cells in a row by clicking on the number to the left of the row:Selected Row

You can select all the cells in a column by clicking on the letter at the top of the column:Selected Column

Any formatting changes you make will be applied to all highlighted cells.

Hiding Worksheets

When working with worksheets and workbooks, there may be times when you want to hide worksheets.

To hide a worksheet:

  1. Select the worksheet you want to hide.
  2. On the HOME tab, in the Cells group, select the Format drop-down arrow. Format in Cells Group of Home Tab
  3. Under Visibility, select Hide & Unhide, and then select Hide Sheet.Hide Sheet

The sheet is now hidden until you unhide it by selecting Unhide Sheet.

Adding Color to Worksheet Tabs

You can customize your workbook by applying colors to the sheet tabs. This can help differentiate the tabs and make them stand out.

To add color to worksheet tabs:

  1. Right-click the tab to which you want to add color.
  2. Select Tab Color. Select Tab Color
  3. Select a color from the displayed options. Tab Color Options

Adding Themes to Workbooks

You can further customize workbooks and worksheets by using a Microsoft Office theme. Themes apply a set of fonts, colors, and other effects.

To apply a theme to a worksheet:

  1. On the PAGE LAYOUT tab, in the Themes group, select Themes. Themes in Themes Group of Page Layout Tab
  2. From the drop-down menu, select a theme to apply to the worksheet. Themes Menu
  3. You can further customize the theme using the Colors, Fonts, and Effects drop-down menus within the Themes group. Colors, Fonts, and Effects Menus

Customize a Workbook Using Tab Colors and Themes

Duration: 5 to 10 minutes.

In this exercise, you will use colors and themes to customize a workbook.

  1. Open the Sales - Customize a Workbook.xlsx from your Excel2013.1/Exercises folder.
  2. Add a color of your choice to each of the workbook tabs. In the solution, we use Blue for Sheet 1 and Red for Sheet 2.
  3. Apply a theme of your choice to your workbook. In the solution, we use the Banded theme.

Solution:

  1. Open or go to the specified file.
  2. Right-click the Sheet1 tab and select Tab Color. Tab Color
  3. From the Tab Color list, select a color. Tab Color List
  4. Right-click the Sheet2 tab and select Tab Color, and then select a color from the list.
  5. From the PAGE LAYOUT tab, in the Themes group, select a theme to apply it to your worksheet. Themes Menu
  6. Save the workbook.

Adding a Watermark

You can insert a watermark in an Excel worksheet by inserting a background image or text.

To add a watermark to a worksheet:

  1. On the INSERT tab, from the Text group, select Header & Footer. Header and Footer in Text Group of Insert Tab
  2. From the HEADER & FOOTER TOOLS tab, from the Header & Footer Elements section, select Picture. Picture in Header and Footer Elements Section
  3. Use the Insert Picture dialog box to select a picture; you can select a saved image, ClipArt, or search the Internet for a picture. Insert Picture Dialog Box
  4. When inserted, you will see: Inserted Picture
  5. Click any cell in the worksheet and the image should appear. Inserted Picture

Working with Font Group Commands

Duration: 10 to 15 minutes.

In this exercise, you will use commands found on the Font group to begin formatting the profit & loss statement for Dave's Lemonade Stand.

  1. Continue working in the workbook from the last exercise or open Dave's Lemonade Stand - Working with Font Group Commands.xlsx from your Excel2013.1/Exercises folder.
  2. Format the profit & loss statement for Dave's Lemonade Stand so that it looks like the below:Final Spreadsheet For the fill color, choose any color you like. In the solution, we use Red, Accent 2, Lighter 60%.

Solution:

  1. Open or go to the specified file.
  2. Select column A by clicking on the "A" at the top of the column:Column A
  3. On the HOME tab, in the Font group, click the Bold command.Bold in Font Group of Home Tab
  4. Select row 1 by clicking on the "1" to the left of the row:Row 1
  5. Click the Bold command twice (as cell A1 is already bolded, the first time you click bold, A1 will be unbolded, making all cells the same. The second time you click bold, all cells in the row will be bolded).
  6. Select cell A2 and click the drop-down arrow next to the Font Size command and select 14:Font Group
    1. Repeat this step for cells A5, A6, A10 and A11.
  7. Select cells A1:E11 and click the drop-down arrow next to the Shading command and select Outside Borders:Outside Borders
    1. Repeat this step, choosing the appropriate borders selection, until all borders have been added.
  8. Select cells A1:E1 and click the drop-down arrow next to the Fill Color command and select a color:Fill Colors
    1. Repeat this step for cells A5:E5 and A10:E11.
  9. Save the workbook.

The Alignment Group

Align Text

Text within cells in Microsoft Excel can be aligned both vertically (top, center and bottom) and horizontally (left, center and right).

To align text vertically within a cell or cells in Microsoft Excel:

  1. Select the cell or cells in which you wish to align the text.
  2. On the HOME tab, in the Alignment group, click either the Top Align, Middle Align or Bottom Align command:Alignment Group

To align text horizontally within a cell or cells in Microsoft Excel:

  1. Select the cell or cells in which you wish to align the text.
  2. On the HOME tab, in the Alignment group, click either the Align Text Left, Center or Align Text Right command:Alignment Group

Wrap Text

By default, text in Microsoft Excel remains on one line. Wrapping text is a way of getting text to show up on multiple lines within a cell. See the following example:Wrapped Text

To wrap text within a cell or cells in Microsoft Excel:

  1. Select the cell or cells in which you wish to wrap the text.
  2. On the HOME tab, in the Alignment group, click Wrap Text:Wrap Text in Alignment Group of Home Tab

Indent Text

Indenting text is a way of showing that one item is a sub-item of another, as demonstrated here:Indented Text

Instead of indenting sub-headings with spaces, you can do so using the Increase Indent command, which makes it a lot easier to keep all indented text equally indented.

To indent text within a cell or cells in Microsoft Excel:

  1. Select the cell or cells in which you wish to indent text.
  2. On the HOME tab, in the Alignment group, click the Increase Indent command:Increase Indent in Alignment Gourp of Home Tab
  3. You can click Increase Indent as many times as you want to achieve the indentation you desire.
  4. To decrease an indent, simply click the Decrease Indent command:Decrease Indent Command in Alignment Group of Home Tab

Merge & Center Text

Often a label applies to multiple columns. In these cases, it is useful to merge cells to show this. In the following example, the years "2012" and "2013" each apply to four columns:Merged Columns

To merge cells in Microsoft Excel:

  1. Select the cells you wish to merge.
  2. On the HOME tab, in the Alignment group, click the Merge & Center command:Merge and Center in Alignment Group of Home Tab

Working with Alignment Group Commands

Duration: 10 to 15 minutes.

In this exercise, you will use commands found on the Alignment group to continue formatting the profit & loss statement for Dave's Lemonade Stand.

  1. Continue working in the workbook from the last exercise or open Dave's Lemonade Stand - Working with Alignment Group Commands.xlsx from your Excel2013.1/Exercises folder.
  2. Format the profit & loss statement for Dave's Lemonade Stand so that it looks like the below:Final Spreadsheet

Solution:

  1. Open or go to the specified file.
  2. Select cells B1:E1 and click the Center command:Center Selected Cells
  3. Select cells A3 and A4 and click the Increase Indent command:Increase Indent of Selected Cells
    1. Repeat for cells A7:A9.
  4. Select cell A5 and click the Wrap Text command:Wrap Text in Selected Cell
    1. Repeat for cells A10 and A11.
  5. Save the workbook.

The Number Group

By default, numbers in Microsoft Excel do not show commas and do show the first two decimals (unless they are 0). However, numbers can be formatted to appear in many different ways.

Number Formats

Numbers in Excel can be formatted to show commas, show currency symbols, appear as percentages, and more.

To display numbers with a thousands separator (a comma) in Microsoft Excel:

  1. Select the cells for which you wish to display numbers with a thousands separator.
  2. On the HOME tab, in the Number group, click the Comma Style command:Comma Style in Number Group of Home Tab

To display numbers with a currency symbol in Microsoft Excel:

  1. Select the cells for which you wish to display a currency symbol.
  2. On the HOME tab, in the Number group, click the Accounting Number Format command (or the drop-down arrow to select a currency symbol other than the one displayed):Accounting Number Format in Number Group of Home Tab

To display numbers as percentages in Microsoft Excel:

  1. Select the cells for which you wish to display numbers as percentages.
  2. On the HOME tab, in the Number group, click the Percent Style command:Percent Style in Number Group of Home Tab

Date Formats

Microsoft Excel actually stores dates as numbers, so displaying dates is really a formatting issue. Excel allows you to display dates in many ways. For example, January 15, 2013 can be displayed include:

  1. 01/15/2013
  2. 01/15/13
  3. 1/15/13
  4. 1/15
  5. 15-Jan
  6. 15-Jan-13
  7. Jan-13
  8. January-13
  9. January 15, 2013
  10. Saturday, January 15, 2013

To set or change the way dates are displayed in Microsoft Excel:

  1. Select the cell or cells for which you wish to change the way dates are displayed.
  2. On the HOME tab, in the Number group, click the Dialog Box Launcher:Dialog Box Launcher
  3. In the Format Cells dialog box, in the Number tab, select Date in the Category box and choose a format from the Type box:Format Cells Dialog Box
  4. Click OK.

Showing Decimals

To change the number of decimals showing for numbers in Microsoft Excel:

  1. Select the cell or cells for which you wish to change the number of decimals showing for numbers.
  2. On the HOME tab, in the Number group, click the Increase Decimal or the Decrease Decimal command:Increase and Decrease Decimal Commands

Working with Number Group Commands

Duration: 10 to 15 minutes.

In this exercise, you will use commands found on the Number group to continue formatting the profit & loss statement for Dave's Lemonade Stand.

  1. Continue working in the workbook from the last exercise or open Dave's Lemonade Stand - Working with Number Group Commands.xlsx from your Excel2013.1/Exercises folder.
  2. Format the profit & loss statement for Dave's Lemonade Stand so that it looks like the below:Final Spreadsheet

Solution:

  1. Open or go to the specified file.
  2. Select cells B1:E1:Selected Cells
  3. On the HOME tab, in the Number group, click the Dialog Box Launcher:Dialog Box Launcher
  4. In the Format Cells dialog box, in the Number tab, select Date in the Category box and choose the "Mar-12" format from the Type box and click OK:Format Cells Dialog Box
  5. Select cells B3:E4:Selected Cells
  6. On the HOME tab, in the Number group, click the Comma Style command:Comma Style in Number Group of Home Tab
  7. On the HOME tab, in the Number group, click the Decrease Decimal command:Decrease Decimal in Number Group of Home Tab
  8. Save the workbook.