Chapter 1 Fundamental Skills

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 "Example of an Excel Worksheet with Embedded Chart" 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. Notice that the embedded chart makes it very easy to see which months have the highest unit sales.

Figure 1.1 Example of an Excel Worksheet with Embedded Chart

Starting Excel

The following steps will guide you in starting the Excel application. Note that these steps along with Figure 1.2 "Start Menu" relate to the Windows 7 operating system, which is very similar to the Windows Vista operating system.

1. Click the Start button on the lower left corner of your computer screen.
2. Click the All Programs arrow at the bottom left of the Start menu.
3. Click the Microsoft Office folder on the Start menu. This will open the list of Microsoft Office applications.
4. Click the Microsoft Excel 2010 option. This will start the Excel application.

Figure 1.2 Start Menu

The Excel Workbook

Once Excel is started, a blank workbook will open on your screen. A workbookAn Excel file that contains one or more worksheets. is an Excel file that contains one or more worksheetsMay also be referred to as a spreadsheet and contains rectangles called cells for entering numeric and nonnumeric data. (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.3 "Blank Workbook" shows a blank workbook after starting Excel.

Figure 1.3 Blank Workbook

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

Figure 1.4 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 cellA specific location on a worksheet where data are entered and stored. 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.4 "Restored Worksheet" is A1. This would be referred to as cell locationA column letter followed by a row number used to identify specific cells on a worksheet. A1 or cell referenceWhen cell locations are used in formulas, Excel will reference the data that is entered into the cell. The cell reference is the cell location address. 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 in orange, as shown in Figure 1.5 "Activating a Cell Location".

   Figure 1.5 Activating a Cell Location

   
3. Move the mouse pointer to cell A1.
4. Click and hold the left mouse button and drag the mouse pointer back to cell D5.

5. Release the left mouse button. You should see several cells highlighted, as shown in Figure 1.6 "Highlighting a Range of Cells". This is referred to as a cell rangeAny group of contiguous cell locations; a cell range is noted as two cell locations separated by a colon. 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.6 Highlighting a Range of Cells

   
6. Click the Sheet3 worksheet tab at the bottom of the worksheet. This is how you open a worksheet within a workbook.
7. Click the Sheet1 worksheet tab at the bottom of the worksheet to return to the worksheet shown in Figure 1.6 "Highlighting a Range of Cells".

The Excel Ribbon

Excel’s features and commands are found in the RibbonThe 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., 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.7 "Ribbon for Excel" 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.7 Ribbon for Excel

The Ribbon shown in Figure 1.7 "Ribbon for Excel" 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.7 "Ribbon for Excel". 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 ToolbarLocated at the upper-left side of the Excel screen above the Ribbon, this toolbar provides access to the most frequently used commands, such as Save and Undo. is found at the upper left side of the Excel screen above the Ribbon, as shown in Figure 1.3 "Blank Workbook". 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.8 "Customizing the Quick Access Toolbar". 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.8 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.9 "Right-Click Menu" shows an example of the commands available in the right-click menu.

Figure 1.9 Right-Click Menu

The File Tab

If you have used Office 2007, you may have noticed that the Office button has disappeared in the 2010 version. It has been replaced with the File tab on the far left side of the Ribbon. The File tab is also known as the Backstage viewThis view, which is opened through the File tab on the Ribbon, contains a variety of features and commands related to the workbook that is currently open. 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.10 "File Tab or Backstage View of a Workbook" shows the options available in the File tab or Backstage view. To leave the Backstage view and return to the worksheet, click any tab on the Ribbon or click the image of the worksheet on the right side of the window. You must click the Info button (highlighted in green in Figure 1.10 "File Tab or Backstage View of a Workbook") to see the image of your worksheet on the right side of the window.

Figure 1.10 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.11 "Excel Options Window" 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.11 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 it. The following steps explain how to save a new workbook and assign it a file name. 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 "Example of an Excel Worksheet with Embedded Chart".

1. If you have not done so already, start Excel. A blank workbook should appear on your screen. Check to make sure the workbook is maximized (see Figure 1.4 "Restored Worksheet").
2. Click the File tab.
3. Click the Save As button in the upper left side of the Backstage view window, as shown in Figure 1.10 "File Tab or Backstage View of a Workbook". This will open the Save As dialog box.
4. Click in the File Name box at the bottom of the Save As dialog box.
5. Use the BACKSPACE key to remove the current file name of the workbook.
6. Type the file name: Excel Objective 1.0.
7. 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 on your computer or network, double click the Computer option, as shown in Figure 1.12 "Save As Dialog Box", and select your preferred location.
8. Click the Save button on the lower right side of the Save As dialog box.

Figure 1.12 Save As Dialog Box

Excel 97-2003 File Type

If you are working with someone who is using a version of Microsoft Office that is older than Office 2007, you will have to save your workbook under the Excel 97-2003 Workbook format. A person who is running Office 2003 will not be able to open workbooks that are saved under the Office 2010 or Office 2007 file types. You can save a workbook as an Excel 97-2003 file type by clicking the down arrow next to the “Save as type” box in the Save As dialog box (see Figure 1.12 "Save As Dialog Box").

You can also change the file type of your workbook by using the File tab on the Ribbon. The following steps explain this method:

1. Open the workbook you wish to convert to the Excel 97-2003 file type.
2. Click the File tab on the Ribbon.
3. Click the Save & Send button on the left side of the Backstage view.
4. Click the Change File Type button.
5. Double click the Excel 97-2003 Workbook option on the right side of the Backstage view. This will open up the Save As dialog box and set the file type box to Excel 97-2003 Workbook (see Figure 1.13 "Changing the File Type of a Workbook").
6. Check to make sure the Save As dialog box is set to the location where you want to save your workbook.
7. Click the Save button at the bottom of the Save As dialog box.

Figure 1.13 Changing the File Type of a Workbook

When you convert an existing workbook created in Office 2010 to the Excel 97-2003 file type, you may not notice any changes on the workbook itself. If you are using a feature or format that is not compatible with Office 97-2003, a warning will appear upon saving the file. You may want to remove these features and formats before sending the workbook to a person who is running an older version of Office. When you open a file that is saved in the Excel 97-2003 format, you will see the Compatibility Mode indicator next to the workbook name, as shown in Figure 1.14 "Workbook That Has Been Saved in Excel 97-2003 Format".

Figure 1.14 Workbook That Has Been Saved in Excel 97-2003 Format

The Status Bar

The Status BarLocated below the worksheet tabs on the Excel screen, 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, or mathematical functions that can be performed when data are highlighted on a worksheet. is located below the worksheet tabs on the Excel screen (see Figure 1.15 "Customizing the Status Bar"). 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 (see Figure 1.15 "Customizing the Status Bar").
2. Select the Caps Lock option from the menu (see Figure 1.15 "Customizing the Status Bar").
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 key again. The indicator on the Status Bar goes away.

Figure 1.15 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 corner of the screen (see Figure 1.3 "Blank Workbook"). Here you can search for specific topics or type a question in the upper-left side of the window, as shown in Figure 1.16 "Excel Help Window".

Figure 1.16 Excel Help Window

Entering Data

We will begin building the workbook shown in Figure 1.1 "Example of an Excel Worksheet with Embedded Chart" by manually entering data into the worksheet. There are other ways in which you can bring data into an Excel worksheet, such as importing data from a website or a Microsoft Access database. However, we will demonstrate these other methods later. The following steps explain how the column headings in Row 2 are typed into the worksheet:

1. Activate 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 Sales Dollars.

   Figure 1.17 "Entering Column Headings into a Worksheet" 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.17 Entering Column Headings into a Worksheet

   

   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.

6. Activate cell location B3.
7. 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.

8. Repeat step 7 by entering the following numbers in cells B4 through B14: 2160, 515, 590, 1030, 2875, 2700, 900, 775, 1180, 1800, and 3560.

   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.

9. Activate cell location C3.
10. Type the number 9.99 and press the ENTER key.
11. Repeat step 10 by entering 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.
12. Activate cell location D3.
13. Type the number 26685 and press the ENTER key.
14. Repeat step 13 by entering the following numbers in cells D4 through D14: 26937, 7701, 10269, 15405, 35916, 26937, 17958, 15708, 23562, 31416, and 53370.

Figure 1.18 "Completed Data Entry for Columns B, C, and D" shows how your worksheet should appear after entering the data. Check your numbers carefully to make sure they are accurately entered into the worksheet.

Figure 1.18 Completed Data Entry for Columns B, C, and D

Editing Data

Data that has been entered in a cell can be changed by double clicking the cell location or using the Formula BarThe area just above the column letters on a worksheet. It can be used for entering data into cells as well as for editing data that already exists in cells.. 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. Activate 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.

6. Click the checkmark to the left of the Formula Bar (see Figure 1.19 "Using the Formula Bar to Edit and Enter Data"). This will enter the change into the cell.

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

Auto Fill

The Auto FillAn Excel feature used to complete data in either a quantitative or qualitative sequence. It can also be used to copy and paste data in a worksheet. 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. Activate 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 HandleA small square in lower right corner of an activated cell. When the mouse pointer gets close to the Fill Handle, the white block plus sign turns into a black plus sign. (see Figure 1.20 "Fill Handle"). When the mouse pointer gets close to the Fill Handle, the white block plus sign will turn into a black plus sign.

   Figure 1.20 Fill Handle

   

5. 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.21 "Using Auto Fill to Enter the Months of the Year"). Release the left mouse button when the tip box reads “December.”

   Figure 1.21 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.22 "Auto Fill Options Button". 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.22 Auto Fill Options Button

   
6. Left click the Auto Fill Options button.
7. Left click the Copy Cells option. This will change the months in the range A4:A14 to January.
8. Left click the Auto Fill Options button again.
9. Left 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. Activate 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. Left click and drag the mouse pointer up to cell C3 (see Figure 1.23 "Using Auto Fill to Delete Contents of Cell"). Release the mouse button. The contents in the range C3:C14 will be removed.

   Figure 1.23 Using Auto Fill to Delete Contents of Cell

   
6. Click the Undo button in the Quick Access Toolbar (see Figure 1.3 "Blank Workbook"). This should replace the data in the range C3:C14.

7. Click the Undo button again. This should replace the data in cell C2.

   Mouseless Command

   Undo Command

   • Hold down the CTRL key while pressing the letter Z on your keyboard.
8. 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.
9. Click the Clear button in the Home tab of the Ribbon, which is next to the Cells group of commands (see Figure 1.24 "Clear Command Drop-Down Menu"). 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.
10. Click the Clear All option. This removes the data in the cell range.
11. Click the Undo button. This replaces the data in the range C2:C14.

Figure 1.24 Clear Command Drop-Down Menu

Adjusting Columns and Rows

In Figure 1.22 "Auto Fill Options Button", there are a few entries 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.25 "Adjusting Column Widths". You will see the white block plus sign turn into double arrows.
2. Left 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 boxA box that appears when the width of a column is being adjusted using the click-and-drag method. It displays the number of characters that will fit into the column using Calibri 11-point font.. This box displays the number of characters that will fit into the column using the Calibri 11-point font.

3. Release the left mouse button.

   Figure 1.25 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 4 through 7 illustrate a second method for adjusting column widths when using a specific number of characters:

4. Activate 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.
5. In the Home tab of the Ribbon, left click the Format button in the Cells group (see Figure 1.26 "Cells Group in the Home Tab").
6. Click the Column Width option from the drop-down menu (see Figure 1.27 "Format Drop-Down Menu"). This will open the Column Width dialog box.

7. 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.28 "Column Width Dialog Box").

   Figure 1.26 Cells Group in the Home Tab

   

   Figure 1.27 Format Drop-Down Menu

   

   Figure 1.28 Column Width Dialog Box

   

   Mouseless Command

   Column Width

   • Press the ALT key on your keyboard, then press the letters H, O, and W one at a time.

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

8. Activate cell A15 by placing the mouse pointer over the cell and clicking the left mouse button.
9. In the Home tab of the Ribbon, left click the Format button in the Cells group (see Figure 1.26 "Cells Group in the Home Tab").
10. Click the Row Height option from the drop-down menu (see Figure 1.27 "Format Drop-Down Menu"). This will open the Row Height dialog box.
11. 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 pointMetric used when measuring the height of a row; equivalent to approximately 1/72 of an inch. 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.29 "Excel Objective 1.0 with Column A and Row 15 Adjusted" shows the appearance of the worksheet after Column A and Row 15 are adjusted.

Figure 1.29 Excel Objective 1.0 with Column A and Row 15 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 Excel Objective 1.0 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. Activate 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 (see Figure 1.27 "Format Drop-Down Menu"). This will open a submenu of options.

4. Click the Hide Columns option in the submenu of options (see Figure 1.30 "Hide & Unhide Submenu"). This will hide Column C.

   Figure 1.30 Hide & Unhide Submenu

   

   Mouseless Command

   Hiding Columns

   • Hold down the CTRL key while pressing the number 0 on your keyboard.

   Figure 1.31 "Hidden Column" 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.31 Hidden Column

   

   To unhide a column, follow these steps:

5. Highlight the range B1:D1 by activating cell B1 and clicking and dragging over to cell D1.
6. Click the Format button in the Home tab of the Ribbon.
7. Place the mouse pointer over the Hide & Unhide option in the drop-down menu (see Figure 1.27 "Format Drop-Down Menu").

8. Click the Unhide Columns option in the submenu of options (see Figure 1.30 "Hide & Unhide Submenu"). Column C will now be visible on the worksheet.

   Mouseless Command

   Unhiding Columns

   • Highlight cells on either side of the hidden column(s), then hold down the CTRL key and the SHIFT key while pressing the close parenthesis key ()) on your keyboard.

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

9. Activate cell A3 in the Sheet1 worksheet by placing the mouse pointer over the cell location and clicking the left mouse button.
10. Click the Format button in the Home tab of the Ribbon.
11. Place the mouse pointer over the Hide & Unhide option in the drop-down menu (see Figure 1.27 "Format Drop-Down Menu"). This will open a submenu of options.

12. Click the Hide Rows option in the submenu of options (see Figure 1.30 "Hide & Unhide Submenu"). This will hide Row 3.

    Mouseless Command

    Hiding Rows

    • Hold down the CTRL key while pressing the number 9 key on your keyboard.

    To unhide a row, follow these steps:

13. Highlight the range A2:A4 by activating cell A2 and clicking and dragging over to cell A4.
14. Click the Format button in the Home tab of the Ribbon.
15. Place the mouse pointer over the Hide & Unhide option in the drop-down menu (see Figure 1.27 "Format Drop-Down Menu").
16. Click the Unhide Rows option in the submenu of options (see Figure 1.30 "Hide & Unhide Submenu"). Row 3 will now be visible on the worksheet.

Inserting Columns and Rows

Using Excel workbooks that have been created by others is a very efficient way to work because it eliminates the need to create data worksheets from scratch. However, you may find that to accomplish your goals, you need to add additional columns or rows of data. In this case, you can insert blank columns or rows into a worksheet. The following steps demonstrate how to do this:

1. Activate cell C1 in the Sheet1 worksheet by placing the mouse pointer over the cell location and clicking the left mouse button.

2. Click the down arrow on the Insert button in the Home tab of the Ribbon (see Figure 1.32 "Insert Button (Down Arrow)").

   Figure 1.32 Insert Button (Down Arrow)

   

3. Click the Insert Sheet Columns option from the drop-down menu (see Figure 1.33 "Insert Drop-Down Menu"). A blank column will be inserted to the left of Column C. The contents that were previously in Column C now appear in Column D. Note that columns are always inserted to the left of the activated cell.

   Mouseless Command

   Inserting Columns

   • Press the ALT key and then the letters H, I, and C one at a time. A column will be inserted to the left of the activated cell.

   Figure 1.33 Insert Drop-Down Menu

   
4. Activate cell A3 in the Sheet1 worksheet by placing the mouse pointer over the cell location and clicking the left mouse button.
5. Click the down arrow on the Insert button in the Home tab of the Ribbon (see Figure 1.32 "Insert Button (Down Arrow)").
6. Click the Insert Sheet Rows option from the drop-down menu (see Figure 1.33 "Insert Drop-Down Menu"). A blank row will be inserted above Row 3. The contents that were previously in Row 3 now appear in Row 4. Note that rows are always inserted above the activated cell.

Moving Data

Once data are entered into a worksheet, you have the ability to move it to different locations. The following steps demonstrate how to move data to different locations on a worksheet:

1. Highlight the range D2:D15 by activating cell D2 and clicking and dragging down to cell D15.

2. Bring the mouse pointer to the left edge of cell D2. You will see the white block plus sign change to cross arrows (see Figure 1.34 "Moving Data"). This indicates that you can left click and drag the data to a new location.

   Figure 1.34 Moving Data

   
3. Left click and drag the mouse pointer to cell C2.
4. Release the left mouse button. The data now appears in Column C.
5. Click the Undo button in the Quick Access Toolbar. This moves the data back to Column D.

Deleting Columns and Rows

You may need to delete entire columns or rows of data from a worksheet. This need may arise if you need to remove either blank columns or rows from a worksheet or columns and rows that contain data. The methods for removing cell contents were covered earlier and can be used to delete unwanted data. However, if you do not want a blank row or column in your workbook, you can delete it using the following steps:

1. Activate cell A3 by placing the mouse pointer over the cell location and clicking the left mouse button.
2. Click the down arrow on the Delete button in the Cells group in the Home tab of the Ribbon.

3. Click the Delete Sheet Rows option from the drop-down menu (see Figure 1.35 "Delete Drop-Down Menu"). This removes Row 3 and shifts all the data (below Row 2) in the worksheet up one row.

   Mouseless Command

   Deleting Rows

   • Press the ALT key and then the letters H, D, and R one at a time. The row with the activated cell will be deleted.

   Figure 1.35 Delete Drop-Down Menu

   
4. Activate cell C1 by placing the mouse pointer over the cell location and clicking the left mouse button.
5. Click the down arrow on the Delete button in the Cells group in the Home tab of the Ribbon.
6. Click the Delete Sheet Columns option from the drop-down menu (see Figure 1.35 "Delete Drop-Down Menu"). This removes Column C and shifts all the data in the worksheet (to the right of Column B) over one column to the left.

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

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)

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.

Figure 1.41 Merge Cell Drop-Down Menu

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

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

Borders (Adding Lines to a Worksheet)

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

AutoSum

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

Inserting a Column Chart

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

Cut, Copy, and Paste

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)

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.

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

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.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").

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

Page Setup

Before you can properly print the worksheets in a workbook, you must establish appropriate settings. The following steps explain several of the commands in the Page Layout tab of the Ribbon used to prepare a worksheet for printing:

1. Open the Unit Sales Rank worksheet by left clicking on the worksheet tab.
2. Click the Page Layout tab of the Ribbon.
3. Click the Margins button in the Page Setup group of commands. This will open a drop-down list of options for setting the margins of your printed document.
4. Click the Wide option from the Margins drop-down list.
5. Open the Sales by Month worksheet by left clicking on the worksheet tab.
6. Click the Page Layout tab of the Ribbon (see Figure 1.62 "Page Layout Commands for Printing").
7. Click the Margins button in the Page Setup group of commands.
8. Click the Narrow option from the Margins drop-down list.
9. Click the Orientation button in the Page Setup group of commands.
10. Click the Landscape option.
11. Click the down arrow to the right of the Width button in the Scale to Fit group of commands.
12. Click the 1 Page option from the drop-down list.
13. Click the down arrow to the right of the Height button in the Scale to Fit group of commands.
14. Click the 1 Page option from the drop-down list. This step along with step 12 will automatically reduce the worksheet so that it fits on one piece of paper. It is very common for professionals to create worksheets that fit within the width of the paper being used. However, for long data sets, you may need to set the height to more than one page. Table 1.2 "Printing Resources: Purpose and Use for Page Setup Commands" provides a list of commands found in the Page Layout tab of the Ribbon

Figure 1.62 Page Layout Commands for Printing

Headers and Footers

When printing worksheets from Excel, it is common to add headersSpace at the top of a printed worksheet that contains information such as the date, page number, file name, company name, and so on. and footersSpace at the bottom of a printed worksheet that contains information such as the date, page number, file name, company name, and so on. to the printed document. Information in the header or footer could include the date, page number, file name, company name, and so on. The following steps explain how to add headers and footers to the Excel Objective 1.0 workbook:

1. Open the Unit Sales Rank worksheet by left clicking on the worksheet tab.
2. Click the Insert tab of the Ribbon.

3. Click the Header & Footer button in the Text group of commands. You will see the Design tab added to the Ribbon; this is used for creating the headers and footers for the printed worksheet. Also, this will convert the view of the worksheet from Normal to Page Layout (see Figure 1.63 "Design Tab for Creating Headers and Footers").

   Figure 1.63 Design Tab for Creating Headers and Footers

   
4. Type your name in the center section of the Header.
5. Place the mouse pointer over the left section of the Header and left click (see Figure 1.63 "Design Tab for Creating Headers and Footers").
6. Click the Current Date button in the Header & Footer Elements group of commands in the Design tab of the Ribbon.
7. Click the Go to Footer button in the Navigation group of commands in the Design tab of the Ribbon.
8. Place the mouse pointer over the far right section of the footer and left click.
9. Click the Page Number button in the Header & Footer Elements group of commands in the Design tab of the Ribbon.
10. Click any cell location outside the header or footer area. The Design tab for creating headers and footers will disappear.

11. Click the Normal view button in the lower right side of the Status Bar (see Figure 1.64 "Worksheet in Page Layout View").

    Figure 1.64 Worksheet in Page Layout View

    
12. Open the Sales by Month worksheet by left clicking the worksheet tab.
13. Repeat steps 2 through 11 to create the same header and footer for this worksheet.

Printing Worksheets and Workbooks

Once you have established the print settings for the worksheets in a workbook and have added headers and footers, you are ready to print your worksheets. The following steps explain how to print the worksheets in the Excel Objective 1.0 workbook:

1. Open the Unit Sales Rank worksheet by left clicking on the worksheet tab.
2. Click the File tab on the Ribbon.

3. Click the Print option on the left side of the Backstage view (see Figure 1.65 "Print Preview"). On the right side of the Backstage view, you will be able to see a preview of your printed worksheet.

   Figure 1.65 Print Preview

   
4. Click the Print Active Sheets button in the Print section of the Backstage view (see Figure 1.65 "Print Preview").
5. Click the Print Entire Workbook option from the drop-down list. This will print all worksheets in a workbook when the Print button is clicked.
6. Click the Next Page arrow at the bottom of the preview window.
7. Click the Print button.
8. Click the Home tab of the Ribbon.
9. Save and close the Excel Objective 1.0 workbook.