O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Analytics in Excel

Video Description

Analytics in Excel In todayís world, we must navigate through a sea of data and simplify it into decisions for growth, problem solving, and success. By learning the data-analysis techniques and features of Microsoft excel we can master the fast growing world of Analytics. Microsoft Excel is the most common analysis software on the market today, but few users are aware of its full powers. In this advanced course, users will master the tasks necessary in order to handle todayís complex data sets, including data validation, data analysis, charting, and design. * Learn how to use Excel and your data to support decision making * Master functions and calculations that streamline your analysis * Simplify statistics through the use of Excels analysis features * Learn to automate repetitive tasks and quickly spot patterns * Translate your analysis into meaningful charts and graphs

Table of Contents

  1. Introduction 00:04:09
  2. Welcome 00:04:09
  3. Work Files 00:04:01
  4. Analytics for Decision Making 00:04:22
  5. Analysis and Decisions 00:04:22
  6. Introduction to Functions 00:02:39
  7. Sumif and Countif 00:03:09
  8. IF Statement 00:03:30
  9. Multiple Conditions 00:03:43
  10. IF Examples 00:04:07
  11. Vlookup 00:04:49
  12. Nested Statement with Vlookup 00:02:44
  13. Excel Tools 00:04:00
  14. Filters 00:04:00
  15. Understanding Subtotals 00:04:41
  16. Creating Subtotals 00:04:07
  17. Conditional Formats 00:05:47
  18. Using Formulas with Conditional Formats 00:05:12
  19. Data Validation 00:02:49
  20. Introduction to Valildation 00:02:49
  21. Constraining Data Input 00:04:38
  22. Circling Invalid Data 00:02:36
  23. Circling Static Lists 00:03:55
  24. Creating Static Lists 00:05:51
  25. Dynamic Lists with Tables and Names 00:05:51
  26. Charts and Graphs 00:04:59
  27. Changes in Excel 2013 00:04:59
  28. Creating Charts 00:04:50
  29. Single Point Chart and Dialog Boxes 00:06:15
  30. Titles that work 00:04:11
  31. Help with Chart Types 00:04:44
  32. Sparklines 00:05:29
  33. Trends Lines 00:06:03
  34. Forecasting with Autofill 00:06:03
  35. Inserting Trend Lines 00:04:06
  36. Understanding the Types 00:04:00
  37. The R Squared Valie 00:03:58
  38. Tables and Ranges 00:02:48
  39. Named Ranges 00:02:48
  40. Named Ranges Continued 00:02:15
  41. Excel Tables 00:03:25
  42. Vlook up and Named Ranges 00:03:49
  43. Merging and Spliting Cells 00:01:58
  44. Upper and Lower Case 00:04:47
  45. Understanding Plugins and Add Ins 00:01:45
  46. Installing an Add In 00:01:45
  47. Collections of Add Ins 00:06:16
  48. Excel Backstage 00:06:06
  49. Data Analysis Tools 00:02:41
  50. Introduction to the Tools 00:02:41
  51. Descriptive Statistics 00:04:06
  52. Descriptive Statistics continued 00:06:17
  53. Histogram 00:02:50
  54. Histogram continued 00:02:58
  55. What if Analysis 00:02:41
  56. Overview of the Tools 00:02:41
  57. Goal Seek 00:02:55
  58. Goal Seek continued 00:03:38
  59. Scenario 00:04:47
  60. Scenario Continued 00:03:35
  61. Scenario Manager 00:03:31
  62. Solver 00:03:17
  63. Named Ranges and Solver 00:03:42
  64. Data Tables 00:03:30
  65. Understanding Data Tables 00:03:30
  66. Single Criteria 00:03:00
  67. Double Criteria 00:04:27
  68. Critera with Conditional Formatting 00:04:01
  69. Pivot Tables 00:02:50
  70. Intro to Pivots 00:02:50
  71. Tables and Pivots 00:04:09
  72. The Foundation 00:02:58
  73. Working with the Slicer 00:04:43
  74. Pivot Charts 00:03:59
  75. Calculated Fields 00:04:47
  76. Conditional Statements 00:04:03
  77. Highlighting Significant Data 00:04:03
  78. Icon Sets 00:05:37
  79. Formulas - Coloring Columns 00:04:06
  80. Formulas - Rows 00:04:00
  81. Customizing Icons and Highights 00:04:41
  82. Dashboards 00:05:01
  83. Creating a Dashboard Part II 00:05:01
  84. Creating a Dashboard Part III 00:05:05
  85. What is a Dashboard? 00:06:12
  86. Creating a Dashboard 00:04:31
  87. Creating a Dashboard Part I 00:05:01
  88. Automation 00:03:51
  89. Developer Toolbar 00:03:51
  90. Creating a Macro 00:04:26
  91. Creating Macro continued 00:04:03
  92. Absolute vs Relative 00:04:49
  93. Editing a Macro 00:06:18
  94. Autocorrect 00:03:40
  95. Report Writing 00:04:51
  96. Starting with a menu page 00:04:51
  97. Buttons rather than Hyperlinks 00:05:04
  98. Key Points or Instructions 00:04:55
  99. Interactive Spreadsheets 00:04:30
  100. Scroll Bars 00:04:22
  101. Radio Buttons 00:04:36
  102. The Formulas 00:06:30
  103. Presenting Data 00:04:14
  104. Utlizing Shapes 00:04:14
  105. Working with SmartArt 00:05:20
  106. Link Paste and Paste Special 00:03:14
  107. Edit Links 00:03:13
  108. Select Visible Cells 00:03:15
  109. Pasting into Powerpoint 00:03:53
  110. Motion in Powerpoint 00:03:53
  111. Motion Continued 00:04:56
  112. Comments and Review 00:05:12
  113. A Twist on Comments 00:05:12
  114. Comments with Data Validation 00:02:49
  115. Protecting your work 00:04:28
  116. Protection continued 00:04:21
  117. Review 00:04:34
  118. What have we covered 00:04:34
  119. Where to Next 00:04:38
  120. About the author 00:03:36
  121. Wrap Up 00:03:36