Bruce Mayer, PE Licensed Electrical & Mechanical Engineer BMayer@ChabotCollege

1. Engr/Math/Physics 25 MS Excel Tables/Plots Bruce Mayer, PE Licensed Electrical & Mechanical EngineerBMayer@ChabotCollege.edu

2. Learning Goals • Construct Formatted Tables in Excel • Use the Cell Formatting Commands • Construct Charts and Graphs • Comparison Charts → Bar, Col, Radar • Analysis Charts → Scatter, Surface • Curve Fits → Linear Regression • Use tables and graphs as problem solving tools

3. Using Tables & Charts • Engineers record and present data in two primary formats: Tables and Graphs

4. Tables in Reports • When using Tables in Reports and Presentations • Tables should always have: • a Title • Column headings with brief descriptive names, symbols and appropriate units. • Numerical data in the table should be written to the proper number of significant digits. • The decimal points in a column should be aligned. • Tables should always be referenced and discussed/explained in the body of the text of the document containing the table

5. Table Examples

6. Budgeting Table

7. Table Construction Demo Time For Live Demo

8. Charts and Graphs • Carefully The Select the TYPE of Chart • Different Charts Convey Different Info • Make Clear and Easy to Read • Large Fonts • Good Contrast • Light-on-Dark or Dark-on-Light • Include Legend Unless Info in Title • Label All Axes, Including Units

9. Charts & Graphs cont • Where Appropriate Annotate or Mark points/regions of Interest with Arrows, Ovals, or Text • There are 11 different chart types in Microsoft Excel (and several variations of each type) • This Covers 99.9% of the Chart Types That Most Engineers will need • Consider Next the Criteria for application

10. The 11 MS Excel Chart Types

11. MS Excel Charts • Area Chart • An area chart emphasizes the magnitude of change over time. By displaying the sum of the plotted values, an area chart also shows the relationship of parts to a whole.

12. MS Excel Charts • Bar Chart • A bar chart illustrates comparisons among individual items. Categories are organized vertically, values horizontally, to focus on comparing values and to place LESS emphasis on time. Stacked bar charts showthe relationship of individual items to the whole.

13. MS Excel Charts • Column Chart • A column chart shows data changes over a period of time or illustrates comparisons among items. Categories are organized horizontally, values vertically, to emphasize variationover time. Stacked column charts show the relationship of individual items to the whole.

14. MS Excel Charts • Line Chart • A line chart shows trends in data at equal intervals. Although line charts are similar to area charts, line charts emphasize time flow and the rate of change, rather than the amount of change or the magnitude of values.

15. MS Excel Charts • Pie Chart • A pie chart shows the proportional size of items that make up a data series to the sum of the items. • It always shows only one data series and is useful when you want to emphasize ONE significant element

16. MS Excel Charts • XY (Scatter) Chart • An xy (scatter) chart either shows the relationships among the numeric values in several data series or plots two groups of numbers as one series of xy coordinates. It shows uneven intervals, or clusters, of data and is commonly used for scientific data.

17. MS Excel Charts • Doughnut Chart • Like a pie chart, a doughnut chart shows the relationship of parts to a whole, but it can contain more than one data series. Each ring of the doughnut chart represents a data series • Basically nestedPie-Charts

18. MS Excel Charts • Radar Chart • In a radar chart, each category has its own value axis radiating from the center point. Lines connect all the values in the same series. A radar chart compares the aggregate values of a number of data series.

19. MS Excel Charts • Surface Chart • A 3D surface chart is useful when you want to find optimum combinations between two sets of data. As in a topographic map, colors and patternsindicate areas thatare in the samerange of values.

20. MS Excel Charts • Bubble Chart • A bubble chart is a type of xy (scatter) chart. The size of the data marker indicates the value of a third variable.

21. MS Excel Charts • Stock Chart • The stock chart is often used to illustrate stock prices. This chart can also be used for scientific data, for example, to indicate temperature changes

22. MS Excel Charts • Cone, Cylinder, and Pyramid Chart • The cone, cylinder, and pyramid data markers can lend a dramatic effect to 3-D column and bar charts.

23. Graph Construction Demo • Given Vapor Pressure Data TABLE I: Vapor Pressure Data Vapor pressures (mm Hg) of less than one atmosphere as a function of temperature. (All temperatures are in degrees Celsius) Do in Tutorial http://research.umbc.edu/~lkelly/DAExp.htm • Construct a Scatter Chart to Find the Clapeyron Eqn Constants m & bfor Stannic Chloride

24. Chart Construction Result Demo_Excel_Table-n-Chart_Build_Fa06.xls

25. SLOPE & INTERCEPT Cmds • Find Explicitly the Least-Sqsm&b for

26. SLOPE & INTERCEPT Cmds

27. S&I Cmdsvs Plot TrendLine

28. RSQ Command

29. All Done for Today ExcelPlotting

30. Engr/Math/Physics 25 Appendix Time For WhtBd Demo Time For Live Demo Bruce Mayer, PE Licensed Electrical & Mechanical EngineerBMayer@ChabotCollege.edu

31. Start Table Construction Demo (1) • Merge Cells Vertically • Top & Bot Rows 20High • Middle Rows 16high

32. Format Alignment of the “Year 3 Cell Table Construction Demo (2) • Result so Far • Format Dialog Box

33. Set Column Widths 2, 30, 11, 5, 30, 11, 5 Table Construction Demo (3) • Format Top & Bot Cells for VERTICAL Centering • Format Fonts • All RED • Top, Bot, & Lt-End Rows Bold

34. Set Border Color to Blue Table Construction Demo (4) • Grid INSIDE blue

35. Double-Line Blue Border Outside Table Construction Demo (5) • Double-Line Blue Border Lt-Vert Cell

36. Double-Line Blue Border Remainder Table Construction Demo (6)

37. Center Columns: Course No., Units Table Construction Demo (7)

38. Start Open File Demo_Excel_Table-n-Chart_Build_0511.xls Copy from Table from Slide-22 → Paste into Demo Sheet Need Vertical Data Chart Construction Demo (1) • Horizontal table starting in Col-H • Copy Table Cells and EDIT → PASTE SPECIAL → transpose

39. Chart Construction Demo (2) • Result after Transpose Paste

40. Archive Data Make Scratch WorkSheet; Xfer horizontal Table to to this sheet Edit Worksheet Adjust Headings Delete Cols other Than SnCl4 Move Remaining to Right Chart Construction Demo (3)

41. Place in cols A & B 1000/T; T in Kelvins Ln(Pv) Chart Construction Demo (4) • After Filling A & B • Formula for Col-B • =LN(E8)

42. Now need to Sort the Data with the indep var (1000/T) in ASCENDING ORDER DATA → SORT Chart Construction Demo (5)

43. Highlight/Select Data to Plot Invoke Chart Wizard Chart Construction Demo (6)

44. Continue with Chart Wizard Chart Construction Demo (7)

45. Add X-Grid Lines Chart Construction Demo (8) • Insert As NEW Sheet • Give Descriptive Name • Remove Legend

46. Chart Construction Demo (9) • Chart Wizard Result • Change • X-axis Scale: 2.5-4 • Shorten Title • Clear BackGround • Lager, Sq Data Markers • GridLine & Text Colors

47. Select Chart Area ThenRight-Clik Chart Construction Demo (10) • Select X-axis, Ther Right-Clik

48. Select Grid Lines, Rt-Clik, Chg Colors Chart Construction Demo (11) • Select Data Series, Rt-Clik, Chg Marker

49. Position Labels at Page Edges → Stretch-Out Plot Area Chart Construction Demo (12)

50. Chart Construction Demo (13) • Chart Fine-Tuning Result • Add TrendLine to find Clapeyron m &b Constants