Microsoft Office’s Excel spreadsheet program offers users a great many features for data organization, description and analysis. In this article, we will take a look at some of the features Excel provides users.
The data validation feature helps to verify that the data that are entered are in a proper format or fits within a certain range. This aids in making sure the data are useful. For example, if the category of data is “Child’s Age,” and the age of 10 is the highest value, the data validation feature will display an error message if a value higher than 10 is entered.
The user can set validation rules for many formats of entries, including dates and length of text entries. The error messages are customizable. In addition, an “input message” can be set that will explain the limits on the cell’s entries when the cell is clicked on, such as:
Once data have been entered into Excel, they can be sorted and filtered in about any way the user needs. Below are some examples of sorting and filtering that can be done in Excel. The data consist of the names of six salespeople in the first column and the number of sales they had during some time period in the second column. The initial entry of the data is shown in the figure on the left. The middle figure shows the data sorted from highest to lowest. And the figure on the right shows the data filtered by sales of 10 or more.
As with data validation, there is some customizability of sorting and filtering. Sorting can be done to multiple levels in the order specified by the user. An example of customized filtering is shown below. In this example, the data were filtered so that only those with sales of 7 or more were displayed (figure on left). These data can then be sorted, if needed and as desired, such as from lowest to highest number of sales (figure on right).
Graphs and charts are relatively easy to make in Excel, and the program offers a wide array of chart types with features, such as modifiable axes labels and colors, that can be edited by the user as wanted. Below are samples of the types of charts available under the “Insert” tab. Most of these can be shown in 2D or 3D renderings.
Column Charts (or Histograms): These display categorical data by using vertical bars to depict the value for each category. One of the customizable features is in the display of the actual data values, as shown in the chart below.
Excel can also make bar graphs, which are similar to column charts but use horizontal as opposed to vertical bars.
Pie Charts: Pie charts are used to show percentages of categorical data as parts of a whole. The pie chart below is shown in 3D style and includes, in the label, the raw number and percentage for each category (salesperson) with the legend to the right of the chart. The type of labels and the placement of the labels and the legend can be selected by the user.
Scatter Plots and Line Graphs: These are used to show changes in data over time. Multiple sets of data can be shown on one graph, as seen in the figure below. As with the other types of charts, scatter plots and line graphs can be customized.
Oftentimes, more than one type of graph or chart can convey the data in a visual way. The researcher presenting the data should choose the most appropriate, clear and accurate display. Excel even allows for combining two types of charts, depending on the data.
Describing and Analyzing Data
Some of the tools in Excel provide descriptive and inferential statistics. Descriptive statistics, including the measures of central tendency and variability, can be found using individual functions (e.g., MEAN, MEDIAN, STDEV) on a set of data. However, Excel has a data analysis add-in that includes a selection that will display the descriptive statistics with that one selection. The figure below shows the descriptive statistics for a set of data with 15 values.
As for inferential statistics, the data analysis package allows for carrying out several statistical tests (e.g., F-test, t-test, z-test), correlation, confidence intervals, Fourier analysis, analysis of variance, and regression among other things. In the figure below, the results of running a t-test to compare the means of two sets of test scores are shown.
Other inferential statistical tests, such the Chi-square test, are available through the regular Excel functions.
As you use Excel to organize, display and analyze data, explore the variety of features the program offers. For details on using features, use the Help button and search the Internet. For more powerful statistical tests, such as nonparametric tests, you will need to use a full statistical package. If you are interested in developing a deeper understanding of programs such as Excel, you may want to consider a degree in data science or data analytics.