Excel PivotTables and Charts: Your visual blueprint for creating dynamic spreadsheets uses clear, descriptive examples to show you how to use Excel PivotTables and PivotCharts to analyze, summarize, and visualize large amounts of data. If you have never used a PivotTable or a PivotChart, this book will show you how powerful and useful they can be. As well, this book will give you step-by-step, visual guidance on how to create PivotTables and PivotCharts and how to customize them to suit your needs. If you are already familiar with Excel PivotTables and PivotCharts, you can use this book as a quick reference for almost anything you can do with Excel PivotTable and PivotCharts.
This book is for experienced computer users who have never used Excel PivotTables and PivotCharts and are interested in finding out what these powerful Excel tools are all about, what they can do, and how they can enhance spreadsheet data analysis. This book is also for more experienced Excel PivotTables and PivotCharts users who want to take their understanding of these tools to a higher level by expanding their knowledge of the different features that Excel PivotTables and PivotCharts have to offer.
Excel PivotTables and PivotCharts: Your visual blueprint for creating dynamic spreadsheets has 11 chapters and 5 appendixes.
Chapter 1, "Understanding PivotTables," introduces you to PivotTables. You learn about data analysis, basic PivotTable concepts, the benefits, uses, and features of PivotTables, and the basics of PivotCharts.
In Chapter 2, you learn how to build a basic PivotTable from the data in an Excel list.
Chapter 3, "Manipulating Your PivotTable," takes you through a number of techniques for working with various parts of a PivotTable. Techniques such as selecting PivotTable items, removing fields from the PivotTable, and refreshing the PivotTable data will be used throughout the book. You also learn important tasks such as publishing a PivotTable to a Web page as well as printing and deleting PivotTables.
The topics in Chapter 4 all deal with changing the PivotTable view, and they include moving fields ("pivoting" the data), sorting PivotTable data, hiding PivotTable items, and grouping PivotTable data values.
In Chapter 5 you learn how to customize PivotTable field. You find out how to rename and field or item, format a PivotTable cell, apply numeric and date formats to PivotTable data, and more.
Chapter 6 takes you through a few PivotTable options, including applying an AutoFormat to an entire PivotTable, renaming a PivotTable, turning off grand totals, and protecting a PivotTable from changes.
Chapter 7, "Performing PivotTable Calculations," shows you how to work with a PivotTable's summary calculations. You learn how to change the calculation and how to convert the calculation to using the powerful difference, percentage, running total, and index summaries.
In Chapter 8, you learn how to create your own custom PivotTable calculations. You learn what custom calculations are, what you can do with them, and what limitations they have. From there, you find out how to create custom calculated fields and items and how to edit and manipulate these calculations.
In Chapter 9, you find out more about PivotCharts and you learn how to create them from existing PivotTables as well as from an Excel list. You also learn a number of PivotChart techniques, including how to change the chart type, change the series order, add titles, and how to print and delete PivotCharts.
Chapter 10, "Building More Advanced PivotTables," shows you how to create PivotTables from multiple consolidation ranges, from existing PivotTables, from external data, and from PivotTable data in a Web page.
In Chapter 11, you find out about online analytical processing (OLAP) and you learn how to create a PivotTable from an OLAP cube.
Appendix A tells you everything you need to know to build formulas for custom PivotTable calculations.
In Appendix B, you learn how to use Microsoft Query to work with external data that you can use in your PivotTables.
Appendix C shows you how to import data into Excel for use in a PivotTable. You learn how to import from a data source, an Access table, a Word table, a text file, a Web page, and an XML file.
Appendix D teaches you the basics of VBA that you need to know to use the various VBA scripts that are presented throughout the book to automate PivotTable tasks.
Appendix E presents a glossary of PivotTable and PivotChart terminology.
For hardware, you will just need whatever is required by Office to work with Excel in the examples in this book. Several tasks show you how to use Excel with VBA, so you will need VBA for those tasks. Other tasks show how to use Excel with the Internet, so access to a site will be necessary. Many of the book's examples use data from the Northwind sample database that comes with Microsoft Access, so it would help if you have this database available. The other examples are available on the book's companion Web site — see the section "What's on the Web Site."
A number of styles have been used throughout Excel PivotTables and Charts: Your visual blueprint for creating dynamic spreadsheets to designate different types of information.
Courier Font
Indicates the use of PHP such as tags or attributes, scripting language code such as statements, operators, or functions, and code such as objects, methods, or properties.
Italics
Indicates a new term.
Numbered Steps
You must perform the instructions in numbered steps in order to successfully complete a section and achieve the final results.
Indented Text
Indented text tells you what the program does in response following a numbered step. For example, if you click a certain menu command, a dialog box may appear, or a window may open. Indented text after a step may also present another way to perform the step.
Notes
Notes give additional information. They may describe special conditions that may occur during an operation. They may warn you of a situation that you want to avoid, for example, the loss of data. A note may also cross reference a related area of the book. A cross reference may guide you to another chapter, or another section within the current chapter.
Icons
Icons are graphical representations within the text. They show you exactly what you need to click to perform a step.
An Apply It section takes the code from the preceding task one step further. Apply It sections allow you to take full advantage of Excel PivotTables and Charts.
An Extra section provides additional information about the preceding task. Extra sections contain the inside information to make working with Excel PivotTables and PivotCharts easier and more efficient.