Statistics in Spreadsheets

Microsoft office contains multiple applications that altogether serve as a complete information management system. A spreadsheet is among those tools and is very important when the analysis of data is required. A spreadsheet is defined as a large sheet that contains data and information arranged in rows and columns. Statistical features of Spreadsheets are today a huge asset as many large multinational companies and research institutions use them for organization, categorization, and analysis of their financial data. There are multiple tools through which we can statistically analyze the data. Tools that facilitate working with statistics in spreadsheets are :

• Pre-Defined Mathematical functions like average, max, min, mean, mode, etc.
• 2D and 3D charts of various types like line charts, pie charts, bar charts, etc.

Mathematical Functions in Spreadsheets 

Microsoft Excel supports a wide range of statistical functions through which we can perform various operations like means, mode, average, etc of the given data. Some of the commonly used statistical functions are as follows:

Example:

In this example, we are finding the average of the following given data: 

Here, we use the AVERAGE(C2:C7) formula to find the average of 2nd test. Similarly, we can use other functions. 

Obtaining Charts in Spreadsheets 

We can also use charts to represent our statistical data in graphic form. Or we can say that a chart is basically a diagram through which we can plot information such that it is easier to perceive and analyze different relationships between data points. The spreadsheets allow us to embed charts of various types for better data visualization. 

For using charts one just needs to select the data cells that need to be plotted into a chart and then choose the chart of their choice. We can customize various properties related to charts such as the color, title of the chart, adjustment of size, etc. as per our requirements as well. The general instructions to create a chart for any given data are as follows:

• Select the data for which the chart has to be created.
• Go to INSERT > Recommended Charts. There a dialog box appears that contains recommended charts for given data as well as All Charts that are available.
• Select the one which you feel is most suitable for information and click OK. The chart will be presented in the worksheet area.

Type of Charts

The spreadsheet supports various types of charts:

1. Line Chart: A line chart that shows the variation of some data by sequentially connected points.

2. Bar Chart: This is another kind of Chart where data is represented in form of rectangular bars with height being proportional to the cell value. 

3. Scatter Plot: This plot is used to compare at least two sets of values of pair of data. 

4. Pie Chart: In a pie chart any information is presented as proportions to the whole data. For example - A person purchased 30 mangoes, 40 apples, and 10 bananas then a pie chart would present this information in the given fashion.

There are also many other kinds of charts besides the above that have different use cases such as hierarchy charts and pivot charts. Each and every one of these charts also supports 3D diagrams as well.