Using Excel and basic statistical analyses on cell viability data, Science Communication, Scientist journey

Using Microsoft Excel 

Introduction

An important aspect of working in a laboratory is being able to faithfully keep track of data. There are many ways to keep and store data (see Lab Notebooks). One way to carefully store data and quickly analyze it, in an organized way is to use a computer program like Microsoft Excel. This spreadsheet program allows us to enter data in specific ways that not only make it easy to organize but also allows for many different mathematical operations to be performed in a few keystrokes. Why should we use Microsoft Excel in our workshop? One reason: it's included with our university account (full access to the Microsoft Office suite can be found at this link, using your UofSC username and password: portal.office.com). In this session, we’ll be learning how to use Microsoft Excel by logging data that we collect from cell count data. Which cells are we using? Where can I find more information on Microsoft Excel? Let’s find out. 

Microsoft Excel Basics

One can think of spreadsheet software like a notebook, allowing us to input data into cells (cell example: A1; Figure 1). These cells are arranged vertically into alphabetized columns (A-F are shown in Figure 1) and horizontally into rows (1-16 shown in Figure 1). We can easily identify cells by typing their column then row: for example, A1. What can we type in a cell? It could be numbers, text, or mathematical formulas. The following is a list of important hints and tricks for using Microsoft Excel for easy, organized data entry:

1.    Label everything.

a.    In the moment, it may seem easy to realize what the data means and how it’s organized, yet returning later, we may be confused. 

b.    It’s crucial to include titles for columns. Some even label each row if they are unsure (or at least include a small key that clearly states what data is in each cell, row, or column). 

c.     When in doubt, label it!

d.    Units are crucial. Ensure that units are clearly stated. 

2.    Making a list? Work in columns!

a.    When collecting/inputting data, work in columns. For example, if we have 5 different cell density values to input from the same treatment, place them all in one single column (Figure 2).

i.     This makes mathematical operations and statistics much easier. 

3.    Navigate with arrow keys or “tab” and “enter”

a.    We can click on specific cells to enter data, and often we’d like to quickly move to another cell. This can be done in a variety of ways, the arrow keys, tab, and enter/return are some of the quickest. 

4.    To input a formula for a mathematical operation, start with “=”. 

a.    Once we’ve typed “=” Microsoft Excel recognizes this as the start of a formula. 

b.    There’s a wide variety of things that can be done now, we’ll mention several below when discussing statistics. 

i.     Example: To find the average of a particular set of data, simply type “=AVERAGE(data start:data end)”. i.e. “=AVERAGE(B3:B7)”

c.     Note that once “=” is typed, everything else we type is included in that equation until we hit enter/return or otherwise exit the cell. 

d.    Tip: we can drag our cursor over data that we want to include in a formula. This works well with sets of data that can be easily viewed on the screen. For extremely large data sets, it’s easier to physically type the range (for example B3:B1898). 

5.    Statistics in Excel

a.    What are statistics? Simply the collection, analysis, interpretation, and presentation of numerical data. Often what occurs is we collect data in the form of a sample: hopefully a representative sample, meaning the data represents the larger population. Statistics is very powerful, allowing us to view trends, differences, and similarities in data. Descriptive statistics summarizes a specific data set. Inferential statistics allows us to make predictions from a data set or data sets (remember we’re hoping our sample is representative of the population). What are some examples? When would we use statistics? How can we do this in Excel? Let’s find out!

b.    Some basic descriptive statistics that will be used often include average (mean), median, and mode of the data set. 

i.     Average is the sum of the data divided by the number data points (for example, if we have a data set of 3,4,4, and 5; the average would be 3+4+4+5=16/4= 4). It can be thought of as a value to show the central tendency of a data set. 

1.    To find average in excel use the formula: =AVERAGE(datastart:dataend).

ii.     Median is the middle value of a set of data ordered from smallest to largest. In our example set, 3,4,4,5; the median would be 4. 

1.    To find median in excel use the formula: =MEDIAN(datastart:dataend).

iii.     Mode is the most frequent number in a data set. For our example set, 3,4,4,5; the mode would be 4. This may or may not be useful for describing our data set. 

1.    To find the mode in excel use the formula: =MODE(datastart:dataend). 

iv.     Standard deviation is a measure of variation in a data set. Usually the smaller the standard deviation, relative to the data set, the more similar the values are in the data set. 

1.    To find the standard deviation in excel use the formula: =STDEV(datastard:dataend). 

c.     Inferential statistics that we’ll cover is the t-test.

i.     T-tests allow us to determine if there’s a statistically significant difference between the average of two data sets.

ii.     Note: there are many different inferential statistic tests that can be performed and there’s an entire field devoted to this, we choose to highlight one of the most utilized in molecular biology: the t-test. 

iii.     To perform a t-test in excel, we first need to see if we have the Data Analysis Toolpack installed. 

1.    Click on “Data” tab at the top of the window. 

2.    Look for “Data Analysis” in the Analyze section (usually to the right of the window). 

3.    Don’t have it? No worries, click on “File” then “Options” and “Addins”. Search for “Analysis Tookpack” click on it then “Ok”. 

iv.     Select “Data” then “Data Analysis”, scroll down and select “t-test: Two-Sample Assuming Equal Variance” if our variance is similar in the data we’re comparing (Figure 3). 

1.    For the sake of our example and the data we’ll be working with in our workshop, this is the best option. 

v.     We’ll now be able to select each variable range and set our alpha level and where the output of the test will display. 

1.    This is where it pays off to have our data in columns as the t-test will require our data to be presented in column format. 

2.    What’s an alpha level? 

a.    The probability of rejecting the null hypothesis. 

b.    Alpha levels are typically 0.05 but can be set depending on the data and the stringency desired.

3.    What is a null hypothesis? 

a.    For this test, the null hypothesis is the two sets of data we’re comparing have the same mean. 

b.    In general, a null hypothesis is one of no difference between the data sets we’re observing. 

4.    For inferential statistics, we are testing hypotheses. The opposite of the null hypothesis is called the alternative hypothesis and for the t-test states that there is a difference between the means of the two data sets being compared. 

5.    When selecting output, depending on the spacing of the spreadsheet, we may select an entire additional worksheet or simply an output range on the current worksheet.

vi.     T-test output gives us several pieces of information.

1.    We’re given the mean, variance, number of observations, and several important pieces of information related to statistics. 

a.    P-value: arguably the most important piece of information given in the test. 

i.     The p-value tells us, in this case, how likely it is that the average of the two sets of data are the same. 

ii.     The alpha level sets the p-value level in terms of whether we accept or reject the null hypothesis. Note this is arbitrary to some degree, 0.05 is generally used but sometimes smaller values are used. 

iii.     In our case, if the p-value is less than 0.05 then we reject the null hypothesis and the difference between the means is statistically significant. 

b.    One-tail or two-tail p value? Which one should we use? If our alternative hypothesis is about showing a difference between the two then the two tailed p value is appropriate, if we are using a statistical test and hypothesis that implies one is greater than another, we could use the one-tailed p-value. 

6.    Creating Graphs in Microsoft Excel

a.    Data in columns is great, but often we want to visualize our data in a way that appeals to us and potentially, an audience. 

b.    Luckily Microsoft Excel has many different graphing options that we can use to visualize our data. 

c.     Please note that we’re going to cover creating a bar graph, yet there’s so many options in excel. If we ever have specific questions, a great resource is the internet! YouTube has numerous tutorials that go through the entire process of creating specific graphs in Microsoft Excel. **Note: not all these tutorials or information online are accurate, proceed with caution. (Hint: websites that include .edu are often fairly accurate).

d.    Creating a Bar Graph

i.     Click on “Insert” tab in Microsoft Excel. Then click on the icon that looks like a bar graph (see Figure 5). Select “2D Column” and “clustered column”. 

ii.     A small rectangle will now appear in the worksheet. From here, click on “Select Data” to choose which data will be graphed.

iii.     In this pop-up window, we’ll be able to select our data (Figure 6). We’ll want to create separate series for our data. To do this, select the “plus sign” under “Legend Entry: Series”. Then enter the name of the series and for the Y value, select the mean of our cell densities. Complete this step again to add the second set of data, the hydrogen peroxide treated cell densities. Select Ok when ready to graph the data. 

iv.     The graph that’s produced is generally not the best. We’ll want to make some adjustments to help our graph look better. (Figure 7).

1.    Make sure there are titles for both the x and y axis.

a.    This can be adjusted under the “Chart Design” tab. Depending on how the data is represented, we may need to adjust a few settings under “Select Data” as well. 

2.    Units should be included. 

3.    Labels are needed for all displayed data. 

4.    A graph title must be included. 

5.    We can also include error bars and specific labels that designate the statistically significant difference between the graphed data as well. 

6.    **Most changes that can be made will be found under the “Chart Design” tab. 

Conclusion

Microsoft Excel provides a useful program that can not only help us organize and store data but can provide powerful mathematical operations and even statistical analyses. With a few keystrokes and some careful attention to detail we can utilize this program to create nice visual representations of data as well. One thing to note: Microsoft Excel isn’t the only program out there. There are numerous programs devoted to graphing alone that make stellar graphics (we chose Microsoft Excel for its availability and practically ubiquitous implementation).

One of the best ways to learn about Microsoft Excel is to use Microsoft Excel! Below is a file that we can download as a Microsoft Excel to test ourselves!