Pivot Table Data Crunching

Note: Supplemental materials are not guaranteed with Rental or Used book purchases.

ISBN: 9780789734358 | 0789734354
Cover: Paperback
Copyright: 6/21/2005

eBook

Available Instantly

Online: 1825 Days

Downloadable: Lifetime Access

$36.21

Become a savvy Microsoft Excel user. Pivot tables are a great feature in Excel that help you organize and analyze data, but not many Excel users know how to use pivot tables.Pivot Table Data Crunchingoffers a comprehensive review of all the functionalities of Pivot Tables from authorBill Jelen, otherwise known asMr. Excelfrom www.mrexcel.com, andMichael Alexander, a Microsoft Certified Application Developer. The authors' practical scenarios and real-world advice demonstrate the benefits of Pivot Tables and how to avoid the common pitfalls of every day data crunching. Each solution presented in the book can be accomplished with resources available in the Excel interface, makingPivot Table Data Crunchinga beneficial resource for all levels of Excel users.

Introduction

(8)

Pivot Table Fundamentals

(8)

What Is a Pivot Table?

(1)

Why Should You Use a Pivot Table?

(1)

When Should You Use a Pivot Table?

(1)

The Anatomy of a Pivot Table

(2)

Data Area

(1)

Row Area

(1)

Column Area

(1)

Page Area

(1)

Pivot Tables Behind the Scenes

(1)

Limitations of Pivot Table Reports

(1)

Next Steps

(2)

Creating a Basic Pivot Table

(18)

Preparing Your Data for Pivot Table Reporting

(5)

Ensure Your Data Is in a Tabular Layout

(1)

Use Unique Headings That Occupy Only a Single Row of Data

(1)

Avoid Storing Data in Section Headings

(1)

Avoid Repeating Groups as Columns

(1)

Eliminate Gaps and Blank Cells in Your Data Source

(1)

Apply Appropriate Type Formatting to Your Fields

(1)

Summary of Good Data Source Design

(1)

Cleaning Up Data for Pivot Table Analysis

(1)

Creating a Basic Pivot Table

(11)

Introduction to the PivotTable Wizard

(2)

Drag Fields to the Report

(1)

Adding Fields to the Pivot Table

(1)

Rearranging the Pivot Table

(1)

Revenue by Market and Model

(4)

Watch the Mouse Pointer to Learn Where You Are Dropping a Field

(1)

Redisplay the Pivot Table Field List

(1)

Redisplay the Pivot Table Toolbar

(1)

Activate the PivotTable Wizard

(1)

Keeping Up with Changes in Your Data Source

(1)

Changes Have Been Made to Your Existing Data Source

(1)

Your Data Source's Range Has Been Expanded with the Addition of Rows or Columns

(1)

Next Steps

(1)

Customizing Fields in a Pivot Table

(20)

The Need to Customize

(1)

Displaying the PivotTable Field Dialog Box

(2)

Customizing Field Names

(1)

Applying Numeric Formats to Data Fields

(1)

Changing Summary Calculations

(2)

One Blank Cell Causes a Count

(1)

Using Functions Other Than Count or Sum

(2)

Adding and Removing Subtotals

(2)

Suppress Subtotals When You Have Many Row Fields

(1)

Adding Multiple Subtotals for One Field

(1)

Using Running Total Options

(10)

Display Change from Year to Year with Difference From

(2)

How Much Does Each Line of Business Contribute to the Total?

(1)

Seasonality Reports

(3)

Revenue by Line of Business Report

(4)

Next Steps

(2)

Formatting Your Pivot Table Report

(16)

Using AutoFormat

(3)

Applying Your Own Style

(3)

Setting Table Options

(9)

Grand Totals for Columns

(1)

Grand Totals for Rows

(1)

AutoFormat Table

(1)

Subtotal Hidden Page Items

(1)

Merged Labels

(1)

Preserve Formatting

(1)

Repeat Item Labels on Each Printed Page

(1)

Mark Totals with*

(1)

Page Layout

(1)

For Error Values Show

(1)

For Empty Cells Show

(1)

Set Print Titles

(1)

Formatting a PivotTable

(2)

Next Steps

(1)

Controlling the Way You View Your Pivot Data

(30)

Showing and Hiding Options

(8)

The Basics of Hiding an Item

(2)

Showing All Items Again

(1)

Showing or Hiding Most Items

(2)

Hiding or Showing Items Without Data

(2)

Hiding or Showing Items in a Page Field

(1)

Showing or Hiding Items in a Data Field

(1)

Sorting in a Pivot Table

(9)

Sorting Using the Advanced Options Dialog Box

(2)

Note the Effect of Layout Changes on AutoSort

(1)

Sorting Using the Manual Method

(2)

Sorting Using the Sorting Buttons on the Standard Toolbar

(3)

Producing Top 10 Reports

(3)

Grouping Pivot Fields

(8)

Grouping Date Fields

(2)

When Grouping by Months, Include Years

(1)

Grouping Date Fields by Week

(1)

Grouping Two Date Fields in One Report

(1)

Order Lead-Time Report

(1)

Grouping Numeric Fields

(1)

Grouping Text Fields

(2)

Grouping and Ungrouping

(1)

Next Steps

(2)

Performing Calculations Within Your Pivot Tables

101

(22)

Introducing Calculated Fields and Calculated Items

101

(3)

Method 1: Manually Add the Calculated Field to Your Data Source

102

(1)

Method 2: Use a Formula Outside of Your Pivot Table to Create the Calculated Field

103

(1)

Method 3: Insert a Calculated Field Directly into Your Pivot Table

103

(1)

Creating Your First Calculated Field

104

(7)

Summarizing Next Year's Forecast

107

(4)

Creating Your First Calculated Item

111

(6)

Creating a Mini-Dashboard

113

(4)

Rules and Shortcomings of Pivot Table Calculations

117

(3)

Order of Operator Precedence

117

(1)

Cell References and Named Ranges

118

(1)

Worksheet Functions

118

(1)

Constants

118

(1)

Referencing Totals

118

(1)

Rules Specific to Calculated Fields

118

(2)

Rules Specific to Calculated Items

120

(1)

Managing and Maintaining Your Pivot Table Calculations

120

(2)

Editing and Deleting Your Pivot Table Calculations

120

(1)

Changing the Solve Order or Your Calculated Items

121

(1)

Documenting Your Formulas

121

(1)

Next Steps

122

(1)

Creating and Using Pivot Charts

123

(18)

What Is a Pivot Chart Really?

123

(1)

Creating Your First Pivot Chart

123

(3)

Rules and Limitations of Pivot Charts

126

(9)

Pivot Chart Layout Optimization

127

(2)

Scatter, Bubble, and Stock Charts Off Limits

129

(1)

Limitations on Element Size and Location

129

(1)

Certain Customizations Aren't Permanent

129

(1)

Create a Dynamic Year-Over-Year Chart

130

(5)

Alternatives to Using Pivot Charts

135

(4)

Avoiding Overhead

135

(1)

Avoid the Formatting Limitations of Pivot Charts

136

(3)

Next Steps

139

(2)

Using Disparate Data Sources for Your Pivot Table

141

(14)

Working with Disparate Data Sources

141

(1)

Using Multiple Consolidation Ranges

142

(4)

The Anatomy of a Multiple Consolidation Range Pivot Table

146

(5)

The Row Field

146

(1)

The Column Field

146

(1)

The Value Field

147

(1)

The Page Fields

147

(1)

Redefining Your Pivot Table

148

(1)

Consolidate and Analyze Eight Datasets

148

(3)

Creating a Pivot Table from an Existing Pivot Table

151

(2)

Next Steps

153

(2)

Using External Data Sources for Your Pivot Table

155

(16)

Building a Pivot Table Using

External Data Sources

155

(1)

Working Around Excel's Data Management Limitations

155

(1)

About MS Query

156

(1)

Analyze a Dataset with More Than 83,000 Records with a Pivot Table

157

(4)

Importing and Using External Data Without the PivotTable Wizard

161

(1)

Creating Dynamic PivotTable Reporting Systems

161

(7)

Create a Standalone Dynamic Pivot Table Reporting System

162

(6)

Pivot Table Data Options

168

(1)

Next Steps

169

(2)

Leveraging the Power of OLAP Cubes

171

(14)

Defining OLAP?

171

(1)

Benefits of OLAP Cubes

171

(1)

Introduction to Data Warehouses and OLAP Cubes

172

(3)

Operational Data

172

(1)

Warehousing Your Data

173

(1)

Enter the Cube

174

(1)

Cubes Offer Prebuilt Data Views

174

(1)

Connecting to an OLAP Cube

175

(3)

Make the Connection to a Local Cube

175

(2)

Make the Connection to a Server Cube

177

(1)

Working with an OLAP Pivot Table

178

(3)

Arranging the Data

178

(1)

Drilling Into the Cube

179

(1)

Using Page Fields

180

(1)

Comparing OLAP Cubes' Pivot Tables to Excel Data

181

(2)

OLAP Handles More Data, Faster

181

(1)

Dimensions or Measures

181

(1)

OLAP Measures Are Already Grouped

182

(1)

Drill-Through of OLAP Data

182

(1)

Calculated Fields with OLAP

182

(1)

Other Pivot Table Features Operate the Same

182

(1)

Other Considerations When Using OLAP Cubes

183

(1)

Viewing an OLAP Cube Online

183

(1)

Writing Back to a Cube

183

(1)

Setting Actions in a Cube

183

(1)

Combining Cubes

183

(1)

Building a Local Cube

183

(1)

Next Steps

184

(1)

Enhancing Your Pivot Table Reports with Macros

185

(14)

Why Use Macros with Your Pivot Table Reports?

185

(1)

Recording Your First Macro

186

(1)

Creating a User Interface with Form Controls

187

(2)

Altering a Recorded Macro to Add Functionality

189

(8)

Synchronize Two Pivot Tables with One Combo Box

193

(4)

Next Steps

197

(2)

Using VBA to Create Pivot Tables

199

(46)

Introduction to VBA

199

(2)

Enable VBA in Your Copy of Excel

199

(1)

Visual Basic Editor

199

(1)

Visual Basic Tools

200

(1)

The Macro Recorder

201

(1)

Understanding Object-Oriented Code

201

(1)

Tricks of the Trade

201

(2)

Write Code to Handle Any Size Data Range

201

(2)

Use Super-Variables --- Object Variables

203

(1)

Versions

203

(1)

Build a Pivot Table in Excel VBA

203

(7)

Getting a Sum Instead of a Count

205

(2)

Cannot Move or Change Part of a Pivot Report

207

(1)

Size of a Finished Pivot Table

207

(3)

Revenue by Model for a Product Line Manager

210

(4)

Eliminate Blank Cells in the Data Area

212

(1)

Control the Sort Order with AutoSort

212

(1)

Default Number Format

212

(1)

Suppress Subtotals for Multiple Row Fields

213

(1)

Suppress Grand Total for Rows

214

(1)

Handle Additional Annoyances

214

(6)

New Workbook to Hold the Report

214

(1)

Summary on a Blank Report Worksheet

215

(1)

Fill Outline View

216

(1)

Final Formatting

216

(1)

Add Subtotals

217

(1)

Put It All Together

218

(2)

Issues with Two or More Data Fields

220

(6)

Calculated Data Fields

222

(2)

Calculated Items

224

(2)

Summarize Date Fields with Grouping

226

(4)

Group by Week

228

(2)

Advanced Pivot Table Techniques

230

(9)

AutoShow Feature to Produce Executive Overviews

230

(3)

ShowDetail to Filter a Recordset

233

(2)

Create Reports for Each Region or Model

235

(3)

Manually Filter Two or More Items in a PivotField

238

(1)

Control the Sort Order Manually

239

(1)

Sum, Average, Count, Min, Max, and More

239

(1)

Report Percentages

240

(3)

Percentage of Total

240

(1)

Percentage Growth from Previous Month

241

(1)

Percentage of a Specific Item

241

(1)

Running Total

241

(1)

Special Considerations for Excel 97

242

(1)

Next Steps

243

(2)

A Solutions to Common Questions and Issues with Pivot Tables

245

(18)

What does ``The PivotTable field name is not valid'' mean?

246

(1)

Problem

246

(1)

Solution

246

(1)

When I refresh my pivot table, my data disappears

246

(1)

Problem

246

(1)

Solution

246

(1)

When I try to group a field, I get an error message

246

(1)

Problem

246

(1)

Solution

247

(1)

Why can't I group my month fields into quarters?

247

(2)

Problem

247

(1)

Solution

248

(1)

My pivot table is showing the same data item twice

249

(1)

Problem

249

(1)

Solution

249

(1)

Why are deleted data items still showing up in the page field?

250

(1)

Problem

250

(1)

Solution

250

(1)

When I type a formula referencing a pivot table, I cannot copy the formula down

251

(1)

Problem

251

(1)

Solution

251

(1)

How can I sort data items in a unique order that is not ascending or descending?

251

(1)

Problem

251

(1)

Solution

252

(1)

How do I turn my pivot table into hard data?

252

(1)

Problem

252

(1)

Solution

252

(1)

Is there an easy way to fill the empty cells left by row fields?

252

(2)

Problem

252

(1)

Solution

253

(1)

Is there an easy way to fill the empty cells left by row fields in multiple columns?

254

(2)

Problem

254

(1)

Solution

255

(1)

How do I add a rank number field to my pivot table?

256

(2)

Problem

256

(1)

Solution

256

(2)

Why does my pivot chart exclude months for certain data items?

258

(1)

Problem

258

(1)

Solution

258

(1)

Can I create a pivot chart on the same sheet as my pivot table?

259

(1)

Problem

259

(1)

Solution

259

(1)

How can I turn my pivot table report into an interactive web page?

259

(4)

Problem

259

(1)

Solution

260

(3)

Index

263

Please wait while the item is added to your bag...

FREE SHIPPING

Amazon no longer offers textbook rentals. We do!

Pivot Table Data Crunching

Summary

Author Biography

Table of Contents

Supplemental Materials