Chapter 1
Section 1.3 – Formatting and Data Analysis
Learning Objectives
- Use formatting techniques as introduced in the Excel Spreadsheet Guidelines to enhance the appearance of a worksheet.
- Understand how to align data in cell locations.
- Examine how to enter multiple lines of text in a cell location.
- Understand how to add borders to a worksheet.
- Examine how to use the AutoSum feature to calculate totals.
- Use the Cell Styles menu to format totals in a worksheet.
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.
Often, your Excel spreadsheet will contain values that are both currency and non-currency in nature. When that is the case, you will want to use the guidelines in the following figure:
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®.
- 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.
- 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.
Keyboard Shortcuts
Bold Format:
Hold the CTRL key while pressing the letter B on your keyboard.
Italics Format:
Hold the CTRL key while pressing the letter I on your keyboard.
Underline Format:
Hold the CTRL key while pressing the letter U on your keyboard.
- Highlight the ra