MS Excel Intermediate

1
MS Excel Intermediate
Adult Continuing Education
2
HI!

• Instructor
• How to contact me
• by phone at
• by e-mail at

3
Attendance
4
MATC General Info

• Grading (Satisfactory or Unsatisfactory)
• Evaluations (Youll get your chance)
• Restrooms
• No food or drink in the lab
• Emergencies
• Tornadoes Interior hallway
• Fire Exits

5
How Did You Get Here?
6
Course Info

• Disks
• Required 3.5 diskette
• Must be NEW--never used in any computer (prevent
  viruses)
• Should be DS /HD (double sided, high density)
• Formatted or unformatted, doesnt matter

7
Course Info

• Texts
• Handouts packet required
• Available from MATC Downtown Bookstore
• Call 258-2417 for hours and pickup options
• Talk Watch Do Practice

8
Course Plan

• This is an intermediate course and assumes some
  knowledge and/or experience with Excel
• Review of Basics
• Creating Charts
• Managing Data and Macros
• Linking, Summarizing, and Consolidating
  Worksheets
• Hyperlinks

9
Tell Me About Yourself

• Why are you taking this course?
• What do you hope to learn?
• What do use Excel for?

10
Review of Excel Basics

• Building a Worksheet
• Formatting Worksheets
• Managing Workbooks
• Using Formulas and Functions

PRACTICE Review Exercises
11
REVIEW EXERCISE

• International Megacorporation produces insect
  repellant. Its flagship product is Blood Sucker
  Be Gone. 1997 annual sales figures for this
  product are given in the file BSBG.xls. Open
  this file and insert a copy of the 1997 sales
  figures into your Mosquito workbook.

12
REVIEW EXERCISE

• Modify the BSBG Sales 97 tab
• so column headings appear bolded
• so sales figures are in currency format
• so an annual regional total is reflected (Annual
  Total should be bolded)
• so a monthly total is reflected (Monthly Total
  should be bolded) NOTE Annual monthly total is a
  meaningless value so do NOT compute it shade
  that cell.
• Below the table, compute the maximum, minimum,
  and average annual sales values

13
REVIEW EXERCISE

• International Megacorporation management monthly
  recognizes each region selling more than 5,000
  of BSBG per month. Compute the number of times
  each region has been recognized in 1997.
• International Megacorporation President, Dr. Ann
  Ty Bug, bestows the coveted governors award to
  those regions whose annual sales are in excess of
  60,000. Use a following row to print
  Governors Award if the region is eligible to
  receive such an honor.

14
Creating Charts and Maps

• Create Separate and Embedded Charts
• Move and Resize an Embedded Chart
• Create Different Types of Charts
• Enhance and Format Charts
• Print a Chart
• Create and Modify a Map

15
Elements of Chart Structure
Z-axis
Gridline
Data marker
Y-axis
Tick mark
X-axis
Legend Box
Legend
16
Procedure for Creating a Chart

• 1) Decide what is the best plot type for your
  data
• 2) Select the worksheet data to plot
• 3) Use the Chart Wizard to create your chart
• 4) Modify or customize the chart

17
Creating a ChartStep 1 Select Chart Type

• Different charts for different types of data
• You must know your data to select the most
  appropriate type of chart
• Can have 2D and 3D chart
• Lets take a look at some of the chart types
  available

18
Line Chart

• Consisting of lines, usually for illustrating
  trends over time

19
Bar or Column Chart

• Compares different sets of data, compare values
  of a series of items at a particular point in time

20
Pie Chart

• Illustrates contribution of various parts to the
  whole by representing the whole as circle and the
  parts as wedges

21
Doughnut Chart

• Similar to a pie chart, but charts multiple
  categories in separate rings of the pie chart,
  showing value of elements to whole

22
Area Chart

• Emphasize the relative value of each elements
  contribution to the whole over a period of time

23
Creating a ChartStep 2 Select the Data to Plot

• Adjacent data
• data in rows or columns that are next to each
  other
• click and drag over range to select
• be sure to include labels for axes

24
Creating a ChartStep 2 Select the Data to Plot

• Non-adjacent data
• data in rows or columns that are NOT next to each
  other
• select part of range
• hold down Control key and drag over next part of
  range
• release Control key and mouse button
• be sure to include labels for axes

25
Creating a ChartStep 3 Activate the Chart Wizard

• Chart button on the toolbar
• OR
• Insert ? Chart
• Leads you through series of dialog boxes to
  create a chart

26
Chart Wizard--Step 1

• Chart Type
• Click chart type in left box
• Click chart sub-type in right box
• Note button to view sample

Click Next button to go on to step 2
27
Chart Wizard--Step 1

• Custom Types
• Regular charts with special formatting
• Examples
• different backgrounds
• chart color schemes
• Do not have to hold a button to see a preview of
  your chart

Click Next button to go on to step 2
28
Chart Wizard--Step 2

• Chart Source Data
• Chance to select data if you have not already
  done so
• Choose whether data in rows or columns (selection
  changes how data is plotted)

29
Chart Wizard--Step 2

• Specify names of data series (values of tick
  marks)
• Excel will do this for you if you include
  headings in your initial selection of data

Click Next button to go on to step 3
30
Chart Wizard--Step 3

• Chart Options--further specify chart appearance
• Title Tab
• Type in chart title
• Type in axes titles (should include name and unit
  of measurement)

31
Chart Wizard--Step 3

• Axes Tab
• Check if X axis categories to be displayed
• Automatic allows Excel to choose how the axis are
  -labeled
• Category uses the info from the data selection
• Time-scale shows in date/time format
• Check if Y axis scale to be displayed

32
Chart Wizard--Step 3

• Gridlines Tab
• Active X and Y axes major and minor gridlines
• Minor gridlines are lines at values included in
  the data between the major tick marks
• Example
• February would be a minor gridline

33
Chart Wizard--Step 3

• Legend Tab
• Select whether to display legend
• Select location of legend in chart

34
Chart Wizard--Step 3

• Data Labels Tab
• Select whether to show data labels and what type
  of label to display
• Value data value
• Label data name
• Select whether to show legend key next to data
  label
• Choices will preview in chart area

35
Chart Wizard--Step 3

• Data Table Tab
• Select whether to show data table
• Select whether to show legend key in data table

Example of data table with legend key activated
Click Next button to go on to step 4
36
Chart Wizard--Step 4

• Chart Location
• As new sheet creates separate chart (appears as
  separate sheet in workbook)
• As object in creates embedded chart (appears on
  worksheet)

Type name of chart for workbook tab
Use list box to select worksheet where chart will
be object
37
Chart Wizard--Final Step

• After specifying all choices click Finish to
  complete the chart
• NOTE clicking Finish at any time (step 1, 2, 3,
  or 4) will accept all defaults and complete the
  chart

38
PRACTICE

• Plot the BSBG sales data. Make an appropriate
  chart of
• monthly sales for international regions
• annual sales by region
• monthly sales for summer months (June-August)
• annual sales figures for the four domestic
  regions and Canada

39
Using a Chart

• When you point to a series on a chart, box will
  come up giving data related to that series such
  as
• value
• percent
• series name

40
Creating a ChartStep 4 Modifying a Chart

• Moving and Resizing a Chart
• Changing Chart Data
• Changing between Embedded and Separate Chart
• Changing Chart Elements
• Change Chart Type
• Adding/Deleting Data Series
• Changing Colors/Effects

41
Moving a Chart

• Can only move an embedded chart
• Click on chart--to get four-sided arrow
• Drag chart to new location
• Click on background to unselect chart

42
Resizing a Chart

• Only works with embedded chart
• Click on chart--get sizing handles (boxes in
  corners and side of chart)
• Click on sizing handle and drag larger or smaller
• SIDE HANDLES --resize chart non-proportionately
  (like Silly Putty)
• CORNER HANDLES --resize chart proportionately
  (gets taller and wider at same time)

43
Changing the Data for a Chart

• Charts automatically change as the data that
  produces them is changed
• True for embedded or separate charts
• Adding/deleting data
• If add/delete data within range that produces
  chart, chart is changed automatically
• If add/delete data outside (beginning or end)
  range that produces chart, must redefine data
  range to update chart

44
Embedded to Separate Chart

• To move chart from an embedded chart to a
  separate chart (or visa versa)
• Right-click on chart area
• Choose Location from pop-up menu
• Select choice to change chart location

45
PRACTICE

• Change annual sales by region chart to a separate
  chart
• Change the monthly sales for summer months
  (June-August) to a separate chart
• Change the latter back to an embedded chart

46
Changing the Chart Type

• Right-click in area of chart with nothing in it
  (i.e. not on title, legend, etc.)
• Choose Chart Type from pop-up menu
• Select desired chart type as in step 1 of Chart
  Wizard

47
Working with Data Series

• Data series is group of plots on a chart
  representing certain set of data
• Example
• Red data series represents East data
• Blue data series represents West data

48
Deleting Data Series from Chart

• Click chart to activate editor
• Click on one of the data series
• all in that series become selected
• note handles on data series
• Hit Delete key to remove series

49
Adding Data Series to Chart

• Right-click in chart area
• Choose Source Data from pop-up
• Specify source data like in step 2 of chart
  wizard

50
PRACTICE

• Working with the chart for monthly sales for
  overseas region
• delete the overseas data series
• add the data series for each domestic region for
  the first half of the year
• resize the chart to a size large enough to
  display each month

51
Changing Colors

• Can change color of
• data series
• chart background
• plot background
• walls and floor color (if 3D)

52
Changing Colors Data Series

• Click series to select
• Right-click on selection
• Choose Format Data Series from pop-up menu
• Click to Patterns tab and specify choices
• Click OK when done

Lets take a closer look at the Patterns tab
53
Patterns Tab

• Format border
• Turn border on/off
• Select border style (use list box)
• Select border color (use list box)
• Select border width (use list box)
• Select colors
• Fill Effects for additional options

Note sample of formatting
54
Fill Effects Dialog Box

• Offers many additional choices for colors and
  patterns
• Has four tabs to specify fill effect
• Only ONE fill effect will apply (although you may
  specify several, only your last selection will
  apply)

55
Fill Effects Dialog BoxGradient Tab

• Select colors to include in gradient (check out
  Presets)
• Select shading styles
• Note sample

56
Fill Effects Dialog BoxTexture Tab

• Select texture from list provided
• Click Other Texture and specify location of other
  picture file to use as texture

57
Fill Effects Dialog BoxPattern Tab

• Click to choose fill pattern to use
• Change foreground color
• Change background color

58
Fill Effects Dialog BoxPicture Tab

• Click Select Picture
• Specify picture to use
• Use Format area to choose how to apply picture

59
Changing Colors

• Can use same technique to change colors of other
  elements of chart
• chart background
• plot background
• legend background
• chart titles
• Right-click on item
• Choose Format (whatever) from pop-up

60
Changing Chart Elements

• Standard techniques can be used to change various
  elements of the chart
• Basic procedure is to right-click on item
• Choose Format (item) from pop-up menu
• Click to tab to format and make choices
• Click OK to complete

61
PRACTICE

• Working with the chart for monthly sales for
  overseas region
• apply a solid color to the northern data series
• apply a gradient color to the southern data
  series
• apply a texture to the eastern data series
• apply a picture to the chart wall

62
Some Examples

• Rotating chart elements--use alignment tab and
  drag red arrow to rotate text (or type value)
• Font elements--use font tab
• Number format--use number tab (currency, number
  of decimal points, etc.)

63
Moving/Resizing Chart Elements

• Click on element to move/resize
• Move by dragging to new location
• Resize by dragging handles
• Examples
• legend
• plot area
• chart title

64
Further Customizing Chart

• Many other options exist for customizing chart
• Much customizing can be eliminated by going
  through all the tabs in the Chart Wizard and
  specifying choices
• To access customizing options, right-click on
  item and choose from pop-up menu

65
PRACTICE

• Rotate the text 90-degrees for the data series
  axis (y axis) (appropriate rotation is so that
  bottom of letters are along the chart)
• Move the legend to the bottom of the chart
• Add data labels to the Canada data series

66
Adding a Map

• Useful for graphically displaying information
  that is defined by state, country, province
• Visualize worksheet information graphically
• Must have Microsoft Map installed to use
• NOTE MS Map does NOT install with standard
  install, must do custom to get it

67
Making a Map

• One column must contain the geographical
  information (state, country, province)
• One column must contain the values relevant to
  that region
• Click and drag over selection
• Click Map button on toolbar
• OR
• Insert ? Map

68
Making a Map Continued

• Drag in worksheet to define an area to draw the
  map
• Excel will give a list of maps to choose from
  based on the regional data
• (e.g. US with states if states, Europe if
  European countries, etc.)
• Excel will create the map

69
Typical MS Map
70
PRACTICE

• Create a map based on the following
• Wisconsin 234
• Minnesota 456
• Iowa 478
• Illinois 678
• Indiana 345
• Michigan 489
• North Dakota1106
• South Dakota 577

71
Editing a Map

• Map must have heavy, shaded border to be able to
  edit (i.e. must be in map editor)
• Map Toolbar will appear

72
Resizing Moving a Map

• To resize a map
• Make sure the map editor is not activated (if so,
  click back to the worksheet to de-activate)
• Click on map once to get handles
• Click and drag handle to resize
• To move a map
• With map handles showing, click and drag to new
  location

73
Changing Map Data

• To change map data
• Change data on worksheet
• Double-click to activate map editor
• Click refresh map button on toolbar

74
Add Data to Map

• To add data to an existing map
• In worksheet, insert row within range used to
  make map
• Enter data in new row
• Double-click to activate map editor
• Click refresh map button on toolbar
• NOTE can NOT add data to end of the range, must
  insert in range

75
PRACTICE

• On the previous map
• change the Wisconsin value to 1234
• change the Minnesota value to 987
• add Kansas 678
• add Missouri 987

76
Working with Map Legend

• Format the Legend
• Activate map editor (double-click if not on)
• Right-click on legend
• Hide legend (takes off map)
• To get legend back View ? All Legends

77
Editing Map Legend

• Edit Legend
• Legend Options Tab

Specify fonts for title and subtitle
Edit how ranges specified in legend
78
Editing Map Legend

• Value Shading Options
• Enter number of ranges
• Enter color--only one color specified, various
  ranges are shades of that color
• Select how value ranges are dispersed
• Select function to be used to summarize info
• Also accessible from Map ? Value Shading Options

79
PRACTICE

• Add a legend to the map
• Change to color selection of the ranges
• Choose an equal spread for data in the legend
  options

80
Other Map Features

• Can add features such as cities, airports, roads,
  etc.
• Map ? Add Features ? Select features to add from
  dialog box
• Can add map pins and labels
• Click map pin button
• Type label associated with pin

81
Other Map Features

• Can add map tags for region (state, country,
  province name)
• Click map tag button
• Click on region (state, country, province, etc.)
  to label
• Can add generic text
• Click text button
• Click in map where text is to appear
• Type text

82
Map Control

• Activate/Deactivate map control using
  button on map toolbar
• Map control to specify how objects appear on the
  map

Items that can be plotted on map
83
More on Map Control

• Sets color scheme to be shades of specified color
• Sets color scheme to be different colors
• Places dots in map with density representing
  values
• Places symbol in map whose size represents value

84
More on Map Control

• Creates a pie chart of the data
• Creates a bar chart of the data

85
Using Map Controls

• Click and drag control to window
• Click and drag data element to window
• Control executes and shows on map
• Drag control off window to remove item from map

86
NEXT TIME

• Excel as a Database
• What is a database?
• Creating a database
• Using a database

Dont miss it !!
87
Managing Data

• Understand Databases
• Build a Database
• View Database Records Using the Data Form
• Add New Database Records
• Delete Records
• Sort Records
• Use the Data Form to Search for Records
• Find Records in a Database Using AutoFilter

88
Excel as a Database

• Database collection of related information
  about a subject organized in a useful manner
• Parts of a database
• record row of cells containing fields of
  related information
• field a column containing one type of
  information

89
Database Example
Field Name
Field
Record
90
Defining a Database

• Define by giving name to range of cells database
  occupies
• select range of database
• under Insert menu choose Name ? Define
• type name of database
• now accessible using name box on formula bar

91
Data Form

• Often database extends more than one page in
  width
• Scrolling can be cumbersome
• Activate Data Form
• brings up form for entering data based on fields
• easier than scrolling across page
• must be in data table
• to activate Data ? Form

92
Example Data Form
93
Data Form

• Deleting a record using the data form is
  PERMANENT
• can NOT undelete or recover a record deleted from
  data form
• Add new records by pressing New button

94
PRACTICE

• BSBG.xls also contains an employee database for
  part of International Megacorporation. Include
  this sheet in your mosquito workbook. Use that
  data form to insert the following new employees
• Dr. Ima Killer, Research Scientist grade 11,
  Northern Region, 123 Crestwood Circle, Bug
  Country, MN 56789, hire date 3/14/98
• Phil Bugbomb, Entomologist grade 15, 44566 Little
  Lane, Bangkok, Thailand 567890TAI, hire date
  11/20/98
• Heh McKenzie, Sales Representative grade 9, 567
  International Way, Quebec, Canada ROC10H0, hire
  date 6/6/98

95
Sorting

• Must be out of data form
• Go to cell in range you want to sort by
• Click sort ascending/descending button on toolbar
• Records are sorted in that order based on the
  field you are in

96
Sorting Continued

• Multiple sort levels
• (sort by field 1, then by field 2, then by field
  3)
• go to cell in range you want to sort
• Data menu ? Sort
• specify first field to sort by and
  ascending/descending
• specify second field (if any) to sort by and
  ascending/descending
• specify third field (if any) to sort by and
  ascending/descending

97
Sort Options

• Click Options button on Sort dialog box to access
  sort options
• Case sensitivity
• Sorting top to bottom or left to right

98
Multiple Sort Levels

• Note that the header row (titles) are not part of
  the sort
• Note that the data within a record is kept
  together (does not sort just one column
  independent of other fields in that record)
• Can indicate if no header row (no titles)

99
Searching for Records

• May want to search database to find all records
  matching certain criterion
• Using the Data Form
• click Criteria button
• enter the criterion in the appropriate field
• select Find Next or Find Previous
• Restore cancels any changes made to a record

100
Criteria Operators

• gt greater than
• lt less than
• gt greater than or equal to
• lt less than or equal to
• ltgt not equal to

101
Finding Specific Records

• Note that (equal to) was not part of the
  criteria operators
• To find data equal to a specific criterion
• click Criteria button
• type specific data in appropriate field
• select Find Next or Find Previous

102
Multiple Criteria

• Can enter different criteria in as many fields as
  you wish
• Excel will find records that match ALL criteria
  entered

103
Finding Records in the Datasheet

• Close the Data Form
• Data? Filter
• AutoFilter
• simple criteria
• Advanced Filter
• complex criteria
• beyond scope of course

104
AutoFilter

• Excel will examine data in fields and will create
  a filter to allow you to show only specified data
• Tells you it has done this by putting little
  arrows in field names
• Arrows bring list boxes for you to specify filter
  criterion

105
AutoFilter Choices

• Will ALWAYS see at least 6 choices
• All--shows all records
• Top 10--filter specified amount of numerical data
  by percent or number of items
• Custom--specify multiple selection conditions in
  an AND or OR relationship

106
AutoFilter Choices Continued

• Exact values--will show the various unique
  value(s) your field contains
• Blanks--shows all records without an entry
• NonBlanks--shows all records with an entry
• When choose certain criterion from this list,
  only those records matching that criterion are
  shown (others are hidden, NOT deleted)

107
More on the AutoFilter

• When selection criteria exist for filter, filter
  arrow turns blue
• To remove filter, select All from the filter list
• To display all records, Data ? Filter ? Show All

108
Filtering Filters

• Can successively use filters to filter filtered
  data
• i.e. can filter by one field then filter that
  result by another, different field
• So be Careful !!

109
Filtering Using Custom Filter

• NOTE this is Data?Filter?AutoFilter?Custom NOT
  Data?Filter?Advanced Filter
• click on filter arrow
• choose custom from list, get dialog box

110
Custom Filter Dialog Box

• Enter operator and select field in top portion
• Choose AND or OR
• Enter operator and select field in bottom portion

111
Custom Filter

• AND will show only those records matching BOTH
  criteria
• OR will show only those records matching either
  one or both criteria
• Removing all old filtering conditions before
  setting new is always good idea. Otherwise, may
  be filtering filter

112
PRACTICE

• Starting each time from NO sorting, make a staff
  directory sorted by
• last name then grade within each region
• hire date within each region
• seniority within each grade
• all those above grade 12
• all those above grade 12 in the U.S.
• all those in the overseas offices
• all those in the Northern region
• 10 employees with the company the longest

113
NEXT TIME

• Automation Using Macros
• What is a macro?
• Creating a macro
• Running a macro
• Links
• What is a link?
• Creating links
• Changing links

Dont miss it !!
114
Using Macros to Automate Repetitive Tasks

• What is a macro?
• Creating a macro
• Running a macro
• Creating a button to run a macro

115
What is a Macro?

• Stored list of commands and keystrokes that are
  automatically executed
• Duplicate tasks you find yourself performing
  repeatedly
• Examples
• changing fonts
• entering the same data in several locations
• changing page setup commands

116
Macro vs Procedure

• Procedure series of Visual Basic statements
  that are created by using the Macro Recorder and
  stored together
• Basically procedure macro in programming
  language

117
Creating a Macro

• 1) Plan the steps to include and think of a macro
  name
• 2) Turn on the Macro Recorder
• 3) Perform the steps
• 4) Turn off the Macro Recorder

118
Planning a Macro

• Before starting macro, plan exactly what you want
  to do and the order in which you want to take the
  actions
• Run through the steps before activating the Macro
  Recorder to make sure you get everything
• Best to record a macro in one session to avoid
  mistakes

119
Naming a Macro

• No spaces or periods in name
• If need more than one word, use underscore (_)
  character
• Name should reflect the actions the macro performs

120
One More Item Before you start

• Prepare the worksheet by doing everything you do
  not want as part of the macro
• open a new worksheet
• scroll to specific location
• etc.
• Once you start recording, all of your actions,
  every keystroke, command, mouse click, etc. will
  be recorded
• Eliminate the fluff up front

121
Activating the Macro Recorder

• Turn on the Macro Recorder
• Tools / Macro / Record New Macro
• Dialog box
• give macro a name (should be descriptive of
  actions)
• specify keyboard shortcut (if any)
• CTRL any character
• CTRL SHFT any character
• CAREFUL dont use those already assigned (CTRL
  s)
• brief description--so you will remember what the
  macro does

122
Creating a Macro

• After starting the Macro Recorder, everything you
  do is recorded
• Work SLOWLY and CAREFULLY, the macro will record
  you mistakes too!
• When done, click the Stop Recording Macro button
  in the pop-up window

123
Viewing a Macro

• Can see the macro in the Visual Basic procedure
• Tools / Macro / Macro
• select macro from list
• click Edit to view procedure
• You do not have to view the macro (or understand
  the programming language) to use it

124
Running a Macro
Save NOW !!

• Before you run a macro, save your work
• could have error in macro that erases part of
  work or worksheet
• Best to move to unused worksheet in workbook

125
Running a Macro Continued

• Tools / Macro / Macros...
• Select macro from list
• Click Run
• Macro will now run through the series of
  keystrokes and commands you performed
• (including any mistakes)

126
PRACTICE

• Create a macro that does the following
• Adds all cell borders to all cells in the
  worksheet
• Bolds the first row
• Changes the text color for rows 2-5 to red
• Fills the first row with a red color and the
  first column with a blue
• Puts your name and page number in a footer
• Call this practice_macro

127
Problems?

• Visual Basic programming is beyond the scope of
  this class
• Macro may require you to select a cell before you
  run the macro
• good idea to active a cell as the first step in
  macro
• make sure this cell is active before start macro

128
Creating a Button to Run a Macro

• Can create a button in worksheet to run macro
• Activate the forms toolbar
• View / Toolbars / Forms
• Click button tool
• Click and drag over location in worksheet where
  you want the button to appear

129
Creating Macro Button Continued

• When release mouse button, dialog box appears
  asking to assign macro to button
• Select appropriate macro for button assignment
• Rename the button from default
• double-click inside button and replace text
• click outside button when done
• Now click button to run macro

130
Creating a Macro Toolbar Button

• Create macro using Macro Recorder
• View / Toolbars / Customize
• Go to Commands tab
• select macros
• add icon or name (from selection given)
• click and drag icon or name from right frame of
  dialog box to the desired toolbar
• pointer will become heavy I-beam
• drop to insert

131
Creating a Macro Toolbar Button Continued

• click on Modify Selection
• click on Assign Macro
• select macro from the list and click
• click on Name box and retype name of button
• click on Change Button Icon to select different
  icon
• Click Close when done
• Button on toolbar will now run macro

132
Toolbar Button for Macro

• Macro is now part of all subsequent workbooks
  opened
• It has become a command you have added to the
  toolbars

133
Deleting a Macro

• Macros only valid for open workbook
• Macros saved when workbook is saved
• To delete a macro
• Tools / Macro / Macros
• click on macro to delete
• click delete button
• its gone

134
Deleting a Macro Button

• Button in worksheet
• right-click on button
• click on border (left-click)
• hit delete
• Button on toolbar
• hold down ALT key
• click on button
• drag from toolbar

135
PRACTICE

• Place a button for your macro on your standard
  toolbar so it appears as the final item
• Change the image of the macro button
• Execute your macro using that button
• Remove the button from the toolbar and delete
  your macro

136
Linking Worksheets

• Links
• definition
• creation
• changing
• removing

137
Links between Worksheets

• Link one-way data connection from the source
  workbook/worksheet to the dependent
  workbook/worksheet
• Source workbook/worksheet
• supplies data over a link to a dependent
  workbook/worksheet
• Dependent workbook/worksheet
• contains an external reference (a link) to
  another (source) workbook/worksheet and therefore
  depends on the other for data

138
More on Links

• Pipelines for data
• links carry data (in one direction) from the
  source (or supporting) workbook/worksheet to the
  dependent (or target) workbook/worksheet
• can be created between other Windows applications
  (e.g. MS PowerPoint)
• Automatically updates dependent data when source
  data changes

139
Creating a Link

• Select the source data and copy
• Move to the location of the dependent data
• Edit ? Paste Special ? Paste Link

140
External Reference

• Note appearance of cell reference for pasted link
• workbook name.xlsworksheet name!absolute cell
  reference
• Examples
• Profits.xls1997!A5
• 1998!G6
• Sheet1!H4--note no quote marks

141
Saving Linked Workbooks

• Link depends on file names
• Links do NOT work if file names are changed
• Save source workbooks first--will see if links
  work in opened dependent workbooks
• Links within worksheets will update automatically
  as sheet names are changed

142
Change and Restore Links

• If you
• ...move the source workbook to another directory,
• ...rename it,
• ...or delete it,
• the links to the data will be broken

Deleted source
Moved source
dependent
Renamed source
143
Change Links

• To update a link between workbooks
• Open dependent workbook
• Edit ? Links
• Select link to update or change
• Click Change Source button
• Find new link location
• Click ok when done

144
Removing a Link

• Select dependent range
• Copy
• Edit ? Paste Special ? Values
• Will paste the values of the cells in the range
  and remove the link referenced

145
Link Data to Another Windows Application

• Useful because does not require cutting and
  pasting of data each time updated
• Will automatically update to Windows application
  just like Excel link
• Has same naming/re-naming, moving, and deleting
  constraints as link

146
Link Excel to Word Processor

• Select range of cells to link
• Copy
• Go to other application
• toggle using ALT tab
• use task bar
• Move cursor to destination location
• Edit / Paste Special
• check Paste Link

147
Links to Other Windows Apps

• Works for data (tables)
• Works for graphs (and graphs are already linked
  to their source data)

148
What is a Hyperlink

• Underlined or otherwise emphasized text or
  graphic that, when clicked with the mouse,
  displays another document
• Behaves somewhat like links on Web pages

149
Examples of Hyperlink Use

• Switch between Excel worksheets and Word
  documents
• Excel expense statement to Web site on Internet
  containing currency exchange rates
• Linked Excel worksheets that compose and
  executive information system

150
Creating Hyperlinks

• 1) enter or copy text in a cell or range and make
  the text a hyperlink
• 2) insert a graphic and make the graphic a
  hyperlink
• 3) create custom hyperlinks using a worksheet
  function

151
Creating Hyperlinks Continued

• Open files you are going to link to
• Go to file where you want the links to appear
• Click hyperlink icon (globe chain) on standard
  toolbar
• Get Insert Hyperlink dialog box

152
To link to a file or URL (Web Site)

• In top part of Insert Hyperlink dialog box, click
  browse button
• Select the location of the file or URL, click OK
• Hyperlink is inserted into your document
• blue underlined text
• when click
• other file or URL is opened
• text changes color

153
Web Toolbar

• Once hyperlink is created, can use web toolbar
  (globe with arrows) to navigate between links
• Address list box
• History list box

154
Selecting a Hyperlink

• 1) click a cell next to the cell that contains
  the hyperlink and then use the arrow keys to move
  onto the cell that contains the hyperlink
• 2) click the cell that contains the hyperlink and
  hold the mouse button down for at least one
  second (then release)
• 3) right-click the hyperlink, then use the pop-up
  menu displayed

155
Changing a Hyperlink

• After selected hyperlink can
• edit text
• delete link

156
PRACTICE

• Create a hyperlink to the MATC homepage
  www.madison.tec.wi.us
• Insert the BSBG logo from your disk
• Create a hyperlink to the BSBG Employees sheet
  using the inserted image
• Can you edit the hyperlink to make the text
  displayed upon mouse point to be BSBG
  Employees?

157
NEXT TIME

• Summarizing, Consolidating,
• and Analyzing Data
• Subtotals
• Consolidation Tables
• Scenarios

Dont miss it !!
158
Summarizing, Consolidating, and Analyzing Data

• Subtotals
• Inserting and removing
• Consolidation Tables
• Definition
• Creation
• Scenarios
• Creation
• Using

159
Summary Reports with Subtotals

• Summary report list in which data is broken
  into groups and summarized by group subtotals
  with an overall total given at the bottom of the
  list
• IMPORTANT
• First step is to make sure the list is sorted so
  that like data is grouped together

160
Subtotal Summary Report

• Click any cell in range to subtotal
• Data / Subtotals
• specify which field represents a group (at each
  change in)
• specify function to create subtotal (use
  function)
• specify location to put the subtotals, i.e.
  column to apply function to (add subtotal to)
• select Summary below data

161
PRACTICE

• Open BSBG Offices from your floppy
• Use subtotals to compute
• Total sales per region
• Total number of employees per region
• Average sales per region

162
Removing Subtotals

• Click any cell in range with subtotals
• Data / Subtotals / Remove All

163
Consolidation Tables

• Useful to make report that draws from multiple
  source areas (usually on different worksheets)
  data into one summary area
• Use consolidation function function that acts
  on source areas as it combines data (e.g. SUM,
  AVERAGE, etc.)

164
Create a Consolidation Table

• Useful to have worksheets set up in similar
  manner
• Go to destination worksheet
• Activate cell at beginning of consolidation range

165
Create a Consolidation Table

• Data / Consolidate
• select consolidation function
• select range of cells to consolidate
• click add
• go to next worksheet
• when done, click ok

166
PRACTICE

• In BSBG Offices use the monthly worksheets to
  create a semiannual consolidation table of the
  data
• Total sales per state
• Average sales per state
• Maximum sales in Michigan
• Minimum sales in Alabama
• Note these are separate consolidation tables

167
What-If Analysis

• Data may have many factors to consider many
  alternatives to examine and compare
• What-If Analysis data analysis in which key
  variables are changed to show the effects on the
  results of the computation
• Example
• loan payments for various interest rates

168
One-Input Table

• Allows for set up of table with various values
  for variable in calculation
• Table is then filled using those values to
  determine the calculated result
• Example
• loan payments for various interest rates
• know loan payment for given interest rate
• WHAT-IF the interest rate changed?
• Answers this question

169
Creating Using One-Input Table

• Create the formula (should include several
  variables)
• In separate area of worksheet
• enter changing values of variable with column
  header
• in next column, paste result formula
• select range covering both columns
• Data / Table
• Enter row input cell or column input cell
  reference

170
Row or Column--Which do I use?

• To determine cell reference ask
• If I want to calculate the formula by manually
  changing the variables, which cell would I have
  to type in?
• Row or column describes how the input data is
  listed
• Lets Try One

171
PRACTICE

• We will use a one-input table to calculate loan
  payments
• Create the following table in a blank worksheet

172
PRACTICE

• Use the function PMT to calculate the monthly
  payment in cell D7
• In C10 enter Interest
• In D10 enter D7
• In C11C19 enter interest in percent starting
  with 10.00 and going to 14.00 in 0.50
  increments
• (i.e. 10.00, 10.50,11.00, etc.)

173
PRACTICE

• Use a one-input table to compute the monthly
  payments based on the various interest rates
  listed
• In F10G19 repeat using 11.00 interest and
  principal varying between 12,000 and 16,000 in
  500 increments

174
Use the Two-Input Table

• Works just like the one, but now changes 2
  variables in calculation
• Create table same way
• Be sure to drag over whole area including the
  changing variables and where the results will go
• Data / Table should have reference of both row
  and column

175
PRACTICE

• Use a two-input table to compute redo the
  previous exercise in a single step
• Interest rates between 10.00 and 16.00 in 0.50
  increments
• Principal amounts between 12,000 and 16,000 in
  500 increments
• The next slide will get you started

176
PRACTICE
177
Scenarios

• Can use Excel to set up worksheets that model the
  finances of your company
• Can then see how that model behaves by changing
  the conditions that go into the model
• Scenario projected sequence of possible future
  events a set of changing input values and their
  results

178
Creating Using Scenarios

• Relies on changing cells input cells in a
  scenario
• For example
• How would my profits change if my sales increased
  by 10, my cost of goods sold increased 12, and
  marketing costs increased 15

179
Creating Using Scenarios

• Create the financial model (as normal)
• Create a table of changing cells
• make sure the labels correspond to the labels in
  the financial model
• include your base projections (scenario 1) in
  this table
• NOTE These values should be used in your
  financial model
• name the range of changing cells

180
Creating Using Scenarios

• Start to make a scenario
• select the changing cell range
• activate Scenario Manager
• Tools / Scenario

181
Using the Scenario Manager

• click Add
• give the scenario a name (up to 255 characters
  CAN contain spaces and numbers)
• Changing Cells gives you the opportunity to
  select changing cells if not already done
• click OK Scenario Values dialog box is displayed
• could change values of changing cells here
• when done, click close

182
Saving Scenarios

• Part of workbook, saved when workbook is saved
• Can create several different scenarios on one
  worksheet
• Save under different names

183
Switching between Scenarios

• Tools / Scenarios
• Move dialog box so that it does not overlay the
  results area on your worksheet
• Select scenario to display and click Show
• values for that scenario appear in changing cells
• worksheet is recalculated

184
PRACTICE

• Create the following scenarios using the BSBG
  Budget file on your disk

185
Editing a Scenario

• Tools / Scenarios
• Select scenario to change and click Edit
• To change value of changing cells, click OK
• change the values in the Scenario Values dialog
  box
• if using , be sure to enter in decimal form
  (e.g. 0.18 not 18 for 18)

186
Deleting Scenarios

• Tools / Scenarios
• Select scenario, click Delete
• Its gone

187
What Next?