ebook ebooks e-book e-books downloaden bei MyEbooks.ch downloaden

Excel 2007 PivotTables Recipes A Problem-Solution Approach

:	Debra Dalgleish
:	Excel 2007 PivotTables Recipes A Problem-Solution Approach
:	Apress
:	9781430205043
:	1
:	CHF 31.50
:

:	Informatik
:	English

:	272
:	Wasserzeichen/DRM
:	PC/MAC/eReader/Tablet
:	PDF

In this book, Debra Dalgleish, Microsoft Office Excel MVP since 2001 as well as an expert and trainer in Excel, brings together a one-stop resource for anyone curious about representing, analyzing, and using their data with PivotTables and PivotCharts. She covers the entire breadth of situations you could ever encounter, from planning and creating, to formatting and extracting data, to maximizing performance and troubleshooting. The author presents tips and techniques that can't be found in Excel's Help section, while carefully explaining the most confusing features of PivotTables to help you realize their powerful potential.

Debra Dalgleish is a computer consultant in Mississauga, Ontario, Canada, serving local and international clients. Self-employed since 1985, she has extensive experience in designing complex Excel and Access applications, as well as sophisticated Word forms and documents. She has led hundreds of Microsoft Office corporate training sessions, from beginner to advanced level. In recognition of her contributions to the Excel newsgroups, she has received the Microsoft Office Excel 'Most Valuable Professional' award each year since 2001. You can find a wide variety of Excel tips and tutorials, and sample files, on her Contextures website: Contextures.com/tiptech.html.

	Contents at a Glance	4
	Contents	5
	About the Author	13
	About the Technical Reviewer	14
	Acknowledgments	15
	Introduction	16
	Creating a Pivot Table	19
	1.1. Planning a Pivot Table: Getting Started	19
	1.2. Planning a Shared Pivot Table	20
	1.3. Preparing the Source Data: Using Excel Data	22
	1.4. Preparing the Source Data: Creating an Excel Table	24
	1.5. Preparing the Source Data: Excel Field Names Not Valid	26
	1.6. Preparing the Source Data: Using Filtered Excel Data	26
	1.7. Preparing the Source Data: Using an Excel Table with Monthly Columns	27
	1.8. Preparing the Source Data: Using an Access Query	31
	1.9. Preparing the Source Data: Using a Text File	32
	1.10. Preparing the Source Data: Using an OLAP Cube	32
	1.11. Creating the Pivot Table: Using Excel Data as the Source	33
	1.12. Creating the Pivot Table: Using Excel Data on Separate Sheets	33
	1.13. Creating the Pivot Table: Using the PivotTable Field List	36
	1.14. Creating the Pivot Table: Changing the Field List Order	38
	Sorting and Filtering Pivot Table Data	39
	2.1. Sorting a Pivot Field: Sorting Row Labels	39
	2.2. Sorting a Pivot Field: New Items Out of Order	41
	2.3. Sorting a Pivot Field: Sorting Items Left to Right	42
	2.4. Sorting a Pivot Field: Sorting Items in a Custom Order	43
	2.5. Sorting a Pivot Field: Items Won t Sort Correctly	45
	2.6. Filtering a Pivot Field: Filtering Row Label Text	46
	2.7. Filtering a Pivot Field: Applying Multiple Filters to a Field	47
	2.8. Filtering a Pivot Field: Filtering Row Label Dates	49
	2.9. Filtering a Pivot Field: Filtering Values for Row Fields	50
	2.10. Filtering a Pivot Field: Filtering for Nonconsecutive Dates	51
	2.11. Filtering a Pivot Field: Including New Items in a Manual Filter	52
	2.12. Filtering a Pivot Field: Filtering by Selection	53
	2.13. Filtering a Pivot Field: Filtering for Top Items	54
	2.14. Using Report Filters: Hiding Report Filter Items	55
	2.15. Using Report Filters: Filtering for a Date Range	56
	2.16. Using Report Filters: Filtering for Future Dates	56
	Calculations in a Pivot Table	58
	3.1. Using Summary Functions: Defaulting to Sum or Count	58
	3.2. Using Summary Functions: Counting Blank Cells	62
	3.3. Using Custom Calculations: Difference From	63
	3.4. Using Custom Calculations: % Of	65
	3.5. Using Custom Calculations: % Difference From	66
	3.6. Using Custom Calculations: Running Total	67
	3.7. Using Custom Calculations:% of Row	69
	3.8. Using Custom Calculations: % of Column	70
	3.9. Using Custom Calculations: % of Total	71
	3.10. Using Custom Calculations: Index	72
	3.11. Using Formulas: Calculated Field vs. Calculated Item	73
	3.12. Using Formulas: Adding Items With a Calculated Item	74
	3.13. Using Formulas: Modifying a Calculated Item	75
	3.14. Using Formulas: Removing a Calculated Item	76
	3.15. Using Formulas: Using Index Numbers in a Calculated Item	76
	3.16. Using Formulas: Modifying a Calculated Item Formula in Cell	77
	3.17. Using Formulas: Creating a Calculated Field	78
	3.18. Using Formulas: Modifying a Calculated Field	79
	3.19. Using Formulas: Removing a Calculated Field	80
	3.20. Using Formulas: Determining the Type of Formula	80
	3.21. Using Formulas: Adding a Calculated Item to a Field with Grouped Items	81
	3.22. Using Formulas: Calculating the Difference Between Amounts	81
	3.23. Using Formulas: Correcting the Grand Total for a Calculated Field	82
	3.24. Using Formulas: Calculated Field Count of Unique Items	83
	3.25. Using Formulas: Correcting Results in a Calculated Field	84
	3.26. Using Formulas: Listing All Formulas	84
	3.27. Using Formulas: Accidentally Creating a Calculated Item	84
	3.28. Using Formulas: Solve Order	85
	Formatting a Pivot Table	87
	4.1. Using PivotTable Styles: Applying a Predefined Format	87
	4.2. Using PivotTable Styles: Removing a PivotTable Style	89
	4.3. Using PivotTable Styles: Changing the Default Style	90
	4.4. Using PivotTable Styles: Creating a Custom Style	90
	4.5. Using PivotTable Styles: Copying a Custom Style to a Different Workbook	92
	4.6. Using Themes: Impacting PivotTable Styles	93
	4.7. Using the Enable Selection Option	94
	4.8. Losing Formatting When Refreshing the Pivot Table	95
	4.9. Hiding Error Values on Worksheet	95
	4.10. Showing Zero in Empty Values Cells	96
	4.11. Hiding Buttons and Labels	97
	4.12. Applying Conditional Formatting: Using a Color Scale	97
	4.13. Applying Conditional Formatting: Using an Icon Set	98
	4.14. Applying Conditional Formatting: Using Bottom 10 Items	100
	4.15. Applying Conditional Formatting: Formatting Cells Between Two Values	101
	4.16. Applying Conditional Formatting: Formatting Labels in a Date Period	102
	4.17. Applying Conditional Formatting: Using Data Bars	103
	4.18. Applying Conditional Formatting: Changing the Data Range	105
	4.19. Applying Conditional Formatting: Changing the Order of Rules	107