Lecture 11B Using Excel Chapter 8
Starting Excel Figure 1-2, page 1.04
The Excel Window • Tool bar • Formula bar • Worksheet window • Columns and Rows • Cells • Pointer • Sheet tabs
Use the mouse Use the keyboard Arrow keys Page Up Page Down Home Crtl + Home F5 Moving Around a Worksheet
Open 1-OhmsLaw.xls • File Open... Figure 1-8, page 1.11
Some Basic Excel Stuff • Learn how to enter text, values, formulas, and functions • Describe a range of cells • Perform what-if analysis • Learn how to use online Help • Demonstrate how to print a worksheet
Entering Information • Text - letters, symbols, numbers, and spaces • Values - numbers that represent a quantity • Formulas - used to calculate values • Functions - special pre-written formulas
Range—used in functions • Range - a group of cells: A1:C4 Figure 1-16, Page 1.19
Saving a Workbook • File Save As... Figure 1-19, Page 1.23
Playing “What-if?” • Change a value in a cell • Excel automatically recalculates the worksheet
Correcting Mistakes • Before you press the Enter key • Use the Backspace key • After you pressed the Enter Key • Double click on the cell to put it in Edit modeDon’t use the Space bar to clear a cell
Getting Help • Microsoft Excel Help • Contents • Answer Wizard • Index • Show/Hide the Office Assistant • What’s This? • Office on the Web • About Microsoft Excel
Printing the Worksheet • File Print... Figure 1-28, Page 1.32
Closing and Exiting • File Close • File Exit
Intermediate Objectives • List the components of a well designed worksheet • Understand “Order of Precedence” • Demonstrate several methods for copying information • Understand Relative vs Absolute Cell Referencing • Change the name of the worksheet
A Well Designed Worksheet • Clearly identifies its goal • Presents information in a clear, well organized format • Includes all necessary data to produce the intended results
Developing the Worksheet • Determine the worksheet’s purpose • Enter the data and formulas • Test the worksheet • Correct errors and make modifications • Document the worksheet • Improve the appearance • Save and print the completed worksheet
Planning the Worksheet • What is the goal of the worksheet? • What data is needed to calculate the results? • What calculations are needed? • EXAMPLE—Calculate density of air as a function of temperature from 0-50C in 5 degree steps • r=P/RT • P=stnd atmosphere 101.3kPa • R=gas constant for air 286.9 J/kg-K • T=temp in Kelvins
Building the Worksheet • Establish the layout • Enter the data • Enter the formulas • See 2-AirDensity.xls example
Entering Labels • Helps to identify the cells where you will enter the data and formulas • Alignment is left justified, and spill into empty cells to the right
Entering Data • Values can be: • Numbers • Formulas • Functions • The data is the information you need to perform the calculations
Entering Formulas • Formulas are equations that perform a calculation • An = sign at the beginning of a cell indicates you are entering a formula • If formulas contain more than one operator, Excel performs the calculation according to the standard order of precedence
Order of Precedence • Predefined rules used to perform a calculation: 3 + 4 * 5 = ? • Is the answer 35 or 23?
Order of Precedence (continued) • Exponent: ^ • Multiplication and Division: * and / • Addition and Subtraction: + and - 3 + 4 * 5 = 23 (3 + 4) * 5 = 35
Copying Formulas • Menu Commands • Toolbar buttons • Fill Handle
AutoSum Button • Automatically creates a formula that contains the SUM function • Looks at the cells adjacent to the active cell and guesses which range of cells you want to sum • Excel’s guess is displayed
Entering Functions • Type the function by hand • Use the Paste Functionbutton • Do the example in 3-Functions.xls Figure 2.17, page 2.17
Relative vs Absolute Reference • Relative Cell Reference = A5*B5 • Absolute Cell Reference = $A$5*$B$5
Relative vs Absolute References when Copying Formulas • Relative Cell References - cell references change when they are copied • Absolute Cell References - cell references do not change when they are copied
Mixed References • Part of the reference is absolute and part is relative: $A5 • Do the example in 4-AbsoluteVsRelative.xls
Copying using Copy & Paste • Select the cell or cells to be copied • Select EditCopy • Select the cell or cells you want to replace • Select EditPaste The copied cells are placed in the Clipboard, and can be pasted many times.
Renaming the Worksheet • Double click on the worksheet tab • Enter the new name
Excel Plotting Skills • Learn to use tables and graphs as problem solving tools • Learn and apply different types of graphs and scales • Prepare graphs in Excel • Be able to edit graphs
Plotting Data • Independent Variables • “The Cause” • X-Axis on Graphs (abscissa) • Left Columns on Tables • Dependent Variables • “The Effect” • Y-Axis on Graphs (ordinate) • Right Columns on Tables
Question • The speed of sound in air depends on the temperature, humidity, and air pressure. • What are the independent variables? • …dependent variables?
Table and Graph Requirements • Label the axes on your graphs. • Include units on the axes and on column headings. • Use landscape graphs.
Proper Use of Tables & Graphs You can copy straight into your final report!
Tables • Tables should always have: • Title • Column headings with brief descriptive name, symbol 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 (at least briefly) in the body of the text of the document containing the table.
Graphs • Proper graphing of data involves several steps: • Select appropriate graph type • Select scale and gradation of axes, and completely label axes • Plot data points, then plot or fit curves • Add titles, notes, and or legend
1. Pie Chart Graphs - Types 2. Bar Graph
Graphs - Types 3. 3-D Graph 4. Line Graph Body Temperature (0C Distance (m) Speed (m/s)
Graphs • Each graph must include: • A descriptive title which provides a clear and concise statement of the information being presented • A legend defining point symbols or line types used for curves needs to be included • Labeled axes • Graphs should always be referenced/discussed in the body of the text of the document containing the table.
Titles and Legends • Each graph must be identified with a descriptive title • The title should include clear and concise statement of the information being presented • A legend defining point symbols or line types used for curves needs to be included
Length (km) Axis Labels • Each axis must be labeled • The axis label should contain the name of the variable and its units. • The units can be enclosed in parentheses, or separated from the label by a comma.
Scale Graduations, Smallest Division=3.33 Scale Graduations, Smallest Division=1 Acceptable Not Acceptable Gradation • Scale gradations should be selected so that the smallest division of the axis is an integer power of 10 times 1, 2, or 5. • Exception is units of time.
Data Points and Curves • Data Points are plotted using symbols • The symbol size must be large enough to easily distinguish them • A different symbol is used for each data set • Data Points are often connected with lines • A different line style is often used for each data set
Velocity of Three Runners During a 5 km Race Example
Building a Graph In Excel • Select the data that you want to include in the chart by dragging through it with the mouse. • Then click the Chart Wizard