1 / 187

MS Excel Intermediate

MS Excel Intermediate. Adult & Continuing Education. HI!. Instructor: How to contact me by phone at by e-mail at. Attendance. MATC General Info. Grading (Satisfactory or Unsatisfactory) Evaluations (You’ll get your chance) Restrooms No food or drink in the lab Emergencies:

bien
Télécharger la présentation

MS Excel Intermediate

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related