E. C. Nelson

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.

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.

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.

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.

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.

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.

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.

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.

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.