EXCEL Spreadsheet Tutorial We will be using Microsoft Excel in this class as a tool for making multiple calculations and graphing equations to help us model physical phenomenon. Starting Excel: 1) Launch Microsoft Excel. 2) When Microsoft Excel opens, at the top of the screen you will find the main menu items (File, Edit, View, Insert, Format, Tools, Data, Window, and Help). Below these, you will see a grid of columns and rows when the program opens up. Columns (vertical) are represented by letters and the rows (horizontal) by numbers. It is in this space that you will be entering equations and adding your experimental data. You will enter your data in the rectangles. The rectangles are called cells and labeled as a point in the X-Y plane: horizontal position, vertical position. A typical cell may be named A9, column A, row 9. There is a toolbar similar to Microsoft Word that allows you to pull down menus. For instance, the File menu, shows commands that deal with files (saving, opening files, deleting files).
Tool Bar Formula Bar
Entering Data: 1) Use your mouse to move your pointer to cell C3. Click on the mouse button once with your pointer over cell C3 to highlight it. Type Distance (m) and press the enter key. As you type, you see the text appear in the active cell on the worksheet and above the worksheet in the formula bar. 2) Using the same technique, type Time (s) in cell D3 and Velocity (m/s) in cell E3.
3) You will notice that the titles for Velocity and Distance are larger than the cell space provided. Let’s fix this! Use the mouse to align your pointer between the cells labeled D and E at the top of the worksheet. When the pointer is between the two lines, the pointer symbol will change shape to two sideways arrows. When you see this change, double click the mouse button and hold down your mouse button and drag the line to the right until you can see the entire word. Release the mouse button. Do the same thing for the Velocity label by aligning your pointer between cells E and F. 4) We’re ready to add data! We will use the data given in the following table based on the position of a runner at specific instants of time. To enter the data, use your mouse to move the pointer to cell C4. Click once so that cell C4 is highlighted. Use your keypad to type 0.1 in the cell corresponding to the first time point. Press enter. This should change the highlighted cell to C5 and you should see a 0.1 in cell C4. Continue entering the distance data provided in the table in cells C5-C9. 5) Now move the pointer to cell D4 and click once to highlight the cell. Enter values for time given in the table in cells D4-D9. Entering Formulas: 1) We will now use Excel to enter the equation to calculate the velocity based on the time and distances that we have entered. Move your pointer to cell E3 and click once to highlight the cell. To enter the formula for velocity we must start by typing an equal sign (=) to indicate to the computer that we want it to calculate a value for us. We wish to calculate the velocity using the equation for velocity=distance/time. We do not need to calculate this ourselves, Excel will do it for us! Our first value for distance is in cell C4 and our first value for time is in cell D4. To enter the full equation, enter =C4/D4 and press the Enter key. Excel will immediately calculate the result (you should see a 1) and displays the answer. Excel uses the asterik (*) to represent multiplication, and the forward slash (/) to represent division. Addition and subtraction are represented with plus (+) and a dash (-), respectively. A ^ (carrot) is used for exponentials. 2) Now, instead of entering the formula into each cell from E5 to E9, we can have excel do it for us. To do this, we will copy the
formula in cell E4 to the other cells. Using the normal mouse pointer, click on cell E4, then go to the Edit menu and choose copy. The cell should have a dashed line around it now. Now use your mouse to click on cell E5, hold down the mouse button and drag down to cell E9. When the cells are highlighted, click on the Edit menu and click paste. The formula should now be copied into the cells and you should see a value for each cell. 3) Let’s save! You should save by clicking on the file menu, then on the “save as” choice. A save window will pop up and ask you to name the file and decide where you want to place it. You should save it in the folder that is set up for your class or class period. Please save as one of your last names (not some nickname!) and press enter. 4) Your worksheet should now look like this: Distance (m) Time (s) 0.1 1 60 11 135 22 220 33 315 44 400 55 Velocity (m/s) 0.1 0.18 0.16 0.15 0.139 0.137
Graphing Data: 1) Let’s say we want to look at a graphical representation of our data. We will use a tool called the “ChartWizard” to do this.
Chart Wizard
2) We need to highlight or select the range of data we want to graph. Let’s graph the velocity as a function of time. To do this, we need to highlight cells D4 though E9. To do this, click in cell D4, continuing to hold down the mouse button, drag your
3)
4)
5)
6)
7)
8)
9)
pointer to E9. When both columns of cells are highlighted, release the mouse button. Once the cells are highlighted, use your mouse to click on the chartwizard icon on the tool bar (there is a bar graph picture on it). Excel will open a box that walks you through the graphing process. The first step is to choose a chart type – click on line (3rd from top) and click next. The next step asks you to select the way in which the data should be charted, for now let’s go with the default method, so click next. The final step asks for information concerning labels on the chart. Everything is fine, except that you want to add a title to the chart and to the x and y axis. For the title, type in Velocity as a Function of Time. Press the Tab key to get to the space for the x-axis. The x-axis title should be Time (s) and the y-axis title should be Velocity (m/s). When you have finished typing these in, click next. Excel will now ask if you want your chart displayed in your excel spreadsheet or as a separate document. The default is to display it on your spreadsheet and that is what we want, so click on the Finish button. When you click finish, excel will place your chart into the worksheet. Your chart may be covering the numbers and equations that we previously entered. If so, you can place your pointer over the graph, press and hold the mouse button and simply drag the chart to where you want it to be. Your graph should look like this:
Velocity as a Function as Time
0.2 0.18 0.16 0.14 0.12 0.1 0.08 0.06 0.04 0.02 0 0 10 20 30 Tim e (s) 40 50 60
Printing in Excel: 1) Before printing your worksheet, be sure to save your work again. Go to the file menu, and click on save. 2) To print your worksheet, make sure that you have highlighted cell A1 (this is just to be sure that you have not highlighted the graph). 3) Use the mouse to click on the File menu bar, then click on print. A print dialog box will appear, click on the OK button in the lower right corner of the print box. Both your spreadsheet and your graph should print out.
If you have time: 1) Explore the features of the graph. If you double click on different parts of the graph you can change the color of the background, the type of symbol used for the graph points, the scale used for the x- and y-axis. Change these to be sure you are comfortable modifying with these features. 2) Construct another graph where you change the Distance used but keep the time constant. See how Velocity depends on distance. How do the two graphs differ?
Velocity (m /s)