Paul McFedries - Excel Data Analysis For Dummies

Excel® Data Analysis For Dummies®, 5th Edition

Published by: John Wiley & Sons, Inc.,111 River Street, Hoboken, NJ 07030-5774, www.wiley.com

Copyright © 2022 by John Wiley & Sons, Inc., Hoboken, New Jersey

Media and software compilation copyright © 2022 by John Wiley & Sons, Inc. All rights reserved.

Published simultaneously in Canada

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without the prior written permission of the Publisher. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions .

Trademarks:Wiley, For Dummies, the Dummies Man logo, Dummies.com, Making Everything Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and may not be used without written permission. Microsoft and Excel are registered trademarks of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: WHILE THE PUBLISHER AND AUTHORS HAVE USED THEIR BEST EFFORTS IN PREPARING THIS WORK, THEY MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION ANY IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES REPRESENTATIVES, WRITTEN SALES MATERIALS OR PROMOTIONAL STATEMENTS FOR THIS WORK. THE FACT THAT AN ORGANIZATION, WEBSITE, OR PRODUCT IS REFERRED TO IN THIS WORK AS A CITATION AND/OR POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE PUBLISHER AND AUTHORS ENDORSE THE INFORMATION OR SERVICES THE ORGANIZATION, WEBSITE, OR PRODUCT MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING PROFESSIONAL SERVICES. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR YOUR SITUATION. YOU SHOULD CONSULT WITH A SPECIALIST WHERE APPROPRIATE. FURTHER, READERS SHOULD BE AWARE THAT WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ. NEITHER THE PUBLISHER NOR AUTHORS SHALL BE LIABLE FOR ANY LOSS OF PROFIT OR ANY OTHER COMMERCIAL DAMAGES, INCLUDING BUT NOT LIMITED TO SPECIAL, INCIDENTAL, CONSEQUENTIAL, OR OTHER DAMAGES.

For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002. For technical support, please visit https://hub.wiley.com/community/support/dummies .

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com . For more information about Wiley products, visit www.wiley.com .

Library of Congress Control Number is available from the publisher.

ISBN 978-1-119-84442-6 (pbk); ISBN 978-1-119-84446-4 (ebk); ISBN 978-1-119-84447-1 (ebk)

1 Cover

2 Title Page

3 Copyright

4 Introduction About This Book What You Can Safely Ignore Foolish Assumptions Icons Used in This Book Beyond the Book Where to Go from Here

5 Part 1: Getting Started with Data Analysis Chapter 1: Learning Basic Data-Analysis Techniques What Is Data Analysis, Anyway? Analyzing Data with Conditional Formatting Summarizing Data with Subtotals Grouping Related Data Consolidating Data from Multiple Worksheets Chapter 2: Working with Data-Analysis Tools Working with Data Tables Analyzing Data with Goal Seek Analyzing Data with Scenarios Optimizing Data with Solver Chapter 3: Introducing Excel Tables What Is a Table and Why Should I Care? Building a Table Analyzing Table Information Chapter 4: Grabbing Data from External Sources What’s All This about External Data? Exporting Data from Other Programs Importing External Data into Excel Querying External Databases Chapter 5: Analyzing Table Data with Functions The Database Functions: Some General Remarks Retrieving a Value from a Table Summing a Column's Values Counting a Column’s Values Averaging a Column's Values Determining a Column’s Maximum and Minimum Values Multiplying a Column’s Values Deriving a Column’s Standard Deviation Calculating a Column’s Variance

6 Part 2: Analyzing Data Using PivotTables and PivotCharts Chapter 6: Creating and Using PivotTables Understanding PivotTables Exploring PivotTable Features Building a PivotTable from an Excel Range or Table Creating a PivotTable from External Data Refreshing PivotTable Data Adding Multiple Fields to a PivotTable Area Pivoting a Field to a Different Area Grouping PivotTable Values Filtering PivotTable Values Chapter 7: Performing PivotTable Calculations Messing around with PivotTable Summary Calculations Working with PivotTable Subtotals Introducing Custom Calculations Inserting a Custom Calculated Field Inserting a Custom Calculated Item Editing a Custom Calculation Deleting a Custom Calculation Chapter 8: Building PivotCharts Introducing the PivotChart Creating a PivotChart Working with PivotCharts

7 Part 3: Discovering Advanced Data-Analysis Tools Chapter 9: Dealing with Data Models Understanding Excel Data Models Managing a Data Model with Power Pivot Transforming Data Creating a PivotTable or PivotChart from Your Data Model Chapter 10: Tracking Trends and Making Forecasts Plotting a Best-Fit Trend Line Calculating Best-Fit Values Plotting Forecasted Values Extending a Linear Trend Calculating Forecasted Linear Values Plotting an Exponential Trend Line Calculating Exponential Trend Values Plotting a Logarithmic Trend Line Plotting a Power Trend Line Plotting a Polynomial Trend Line Creating a Forecast Sheet Chapter 11: Analyzing Data Using Statistics Counting Things Averaging Things Finding the Rank Determining the Nth Largest or Smallest Value Creating a Grouped Frequency Distribution Calculating the Variance Calculating the Standard Deviation Finding the Correlation Chapter 12: Analyzing Data Using Descriptive Statistics Loading the Analysis ToolPak Generating Descriptive Statistics Calculating a Moving Average Determining Rank and Percentile Generating Random Numbers Creating a Frequency Distribution Chapter 13: Analyzing Data Using Inferential Statistics Sampling Data Using the t-Test Tools Performing a z-Test Determining the Regression Calculating the Correlation Calculating the Covariance Using the Anova Tools Performing an f-Test

8 Part 4: The Part of Tens Chapter 14: Ten Things You Ought to Know about Statistics Descriptive Statistics Are Straightforward Averages Aren’t So Simple Sometimes Standard Deviations Describe Dispersion An Observation Is an Observation A Sample Is a Subset of Values Inferential Statistics Are Cool But Complicated Probability Distributions Aren't Always Confusing Parameters Aren't So Complicated Skewness and Kurtosis Describe a Probability Distribution’s Shape Confidence Intervals Seem Complicated at First But Are Useful Chapter 15: Ten Ways to Analyze Financial Data Calculating Future Value Calculating Present Value Determining Loan Payments Calculating a Loan Payment's Principal and Interest Calculating Cumulative Loan Principal and Interest Finding the Required Interest Rate Determining the Internal Rate of Return Calculating Straight-Line Depreciation Returning the Fixed-Declining Balance Depreciation Determining the Double-Declining Balance Depreciation Chapter 16: Ten Ways to Raise Your PivotTable Game Turn the PivotTable Fields Task Pane On and Off Change the PivotTable Fields Task Pane Layout Display the Details Behind PivotTable Data Apply a PivotTable Style Create a Custom PivotTable Style Preserve PivotTable Formatting Rename the PivotTable Turn Off Grand Totals Reduce the Size of PivotTable Workbooks Use a PivotTable Value in a Formula