Excel Data Analysis For Dummies (For Dummies (Computer/Tech))

Take Excel to the next levelExcel is the world's leading spreadsheet application. It's a key module in Microsoft Office--the number-one productivity suite--and it is the number-one business intelligence tool. An Excel dashboard report is a visual presentation of critical data and uses gauges, maps, charts, sliders, and other graphical elements to present complex data in an easy-to-understand format.Excel Data Analysis For Dummies explains in depth how to use Excel as a tool for analyzing big data sets. In no time, you'll discover how to mine and analyze critical data in order to make more informed business decisions.* Work with external databases, PivotTables, and Pivot Charts* Use Excel for statistical and financial functions and data sharing* Get familiar with Solver* Use the Small Business Finance ManagerIf you're familiar with Excel but lack a background in the technical aspects of data analysis, this user-friendly book makes it easy to start putting it to use for you. - * Navigate and analyze complicated data* Work with external databases, PivotTables, and PivotCharts* Make the most of the latest features of Excel 2019Take your Excel data-analysis skills to the next levelIf you can get data into Excel, then you have a giant basket of data analysis tools at your fingertips. This book shows you how to access and use the features and techniques for organizing, manipulating, and summarizing just about anything that resides in a worksheet. So if you're familiar with Excel but lack a background in the technical aspects of data analysis, this user-friendly book makes it easy to start putting Excel to use for you.Inside.* Work with external databases* Start data sharing* Get familiar with Solver* Clean your data* Use PivotTables and PivotCharts* Track trends and make forecasts Title Page 2 Copyright Page 3 Table of Contents 6 Introduction 14 About This Book 14 What You Can Safely Ignore 15 Foolish Assumptions 16 Icons Used in This Book 16 Beyond the Book 17 Where to Go from Here 17 Part 1 Getting Started with Data Analysis 18 Chapter 1 Learning Basic Data-Analysis Techniques 20 What Is Data Analysis, Anyway? 21 Cooking raw data 21 Dealing with data 21 Building data models 22 Performing what-if analysis 22 Analyzing Data with Conditional Formatting 22 Highlighting cells that meet some criteria 23 Showing pesky duplicate values 25 Highlighting the top or bottom values in a range 26 Analyzing cell values with data bars 27 Analyzing cell values with color scales 29 Analyzing cell values with icon sets 29 Creating a custom conditional formatting rule 30 Editing a conditional formatting rule 33 Removing conditional formatting rules 35 Summarizing Data with Subtotals 36 Grouping Related Data 37 Consolidating Data from Multiple Worksheets 39 Consolidating by position 40 Consolidating by category 41 Chapter 2 Working with Data-Analysis Tools 44 Working with Data Tables 44 Creating a basic data table 45 Creating a two-input data table 47 Skipping data tables when calculating workbooks 49 Analyzing Data with Goal Seek 50 Analyzing Data with Scenarios 52 Create a scenario 52 Apply a scenario 54 Edit a scenario 54 Delete a scenario 55 Optimizing Data with Solver 55 Understanding Solver 56 The advantages of Solver 56 When should you use Solver? 56 Loading the Solver add-in 58 Optimizing a result with Solver 59 Adding constraints to Solver 61 Save a Solver solution as a scenario 64 Chapter 3 Introducing Excel Tables 66 What Is a Table and Why Should I Care? 66 Building a Table 68 Getting the data from an external source 69 Converting a range to a table 69 Basic table maintenance 71 Analyzing Table Information 72 Displaying simple statistics 72 Adding a column subtotal 73 Sorting table records 75 Filtering table records 77 Clearing a filter 79 Turning off AutoFilter 79 Applying a predefined AutoFilter 79 Applying multiple filters 82 Applying advanced filters 82 Chapter 4 Grabbing Data from External Sources 88 What’s All This About External Data? 88 Exporting Data from Other Programs 90 Importing External Data into Excel 91 Importing data from an Access table 91 Importing data from a Word table 92 Introducing text file importing 93 Importing a delimited text file 94 Importing a fixed-width text file 95 Importing data from a web page 96 Importing an XML file 98 Querying External Databases 100 Defining a data source 101 Querying a data source 104 It’s Sometimes a Raw Deal 108 Chapter 5 Scrub-a-Dub-Dub: Cleaning Data 110 Editing Your Imported Workbook 110 Deleting unnecessary columns 111 Deleting unnecessary rows 112 Resizing columns 112 Resizing rows 112 Erasing unneeded contents in a cell or range 113 Formatting numeric values 113 Copying worksheet data 114 Moving worksheet data 114 Replacing data in fields 115 Cleaning Data with Text Functions 116 The CLEAN function 116 The CONCAT function 117 The EXACT function 118 The FIND function 118 The LEFT function 119 The LEN function 120 The LOWER function 120 The MID function 120 The NUMBERVALUE function 121 The PROPER function 122 The REPLACE function 122 The RIGHT function 123 The SEARCH function 124 The SUBSTITUTE function 124 The TEXT function 125 The TEXTJOIN function 125 The TRIM function 125 The UPPER function 126 The VALUE function 126 Converting text function formulas to text 127 Using Validation to Keep Data Clean 127 Chapter 6 Analyzing Table Data with Functions 132 The Database Functions: Some General Remarks 133 Retrieving a Value from a Table 134 Summing a Column’s Values 135 Counting a Column’s Values 136 Averaging a Column’s Values 138 Determining a Column’s Maximum and Minimum Values 139 Multiplying a Column’s Values 140 Deriving a Column’s Standard Deviation 141 Calculating a Column’s Variance 143 Part 2 Analyzing Data with PivotTables and PivotCharts 146 Chapter 7 Creating and Using PivotTables 148 Understanding PivotTables 148 Exploring PivotTable Features 150 Building a PivotTable from an Excel Table 151 Creating a PivotTable from External Data 154 Refreshing PivotTable Data 156 Refreshing PivotTable data manually 157 Refreshing PivotTable data automatically 157 Adding Multiple Fields to a PivotTable Area 158 Pivoting a Field to a Different Area 159 Grouping PivotTable Values 160 Grouping numeric values 160 Grouping date and time values 161 Grouping text values 161 Filtering PivotTable Values 162 Applying a report filter 162 Filtering row or column items 164 Filtering PivotTable values 164 Filtering a PivotTable with a slicer 165 Chapter 8 Performing PivotTable Calculations 168 Messing around with PivotTable Summary Calculations 169 Changing the PivotTable summary calculation 169 Trying out the difference summary calculation 171 Applying a percentage summary calculation 173 Adding a running total summary calculation 175 Creating an index summary calculation 177 Working with PivotTable Subtotals 179 Turning off subtotals for a field 179 Displaying multiple subtotals for a field 180 Introducing Custom Calculations 181 Formulas for custom calculations 181 Checking out the custom calculation types 182 Understanding custom calculation limitations 182 Inserting a Custom Calculated Field 184 Inserting a Custom Calculated Item 186 Editing a Custom Calculation 188 Deleting a Custom Calculation 189 Chapter 9 Building PivotCharts 190 Introducing the PivotChart 190 Understanding PivotChart pros and cons 191 Taking a PivotChart tour 192 Understanding PivotChart limitations 193 Creating a PivotChart 194 Creating a PivotChart from a PivotTable 194 Embedding a PivotChart on a PivotTable’s worksheet 194 Creating a PivotChart from an Excel table 196 Working with PivotCharts 198 Moving a PivotChart to another sheet 198 Filtering a PivotChart 199 Changing the PivotChart type 201 Adding data labels to your PivotChart 202 Sorting the PivotChart 203 Adding PivotChart titles 204 Moving the PivotChart legend 206 Displaying a data table with the PivotChart 207 Part 3 Discovering Advanced Data Analysis Tools 210 Chapter 10 Tracking Trends and Making Forecasts 212 Plotting a Best-Fit Trend line 213 Calculating Best-Fit Values 215 Plotting Forecasted Values 217 Extending a Linear Trend 219 Extending a linear trend using the fill handle 219 Extending a linear trend using the Series command 220 Calculating Forecasted Linear Values 221 Plotting an Exponential Trend Line 223 Calculating Exponential Trend Values 224 Plotting a Logarithmic Trend Line 226 Plotting a Power Trend Line 228 Plotting a Polynomial Trend Line 229 Chapter 11 Analyzing Data with Statistics 232 Counting Things 233 Counting numbers 233 Counting nonempty cells 234 Counting empty cells 234 Counting cells that match criteria 234 Counting cells that match multiple criteria 235 Counting permutations 236 Counting combinations 237 Averaging Things 238 Calculating an average 238 Calculating a conditional average 238 Calculating an average based on multiple conditions 239 Calculating the median 240 Calculating the mode 240 Finding the Rank 241 Determining the Nth Largest or Smallest Value 243 Calculating the nth highest value 244 Calculating the nth smallest value 244 Creating a Grouped Frequency Distribution 245 Calculating the Variance 246 Calculating the Standard Deviation 247 Finding the Correlation 248 Chapter 12 Analyzing Data with Descriptive Statistics 250 Loading the Analysis ToolPak 251 Generating Descriptive Statistics 253 Calculating a Moving Average 256 Determining Rank and Percentile 258 Generating Random Numbers 260 Creating a Frequency Distribution 263 Chapter 13 Analyzing Data with Inferential Statistics 266 Sampling Data 267 Using the t-Test Tools 269 Performing a z-Test 272 Determining the Regression 274 Calculating the Correlation 277 Calculating the Covariance 278 Using the Anova Tools 280 Performing an f-test 281 Part 4 The Part of Tens 284 Chapter 14 Ten Things You Ought to Know about Statistics 286 Descriptive Statistics Are Straightforward 287 Averages Aren’t So Simple Sometimes 287 Standard Deviations Describe Dispersion 288 An Observation Is an Observation 289 A Sample Is a Subset of Values 289 Inferential Statistics Are Cool but Complicated 290 Probability Distributions Aren’t Always Confusing 291 Uniform distribution 292 Normal distribution 293 Parameters Aren’t So Complicated 294 Skewness and Kurtosis Describe a Probability Distribution’s Shape 294 Confidence Intervals Seem Complicated at First, but Are Useful 295 Chapter 15 Ten Ways to Analyze Financial Data 298 Calculating Future Value 299 Calculating Present Value 300 Determining Loan Payments 301 Calculating a Loan Payment’s Principal and Interest 302 Calculating Cumulative Loan Principal and Interest 302 Finding the Required Interest Rate 303 Determining the Internal Rate of Return 304 Calculating Straight-Line Depreciation 305 Returning the Fixed-Declining Balance Depreciation 305 Determining the Double-Declining Balance Depreciation 306 Chapter 16 Ten Ways to Raise Your PivotTable Game 308 Turn the PivotTable Fields Task Pane On and Off 309 Change the PivotTable Fields Task Pane Layout 309 Display the Details Behind PivotTable Data 311 Apply a PivotTable Style 313 Create a Custom PivotTable Style 314 Preserve PivotTable Formatting 316 Rename the PivotTable 317 Turn Off Grand Totals 318 Reduce the Size of PivotTable Workbooks 319 Use a PivotTable Value in a Formula 319 Appendix Glossary of Data Analysis and Excel Terms 322 Index 332 EULA -1 Take Excel to the next level Excel is the world's leading spreadsheet application. It's a key module in Microsoft Office--the number-one productivity suite--and it is the number-one business intelligence tool. An Excel dashboard report is a visual presentation of critical data and uses gauges, maps, charts, sliders, and other graphical elements to present complex data in an easy-to-understand format. Excel Data Analysis For Dummies explains in depth how to use Excel as a tool for analyzing big data sets. In no time, you'll discover how to mine and analyze critical data in order to make more informed business decisions. Work with external databases, PivotTables, and Pivot Charts Use Excel for statistical and financial functions and data sharing Get familiar with Solver Use the Small Business Finance Manager If you're familiar with Excel but lack a background in the technical aspects of data analysis, this user-friendly book makes it easy to start putting it to use for you