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 (You’ll 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, doesn’t 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 governor’s award to those regions whose annual sales are in excess of $60,000. Use a following row to print “Governor’s 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 Let’s 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 element’s 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. 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 PRACTICE

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. 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 PRACTICE