Main menu
This is “Fundamental Skills”, chapter 1 from the book Using Microsoft Excel (v. 1.1). For details on it (including licensing), click here.
For more information on the source of this book, or why it is available for free, please see the project's home page. You can browse or download additional books there.
Has this book helped you? Consider passing it on:
Help Creative Commons
Creative Commons supports free culture from music to education. Their licenses helped make this book available to you.
Help a Public School
DonorsChoose.org helps people like you help teachers fund their classroom projects, from art supplies to books to calculators.
Previous Chapter
Table of Contents
Next Chapter
Chapter 1 Fundamental Skills
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.
1.1 An Overview of Microsoft® Excel®
Learning Objectives
1.Examine the value of using Excel to make decisions.
2.Learn how to start Excel.
3.Become familiar with the Excel workbook.
4.Understand how to navigate worksheets.
5.Examine the Excel Ribbon.
6.Become familiar with the Quick Access Toolbar.
7.Examine the right-
8.Become familiar with the commands in the File tab.
9.Learn how to save workbooks.
10.Save workbooks in the Excel 97-
11.Examine the Status Bar.
12.Become familiar with the features in the Excel Help window.
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
Follow-
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-
Figure 1.1 Example of an Excel Worksheet with Embedded Chart
Starting Excel
Follow-
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
Follow-
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
Follow-
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".
Mouseless Commands
Basic Worksheet Navigation
•Use the arrow keys on your keyboard to activate cells on the worksheet.
•Hold the SHIFT key and press the arrow keys on your keyboard to highlight a range of cells in a worksheet.
•Hold the CTRL key while pressing the PAGE DOWN or PAGE UP keys to open other worksheets in a workbook.
The Excel Ribbon
Follow-
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
Table 1.1 Command Overview for Each Tab of the Ribbon
Tab Name
Description of Commands
File Also known as the Backstage view of the Excel workbook. Contains all commands for opening, closing, saving, and creating new Excel workbooks. Includes print commands, document properties, e-
Home Contains the most frequently used Excel commands. Formatting commands are found in this tab along with commands for cutting, copying, pasting, and for inserting and deleting rows and columns.
Insert Used to insert objects such as charts, pictures, shapes, PivotTables, Internet links, symbols, or text boxes.
Page Layout Contains commands used to prepare a worksheet for printing. Also includes commands used to show and print the gridlines on a worksheet.
Formulas Includes commands for adding mathematical functions to a worksheet. Also contains tools for auditing mathematical formulas.
Data Used when working with external data sources such as Microsoft® Access®, text files, or the Internet. Also contains sorting commands and access to scenario tools.
Review Includes Spelling and Track Changes features. Also contains protection features to password protect worksheets or workbooks.
View Used to adjust the visual appearance of a workbook. Common commands include the Zoom and Page Layout view.
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.
Mouseless Commands
Minimizing or Maximizing the Ribbon
•Hold down the CTRL key and press the F1 key.
•Hold down the CTRL key and press the F1 key again to maximize the Ribbon.
Quick Access Toolbar and Right-
Follow-
The Quick Access ToolbarLocated at the upper-
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-
Figure 1.9 Right-
The File Tab
Follow-
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)
Follow-
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
Mouseless Commands
Save As
•Press the F12 key and use the tab and arrow keys to navigate around the Save As dialog box. Use the ENTER key to make a selection.
•Or press the ALT key on your keyboard. You will see letters and numbers, called Key Tips, appear on the Ribbon. Press the F key on your keyboard for the File tab and then the A key. This will open the Save As dialog box.
Skill Refresher: Saving Workbooks (Save As)
(click to see video)
1.Click the File tab on the Ribbon.
2.Click the Save As option.
3.Select a location on your PC or network.
4.Click in the File name box and type a new file name if needed.
5.Click the down arrow next to the “Save as type” box and select the appropriate file type if needed.
6.Click the Save button.
Excel 97-
Follow-
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-
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-
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-
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
Why?
No Office 2007 File Type
Workbooks that are created in Office 2010 are automatically compatible with Office 2007. A person who is running Office 2007 will be able to open, edit, and save workbooks created in Office 2010.
When you convert an existing workbook created in Office 2010 to the Excel 97-
Figure 1.14 Workbook That Has Been Saved in Excel 97-
The Status Bar
Follow-
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
Follow-
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-
Figure 1.16 Excel Help Window
Mouseless Command
Excel Help
•Press the F1 key on your keyboard.
Key Takeaways
•Excel is a powerful tool for processing data for the purposes of making decisions.
•You can find Excel commands throughout the tabs in the Ribbon.
•You can customize the Quick Access Toolbar by adding commands you frequently use.
•You must save your workbook in the Excel 97-
•Office 2007 can open files created in Office 2010.
•You can add or remove the information that is displayed on the Status Bar.
•The Help window provides you with extensive information about Excel.
Exercises
1.
Which of the following responses best defines the notation A1:B15?
1.The contents in cell A1 are identical to the contents in cell B15.
2.The cells between A1 and B15 are hidden.
3.A cell range or contiguous group of cells that begins with cell A1 and include all cells up to and including cell B15.
4.A cell link that connects cell A1 to B15.
2.
The Spell Check feature is in which tab of the Excel Ribbon?
1.Home
2.Review
3.Data
4.Formulas
3.
Holding down the CTRL key and pressing the F1 key on your keyboard is used to
1.minimize the Ribbon
2.open the Excel Help window
3.save a workbook
4.switch between open workbooks
4.
If you are sending an Excel workbook created in Office 2010 to a person who is running Office 2007, you should do the following:
1.Use the Save As command to save the workbook in the Office 2007 file format.
2.Use the Save As command to save the workbook in the Excel 97-
3.Use the Save As command to save the workbook in the Universal Compatibility format.
4.Nothing. Office 2007 can open files created in Office 2010.
1.2 Entering, Editing, and Managing Data
Learning Objectives
1.Understand how to enter data into a worksheet.
2.Examine how to edit data in a worksheet.
3.Examine how Auto Fill is used when entering data.
4.Understand how to delete data from a worksheet and use the Undo command.
5.Examine how to adjust column widths and row heights in a worksheet.
6.Understand how to hide columns and rows in a worksheet.
7.Examine how to insert columns and rows into a worksheet.
8.Understand how to delete columns and rows from a worksheet.
9.Learn how to move data to different locations in a worksheet.
In this section, we will begin the development of the workbook shown in Figure 1.1 "Example of an Excel Worksheet with Embedded Chart". 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-
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.
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 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
Follow-
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.
Mouseless Command
Editing Data in a Cell
•Activate the cell that is to be edited and press the F2 key on your keyboard.
Auto Fill
Follow-
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
Follow-
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-
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-
Adjusting Columns and Rows
Follow-
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-
3.
Release the left mouse button.
Figure 1.25 Adjusting Column Widths
You may find that using the click-
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-
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-
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-
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.
Mouseless Command
Row Height
•Press the ALT key on your keyboard, then press the letters H, O, and H one at a time.
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
Skill Refresher: Adjusting Columns and Rows
(click to see video)
1.Activate at least one cell in the row or column you are adjusting.
2.Click the Home tab of the Ribbon.
3.Click the Format button in the Cells group.
4.Click either Row Height or Column Width from the drop-
5.Enter the Row Height in points or Column Width in characters in the dialog box.
6.Click the OK button.
Hiding Columns and Rows
Follow-
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-
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-
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-
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-
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.
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.
Skill Refresher: Hiding Columns and Rows
(click to see video)
1.Activate at least one cell in the row(s) or column(s) you are hiding.
2.Click the Home tab of the Ribbon.
3.Click the Format button in the Cells group.
4.Place the mouse pointer over the Hide & Unhide option.
5.Click either the Hide Rows or Hide Columns option.
Skill Refresher: Unhiding Columns and Rows
(click to see video)
1.Highlight the cells above and below the hidden row(s) or to the left and right of the hidden column(s).
2.Click the Home tab of the Ribbon.
3.Click the Format button in the Cells group.
4.Place the mouse pointer over the Hide & Unhide option.
5.Click either the Unhide Rows or Unhide Columns option.
Inserting Columns and Rows
Follow-
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-
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-
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-
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.
Skill Refresher: Inserting Columns and Rows
(click to see video)
1.Activate the cell to the right of the desired blank column or below the desired blank row.
2.Click the Home tab of the Ribbon.
3.Click the down arrow on the Insert button in the Cells group.
4.Click either the Insert Sheet Columns or Insert Sheet Rows option.
Moving Data
Follow-
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.
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 Rows
Follow-
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-
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-
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-
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.
Skill Refresher: Deleting Columns and Rows
(click to see video)
1.Activate any cell in the row or column that is to be deleted.
2.Click the Home tab of the Ribbon.
3.Click the down arrow on the Delete button in the Cells group.
4.Click either the Delete Sheet Columns or the Delete Sheet Rows option.
Key Takeaways
•Column headings should be used in a worksheet and should accurately describe the data contained in each column.
•Using symbols such as dollar signs when entering numbers into a worksheet can slow down the data entry process.
•Worksheets must be carefully proofread when data has been manually entered.
•The Undo command is a valuable tool for recovering data that was deleted from a worksheet.
•When using a worksheet that was developed by someone else, look carefully for hidden column or rows.
Exercises
1.
When entering numeric data into an Excel worksheet, you should omit symbols such as commas or dollar signs because:
1.These numbers will not be usable in mathematical functions or formulas.
2.Excel will convert this to text data.
3.Excel will not accept these entries into a cell location.
4.It slows down the data entry process.
2.
Which of the following statements is true with respect to editing the content in a cell location?
1.Activate the cell location and press the F2 key on your keyboard to edit the data in the cell.
2.Double click the cell location to edit the data in a cell location.
3.Activate the cell location, click the Formula Bar, and make any edits for the cell location in the Formula Bar.
4.All of the above are true.
3.
Which of the following will enable you to identify hidden columns in a worksheet?
1.The column letter appears in a tip box when the mouse pointer is moved over a hidden column.
2.Clicking the Page Layout View button in the View tab of the Ribbon shows all columns in the worksheet and shades hidden columns.
3.The column letters that appear above the columns in a worksheet will be missing for hidden columns.
4.Click the Hidden Columns indicator in the Status Bar.
4.
Which of the following is true with respect to inserting blank rows into a worksheet?
1.Blank rows are inserted above the activated cell or cell range in a worksheet.
2.Blank rows are always inserted in the center of a cell range. At least two or more cells in a worksheet must be highlighted before a row can be inserted.
3.The command for inserting blank rows and columns can be found by clicking the Format button in the Home tab of the Ribbon.
4.When inserting blank rows into a worksheet, the Undo button is disabled. You must use the Delete button in the Home tab of the Ribbon to remove unwanted blank rows.
1.3 Formatting and Data Analysis
Learning Objectives
1.Use formatting techniques to enhance the appearance of a worksheet.
2.Understand how to align data in cell locations.
3.Examine how to enter multiple lines of text in a cell location.
4.Understand how to add borders to a worksheet.
5.Examine how to use the AutoSum feature to calculate totals.
6.Understand how to insert a chart into a worksheet.
7.Use the Cut, Copy, and Paste commands to manipulate the data on a worksheet.
8.Examine how to use the Sort command to rank data on a worksheet.
9.Understand how to move, rename, insert, and delete worksheet tabs.
This section addresses formatting commands that can be used to enhance the visual appearance of a worksheet. It also provides an introduction to mathematical calculations and charts. The skills introduced in this section will give you powerful tools for analyzing the data that we have been working with in this workbook and will highlight how Excel is used to make key decisions in virtually any career.
Formatting Data and Cells
Follow-
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-
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-
25.Notice that as you move the mouse pointer over the font style options, you can see the font change in the highlighted cells.
26.Expand the row width of Column D to 10 characters.
Why?
Pound Signs (####) Appear in Columns
When a column is too narrow for a long number, Excel will automatically convert the number to a series of pound signs (####). In the case of words or text data, Excel will only show the characters that fit in the column. However, this is not the case with numeric data because it can give the appearance of a number that is much smaller than what is actually in the cell. To remove the pound signs, increase the width of the column.
Figure 1.39 "Formatting Techniques Applied" shows how the Sheet1 worksheet should appear after the formatting techniques are applied.
Figure 1.39 Formatting Techniques Applied
Data Alignment (Wrap Text, Merge Cells, and Center)
Follow-
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-
Mouseless Commands
Merge Commands
•Merge & Center: Press the ALT key and then the letters H, M, and C one at a time.
•Merge Cells: Press the ALT key and then the letters H, M, and M one at a time.
•Unmerge Cells: Press the ALT key and then the letters H, M, and U one at a time.
Figure 1.41 Merge Cell Drop-
Why?
Merge & Center
One of the most common reasons the Merge & Center command is used is to center the title of a worksheet directly above the columns of data. Once the cells above the column headings are merged, a title can be centered above the columns of data. It is very difficult to center the title over the columns of data if the cells are not merged.
Figure 1.42 "Sheet1 with Data Alignment Features Added" shows the Sheet1 worksheet with the data alignment commands applied. The reason for merging the cells in the range A1:D1 will become apparent in the next segment.
Figure 1.42 Sheet1 with Data Alignment Features Added
Skill Refresher: Wrap Text
(click to see video)
1.Activate the cell or range of cells that contain text data.
2.Click the Home tab of the Ribbon.
3.Click the Wrap Text button.
Skill Refresher: Merge Cells
(click to see video)
1.Highlight a range of cells that will be merged.
2.Click the Home tab of the Ribbon.
3.Click the down arrow next to the Merge & Center button.
4.Select an option from the Merge & Center list.
Entering Multiple Lines of Text
Follow-
In the Sheet1 worksheet, the cells in the range A1:D1 were merged for the purposes of adding a title to the worksheet. This title will require that two lines of text be entered into a cell. The following steps explain how you can enter text into a cell and determine where you want the second line of text to begin:
1.Activate cell A1 in the Sheet1 worksheet by placing the mouse pointer over cell A1 and clicking the left mouse button. Since the cells were merged, clicking cell A1 will automatically activate the range A1:D1.
2.Type the text General Merchandise World.
3.Hold down the ALT key and press the ENTER key. This will start a new line of text in this cell location.
4.Type the text 2011 Retail Sales (in millions) and press the ENTER key.
5.Select cell A1. Then click the Italics button in the Font group of commands in the Home tab of the Ribbon.
6.Increase the height of Row 1 to 30 points. Once the row height is increased, all the text typed into the cell will be visible (see Figure 1.43 "Title Added to the Sheet1 Worksheet").
Figure 1.43 Title Added to the Sheet1 Worksheet
Skill Refresher: Entering Multiple Lines of Text
(click to see video)
1.Activate a cell location.
2.Type the first line of text.
3.Hold down the ALT key and press the ENTER key.
4.Type the second line of text and press the ENTER key.
Borders (Adding Lines to a Worksheet)
Follow-
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-
Figure 1.44 Borders Drop-
3.Left click the All Borders option from the Borders drop-
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-
7.Highlight the range A1:D15.
8.Click the down arrow to the right of the Borders button.
9.This will open the Format Cells dialog box (see Figure 1.45 "Borders Tab of the Format Cells Dialog Box"). You can access all formatting commands in Excel through this dialog box.
10.In the Style section of the Borders tab, left click the thickest line style (see Figure 1.45 "Borders Tab of the Format Cells Dialog Box").
11.Left click the Outline button in the Presets section (see Figure 1.45 "Borders Tab of the Format Cells Dialog Box").
12.Click the OK button at the bottom of the dialog box (see Figure 1.45 "Borders Tab of the Format Cells Dialog Box").
Figure 1.45 Borders Tab of the Format Cells Dialog Box
Figure 1.46 Borders Added to the Sheet1 Worksheet
Skill Refresher: Preset Borders
(click to see video)
1.Highlight a range of cells that require borders.
2.Click the Home tab of the Ribbon.
3.Click the down arrow next to the Borders button.
4.Select an option from the preset borders list.
Skill Refresher: Custom Borders
(click to see video)
1.Highlight a range of cells that require borders.
2.Click the Home tab of the Ribbon.
3.Click the down arrow next to the Borders button.
4.Select the More Borders option at the bottom of the options list.
5.Select a line style and line color.
6.Select a placement option.
7.Click the OK button on the dialog box.
AutoSum
Follow-
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-
Figure 1.47 AutoSum Drop-
4.Click the Sum option from the AutoSum drop-
5.Excel will provide a total for the values in the Unit Sales column.
6.Activate cell D15.
7.Repeat steps 3 through 5 to sum the values in the Sales Dollars column (see Figure 1.48 "Totals Added to the Sheet1 Worksheet").
8.This will remove the pound signs (####) and show the total.
Figure 1.48 Totals Added to the Sheet1 Worksheet
Skill Refresher: AutoSum
(click to see video)
1.Highlight a cell location below or to the right of a range of cells that contain numeric values.
2.Click the Formulas tab of the Ribbon.
3.Click the down arrow below the AutoSum button.
4.Select a mathematical function from the list.
Inserting a Column Chart
Follow-
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-
Figure 1.49 Column Chart Drop-
4.
Select the Clustered Column option from the list of column chart options (see Figure 1.49 "Column Chart Drop-
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-
Figure 1.50 Embedded Column Chart in Sheet1
5.The block white plus sign will become black cross arrows (see Figure 1.50 "Embedded Column Chart in Sheet1").
6.
Left click and drag the chart so the upper left corner is placed in the middle of cell F1 (see Figure 1.51 "Moving an Embedded Chart").
Figure 1.51 Moving an Embedded Chart
7.Place the mouse pointer over the top center sizing handleDots that appear around the perimeter of an embedded chart in a worksheet. They can be clicked and dragged to desired dimensions to change the size of a chart. (see Figure 1.50 "Embedded Column Chart in Sheet1"). You will see the mouse pointer change from a white block plus sign to a vertical double arrow. Make sure the mouse pointer is not in the cross arrow mode as shown in Figure 1.50 "Embedded Column Chart in Sheet1" as this will move the chart instead of resizing it.
8.While holding down the ALT key on your keyboard, left click and drag the mouse pointer slightly up. The chart will automatically adjust up to the top of Row 1.
9.Place the mouse pointer over the left center sizing handle.
10.While holding down the ALT key on your keyboard, left click and drag the mouse slightly toward the left. The chart will automatically adjust to the left side of Column F.
11.Place the mouse pointer over the lower center sizing handle.
12.While holding down the ALT key on your keyboard, left click and drag the mouse slightly down. The chart will automatically adjust to the bottom of Row 14.
13.Place the mouse pointer over the right center sizing handle.
14.
While holding down the ALT key on your keyboard, left click and drag the mouse slightly to the right. The chart will automatically adjust to the right side of Column M.
Why?
There Are No Sizing Handles on a Chart
If you do not see the dots or sizing handles around the perimeter of a chart, it could be that the chart is not activated. To activate a chart, left click anywhere on the chart.
Figure 1.52 "Embedded Chart Moved and Resized" shows the column chart moved and resized. Notice that the sizing handles are not visible around the perimeter of the chart. This is because the chart is not activated. Once you click anywhere on the worksheet outside the chart area, the chart is automatically deactivated.
Figure 1.52 Embedded Chart Moved and Resized
Why?
Use the ALT Key When Resizing a Chart
Using the ALT key while resizing an embedded chart locks the perimeter of the chart to the columns and rows of the worksheet. This gives you the ability to adjust the chart to precise sizes as you adjust the width and height of the worksheet rows and columns.
As shown in Figure 1.50 "Embedded Column Chart in Sheet1", when a chart is created, three tabs are added to the Ribbon. The following steps explain how to use a few of the formatting and design features in these tabs:
15.Check to make sure the column chart in Sheet1 is activated. To activate the chart, left click anywhere on the chart.
16.Click the Design tab under the Chart Tools set of tabs on the Ribbon.
17.
Click the down arrow on the right side of the Chart Styles section (see Figure 1.53 "Chart Styles in the Design Tab").
Figure 1.53 Chart Styles in the Design Tab
18.Click Style 44 in the Chart Styles section. This style has a black background with red columns (see Figure 1.53 "Chart Styles in the Design Tab").
19.Click the Format tab under the Chart Tools set of tabs on the Ribbon.
20.Click the down arrow on the right side of the WordArt Styles section (see Figure 1.54 "WordArt Styles in the Format Tab").
Figure 1.54 WordArt Styles in the Format Tab
Click the Blue, Accent 1, Inner Shadow option (see Figure 1.54 "WordArt Styles in the Format Tab"). Notice that as you move the mouse pointer over the WordArt Styles options, the format of the chart title as well as the X and Y axis titles changes.
Figure 1.55 "Formatting Features Applied to the Column Chart" shows the embedded column chart with the formatting features applied. This chart is very effective in displaying the Unit Sales trends for this company. You can see very quickly that the tallest bar in the chart is the month of December, followed by the months of June, July, January, and February.
Figure 1.55 Formatting Features Applied to the Column Chart
Skill Refresher: Creating a Column Chart
(click to see video)
1.Highlight a range of cells that contain data that will be used to create the chart.
2.Click the Insert tab of the Ribbon.
3.Click the Column button in the Charts group.
4.Select an option from the Column drop-
Cut, Copy, and Paste
Follow-
The Cut, Copy, and Paste commands are perhaps the most widely used commands in Microsoft Office. With regard to Excel, the Copy and Paste commands are often used to make copies of worksheets for developing different scenarios or versions for the data being analyzed. The following steps demonstrate how these commands are used for the objective in this chapter:
1.
Click the Select All button in the upper left corner of the Sheet1 worksheet (see Figure 1.56 "Clipboard Group of Commands").
Figure 1.56 Clipboard Group of Commands
2.
Click the Copy button in the Clipboard group of commands in the Home tab of the Ribbon (see Figure 1.56 "Clipboard Group of Commands").
Mouseless Command
Copy
◦Press the CTRL key and then the letter C key on your keyboard.
3.Open the Sheet2 worksheet by left clicking on the Sheet2 worksheet tab at the bottom of the workbook.
4.Activate cell location A1.
5.
Click the Paste button in the Clipboard group of commands in the Home tab of the Ribbon. Be sure to click the upper area of the Paste button and not the down arrow at the bottom of the button. A copy of Sheet1 will now appear in Sheet2.
Mouseless Command
Paste
◦Press the CTRL key and then the letter V key on your keyboard.
6.Click anywhere on the chart in the Sheet2 worksheet.
7.
Click the Cut button in the Clipboard group on the Home tab of the Ribbon. This will remove the chart from the Sheet2 worksheet.
Mouseless Command
Cut
◦Press the CTRL key and then the letter X key on your keyboard.
8.Open the Sheet3 worksheet by left clicking on the Sheet3 worksheet tab at the bottom of the workbook.
9.Activate cell location A1.
10.Click the Paste button in the Home tab of the Ribbon. This will paste the chart from the Sheet2 worksheet into the Sheet3 worksheet.
Sorting Data (One Level)
Follow-
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-
Figure 1.58 Sort Dialog Box
5.Click the Unit Sales option from the drop-
6.Click the down arrow next to the Order drop-
7.Click Largest to Smallest from the drop-
8.Click the OK button at the bottom of the Sort dialog box. The data in the range A2:D14 will now be sorted in descending order based on the values in the Unit Sales column.
Integrity Check
Sorting Data
Carefully check the highlighted range of the data you are sorting. It is critical that all columns in a contiguous range of data are highlighted before sorting. If you do not sort all the columns in a data set, the data could become corrupted in such a way that it may not be corrected. If Excel detects that you are trying to sort only part of a contiguous range of data, it will give you a warning dialog box.
Figure 1.59 "Data Sorted Based on Unit Sales" shows the data in the Sheet2 worksheet sorted based on the values in the Unit Sales column. Similar to the chart, the Sort command makes it easy to identify the months of the year with the highest unit sales.
Figure 1.59 Data Sorted Based on Unit Sales
Skill Refresher: Sorting Data (One Level)
(click to see video)
1.Highlight a range of cells to be sorted.
2.Click the Data tab of the Ribbon.
3.Click the Sort button in the Sort & Filter group.
4.Select a column from the “Sort by” drop-
5.Select a sort order from the Order drop-
6.Click the OK button on the Sort dialog box.
Moving, Renaming, Inserting, and Deleting Worksheets
Follow-
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-
12.Click the Delete button on the Delete warning box.
13.Click the Insert Worksheet tab at the bottom of the workbook (see Figure 1.60 "Renaming a Worksheet Tab").
Integrity Check
Deleting Worksheets
Be very cautious when deleting worksheets that contain data. Once a worksheet is deleted, you cannot use the Undo command to bring the sheet back. Deleting a worksheet is a permanent command.
Mouseless Command
Inserting New Worksheets
•Press the SHIFT key and then the F11 key on your keyboard.
Figure 1.61 "Final Appearance of the Excel Objective 1.0 Workbook" shows the final appearance of the Excel Objective 1.0 workbook after the worksheet tabs have been renamed and moved.
Figure 1.61 Final Appearance of the Excel Objective 1.0 Workbook
Skill Refresher: Renaming Worksheets
(click to see video)
1.Double click the worksheet tab.
2.Type the new name.
3.Press the ENTER key.
Skill Refresher: Moving Worksheets
(click to see video)
1.Left click the worksheet tab.
2.Drag it to the desired position.
Skill Refresher: Deleting Worksheets
(click to see video)
1.Open the worksheet to be deleted.
2.Click the Home tab of the Ribbon.
3.Click the down arrow on the Delete button.
4.Select the Delete Sheet option.
5.Click Delete on the warning box.
Key Takeaways
•Formatting skills are critical for creating worksheets that are easy to read and have a professional appearance.
•A series of pound signs (####) in a cell location indicates that the column is too narrow to display the number entered.
•Using the Wrap Text command allows you to stack multiword column headings vertically in a cell location, reducing the need to expand column widths.
•Use the Merge & Center command to center the title of a worksheet directly over the columns that contain data.
•Adding borders or lines will make your worksheet easier to read and helps to separate the data in each column and row.
•Effective charts enable readers to immediately identify key trends in the data you are displaying.
•Check to make sure all the data in a contiguous range of cells is highlighted before using the Sort command. Highlighting and sorting only part of a contiguous data set could corrupt your data in such a way that its integrity may not be restored.
•You cannot use the Undo command to bring back a worksheet that has been deleted.
Exercises
1.
The pound signs (####) that appear in a cell location indicate that:
1.A computational error has occurred.
2.The AutoSum feature was applied to text data instead of numeric data.
3.A number is too long for the current width of a column.
4.You must click the Calculate Sheet command in the Formulas tab of the Ribbon.
2.
Which of the following is most accurate with respect to the Wrap Text command?
1.It allows you to designate which words are placed on a second line in a cell.
2.It reduces the need to expand the width of the columns in a worksheet.
3.It converts any numeric data to text data.
4.It can be accessed only through the right-
3.
What is the quickest way to center a title over six columns of data?
1.Type the title into the cell location over the left-
2.Type the title into the cell location over the left-
3.Type the title into the cell location over the third column and use the BACKSPACE key to place the title over the center of the six columns.
4.Highlight the six cell locations over each of the columns and click the Merge & Center button in the Home tab of the Ribbon.
4.
Which of the following is true with respect to deleting worksheets?
1.You cannot use the Undo button to bring back a worksheet once it has been deleted.
2.Click the Select All button and press the DELETE key on your keyboard to delete a worksheet from a workbook.
3.Holding down the SHIFT key while pressing the F11 key on your keyboard will delete a worksheet from your workbook.
4.Excel will not let you delete a worksheet that contains data. All data must be removed from the worksheet before the worksheet can be deleted.
1.4 Printing
Learning Objectives
1.Use the Page Layout tab to prepare a worksheet for printing.
2.Add headers and footers to a printed worksheet.
3.Examine how to print worksheets and workbooks.
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 Setup
Follow-
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-
4.Click the Wide option from the Margins drop-
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-
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-
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-
Why?
Use Print Settings
Because professionals often share Excel workbooks, it is a good practice to select the appropriate print settings in the Page Layout tab even if you do not intend to print the worksheets in a workbook. It can be extremely frustrating for recipients of a workbook who wish to print your worksheets to find that the necessary print settings have not been selected. This may reflect poorly on your attention to detail, especially if the recipient of the workbook is your boss.
Figure 1.62 Page Layout Commands for Printing
Table 1.2 Printing Resources: Purpose and Use for Page Setup Commands
Command
Purpose
Use
Margins Sets the top, bottom, right, and left margin space for the printed document 1. Click the Page Layout tab of the Ribbon.
2. Click the Margin button.
3. Click one of the preset margin options or click Custom Margins.
Orientation Sets the orientation of the printed document to either portrait or landscape 1. Click the Page Layout tab of the Ribbon.
2. Click the Orientation button.
3. Click one of the preset orientation options.
Size Sets the paper size for the printed document 1. Click the Page Layout tab of the Ribbon.
2. Click the Size button.
3. Click one of the preset paper size options or click More Paper Sizes.
Print Area Used for printing only a specific area or range of cells on a worksheet 1. Highlight the range of cells on a worksheet that you wish to print.
2. Click the Page Layout tab of the Ribbon.
3. Click the Print Area button.
4. Click the Set Print Area option from the drop-
Breaks Allows you to manually set the page breaks on a worksheet 1. Activate a cell on the worksheet where the page break should be placed. Breaks are created above and to the left of the activated cell.
2. Click the Page Layout tab of the Ribbon.
3. Click the Breaks button.
4. Click the Insert Page Break option from the drop-
Background Adds a picture behind the cell locations in a worksheet 1. Click the Page Layout tab of the Ribbon.
2. Click the Background button.
3. Select a picture stored on your computer or network.
Print Titles Used when printing large data sets that are several pages long. This command will repeat the column headings at the top of each printed page. 1. Click the Page Layout tab of the Ribbon.
2. Click the Print Titles button.
3. Click in the Rows to Repeat at Top input box in the Page Setup dialog box.
4. Click any cell in the row that contains the column headings for your worksheet.
5. Click the OK button at the bottom of the Page Setup dialog box.
Headers and Footers
Follow-
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
Follow-
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-
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.
Key Takeaways
•The commands in the Page Layout tab of the Ribbon are used to prepare a worksheet for printing.
•You can add headers and footers to a worksheet to show key information such as page numbers, the date, the file name, your name, and so on.
•The Print commands are in the File tab of the Ribbon.
Exercises
1.
Which of the following commands is used to print the column headings in a worksheet at the top of each printed page for a worksheet that contains 100 rows of data?
1.the Header & Footer command in the Insert tab of the Ribbon
2.the Print Titles command in the Page Layout tab of the Ribbon
3.the Insert command in the Home tab of the Ribbon
4.the Conditional Formatting command in the Home tab of the Ribbon
2.
Which of the following is true with respect to printing Excel worksheets?
1.Setting the Width and Height drop-
2.The page layout settings must be set for each worksheet in a workbook.
3.You can print only one worksheet at a time in a workbook that contains multiple worksheets.
4.All of the above are true.
1.5 Chapter Assignments and Tests
To 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)
Starter File: Chapter 1 CiP Exercise 1
Difficulty: Level 1
Creating and maintaining budgets are common practices in many careers. Budgets play a critical role in helping a business or household control expenditures. In this exercise you will create a budget for a hypothetical medical office while reviewing the skills covered in this chapter. Begin the exercise by opening the file named Chapter 1 CiP Exercise 1.
Entering, Editing, and Managing Data
1.Activate all the cell locations in the Sheet1 worksheet by left clicking the Select All button in the upper left corner of the worksheet.
2.In the Home tab of the Ribbon, set the font style to Arial and the font size to 12 points.
3.Increase the width of Column A so all the entries in the range A3:A8 are visible. Place the mouse pointer between the letter A and letter B of Column A and Column B. When the mouse pointer changes to a double arrow, left click and drag it to the right until the character width is 18.00.
4.Enter Quarter 1 in cell B2.
5.Use AutoFill to complete the headings in the range C2:E2. Activate cell B2 and place the mouse pointer over the Fill Handle. When the mouse pointer changes to a black plus sign, left click and drag it to cell E2.
6.Increase the width of Columns B, C, D, and E to 10.14 characters. Highlight the range B2:E2 and click the Format button in the Home tab of the Ribbon. Click the Column Width option, type 10.14 in the Column Width dialog box, and then click the OK button in the Column Width dialog box.
7.Enter the words Medical Office Budget in cell A1.
8.Insert a blank column between Columns A and B. Activate any cell location in Column B. Then, click the drop-
9.Enter the words Budget Cost in cell B2.
10.
Adjust the width of Column B to 13.29 characters.
Formatting and Basic Charts
11.Merge the cells in the range A1:F1. Highlight the range and click the Merge & Center button in the Home tab of the Ribbon.
12.Make the following format adjustments to the range A1:F1: bold; italics; change the font size to 14 points; change the cell fill color to Aqua, Accent 5, Darker 50%; and change the font color to white.
13.Increase the height of Row 1 to 24.75 points.
14.Center the title of the worksheet in the range A1:F1 vertically. Activate the range and then click the Middle Align button in the Home tab of the Ribbon.
15.Make the following format adjustment to the range A2:F2: bold; and change the cell fill color to Tan, Background 2, Darker 10%.
16.Set the alignment in cell B2 to Wrap Text. Activate the cell location and click the Wrap Text button in the Home tab of the Ribbon.
17.Copy cell C3 and paste the contents into the range D3:F3.
18.Copy the contents in the range C6:C8 by highlighting the range and clicking the Copy button in the Home tab of the Ribbon. Then, highlight the range D6:F8 and click the Paste button in the Home tab of the Ribbon.
19.Calculate the total budget for all four quarters for the salaries. Activate cell B3 and click the down arrow on the AutoSum button in the Formulas tab of the Ribbon. Click the Sum option from the drop-
20.Copy the contents of cell B3 and paste them into the range B4:B8.
21.Format the range B3:F8 with a US dollar sign and zero decimal places.
22.Sort the data in the range A2:F8 based on the values in the Quarter 4 column in ascending order. Highlight the range A2:F8 and click the Sort button in the Data tab of the Ribbon. Select Quarter 4 in the “Sort by” drop-
23.Add vertical and horizontal lines to the range A1:F8. Highlight the range and click the down arrow next to the Borders button in the Home tab of the Ribbon. Select the All Borders option from the drop-
24.Change the name of the Sheet1 worksheet tab to “Budget.” Double click the worksheet tab, type the word Budget, and press the ENTER key.
25.Insert a pie chart using the data in the range A2:B8. Highlight the range and click the Pie button in the Insert tab of the Ribbon. Click the first option on the list (the Pie option).
26.Click and drag the chart so the upper left corner is in the center of cell H2.
27.
Add labels to the chart by clicking the Layout 1 option from the Chart Layouts list in the Design tab of the Ribbon. Make sure the chart is activated by clicking it once before you look for the Layout 1 Chart Layout option.
Printing
28.Change the orientation of the Budget worksheet so it prints landscape instead of portrait.
29.Adjust the appropriate settings so the Budget worksheet prints on one piece of paper.
30.Add a header to the Budget worksheet that shows the date in the upper left corner and your name in the center.
31.Add a footer to the Budget worksheet that shows the page number in the lower right corner.
32.Use the Save As command in the File tab of the Ribbon to save the workbook by adding your name in front of the current workbook name (i.e., “your name Chapter 1 CiP Exercise 1”).
33.Close the workbook and Excel.
Figure 1.66 Completed Medical Budget Exercise
Marketing for Specialty Women’s Apparel
Starter File: Chapter 1 CiP Exercise 2
Difficulty: Level 2
A key activity for marketing professionals is to analyze how population demographics change in certain regions. This is especially important for specialty retail stores that target a specific age group within a population. This exercise utilizes the skills covered in this chapter to analyze hypothetical population trends. The decisions that can be made with such information include where to open new stores, whether existing stores should be closed and reopened in other communities, or whether the product assortment should be adjusted. The purpose of this exercise is to use the skills presented in this chapter to analyze hypothetical population trends for a fashion retailer.
1.In the Sheet1 worksheet, enter the year 2008 into cell B3.
2.Use AutoFill to fill the years 2009 to 2012 in the range C3:F3.
3.Change the font style to Arial and the font size to 12 points for all cell locations in the Sheet1 worksheet.
4.Merge and center the cells in the range A1:F1.
5.Make the following formatting adjustments to the range A1:F1: bold; italics; change the cell fill color to Olive Green, Accent 3, Lighter 60%; change the font size to 14 points.
6.Enter the title for this worksheet into the range A1:F1 on two lines. The first line should read Population Trends by Age Group. The second line should read for Region 5.
7.Increase the height of Row 1 so the title is visible.
8.Delete Row 2.
9.Increase the height of Row 2 to 21 points.
10.Format the values in the range B3:F6 so a comma separates each thousands place with zero decimal places.
11.Make the necessary adjustments to remove any pound signs (####) that may have appeared after formatting the values.
12.Sort the data in the range A2:F6 based on the values in the year 2008 from largest to smallest.
13.Enter the word Totals in cell A7.
14.Increase the height of Row 7 to 22.50 points.
15.Format the range A7:F7 so entries are bold and italic.
16.In cell B7, add a total that sums the values in the range B3:B6. Format the value with zero decimal places and a comma for each thousands place.
17.Copy the contents of cell B7 and paste them into the range C7:F7.
18.Add vertical and horizontal lines to the range A1:F7.
19.Add a very bold orange border around the perimeter of the range A1:F7.
20.Insert a column chart using the data in the range A2:F6. Select the 2-
21.Move the column chart so the upper left corner is in the middle of cell A8.
22.Rename the Sheet1 worksheet tab to Population Trends.
23.Adjust the appropriate settings so the Population Trends worksheet prints on one piece of paper.
24.Add a header to the Population Trends worksheet that shows the date in the upper left corner and your name in the center.
25.Save the workbook by adding your name in front of the current workbook name (i.e., “your name Chapter 1 CiP Exercise 2”). (Hint: you will need to Save As.)
26.Close the workbook and Excel.
Figure 1.67 Completed Population Trends Exercise
Integrity Check
Starter File: Chapter 1 IC Exercise 1
Difficulty: Level 3
The purpose of this exercise is to analyze a worksheet to determine whether there are any integrity flaws. First, read the scenario below. Then, open the file that is related to this exercise and analyze the worksheets contained in the workbook. You will find a worksheet in the workbook named AnswerSheet. This worksheet is to be used for any written responses required for this exercise.
Scenario
Your 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-
•The data represents the top-
•I received the data from an analyst in my department who insisted the cost data for each item was included. However, I don’t see it. You might have to manually enter this yourself. I included the cost for each item below.
•The original data I received is in Sheet1. I copied this data and pasted it into Sheet2. I thought you might like to see this sorted.
•
Cost per item data:
Item
Cost
Black Flat $35.00
Bracelet $30.00
Brown Pump $25.00
Daisy Print $40.00
Grey Stripe $110.00
Jersey Knit $80.00
Navy Pinstripe $125.00
Navy Wool $135.00
Quartz Watch $80.00
Sandal $45.00
Tan Trench $115.00
Topaz Ring $50.00
Assignment
1.Analyze the data in this workbook carefully. Would you be comfortable using this data in a meeting with your boss? Use the AnswerSheet in this workbook to briefly list any concerns you have with this data.
2.If it is necessary to enter the cost information, enter it in the Sheet1 worksheet. If not, state why in the AnswerSheet.
3.Correct any problems and make any adjustments you think are appropriate to this workbook.
4.Save the workbook by adding your name in front of the current workbook name.
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.
Starter File: Chapter 1 AES Assignment 1
Difficulty: Level 3
The workbook for this assignment contains sales plan data by month for merchandise categories sold by a hypothetical clothing retailer. Use the skills covered in this chapter to accomplish the points listed below.
1.Show the total sales plan dollars next to each category in Column B.
2.
Instead of showing the sales plan dollars by month, calculate the plan dollars for each quarter (see the following figure, “Layout for Sales by Quarter”). The months assigned to each quarter are as follows:
◦Quarter 1: February, March, and April
◦Quarter 2: May, June, and July
◦Quarter 3: August, September, and October
◦Quarter 4: November, December, and January
3.Show the total plan for each quarter.
4.Sort the merchandise categories based on the total sales plan dollars.
5.Add any additional formatting enhancements that will make the worksheet easier to read.
Figure 1.68 Layout for Sales by Quarter
Chapter Skills Test
Starter File: Chapter 1 Skills Test
Difficulty: Level 2
Answer the following questions by executing the skills on the starter file required for this test. Answer each question in the order in which it appears. If you do not know the answer, skip to the next question. Open the starter file listed above before you begin this test.
1.In the Sheet1 worksheet, enter the word Totals in cell C14.
2.Format all the cells in Sheet1 to Arial font style and a 12-
3.Set the character width for Columns A through G to 12.71.
4.Edit the entry in cell B2 to read “Item Number.”
5.Use AutoFill to fill the contents of cell B3 into the range B4:B13.
6.Copy the contents of cell A3 and paste them into the range A4:A8.
7.Delete Column F.
8.Format the range A1:F2 so the text is Bold.
9.Set the alignment in the range A2:F2 to Wrap Text.
10.Change the fill color of the cells in the range A1:F1 to Red, Accent 2, Darker 25%.
11.Make the following font changes to the range A1:F1: set the font color to white, add italics, and set the font size to 14.
12.Merge and center the cells in the range A1:F1.
13.Enter the title for this worksheet in the range A1:F1. The title should appear on two lines. The first line should read Status Report. The second line should read Sales and Inventory by Item.
14.Increase the height of Row 1 so the entire title is visible.
15.Insert a blank row above Row 14.
16.Format the values in the range C3:C13 with a US dollar sign and two decimal places.
17.Format the values in the range E3:F13 with zero decimal places and a comma at each thousands place.
18.In cell E15, use AutoSum to calculate the sum of the values in the range E3:E14.
19.Add vertical and horizontal lines to the range A1:F15.
20.Add a bold line border around the perimeter of the range A1:F15.
21.Insert a column chart using the data in the range D2:E13.
22.Move the chart so the upper left corner is in the middle of cell H2.
23.Sort the data in the range A2:F13 based on the values in the Sales in Units column. Sort the values in descending order or largest to smallest.
24.Insert a new blank worksheet in the workbook.
25.Delete Sheet3.
26.Move Sheet4 ahead of Sheet2 so the order of the worksheets is Sheet1, Sheet4, and Sheet2.
27.Rename the Sheet1 worksheet tab to “Status Report.”
28.Change the orientation of the Status Report worksheet so it prints landscape instead of portrait.
29.Adjust the appropriate settings so the Status Report worksheet prints on one piece of paper.
30.Add a header to the Status Report worksheet that shows the date in the upper left corner and your name in the center.
31.Add a footer to the Status Report worksheet that shows the page number in the lower right corner.
32.Save the workbook by adding your name in front of the current workbook name (i.e., “your name Chapter 1 Skills Test”). (Hint: you will use Save As.)
33.Close the workbook and Excel.
Previous Chapter
Table of Contents
Next Chapter