Transcription
Data Analysis with ExcelAbout the TutorialData Analysis with Excel is a comprehensive tutorial that provides a good insight into thelatest and advanced features available in Microsoft Excel. It explains in detail how toperform 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 astep-by-step manner.AudienceThis tutorial has been designed for all those readers who depend heavily on MS-Excel toprepare charts, tables, and professional reports that involve complex data. It will help allthose readers who use MS-Excel regularly to analyze data.PrerequisitesThe readers of this tutorial are expected to have a good prior understanding of the basicfeatures 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 republishany contents or a part of contents of this e-book in any manner without written consentof the publisher.We strive to update the contents of our website and tutorials as timely and as precisely aspossible, however, the contents may contain inaccuracies or errors. Tutorials Point (I) Pvt.Ltd. provides no guarantee regarding the accuracy, timeliness or completeness of ourwebsite or its contents including this tutorial. If you discover any errors on our website orin this tutorial, please notify us at contact@tutorialspoint.comi
Data Analysis with ExcelTable of ContentsAbout the Tutorial . iAudience . iPrerequisites . iCopyright & Disclaimer . iTable of Contents . iiDATA ANALYSIS WITH EXCEL . 11.Data Analysis Overview . 2Types of Data Analysis . 2Data Analysis with Excel . 42.Data Analysis Process . 53.Data Analysis with Excel – Overview . 74.Working with Range Names . 10Copying Name using Formula Autocomplete . 11Range Name Syntax Rules . 11Creating Range Names . 12Creating Names for Constants . 15Managing Names . 16Scope of a Name . 18Deleting Names with Error Values. 20Editing Names . 21Applying Names . 24Using Names in a Formula . 26Viewing Names in a Workbook . 27Using Names for Range Intersections . 28Copying Formulas with Names . 305.Tables . 31Difference between Tables and Ranges . 31Create Table . 32Table Name . 35Managing Names in a Table . 36Table Headers replacing Column Letters . 38Propagation of a Formula in a Table . 39Resize Table . 41Remove Duplicates . 42Convert to Range . 45Table Style Options . 45Table Styles . 46ii
Data Analysis with Excel6.Cleaning Data with Text Functions . 48Removing Unwanted Characters from Text . 48Extracting Data Values from Text . 50Formatting Data with Text Functions . 577.Cleaning Data Containing Date Values . 59Date Formats . 59Converting Dates in Serial Format to Month-Day-Year Format . 60Converting Dates in Month-Day-Year Format to Serial Format . 61Obtaining Today's Date . 62Finding a Workday after Specified Days . 63Customizing the Definition of a Weekend . 64Number of Workdays between two given Dates . 65Extracting Year, Month, Day from Date . 66Extracting Day of the Week from Date . 67Obtaining Date from Year, Month and Day . 67Calculating Years, Months and Days between two Dates . 688.Working with Time Values . 70Time Formats . 70Converting Times in Serial Format to Hour-Minute-Second Format . 71Converting Times in Hour-Minute-Second Format to Serial Format . 72Obtaining the Current Time . 73Obtaining Time from Hour, Minute and Second . 74Extracting Hour, Minute and Second from Time . 74Number of hours between Start Time and End Time . 749.Conditional Formatting . 75Highlight Cells Rules . 76Top / Bottom Rules . 78Data Bars . 83Color Scales . 85Icon Sets . 87New Rule . 89Clear Rules . 93Manage Rules . 9410. Sorting . 98Sort by Text. 98Sort by Numbers . 100Sort by Dates or Times . 101Sort by Cell Color . 102Sort by Font Color . 104Sort by Cell Icon . 105Sort by a Custom List . 106Sort by Rows . 112Sort by more than one Column or Row . 11211. Filtering. 115Filter by Selected Values . 115Filter by Text . 118Filter by Date . 119iii
Data Analysis with ExcelFilter by Numbers . 121Filter by Cell Color . 123Filter by Font Color . 125Filter by Cell Icon . 126Clear Filter . 128Advanced Filtering . 129Filter Using Slicers . 13312. Subtotals with Ranges. 137Subtotals . 137Nested Subtotals . 14213. Quick Analysis . 150Quick Analysis with TOTALS. 154Sum . 154Average . 155Count . 156%Total . 156Running Total . 157Sum of Columns . 15814. Lookup Functions . 159Using VLOOKUP Function . 159Using VLOOKUP Function with range lookup TRUE . 160Using VLOOKUP Function with range lookup FALSE . 162Using HLOOKUP Function . 164Using HLOOKUP Function with range lookup FALSE . 165Using HLOOKUP Function with range lookup TRUE . 166Using INDEX Function . 167Using MATCH Function . 16915. PivotTables . 171Creating PivotTable . 171Recommended PivotTables . 173PivotTable Fields . 176PivotTable Areas . 177Nesting in the PivotTable . 178Filters . 180Slicers. 184Summarizing Values by other Calculations . 185PivotTable Tools . 187ANALYZE . 188DESIGN. 188Expanding and Collapsing Field . 188Report Presentation Styles . 191Timeline in PivotTables . 19416. Data Visualization . 197Creating Combination Charts . 197Creating a Combo Chart with Secondary Axis . 201Discriminating Series and Category Axis . 204Chart Elements and Chart Styles . 205iv
Data Analysis with ExcelData Labels . 207Quick Layout . 208Using Pictures in Column Charts . 208Band Chart . 210Thermometer Chart . 214Gantt Chart . 221Waterfall Chart . 224Sparklines . 229PivotCharts . 232PivotChart from PivotTable . 232PivotChart without a PivotTable . 23517. Data Validation . 237Prepare the Structure for the Worksheet . 238Format Serial Number Values . 25718. Financial Analysis . 262Present Value of a series of Future Payments . 262What is EMI?. 264Monthly Payment of Principal and Interest on a Loan . 266Calculating Interest Rate . 269Calculating Term of Loan . 270Decisions on Investments . 271Cash Flows at the Beginning of the Year . 272Cash Flows in the Middle of the Year . 273Cash Flows at Irregular Intervals . 275Internal Rate of Return (IRR) . 277Determining IRR of Cash Flows for a Project . 277Unique IRR . 278Multiple IRRs . 279No IRRs . 281Cash Flows Patterns and IRR . 282Decisions based on IRRs . 282IRR of Irregularly Spaced Cash Flows (XIRR) . 283Modified IRR (MIRR) . 28419. Working with Multiple Sheets. 286Multiple Worksheets with same Structure. 287Creating a Formula across Multiple Worksheets . 288Summarizing Data in Multiple Worksheets . 29220. Formula Auditing . 297Setting the Display Options . 297Tracing Precedents . 298Tracing Dependents. 300Showing Formulas . 304Evaluating a Formula . 306Error Checking . 310v
Data Analysis with Excel21. Inquire . 313INQUIRE Commands . 314Comparing Two Workbooks . 315Creating an Interactive Report . 319Viewing with Diagrams . 325Viewing Workbook Relationships . 325Viewing Worksheet Relationships . 326Viewing Cell Relationships . 327Cleaning Excess Cell Formatting . 330Managing Passwords of Files .
Data Analysis with Excel 10 Data Analysis is a process of inspecting, cleaning, transforming and modeling data with the goal of discovering useful information, suggesting conclusions and supporting decision-making. Types of Data Analysis Several data analysis techni
