Session 2: Tutorial 2 cont’d
Excel Teaching Notes
Excel 2003
1/6/2005
Session 1: Tutorial 1 and Tutorial 3
Managing Data and Worksheet Navigation
Formatting Cells and Worksheets
Spread sheets:
Are used to analyze and report on information.
They are used for budgeting, inventory management, and decision making.
• What-if analysis: where one or more values can be changed and then recalculated to determine the effect of the change
• Workbook: Made up of worksheets
• Worksheet: Individual sheets
• Cells: Intersection of Column and Row
• Columns: Runs top to bottom and contains the headers
• Row: Runs left to right and contains the records
• Cell Reference: The location identifier for a cell
• Active Cell: Cell currently being used
• Formula Bar: Located above the workbook window where the contents of a cell is displayed
• Ranges: Can be either adjacent or nonadjacent and is a group of cells
• Range Reference: Indicates the location and size of a cell range
• Function: Redefined formula that performs calculations using specific values call arguments
• Syntax: Is the rule specifying how the function should be written
• General Syntax rule: Where FUNCTION is the name of the Excel function and arugment1, argument2 and so forth are arguments specifying the cell reference used by the function to calculate a value: FUNCTION(argument1,[argument2=value2])
Entering Data:
• Ctrl+home takes curser to top left of worksheet
• Auto Fill: Months, Days, Quarters, Patterns
• Select styles: click and drag, click/shift/click, click/ctrl/click, black arrow for whole column or row (( ()
New Spreadsheet - Enter the Following Data:
A1: Name
B1: Address
C1: Date
D1: Item
E1: Price
F1: Quantity
G1: Total
|A |B |C |D |E |F |G |
|Alison Wilson |45 Lincoln Street |4/18/2006 |Red Grape |14 |2 | |
| |Midtown Fl 80481 | | | | | |
|Carl Ramirez |900 South Street |4/17/2006 |Navel Oranges |17 |2 | |
| |Crawford, FL | | | | | |
| |81891 | | | | | |
|David Wu |315 Oak Lane |4/16/2006 |Navel Oranges |17 |1 | |
| |Midtown FL 80422 | | | | | |
|Jerry Dawson |781 Tree Lane |4/18/2006 |Deluxe Combo |21 |4 | |
| |Midtown FL 80422 | | | | | |
Formula for G2:=E2*F2
Copy down column
Change Width of Columns:
Move pointer to dividing line between column A and B until it changes to double headed arrow and double-click
SaveAs: Dalton
Select Data:
• Click and Drag
• Click/Shift/Click
• Click/Ctrl/Click
Select A1:G5
Move to A5:G9
Select A1 Type: Dalton Food Co-op
Select A2 Type: List of Orders
Select A3 Type: April 2006
(Note that the formula typed earlier (G6) has changed to refer to the correct cells when it was moved)
Auto Sum
A10: TOTAL
F10: Click the auto Sum button on the toolbar (∑) Sum cells above
Insert Row:
Select A10: Right-click and insert row
Delete Row:
Select row 7 with ( to highlight whole row/right-click and delete row
Select E8
Change 21 to 23
Undo/Redo last action
Delete Sheet2 and Sheet3
Right-click sheet tab/delete
Insert a new worksheet
Insert/Worksheet/ Right-click to name the sheet / Documentation
Rename Sheet1: Orders
Reposition sheets by dragging so that Documentation is ahead of Orders
Insert the following information on the Documentation sheet:
|In A1: Dalton Food Co-op | |
| | |
|In A3: Date: |In B3 (enter current date) |
|In A4: Created By: |In B4: (your name) |
|In A5: Purpose |In B5: To enter orders for the Dalton Food Co-op |
Spell Check:
Switch to ‘Orders’ Sheet
Click G5
Type: Totale (spell it wrong)
Click A1 and start spell checker using the ABC spell check button from the toolbar
Spell Checker always starts at the active cell and moves down the page
Click: ‘spelling’ button on the toolbar/accept change to Total/OK
Print Preview
File/print preview
Setup will allow changes to Orientation, Margins/Centering table/Gridlines/Rows or Columns to repeat
Printing Worksheet Formulas:
Display formulas:
• Ctrl + ` (the second symbol is a grave accent and is found above the ‘tab’ key: be sure and use it, NOT the ‘ key next to enter)
• OR: Tools/Options/View/Formulas
Print Preview will show if the formulas are printing
Setup: will allow you to use the ‘Fit to’ option
If you have the ‘fit to’ option checked: page break sets will not work
Ctrl + ‘ will remove the formulas from sight (grave accent is found above the ‘tab’ key).
Session 1 (Cont’d): Tutorial 3
Formatting cells worksheets
OPEN: Sales
Apply Comma style and remove training zeros:
B7:E19
Click ‘Comma Style’ on the formatting toolbar
Click ‘Decrease Decimal’ on formatting toolbar
Apply Currency Style:
B23:D25, F23:G26
Click ‘Currency Style’ button on formatting toolbar
Apply Percent style and increase the number of decimal places:
H23:H25
Click ‘Percent Style’ button on the formatting toolbar
Click ‘Increase Decimal’ button on the formatting toolbar
Copying Formats:
B7:
Click the ‘Format Painter’ button (paintbrush) on the formatting toolbar
Drag pointer over the range: E23:E26
Format Cells Dialog Box:
B23:D25, F23:G26
Click format on the menu/Cells/Number/Currency
(If you choose ‘currency from the toolbar the $ aligns on the left side of the cell. If you choose ‘currency’ from the ‘format cells’ menu, the $ aligns on the figure directly to its left)
Change Font and Font Size:
A1: change font to ‘Times New Roman’ and size to 18
A6:E6, A22:H22 Bold
A19:A26 Italic
A1:A3 Font color to Red
Aligning Cell contents:
B6:E6, B22:H22 Center
Center titles and subtitles across the first 8 columns
A1:H3 Format/Cells/Alignment/Horizontal/Center Across Selection/OK
Indent
A7:A18, A23:A25 Increase Indent button on the formatting tool bar
Text Wrap:
A22:H22 Format/Cells/Check in Wrap text/OK
Automatically resize columns
Highlight columns use the double-headed arrow between any two column letters/ double-click
Borders:
A6:E19, A22:H26
Click ‘Borders’ on the toolbar choose all borders
Same range: choose thick Box border form ‘borders’ button on toolbar
Double border lines:
A18:E18, A25:H25
Format (on the menu)/cells/Border/Double line Bottom Border/OK
Fill/Patterns:
A6:E6, A22:H22
Click Fill Color button on formatting toolbar
Look at colors and patterns/ choose one
Formatting Worksheet:
Merge and center cell ranges containing titles
A5:E5/ click the ‘merge and center’ button on the formatting toolbar/Bold
A21:H21: same procedure as above
(Cannot choose nonadjacent cells to merge)
Hide Columns:
Select Headings for rows 7 through 18
Right-click/’Hide’
Unhide:
Select row headings fro rows 6-19/ Right-click/’Unhide’
Hide Documentation Sheet:
Click documentation sheet tab/Format/Sheet/Hide
Unhide Documentation Sheet:
Format/Sheet/’Unhide’
Formatting Sheet Tabs:
Right-click ‘Sales’ tab/tab color/ choose a color/OK
Clearing Formats:
A1:
Edit/clear/Formats
Format: Find and replace
Edit/Replace/Options>>/Format/Font/Bold/OK
Replace with: Format/Bold/Blue OK
Find next/ Replace All after you are sure that it is working correctly
AutoFormat:
A5:E19
Format/Autoformat/Choose one/OK
Change Margins and Center Worksheet on page:
Print Preview/Setup/Margins/Change left to 1”/Right to 1”/Center Horizontally/OK
Change Page Orientation:
Print Preview/Setup/Page/Landscape/OK
Header/Footer:
Print Preview/Setup/Header. Footer/Custom Header
Left Section type: Filename: click the ‘filename’ button
Right Section: click the date button/ OK
Center Section: type Page click page number
Close
Page Break Preview:
A1/View/Page Break Preview/OK
Insert page break:
Click row 20/Insert/Page Break/OK
Change view back to normal
Repeat Rows:
File/Page Setup/Sheet/Rows to repeat at top/A1:A3
End of Session 1
Session 2: Tutorial 5
Excel Lists
Pivot Table and Validation
Open Vehicle 1
Freezing Rows and Columns:
B2
Click ‘Window’ on the menu bar
Click Freeze panes
Scroll right and down: The header row and the first column stay visible
Find & Replace:
Select column F
Edit/Replace
Type Telecomm in the ‘Find’ box
Type Telecommunications in the ‘Replace’ box
Find Next/Replace
When it is determined that the search is working correctly, ‘Replace all’ can be used
‘Options’ in the dialog will allow closer defining of the search
Creating a List Range:
Select a range of cells/Highlight the Name Box/Type a ‘range name’ (NO spaces)/
Sort:
Click any cell in department Assigned column
A single cell can be selected to sort. NEVER select a whole column. Excel will sort ONLY that column. OR select the list/data/sort/ choose the fields to be sorted. Selecting a single cell and sorting from the toolbar allows for only one sort.
Maintaining a List:
Insert record using ‘data form’
Make sure the active cell is within the list
Click ‘Data’ on the menu/Form/New
ID: 2525
Year: 2005
Make: Chevrolet
Type: Sedan
Odometer: 1203
Dept Assigned: Police & Security
Price: 22400
Maintenance: 76
Close
Search using Data Form:
Data/Form/Criteria/ID# 2142/Find Next
Change the odometer to 3456
Close
Search and Delete using Data Form:
Data/Form/Criteria/ID# 678/Find Next/Delete button/OK/Close
Filter: using AutoFilter:
Make sure the active cell is within the list
Data/Filter/AutoFilter
Click ‘Make’/Ford
Custom AutoFilter:
Allows specific relationships other than ‘equal to’
Click YEAR on the filter/Custom
‘Is Greater than’/2000/And/’is less than or equal to’/2005/OK
Show all records from filtered list:
Data/filter/Show All
If you close your workbook with the filter on, the next time you open the workbook, the filter will still be on. It will appear that you have lost tons of data, remove the filter.
Conditional Formatting:
Formatting that appears in a cell when data in the cell meets conditions that are specified or is the result of a formula
E2:E73/Format/Conditional Formatting
Specify the condition: Cell Value Is/Greater than/100000/Format/Patterns/choose a color/OK
This chooses a cell color: Font color is also a choice
Add>>/ between 80,000 and 100000/choose different color/OK
Deselect range:
Calculate Summary stats on a filtered list:
Click ‘Type’ AutoFilter arrow/truck to display only trucks
Click ‘Year’ AutoFilter/custom/Select ‘is greater than or equal to’/2000/OK
Add a total row to calculate the average maintenance cost
Click the ‘Toggle Total Row’ button on the list toolbar (Shown on left side of sheet)
Select H75/click the list arrow/Average
Remove the total row for list range:
Data/Filter/Show All/Click ‘Toggle Total Row’ button/scroll down to bottom of list: Total row is removed from list range
Calculate Subtotals:
Sort list by department/Ascending
Data/List/Convert to range/yes
Data/Subtotals/At each change in ‘Department Assigned/Use function/Sum
In the Add subtotal list box, scroll and select ‘Price’ check box (Remove check from ‘Maintenance’)/Summary below data/OK
Subtotals Outline View:
Click level buttons to display different levels
Remove Subtotals:
Data/Subtotals/Remove All
Data/List/Create List/OK
Pivot Table used to create a data validation list.
A validation list is needed for the Department Assigned Column.
The Department Assigned is a data column that needs to be filled in correctly to allow the filtering and sorting to function properly.
Create a pivot table and move the Department Assigned Tag to the row location on the pivot table.
Open Vehicle.xls
• Use the Pivot table to build a validation list.
• Click Data( Pivot Table and Chart Wizard( choose Microsoft Excel List or Database(Pivot Table(Next(Select entire A1:H72 cells from Vehicle.xls(click Finish
• Drag Department Assigned group to Row Area(highlight all department Names and click copy(paste values on new worksheet(Highlight all names(click Insert(Name(Define and type TheList
• Go back to Vehicle worksheet( highlight all Department Assigned Row(click Data(Validation(check List(and then under source type =TheList(Ok
The Validation rule now applies for the data entered in the column. Typing anything other than the values in ‘TheList’ will be in error and not allowed to be entered.
Any corrections needed for ‘TheList’ may be applied to the named range.
End of Session 2
Session 3: Tutorial 4
Charts and Graphs
Charts:
Open Vega1
Chart Wizard:
Clustered Column Chart:
A5:D9
Click ‘Chart Wizard’ button/Column/Clustered Column
Hold the ‘View Sample’/Next
If the data source is not chosen or is incorrect, select the source
A data series consists of data values which are plotted on the chart’s vertical axis (y-axis). The horizontal axis(x-axis) is the data series’ category values. In this chart the data values are the sales values and the category values are the names of the different telescope models.
Click Rows:
Excel changes the orientation of the data source. The category values now represent the three regions rather than the four telescope models.
Click the ‘Series’ tab:
The Series tab lists all of the data series used in the chart and the corresponding cell references for the cell that contains the name of the data series, the cells that contain the values for the data series, and the cells that contain the category labels.
Chart Options:
Insert Titles:
Choose ‘Titles’ from the chart wizard
Chart title: Telescope Sales by Region
Category (X): Region
Value (Y): Sales ($US)
Check Gridlines tab/Major gridlines for Value (Y) to remove major gridlines
Next
Choosing Chart Location:
-Choose Embedded:
Embedded Chart is a chart that is displayed within a worksheet A Chart sheet is a new sheet that is automatically inserted into the workbook. The chart occupies the entire worksheet window and thus provides more space and details for the chart.
-If a Chart is highlighted with ‘sizing handles’ that is what will print and what you will see in ‘print preview’.
-Resizing a chart using the ‘corner’ handles will keep it in perspective. Using ‘side’ handles will pull it longer or taller. A 4-headed arrow will allow relocation of a chart.
Pie Chart:
A6:A9; E6:E9
Chart Wizard/Pie/Next
Title: Total Telescope Sales
Click ‘Data Labels’/‘Percentage’
Next/ As new sheet/Sheet Title: Pie Chart of Sales/Finish
Rotate pie chart:
Double-click the chart/Options
Double-click the value in the angle of the first slice text box/click Degrees up arrow change to 180/ OK
Explode Slice:
Double-click slice and drag outward
Modify Chart:
Edit Data source:
Switch to ‘Sales’ worksheet/select column chart
Click ‘Chart’ on the menu bar/Source Data/Series/NightVision 16/remove/OK
Switch to ‘Pie chart of Sales’/select the chart/click ‘Chart’ on the menu bar/Click Source Data/verify that the series tab is selected
Click ‘Collapse dialog box’ button select the range A6:A8
Click expand dialog box/ new chart will display
Change Chart Options:
Click ‘chart’ on the menu/Chart Options/Data Labels/Value/OK
Values are now added to chart
Format data labels:
Double-click data label $1858,000/Font/color/Yellow
Alignment/change to 90
Label position/Inside Base/OK
Right-click $2,160,000
Format data Labels/font/color/white/90 degrees/label inside base of each column
Select data labels for the NightVision 12 data series by:
Click ‘Chart Objects’ list arrow on the Chart toolbar select NightVision12 data labels
Format data/font/color/choose a color/ 90 degrees/label inside base of each column/OK
Insert Unattached Text
Select Column chart
Click in the Formula bar above the chart/ type Vega Sales from the Last Fiscal Year
Font Color/Ivory
Drag text box to the upper-right corner of plot area
Border for the text box:
Select text box/Format text box/Colors and Lines/Color/Ivory/OK
Fill color of chart columns:
Click on a bar (data series) [One click selects all bars of the same type, two clicks selects the bar clicked on]
Choose a color or fill effect/OK
End of Session 3
Session 4: Tutorial 6
Multiple Worksheets
Open LBC1
Grouping Worksheets:
With January worksheet selected
Click the ‘Last Sheet’ Button ((| ) //December
Click the ‘First Sheet’ Button (|() to return to first sheet without loosing selection
Enter same information in all the sheets in the workgroup:
B12: =SUM(B8:B11) calculates the total cash inflow amt.
B19: =SUM(B14:B18) calculates total cash outflow amt.
B20: =B12-B19 calculates net change in cash
B22: =B21+B20 calculates the ending cash balance
Check each sheet to review formulas
Ungroup Worksheets:
Right-click January tab/Ungroup Sheets
Referencing Cells and Ranges in other Worksheets:
Insert cell reference to the January worksheet in the February worksheet:
February tab/B21: = (click the January tab) click B22
3-D Cell Reference from multiple worksheets:
Add worksheet to the left of January named Annual
Copy values and formats from the January worksheet across the worksheet group:
January tab/ & hold/Annual tab to select Jan and Annual sheets
In Jan worksheet: A1:B22: Edit/Fill/Across Worksheets/All/OK
Ungroup worksheets: he formats and data have been copied
Enter formula that includes the 3-D reference:
Annual tab: B:8 =SUM (Click the Jan. tab/‘Last Sheet’ Button ((| ) //December
In the December worksheet click B:8
The formula should read: =SUM (January-December!B:8)
Use the fill handle to copy the formula to B:11
Annual tab: B5 type All Months
End of Session 4
Session 5: Tutorial 2
Formula and Functions
Validation Rule
Open: Budget1
Function: redefined formula that performs calculations using specific values call arguments
Calculate totals using the SUM function (∑)
Click B8
Type the formula (Formulas ALWAYS begin with =)
=SUM(B6:B7)
: indicates that the first and last cell and everything in between are used in the calculation
, indicates that only the cells listed are included in the calculation
Click B22 and type the formula
=SUM then use the mouse to select the range B11:B21
Calculate the net income for the first month:
Click A24 and type Net Income
In B4 enter the formula:
=B8-B22
Formulas can be copied and pasted or filled using the + handle
Copy the formula from B8 to the range: C8:M8
Copy the formula from B22:B24 to C22:M24
‘ESC’ removes the marching ants from the selected range
Calculate total using Absolute reference cell
Click A25 type: Running Total
C25: type the formula:
=SUM(B24:C24)
Change B24 to an absolute reference ($B$24) This can be done by positioning the curser in front of B24 and pressing the F4 key. This will cause the B24 reference to remain constant while the second reference remains referential as the formula is copied down the column.
Paste Special determines what is pasted into a cell:
• Edit->Paste Special
Zoom feature:
• determines what you see: It does NOT change the print size
• Change the 100% on the toolbar to another setting
Copy the Income and Expense categories:
A27: YEAR-END SUMMARY
Copy: A5:A24 and Paste it into A29:A48
B28: Total
B30: Formula:
=SUM(B6:M6)
B30: copy into: B31:B32, B35:B46 , B48/ Paste
Functions: Average / Minimum / Maximum
C28: type Average
C30: ‘Insert Function’ button from tool bar/Statistical/Average/OK
B6:M6 are the cells to average/OK
C30: copy
Select (using Ctrl) C31:C32, C35:C36, C46:C48/ Paste
Fill Handle
Calculate the year-end minimum and maximum amounts
D28: Minimum
E28: Maximum
D30: formula
=Min(B6:M6)
E30: formula
=Max(B6:M6)
(The amount 2,400 will appear in both cells)
Select the range: D30:E30
Use the fill handle (+) in the lower-right corner to fill from D30:E48
Select: D33:E34, D47:E47 and delete the contents
Using the delete key will delete the contents: Right-clicking and choosing delete will delete the whole cell causing the sheet to change shape by moving cells into the vacant space
Use AutoFill for months of the year:
Ctrl+home
B4: type Jan.
Use the fill handle (+) and drag from B4:M4 The computer will add the months in abbreviation across the selection as you drag
Date Functions:
Switch to the Documentation sheet
B4: type formula
=TODAY()
Today’s date will display
Insert a New Worksheet within ‘Budget’ and name it Loan Analysis
SaveAs: Budget3
A1: Loan Analysis
Copy the labels and the average values from the ‘Budget’ worksheet (A3:B22)
Switch to ‘Budget’ worksheet and select: A29:A48, C29:C48/Copy
Switch to ‘Loan analysis’ worksheet
A3: ‘Paste only the values’ (Edit/Paste Special)
Modify the layout and replace some of the values with formulas:
Remove highlighting from pasted range
B6: AutoSum (B4:B5) To calculate the total average monthly income
B20: AutoSum (B9:B19) To calculate to total average monthly expenses
B22: formula to calculate the average monthly net income
=B6-B20
Enter the conditions of the loan
D3: Loan Conditions
D4: Loan Amount
E4: -175,000
D5: Length of Loan
E5: 30
D6: Annual Interest Rate
E6: 5.5%
D8: Payment Conditions
D9: Payments per Year
E9: 12 (for the number of payments per year)
D10: Total Payments
E10: =E5*E9
D11: Payment Amount
Payment Function:
PMT functions will calculate the monthly payment required to pay off a loan. The syntax of PMT: =PMT (rate, nper, pv)
E11: ‘Insert Function’ (the pull-down arrow beside the sum button [∑] on the tool bar)
Choose PMT
Rate: E6/12 (E6 is the annual rate; divide by 12 to get the monthly rate; [divide by the amount of payments per year; it would be 6 if you paid bimonthly])
Nper (number of payments): E10 (Use E10 because you have already multiplied the years by months in the sheet: If that is not done you would use the years cell * 12)
Pv: (present value): E4
OK
A9: House Payment
B9: formula to make the value of House Payment equal to the value Payment Amount
=E11 (this recalculates the average total monthly expenses)
What-if analysis
E4: -250,000
The calculations change
Undo change
E6: 6.5%
Calculations change
Undo Change
Save
Calculate family’s yearly net income:
D13: Is the loan affordable
D14: required Savings
E14: 5,000
D15: Calculated Savings
E15: formula to calculate how much the family saved in one year using the average monthly net income multiplied by 12 months
=B22*12 (approximately 4,672)
Logical Functions:
A function that tests or evaluates whether a condition in the workbook is true or false
IF function:
Gives you one evaluation if true and another if the value is false
D16: Conclusion
E16: formula to determine if the loan is affordable
=IF(E14 ................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.