Home

Unit 5

Student Learning Outcomes:


1. Learn about spreadsheets and how they work.
2. Identify major components of the Excel window.
3. navigate within and between worksheets.
4. Enter text, dates, data, and formulas into a worksheet.
5. Change the size of a column or row.
6. Select and move cell ranges
7. Calculate totals with AutoSum
8.Insert and delete a column or row
9. Work in edit mode
10. Undo an action
11. Insert, move, and rename a worksheet.
12. Check the spelling in a workbook.
13. Preview and print a workbook
14. Display the formulas in a worksheet.

Important Points:

1. Spreadsheet is tool for analyzing and reporting information. budgeting, what if, inventory.
2. Workbook is 256 sheets. A sheet is 256 columns A to IV and 65536 rows.
3. Excel Window components:
 

Feature Description
Active cell The cell in which you are currently working.
Column headings The letters that appear along the top of the worksheet window A to IV
Formula bar The bar located immediately below the toolbars that displays the contents of the active cell.
Name box The box that displays the cell reference, or column and row location of the active cell in the workbook.
Row headings the numbers that appear along the left side of the worksheet window. 1 to 65536
Select All button Square button located at the intersection of the column and row heading that you click to select the entire content of the workbook.
Sheet tabs Tabs located at the bottom of each worksheet in the workbook that display the names of the sheets.
Task pane The pane that provides access to frequently used tasks.
Tab scrolling buttons Series of buttons located to the left of the sheet tabs that you can click to move between worksheets in the workbook.
Toolbars Toolbars that provide quick access to commonly used commands.

4. F5 is go to key.
5. Developing a worksheet:
a. What is goal of the worksheet.
b. What are the desired results? output
c. What is required data input?
d. What are the required data manipulations to get the results.
6. Data in worksheet: text, numeric values, dates and calculated values.
7. Formula must begin with =.
8. Entering multiple line of text in cell: enter line of text; Alt-Enter gives you new line in cell.
9. Dates are treated as numbers: Allowed formats: mm/dd/yyyy- mm/dd/yy - mm-dd-yyyy - Month dd, yyyy - dd-month-yy:
10. Formula is mathematical expression that calculate a value.
11. Arithmetic operators - +, -, *, /.
12. Order of precedence - Exponential (^), Multiplication (*) or Division (/), addition (+) and subtraction (-)
13. Parentheses ( ) is used to change the order of operations.
14. Boolean operators And, Or, XOR, NOT
15. Relational operators >, <, >=, <=, <>, =
16. AutoComplete - once text is entered into cell, excel anticipates the text you are about to enter in another cell.
17. Changing size of column or row.
a. click column or row heading, click format, point to column or row, click width or height or click auto fit, in width or row height enter the new column width or row, click ok.
b. drag the column or row heading dividing line to the right or up to increase the column or row height or width.
c. double click the column or row heading to auto adjust.
18. Save workbook - click file, click save, select where to save, type in file name, select format.
 

Format Description
Microsoft Excel 4.0,5.0,97,2000,2002 Save workbook in earlier version of Excel
Single file web page Save the workbook as a single web page file that can be read by IE 4.0 and later
Template save the workbook as a template to be used for creating other Excel workbooks.
Web Page Saves the workbook in separate files that are used as the basis for a web site in a format that is readable by most browsers.
XML spreadsheet Save the workbook as an XML document.

19. Cell range is a group of worksheet cells.
20. Range reference indicates the location and size of a cell range.
21. Select adjacent range - click on cell in upper left of range, drag and select cells.
22. Nonadjacent range - select first range, use ctrl-click to select next nonadjacent range.
23. Move a selection of cells - highlight cells, move pointer to border of selection, Press and hold the left mouse button, drag to new place.
24. AutoSum feature is a quick and convenient way to enter the SUM function. Select the cell that you want a sum, click the AutoSum button on the toolbar..
25. Insert Row or Column: select cell where you want to insert. Click insert on menu bar. click rows or columns. In insert dialog box make selection.
26. Delete row or column: click the row or column header, Edit, Delete.
27. Edit mode is used to edit cell contents. Enter edit mode by double clicking cell.
28. Undo and redo can be used for changing actions.
29. Delete a worksheet. select the sheet tab, right-click the sheet tab, choose delete.
30. Insert a new worksheet: Click insert on the menu bar, click worksheet.
31. Rename worksheet: Double sheet tab, type in new name.
32. Reposition worksheet. click sheet tab, click and drag along row of sheet tabs and release when in position.
33. Spell Checker check word against the programs internal dictionary. Check each cell. Click spelling button on standard toolbar.
34 . Previewing and Printing a Worksheet. File, Print, open print dialog box. Select what you want to print. Click preview on the print dialog box to preview the document in print format.
35. Portrait and Landscape orientation: Click setup on print preview toolbar. Click landscape or portrait option button., Click OK.
36. Printing Formulas. With worksheet active press Ctrl + ` (grave accent not single quote), click print preview on standard toolbar, click setup, click fit to, click ok, click print.

 

Unit 6

Student Learning Outcomes:

1. Learn abut the syntax of an excel function
2. Use the SUM, Average, and Max functions.
3. Copy and paste formulas
4. Work with relative, absolute, and mixed references.
5. Change the magnification of the workbook window.
6. Insert a function using the insert function dialog box.
7. Use Auto Fill to insert formulas and complete a series.
8. Insert the current date using a date function.
9. Work with Financial functions
10. Work with logical functions.

Important Points.
 

1. Functions are built in programs with the following syntax: Function(argument, optional argument) Example: sum(c1:c15) sum is the name of the function: c1:c15 is the range of cells that make up the argument. The argument is what the function works on.
2. 350 functions built into excel: Database, Date and Time, Engineering, Financial, Information, Logical, Lookup and reference, math and trigonometry, statistical, and text and data functions are the categories:

Functions Description
Average(cell ref or sum) Calculates the average of a collection of number who are in cells
Count(cell ref) Calculates the total number of values that are numeric, text or cell ref.
Max(cell ref or num) Calculates the maximum number of a group.
Median(cell or num) Calculates the median (center number)
Min(cell or num) Calculates the minimum number.
Round(number,num-digits) Rounds a number to specified number of digits. Number represent the number and num-digits represent the number of places to be rounded to.
Sum(cell or num) Calculates the sum of a collection of numbers.

3. Copying and Pasting a cell or range. Select cell or range, click copy, select the cell or range you want to copy to, click paste.
4. Relative reference is a cell reference that will change relative to the cell copied to.
5. Absolute reference is a cell reference that will not change when copied. indicated with $.
6. Mixed reference contain both in the formula when copied.
7. F4 is your absolute reference key.
8. Other paste options:

Option Description
Formulas Pastes the formula(s), but not the formatting, of the source cell range
Values Pastes the value(s), but not the formula(s) or formatting, of the source cell range
No Borders Pastes the formula(s) and formatting of the source cell range, but not the format f cell range borders.
Transpose Pastes the formula(s) and formatting of the source cell range, except changes the orientation so that row in the source cell range become columns, and columns become rows.
Paste Link Pastes a link to the cell(s) in the source cell range, including the formatting used
Paste Special Opens a dialog box displaying more paste options. Let you specify what part of cell you want to paste and specify what mathematical operation to be performed.

9. Changing Magnification - click view, click zoom, click option button for percent of magnification, click ok or click the zoom list arrow and click the percent you want.
10. Insert Function Dialog Box - put you cursor on the cell, click Insert Function button on the formula bar, search for a function or select a category, select function, click ok.
11. Fill handle-Auto Fill - enables you to copy the contents of selected cells simply by dragging the fill handle.
12. Auto Fill options - Copy Cells, Fill Formatting Only, Fill without Formatting.
13. Auto Fill Series
 

Type Initial Selection Extended Series
Values 123
2,4,6
4,5,6
8,10,12
Dates and Times Jan
January
Jan, Apr
15-Jan, 15-Feb
12/30/2005
12/31/2005,1/31/2006
Mon
Monday
11:00 AM
Feb, Mar, Apr,...
February, March, April
Jul, Oct, Jan, ...
15-Mar, 15-Apr,15-May, ...
12/31/2005,1/1/2006,1/2/2006
2/28/2006, 3/31/2006, 4/30/2006
Tue, Wed, Thu, ....
Tuesday, Wednesday, Thursday, ...
12:00 PM, 1:00 PM, 2:00 PM
Patterned Text 1st Period
Region 1
Quarter 3
Qtr3
2nd Period, 3rd Period, 4th Period
Region2, Region 3, Region 4, .....
Quarter 4, Quarter 1, Quarter 2, ....
Qtr4, Qtr1, Qtr2, ......

14. Date and Time Functions: Remember all functions begin with =.

Function Description
Date(year, month, day) Creates a date value for the date represented by the year, month, and day arguments.
Day(date) Extract the moth number from the date value, where 1= Jan, 2=Feb
Month(date) Extracts the month number from the date value, where 1=January
Year(date) Extracts the year number from the date value
Weekday(date,[return_type]) Calculates the day of the week from the date value, where 1=Sunday
Now() Displays the current date and time
Today() Displays the current date


15. Financial functions are widely used in the world of business.
 

Function Description
PMT(rate,nper,pv,[fv=0],[type=0]) rate is interest rate per payment period- nper is total number of payments - pv is the present value of the loan and must be entered as a negative number -fv indicates the future value of the loan with default of 0 - type specifies when payment is made.
PV(rate,nper,pmt,[fv=0],[type=0]) Calculates the present value of a loan or investment based on periodic, constant payments.
NPER(rate,pmt,pv,[fv=0],[type=0]) Calculates the number of periods required to pay off a loan or investment
RATE9nper,pmt,pv,[fv=0],[type=0]) Calculates the interest rate of a loan or investment based on periodic, constant payments.

16. Logical function test or evaluates whether a condition in the workbook is true or false.
17. =if(logical_test, value_if_ true, value_if_false) is a selection control structure in programming.

Example: =if(a1>60,"You passed","You failed")

18. Comparison operators

> greater than A > B
< less than A < B
>= greater than or equal to A >= B
<= less that or equal to A <= B
= equal to A = B
<> not <> A

Unit 7

Student Learning Outcomes:

1. Format date using the comma, currency, and percent styles
2. Copy and paste formats using the format painter.
3. Modify and apply number formatting styles.
4. Change font type, style, size, and color.
5. Change the alignment of cell contents
6. Apply borders, background color's, and patterns.
7. merge a range of cells into a single cell
8. Hide rows, columns, and worksheets
9. Format worksheet tabs
10. Clear and replace formats
11. Create and apply styles
12. Apply an AutoFormat
13. Set up a worksheet for printing
14. Add header and footers to printouts.
 

Important Points:

1. Formatting is the process of changing the appearance of your workbook.
2. The formatting toolbar is one of the fastest ways to format a worksheet. Click single button.
3. Comma Style - adds the comma separator to each of the values in the table with two digits to the right of the decimal point.
4. Decrease decimal. - to remove decimal places.
5. Currency Style - adds the dollar signs, commas, and keeps tow decimal places. dollar sign at left of cell and decimals lined up vertically.
6. Percent Style- values appear with percent signs and without zeros.
7. Increase Decimal - to display additional decimal places.
8. Format Painter- allows you to copy the format of cells to other cells.- one click to one range or cell, two click to multiple ranges or cells.
9. Format Cells Dialog box - click format, click cells to activate.
 

Tab Function
Number Provides options for formatting numbers, dates, and numbers treated as text.
Alignment Provides options for controlling how data is aligned within a cell
Font Provides options for selecting font types, sizes, and styles, underlining, colors.
Border provides options for adding borders around cells
Patterns Provides options for creating and applying background colors and patterns to cells
Protection Provides options for locking or hiding cells to prevent other users from modifying their contents.

10. Number format categories
 

Category Description
General Default format that display numbers as they are entered
Number Used for general display of numbers, with options for the formatting of negative numbers and the number of decimal places
Accounting Used for displaying monetary values with dollar signs aligned at the left edge of the cell, the decimal points aligned vertically, and comma separators inserted.
Currency Used for displaying monetary values with dollar signs aligned next to the leftmost digit and comma separators inserted.
Date, Time Used for displaying date and time values.
Percentage Used for displaying decimal values as percentages
Fraction, Scientific Used for displaying values as fractions or in scientific notation.
Text Used for displaying values as text strings
Special Used for displaying ZIP codes, phone numbers, ssn
Custom Used for displaying numbers used in coding or specialized designs.

11. Font is set of characters that use the same typeface, style, and size.
12. Font styles - bold, bold italic, italic, underline, strikeout, color.
13. Font sizes are measured in points, the higher the points the larger the font. 1 pt = 1/72 of an inch.
14. Font color - use the font color list arrow on the formatting toolbar.
15. Aligning Cells - use the left, right, center buttons on the formatting tool bar.
16. To center across a selection of cells - click Format, click Cells, click alignment tab, click horizontal list arrow, click center across selection, click ok.
17. Increase indent button is used to indent contents of cell.
18. Auto wrap - select range of cells, click format, click cells, click alignment tab, select wrap text check box, click OK. Also can rotate the contents of a cell.
19. Cell borders: Gridlines provide a visual cue for the layout of the cells.
20. select cells, click the borders button list arrow on the formatting toolbar. Select the border options you want.
21. Draw borders. Click borders list arrow, click the draw borders button, click the line style list arrow, select your line style, drag the pointer over the borders you want drawn.
22. Draw double borders - format, cells, border, double line in style list box, select desired border, click OK.
23. Background colors and patterns. Fill color, select range, click fill color on formatting toolbar, select the color.
24. Patterns: select range, click format, click cells, clickpatter5ns tab, click pattern list arrow, select pattern, click ok.
25. Merge Cells - select range, click merge and center button on formatting toolbar
26. Hide row, columns and worksheets- select row or column. Right click selection, click hide.
27. Unhide row, columns - select row or column , right click, click unhide.
28. Hide sheet - click format, point to sheet, click hide.
29. Unhide sheet - click format, point to sheet, click unhide, select sheet in list, click ok.
30. Background Image - click format, point to sheet, click background, locate image, click insert button.
31. Change tab color - right click tab, click tab color, select color, click ok.
32. Clear Formatting - select cells, edit, clear, formats.
33. Find and Replace Format- Edit, Replace, options, find what format, font, select formatting to be found, OK, Replace With Format, select format to replace prior format, Replace all, OK.
34. Style is a saved collection of formatting options. Format, Style, type a name in the style name list box, select or deselect options, OK.
35. Apply Style - select cells, format, style, click style name list arrow, select style, OK.
36. AutoFormat provides a gallery of 17 predefined formats.- select cells, Format, AutoFormat, select the desired AutoFormat, OK.
37. Formatting Printed Worksheet- Print Preview, Setup, Margins, select desired margin widths, center on page Horizontally or Vertically, OK.
38. Change page orientation: Print Preview, Setup, Page, click Landscape or Portrait, OK.
39. Header is text printed in the top margin of every worksheet, Footer is text printed in the bottom margin of every worksheet.
40. Header/Footer formatting buttons

Button Name Formatting Code Action
  Font none sets font, text style, and font size
  Page Number &[Page] Inserts page number
  Total Pages &[Pages] Inserts total number of pages
  Date &[date] Inserts Current Date
  Time &[time] Inserts Current Time
  File Path &[Path]&[File] Inserts path and filename
  Filename &[file] Inserts file name
  Tab Name &[tab] Inserts name of active worksheet
  Insert Picture &[picture] Inserts an image file
  Format Picture none Opens the format picture dialog box.

41. Add header/footer to workbook: Print preview, setup, header/footer, custom header. Fill in the left, center and right sections. Ok.
custom footer, fill in the left, center and right sections, OK, OK.
42. A print area contains the content that you want to print. Select range of cells, File, Print Area, Set Print area.
43. Insert Page Break- Click row, insert, page break
44. To Repeat rows on each page. File, Page Setup, sheet, rows to repeat at top, select rows

Unit 8

Student Learning Outcomes:

1. Create column and pie charts using the Chart Wizard
2. Move and resize a chart
3. Embed a chart in a worksheet
4. Place a chart on a chart sheet
5. Edit the data source
6. Change the location of a chart
7. Modify chart objects
8. Insert and format chart text
9. Create 3-D charts
10. Add and modify drawing objects using the Drawing toolbar
11. Print a chart

Important Points:

1. Chart Wizard - select data, chick chart wizard, select chart type, next, make any modification to chart data source, next, make modifications to chart appearance, next, specify location for chart, finish.

Dialog box Task Options
Chart Type Select from list of available chart types and corresponding sub-types, or choose to customize a chart type
Chart Source Data Specify the cells that contain the data on which the chart will be based and the cells that contain the labels that will appear in the chart
Chart Options Change the appearance of the chart by selecting the options that affect titles, axes, gridlines, legends, data labels, and data tables
Chart Location specify where the chart will be placed: embedded as on object in the worksheet containing the data or on a separate worksheet, also called a chart sheet.

2. Chart Type:

Chart Type Description
Column compares values from different categories. Values are indicated by height of column
Bar compares values from different categories. Values are indicated by the length of the bars.
Line compares values from different categories. Values are indicated by the height of the line. Often used to show trends.
Pie Compares relative values of different categories to the whole. value are indicated by the size of the pie slice.
XY(scatter) shows the patterns or relationship between two or more sets of numeric values. Often used in scientific studies and statistical analyses.
Area Similar to the line chart, except that areas under the lines are filled with colors indicating the different categories.
Doughnut similar to the pie chart, except the it can display multiple sets of data
Radar compares a collection of values from several different data sets.
Surface compares threes sets of values in a three-dimensional chart.
Bubble similar to the XY (scatter) chart, except the size of the data marker is determined by a third numeric value.
Cylinder, cone, Pyramid similar to the column chart, except that cylinders, cones, and pyramids are used in place of columns.

3. data series consist of data values - plotted on vertical y-axis
4. category values are plotted on horizontal x-axis.
5. Plot area is rectangular area contain graphical representation of values.
6. Data marker represent data
7. Scale is the range of values that spans an axis.
8. Tic marks are like division lines on a ruler.
9. Gridlines extend tick marks Major and minor.
10. legend identifies the format of the data marker used for each series.
11. chart title is used to describe the content of the plot area and data series.
12. chart area is the entire chart.
13. Insert Titles into chart- click titles tag in Chart Wizard, Click Chart title, type in title, tab, type in x axis title, tab, type in Y axis title.
14. Insert Gridlines - click gridlines tab in chart wizard, click Major gridlines and/or Minor Gridlines.
15. Chart Location - embedded chart is a chart that is displayed within the worksheet- chart sheet is a new sheet that is automatically inserted into the workbook.
16. The embedded chart can be moved in the sheet.
17. Pie Chart - select range of cells, click chart wizard, click Pie in the chart type list box, next, next, titles, chart title, data labels, percentage, next, as new sheet, name chart, finish.
18. Rotating a pie chart - double click the pie chart to open format data series box, double click the first slice of the pie chart, click in angle of first slice and put in the degrees desired, OK.
19.Explode a slice of a pie chart. - click the pie chart, click the pie slice you want to explode, drag the selected pie slice away from the rest, release mouse.
20. Explode the entire pie chart - click the pie chart, drag any pie slice to explode all the slices, release mouse.
21. Editing data source of a chart- select the chart, click chart on the menu bar, click source data, click series tab.
22.To add data, click add button, then select the cell range for the new data. Ok
23. To remove data- select the data series in the series list box, Remove button. Ok
24. To revise data- select data series in series list box, click the reference box. select a new cell reference, OK.
25. Changing chart options- chart, chart options, data labels, value,ok.
26. Formatting chart elements.
27. Data labels: Double click data label, You will see. Patterns, Font, Number and Alignment tabs.
Use the tab to apply the desired formatting for the data labels.
28. Iinserting Unattached text into chart- Select the chart, in formula bar type the text that you want.
29. To resize the new unattached text box click and drag selection handles.
30 To move text box, click the border, drag the text box to new location
31 To format unattached text, select text box, click the appropriate formatting buttons.
32.To Create border for text box- select text box, double click the border to open format text box dialog box. Click colors and lines, click color, choose color, click ok.
33.To Format fill color of the chart columns.- Double click column, click fill effects button,
34. One color to create a blend that uses different shades of one color
35. Two colors: blend from one color into another.
36. Preset: apply a predefined blend style.
37. Using graphic image as background- To fill plot area within a image- click chart objects list arrow on chart toolbar, click plot area, click format plot area, click fill effects, click picture, click select picture, find picture, click insert, click ok.
38. Changing axis scale - click chart objects list arrow, value axis, format axis, scale, select options desired and type in.
39. Three dimensional charts. - Chart, Chart type, select three dimensional chart, Ok
40. Several option to modify on 3d chart- Elevation (view from above or below), perspective (illusion that some parts of chart are farther away), rotation.
41. Using the drawing toolbar. Click drawing button on the standard toolbar.
42. Inserting a auto shape. click auto shapes list arrow, point to auto shape you want, click it, put cross hair pointer where you want it, then click and drag ( for proportional use shift key while dragging), release mouse button.
43. To resize, click shape, drag one of the selection handles.
44. To rotate - click green rotation handle, drag handle to rotate shape.
45. To change shape, click yellow diamond tool, drag the tool too to change shape.
46. To add a multi-pointed auto shape - Click auto shapes list arrow, starts and banners, 16 point. move pointer to desired location, hold shift key to get proportional shape and drag, release mouse.
47. Insert text into auto shape - Click border of auto shape, type the text you want. Click Format on menu bar, click auto shape, click alignment, select alignment option you want.
48. To change background color of auto shape, click the fill color list arrow and select the color you want.
49. To add drop shadow - click shadow style button on drawing toolbar, select the shadow type you want.
50. Printing a chart. Select chart or charts- Print preview, setup, chart
51. Use full page - chart is resize to fit the full page.
52. Scale to fit page. chart is resize until one of the edges reaches a page margin.
53. Custom dimensions are specified on the chart sheet using the zoom tool. OK.

Unit 9

Student Learning Outcomes:

1. Understand about object linking and embedding(OLE).
2. Embed an Excel chart in a Word document
3. Edit an embedded Excel chart in Word
4. Link an Excel worksheet to a Word document.
5. Update a linked excel worksheet
6. Test and break a link.
 

Important Points:

1. Object linking and embedding (OLE) is a way of transferring and sharing information between programs. Integration:
2. An object is anything that can be manipulated as a whole.
3. Source program is program used to create the object. Source file:
4. Destination program is the one the object will be inserted in. Destination file:
5. Embedding a copy of the object becomes part of the destination file.
6. Linking the object does not exist as a separate object in the destination file.
 

  Embedding Linking
Description Displays and stores an object in the destination file. Displays an object in the destination file along with the source files location; stores the object in the source file.
Use if you want to Include the object in the destination file, and edit the object using the source program without affecting the source file. Edit the object in the source file and have the changes appear in the destination file
Advantages The source file and the destination file can be stored separately The destination file size remain fairly small.
Disadvantages The destination files size increases to reflect the addition of the object. The source and destination files must be stored together.
 

7. Embedding an excel chart into word. Copy the excel chart, go to word, place your cursor where you want the chart in word, edit, paste special, click paste option, click microsoft office excel chart, click OK.
8. Modify embedded object in word. Double click object, use chart toolbar to modify.
9. Linking an excel chart into word. Copy the excel chart, open word, place the cursor where you want the object to appear, edit, paste special, click past link, select option you want, click ok.
 
Choose this Paste options command: To do the following:
Keep Source Formatting Paste the object without linking and match the formatting in the source file.
Match Destination Table Style Paste the object without linking and format the object in the word default table style.
Keep Text Only Paste the object as text only, not as a table or other formatted object.
Keep source Formatting and link to Excel Link the pasted object to Excel and match the formatting in the Excel source file:
Match Destination Table Style and Link to Excel Link the pasted object to excel and format the linked data in the word default table style:
Apply Style or Formatting Open the Style and formatting task pane to select styles to apply to the pasted object

10. Updating linked object - Right click table in word, point to linked worksheet object, click open link and the source file opens. click in the excel window, enter/modify data into the spreadsheet, go back to word window, right click on table, click update link on short cut menu.
11. Updating link object when word is closed. go to excel, update the data, open up the word document and you will be asked if you want to update data, click yes.
12. Breaking a link - In word window, right click linked table, point to linked worksheet object, click links, click break link, click yes.