Welcome to the Physics Department

Tips and tricks for physics calculations in Excel


Entering Formulas

Start a formula with an equal sign.
Click on a cell to include the cell's value in your formula.
Use dollar signs when referring to a single cell that isn't part of a column of values.
To apply formula to entire column, put cursor on lower right corner and drag down -
or double click on the lower right corner to automatically drag down to match an adjacent column.


Naming Cells

If you name a cell, you can use the name in a formula, which makes it more readable.
Right click a cell, choose define name, and then edit if needed in the dialog box.
Excel will look for text next to the cell for a name, but you can add your own.
Sometimes it makes you put an underscore before a single letter, like '_m'


Scatter plots and Trendline Functions

Highlight the data you want to plot such that the x-values are on the left.
If data columns aren't adjacent, hold down Ctl key to select new columns.
In the Insert tab, select charts and click on Scatter (X,Y).
Choose the graph with data points only - no lines between the points.

Always label your chart axes, including units:

Click on the graph and then the "+" symbol and check the label boxes.

Video tutorial

Adding a trendline and getting equation:

Right click on a data point, choose "add trendline".
Choose between a linear or other type of fit. Check "display equation on chart."

Video tutorial

Plotting multiple data sets on one chart.

Right click on a data point, choose 'Select Data', then add a new data series.
Select X and Y data for the new series.
Make sure to erase what's in the 'Y values' box.

Video tutorial