About the Tutorial

[Pages:40]Data Analysis with Excel

About the Tutorial

Data Analysis with Excel is a comprehensive tutorial that provides a good insight into the latest and advanced features available in Microsoft Excel. It explains in detail how to perform various data analysis functions using the features available in MS-Excel. The tutorial has plenty of screenshots that explain how to use a particular feature, in a step-by-step manner.

Audience

This tutorial has been designed for all those readers who depend heavily on MS-Excel to prepare charts, tables, and professional reports that involve complex data. It will help all those readers who use MS-Excel regularly to analyze data.

Prerequisites

The readers of this tutorial are expected to have a good prior understanding of the basic features available in Microsoft Excel.

Copyright & Disclaimer

Copyright 2016 by Tutorials Point (I) Pvt. Ltd. All the content and graphics published in this e-book are the property of Tutorials Point (I) Pvt. Ltd. The user of this e-book is prohibited to reuse, retain, copy, distribute or republish any contents or a part of contents of this e-book in any manner without written consent of the publisher. We strive to update the contents of our website and tutorials as timely and as precisely as possible, however, the contents may contain inaccuracies or errors. Tutorials Point (I) Pvt. Ltd. provides no guarantee regarding the accuracy, timeliness or completeness of our website or its contents including this tutorial. If you discover any errors on our website or in this tutorial, please notify us at contact@

i

Data Analysis with Excel

Table of Contents

About the Tutorial ............................................................................................................................................ i Audience........................................................................................................................................................... i Prerequisites..................................................................................................................................................... i Copyright & Disclaimer ..................................................................................................................................... i Table of Contents ............................................................................................................................................ ii

DATA ANALYSIS WITH EXCEL.......................................................................................................1

1. Data Analysis - Overview..........................................................................................................................2 Types of Data Analysis ..................................................................................................................................... 2 Data Analysis with Excel .................................................................................................................................. 4

2. Data Analysis Process ...............................................................................................................................5

3. Data Analysis with Excel ? Overview.........................................................................................................7

4. Working with Range Names....................................................................................................................10 Copying Name using Formula Autocomplete ................................................................................................ 11 Range Name Syntax Rules ............................................................................................................................. 11 Creating Range Names .................................................................................................................................. 12 Creating Names for Constants....................................................................................................................... 15 Managing Names........................................................................................................................................... 16 Scope of a Name............................................................................................................................................ 18 Deleting Names with Error Values................................................................................................................. 20 Editing Names................................................................................................................................................ 21 Applying Names............................................................................................................................................. 24 Using Names in a Formula ............................................................................................................................. 26 Viewing Names in a Workbook ..................................................................................................................... 27 Using Names for Range Intersections ........................................................................................................... 28 Copying Formulas with Names ...................................................................................................................... 30

5. Tables .....................................................................................................................................................31 Difference between Tables and Ranges ........................................................................................................ 31 Create Table .................................................................................................................................................. 32 Table Name ................................................................................................................................................... 35 Managing Names in a Table .......................................................................................................................... 36 Table Headers replacing Column Letters....................................................................................................... 38 Propagation of a Formula in a Table ............................................................................................................. 39 Resize Table ................................................................................................................................................... 41 Remove Duplicates ........................................................................................................................................ 42 Convert to Range ........................................................................................................................................... 45 Table Style Options........................................................................................................................................ 45 Table Styles.................................................................................................................................................... 46

ii

Data Analysis with Excel

6. Cleaning Data with Text Functions ..........................................................................................................48 Removing Unwanted Characters from Text .................................................................................................. 48 Extracting Data Values from Text .................................................................................................................. 50 Formatting Data with Text Functions ............................................................................................................ 57

7. Cleaning Data Containing Date Values ....................................................................................................59 Date Formats ................................................................................................................................................. 59 Converting Dates in Serial Format to Month-Day-Year Format .................................................................... 60 Converting Dates in Month-Day-Year Format to Serial Format .................................................................... 61 Obtaining Today's Date ................................................................................................................................. 62 Finding a Workday after Specified Days ........................................................................................................ 63 Customizing the Definition of a Weekend..................................................................................................... 64 Number of Workdays between two given Dates .......................................................................................... 65 Extracting Year, Month, Day from Date ........................................................................................................ 66 Extracting Day of the Week from Date.......................................................................................................... 67 Obtaining Date from Year, Month and Day................................................................................................... 67 Calculating Years, Months and Days between two Dates ............................................................................. 68

8. Working with Time Values ......................................................................................................................70 Time Formats................................................................................................................................................. 70 Converting Times in Serial Format to Hour-Minute-Second Format............................................................. 71 Converting Times in Hour-Minute-Second Format to Serial Format............................................................. 72 Obtaining the Current Time........................................................................................................................... 73 Obtaining Time from Hour, Minute and Second ........................................................................................... 74 Extracting Hour, Minute and Second from Time ........................................................................................... 74 Number of hours between Start Time and End Time.................................................................................... 74

9. Conditional Formatting ...........................................................................................................................75 Highlight Cells Rules ...................................................................................................................................... 76 Top / Bottom Rules........................................................................................................................................ 78 Data Bars ....................................................................................................................................................... 83 Color Scales ................................................................................................................................................... 85 Icon Sets ........................................................................................................................................................ 87 New Rule ....................................................................................................................................................... 89 Clear Rules ..................................................................................................................................................... 93 Manage Rules ................................................................................................................................................ 94

10. Sorting ....................................................................................................................................................98 Sort by Text.................................................................................................................................................... 98 Sort by Numbers.......................................................................................................................................... 100 Sort by Dates or Times ................................................................................................................................ 101 Sort by Cell Color ......................................................................................................................................... 102 Sort by Font Color........................................................................................................................................ 104 Sort by Cell Icon ........................................................................................................................................... 105 Sort by a Custom List ................................................................................................................................... 106 Sort by Rows ................................................................................................................................................ 112 Sort by more than one Column or Row ....................................................................................................... 112

11. Filtering.................................................................................................................................................115 Filter by Selected Values ............................................................................................................................. 115 Filter by Text ................................................................................................................................................ 118 Filter by Date ............................................................................................................................................... 119

iii

Data Analysis with Excel

Filter by Numbers ........................................................................................................................................ 121 Filter by Cell Color ....................................................................................................................................... 123 Filter by Font Color ...................................................................................................................................... 125 Filter by Cell Icon ......................................................................................................................................... 126 Clear Filter ................................................................................................................................................... 128 Advanced Filtering ....................................................................................................................................... 129 Filter Using Slicers ....................................................................................................................................... 133

12. Subtotals with Ranges...........................................................................................................................137 Subtotals...................................................................................................................................................... 137 Nested Subtotals ......................................................................................................................................... 142

13. Quick Analysis.......................................................................................................................................150 Quick Analysis with TOTALS......................................................................................................................... 154 Sum.............................................................................................................................................................. 154 Average........................................................................................................................................................ 155 Count ........................................................................................................................................................... 156 %Total .......................................................................................................................................................... 156 Running Total .............................................................................................................................................. 157 Sum of Columns........................................................................................................................................... 158

14. Lookup Functions..................................................................................................................................159 Using VLOOKUP Function ............................................................................................................................ 159 Using VLOOKUP Function with range_lookup TRUE ................................................................................... 160 Using VLOOKUP Function with range_lookup FALSE .................................................................................. 162 Using HLOOKUP Function ............................................................................................................................ 164 Using HLOOKUP Function with range_lookup FALSE .................................................................................. 165 Using HLOOKUP Function with range_lookup TRUE ................................................................................... 166 Using INDEX Function .................................................................................................................................. 167 Using MATCH Function................................................................................................................................ 169

15. PivotTables ...........................................................................................................................................171 Creating PivotTable ..................................................................................................................................... 171 Recommended PivotTables ......................................................................................................................... 173 PivotTable Fields.......................................................................................................................................... 176 PivotTable Areas.......................................................................................................................................... 177 Nesting in the PivotTable ............................................................................................................................ 178 Filters ........................................................................................................................................................... 180 Slicers........................................................................................................................................................... 184 Summarizing Values by other Calculations ................................................................................................. 185 PivotTable Tools .......................................................................................................................................... 187 ANALYZE ...................................................................................................................................................... 188 DESIGN......................................................................................................................................................... 188 Expanding and Collapsing Field ................................................................................................................... 188 Report Presentation Styles .......................................................................................................................... 191 Timeline in PivotTables................................................................................................................................ 194

16. Data Visualization .................................................................................................................................197 Creating Combination Charts ...................................................................................................................... 197 Creating a Combo Chart with Secondary Axis ............................................................................................. 201 Discriminating Series and Category Axis ..................................................................................................... 204 Chart Elements and Chart Styles ................................................................................................................. 205

iv

Data Analysis with Excel

Data Labels .................................................................................................................................................. 207 Quick Layout ................................................................................................................................................ 208 Using Pictures in Column Charts ................................................................................................................. 208 Band Chart ................................................................................................................................................... 210 Thermometer Chart..................................................................................................................................... 214 Gantt Chart .................................................................................................................................................. 221 Waterfall Chart ............................................................................................................................................ 224 Sparklines .................................................................................................................................................... 229 PivotCharts .................................................................................................................................................. 232 PivotChart from PivotTable ......................................................................................................................... 232 PivotChart without a PivotTable.................................................................................................................. 235

17. Data Validation .....................................................................................................................................237 Prepare the Structure for the Worksheet ................................................................................................... 238 Format Serial Number Values...................................................................................................................... 257

18. Financial Analysis..................................................................................................................................262 Present Value of a series of Future Payments............................................................................................. 262 What is EMI?................................................................................................................................................ 264 Monthly Payment of Principal and Interest on a Loan ................................................................................ 266 Calculating Interest Rate ............................................................................................................................. 269 Calculating Term of Loan ............................................................................................................................. 270 Decisions on Investments ............................................................................................................................ 271 Cash Flows at the Beginning of the Year ..................................................................................................... 272 Cash Flows in the Middle of the Year .......................................................................................................... 273 Cash Flows at Irregular Intervals ................................................................................................................. 275 Internal Rate of Return (IRR) ....................................................................................................................... 277 Determining IRR of Cash Flows for a Project ............................................................................................... 277 Unique IRR ................................................................................................................................................... 278 Multiple IRRs ............................................................................................................................................... 279 No IRRs ........................................................................................................................................................ 281 Cash Flows Patterns and IRR ....................................................................................................................... 282 Decisions based on IRRs .............................................................................................................................. 282 IRR of Irregularly Spaced Cash Flows (XIRR) ................................................................................................ 283 Modified IRR (MIRR) .................................................................................................................................... 284

19. Working with Multiple Sheets...............................................................................................................286 Multiple Worksheets with same Structure.................................................................................................. 287 Creating a Formula across Multiple Worksheets ........................................................................................ 288 Summarizing Data in Multiple Worksheets ................................................................................................. 292

20. Formula Auditing ..................................................................................................................................297 Setting the Display Options ......................................................................................................................... 297 Tracing Precedents ...................................................................................................................................... 298 Tracing Dependents..................................................................................................................................... 300 Showing Formulas ....................................................................................................................................... 304 Evaluating a Formula ................................................................................................................................... 306 Error Checking ............................................................................................................................................. 310

v

Data Analysis with Excel

21. Inquire ..................................................................................................................................................313 INQUIRE Commands .................................................................................................................................... 314 Comparing Two Workbooks ........................................................................................................................ 315 Creating an Interactive Report .................................................................................................................... 319 Viewing with Diagrams ................................................................................................................................ 325 Viewing Workbook Relationships................................................................................................................ 325 Viewing Worksheet Relationships ............................................................................................................... 326 Viewing Cell Relationships........................................................................................................................... 327 Cleaning Excess Cell Formatting .................................................................................................................. 330 Managing Passwords of Files....................................................................................................................... 331

ADVANCED DATA ANALYSIS .................................................................................................... 334

22. Overview ..............................................................................................................................................335 What-If Analysis........................................................................................................................................... 335 Importing Data into Excel ............................................................................................................................ 335 Aesthetic Power View Reports .................................................................................................................... 337

23. Data Consolidation ...............................................................................................................................338 Preparing Data for Consolidation ................................................................................................................ 338 Consolidating Data in the Same Workbook................................................................................................. 339 Consolidating Data Automatically ............................................................................................................... 343 Consolidating Data from Different Workbooks ........................................................................................... 345

24. What-If Analysis....................................................................................................................................348 Data Tables .................................................................................................................................................. 348 Scenario Manager........................................................................................................................................ 349 Goal Seek ..................................................................................................................................................... 349 Solver ........................................................................................................................................................... 349

25. What-If Analysis with Data Tables ........................................................................................................350 Analysis with Two-variable Data Table........................................................................................................ 354 Speeding up the Calculations in a Worksheet ............................................................................................. 357

26. What-If Analysis with Scenario Manager ..............................................................................................359 Scenarios ..................................................................................................................................................... 359 Scenario Manager........................................................................................................................................ 359 Initial Values for Scenarios .......................................................................................................................... 360 Creating Scenarios ....................................................................................................................................... 361 Scenario Summary Reports ......................................................................................................................... 367 Scenario Summary....................................................................................................................................... 367 Scenarios from Different Sources................................................................................................................ 368 Displaying Scenarios .................................................................................................................................... 374 Scenario PivotTable Report ......................................................................................................................... 375

27. What-If Analysis with Goal Seek ...........................................................................................................376 Analysis with Goal Seek ............................................................................................................................... 376 Solving Story Problems ................................................................................................................................ 379 Performing a Break-even Analysis............................................................................................................... 381

vi

Data Analysis with Excel

28. Optimization with Excel Solver .............................................................................................................384 Activating Solver Add-in .............................................................................................................................. 384 Solving Methods used by Solver.................................................................................................................. 386 Solving the Problem .................................................................................................................................... 389 Stepping through Solver Trial Solutions ...................................................................................................... 395 Saving Solver Selections .............................................................................................................................. 396

29. Importing Data into Excel......................................................................................................................398 Importing Data from Microsoft Access Database ....................................................................................... 398 Importing Data from a Web Page................................................................................................................ 402 Importing Data from a Text File................................................................................................................... 407 Importing Data from another Workbook .................................................................................................... 411 Importing Data from Other Sources ............................................................................................................ 417 Importing Data using an Existing Connection.............................................................................................. 418 Renaming the Data Connections ................................................................................................................. 419 Refreshing an External Data Connection ..................................................................................................... 420 Updating all the Data Connections in the Workbook .................................................................................. 421 Automatically Refresh Data when a Workbook is opened .......................................................................... 422 Automatically Refresh Data at regular Intervals ......................................................................................... 424 Enabling Background Refresh ...................................................................................................................... 426

30. Data Model ...........................................................................................................................................429 Creating Data Model while Importing Data ................................................................................................ 429 Creating Data Model from Excel Tables ...................................................................................................... 430 Creating Relationships between Tables ...................................................................................................... 434 Summarizing the Data in the Tables in the Data Model .............................................................................. 437 Adding Data to Data Model ......................................................................................................................... 439

31. Exploring Data with PivotTables ...........................................................................................................441 Creating a PivotTable to analyze External Data........................................................................................... 441 Exploring Data in Multiple Tables................................................................................................................ 443 Exploring Data using PivotTable .................................................................................................................. 443 Creating a Relationship between Tables with PivotTable Fields ................................................................. 446

32. Exploring Data with POwerpivot ...........................................................................................................450 Adding Tables to Data Model ...................................................................................................................... 450 Viewing Tables in the Data Model............................................................................................................... 452 Viewing Relationships between Tables ....................................................................................................... 453 Creating Relationships between Tables ...................................................................................................... 453 Viewing the Field defining a Relationship ................................................................................................... 456

33. Exploring Data with Power View...........................................................................................................458 Creating a Power View Report .................................................................................................................... 458 Power View with Calculated Fields.............................................................................................................. 459 Filtering Power View.................................................................................................................................... 462 Power View Visualizations........................................................................................................................... 463 Exploring Data with Matrix Visualization .................................................................................................... 464 Exploring Data with Card Visualization........................................................................................................ 468 Data Model and Power View ....................................................................................................................... 470 Creating Data Model from Power View Sheet ............................................................................................ 470

vii

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download