# Microsoft Excel

## Tables in Excel

### The first stage of creating a table within Excel is having data to handle. Below shows a data set (an Ohm's Law investigation) to be inputted into Excel :- ### When Excel is first opened, the following spreadsheet is generated :- ### In order to input the data, simply click one of the boxes (known as cells) within the spreadsheet and type. The data in each cell can be edited using the controls at the top of the screen, just like a Word document. Cells can be widened to fit larger amounts of text simply by "double clicking" the line between the cell title letters in the top bar:- ### In order to create table lines, highlight the cells to be given borders then click on the border icon in the toolbar (circled in red below) and select the required borders :- ### Once border lines have been added, the table is complete :- ## Basic Calculations Within Excel

### The first stage of performing a calculation is to first extend the table to contain any new columns required for the calculation (in this case the conversion of mA to Amperes and then calculation of Resistance) :- ### To perform a calculation, select the cell required and press the "=" key. An equation can then be simply types into the cell, and on pressing "enter" the calculation will be complete. If the calculation requires content from another cell, simply click that cell at the appropriate point in the calculation (as shown below) . This is very useful as if the content in the targeted cell changes, then the calculation automatically updates to give the new required answer :- ### By clicking and dragging the box at the bottom right of the cell (circled again in red), the calculation can be copied down to each row. The system will automatically change the selected cell contained within to the correct row (C3 to D3 to E3 etc.) :- ### A similar calculation can be performed to give the Resistance column :- ### To calculate the average Resistance value, a SUM calculation can be performed. This is performed by typing "=SUM" into the required cell then "clicking and dragging" to highlight the required data. Through the use of brackets, this SUM calculation can be divided by the number of readings (in this case 6) to calculate the average value :- ### The resultant value for the Resistance calculated using the above method has too many significant figures. This can be corrected by highlighting the required cells and "right clicking" on them. From the drop down menu select "Format Cells", then in the pop up window select the number tab (as shown below) and set decimal points to the required value (in this case 0) :- ### The final result of this processing is shown below :- ## Basic Charts / Graphs Within Excel

### The first stage in creating a graph is to first identify and highlight the data sets to be used (in this case the Voltage and Current columns). As we do not require the mA column, in order to highlight only the required data, simply "Click and Drag" down the first column, press and hold "ctrl" then select the second required column :- ### Once the required data has been highlighted the type of chart or graph can be selected. To do this, click on the "Insert" tab in the toolbar, then select the required graph type to insert into the document. For almost all graph work within Physics, select the Scatter graph, as shown below :- ### Next, click the "Move Chart Location" in the toolbar and select "new sheet". This opens your graph as a new page at the bottom of the spreadsheet, which will show the following window :- ### The graph in this form is not complete, several aspects of editing is required, firstly the addition of a "Line of Best Fit". In order to add a line of best fit, simply "left click" on a data point to highlight the set, then "right click" and select "Add Trendline" from the pop-up menu. The following sidebar will open and can be used to edit the trendline :- ### For most experiments within Physics, the "Linear" trendline will be used. As seen from the Physics course, a straight line equation can be used to calculate additional variables (in this case the gradient will give the 1/Resistance value), and the equation can be added to the graph by selecting the option at the bottom of the list. By clicking the "bucket" option, the line itself can be edited (colour, thickness etc.). Once this been completed, the graph will appear as shown below :- ### The next stage of editing will be the data points themselves. In Physics, circular data points must not be used. The correct data point will be a cross, as the center of the cross shows the exact position of the data point. They should be small, but of a size that ensures they are visible through the trendline, and finally they must show data labels. In order to edit the data points, simply "left click" on a data point to highlight the set, then "right click" and select "Format Data Series" from the pop-up menu. The following sidebar will open and can be used to edit :- ### Once this been completed, the graph will appear as shown below :- ### The next stage of editing is to add Major and Minor Gridlines, Axis Titles and Data Labels to the graph. This is done by clicking on the "+" symbol and selecting "Gridlines" and ticking all boxes, then ticking the boxes for Axis Titles and Data Labels :- ### Once these boxes have been ticked and the axes labels corrected, the graph will appear as shown below :- ### If the minor grid lines are still not fine enough, the number of minor grid lines can be edited also. To edit these, "right click" on each axis and select "format Axis" from the pop up menu. The following sidebar will open and can be used to edit :- ### Once this has been completed the graph is complete and will appear in its final form as shown below:- ## Complex Calculations - LINEST Function

### To calculate this, the starting point required is the same as for a graph :- ### To allow the calculation of a LINEST function, an additional 4x4 cell section needs to be used. The following shows the additional layout, as well as labels for each :- ### Once this calculation has been typed into the first cell, press Ctrl+shift+Enter, and the table should now show four values :- ### As was stated earlier, the gradient of this graph shows 1/Resistance. If the axes are reversed (Current X axis, Voltage Y axis), the gradient will then give Resistance, as shown below :- 