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

Excel and Big Data

Video Description

Microsoft Excel is one of the most popular programs to use for analyzing, extracting, and cleaning the tremendous amounts of data that we have collected. Learn how Microsoft Excel can manage and summarize your data with PowerPivot and Pivot Tables, automation and coding, along with the standard features of Arrays, functions such as MATCH and INDEX, and the Data Analysis Tools. Understand speed and file size limitations while appreciating the art of data cleaning and manipulation. Explore how the What If Analysis tools can help with plans and forecasts and consolidate and link data for interactive reports. This course will allow you to unleash the power of Excel to make accurate decisions based on your data and Excel's power.

Table of Contents

  1. Introduction 00:05:53
  2. Introduction 00:05:53
  3. Work Files 00:02:47
  4. What is Big Data 00:06:18
  5. Working with Large Files 00:06:18
  6. Connecting to the Web 00:02:58
  7. Databses and Text Files 00:02:34
  8. The Connections Dialog Box 00:04:27
  9. File Size Connections 00:05:31
  10. Reviewing the Basics 00:05:00
  11. Tables 00:05:00
  12. Filters and Sort 00:03:46
  13. Filtering Tables and Slicers 00:03:07
  14. Subtotal Troubleshooting 00:05:24
  15. Subtotal Tricks 00:05:10
  16. Patterns and Sparklines 00:03:54
  17. Pivot Tables 00:06:12
  18. Data Extraction and Creation 00:06:12
  19. Calculations and Summarization 00:05:51
  20. Formulas 00:03:46
  21. Slicers and Filters 00:06:03
  22. Pivot Charts and Menu Formats 00:05:41
  23. Power Pivot 00:04:54
  24. Introduction to PowerPivot 00:04:54
  25. Relationships 00:05:10
  26. Adding Data 00:04:59
  27. DAX Overview 00:03:39
  28. Basic Syntax 00:04:52
  29. Power Pivot KPI 00:05:10
  30. Installing PowerPivot 00:06:00
  31. Working with PowerPivot DAX 00:02:19
  32. Dax in the Pivot Table 00:02:19
  33. Installing PowerPivot 00:06:00
  34. Option Add Ins 00:02:46
  35. Dynamic Charting 00:04:29
  36. Review of Charting Features 00:04:29
  37. Dialog Boxes 00:04:42
  38. Intro to Dynamic 00:06:41
  39. Using the Offest Command 00:03:41
  40. Dynamic Chart Continued 00:05:19
  41. Interpreting and Summarizing Data 00:06:24
  42. Conditional Formatting and IF 00:06:24
  43. Use IF and Formulas 00:02:15
  44. The AND operatore and IF 00:03:51
  45. Using a List with VLOOKUP 00:04:33
  46. VLOOKUP 00:03:26
  47. VLOOKUP with Approximate 00:02:39
  48. VLOOKUP with IF 00:02:57
  49. Matching Functions 00:03:51
  50. Create a Table of Contents 00:03:51
  51. Finding Duplicates 00:04:59
  52. Match 00:04:29
  53. index 00:03:04
  54. MATCH and INDEX Together 00:03:03
  55. Functions for Manipulatating Text 00:04:53
  56. String Extractions 00:04:53
  57. Case Functions and Concatenate 00:05:19
  58. Replace and Subsitiute 00:06:22
  59. Data Cleaning 00:03:50
  60. Cleaning Empty Cells 00:03:50
  61. Cleaning Continued 00:06:58
  62. Text to Column 00:02:34
  63. ISERORR Function 00:02:50
  64. Arrays 00:05:01
  65. About Arrays 00:05:01
  66. Creating 00:04:35
  67. Including IF Statements 00:03:28
  68. Array Exercies From the Help Menu 00:02:59
  69. Macros 00:05:48
  70. Getting Started with Macros 00:05:48
  71. Enhancing Usage and Automation 00:04:06
  72. Absolute vs Relative 00:05:09
  73. Viewing the Macro in VBA 00:04:30
  74. Invoking macros with Form Controls 00:05:18
  75. Forms Continued 00:06:34
  76. Options Buttons 00:03:01
  77. Security 00:02:31
  78. R!C1 Reference 00:04:22
  79. VBA 00:04:13
  80. The VBA Window 00:04:13
  81. Simple Code and Work Files 00:06:38
  82. Creating a Form 00:05:48
  83. Creating a Form Continued 00:04:34
  84. Adding Code 00:04:04
  85. Fine Tuning the Spreadsheet Form 00:04:13
  86. Custom Functions 00:03:54
  87. Understanding Custom Functions 00:03:54
  88. Creating a Function 00:04:00
  89. Properties 00:04:34
  90. Adding Descriptions 00:05:51
  91. Auditing and Other Features 00:05:05
  92. Tools for Formulas 00:05:05
  93. Tracing Calculations 00:04:15
  94. Tracing calculations continued 00:05:51
  95. Watches and Formula Bar 00:04:50
  96. Analysis Tools 00:03:51
  97. Toolpak 00:03:51
  98. Goal Seek 00:02:38
  99. Solver 00:04:24
  100. Add Ons 00:05:35
  101. Remembering the User 00:04:54
  102. Scenarios 00:04:03
  103. The Power of Scenarios 00:04:03
  104. Using Named Ranges 00:02:43
  105. Creating a Scenario 00:06:26
  106. Scenario Summary 00:04:00
  107. Dashboards 00:05:02
  108. Power of the Dashboard 00:05:02
  109. Templates 00:04:04
  110. Creating Dashboards 00:05:45
  111. Business Intelligence 00:07:47
  112. Wrap Up 00:04:42