This is “Formatting and Data Analysis”, section 1.3 from the book Using Microsoft Excel (v. 1.0). For details on it (including licensing), click here.

For more information on the source of this book, or why it is available for free, please see the project's home page. You can browse or download additional books there. To download a .zip file containing this book to use offline, simply click here.

Has this book helped you? Consider passing it on:
Creative Commons supports free culture from music to education. Their licenses helped make this book available to you.
DonorsChoose.org helps people like you help teachers fund their classroom projects, from art supplies to books to calculators.

1.3 Formatting and Data Analysis

Learning Objectives

  1. Use formatting techniques 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. Understand how to insert a chart into a worksheet.
  7. Use the Cut, Copy, and Paste commands to manipulate the data on a worksheet.
  8. Examine how to use the Sort command to rank data on a worksheet.
  9. 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 and charts. 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.

Formatting Data and Cells

Follow-along file: Excel Objective 1.0 (Use file Excel Objective 1.04 if you are starting with this skill.)

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. 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.
  2. Click the Bold button in the Font group of commands in the Home tab of the Ribbon (see Figure 1.36 "Font Group of Commands").

    Figure 1.36 Font Group of Commands

    Mouseless Command

    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 Italics button in the Font group of commands in the Home tab of the Ribbon (see Figure 1.36 "Font Group of Commands").
  6. Click the Underline button in the Font group of commands in the Home tab of the Ribbon (see Figure 1.36 "Font Group of Commands"). Notice that there is a drop-down arrow next to the Underline button. This is for selecting a double underline format, which is common in careers that deal with accounting or budgeting activities.

    Mouseless Command

    Italics Format

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

    Mouseless Command

    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.

  7. Highlight the range B3:B14 by placing the mouse pointer over cell B3 and left clicking and dragging down to cell B14.
  8. Click the Comma Style button in the Number group of commands in the Home tab of the Ribbon (see Figure 1.37 "Number Group of Commands").

    Figure 1.37 Number Group of Commands

  9. Click the Decrease Decimal button in the Number group of commands in the Home tab of the Ribbon (see Figure 1.37 "Number Group of Commands").
  10. The numbers will also be reduced to zero decimal places.
  11. Highlight the range C3:C14 by placing the mouse pointer over cell C3 and left clicking and dragging down to cell C14.
  12. Click the Accounting Number Format button in the Number group of commands in the Home tab of the Ribbon (see Figure 1.37 "Number Group of Commands"). This will add the US currency symbol and two decimal places to the values. This format is common when working with pricing data.
  13. Highlight the range D3:D14 by placing the mouse pointer over cell D3 and left clicking and dragging down to cell D14.
  14. Again, this will add the US currency symbol to the values as well as two decimal places.
  15. Click the Decrease Decimal button in the Number group of commands in the Home tab of the Ribbon.
  16. This will add the US currency symbol to the values and reduce the decimal places to zero.
  17. Highlight the range A1:D1 by placing the mouse pointer over cell A1 and left clicking and dragging over to cell D1.
  18. 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.38 "Fill Color Palette").

    Figure 1.38 Fill Color Palette

  19. Click the Aqua, Accent 5, Darker 25% color from the palette (see Figure 1.38 "Fill Color Palette"). 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.
  20. Click the down arrow next to the Font Color button in the Font group of commands in the Home tab of the Ribbon (see Figure 1.36 "Font Group of Commands").
  21. This change will be visible once text is typed into the highlighted cells.
  22. Click the Increase Font Size button in the Font group of commands in the Home tab of the Ribbon (see Figure 1.38 "Fill Color Palette").
  23. Highlight the range A1:D15 by placing the mouse pointer over cell A1 and left clicking and dragging down to cell D15.
  24. Click the drop-down arrow on the right side of the Font button in the Home tab of the Ribbon (see Figure 1.36 "Font Group of Commands").
  25. Notice that as you move the mouse pointer over the font style options, you can see the font change in the highlighted cells.
  26. Expand the row width of Column D to 10 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.39 "Formatting Techniques Applied" shows how the Sheet1 worksheet should appear after the formatting techniques are applied.

Figure 1.39 Formatting Techniques Applied

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

Follow-along file: Excel Objective 1.0 (Use file Excel Objective 1.06 if you are starting with this skill.)

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 textStacking multiword text entries vertically in a cell.. 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.40 "Alignment Group in Home Tab"). This will center the column headings in each cell location.

    Figure 1.40 Alignment Group in Home Tab

  3. Click the Wrap Text button in the Alignment group (see Figure 1.40 "Alignment Group in Home Tab"). The height of Row 2 automatically expands, and the words that were cut off because the columns were too narrow are now stacked vertically (see Figure 1.42 "Sheet1 with Data Alignment Features Added").

    Mouseless Command

    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.41 "Merge Cell Drop-Down Menu"). This will create one large cell location running across the top of the data set.

Mouseless Commands

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.

Figure 1.41 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.42 "Sheet1 with Data Alignment Features Added" 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.

Figure 1.42 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

Follow-along file: Excel Objective 1.0 (Use file Excel Objective 1.07 if you are starting with this skill.)

In the Sheet1 worksheet, the cells in the range A1:D1 were merged for the purposes of adding a title to the worksheet. This title will require that two lines of text be entered into a cell. 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.
  2. Type the text General Merchandise World.
  3. Hold down the ALT key and press the ENTER key. This will start a new line of text in this cell location.
  4. Type the text 2011 Retail Sales (in millions) and press the ENTER key.
  5. Select cell A1. Then click the Italics button in the Font group of commands in the Home tab of the Ribbon.
  6. 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.43 "Title Added to the Sheet1 Worksheet").

Figure 1.43 Title Added to the Sheet1 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)

Follow-along file: Excel Objective 1.0 (Use file Excel Objective 1.08 if you are starting with this skill.)

In Excel, adding custom lines to a worksheet is known as adding borders. BordersLines that are added to a worksheet to separate the data in columns and rows. 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. Note that when you click on cell A1, cells B1:D1 will also activate since they are merged.
  2. Click the down arrow to the right of the Borders button in the Font group of commands in the Home page of the Ribbon (see Figure 1.44 "Borders Drop-Down Menu").

    Figure 1.44 Borders Drop-Down Menu

  3. Left click the All Borders option from the Borders drop-down menu (see Figure 1.44 "Borders Drop-Down Menu"). This will add vertical and horizontal lines to the range A1:D15.
  4. Highlight the range A2:D2 by placing the mouse pointer over cell A2 and left clicking and dragging over to cell D2.
  5. Click the down arrow to the right of the Borders button.
  6. Left click the Thick Bottom Border option from the Borders drop-down menu.
  7. Highlight the range A1:D15.
  8. Click the down arrow to the right of the Borders button.
  9. This will open the Format Cells dialog box (see Figure 1.45 "Borders Tab of the Format Cells Dialog Box"). You can access all formatting commands in Excel through this dialog box.
  10. In the Style section of the Borders tab, left click the thickest line style (see Figure 1.45 "Borders Tab of the Format Cells Dialog Box").
  11. Left click the Outline button in the Presets section (see Figure 1.45 "Borders Tab of the Format Cells Dialog Box").
  12. Click the OK button at the bottom of the dialog box (see Figure 1.45 "Borders Tab of the Format Cells Dialog Box").

Figure 1.45 Borders Tab of the Format Cells Dialog Box

Figure 1.46 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.

Skill Refresher: 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

Follow-along file: Excel Objective 1.0 (Use file Excel Objective 1.09 if you are starting with this skill.)

You will see at the bottom of Figure 1.46 "Borders Added to the Sheet1 Worksheet" 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 functionsMathematical computations that are applied to a range of cells or specific cells on a worksheet. 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.47 "AutoSum Drop-Down List"). Note that the AutoSum button can also be found in the Editing group of commands in the Home tab of the Ribbon.

    Figure 1.47 AutoSum Drop-Down List

  4. Click the Sum option from the AutoSum drop-down menu.
  5. Excel will provide a total for the values in the Unit Sales column.
  6. Activate cell D15.
  7. Repeat steps 3 through 5 to sum the values in the Sales Dollars column (see Figure 1.48 "Totals Added to the Sheet1 Worksheet").
  8. This will remove the pound signs (####) and show the total.

Figure 1.48 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.

Inserting a Column Chart

Follow-along file: Excel Objective 1.0 (Use file Excel Objective 1.10 if you are starting with this skill.)

As mentioned at the beginning of this chapter, Excel serves as a critical tool for making decisions in both personal and professional contexts. ChartsTools used to graphically display the data in a worksheet. are a powerful tool in Excel that allow you to graphically display the data in a worksheet. Graphical displays allow the reader to immediately identify key trends and behaviors in the data that is being analyzed. For the workbook that we are using for this chapter, understanding the trends in monthly sales data is critical for making decisions such as how many staff members to assign to the store for each month as well as supplying the store with enough inventory to accommodate expected sales. To assist the reader in analyzing this data, a column chart will be created to graphically display the data. It is important for you to plan which type of chart will best display the data so your readers can quickly see key trends. More details on creating charts and on chart types will be presented in a later chapter. The following steps are an introduction to creating the column chart required for this chapter’s objective:

  1. Highlight the range A2:B14.
  2. Click the Insert tab of the Ribbon.
  3. Click the Column button (see Figure 1.49 "Column Chart Drop-Down Menu"). This will open the column chart drop-down menu of options.

    Figure 1.49 Column Chart Drop-Down Menu

  4. Select the Clustered Column option from the list of column chart options (see Figure 1.49 "Column Chart Drop-Down Menu"). This will create an embedded chart in the Sheet1 worksheet (see Figure 1.50 "Embedded Column Chart in Sheet1").

    Figure 1.50 "Embedded Column Chart in Sheet1" shows the column chart that is created once a selection is made from the column chart drop-down menu. Notice that there are three new tabs added to the Ribbon. These tabs contain features for enhancing the appearance and construction of Excel charts. These commands will be covered in more detail in a later chapter. For now, you will see that Excel places the chart over the data in the worksheet. The following steps explain how to move and resize the chart:

    Figure 1.50 Embedded Column Chart in Sheet1

  5. The block white plus sign will become black cross arrows (see Figure 1.50 "Embedded Column Chart in Sheet1").
  6. Left click and drag the chart so the upper left corner is placed in the middle of cell F1 (see Figure 1.51 "Moving an Embedded Chart").

    Figure 1.51 Moving an Embedded Chart

  7. Place the mouse pointer over the top center sizing handleDots that appear around the perimeter of an embedded chart in a worksheet. They can be clicked and dragged to desired dimensions to change the size of a chart. (see Figure 1.50 "Embedded Column Chart in Sheet1"). You will see the mouse pointer change from a white block plus sign to a vertical double arrow. Make sure the mouse pointer is not in the cross arrow mode as shown in Figure 1.50 "Embedded Column Chart in Sheet1" as this will move the chart instead of resizing it.
  8. While holding down the ALT key on your keyboard, left click and drag the mouse pointer slightly up. The chart will automatically adjust up to the top of Row 1.
  9. Place the mouse pointer over the left center sizing handle.
  10. While holding down the ALT key on your keyboard, left click and drag the mouse slightly toward the left. The chart will automatically adjust to the left side of Column F.
  11. Place the mouse pointer over the lower center sizing handle.
  12. While holding down the ALT key on your keyboard, left click and drag the mouse slightly down. The chart will automatically adjust to the bottom of Row 14.
  13. Place the mouse pointer over the right center sizing handle.
  14. While holding down the ALT key on your keyboard, left click and drag the mouse slightly to the right. The chart will automatically adjust to the right side of Column M.

    Why?

    There Are No Sizing Handles on a Chart

    If you do not see the dots or sizing handles around the perimeter of a chart, it could be that the chart is not activated. To activate a chart, left click anywhere on the chart.

    Figure 1.52 "Embedded Chart Moved and Resized" shows the column chart moved and resized. Notice that the sizing handles are not visible around the perimeter of the chart. This is because the chart is not activated. Once you click anywhere on the worksheet outside the chart area, the chart is automatically deactivated.

    Figure 1.52 Embedded Chart Moved and Resized

    Why?

    Use the ALT Key When Resizing a Chart

    Using the ALT key while resizing an embedded chart locks the perimeter of the chart to the columns and rows of the worksheet. This gives you the ability to adjust the chart to precise sizes as you adjust the width and height of the worksheet rows and columns.

    As shown in Figure 1.50 "Embedded Column Chart in Sheet1", when a chart is created, three tabs are added to the Ribbon. The following steps explain how to use a few of the formatting and design features in these tabs:

  15. Check to make sure the column chart in Sheet1 is activated. To activate the chart, left click anywhere on the chart.
  16. Click the Design tab under the Chart Tools set of tabs on the Ribbon.
  17. Click the down arrow on the right side of the Chart Styles section (see Figure 1.53 "Chart Styles in the Design Tab").

    Figure 1.53 Chart Styles in the Design Tab

  18. Click Style 44 in the Chart Styles section. This style has a black background with red columns (see Figure 1.53 "Chart Styles in the Design Tab").
  19. Click the Format tab under the Chart Tools set of tabs on the Ribbon.
  20. Click the down arrow on the right side of the WordArt Styles section (see Figure 1.54 "WordArt Styles in the Format Tab").

Figure 1.54 WordArt Styles in the Format Tab

Click the Blue, Accent 1, Inner Shadow option (see Figure 1.54 "WordArt Styles in the Format Tab"). Notice that as you move the mouse pointer over the WordArt Styles options, the format of the chart title as well as the X and Y axis titles changes.

Figure 1.55 "Formatting Features Applied to the Column Chart" shows the embedded column chart with the formatting features applied. This chart is very effective in displaying the Unit Sales trends for this company. You can see very quickly that the tallest bar in the chart is the month of December, followed by the months of June, July, January, and February.

Figure 1.55 Formatting Features Applied to the Column Chart

Skill Refresher: Creating a Column Chart

  1. Highlight a range of cells that contain data that will be used to create the chart.
  2. Click the Insert tab of the Ribbon.
  3. Click the Column button in the Charts group.
  4. Select an option from the Column drop-down menu.

Cut, Copy, and Paste

Follow-along file: Excel Objective 1.0 (Use file Excel Objective 1.11 if you are starting with this skill.)

The Cut, Copy, and Paste commands are perhaps the most widely used commands in Microsoft Office. With regard to Excel, the Copy and Paste commands are often used to make copies of worksheets for developing different scenarios or versions for the data being analyzed. The following steps demonstrate how these commands are used for the objective in this chapter:

  1. Click the Select All button in the upper left corner of the Sheet1 worksheet (see Figure 1.56 "Clipboard Group of Commands").

    Figure 1.56 Clipboard Group of Commands

  2. Click the Copy button in the Clipboard group of commands in the Home tab of the Ribbon (see Figure 1.56 "Clipboard Group of Commands").

    Mouseless Command

    Copy

    • Press the CTRL key and then the letter C key on your keyboard.
  3. Open the Sheet2 worksheet by left clicking on the Sheet2 worksheet tab at the bottom of the workbook.
  4. Activate cell location A1.
  5. Click the Paste button in the Clipboard group of commands in the Home tab of the Ribbon. Be sure to click the upper area of the Paste button and not the down arrow at the bottom of the button. A copy of Sheet1 will now appear in Sheet2.

    Mouseless Command

    Paste

    • Press the CTRL key and then the letter V key on your keyboard.
  6. Click anywhere on the chart in the Sheet2 worksheet.
  7. Click the Cut button in the Clipboard group on the Home tab of the Ribbon. This will remove the chart from the Sheet2 worksheet.

    Mouseless Command

    Cut

    • Press the CTRL key and then the letter X key on your keyboard.
  8. Open the Sheet3 worksheet by left clicking on the Sheet3 worksheet tab at the bottom of the workbook.
  9. Activate cell location A1.
  10. Click the Paste button in the Home tab of the Ribbon. This will paste the chart from the Sheet2 worksheet into the Sheet3 worksheet.

Sorting Data (One Level)

Follow-along file: Excel Objective 1.0 (Use file Excel Objective 1.12 if you are starting with this skill.)

As mentioned earlier in this section, a chart is a tool that enables worksheet readers to analyze data quickly to spot key trends or patterns. Another powerful tool that provides similar benefits is the SortAn Excel command used to rank the data in a worksheet based on designated criteria. command. This feature ranks the rows of data in a worksheet based on designated criteria. The following steps demonstrate how the Sort command is used to rank the data in the Sheet2 worksheet:

  1. In the Sheet2 worksheet, highlight the range A2:D14.
  2. Click the Data tab of the Ribbon.
  3. Click the Sort button in the Sort & Filter group of commands. This will open the Sort dialog box (see Figure 1.57 "Sort & Filter Group of Commands").

    Figure 1.57 Sort & Filter Group of Commands

  4. Click the down arrow next to the “Sort by” drop-down box in the Sort dialog box (see Figure 1.58 "Sort Dialog Box").

    Figure 1.58 Sort Dialog Box

  5. Click the Unit Sales option from the drop-down list.
  6. Click the down arrow next to the Order drop-down box.
  7. Click Largest to Smallest from the drop-down list.
  8. Click the OK button at the bottom of the Sort dialog box. The data in the range A2:D14 will now be sorted in descending order based on the values in the Unit Sales column.

Integrity Check

Sorting Data

Carefully check the highlighted range of the data you are sorting. It is critical that all columns in a contiguous range of data are highlighted before sorting. If you do not sort all the columns in a data set, the data could become corrupted in such a way that it may not be corrected. If Excel detects that you are trying to sort only part of a contiguous range of data, it will give you a warning dialog box.

Figure 1.59 "Data Sorted Based on Unit Sales" shows the data in the Sheet2 worksheet sorted based on the values in the Unit Sales column. Similar to the chart, the Sort command makes it easy to identify the months of the year with the highest unit sales.

Figure 1.59 Data Sorted Based on Unit Sales

Skill Refresher: Sorting Data (One Level)

  1. Highlight a range of cells to be sorted.
  2. Click the Data tab of the Ribbon.
  3. Click the Sort button in the Sort & Filter group.
  4. Select a column from the “Sort by” drop-down list.
  5. Select a sort order from the Order drop-down list.
  6. Click the OK button on the Sort dialog box.

Moving, Renaming, Inserting, and Deleting Worksheets

Follow-along file: Excel Objective 1.0 (Use file Excel Objective 1.13 if you are starting with this skill.)

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.60 "Renaming a Worksheet Tab").
  2. Type the name Sales by Month.
  3. Press the ENTER key on your keyboard.
  4. With the left mouse button, double click the Sheet2 worksheet tab at the bottom of the workbook.
  5. Type the name Unit Sales Rank.
  6. Press the ENTER key on your keyboard.

    Figure 1.60 Renaming a Worksheet Tab

  7. Left click and drag the Unit Sales Rank worksheet tab to the left of the Sales by Month worksheet tab.
  8. Click the Sheet3 worksheet tab.
  9. Click the Home tab of the Ribbon.
  10. Click the down arrow on the Delete button in the Cells group of commands.
  11. Click the Delete Sheet option from the drop-down list (see Figure 1.35 "Delete Drop-Down Menu").
  12. Click the Delete button on the Delete warning box.
  13. Click the Insert Worksheet tab at the bottom of the workbook (see Figure 1.60 "Renaming a Worksheet Tab").

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.

Mouseless Command

Inserting New Worksheets

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

Figure 1.61 "Final Appearance of the Excel Objective 1.0 Workbook" shows the final appearance of the Excel Objective 1.0 workbook after the worksheet tabs have been renamed and moved.

Figure 1.61 Final Appearance of the Excel Objective 1.0 Workbook

Skill Refresher: Renaming Worksheets

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

Skill Refresher: Moving Worksheets

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

Skill Refresher: 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.
  • Effective charts enable readers to immediately identify key trends in the data you are displaying.
  • Check to make sure all the data in a contiguous range of cells is highlighted before using the Sort command. Highlighting and sorting only part of a contiguous data set could corrupt your data in such a way that its integrity may not be restored.
  • You cannot use the Undo command to bring back a worksheet that has been deleted.

Exercises

  1. The pound signs (####) that appear in a cell location indicate that:

    1. A computational error has occurred.
    2. The AutoSum feature was applied to text data instead of numeric data.
    3. A number is too long for the current width of a column.
    4. You must click the Calculate Sheet command in the Formulas tab of the Ribbon.
  2. Which of the following is most accurate with respect to the Wrap Text command?

    1. It allows you to designate which words are placed on a second line in a cell.
    2. It reduces the need to expand the width of the columns in a worksheet.
    3. It converts any numeric data to text data.
    4. It can be accessed only through the right-click menu of options.
  3. What is the quickest way to center a title over six columns of data?

    1. Type the title into the cell location over the left-most column and use the space bar to try and place the title over the center of the six columns.
    2. Type the title into the cell location over the left-most column and click the Center alignment button in the Home tab of the Ribbon.
    3. Type the title into the cell location over the third column and use the BACKSPACE key to place the title over the center of the six columns.
    4. Highlight the six cell locations over each of the columns and click the Merge & Center button in the Home tab of the Ribbon.
  4. Which of the following is true with respect to deleting worksheets?

    1. You cannot use the Undo button to bring back a worksheet once it has been deleted.
    2. Click the Select All button and press the DELETE key on your keyboard to delete a worksheet from a workbook.
    3. Holding down the SHIFT key while pressing the F11 key on your keyboard will delete a worksheet from your workbook.
    4. Excel will not let you delete a worksheet that contains data. All data must be removed from the worksheet before the worksheet can be deleted.