Download Excel Tutorial: A Comprehensive Guide to Excel Basics and Data Analysis and more Assignments Physics in PDF only on Docsity!
Appendix A. Excel Tutorial
A.1 Excel Basics A1.1 Window Title Bar - top row of window which contains the name of the program (Microsoft Excel) and the name of the workbook you are in. Menu Bar - row of words just under the title bar. Each word is the title of a menu. The menu may be opened by positioning the mouse arrow on top of the menu title and then clicking the left mouse button. Buttons - shortcuts to various options and commands within Excel. Each button has an icon on it to help you remember what it does. To start the function, click on the button. Toolbar - collection of buttons under a unifying name. Each active toolbar is located in the rows directly under the menu bar. Toolbars may be activated by clicking on Toolbars from the View menu and then clicking on the titles of the toolbars you want active. The two most useful ones are the Standard toolbar and the Formatting toolbar which are both shown in the figure. Formula Bar - located under any toolbars you have selected. It displays the current cell address and contents. It is divided into three sections by vertical lines. The second and third sections only come alive when you type something into the cell. Whenever a cell contains a formula, the formula will be displayed here. Status Bar - at the very bottom of the window. It displays information about the current state of Excel.
A- 1
Document Window - located right below the formula bar. It has various features along its edges.
- Scroll bars - for moving the spreadsheet vertically and horizontally.
- Sheet tabs - for displaying the first six sheets of the workbook. Click on the appropriate tab to activate a new worksheet (bringing it to the top for display).
- Cell letters and numbers - letters across the top of the window and numbers along the left-hand side. The letters and numbers are the titles of the columns and rows within the spreadsheet. They fix the location of the various cells by specifying the column and row each cell is in. The current active cell has its column letter and row number displayed in the first part of the formula bar. A1.2 Spreadsheet Components Spreadsheet - collection of cells in rows and columns in which data and equations can be entered. An equation in one cell can operate on data in other cells. Two major features help you save time doing data analysis.
- Equation copying - spreadsheets allow you to copy an equation that is valid for a whole column (or row) of data. Excel automatically figures out the appropriate data for each row to use in the equation. Therefore, you are only required to type a given equation one time.
- Data updating - results are automatically recomputed if you change any of the data in the spreadsheet. Therefore, once again, if you want to make the same computations with multiple data runs, you only type the equations once. Also, if you make a mistake inputting the data (e.g., you messed up the units the first time you entered the data), then make your corrections to the data and the results are recomputed for you everywhere in the spreadsheet that data is used. Workbook - collection of worksheets, charts, and macro sheets which are saved as a single file. Contains 16 blank worksheets to start. Worksheet - another name for a spreadsheet. Each worksheet has cells organized in a series of 256 columns and 16,384 rows. Active Cell - the selected block within the spreadsheet which is formed by the intersection of column and row gridlines. The cell is selected by clicking on it. The active cell is shown by a heavy outline. It is referenced by its column letter and row number.
- Relative cell reference - the column letter followed by its row number.
- Absolute cell reference - indicated by placing a dollar sign in front of the column letter and also in front of the row number. Absolute references in formulas don't change when you copy the equation. A1.3 Basic Features Beginning and Ending
A- 2
- Closing and opening workbooks - click on Close from the File menu. If you have not yet saved the workbook, you will be prompted to do so. After closing the workbook, you can open a previously existing workbook by clicking on Open from the File menu (or clicking on the floppy disk icon in the Standard toolbar). You need to select the appropriate drive and folder that the file is stored in. Click on the file you want and then press OK. The workbook will appear in your window.
- Quitting Excel - click on Exit from the File menu. Entering Text and Data
- Typing method o Select a cell (e.g., cell A1) o Type into the cell the words you want to appear. For example, type “Purpose: Excel basics” in cell A1. Press Enter when finished. Note that the words appear both in the selected cell and in the formula bar. o Correct any mistakes by clicking first on the cell, then on the text in the formula bar. Use delete and other keys to make corrections and then press Enter when finished.
- Series method - for typing in a series of numbers. o Small series - type the first number of the series into the first cell (e.g., type '10'1 into cell A3 and then press Enter). Highlight cells A3 to A12 by clicking on cell A3 and then dragging the pointer down to cell A12 and then releasing the left mouse button (the top cell will be clear while the rest are dark). From the Edit menu, click on Fill and then on Series from the submenu. In the dialog box that appears, choose Columns, Linear, and Step Value of 10. Press OK and the series 10 to 100 will appear in cells A3 to A12.
A- 4
o Large series - type in the first number as before, but don't highlight the cells (too many). Click on Edit, Fill, and Series from the menu bar. In the dialog box, fill in as before, but place a number in the stop value. Click OK and see your series. o Clearing a series - highlight the series and then from the Edit menu, click on Clear and then on Contents from the submenu. Alternatively, after highlighting the series, right click with your mouse. From the menu that appears, select Clear Contents. Formatting the Spreadsheet
- Labels - type in descriptive labels for columns of numbers that you have in your spreadsheet. For example, in cell A2, type “Angle (deg)”. Press Tab to go to cell B2. Type “Angle (rad)” in cell B2. Type “ cos “ in cell C2. To get , you will need to type the letter “q”, highlight it, and then select the Symbol font in the Formatting toolbar (press the down arrow attached to the font box and then scroll down till you find the Symbol font listed). Type “Vx (m/s)” in cell D2, “Vy (m/s)”
A- 5
o Simple auto fit method - simply double-click on the right-hand border of the column. Finish adjusting columns A through F to make sure each label is completely visible.
- Styles o Selected cells - after highlighting the cells you want to change, select the appropriate button on the Formatting toolbar. For example, highlight cells A3-F3 and click the B on the toolbar to make the labels boldface. You will have to reformat. o Individual characters - highlight the appropriate characters and then select the button you want on the Formatting toolbar. o Example styles a. Number format - increasing or decreasing the number of digits shown by Excel. First highlight the cells with the digits that are to be changed. Click on either the Increase Decimal button, or the Decrease Decimal button, as appropriate. b. Greek letters - after highlighting the letter to change, click on the down arrow on the right-hand side of the font window. Choose the symbol font. The English letter will change to a corresponding Greek letter (e.g., r changes to^ ^ , q to , p to^ ^ , d to , and D to (^) ). Entering Formulas
- Typing method - select the cell for the formula and type an = sign. Next, type in the formula using the appropriate cell identifying letters and numbers. If you need -a special function, Excel has some built in formulas that you can access by clicking on the Insert menu and then Function (or click on the fx icon). In the Function Wizard dialog box, click on the Math & Trig category and then scroll through the right-hand list until you find the appropriate function. For example, to change the 10 degrees in cell A4 to radians in cell B4, type “= (A4/180)*Pl()” in cell B4 and then press Enter. You can either type “PI()” yourself or select it from the Function Wizard dialog box. Note that after you press Enter, the typed in formula remains in the function bar while the cell displays the number calculated from the formula.
A- 7
- Clicking and typing method - same as above, except instead of typing cell references each time, you can just click on the cell itself. For example, in cell C type the = sign and then “cos(”. Next, click on cell B4 and the formula will read = cos(B4. Finish by typing the remaining parentheses and then pressing Enter. For cell D4, type “= 5” and then click on cell C4. This will calculate an x component of the velocity which equals 5 cos^ ^. In cell E4, type “= 5*sin(”, click on cell B4, and finish with “)”. To calculate the magnitude of the velocity we use the Pythagorean formula. In, cell F4, type “= sqrt(”, then click on cell D4, then type “A2 +”. The hat symbol tells Excel to raise the previous entry to the power of the following number. Finish by clicking on cell E4 and then typing “A2)”. After pressing enter, the cell should read a velocity of 5.
A- 8
- Without preview - simply choose Print from the File menu or click on the printer icon on the Standard toolbar.
- With preview - click on Print Preview from the File menu. The worksheet as it will print is shown. You can zoom in on various features if you wish or change the margins. If you like the way it looks, you can print from this screen.
A- 10
A.2 Data Analysis Tutorial A2.1 Table Preparation Select Worksheet
- Click sheet2 tab - a new worksheet comes to the front of the window.
- Name the worksheet - using menu item Format ^ Sheet ^ Rename, entitle the sheet a “acceleration table”. Alternatively, right click-on the tab to rename it. Organize Table
- Name and section - in cell Al, type “Name: your name”. Press Tab to go to the next cell and then type “Section: your lab day and time”.
- Description of table purpose - in cell A3, type “Purpose: to calculate the acceleration of a failing object”.
- Data set identification - in cell A4, type “Data set 1: One Dimension”.
- Table labels - skip a row and type labels for each column in row 6. column headings should be: “Data No.”, “time(s)”, “Distance(m)”, “Mid-Time(s)”, “Velocity(m/s)”, and “Acceleration (m/s^2 )”.
- Format as needed A2.2 Data Entry Data Number - starting in cell A7 (directly under the heading “Data No.”), enter the number “1”. Highlight cell A7, then select Edit ^ Fill ^ Series from the menu bar. In the dialog box choose Columns, Linear, Step Value of 1, and Stop Value of the number of frames you are using (ie 12). Time Data - starting in cell B7, enter “=A7/fframerate”. Highlight cells B7 to B18, then select Edit ^ Fill ^ Down. Note there is no need to enter a stop value since Excel will only fill up the cells you have highlighted. Distance Data – starting in cell C7 enter the following distances into the D column: 0.033, 0.084, 0.129, 0.162, 0.188, 0.204, 0.209, 0.197, 0.197, 0.178, 0.143, 0.094, and 0.033 (if you have the data you collected in the lab, insert your data in the cells instead).
A- 11
- Step 2 box: verify the range of data to be plotted. The range should read B6:C18.
- Step 3 box: Chart Options - fill in the appropriate information then click Next. o Titles: for the Chart Title enter “Distance of Fall vs. Time – One Dimension”. For the axes, enter “Time(s)” for Value(X) and “Vertical Distance(m)” for Value(Y). o Gridlines - click major for both axes. o Legend - click off. o Data Labels - none.
A- 13
- Step 4 box - Chart Location - choose As New Sheet, then give the new sheet a name (e.g., “dist. vs. time”). Click on Finish. Select or Deselect the Chart - in order to modify the chart or move it. When selected the chart will have “handles” showing at the corners and the sides of the chart.
- Resizing the chart: for a chart on its own chart sheet, resize it in Print Preview (in the File menu). Click on Margins and then drag each of the newly visible margin lines closer to the paper edges in order to make the chart as big as possible.
- Editing the chart: right click your mouse on any chart feature that you want to edit. Curve Fit the Data (if desired) - Excel determines the best curve that fits your data. Excel calls this process the calculation of a “trend line”, although for a linear fit, it is better known as least squares fitting. All you have to do is right click on one of the data points (which will then turn yellow) and then select Add trend line in the menu which appears.
A- 14
- Linear fit - click Linear for Trend/Regression Type. Click on the Options tab and select the appropriate options as described above. You should now go back and create a chart for velocity vs. the midpoint time (columns E and F.) and use the linear fit trendline to determine the acceleration from the slope of the line. It should be approximately 9.8 m/s^2. Scale the axes (by right clicking on the numbers) so that the data points stretch from the bottom left corner of the graph to the upper right corner. Make sure the intercept shows on the graph.
A- 16
A2.5 Fine tuning the trend line Excel provides a variety of options for fine-tuning the trend line you add to a data set. These options include inserting the equation of the line on the graph, extending the trend line backwards or forwards, setting the intercept of the trend line etc. To get to these options, right click on a trend line that you have already drawn on a data set and in the menu that comes up choose “Format Trendline”. In the pop-up window that appears, choose the ‘Options” tab. The window then looks like the one in the screenshot given below Some of the more useful controls that you have over the trend line are the following Display equation on the chart: checking this option makes Excel compute and display the equation on the line on the chart. This is useful for finding slopes and intercepts of the graphs. Forecast (Forward/Backward): The fields titled Forward and Backward Forecast on the options window can be used to extend the line forward or backward. The value in the field denotes how many times the current length of the line should it be extended. Backward forecast is useful in lab #1 for extending the velocity versus mid-point time graph so as to see the x and y intercepts. For the velocity versus midpoint time graph you will have to do a backward forecast to make the line intersect both the y and x-axis. The value in the forecast field will usually be a small number like 0.3 or 0.4 in this particular case.
A- 17
Click on the “Add” button so that a new series named ‘Series2’ appears in the text box above the add button. Now you have to provide the x and y values for the new series. The easiest way of doing this is to click on the multicolored button on the right end of the text box labeled ‘X values’. Avoid clicking on the actual text box itself. On clicking on the button, the chart wizard pop-up will collapse into a smaller window and you will be able to see the worksheet behind it. Select the column of numbers (numbers only) which has to be on the x-axis for the new series. In this example we want time to be x-axis again and so select A7 through A16 as shown in the screenshot below Make sure that the sequence “=Sheet…!$A$7:$A$16” appears in the textbox of the chart wizard when you select the time values. Click again on the button on the right end of text box in the collapsed version of the chart wizard pop-up to restore the window to the original size. Supply the y-values for the new series in the same manner by clicking on the button at the right end of the text box labeled ‘Y values’. In this example the new y-axis values are the KE values (E7 to E16). There will already be some characters printed in the ‘Y values’ text box which should automatically be replaced with the appropriate cell references of the data you want to plot. i.e. “=Sheet…!$E$6:$E$16” will appear in the Y Values box on selecting the data. This will happen only if you avoid clicking inside text box before collapsing the chart wizard pop-up. After selecting the y-axis data restore the pop-up and the second series would have appeared on the preview area of the chart wizard. The preview must look as shown in the diagram on the right.
A- 19
Add a third series with the time values (A7 to A16) on the x-axis and the E values on the y-axis. Make sure that all three data sets extend through the same range on the x-axis on the preview graph once the third series has also been entered. Click on ‘Next’ on the chart wizard and complete the remaining steps exactly as in the previous section (enter the axes labels, chart title, legend, etc). The completed sheet with three graphs on it should look as shown in the screenshot below You can now select each individual series that appears on the graph and add trend lines to them as before. For lab #4 the PE and KE graphs must be parabolas while the E graph should be linear. The completed sheet with the lines is shown in the screenshot below
A- 20
