E. C. Nelson

Article / Updated 11-02-2021

Perhaps the most common data analysis tool that you'll use in Excel is the one for calculating descriptive statistics. To see how this works, take a look at this worksheet. It summarizes sales data for a book publisher. In column A, the worksheet shows the suggested retail price (SRP). In column B, the worksheet shows the units sold of each book through one popular bookselling outlet. You might choose to use the Descriptive Statistics tool to summarize this data set. To calculate descriptive statistics for the data set, follow these steps: Click the Data tab’s Data Analysis command button to tell Excel that you want to calculate descriptive statistics. Excel displays the Data Analysis dialog box. In the Data Analysis dialog box, highlight the Descriptive Statistics entry in the Analysis Tools list and then click OK. Excel displays the Descriptive Statistics dialog box. In the Input section of the Descriptive Statistics dialog box, identify the data that you want to describe. To identify the data that you want to describe statistically: Click the Input Range text box and then enter the worksheet range reference for the data. In the case of the example worksheet, the input range is $A$1:$C$38. Note that Excel wants the range address to use absolute references — hence, the dollar signs. To make it easier to see or select the worksheet range, click the worksheet button at the right end of the Input Range text box. When Excel hides the Descriptive Statistics dialog box, select the range that you want by dragging the mouse. Then click the worksheet button again to redisplay the Descriptive Statistics dialog box. To identify whether the data is arranged in columns or rows: Select either the Columns or the Rows radio button. To indicate whether the first row holds labels that describe the data: Select the Labels in First Row check box. In the case of the example worksheet, the data is arranged in columns, and the first row does hold labels, so you select the Columns radio button and the Labels in First Row check box. In the Output Options area of the Descriptive Statistics dialog box, describe where and how Excel should produce the statistics. To indicate where the descriptive statistics that Excel calculates should be placed: Choose from the three radio buttons here — Output Range, New Worksheet Ply, and New Workbook. Typically, you place the statistics onto a new worksheet in the existing workbook. To do this, simply select the New Worksheet Ply radio button. To identify what statistical measures you want calculated: Use the Output Options check boxes. Select the Summary Statistics check box to tell Excel to calculate statistical measures such as mean, mode, and standard deviation. Select the Confidence Level for Mean check box to specify that you want a confidence level calculated for the sample mean. Note: If you calculate a confidence level for the sample mean, you need to enter the confidence level percentage into the text box provided. Use the Kth Largest and Kth Smallest check boxes to indicate you want to find the largest or smallest value in the data set. After you describe where the data is and how the statistics should be calculated, click OK. Here are the statistics that Excel calculates. Statistic Description Mean Shows the arithmetic mean of the sample data. Standard Error Shows the standard error of the data set (a measure of the difference between the predicted value and the actual value). Median Shows the middle value in the data set (the value that separates the largest half of the values from the smallest half of the values). Mode Shows the most common value in the data set. Standard Deviation Shows the sample standard deviation measure for the data set. Sample Variance Shows the sample variance for the data set (the squared standard deviation). Kurtosis Shows the kurtosis of the distribution. Skewness Shows the skewness of the data set’s distribution. Range Shows the difference between the largest and smallest values in the data set. Minimum Shows the smallest value in the data set. Maximum Shows the largest value in the data set. Sum Adds all the values in the data set together to calculate the sum. Count Counts the number of values in a data set. Largest(X) Shows the largest X value in the data set. Smallest(X) Shows the smallest X value in the data set. Confidence Level(X) Percentage Shows the confidence level at a given percentage for the data set values. Here is a new worksheet with the descriptive statistics calculated.

Article / Updated 10-06-2021

The Data Analysis command provides a tool for calculating moving and exponentially smoothed averages in Excel. Suppose, for sake of illustration, that you’ve collected daily temperature information. You want to calculate the three-day moving average — the average of the last three days — as part of some simple weather forecasting. To calculate moving averages for this data set, take the following steps. To calculate a moving average, first click the Data tab’s Data Analysis command button. When Excel displays the Data Analysis dialog box, select the Moving Average item from the list and then click OK. Excel displays the Moving Average dialog box. Identify the data that you want to use to calculate the moving average. Click in the Input Range text box of the Moving Average dialog box. Then identify the input range, either by typing a worksheet range address or by using the mouse to select the worksheet range. Your range reference should use absolute cell addresses. An absolute cell address precedes the column letter and row number with $ signs, as in $A$1:$A$10. If the first cell in your input range includes a text label to identify or describe your data, select the Labels in First Row check box. In the Interval text box, tell Excel how many values to include in the moving average calculation. You can calculate a moving average using any number of values. By default, Excel uses the most recent three values to calculate the moving average. To specify that some other number of values be used to calculate the moving average, enter that value into the Interval text box. Tell Excel where to place the moving average data. Use the Output Range text box to identify the worksheet range into which you want to place the moving average data. In the worksheet example, the moving average data has been placed into the worksheet range B2:B10. (Optional) Specify whether you want a chart. If you want a chart that plots the moving average information, select the Chart Output check box. (Optional) Indicate whether you want standard error information calculated. If you want to calculate standard errors for the data, select the Standard Errors check box. Excel places standard error values next to the moving average values. (The standard error information goes into C2:C10.) After you finish specifying what moving average information you want calculated and where you want it placed, click OK. Excel calculates moving average information. Note: If Excel doesn't have enough information to calculate a moving average for a standard error, it places the error message into the cell. You can see several cells that show this error message as a value.

Article / Updated 07-26-2019

You can move beyond the visual regression analysis that the scatter plot technique provides. You can use Excel’s Regression tool provided by the Data Analysis add-in. For example, say that you used the scatter plotting technique, to begin looking at a simple data set. You can then create a scatterplot in excel. And, after that initial examination, suppose that you want to look more closely at the data by using full blown, take-no-prisoners, regression. To perform regression analysis by using the Data Analysis add-in, do the following: Tell Excel that you want to join the big leagues by clicking the Data Analysis command button on the Data tab. When Excel displays the Data Analysis dialog box, select the Regression tool from the Analysis Tools list and then click OK. Excel displays the Regression dialog box. Identify your Y and X values. Use the Input Y Range text box to identify the worksheet range holding your dependent variables. Then use the Input X Range text box to identify the worksheet range reference holding your independent variables. Each of these input ranges must be a single column of values. For example, if you want to use the Regression tool to explore the effect of advertisements on sales, you enter $A$1:$A$11 into the Input X Range text box and $B$1:$B$11 into the Input Y Range text box. If your input ranges include a label, select the Labels check box. (Optional) Set the constant to zero. If the regression line should start at zero — in other words, if the dependent value should equal zero when the independent value equals zero — select the Constant Is Zero check box. (Optional) Calculate a confidence level in your regression analysis. To do this, select the Confidence Level check box and then (in the Confidence Level text box) enter the confidence level you want to use. Select a location for the regression analysis results. Use the Output Options radio buttons and text boxes to specify where Excel should place the results of the regression analysis. To place the regression results into a range in the existing worksheet, for example, select the Output Range radio button and then identify the range address in the Output Range text box. To place the regression results someplace else, select one of the other option radio buttons. Identify what data you want returned. Select from the Residuals check boxes to specify what residuals results you want returned as part of the regression analysis. Similarly, select the Normal Probability Plots check box to add residuals and normal probability information to the regression analysis results. Click OK. Excel shows a portion of the regression analysis results including three, stacked visual plots of data from the regression analysis. There is a range that supplies some basic regression statistics, including the R-square value, the standard error, and the number of observations. Below that information, the Regression tool supplies analysis of variance (or ANOVA) data, including information about the degrees of freedom, sum-of-squares value, mean square value, the f-value, and the significance of F. Beneath the ANOVA information, the Regression tool supplies information about the regression line calculated from the data, including the coefficient, standard error, t-stat, and probability values for the intercept — as well as the same information for the independent variable, which is the number of ads. Excel also plots out some of the regression data using simple scatter charts.

Article / Updated 05-09-2017

After you place information in an Excel table, you'll find it very easy to sort the records. You can use the Sort & Filter button’s commands to sort and organize your information. Using the Sort buttons To sort table information by using a Sort & Filter button’s commands, click in the column you want to use for your sorting. For example, to sort a grocery list by the store, click a cell in the Store column. After you select the column you want to use for your sorting, click the Sort & Filter button and choose the Sort A to Z command from the menu Excel displays to sort table records in ascending, A-to-Z order using the selected column’s information. Alternatively, choosing the Sort Z to A command from the menu Excel displays sort table records in descending, Z-to-A order using the selected column’s information. Using the Custom Sort dialog box When you can’t sort table information exactly the way you want by using the Sort A to Z and Sort Z to A commands, use the Custom Sort command. To use the Custom Sort command, follow these steps: Click a cell inside the table. Click the Sort & Filter button and choose the Sort command from the Sort & Filter menu. Excel displays the Sort dialog box. In Excel 2007 and Excel 2010, choose the Data→Custom Sort command to display the Sort dialog box. Select the first sort key. Use the Sort By drop-down list to select the field that you want to use for sorting. Next, choose what you want to use for sorting: values, cell colors, font colors, or icons. If you choose to sort by values, you’ll also need to indicate whether you want records arranged in ascending or descending order by selecting either the ascending A to Z or descending Z to A entry. Ascending order, predictably, alphabetizes labels and arranges values in smallest-value-to-largest-value order. Descending order arranges labels in reverse alphabetical order and values in largest-value-to-smallest-value order. If you sort by color or icons, you need to tell Excel how it should sort the colors by using the options that the Order box provides. Typically, you want the key to work in ascending or descending order. However, you might want to sort records by using a chronological sequence, such as Sunday, Monday, Tuesday, and so on, or January, February, March, and so forth. To use one of these other sorting options, select the custom list option from the Order box and then choose one of these other ordering methods from the dialog box. (Optional) Specify any secondary keys. If you want to sort records that have the same primary key with a secondary key, click the Add Level button and then use the next row of choices from the Then By drop-down lists to specify which secondary keys you want to use. If you add a level that you later decide you don’t want or need, click the sort level and then click the Delete Level button. You can also duplicate the selected level by clicking Copy Level. Finally, if you do create multiple sorting keys, you can move the selected sort level up or down in significance by clicking the Move Up or Move Down buttons. Note: The Sort dialog box also provides a My Data Has Headers check box that enables you to indicate whether the worksheet range selection includes the row and field names. If you’ve already told Excel that a worksheet range is a table, however, this check box is disabled. (Really optional) Fiddle-faddle with the sorting rules. If you click the Options button in the Sort dialog box, Excel displays the Sort Options dialog box. Make choices here to further specify how the first key sort order works. For a start, the Sort Options dialog box enables you to indicate whether case sensitivity (uppercase versus lowercase) should be considered. You can also use the Sort Options dialog box to tell Excel that it should sort rows instead of columns or columns instead of rows. You make this specification by using either Orientation radio button: Sort Top to Bottom or Sort Left to Right. Click OK when you’ve sorted out your sorting options. Click OK. Excel then sorts your list.

Article / Updated 04-14-2017

In Excel, you can use the Histogram Data Analysis tool to create a frequency distribution and, optionally, a histogram chart. A frequency distribution shows just how values in a data set are distributed across categories. A histogram shows the same information in a cute little column chart. Here’s an example of how all this works — everything will become clearer if you’re currently confused. To use the Histogram tool, you first need to identify the bins (categories) that you want to use to create a frequency distribution. The histogram plots out how many times your data falls into each of these categories. This worksheet shows bins information in the worksheet range E1:E12. The bins information shows Excel exactly what bins (categories) you want to use to categorize the unit sales data. The bins information shown in the worksheet range E1:E12, for example, create hundred-unit bins: 0-100, 101-200, 201-300, and so on. To create a frequency distribution and a histogram, follow these steps: Click the Data tab’s Data Analysis command button to tell Excel that you want to create a frequency distribution and a histogram. When Excel displays the Data Analysis dialog box, select Histogram from the Analysis Tools list and click OK. In the Histogram dialog box that appears, identify the data that you want to analyze. Use the Input Range text box to identify the data that you want to use to create a frequency distribution and histogram. If you want to create a frequency distribution and histogram of unit sales data, for example, enter the worksheet range $B$1:$B$38 into the Input Range text box. To identify the bins that you use for the frequency distribution and histogram, enter the worksheet range that holds the bins into the Bin Range text box. In the case of the example worksheet, the bin range is $E$1:$E$12. If your data ranges include labels, select the Labels check box. Tell Excel where to place the frequency distribution and histogram. Use the Output Options buttons to tell Excel where it should place the frequency distribution and histogram. To place the histogram in the current worksheet, for example, select the Output Range radio button and then enter the range address into its corresponding Output Range text box. To place the frequency distribution and histogram in a new worksheet, select the New Worksheet Ply radio button. Then, optionally, enter a name for the worksheet into the New Worksheet Ply text box. To place the frequency distribution and histogram information in a new workbook, select the New Workbook radio button. (Optional) Customize the histogram. Make choices from the Output Options check boxes to control what sort of histogram Excel creates. For example, select the Pareto (Sorted Histogram) check box, and Excel sorts bins in descending order. Conversely, if you don't want bins sorted in descending order, leave the Pareto (Sorted Histogram) check box clear. Selecting the Cumulative Percentage check box tells Excel to plot a line showing cumulative percentages in your histogram. Optionally, select the Chart Output check box to have Excel include a histogram chart with the frequency distribution. If you don’t select this check box, you don't get the histogram — only the frequency distribution. Click OK. Excel creates the frequency distribution and, optionally, the histogram. Here is the frequency distribution along with a histogram for the example workbook data. Note: Excel also provides a Frequency function with which you use can use arrays to create a frequency distribution.

Step by Step / Updated 03-27-2016

You typically create a pivot chart by starting with the Create PivotChart Wizard. However, Excel also gives you the option of using the Insert Chart command on an existing pivot table. In Excel 2007 and Excel 2010, you use the PivotTable and PivotChart Wizard to create a pivot chart, but despite the seemingly different name, that wizard is the same as the Create PivotChart Wizard. To run the Create PivotChart Wizard, take the following steps:

Step by Step / Updated 03-27-2016

Excel provides an AutoFilter command that’s pretty cool. When you use AutoFilter, you produce a new table that includes a subset of the records from your original table. For example, in the case of a grocery list table, you could use AutoFilter to create a subset that shows only those items that you’ll purchase at Butchermans or a subset table that shows only those items that cost more than $2. To use AutoFilter on a table, take these steps:

Step by Step / Updated 03-27-2016

You can construct a custom AutoFilter. To do this, select the Text Filter command from the table menu and choose one of its text filtering options. No matter which text filtering option you pick, Excel displays the Custom AutoFilter dialog box. This dialog box enables you to specify with great precision what records you want to appear on your filtered list. To create a custom AutoFilter, take the following steps:

Step by Step / Updated 03-27-2016

Excel provides a powerful method for retrieving information from external databases. You aren't limited to simply grabbing all the information from a specified table. You can, alternatively, query a database. By querying a database, you retrieve only information from a table that matches your criteria. You can also use a query to combine information from two or more tables. Therefore, use a query to massage and filter the data before it's actually placed in your Excel workbook. Querying is often the best approach when you want to combine data before importing it and when you need to filter the data before importing it. To run a database query and import query results, follow these steps:

Step by Step / Updated 03-27-2016

One of the neatest ways to grab external data in Excel is through a web query. As you know if you’ve wasted any time surfing the web, Internet websites provide huge volumes of interesting data. Often, you’d like to grab this data and analyze it in some way. And fortunately, Excel provides an easy way to move such data from a web page into Excel. With the Excel web Query tool, as long as the data that you want to grab or analyze is stored in something that looks like a table — that is, in something that uses rows and columns to organize the information — you can grab the information and place it into an Excel workbook. To perform a web query, follow these steps: