Learn EXCEL fundamental expert skills

DATA FILES

Data files for each chapter are linked above assignments where students will need to use them. They are also collected on this page.

• 1.5 Chapter Practice:
• 1.6 Scored Assessment:
• 2.1 Formulas:
• 2.4 Preparing to Print:
• 2.5 Chapter Practice:
• 2.6 Chapter Scored:
• 3.1 More on Formulas and Functions:
• 3.4 Preparing to Print:
• 3.5 Chapter Practice:
• 3.6 Chapter Scored:
• 4.1 Choosing a Chart Type:
• 4.3 Using Charts with Microsoft Word and Microsoft Powerpoint: (W0rd) and (PPT)
• 4.5 Chapter Practice:
• 4.6 Chapter Scored:
• 5.1 Table Basics:
• 5.4 Chapter Practice:
• 5.5 Chapter Scored:
• 6.1 Multiple Sheet Basics:
• 6.5 Chapter Practice:

BEGINNING EXCEL v

Microsoft® Excel® is a tool that can be used in virtually all careers and is valuable in both professional and personal settings. Whether you need to keep track of medications in inventory for a hospital or create a financial plan for your retirement, Excel enables you to do these activities efficiently and accurately. This chapter introduces the fundamental skills necessary to get you started in using Excel. You will find that just a few skills can make you very productive in a short period of time.

ATTRIBUTION

Adapted from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The without attribution as requested by the work’s original creator or licensee, and licensed under .

Microsoft® Office contains a variety of tools that help people accomplish many personal and professional objectives. Microsoft Excel is perhaps the most versatile and widely used of all the Office applications. No matter which career path you choose, you will likely need to use Excel to accomplish your professional objectives, some of which may occur daily. This chapter provides an overview of the Excel application along with an orientation for accessing the commands and features of an Excel workbook.

MAKING DECISIONS WITH EXCEL

Taking a very simple view, Excel is a tool that allows you to enter quantitative data into an electronic spreadsheet to apply one or many mathematical computations. These computations ultimately convert that quantitative data into information. The information produced in Excel can be used to make decisions in both professional and personal contexts. For example, employees can use Excel to determine how much inventory to buy for a clothing retailer, how much medication to administer to a patient, or how much money to spend to stay within a budget. With respect to personal decisions, you can use Excel to determine how much money you can spend on a house, how much you can spend on car lease payments, or how much you need to save to reach your retirement goals. We will demonstrate how you can use Excel to make these decisions and many more throughout this text.

Figure 1.1 shows a completed Excel worksheet that will be constructed in this chapter. The information shown in this worksheet is top-line sales data for a hypothetical merchandise retail company. The worksheet data can help this retailer determine the number of salespeople needed for each month, how much inventory is needed to satisfy sales, and what types of products should be purchased.

Figure 1.1 Example of an Excel Worksheet

STARTING EXCEL

1. Locate Excel on your computer.
2. Click Microsoft Excel to launch the Excel application and present you with workbook options.
3. Click the first option; “Blank Workbook”.

THE EXCEL WORKBOOK

Once Excel is started, a blank workbook will open on your screen. A workbook is an Excel file that contains one or more worksheets (sometimes referred to as spreadsheets). Excel will assign a file name to the workbook, such as Book1, Book2, Book3, and so on, depending on how many new workbooks are opened. Figure 1.2 shows a blank workbook after starting Excel. Take some time to familiarize yourself with this screen. Your screen may be slightly different based on the version you’re using.

Figure 1.2 Blank Workbook

Your workbook should already be maximized (or shown at full size) once Excel is started, as shown in Figure 1.2. However, if your screen looks like Figure 1.3 after starting Excel, you should click the Maximize button, as shown in the figure.

Figure 1.3 Restored Worksheet

NAVIGATING WORKSHEETS

Data are entered and managed in an Excel worksheet. The worksheet contains several rectangles called cells for entering numeric and nonnumeric data. Each cell in an Excel worksheet contains an address, which is defined by a column letter followed by a row number. For example, the cell that is currently activated in Figure 1.3 is A1. This would be referred to as cell location A1 or cell reference A1. The following steps explain how you can navigate in an Excel worksheet:

1. Place your mouse pointer over cell D5 and left click.
2. Check to make sure column letter D and row number 5 are highlighted, as shown in Figure 1.4.

Note: Your highlighted column letter and row number may be different than figure shown.

Figure 1.4 Activating a Cell Location

1. Move the mouse pointer to cell A1.
2. Click and hold the left mouse button and drag the mouse pointer back to cell D5.
3. Release the left mouse button. You should see several cells highlighted, as shown in Figure 1.5.

This is referred to as a cell range and is documented as follows: A1:D5. Any two cell locations separated by a colon are known as a cell range. The first cell is the top left corner of the range, and the second cell is the lower right corner of the range.

Figure 1.5 Highlighting a Range of Cells

1. At the bottom of the screen, you’ll see worksheets. Depending on your version of Excel, you will see either three as displayed above or just one. If you only have one sheet, click the “Insert Worksheet” to add a worksheet. Depending on your version, you instead may have a + sign; a

click on the + adds an additional worksheet as well. This is how you open or add a worksheet within a workbook. Add another worksheet so that you now have three sheets displaying here.

2. Click the Sheet1 worksheet tab at the bottom of the worksheet to return to the worksheet shown in Figure 1.5.

Excel’s features and commands are found in the Ribbon, which is the upper area of the Excel screen that contains several tabs running across the top. Each tab provides access to a different set of Excel commands. Figure 1.6 shows the commands available in the Home tab of the Ribbon. Table 1.1 “Command Overview for Each Tab of the Ribbon” provides an overview of the commands that are found in each tab of the Ribbon.

Figure 1.6 Home Tab of Ribbon

Table 1.1 Command Overview for Each Tab of the Ribbon

The Ribbon shown in Figure 1.6 is full, or maximized. The benefit of having a full Ribbon is that the commands are always visible while you are developing a worksheet. However, depending on the screen dimensions of your computer, you may find that the Ribbon takes up too much vertical space on your worksheet. If this is the case, you can minimize the Ribbon by clicking the button shown in Figure 1.6. When minimized, the Ribbon will show only the tabs and not the command buttons. When you click on a tab, the command buttons will appear until you select a command or click anywhere on your worksheet.

QUICK ACCESS TOOLBAR AND RIGHT-CLICK MENU

The Quick Access Toolbar is found at the upper left side of the Excel screen above the Ribbon, as shown in Figure 1.7. This area provides access to the most frequently used commands, such as Save and Undo. You also can customize the Quick Access Toolbar by adding commands that you use on a regular basis. By placing these commands in the Quick Access Toolbar, you do not have to navigate through the Ribbon to find them. To customize the Quick Access Toolbar, click the down arrow as shown in Figure 1.7. This will open a menu of commands that you can add to the Quick Access Toolbar. If you do not see the command you are looking for on the list, select the More Commands option.

Figure 1.7 Customizing the Quick Access Toolbar

In addition to the Ribbon and Quick Access Toolbar, you can also access commands by right clicking anywhere on the worksheet. Figure 1.8 shows an example of the commands available in the rightclick menu.

Figure 1.8 Right-Click Menu

THE FILE TAB

The File tab is also known as the Backstage view of the workbook. It contains a variety of features and commands related to the workbook that is currently open, new workbooks, or workbooks stored in other locations on your computer or network. Figure 1.9 shows the options available in the File tab or Backstage view. To leave the Backstage view and return to the worksheet, click the arrow in the upper left-hand corner as shown below.

Figure 1.9 File Tab or Backstage View of a Workbook

Included in the File tab are the default settings for the Excel application that can be accessed and modified by clicking the Options button. Figure 1.10 shows the Excel Options window, which gives you access to settings such as the default font style, font size, and the number of worksheets that appear in new workbooks.

Figure 1.10 Excel Options Window

SAVING WORKBOOKS (SAVE AS)

Once you create a new workbook, you will need to change the file name and choose a location on your computer or network to save that file. It is important to remember where you save this workbook on your computer or network as you will be using this file in the Section 1.2 “Entering, Editing, and Managing Data” to construct the workbook shown in Figure 1.1. The process of saving can be different with different versions of Excel. Please be sure you follow the steps for the version of Excel you are using. The following steps explain how to save a new workbook and assign it a file name.

SAVING WORKBOOKS IN EXCEL 2013

1. If you have not done so already, open a blank workbook in Excel.
2. When saving your workbook for the first time, click the File tab.
3. Click the Save As button in the upper left side of the Backstage view window. This will open the Save As dialog box, as shown in Figure 1.11.
4. Click in the File Name box at the bottom of the Save As dialog box and use the BACKSPACE key to remove the current default name of the workbook.
5. Type the file name: CH1 GMW Sales Data.
6. Click the Desktop button on the left side of the Save As dialog box if you wish to save this file on your desktop. If you want to save this workbook in a different location, such as a USB drive, select your preferred location.
7. Click the Save button on the lower right side of the Save As dialog box.
8. As you continue to work on your workbook, you will want to Save frequently by click either the Save button on the Home ribbon; or by selecting the Save option from the File menu.

Figure 1.11 Save As Dialog Box in Excel 2013

SAVING WORKBOOKS IN EXCEL 2016

1. If you have not done so already, open a blank workbook in Excel.
2. Click the File tab and then the Save As button in the left side of the Backstage view window. This will open the Save As dialog box.
3. Determine a location for saving on your computer by clicking Browse on the left side to open the Save As dialog box.
4. Click in the File Name box near the bottom of the Save As dialog box. Type the new file name: CH1 GMW Sales Data
5. Review the settings in the screen for correctness and click the Save button.

The Status Bar is located below the worksheet tabs on the Excel screen (see Figure 1.13). It displays a variety of information, such as the status of certain keys on your keyboard (e.g., CAPS LOCK), the available views for a workbook, the magnification of the screen, and mathematical functions that can be performed when data are highlighted on a worksheet. You can customize the Status Bar as follows:

1. Place the mouse pointer over any area of the Status Bar and right click to display the “Customize Status Bar” list of options (see Figure 1.13).
2. Select the Caps Lock option from the menu (see Figure 1.13).
3. Press the CAPS LOCK key on your keyboard. You will see the Caps Lock indicator on the lower right side of the Status Bar.
4. Press the CAPS LOCK on your keyboard again. The indicator on the Status Bar goes away.

Figure 1.13 Customizing the Status Bar

EXCEL HELP

The Help feature provides extensive information about the Excel application. Although some of this information may be stored on your computer, the Help window will automatically connect to the Internet, if you have a live connection, to provide you with resources that can answer most of your questions. You can open the Excel Help window by clicking the question mark in the upper right area of the screen or ribbon. With newer versions of Excel, use the query box to enter your question and select from helpful option links or select the question mark from the dropdown list to launch Excel Help windows.

ATTRIBUTION

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

In this section, we will begin the development of the workbook shown in Figure 1.1. The skills covered in this section are typically used in the early stages of developing one or more worksheets in a workbook.

ENTERING DATA

You will begin building the workbook shown in Figure 1.1 by manually entering data into the worksheet. The following steps explain how the column headings in Row 2 are typed into the worksheet:

1. Click cell location A2 on the worksheet.
2. Type the word Month.
3. Press the RIGHT ARROW key. This will enter the word into cell A2 and activate the next cell to the right.
4. Type Unit Sales and press the RIGHT ARROW key.
5. Repeat step 4 for the words Average Price and then again for Sales Dollars.

Figure 1.15 shows how your worksheet should appear after you have typed the column headings into Row 2. Notice that the word Price in cell location C2 is not visible. This is because the column is too narrow to fit the entry you typed. We will examine formatting techniques to correct this problem in the next section.

Figure 1.15 Entering Column Headings into a Worksheet

1. Click cell location B3.
2. Type the number 2670 and press the ENTER key. After you press the ENTER key, cell B4 will be activated. Using the ENTER key is an efficient way to enter data vertically down a column.
3. Enter the following numbers in cells B4 through

B14: 2160, 515, 590, 1030, 2875, 2700, 900, 775, 1180, 1800, and 3560.

4. Click cell location C3.
5. Type the number 9.99 and press the ENTER key.
6. Enter the following numbers in cells C4 through

C14: 12.49, 14.99, 17.49, 14.99, 12.49, 9.99, 19.99, 19.99, 19.99, 17.49, and 14.99.

Integrity Check

Column Headings

It is critical to include column headings that accurately describe the data in each column of a worksheet. In professional environments, you will likely be sharing Excel workbooks with coworkers. Good column headings reduce the chance of someone misinterpreting the data contained in a worksheet, which could lead to costly errors depending on your career.

7. Activate cell location D3.
8. Type the number 26685 and press the ENTER key.
9. Enter the following numbers in cells D4 through

D14: 26937, 7701, 10269, 15405, 35916, 26937, 17958, 15708, 23562, 31416, and 53370.

10. When finished, check that the data you entered matches Figure 1.16.

EDITING DATA

Data that has been entered in a cell can be changed by double clicking the cell location or using the Formula Bar. You may have noticed that as you were typing data into a cell location, the data you typed appeared in the Formula Bar. The Formula Bar can be used for entering data into cells as well as for editing data that already exists in a cell. The following steps provide an example of entering and then editing data that has been entered into a cell location:

1. Click cell A15 in the Sheet1 worksheet.
2. Type the abbreviation Tot and press the ENTER key.
3. Click cell A15.
4. Move the mouse pointer up to the Formula Bar. You will see the pointer turn into a cursor.

Move the cursor to the end of the abbreviation Tot and left click.

5. Type the letters al to complete the word Total.

Why?

Avoid Formatting Symbols When Entering Numbers

When typing numbers into an Excel worksheet, it is best to avoid adding any formatting symbols such as dollar signs and commas. Although Excel allows you to add these symbols while typing numbers, it slows down the process of entering data. It is more efficient to use Excel’s formatting features to add these symbols to numbers after you type them into a worksheet.

Integrity Check

Data Entry

It is very important to proofread your worksheet carefully, especially when you have entered numbers. Transposing numbers when entering data manually into a worksheet is a common error. For example, the number 563 could be transposed to 536. Such errors can seriously compromise the integrity of your workbook.

6. Click the checkmark to the left of the Formula Bar (see Figure 1.17). This will enter the change into the cell.

Figure 1.17 Using the Formula Bar to Edit and Enter Data

7. Double click cell A15.
8. Add a space after the word Total and type the word Sales.
9. Press the ENTER key.

The Auto Fill feature is a valuable tool when manually entering data into a worksheet. This feature has many uses, but it is most beneficial when you are entering data in a defined sequence, such as the numbers 2, 4, 6, 8, and so on, or nonnumeric data such as the days of the week or months of the year. The following steps demonstrate how Auto Fill can be used to enter the months of the year in Column A:

1. Click cell A3 in the Sheet1 worksheet.
2. Type the word January and press the ENTER key.
3. Activate cell A3 again.
4. Move the mouse pointer to the lower right corner of cell A3. You will see a small square in this corner of the cell; this is called the Fill Handle (See Figure 1.18) When the mouse pointer gets close to the Fill Handle, the white block plus sign will turn into a black plus sign.

Figure 1.18 Fill Handle

Left click and drag the Fill Handle to cell A14. Notice that the Auto Fill tip box indicates what month will be placed into each cell (see Figure 1.19). Release the left mouse button when the tip box reads “December.”

Figure 1.19 Using Auto Fill to Enter the Months of the Year

Once you release the left mouse button, all twelve months of the year should appear in the cell range A3:A14, as shown in Figure 1.20. You will also see the Auto Fill Options button. By clicking this button, you have several options for inserting data into a group of cells.

Figure 1.20 Auto Fill Options Button

1. Click the Auto Fill Options button.
2. Click the Copy Cells option. This will change the months in the range A4:A14 to January.
3. Click the Auto Fill Options button again.
4. Click the Fill Months option to return the months of the year to the cell range A4:A14. The Fill Series option will provide the same result.

DELETING DATA AND THE UNDO COMMAND

There are several methods for removing data from a worksheet, a few of which are demonstrated here. With each method, you use the Undo command. This is a helpful command in the event you mistakenly remove data from your worksheet. The following steps demonstrate how you can delete data from a cell or range of cells:

1. Click cell C2 by placing the mouse pointer over the cell and clicking the left mouse button.
2. Press the DELETE key on your keyboard. This removes the contents of the cell.
3. Highlight the range C3:C14 by placing the mouse pointer over cell C3. Then left click and drag the mouse pointer down to cell C14.
4. Place the mouse pointer over the Fill Handle. You will see the white block plus sign change to a black plus sign.
5. Click and drag the mouse pointer up to cell C3 (see Figure 1.21). Release the mouse button. The contents in the range C3:C14 will be removed.

Figure 1.21 Using Auto Fill to Delete Contents of Cell

1. Click the Undo button in the Quick Access Toolbar (see Figure 1.2). This should replace the data in the range C3:C14.
2. Click the Undo button again. This should replace the data in cell C2.

•   Highlight the range C2:C14 by placing the mouse pointer over cell C2. Then left click and drag the mouse pointer down to cell C14.
•   Click the Clear button in the Home tab of the Ribbon, which is next to the Cells group of commands (see Figure 1.22). This opens a drop-down menu that contains several options for removing or clearing data from a cell. Notice that you also have options for clearing just the formats in a cell or the hyperlinks in a cell.
•   Click the Clear All option. This removes the data in the cell range.
•   Click the Undo button. This replaces the data in the range C2:C14.

Figure 1.22 Clear Command Drop-Down Menu

ADJUSTING COLUMNS AND ROWS

There are a few entries in the worksheet that appear cut off. For example, the last letter of the word September cannot be seen in cell A11. This is because the column is too narrow for this word. The columns and rows on an Excel worksheet can be adjusted to accommodate the data that is being entered into a cell. The following steps explain how to adjust the column widths and row heights in a worksheet:

1. Bring the mouse pointer between Column A and Column B in the Sheet1 worksheet, as shown in Figure 1.23. You will see the white block plus sign turn into double arrows.
2. Click and drag the column to the right so the entire word September in cell A11 can be seen. As you drag the column, you will see the column width tip box. This box displays the number of characters that will fit into the column using the Calibri 11-point font which is the default setting for font/size.
3. Release the left mouse button.

Figure 1.23 Adjusting Column Widths

You may find that using the click-and-drag method is inefficient if you need to set a specific character width for one or more columns. Steps 1 through 6 illustrate a second method for adjusting column widths when using a specific number of characters:

1. Click any cell location in Column A by moving the mouse pointer over a cell location and clicking the left mouse button. You can highlight cell locations in multiple columns if you are setting the same character width for more than one column.
2. In the Home tab of the Ribbon, left click the Format button in the Cells group.
3. Click the Column Width option from the drop-down menu. This will open the Column Width dialog box.
4. Type the number 13 and click the OK button on the Column Width dialog box. This will set Column A to this character width (see Figure 1.24).
5. Once again bring the mouse pointer between Column A and Column B so that the double arrow pointer displays and then double-click to activate AutoFit. This features adjusts the column width based on the longest entry in the column.
6. Use the Column Width dialog box (step 6 above) to reset the width to 13.

Steps 1 through 4 demonstrate how to adjust row height, which is similar to adjusting column width:

1. Click cell A15 by placing the mouse pointer over the cell and clicking the left mouse button.
2. In the Home tab of the Ribbon, left click the Format button in the Cells group.
3. Click the Row Height option from the drop-down menu. This will open the Row Height dialog box.
4. Type the number 24 and click the OK button on the Row Height dialog box. This will set Row 15 to a height of 24 points. A point is equivalent to approximately 1/72 of an inch. This adjustment in row height was made to create space between the totals for this worksheet and the rest of the data.

Figure 1.25 shows the appearance of the worksheet after Column A and Row 15 are adjusted.

HIDING COLUMNS AND ROWS

In addition to adjusting the columns and rows on a worksheet, you can also hide columns and rows. This is a useful technique for enhancing the visual appearance of a worksheet that contains data that is not necessary to display. These features will be demonstrated using the GMW Sales Data workbook. However, there is no need to have hidden columns or rows for this worksheet. The use of these skills here will be for demonstration purposes only.

1. Click cell C1 in the Sheet1 worksheet by placing the mouse pointer over the cell location and clicking the left mouse button.
2. Click the Format button in the Home tab of the Ribbon.
3. Place the mouse pointer over the Hide & Unhide option in the drop-down menu. This will open a submenu of options.
4. Click the Hide Columns option in the submenu of options (see Figure 1.26). This will hide Column C.

Figure 1.26 Hide & Unhide Submenu

Figure 1.27 shows the workbook with Column C hidden in the Sheet1 worksheet. You can tell a column is hidden by the missing letter C.

Figure 1.27 Hidden Column

To unhide a column, follow these steps:

1. Highlight the range B1:D1 by activating cell B1 and clicking and dragging over to cell D1.
2. Click the Format button in the Home tab of the Ribbon.
3. Place the mouse pointer over the Hide & Unhide option in the drop-down menu.
4. Click the Unhide Columns option in the submenu of options. Column C will now be visible on the worksheet.

The following steps demonstrate how to hide rows, which is similar to hiding columns:

1. Click cell A3 in the Sheet1 worksheet by placing the mouse pointer over the cell location and clicking the left mouse button.
2. Click the Format button in the Home tab of the Ribbon.
3. Place the mouse pointer over the Hide & Unhide option in the drop-down menu. This will open a submenu of options.
4. Click the Hide Rows option in the submenu of options. This will hide Row 3.

To unhide a row, follow these steps:

1. Highlight the range A2:A4 by activating cell A2 and clicking and dragging over to cell A4.
2. Click the Format button in the Home tab of the Ribbon.
3. Place the mouse pointer over the Hide & Unhide option in the drop-down menu.
4. Click the Unhide Rows option in the submenu of options. Row 3 will now be visible on the worksheet.