1.3 Formatting and Data Analysis

Learning Objectives

  1. Use formatting techniques as introduced in the Excel Spreadsheet Guidelines to enhance the appearance of a worksheet.
  2. Understand how to align data in cell locations.
  3. Examine how to enter multiple lines of text in a cell location.
  4. Understand how to add borders to a worksheet.
  5. Examine how to use the AutoSum feature to calculate totals.
  6. Use the Cut, Copy, and Paste commands to manipulate the data on a worksheet.
  7. Understand how to move, rename, insert, and delete worksheet tabs.

This section addresses formatting commands that can be used to enhance the visual appearance of a worksheet. It also provides an introduction to mathematical calculations. The skills introduced in this section will give you powerful tools for analyzing the data that we have been working with in this workbook and will highlight how Excel is used to make key decisions in virtually any career. Additionally, Excel Spreadsheet Guidelines for format and appearance will be introduced as a format for the course and spreadsheets submitted.

Formatting Data and Cells

Enhancing the visual appearance of a worksheet is a critical step in creating a valuable tool for you or your coworkers when making key decisions. There are accepted professional formatting standards when spreadsheets contain only currency data. For this course, we will use the following Excel Guidelines for Formatting. The first figure displays how to use Accounting number format when ALL figures are currency. Only the first row of data and the totals should be formatted with the Accounting format.  The other data should be formatted with Comma style.  There also needs to be a Top Border above the numbers in the total row.  If any of the numbers have cents, you need to format all of the data with two decimal places.

Format Guidelines (used when both currency and non-currency are reflected in a worksheet). Excel Guidelines for Units and Dollar Amounts in Same Worksheet. Three-line title for workbooks not containing Documentation sheet: Company Name, Type of Report, Date. When mixing units and dollars columns, format entire dollar column with Accounting Number format ($). No decimals when dollar amounts are whole dollars, no cents. Remember: Spellcheck. Print preview before printing or submitting. Proofreading common sense: do results make sense? Make sure worksheet looks professional.
Figure 1.31a

Often, your Excel spreadsheet will contain values that are both currency and non-currency in nature. When that is the case, you’ll want to use the guidelines in the following figure:

 

Excel Guidelines for Units and Dollar Amounts in Same Worksheet. Three-line title for workbooks not containing Documentation sheet: Company Name, Type of Report, Date. When mixing units and dollars columns, format entire dollar column with Accounting Number format ($). No decimals when dollar amounts are whole dollars, no cents. Remember: Spellcheck. Print preview before printing or submitting. Proofreading common sense: do results make sense? Make sure worksheet looks professional.
Figure 1.31b

The following steps demonstrate several fundamental formatting skills that will be applied to the workbook that we are developing for this chapter. Several of these formatting skills are identical to ones that you may have already used in other Microsoft applications such as Microsoft® Word® or Microsoft® PowerPoint®.

  1. Highlight the range A2:D2 in the Sheet1 worksheet by placing the mouse pointer over cell A2 and left clicking and dragging to cell D2. Click the Bold button in the Font group of commands in the Home tab of the ribbon.
  2. Click the Border button in the Font group of commands in the Home tab of the Ribbon (see Figure 1.32). Select the Bottom Border option from the list to achieve the goal of a border on the bottom of row 2 below the column headings.
    Font group of commands in Home tab containing Border button and commands such as font style, size, formatting, color, and cell fill color.
    Figure 1.32 Font Group of Commands

     

    Keyboard Shortcuts

    Bold Format

    • Hold the CTRL key while pressing the letter B on your keyboard.
  3. Highlight the range A15:D15 by placing the mouse pointer over cell A15 and left clicking and dragging to cell D15.
  4. Click the Bold button in the Font group of commands in the Home tab of the Ribbon.
  5. Click the Border button in the Font group of commands in the Home tab of the Ribbon (see Figure 1.32). Select the Top Border option from the list to achieve the goal of a border on the top of row 15 where totals will eventually display.

    Keyboard Shortcuts

    Italics Format

    • Hold the CTRL key while pressing the letter I on your keyboard.

     

    Keyboard Shortcuts

    Underline Format

    • Hold the CTRL key while pressing the letter U on your keyboard.

     

    Why?

    Format Column Headings and Totals

    Applying formatting enhancements to the column headings and column totals in a worksheet is a very important technique, especially if you are sharing a workbook with other people. These formatting techniques allow users of the worksheet to clearly see the column headings that define the data. In addition, the column totals usually contain the most important data on a worksheet with respect to making decisions, and formatting techniques allow users to quickly see this information.

 

  1. Highlight the range B3:B14 by placing the mouse pointer over cell B3 and left clicking and dragging down to cell B14.
  2. Click the Comma Style button in the Number group of commands in the Home tab of the Ribbon. This feature adds a comma as well as two decimal places. (see Figure 1.33).
    Number group of commands: Comma Style Format (","), Increase & Decrease Decimal buttons, Accounting Number Format ("$").
    Figure 1.33 Number Group of Commands
  3. Since the figures in this range do not include cents, click the Decrease Decimal button in the Number group of commands in the Home tab of the Ribbon two times (see Figure 1.33).
  4. The numbers will also be reduced to zero decimal places.
  5. Highlight the range C3:C14 by placing the mouse pointer over cell C3 and left clicking and dragging down to cell C14.
  6. Click the Accounting Number Format button in the Number group of commands in the Home tab of the Ribbon (see Figure 1.33). This will add the US currency symbol and two decimal places to the values. This format is common when working with pricing data. As discussed above in the Formatting Data and Cells section, you will want to use Accounting format on all values in this range since the worksheet contains non-currency as well as currency data.
  7. Highlight the range D3:D14 by placing the mouse pointer over cell D3 and left clicking and dragging down to cell D14.
  8. Again, select the Accounting Number Format; this will add the US currency symbol to the values as well as two decimal places.
  9. Click the Decrease Decimal button in the Number group of commands in the Home tab of the Ribbon.
  10. This will add the US currency symbol to the values and reduce the decimal places to zero since there are no cents in these figures.
  11. Highlight the range A1:D1 by placing the mouse pointer over cell A1 and left clicking and dragging over to cell D1.
  12. Click the down arrow next to the Fill Color button in the Font group of commands in the Home tab of the Ribbon (see Figure 1.34). This will prepare the range for a worksheet title.
    Fill Color Palette drop-down menu with range of colors.
    Figure 1.34 Fill Color Palette

     

  13. Click the Blue, Accent 1, Darker 25% color from the palette (see Figure 1.34). Notice that as you move the mouse pointer over the color palette, you will see a preview of how the color will appear in the highlighted cells. Experiment with this feature.
  14. Click on A1 and enter the worksheet title: General Merchandise World and click on the check mark in the formula bar to enter this information.
  15. Since the black font is difficult to read on the blue background, you’ll change the font color to be more visible. Click the down arrow next to the Font Color button in the Font group of commands in the Home tab of the Ribbon; select White as the font color for this range (see Figure 1.32).
  16. Highlight the range A1:D15 by placing the mouse pointer over cell A1 and left clicking and dragging down to cell D15.
  17. Click the drop-down arrow on the right side of the Font button in the Home tab of the Ribbon; select Arial as the font for this range. (see Figure 1.32).
  18. Notice that as you move the mouse pointer over the font style options, you can see the font change in the highlighted cells.
  19. Expand the column width of Column D to 14 characters.

Why?

Pound Signs (####) Appear in Columns

When a column is too narrow for a long number, Excel will automatically convert the number to a series of pound signs (####). In the case of words or text data, Excel will only show the characters that fit in the column. However, this is not the case with numeric data because it can give the appearance of a number that is much smaller than what is actually in the cell. To remove the pound signs, increase the width of the column.

 

Figure 1.35 shows how the Sheet1 worksheet should appear after the formatting techniques are applied.

"General Merchandise World" Worksheet shows months of the year in Column A,"Unit Sale" in Column B, "Average" in Column C, and "Sales Dollars" in Column D. Columns A-D have data entered. Row 15, "Total Sales", is not calculated.
Figure 1.35 Formatting Techniques Applied

Data Alignment (Wrap Text, Merge Cells, and Center)

The skills presented in this segment show how data are aligned within cell locations. For example, text and numbers can be centered in a cell location, left justified, right justified, and so on. In some cases you may want to stack multiword text entries vertically in a cell instead of expanding the width of a column. This is referred to as wrapping text. These skills are demonstrated in the following steps:

  1. Highlight the range B2:D2 by placing the mouse pointer over cell B2 and left clicking and dragging over to cell D2.
  2. Click the Center button in the Alignment group of commands in the Home tab of the Ribbon (see Figure 1.36). This will center the column headings in each cell location.
    Alignment Group in Home Tab: Vertical and Horizontal alignment, Increase and Decrease Indent, Wrap Text, Merge & Center buttons.
    Figure 1.36 Alignment Group in Home Tab

     

  3. Click the Wrap Text button in the Alignment group (see Figure 1.36). The height of Row 2 automatically expands, and the words that were cut off because the columns were too narrow are now stacked vertically.

    Keyboard Shortcuts

    Wrap Text

    • Press the ALT key and then the letters H and W one at a time.

    Why?

    Wrap Text

    The benefit of using the Wrap Text command is that it significantly reduces the need to expand the column width to accommodate multiword column headings. The problem with increasing the column width is that you may reduce the amount of data that can fit on a piece of paper or one screen. This makes it cumbersome to analyze the data in the worksheet and could increase the time it takes to make a decision.

  4. Highlight the range A1:D1 by placing the mouse pointer over cell A1 and left clicking and dragging over to cell D1.
  5. Click the down arrow on the right side of the Merge & Center button in the Alignment group of commands in the Home tab of the Ribbon.
  6. Left click the Merge & Center option (see Figure 1.37). This will create one large cell location running across the top of the data set.

Keyboard Shortcuts

Merge Commands

  • Merge & Center: Press the ALT key and then the letters H, M, and C one at a time.
  • Merge Cells: Press the ALT key and then the letters H, M, and M one at a time.
  • Unmerge Cells: Press the ALT key and then the letters H, M, and U one at a time.

 

 

Merge Cell Drop-Down Menu featuring Merge & Center, Merge Across, Merge Cells without centering data, and Unmerge Cells to break a merged cell into separate cells.
Figure 1.37 Merge Cell Drop-Down Menu

Why?

Merge & Center

One of the most common reasons the Merge & Center command is used is to center the title of a worksheet directly above the columns of data. Once the cells above the column headings are merged, a title can be centered above the columns of data. It is very difficult to center the title over the columns of data if the cells are not merged.

 

Figure 1.38 shows the Sheet1 worksheet with the data alignment commands applied. The reason for merging the cells in the range A1:D1 will become apparent in the next segment.

Cell range A1:D1 merged into one cell for title "General Merchandise World". A:2 has "Month" as title, then Wrap Text feature applied to show full titles in range B2:D2 as "Unit Sales", "Average Price", and "Sales Dollars".
Figure 1.38 Sheet1 with Data Alignment Features Added

 

Skill Refresher

Wrap Text

  1. Activate the cell or range of cells that contain text data.
  2. Click the Home tab of the Ribbon.
  3. Click the Wrap Text button.

 

Skill Refresher

Merge Cells

  1. Highlight a range of cells that will be merged.
  2. Click the Home tab of the Ribbon.
  3. Click the down arrow next to the Merge & Center button.
  4. Select an option from the Merge & Center list.

Entering Multiple Lines of Text

In the Sheet1 worksheet, the cells in the range A1:D1 were merged for the purposes of adding a title to the worksheet. This worksheet will contain both a title and a subtitle. The following steps explain how you can enter text into a cell and determine where you want the second line of text to begin:

  1. Activate cell A1 in the Sheet1 worksheet by placing the mouse pointer over cell A1 and clicking the left mouse button. Since the cells were merged, clicking cell A1 will automatically activate the range A1:D1. Position your mouse to the end of the title, directly after the “d” in the word “World” and double-click to get a cursor (flashing I-beam).
  2. Hold down the ALT key and press the ENTER key. This will start a new line of text in this cell location.
  3. Type the text Retail Sales (in millions) and press the ENTER key.
  4. Select cell A1. Then click the Italics and Bold buttons in the Font group of commands in the Home tab of the Ribbon.
  5. Increase the height of Row 1 to 30 points. Once the row height is increased, all the text typed into the cell will be visible (see Figure 1.39).
"Retail Sales (in millions)" added as subtitle in merged cell range A1:D1.
Figure 1.39 Title & Subtitle Added to the Worksheet

 

Skill Refresher

Entering Multiple Lines of Text

  1. Activate a cell location.
  2. Type the first line of text.
  3. Hold down the ALT key and press the ENTER key.
  4. Type the second line of text and press the ENTER key.

Borders (Adding Lines to a Worksheet)

In Excel, adding custom lines to a worksheet is known as adding borders. Borders are different from the grid lines that appear on a worksheet and that define the perimeter of the cell locations. The Borders command lets you add a variety of line styles to a worksheet that can make reading the worksheet much easier. The following steps illustrate methods for adding preset borders and custom borders to a worksheet:

  1. Click the down arrow to the right of the Borders button in the Font group of commands in the Home page of the Ribbon to view border options. (see Figure 1.40).Figure 1.40 Borders Drop-Down MenuFormat Borders Drop-Down Menu options
  2. Highlight the range A1:D15. Left click the All Borders option from the Borders drop-down menu (see Figure 1.40). This will add vertical and horizontal lines to the range A1:D15.
  3. Highlight the range A2:D2 by placing the mouse pointer over cell A2 and left clicking and dragging over to cell D2.
  4. Click the down arrow to the right of the Borders button.
  5. Left click the Thick Bottom Border option from the Borders drop-down menu.
  6. Highlight the range A14:D14 and apply a Thick Bottom Border from the drop-down menu. The thick border will help maintain the Excel Formatting Guidelines.
  7. Highlight the range A1:D15.
  8. Click the down arrow to the right of the Borders button.
  9. Click More Borders… at the bottom of the List.
  10. This will open the Format Cells dialog box (see Figure 1.41). You can access all formatting commands in Excel through this dialog box.
  11. In the Style section of the Borders tab, left click the thickest line style (see Figure 1.41).
  12. Left click the Outline button in the Presets section (see Figure 1.41).
  13. Click the OK button at the bottom of the dialog box (see Figure 1.41).

 

Format Cells Dialog Box options including outline, line placement in a highlighted cell range, and thickest line style.
Figure 1.41 Borders Tab of the Format Cells Dialog Box
Borders added with bold outlines and thick borders around column titles and Total Sales.
Figure 1.42 Borders Added to the Sheet1 Worksheet

 

Skill Refresher

Preset Borders

  1. Highlight a range of cells that require borders.
  2. Click the Home tab of the Ribbon.
  3. Click the down arrow next to the Borders button.
  4. Select an option from the preset borders list.

Custom Borders

  1. Highlight a range of cells that require borders.
  2. Click the Home tab of the Ribbon.
  3. Click the down arrow next to the Borders button.
  4. Select the More Borders option at the bottom of the options list.
  5. Select a line style and line color.
  6. Select a placement option.
  7. Click the OK button on the dialog box.

 

AutoSum

You will see at the bottom of Figure 1.42 that Row 15 is intended to show the totals for the data in this worksheet. Applying mathematical computations to a range of cells is accomplished through functions in Excel. Chapter 2 “Mathematical Computations” will review mathematical formulas and functions in detail. However, the following steps will demonstrate how you can quickly sum the values in a column of data using the AutoSum command:

  1. Activate cell B15 in the Sheet1 worksheet.
  2. Click the Formulas tab of the Ribbon.
  3. Click the down arrow below the AutoSum button in the Function Library group of commands (see Figure 1.43). Note that the AutoSum button can also be found in the Editing group of commands in the Home tab of the Ribbon.
    AutoSum Drop-Down menu in Formulas tab: Sum, Average, Count Numbers, Max, Min, and More Functions sub-menu.
    Figure 1.43 AutoSum Drop-Down List

     

  4. Click the Sum option from the AutoSum drop-down menu. The first click will display a flashing marquee around the range. Click the check mark next to the Formula bar to complete the function.
  5. Excel will provide a total for the values in the Unit Sales column.
  6. Activate cell D15. It would not make sense to total the averages in column C so C15 will be left blank.
  7. Repeat steps 3 through 5 to sum the values in the Sales Dollars column (see Figure 1.44).
Total Sales calculated for Unit Sales and Sales Dollars bold in cells B:15 and D:15.
Figure 1.44 Totals Added to the Sheet1 Worksheet

Skill Refresher

AutoSum

  1. Highlight a cell location below or to the right of a range of cells that contain numeric values.
  2. Click the Formulas tab of the Ribbon.
  3. Click the down arrow below the AutoSum button.
  4. Select a mathematical function from the list.

Moving, Renaming, Inserting, and Deleting Worksheets

The default names for the worksheet tabs at the bottom of workbook are Sheet1, Sheet2, and so on. However, you can change the worksheet tab names to identify the data you are using in a workbook. Additionally, you can change the order in which the worksheet tabs appear in the workbook. The following steps explain how to rename and move the worksheets in a workbook:

  1. With the left mouse button, double click the Sheet1 worksheet tab at the bottom of the workbook (see Figure 1.45). Type the name Sales by Month.
  2. Press the ENTER key on your keyboard.
  3. With the left mouse button, double click the Sheet2 worksheet tab at the bottom of the workbook.
  4. Type the name Unit Sales Rank to prepare the worksheet for future use.
  5. Press the ENTER key on your keyboard.
    Worksheet tabs at bottom of workbook can be dragged to change order, and named or renamed.
    Figure 1.45 Renaming a Worksheet Tab

     

  6. Click the Sheet3 worksheet tab.
  7. Click the Home tab of the Ribbon.
  8. Click the down arrow on the Delete button in the Cells group of commands.
  9. Click the Delete Sheet option from the drop-down list. This removes the unneeded worksheet.
  10. Click the Delete button on the Delete warning box (if a warning box appears).
  11. Complete the steps above to delete the newly named Unit Sales Rank worksheet since it’s decided that worksheet is also unnecessary so that you are left with just one worksheet.
  12. Save the changes to your workbook by clicking either the Save button on the Home ribbon; or by selecting the Save option from the File menu.

 

Integrity Check

Deleting Worksheets

Be very cautious when deleting worksheets that contain data. Once a worksheet is deleted, you cannot use the Undo command to bring the sheet back. Deleting a worksheet is a permanent command.

 

Keyboard Shortcuts

Inserting New Worksheets

  • Press the SHIFT key and then the F11 key on your keyboard.

Figure 1.46 shows the final appearance of the GMW Sales workbook.

 

Final appearance of the GMW Sales Data workbook after worksheet tabs have been renamed and moved.
Figure 1.46 Final Appearance of the GMW Sales Workbook

 

Skill Refresher

Renaming Worksheets

  1. Double click the worksheet tab.
  2. Type the new name.
  3. Press the ENTER key.

Moving Worksheets

  1. Left click the worksheet tab.
  2. Drag it to the desired position.

Deleting Worksheets

  1. Open the worksheet to be deleted.
  2. Click the Home tab of the Ribbon.
  3. Click the down arrow on the Delete button.
  4. Select the Delete Sheet option.
  5. Click Delete on the warning box.

Key Takeaways

  • Formatting skills are critical for creating worksheets that are easy to read and have a professional appearance.
  • A series of pound signs (####) in a cell location indicates that the column is too narrow to display the number entered.
  • Using the Wrap Text command allows you to stack multiword column headings vertically in a cell location, reducing the need to expand column widths.
  • Use the Merge & Center command to center the title of a worksheet directly over the columns that contain data.
  • Adding borders or lines will make your worksheet easier to read and helps to separate the data in each column and row.
  • You cannot use the Undo command to bring back a worksheet that has been deleted.

Attribution

Adapted by Barbara Lave from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0.

License

Icon for the Creative Commons Attribution 4.0 International License

1.3 Formatting and Data Analysis by Noreen Brown, Barbara Lave, Julie Romey, Mary Schatz, Diane Shingledecker is licensed under a Creative Commons Attribution 4.0 International License, except where otherwise noted.