Using Microsoft Excel for Data analysis Physics

Many labs in Physics require the working knowledge of Excel. This lab will provide the basic tutorial for using Microsoft excel for data analysis in a very organized way. It includes writing data in rows and columns, writing formulas, manipulating, analyzing and graphing data using Excel tools. We will learn how to use most of the tools of Microsoft Excel to analyze data. After completing this lab, you should be able to deal with all forms of data in your future labs effectively. It is important for you to understand completely the use of each tool extensively.
In order to understand use of Excel, you will be given an experiment data. You do not have to worry about experiment details. You will only use this data to learn how to enter data, calculating formulas and graphing different given quantities.

  • Click on Excel icon on Desktop. It will open a new window in Excel. The file tab on the top includes all the options of getting new workbook, opening an existing workbook and saving your work with current file name or new file name. It is good idea to hit save button frequently during your work so you do not lose your data accidentally. The save as and print options open a pop-up window with further information input.
  • Entering data: The Home tab shows your current workbook. In the first cell, A1, place the mouse cursor and click so you can write in this cell. Write “Atwood machine experiment data analysis”. This will be title of your data table. Now you are ready to enter data. Data is given as follow.


Distance covered ,h (cm) time taken, t (sec) m1(grams) m2(grams)
130 2.4 105 95
130 1.61 110 90
130 1.37 115 85
130 1.12 120 80
130 0.98 125 75
130 0.91 130 70
130 0.83 135 65


  • The data above is taken from Atwood machine experiment. Atwood machine consists of two mass attached at the end of a light string. The mass of string is neglected here. The string passes over a frictionless pulley which can rotate about its axis. When both masses are equal, string does not move. According to Newton’s 2nd law, If F = 0, acceleration is zero. The equation is given by the relation

(1)                                               F = ma
Using the total force and tension in the string, the acceleration produced in the string is          given as
This equation gives the value of theoretical acceleration. The experimental acceleration can be measure by using kinematics equation for dropped mass at a given height.
The distance covered by mass, time taken and mass m1 and m2 are provided in the data.

  • Also add labels to the columns by clicking on A2, B2, C2, D2. Add more labels for h (m), m1(kg), m2(kg), m1 – m2, m1 + m2, experimental acceleration 𝑎(exp), theoretical acceleration 𝑎 (theo) and experimental error.
  • In cell A3, enter 130 cm for distance. Since you want same number for more cells in the column, bring the mouse to right lower corner of cell A3 until it changes into “+” sign and then drag it down to the cell A9. This action will copy same number to all cells below A3.
  • Now in B3, enter “time” values. You will have to enter all values manually by clicking each cell.
  • The mass m1 changes systematically with the increment of 5.0 kg. Enter first two values of m1 in C3 and C4. Now using your mouse, left click on C3 and drag it down to C4 by highlighting both cells together. You should see a “+” sign in the lower right corner of C4 which can be dragged down to enter remaining m1 values through C9.
  • Repeat previous step to enter values for m2 starting from D3 to D9.
  • To save your worksheet, click on File tab in the upper left corner and choose Save as You can save your file by providing name and folder name where you want to store it.

Writing formulas in cells:

  • Now, you need to change distance given in centimeters into meters in column E3. The formula can be written in the cell by enter “ = ” sign first. After you enter “ = “ sign in E3, write the formula as “ = A3/ 100”. Drag the formula down to cell A9. You do not need to enter formula in each cell below.
  • Repeat the previous step for F3 and G3 to convert grams into kilograms.
  • Now, calculate a(exp) by entering formula in J3. Type ‘ = ’ in J3. Enter formula by typing as “2* E3/(B3^2)”. This will generate results from the formula given by Eq. (3). By dragging ‘+’ down to J9 will copy the same formula to all cells selected.
  • Enter Eq. (2) in K3 in order to calculate a (theo).
  • Similarly percentage error formula can be added to L3. You can enter “=ABS(K3-J3)/K3*100”.This will complete all the data calculations.

In the Physics laboratory experiments, graph is required in order to explain the relationship between different physical quantities. This section will tell you how to plot different quantities using Excel plot function.
Click on insert on the top menu. From Charts submenu, select Scatter. In the Scatter pop-up menu, choose the icon with marked scatter with no lines. This will generate a graph without any labels and data. You can add data by going through following steps.

  1. In the Data submenu of charts tool design, click on Select Data icon, Click on Add. Enter Series name “Newton’s Second law and Atwood machine”.
  2. Under Series X values, click on the red arrow on the right side of the box and open Edit Series. Bring your mouse arrow to cell H3 and holding and dragging mouse to H9 will select the range for x-series.
  3. Repeat step 2 to edit Series Y values for a(exp) from J3 through J9. Hit OK button.
  4. You should now see Series 1 in Select data source window. Click on Series 1 and OK.
  5. The graph should now show both “a(exp)” along y-axis and “m1-m2” along x-axis.
  6. Clicking on one data point in chart will highlight all data points. Right click and in the pop-up window, choose Add trend line. A new window will pop up with several Trendline options. You should choose linear since the data points seem to lie on a straight line. Also check the box for “Display equation on chart” and “Display R-squared value on chart”. From Trendline window, you can also choose Line color, line style and other options. Hit close button.
  7. This should produce a best-fit line through the middle of your data points. You should also see the linear equation with slope m and y-intercept c. R-squared value close to “1” shows a good fit of data.

Additional Practice:
A projectile is fired from ballistic pendulum six times. Distance covered by projectile horizontally and vertically is given in the data table below.

Vertical distance, y, cm Horizontal distance, x, cm
100 150
100 155
100 179
100 175
100 139
100 180


If fired horizontally:                          
If fired at an angle:
Horizontal motion:
ax= 0
vx = v0
x = v0t
Vertical motion:
ay = -g
vy = 0 – gt
y = -1/2 gt2
Horizontal motion:
ax= 0
vx = v0 cos θ
x = v0 cos θ t
Vertical motion:
ay = -g
vy = v0 sin θ – gt
y = v0 sin θ t-1/2 gt2

Open a new Spreadsheet in Excel and calculate the following quantities using the data and equations above.

X (m) Y (m) t (sec)  Vx
v0= mean vx
*Standard deviation,Sv V0 + Sv V0 – Sv

Vx and standard deviation should general only one number for all values of V0 in data. If the projectile is fired at same velocity (calculated in previous step) at an angle of 300, what will be the range of this projectile? You will need to solve quadratic equation to solve for time in 3rd kinematic equation given above. Once you have time, you can obtain x for V0 + Sv and V0 – Sv.
*A way that the deviations can give information concerning the grouping of the values about the mean is by the squares of the deviations.

Open chat
Need assignment help?