5 Chapter 5FIGURE 5-1: A table and its criteria range.FIGURE 5-2: Use DGET to retrieve a value from a table based on your criteria.FIGURE 5-3: Use DSUM to add a column’s values based on your criteria.FIGURE 5-4: Use DCOUNT (or DCOUNTA) to tally a column's values based on your cr...FIGURE 5-5: Use DAVERAGE to average a column’s values based on your criteria.FIGURE 5-6: Use DMAX and DMIN to return a column's largest and smallest values ...FIGURE 5-7: Use DPRODUCT to multiply a column’s values based on your criteria.FIGURE 5-8: Use DSTDEV (or DSTDEVP) to derive the standard deviation of a colum...FIGURE 5-9: Use DVAR (or DVARP) to derive the variance of a column’s values bas...
6 Chapter 6FIGURE 6-1: Some great data, but how do you make sense of it?FIGURE 6-2: The PivotTable creates order out of data chaos.FIGURE 6-3: The features of a typical PivotTable.FIGURE 6-4: You start with a blank PivotTable and the PivotTable Fields task pa...FIGURE 6-5: The features of a typical PivotTable.FIGURE 6-6: Import the external data to a PivotTable Report.FIGURE 6-7: Select Refresh Data When Opening the File to tell Excel to refresh ...FIGURE 6-8: The Grouping dialog box.FIGURE 6-9: From the filter field’s drop-down list, select a report filter.FIGURE 6-10: Excel filters the PivotTable to show just the selected items in ea...
7 Chapter 7FIGURE 7-1: Use the Value Field Settings dialog box to choose a summary calcula...FIGURE 7-2: A PivotTable that shows sales in two years: 2021 and 2022.FIGURE 7-3: Use the Value Field Settings dialog box to choose a summary calcula...FIGURE 7-4: The PivotTable from Figure 7-2 is now using the Difference From cal...FIGURE 7-5: A PivotTable that shows quarterly sales by region.FIGURE 7-6: The PivotTable from Figure 7-5, now using the % Of calculation.FIGURE 7-7: A PivotTable showing monthly order totals.FIGURE 7-8: The PivotTable from Figure 7-7, with the Running Total In calculati...FIGURE 7-9: A PivotTable showing units sold by category and region.FIGURE 7-10: The PivotTable from Figure 7-9, with the Index calculation applied...FIGURE 7-11: A PivotTable with multiple subtotals.FIGURE 7-12: A custom calculated field, ready for insertion into the PivotTable...FIGURE 7-13: The custom calculated field in action.FIGURE 7-14: A custom calculated item, ready for action.FIGURE 7-15: Two custom calculated items added to the row area.
8 Chapter 8FIGURE 8-1: The major sights to see in the PivotChart landscape.FIGURE 8-2: Excel kicks things off with a blank PivotTable and PivotChart, and ...FIGURE 8-3: An embedded PivotChart and its PivotTable.FIGURE 8-4: Use the Move Chart dialog box to move a PivotChart to another works...FIGURE 8-5: Pull down the Report Filter field list and then select an item.FIGURE 8-6: The PivotChart from Figure 8-5 now displayed as a pie chart.FIGURE 8-7: A data table shown below a PivotChart.
9 Chapter 9FIGURE 9-1: In the Orders table, the first record has an Order ID value of 1024...FIGURE 9-2: In the Order Details table, the first three records have an Order I...FIGURE 9-3: In the Create Relationship dialog box, relate your two tables by us...FIGURE 9-4: You can click Select Related Tables to have Excel figure out which ...FIGURE 9-5: When you import multiple, related tables, you can opt to create onl...FIGURE 9-6: Be sure to select the Add This Data to the Data Model check box.FIGURE 9-7: A PivotTable that summarizes data from two related tables: Orders a...FIGURE 9-8: Excel displays this message if you try to add a field from an unrel...FIGURE 9-9: Excel can automatically detect the required relationship.FIGURE 9-10: Click Select Related Tables and Power Pivot will automatically sel...FIGURE 9-11: Visualize your Data Model tables and relationships with diagram vi...FIGURE 9-12: See the details of your Data Model relationships by using the Mana...FIGURE 9-13: The Data Refresh dialog box shows you the progress of the refresh.
10 Chapter 10FIGURE 10-1: A chart with a trend line and some geeky numbers.FIGURE 10-2: An array of best-fit trend values (column F).FIGURE 10-3: A best-fit trend line, extended four periods into the future.FIGURE 10-4: The Series dialog box: your best-fit trend best friend.FIGURE 10-5: An array of forecasted trend values (see the range D14:D17).FIGURE 10-6: A chart with an exponential trend line.FIGURE 10-7: An array of exponential trend values (column C) and forecast value...FIGURE 10-8: A chart with a logarithmic trend line.FIGURE 10-9: A chart with a power trend line.FIGURE 10-10: A chart with a polynomial trend line.FIGURE 10-11: You use the Create Forecast Worksheet dialog box to set up your f...FIGURE 10-12: The new forecast worksheet showing a table and line chart for the...
11 Chapter 11FIGURE 11-1: COUNT returns the tally of the numeric values in a range.FIGURE 11-2: A parts database.FIGURE 11-3: MEDIAN returns the median value of a set of numeric values.FIGURE 11-4: RANK returns the ranking of a value in a set.FIGURE 11-5: LARGE returns the nth largest value in a range or array.FIGURE 11-6: FREQUENCY tells you how many items in a range appear in each bin.FIGURE 11-7: VAR.P returns the variance of data that represents an entire popul...FIGURE 11-8: CORREL calculates the correlation between two sets of values.
12 Chapter 12FIGURE 12-1: Loading the Analysis ToolPak add-in.FIGURE 12-2: The Analysis ToolPak’s tools appear in the Data Analysis dialog bo...FIGURE 12-3: The Descriptive Statistics dialog box, ready to run.FIGURE 12-4: The statistics generated by the Descriptive Statistics tool.FIGURE 12-5: The Moving Average dialog box, good to go.FIGURE 12-6: Some moving averages generated by the Moving Average tool.FIGURE 12-7: A completed version of the Rank and Percentile dialog box.FIGURE 12-8: The product defects with their respective ranks and percentiles.FIGURE 12-9: The parameters change depending on the Distribution value.FIGURE 12-10: Random test scores generated by the Random Number Generation tool...FIGURE 12-11: A worksheet with student grades and a bin range for the distribut...FIGURE 12-12: The Histogram dialog box, all dressed up.FIGURE 12-13: A frequency distribution and histogram for some student grades.
13 Chapter 13FIGURE 13-1: A table of invoice data from which you might select a sample.FIGURE 13-2: The Sampling dialog box, ready to sample.FIGURE 13-3: The sample extracted by the Sampling tool.FIGURE 13-4: Some sample data you can use to perform t-test calculations.FIGURE 13-5: The t-Test: Two-Sample Assuming Equal Variances dialog box.FIGURE 13-6: The results of a t-test.FIGURE 13-7: The z-Test: Two Sample for Means dialog box.FIGURE 13-8: The z-test calculation results.FIGURE 13-9: The completed Regression dialog box.FIGURE 13-10: The regression analysis results.FIGURE 13-11: The completed Correlation dialog box.FIGURE 13-12: The worksheet showing the correlation results for the list price ...FIGURE 13-13: The completed Covariance dialog box.FIGURE 13-14: The worksheet showing the covariance results for the list price a...FIGURE 13-15: The completed Anova: Single Factor dialog box.FIGURE 13-16: The worksheet showing the Anova results for the list price and un...FIGURE 13-17: The completed F-Test Two-Sample for Variances dialog box.FIGURE 13-18: The worksheet showing the F-Test Two-Sample for Variances results...
14 Chapter 14FIGURE 14-1: A standard normal distribution.FIGURE 14-2: A uniform distribution generated by 60,000 dice rolls.FIGURE 14-3: Data sets that are negatively skewed (left) and positively skewed ...FIGURE 14-4: Data sets that have negative kurtosis (left) and positive kurtosis...
15 Chapter 16FIGURE 16-1: Click Tools to see the PivotTable Fields task pane layout options.FIGURE 16-2: You've sold 792 earbuds, but what are the details behind this numb...FIGURE 16-3: The details behind the earbud sales shown in Figure 16-2.FIGURE 16-4: Use the New PivotTable Style dialog box to define your custom styl...FIGURE 16-5: The GETPIVOTDATA function doing its thing.
Guide
1 Cover
4 Table of Contents
6 Appendix: Glossary of Data Analysis and Excel Terms
7 Index