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. Show
1.1 An Overview of Microsoft® Excel®Learning Objectives
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 ExcelFollow-along file: Not needed for this skill Starting ExcelFollow-along file:
Not needed for this skill
The Excel WorkbookFollow-along file: Not needed for this skill Your workbook should already be maximized (or shown at full size) once Excel is started, as shown in Figure 3. However, if your screen looks like Figure 4 after starting Excel, you should click the Maximize button, as shown in the figure. Navigating WorksheetsFollow-along file: Not needed for this skill
Mouseless Commands: Basic Worksheet Navigation
The Excel RibbonFollow-along file: Not needed for this skill
The Ribbon shown in Figure 7 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 7. 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. Mouseless Commands: Minimizing or Maximizing the Ribbon
Quick Access Toolbar and Right-Click MenuFollow-along file: Not needed for this skill In addition to the Ribbon and Quick Access Toolbar, you can also access commands by right clicking anywhere on the worksheet. Figure 9 shows an example of the commands available in the right-click menu. The File TabFollow-along file: Not needed for this skill 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 11 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. Saving Workbooks (Save As)Follow-along file: Not needed for this skill
Mouseless Commands: Save As
Excel 97-2003 File TypeFollow-along file: Open a blank workbook.
Why? No Office 2007 File Type 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 14. The Status BarFollow-along file: Continue with a blank workbook or open a new one.
Excel HelpFollow-along file: Continue
with a blank workbook or open a new one. Mouseless Command: Excel Help
Key Takeaways
Exercises
1.2 Entering, Editing, and Managing DataLearning Objectives
In this section, we will begin the development of the workbook shown in Figure 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 Follow-along file: Excel Objective 1.0 (This is a blank workbook that was named in the previous section. If you skipped the
previous section, open a new workbook and save it with the file name “Excel Objective 1.0.”)
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.
Why? Avoid Formatting Symbols When Entering Numbers
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. Figure 18 shows how your worksheet should appear after entering the data. Check your numbers carefully to make sure they are accurately entered into the worksheet. Editing DataFollow-along file: Excel Objective 1.0 (Use file Excel Objective 1.01 if you are starting with this skill.)
Mouseless Command: Editing Data in a Cell Activate the cell that is to be edited and press the F2 key on your keyboard. Auto FillFollow-along file: Excel Objective 1.0 (Use file Excel Objective 1.02 if you are starting with this skill.)
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 22. 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.
Deleting Data and the Undo CommandFollow-along file: Excel Objective 1.0 (Use file Excel Objective 1.03 if you are starting with this skill.)
Mouseless Command: Undo Command Hold down the CTRL key while pressing the letter Z on your keyboard.
Adjusting Columns and RowsFollow-along
file: Excel Objective 1.0 (Use file Excel Objective 1.03 if you are starting with this skill.)
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:
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:
Mouseless Command: Row Height Press the ALT key on your keyboard, then press the letters H, O, and H one at a time. Figure 29 shows the appearance of the worksheet after Column A and Row 15 are adjusted. Hiding Columns and RowsFollow-along file: Excel Objective 1.0 (Use file Excel Objective 1.04 if you are starting with this skill.)
Mouseless Command: Hiding Columns Hold down the CTRL key while pressing the number 0 on your keyboard. Figure 31 shows the workbook with Column C hidden in the Sheet1 worksheet. You can tell a column is hidden by the missing letter C. To unhide a column, follow these steps:
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:
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:
Mouseless Command: Unhiding Rows Highlight cells above and below the hidden row(s), then hold down the
CTRL key and the SHIFT key while pressing the open parenthesis key (() on your keyboard. Integrity Check: Hidden Rows and Columns In most careers, it is common for professionals to use Excel workbooks that have been designed by a coworker. Before you use a workbook developed by someone else, always check for hidden rows and columns. You can quickly see whether a row or column is hidden if a row number or column letter is missing. Inserting Columns and RowsFollow-along file: Excel Objective 1.0 (Use file Excel Objective 1.04 if you are starting with this skill.)
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.
Mouseless Command: Inserting Rows Press the ALT key and then the letters H, I, and R one at a time. A row will be inserted above the activated cell. Moving DataFollow-along file: Excel
Objective 1.0 (Use file Excel Objective 1.05 if you skipped the previous skill and are starting with this skill.)
Integrity Check: Moving Data Before moving data on a worksheet, make sure you identify all the components that belong with the series you are moving. For example, if you are moving a column of data, make sure the column heading is included. Also, make sure all values are highlighted in the column before moving it. Deleting Columns and RowsFollow-along
file: Excel Objective 1.0 (Use file Excel Objective 1.05 if you are starting with this skill.)
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.
Mouseless Command: Deleting Columns Press the ALT key and then the letters H, D, and C one at a time. The column with the activated cell will be deleted.
Key Takeaways
Exercises
1.3 Formatting and Data AnalysisLearning Objectives
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 CellsFollow-along file: Excel Objective 1.0 (Use file Excel Objective 1.04 if you are starting with this skill.)
Mouseless Command: Bold Format Hold the CTRL key while
pressing the letter B on your keyboard.
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
Why? Pound Signs (####) Appear in Columns Figure 39 shows how the Sheet1 worksheet should appear after the formatting techniques are 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.)
Mouseless Command: Wrap Text Press the ALT key and then the letters H and W one at a time. Why? Wrap Text
Mouseless Commands: Merge Commands
Why? Merge & Center Figure 42 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. Entering Multiple Lines of TextFollow-along file: Excel Objective 1.0 (Use file Excel Objective 1.07 if you are starting with this skill.)
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.)
AutoSumFollow-along file: Excel Objective 1.0 (Use file Excel Objective 1.09 if you are starting with this skill.)
Inserting a Column ChartFollow-along file: Excel Objective 1.0 (Use file Excel Objective 1.10 if you are starting with this skill.)
Figure 50 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:
Why? There Are No Sizing Handles on a Chart Figure 52 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. Why? Use the ALT Key When Resizing a Chart As shown in Figure 50, 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:
Click the Blue, Accent 1, Inner Shadow
option (see Figure 54). 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. Cut, Copy, and PasteFollow-along file: Excel Objective 1.0 (Use file Excel Objective 1.11 if you are starting with this skill.)
Mouseless Command: Copy Press the CTRL key and then the letter C key on your keyboard.
Mouseless Command: Paste Press the CTRL key and then the letter V key on your
keyboard.
Mouseless Command: Cut Press the CTRL key and then the letter X key on your keyboard.
Sorting Data (One Level)Follow-along file: Excel Objective 1.0 (Use file Excel Objective 1.12 if you are starting with this skill.)
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 59 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. Moving, Renaming, Inserting, and Deleting WorksheetsFollow-along file: Excel Objective 1.0 (Use file Excel Objective 1.13 if you are starting with this skill.)
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 61 shows the final appearance of the Excel Objective 1.0 workbook after the worksheet tabs have been renamed and moved. Key Takeaways
Exercises
1.4 PrintingLearning Objectives
Once you have completed a workbook, it is good practice to select the appropriate settings for printing. These settings are in the Page Layout tab of the Ribbon and discussed in this section of the chapter. Page SetupFollow-along file: Excel Objective 1.0 (Use file Excel Objective 1.14 if you are starting with this skill.)
Why? Use Print Settings Headers and FootersFollow-along file: Excel Objective 1.0 (Use file Excel Objective 1.15 if you are starting with this skill.)
Printing Worksheets and WorkbooksFollow-along file: Excel Objective 1.0 (Use file Excel Objective 1.16 if you are starting with this skill.)
Key Takeaways
Exercises
1.5 Chapter Assignments and TestsTo assess your understanding of the material covered in the chapter, please complete the following assignments. Careers in Practice (Skills Review) Basic Monthly Budget for Medical Office (Comprehensive Review) Entering, Editing, and Managing Data
Formatting and Basic Charts
Printing
Marketing for Specialty Women’s ApparelDifficulty: Level 2
Integrity CheckDifficulty: Level 3 ScenarioYour coworker provides you with sales data in an Excel workbook, which you intend to use for a sales strategy meeting with your boss. The workbook was attached to an e-mail with the following points stated in the message.
Assignment
Applying Excel Skills The assignment in this section requires that you apply the skills presented in this chapter to achieve the stated objective. Read the assignment first and then open the file and complete the stated requirements. When you complete an assignment, save the file by adding your name in front of the current name of the
workbook.
CHAPTER SKILLS TESTDifficulty: Level 2
Licenses and AttributionsHow can you display the open backstage area in Access?Home | File (Backstage View). After you click the File tab, you see the file ribbon, which is also called the backstage view. ... . If there is an active project and you click on the File tab to open the backstage view, the Project tab is automatically selected.. How can you display the open backstage area Access quizlet?Click file tab to access backstage view.
Where are the print and print preview options located in Access quizlet?Click the Print Preview view button on the status bar. On the Home tab, in the Views group, click the View button, and select Print Preview.
What is the keyboard shortcut to display the field list task pane?You might need to press F6 more than once. If pressing F6 doesn't display the task pane you want, press Alt to move the focus to the ribbon, and then press Ctrl+Tab to move to the task pane. Switch to the next or previous database window.
|