Chapter 1: Understanding PivotTables and PivotCharts
Understanding Data Analysis
The PivotTables and PivotCharts that you learn about in this book are part of the larger category of data analysis. You can get the most out of these tools if you have a broader understanding of what data analysis is, what its benefits are, and what other tools are available to you.
Data analysis is the application of tools and techniques to organize, study, reach conclusions and sometimes also make predictions about a specific collection of information. A sales manager might use data analysis to study the sales history of a product, determine the overall trend, and produce a forecast of future sales. A scientist might use data analysis to study experimental findings and determine the statistical significance of the results. A family might use data analysis to find the maximum mortgage it can afford or how much it must put aside each month to finance retirement or the kids’ education.
The point of data analysis is to understand information on some deeper, more meaningful level. By definition, raw data is a mere collection of facts that by themselves tell you little or nothing of any importance. To gain some understanding of the data, you must manipulate it in some meaningful way. This can be something as simple as finding the sum or average of a column of numbers or as complex as employing a full-scale regression analysis to determine the underlying trend of a range of values. Both are examples of data analysis, and Excel offers a number of tools — from the straightforward to the sophisticated — to meet even the most demanding needs.
Data
The “data” part of data analysis is a collection of numbers, dates, and text that represents the raw information you have to work with. In Excel, this data resides inside a worksheet and you get it there in one of two ways: You enter it by hand or you import it from an external source. You can then either leave the data as a regular range, or you can convert it into a table for easier data manipulation.
Data Entry
In many data analysis situations, the required data must be entered into the worksheet manually. For example, if you want to determine a potential monthly mortgage payment, you must first enter values such as the current interest rate, the principal, and the term. Manual data entry is suitable for small projects only, because entering hundreds or even thousands of values is time consuming and can lead to errors.
Imported Data
Most data analysis projects involve large amounts of data, and the fastest and most accurate way to get that data onto a worksheet is to import it from a non-Excel data source. In the simplest scenario, you can copy the data — from a text file, a Word table, or an Access datasheet — and then paste it into a worksheet. However, most business and scientific data is stored in large databases, and Excel offers tools to import the data you need into your worksheet. See Appendixes B and C for more about these tools.
Table
After you have your data in the worksheet, you can leave it as a regular range and still apply many data analysis techniques to the data. However, if you convert the range into a table, Excel treats the data as a simple flat-file database and enables you to apply a number of database-specific analysis techniques to the table. To learn how to do this, see Chapter 2.
Data Models
In many cases, you perform data analysis on worksheet values by organizing those values into a data model, a collection of cells designed as a worksheet version of some real-world concept or scenario. The model includes not only the raw data, but also one or more cells that represent some analysis of the data. For example, a mortgage amortization model would have the mortgage data — interest rate, principal, and term — and cells that calculate the payment, principal, and interest over the term. For such calculations, you use formulas and Excel’s built-in functions, as described in Appendix A.
Formulas
A formula is a set of symbols and values that perform some kind of calculation and produce a result. All Excel formulas have the same general structure: an equals sign (=) followed by one or more operands — which can be a value, a cell reference, a range, a range name, or a function name — separated by one or more operators, which are the symbols that combine the operands in some way, such as the plus sign (+) and the multiplication sign (*). For example, the formula =A1+A2 adds the values in cells A1 and A2.
Functions
A function is a predefined formula that is built in to Excel. Each function takes one or more inputs — called arguments, such as numbers or cell references — and then returns a result. Excel offers hundreds of functions and you can use them to compute averages, determine the future value of an investment, compare values, and much more.
What-If Analysis
One of the most common data analysis techniques is what-if analysis, where you set up worksheet models to analyze hypothetical situations. The what-if part comes from the fact that these situations usually come in the form of a question: “What happens to the monthly payment if the interest rate goes up by 2 percent?” “What will the sales be if you increase the advertising budget by 10 percent?” Excel offers four what-if analysis tools: data tables, Goal Seek, Solver, and scenarios.
Data Tables
A data table is a range of cells where one column consists of a series of values, called input cells. You can then apply each of those inputs to a single formula, and Excel displays the results for each case. For example, you can use a data table to apply a series of interest rate values to a formula that calculates the monthly payment for a loan or mortgage.
Goal Seek
You use Excel’s Goal Seek tool when you want to manipulate one formula component — called the changing cell — in such a way that the formula produces a specific result. For example, in a break-even analysis, you determine the number of units of a product that you must sell for the profit to be 0. Given a formula that calculates profit, you can use Goal Seek to determine the break-even point.
Solver
You use Excel’s Solver tool when you want to manipulate multiple formula components — called the changing cells — in such a way that the formula produces the optimal result. For example, you can use Solver to tackle the so-called transportation problem, where the goal is to minimize the cost of shipping goods from several product plants to various warehouses around the country.
Scenarios
A scenario is a collection of input values that you plug into formulas within a model to produce a result. The idea is that you make up scenarios for various situations — for example, best-case, worst-case, and so on — and Excel’s Scenario Manager saves each one. Later you can apply any of the saved scenarios, and Excel automatically applies all the input...