Excel data analysis for dummies

Description

Title Page Copyright Page Table of Contents Introduction About This Book What You Can Safely Ignore Foolish Assumptions Icons Used in This Book Beyond the Book Where to Go from Here Part 1 Getting Started with Data Analysis Chapter 1 Learning Basic Data-Analysis Techniques What Is Data Analysis, Anyway? Cooking raw data Dealing with data Building data models Performing what-if analysis Analyzing Data with Conditional Formatting Highlighting cells that meet some criteria Showing pesky duplicate values Highlighting the top or bottom values in a range Analyzing cell values with data bars Analyzing cell values with color scales Analyzing cell values with icon sets Creating a custom conditional formatting rule Editing a conditional formatting rule Removing conditional formatting rules Summarizing Data with Subtotals Grouping Related Data Consolidating Data from Multiple Worksheets Consolidating by position Consolidating by category Chapter 2 Working with Data-Analysis Tools Working with Data Tables Creating a basic data table Creating a two-input data table Skipping data tables when calculating workbooks Analyzing Data with Goal Seek Analyzing Data with Scenarios Create a scenario Apply a scenario Edit a scenario Delete a scenario Optimizing Data with Solver Understanding Solver The advantages of Solver When should you use Solver? Loading the Solver add-in Optimizing a result with Solver Adding constraints to Solver Save a Solver solution as a scenario Chapter 3 Introducing Excel Tables What Is a Table and Why Should I Care? Building a Table Getting the data from an external source Converting a range to a table Basic table maintenance Analyzing Table Information Displaying simple statistics Adding a column subtotal Sorting table records Filtering table records Clearing a filter Turning off AutoFilter Applying a predefined AutoFilter Applying multiple filters Applying advanced filters Chapter 4 Grabbing Data from External Sources What’s All This About External Data? Exporting Data from Other Programs Importing External Data into Excel Importing data from an Access table Importing data from a Word table Introducing text file importing Importing a delimited text file Importing a fixed-width text file Importing data from a web page Importing an XML file Querying External Databases Defining a data source Querying a data source It’s Sometimes a Raw Deal Chapter 5 Scrub-a-Dub-Dub: Cleaning Data Editing Your Imported Workbook Deleting unnecessary columns Deleting unnecessary rows Resizing columns Resizing rows Erasing unneeded contents in a cell or range Formatting numeric values Copying worksheet data Moving worksheet data Replacing data in fields Cleaning Data with Text Functions The CLEAN function The CONCAT function The EXACT function The FIND function The LEFT function The LEN function The LOWER function The MID function The NUMBERVALUE function The PROPER function The REPLACE function The RIGHT function The SEARCH function The SUBSTITUTE function The TEXT function The TEXTJOIN function The TRIM function The UPPER function The VALUE function Converting text function formulas to text Using Validation to Keep Data Clean Chapter 6 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 Part 2 Analyzing Data with PivotTables and PivotCharts Chapter 7 Creating and Using PivotTables Understanding PivotTables Exploring PivotTable Features Building a PivotTable from an Excel Table Creating a PivotTable from External Data Refreshing PivotTable Data Refreshing PivotTable data manually Refreshing PivotTable data automatically Adding Multiple Fields to a PivotTable Area Pivoting a Field to a Different Area Grouping PivotTable Values Grouping numeric values Grouping date and time values Grouping text values Filtering PivotTable Values Applying a report filter Filtering row or column items Filtering PivotTable values Filtering a PivotTable with a slicer Chapter 8 Performing PivotTable Calculations Messing around with PivotTable Summary Calculations Changing the PivotTable summary calculation Trying out the difference summary calculation Applying a percentage summary calculation Adding a running total summary calculation Creating an index summary calculation Working with PivotTable Subtotals Turning off subtotals for a field Displaying multiple subtotals for a field Introducing Custom Calculations Formulas for custom calculations Checking out the custom calculation types Understanding custom calculation limitations Inserting a Custom Calculated Field Inserting a Custom Calculated Item Editing a Custom Calculation Deleting a Custom Calculation Chapter 9 Building PivotCharts Introducing the PivotChart Understanding PivotChart pros and cons Taking a PivotChart tour Understanding PivotChart limitations Creating a PivotChart Creating a PivotChart from a PivotTable Embedding a PivotChart on a PivotTable’s worksheet Creating a PivotChart from an Excel table Working with PivotCharts Moving a PivotChart to another sheet Filtering a PivotChart Changing the PivotChart type Adding data labels to your PivotChart Sorting the PivotChart Adding PivotChart titles Moving the PivotChart legend Displaying a data table with the PivotChart Part 3 Discovering Advanced Data Analysis Tools Chapter 10 Tracking Trends and Making Forecasts Plotting a Best-Fit Trend line Calculating Best-Fit Values Plotting Forecasted Values Extending a Linear Trend Extending a linear trend using the fill handle Extending a linear trend using the Series command 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 Chapter 11 Analyzing Data with Statistics Counting Things Counting numbers Counting nonempty cells Counting empty cells Counting cells that match criteria Counting cells that match multiple criteria Counting permutations Counting combinations Averaging Things Calculating an average Calculating a conditional average Calculating an average based on multiple conditions Calculating the median Calculating the mode Finding the Rank Determining the Nth Largest or Smallest Value Calculating the nth highest value Calculating the nth smallest value Creating a Grouped Frequency Distribution Calculating the Variance Calculating the Standard Deviation Finding the Correlation Chapter 12 Analyzing Data with 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 with 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 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 Uniform distribution Normal distribution 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 Appendix Glossary of Data Analysis and Excel Terms Index EULA