Skip to main content
Chemistry LibreTexts

Excel How To...

  • Page ID
    275315
  • \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)\(\newcommand{\AA}{\unicode[.8,0]{x212B}}\)

    Calculate Mean

    The format of Excel's formula for calculating a mean is

    =average(number1, number2,...)

    where number1, number 2... are the individual values used to find the average. Position the cursor in the cell where you wish to place the mean and type

    =average()

    Place the cursor inside the parentheses and click on each cell that is to be included in calculating the average. After selecting the cells, press Enter to complete calculation. If the data are in a single column or row, you can click on the cell containing the first number and drag over the cells until you reach the cell containing the final number. Shown below are a few examples:

    reports the average for the values in
    =average(A1,A3,A7) cells A1, A3 and A7
    =average(A1:A7) the first seven rows of column A
    =average(A1:E1) the first five columns of row 1
    =average(A1,A3:A7) the values in cells A1 and the cells from A3 to A7

    Calculate Standard Deviation

    The format of Excel's formula for calculating a standard deviation is

    =stdev(number1, number2,...)

    where number1, number 2... are the individual values used to find the standard deviation. Position the cursor in the cell where you wish to place the standard deviation and type

    =stdev()

    Place the cursor inside the parentheses and click on each cell that is to be included in calculating the standard deviation. After selecting the cells, press Enter to complete calculation. If the data are in a single column or row, you can click on the cell containing the first number and drag over the cells until you reach the cell containing the final number. Shown below are a few examples:

    reports the standard deviation for the values in
    =stdev(A1,A3,A7) cells A1, A3 and A7
    =stdev(A1:A7) the first seven rows of column A
    =stdev(A1:E1) the first five columns of row 1
    =stdev(A1,A3:A7) the values in cells A1 and the cells from A3 to A7

    Create a Scatterplot

    Suppose that we wish to create a plot showing how the concentration of copper in a solution affects the solution's absorbance. We prepare a series of solutions with known concentrations of copper and measure the absorbance of each, obtaining the data shown below (and available here):

    [Cu2+] (M)
    Absorbance
    0.000
    0.000
    0.100
    0.124
    0.200
    0.248
    0.300
    0.359
    0.400
    0.488
    0.500
    0.604

    To create a scatterplot of the data, do the following. First, organize your data so that the first column contains the data to be plotted on the X-axis and the second column contains the data to be plotted on the Y-axis. Select the data by clicking in the table's upper left corner and dragging to the table's lower right corner; your data is now highlighted. Click on the menu button for the Chart Wizard (the icon looks like a small bar graph).

    On the Chart Wizard's first page, select XY (scatter) as the chart type and select one of the five displayed options. In general, the best option is to display points without lines.

    Continue to the Chart Wizard's second page to view the current plot. If the plot is correct, then continue to the next page. If the plot is incorrect, you can make some changes here, such as indicating that your data is organized by rows instead of columns, or by selecting the Series tab and adjusting the data used for plotting. In most cases you should not need to make any adjustments.

    On the Chart Wizard's third page you can make adjustments to several of your scatterplot's features. Use the Title tab to add a legend to the X-axis and to the Y-axis. The Axes tab allows you to choose whether to display a scale along each axis. Use the Gridlines tab to add or remove gridlines. The Legend tab allows you to add or remove a legend and to select its position. Finally, the Data Labels tab provides tools for adding labels to your data.

    The last of the Chart Wizard's pages allows you to select a location for your chart.

    After completing your chart you can make changes to it by clicking on the scatterplot and selecting Chart: Chart Options... from the main menu. You also can change your scatterplot's background color by double-clicking the background and selecting a new color (or no color) for the area. The font, the scale and the positioning of tick marks on either axes can be changed by double-clicking on the axis.

    Here is a final scatterplot for the data given above:

    Scatterplot.png

    Find a Trendline. To add a trendline to a scatterplot, click on the scatterplot and select Chart: Add Trendline... from the main menu, which opens the Add Trendline window. On the tab labeled Type, select the regression equation you wish to use: for these modules, choose either linear or polynomial (indicating the polynomial's order). On the Options tab, click on the appropriate boxes to display the equation and the value for R2 on the chart. Click OK to add the trendline. Here is the result for the data considered under "How to Create a Scatterplot."

    Trendline.png

    Sort Data

    Suppose that we have the following table in Excel containing data for 10 pennies, organized by the order in which the pennies were analyzed (click here to download this data set).

    Penny
    Year
    Mass
    1
    1972
    3.1006
    2
    1984
    2.4899
    3
    1991
    2.5251
    4
    1984
    2.5146
    5
    1991
    2.5111
    6
    1984
    2.4894
    7
    1974
    3.1029
    8
    1989
    2.5276
    9
    1979
    3.0843
    10
    1975
    3.1034

    To aid in viewing the data, it might be nice to reorganize the table so that it is ordered by the year of minting and, within each year, by mass. Here is how to do this. Click on any cell within the data you wish to sort. Select Data: Sort... from the menu bar, which opens the Sort window; note that the data in your spreadsheet are now highlighted. You can sort your data using up to three variables; for this example we will sort first by year and then by mass. Use the first pop-up list (Sort by) and select Year. Next, use the second pop-list (Then by) and select Mass. For each variable select one of the two radio buttons: ascending to sort from smallest-to-largest or descending to sort from largest-to-smallest. Because the table includes a row of column labels, select the radio button indicating that there is a header row. Click on OK to complete the sort. The final table is shown here:

    Year
    Penny
    Mass
    1972
    1
    3.1006
    1974
    7
    3.1029
    1975
    10
    3.1034
    1979
    9
    3.0843
    1984
    6
    2.4894
    1984
    2
    2.4899
    1984
    4
    2.5146
    1989
    8
    2.5276
    1991
    5
    2.5111
    1991
    3
    2.5251

    Note that the data are now ordered from 1972 to 1991 and that the pennies from 1984 and 1991 are ordered from the smallest mass to the largest mass.


    This page titled Excel How To... is shared under a CC BY-NC-SA 4.0 license and was authored, remixed, and/or curated by Contributor.

    • Was this article helpful?