Education Assignment for shy
Confirming Pages
Introduction to Excel Microsoft Excel is a spreadsheet program that enables you to enter, manipulate, calculate, and chart data. An Excel file is referred to as a workbook, which is a collection of worksheets. Each worksheet is comprised of rows and columns of data that you can perform calculations on. It is these calculations that make Excel such a powerful tool.
You can use Excel for a wide variety of purposes, from calculating payments for a per- sonal loan, to creating a personal budget, to tracking employee sales and calculating bonuses for your business.
This plug-in introduces the basics of using Excel. It is designed to show you the nuts and bolts, along with a few fancy features, to get you off to a good start using the program.
This plug-in focuses on the following topics:
1. Working with cells and cell data. 2. Formatting worksheets. 3. Creating and editing formulas. 4. Working with charts and graphics.
1. Describe how to insert, delete, merge, and split cells in a Microsoft Excel worksheet.
2. Describe how to insert and delete rows and columns in an Excel worksheet.
3. Explain how to create and edit formulas in Excel using the formula bar.
4. Describe how to create a chart using Excel.
LEARNING OUTCOMES
Basic Skills Using Excel 2019 T2 P L U G - I N
Plug-In T2 * T2-1
baL6732X_pluginT02_001-018.indd T2-1 12/21/16 07:43 AM
Confirming Pages
T2-2 * Plug-In T2
baL6732X_pluginT02_001-018.indd T2-2 12/21/16 07:43 AM
Working with Cells and Cell Data INSERTING AND DELETING CELLS You may find that you want to add some extra space or more information into your work- sheet. To do this, you must insert a new cell. This new cell can be left blank, or you can enter information into the cell. When you insert a new cell, you have the option to shift the existing data to the right or down, allowing you to place the new cell exactly where you want it.
To insert a cell:
1. Select the cell or cells where you want to insert the new cell(s). 2. Click the Home tab. 3. Click the Insert button, and then click Insert Cells. 4. Click the Shift cells right or Shift cells down radio button (see Figure T2.1). 5. Click OK.
You can customize a workbook and change the layout of data by deleting cells. Deleting cells not only deletes the information and formatting in the cell, but it also shifts the layout of the workbook. By deleting an empty cell, you shift all the surrounding cells as well.
To delete a cell:
1. Select the cell or cells that you want to delete. 2. Click the Home tab. 3. Click the Delete button, and then click Delete Cells. 4. Click the Shift cells left or Shift cells up radio button (see Figure T2.2). 5. Click OK.
Pressing the Delete key on the keyboard will delete the contents of the cell but not the cell itself.
MERGING AND SPLITTING CELLS Merging and splitting cells is one way to control the appearance of your worksheet. Titles of worksheets are typically centered across the top of the columns of information. Excel allows you to merge and center cells to create a title that appears centered in one cell across the top of your workbook. Excel also allows you to reverse this action by splitting the cell. Splitting a cell con- verts a merged cell back to several cells, with the information displayed in the uppermost left cell.
To center and merge cells:
1. Select the cells you want to merge, making sure the text you want centered is in the uppermost left cell.
2. Click the Home tab. 3. Click the Merge & Center button (see Figure T2.3).
FIGURE T2.1
Inserting a Cell
FIGURE T2.2
Deleting a Cell
FIGURE T2.3
Merging Cells
Copy
Paste
A cell that you have copied appears with a dotted line around it.
Cut
Confirming Pages
Plug-In T2 * T2-3
baL6732X_pluginT02_001-018.indd T2-3 12/21/16 07:43 AM
CUTTING, COPYING, AND PASTING CELLS The Cut, Copy, and Paste commands are used to move data and other items within a work- book and between applications. Data that are cut are removed from the document and placed on the Clipboard for later use. The Copy command places a duplicate of the selected data on the Clipboard without changing the workbook. The Paste command is used to insert items from the Clipboard into a workbook.
To cut or copy data within a workbook:
1. Select the cell or cells you want to cut or copy. 2. Click the Home tab. 3. Click the appropriate toolbar button:
a. Cut or b. Copy c. The cell appears with a flashing dotted line around it.
4. Place the cursor where you want to insert data from the Clipboard. 5. Click the Paste toolbar button (see Figure T2.4).
When you cut or copy items, they are placed on the Clipboard. The Clipboard can store up to 24 items for use in the current document or any other application. You can view the contents
FIGURE T2.4
Copying and Pasting Cells
Confirming Pages
T2-4 * Plug-In T2
baL6732X_pluginT02_001-018.indd T2-4 12/21/16 07:43 AM
of the Clipboard at any time by selecting Clipboard from the Home tab. The icons in the Clip- board identify the type of document from which each item originated (Word, Excel, Paint, etc.). A short description of an item will appear when you select it or move the cursor over its icon.
ENTERING TEXT IN CELLS Without text headers, descriptions, and instructions, your workbook would consist of num- bers and formulas without any structure. Adding text headers to your rows and columns cre- ates the structure for you to enter data into your workbook.
To add text to your workbook:
1. Click in the cell in which you want to add text. 2. Type your text. 3. Click outside the cell to have your entry accepted.
APPLYING NUMBER FORMATS Formatting your numbers changes the appearance of the data in your worksheet but does not change their value. The formatted number is displayed in the cell, and the actual value is displayed in the formula bar. Excel provides several numeric formats for you to use in your workbook, including Currency, Percentage, Date, Time, and Accounting.
To format numbers:
1. Select the cells you want to format. 2. Right click to get the Shortcut Menu, and choose Format Cells. 3. Click the Number list arrow, and then click the number format from the list (see Figure T2.5).
FIGURE T2.5
Applying Number Formats
Confirming Pages
Plug-In T2 * T2-5
baL6732X_pluginT02_001-018.indd T2-5 12/21/16 07:43 AM
Under each number category, you can choose predefined formatting or create and edit formats of your own.
The Formatting toolbar allows you to add default number styles. Select the cell you want to format, and then do one of the following:
To add the default currency style,
1. Select the cells you want to format. 2. Right click to get the Shortcut Menu, choose Format Cells. 3. Click the Currency list arrow, and then click the number format from the list (see
Figure T2.5).
As before, under each number category, you can choose predefined formatting or create and edit formats of your own.
APPLYING STYLES A style is the combination of effects that can be applied at one time. Styles can include for- matting such as character effects, background color, typefaces, and number formatting. Excel comes with predefined styles including Currency, Comma, and Percent styles, but also gives you the ability to create your own styles in the Style dialog box.
To apply a basic style:
1. Select the cells you want to format. 2. Click the Home tab. 3. Click the Styles drop-down button, and then click the cell style that you want to apply (see
Figure T2.6).
FIGURE T2.6
Applying Styles
Confirming Pages
T2-6 * Plug-In T2
baL6732X_pluginT02_001-018.indd T2-6 12/21/16 07:43 AM
Formatting Worksheets INSERTING ROWS AND COLUMNS You may need to add rows or columns of new information into your worksheet.
To insert a row:
1. Click the row immediately below the location of the row you want to insert. 2. Click the Home tab. 3. Click the Insert button arrow, and click Insert Sheet Rows.
To insert a column:
1. Click to the right of the location of the new column you want to insert. 2. Click the Home tab. 3. Click the Insert button arrow, and click Insert Sheet Columns (see Figure T2.7).
When you insert a row or column, a smart tag will appear. Click the smart tag to choose formatting options—Format Same As Left, Format Same As Right, or Clear Formatting.
FIGURE T2.7
Inserting a Row or Column
DELETING ROWS AND COLUMNS When you delete a row or column, you are removing all of those cells from your workbook. Once you have deleted the row or column, it disappears and the rest of the columns and rows move to replace it.
To delete a row or column:
1. Select the row header or column header you want to delete. 2. Click the Home tab (see Figure T2.8). 3. Click the Delete button arrow, and then click Delete Sheet Rows or Delete Sheet Columns.
Be careful. If you delete a row or column containing data, the data will be lost.
Confirming Pages
Plug-In T2 * T2-7
baL6732X_pluginT02_001-018.indd T2-7 12/21/16 07:43 AM
MODIFYING ROW HEIGHTS When you first enter data in your workbook, Excel automatically sets the rows of your work- sheet according to preferences. You may want to make rows a different height from this default setting.
To modify row heights:
1. Select the row or rows you want to change. 2. Drag the boundary (the physical line that separates each column and row) until the row is
the height you want (see Figure T2.9).
If you want to change all the rows in your worksheet to the same height, click the Select All button (the gray box above Row 1 and to the left of Column A) and then drag the bound- ary of any row to the height you want.
Double-click the boundary to make the row automatically fit the contents.
FIGURE T2.8
Delete a Row or Column
FIGURE T2.9
Modifying Row Heights
Confirming Pages
T2-8 * Plug-In T2
baL6732X_pluginT02_001-018.indd T2-8 12/21/16 07:43 AM
MODIFYING COLUMN WIDTHS When you first enter data in your workbook, Excel automatically sets the widths of the col- umns. As you type data into multiple columns, you may find that Excel does not display all the text in a cell. You can change the widths of columns in your workbook so that all your information is displayed.
To modify column widths:
1. Select the column or columns you want to change. 2. Drag the boundary until the column is the width you want (see Figure T2.10). If you
want to change all the columns in your worksheet to the same width, click on the Home tab, then the Format button, and then Column Width, and type in the width you want. Alternatively, you can click the Select All button (the gray box above Row 1 and to the left of Column A) and then drag the boundary. To make the column automatically fit the contents of the selected cell, double-click the boundary to the right of the column.
FIGURE T2.10
Modifying Column Widths
Creating and Editing Formulas ENTERING FORMULAS A formula is an equation that performs calculations between cells in a worksheet or table. A formula always begins with an equal sign. A simple formula may contain cell references and operators.
To enter a formula:
1. Click the cell in which you want to enter the formula. 2. Type = (an equal sign). 3. Type the formula. 4. Click outside the cell or press Enter (see Figure T2.11).
If a formula has more than one operator, Excel will perform mathematical operations, in this order:
■ Exponentiation. ■ Multiplication and division. ■ Addition and subtraction.
Adding parentheses around an operation will override this order, forcing Excel to perform calculations within the parentheses first.
Confirming Pages
Plug-In T2 * T2-9
baL6732X_pluginT02_001-018.indd T2-9 12/21/16 07:43 AM
USING THE FORMULA BAR To enter a formula in the formula bar:
1. Select the cell in which you want to add the formula. 2. In the formula bar, type = (an equal sign). 3. Enter the formula (including any functions, operators, references, and/or constants). 4. Click the Enter Formula button (see Figure T2.12).
FIGURE T2.11
Entering a Formula
FIGURE T2.12
Using the Formula Bar
Confirming Pages
T2-10 * Plug-In T2
baL6732X_pluginT02_001-018.indd T2-10 12/21/16 07:43 AM
Formulas can be complex equations. Often when you first enter a formula, you will not get the result you intended. This may be because a cell reference has changed, or because the operations are being performed in an undesired order. Use the formula bar when you need to edit a formula.
To edit a formula using the formula bar:
1. Select the cell containing the formula you want to change. 2. Click inside the formula bar. 3. Click and drag to highlight the part of the formula you want to change. 4. Make the changes to the formula. 5. Click the Enter Formula button.
USING ABSOLUTE AND RELATIVE REFERENCES Cell references can be relative, absolute, or mixed. A relative reference is a reference that adjusts to the new location in the worksheet when the formula is copied. An absolute refer- ence is a reference whose location remains constant when the formula is copied. A mixed reference is a reference that contains both a relative and an absolute reference. Figure T2.13 displays an example of each.
FIGURE T2.13
Using Absolute and Relative References
A2 $A$2 A$2
A relative reference adjusts to the new location when the formula is copied
An absolute reference remains constant when the formula is copied
A mixed reference contains both a relative and an absolute reference
To enter an absolute or relative reference:
1. Select the cell to enter a formula. 2. Type the = (equal sign) and the name of the cell.
■ To enter an absolute reference, type a $, the column name, another $, and the row name (e.g., $A$1).
■ To enter a mixed reference, type a relative reference and an absolute reference in the cell reference (e.g., A$1 or A1$).
By default, formulas use relative references. If you want your formula to have an absolute reference, you must change the reference to absolute.
USING THE SUM FUNCTION The SUM mathematical function is used to add together the values in several cells. Instead of writing a formula with several references separated by a plus sign, you can sum a range of cells. A SUM function looks like this: =SUM(A3:A6).
To use the SUM function:
1. Select the cell in which you want to enter the function. 2. Click the Insert Function button (fx) to the left of the formula bar (see Figure T2.11). 3. Click SUM from the list of Most Recently Used in the Or select a category: drop-down
box, and click OK. 4. Enter the range of cells that you want to add. 5. Click OK (see Figure T2.14).
Confirming Pages
Plug-In T2 * T2-11
baL6732X_pluginT02_001-018.indd T2-11 12/21/16 07:43 AM
When you click an argument box, a description of the argument appears below the descrip- tion of the function. An argument is a name for a value, expression, or cell reference that is passed to the function for its use in calculating an answer. As you enter arguments, the dialog box will display the results of your formula.
If the SUM function is not in your list of most recently used functions, click the arrow next to the Or select a category: box, click Math & Trig, and select SUM from that list of functions.
USING THE MIN AND MAX FUNCTIONS The MIN (minimum) statistical function will give you the smallest value in a range of values. The MAX (maximum) statistical function will give you the largest value in a range of values. These functions look like this:
MIN function: = MIN(A3:A6) MAX function: = MAX(A3:A6) To use the MIN and MAX functions:
1. Select the cell in which you want to enter the function. 2. Click the Insert Function button on the formula bar. 3. Click MIN or MAX from the list of Most Recently Used functions and click OK. 4. If necessary, enter the range of cells. 5. Click OK (see Figure T2.15).
If the MIN or MAX functions are not in your list of Most Recently Used functions, click the arrow next to the Or select a category: box, click Statistical, and select MIN or MAX from that list of functions.
When you click an argument box, a description of the argument appears below the descrip- tion of the function. As you enter arguments, the dialog box will display the results of your formula. By default, Excel will enter a range of contiguous cells for you.
FIGURE T2.14
Insert Functions
Confirming Pages
T2-12 * Plug-In T2
baL6732X_pluginT02_001-018.indd T2-12 12/21/16 07:43 AM
USING THE DATE OR NOW FUNCTION Use the Date & Time function or the NOW function to insert the date and time into your workbook. The date and time will be displayed at all times but will be updated only when the worksheet is calculated. The NOW function looks like this: =NOW()
To use the NOW function:
1. Select the cell in which you want to enter the function. 2. Click the Insert Function button on the formula bar. 3. Click NOW from the list of Most Recently Used or Date & Time functions and click OK. 4. The NOW function takes no arguments. 5. Click OK (see Figure T2.16).
The NOW function uses the computer’s system clock to determine the date and time.
FIGURE T2.15
Using the MIN and MAX Functions
FIGURE T2.16
Using the NOW Function
Confirming Pages
Plug-In T2 * T2-13
baL6732X_pluginT02_001-018.indd T2-13 12/21/16 07:43 AM
Working with Charts and Graphics CREATING A CHART A chart is a visual representation of data from your workbook. Charts add a visual element to your workbook and help convey the information in a simple, easy-to-understand manner (see Figure T2.17).
To create a chart:
1. Select the data you want to use to create a chart. 2. Click the Insert tab. 3. Use one of the following methods:
■ Chart Types. Click a chart button (Column, Line, Pie, Bar, Area, Scatter, Other Charts) in the Charts group, and then click the chart type you want.
■ Recommended Chart. Click the Recommended Charts Dialog Box Launcher, click a category in the left pane, click a chart, and then click OK.
FIGURE T2.17
An Excel Chart
Confirming Pages
T2-14 * Plug-In T2
baL6732X_pluginT02_001-018.indd T2-14 12/21/16 07:43 AM
FIGURE T2.18
Modifying a Chart
MODIFYING CHARTS When you modify a chart, you can change any of the options that belong to that chart type. Modifying a chart allows you to change the text of the chart and how it appears on the chart. This includes titles, legends, axes, data labels, and data tables (see Figure T2.18).
To change chart elements select the chart you want to modify, and go to the Type group.
■ To change a chart type, click the Change Chart Type button. ■ Click the layout you want. ■ To change the chart title, click the Chart Elements button, and then choose Chart Title. ■ To change the chart labels, click the Chart Elements button, and then click the Chart
Legend button. ■ To delete a chart, select the chart and press the Delete key.
MOVING A CHART When you create a chart, Excel places the chart in the middle of the worksheet. However, the chart may be covering data that you want to view. You can move a chart by selecting it and then dragging it anywhere on the worksheet.
To move a chart by dragging:
1. Select the chart you want to move. 2. Click in the chart area margin. 3. With your left mouse depressed, drag the chart to the new location on the worksheet. 4. Release the mouse button.
ADDING GRAPHICS A graphic is a drawing or illustration that can be added to your workbooks. You can add drawing objects such as AutoShapes from the Drawing toolbar. You can also insert clip art and other graphic files into your workbook. These images are embedded objects, meaning they become part of the new document.
To add a graphic to a workbook:
1. Place your cursor where you want the graphic to appear. 2. Click the Insert tab. 3. Click the Picture button (see Figure T2.19). 4. Select Insert Picture from File, then click the Look in: list arrow, and then select the
drive and folder that contains the file you want to insert. 5. Click the file you want to insert and then click Insert.
Confirming Pages
Plug-In T2 * T2-15
baL6732X_pluginT02_001-018.indd T2-15 12/21/16 07:43 AM
FIGURE T2.19
Adding Graphics
Confirming Pages
T2-16 * Plug-In T2
baL6732X_pluginT02_001-018.indd T2-16 12/21/16 07:43 AM
Microsoft Excel is a general-purpose electronic spreadsheet used to organize, calculate, and analyze data. The tasks you can perform with Excel range from preparing a simple invoice to managing an accounting ledger for a business.
This plug-in covered the following topics related to Excel:
1. Working with cells and cell data.
2. Formatting worksheets.
3. Creating and editing formulas.
4. Working with charts and graphics.
P L U G - I N S U M M A R Y
1. Stock Watcher Mark Martin has created a basic stock watcher worksheet that he uses to report on gains or losses from when he purchased the stock and the last recorded date and price. Mark has given you a snapshot of his spreadsheet (see Figure T2.20) that you can use to recreate this spreadsheet for yourself. Here are some basic steps to follow:
1. Open T2 Stock Watcher Data File.xlsx. 2. Enter all the information provided in Figure T2.20.
3. Apply the Currency format to the respective columns.
4. The date should be entered as a function. Hint: Use the NOW function.
5. Enter a formula for the Gain/Loss (%) column. Hint: You should subtract the Last column from the Purchase column, and then divide by the Purchase column.
6. Format for percent in the Gain/Loss (%) column
M A K I N G B U S I N E S S D E C I S I O N S
FIGURE T2.20
Stock Watcher Data
2. Total Mischief
Mischief, Inc., is a regional pet toy supplier that tracks its business sales in a spreadsheet. The owner, Lisa Derrick, has provided you with a skeleton worksheet, T2 TotalMischief Data File.xlsx, with the totals for each quarter by sales region. Lisa needs you to total each column and row, and then provide her with a clustered column chart of each region by quarter. See Figure T2.21 for a sample of what Lisa would like you to do.
Confirming Pages
Plug-In T2 * T2-17
baL6732X_pluginT02_001-018.indd T2-17 12/21/16 07:43 AM
3. Recycling Can
For the past 10 years, five Colorado cities have held a recycling contest to see which city does the best job of recycling plastic, glass, and aluminum. The cities participating in this year’s contest are Arvada, Centennial, Lakewood, Highlands Ranch, and Parker.
To make the contest fair for both large and small cities, the winning city will be the one that recycles the largest number of cans per capita—the number of cans recycled divided by the num- ber of city residents.
You have been asked to help the coordinator, Jill Slater, to compile the numbers in an Excel worksheet and create the formulas to compute the total recycling by city each month, total recycling for all cities each month, and the per-capita recycling value that determines the contest winner. In addition, Jill wants to know a few statistics about the monthly recycling efforts, including the minimum, average, and maximum number of cans recycled. Jill has provided you with sample data, T2 RecyclingCans Data File.xlsx. Figure T2.22 shows a sample of what Jill would like to see as a completed worksheet.
FIGURE T2.21
Total Mischief Spreadsheet
FIGURE T2.22
Recycling Can Contest Worksheet
4. MusicPlayerz Sales Projections
MusicPlayerz is a wholesale MP3 distributor headquartered in Morrison, Colorado. Corporate buyers for the retail stores contract with MusicPlayerz to supply and ship MP3s to warehouses scattered throughout the western United States. MusicPlayerz chief procurement officer Julianne Beekman oversees the purchase and distribution operations for all divisions from the Morrison office.
Confirming Pages
T2-18 * Plug-In T2
baL6732X_pluginT02_001-018.indd T2-18 12/21/16 07:43 AM
MusicPlayerz also maintains a small website from which it sells to consumers. Although the online store is not a large part of the revenue stream, it is an essential and growing part of Music- Playerz’s business. Julianne has developed a sales report for the coming year, using the previous year’s figures as the basis of the projection. Julianne wants to investigate sales predictions based on the assumption that next year’s wholesale sales will increase by 10 percent for each product included in the projection.
Julianne has asked you to complete the worksheet she has provided you, T2 Music-Playerz Data File.xlsx, for her presentation at the annual board meeting next month. You will have to calculate the following:
Projected sales (this is 10 percent more than current year figures).
Gross sales (this is the projected sales times the price).
Profit.
Percent of sales.
Figure T2.23 shows a sample of what Julianne would like to see as a completed worksheet.
FIGURE T2.23
MusicPlayerz Sales Projection Worksheet