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

Big Data Analytics with Excel

Video Description

In this Big Data Analytics with Excel training course, expert author Guy Vaccaro teaches you how to manage large quantities of data with Excel. This course is designed for users that are already familiar with Excel and how to navigate a workbook and manage worksheets.

You will start by learning basic data manipulation, then jump into learning about data cleansing, including removing duplicate records, replacing text, and merging and splitting data columns. From there, Guy will teach you about Excel tables, pivot tables, charts, and pivot charts. This video tutorial also covers Power Query, Power Map, and Power Pivot. Finally, you will learn how to use Goal Seek and Solver, as well as learn about data analysis tools.

Once you have completed this computer based training course, you will have learned everything you need to know to effectively manage large quantities of data with Excel. Working files are included, allowing you to follow along with the author throughout the lessons.

Table of Contents

  1. Introduction
    1. Introducing Excel Big Data 00:04:46
    2. About The Author 00:01:17
    3. Using The Working Files 00:01:33
  2. Basic Data Manipulation
    1. Fixing Headers For Screen And Print 00:05:00
    2. Sorting Data 00:04:50
    3. Filtering Data 00:05:41
    4. Add A Chart In A Second 00:03:18
  3. Data Cleansing
    1. Starting Out The Cleaning Process 00:05:08
    2. Removing Duplicate Records 00:05:49
    3. Changing The Case 00:05:40
    4. Replacing Text Method Part - 1 00:06:21
    5. Replacing Text Method Part - 2 00:06:16
    6. Removal Of Non Printing Characters And Spaces 00:07:37
    7. Numbers Standardized On Import And Export 00:06:48
    8. Dealing With Date And Time Issues 00:09:24
    9. Merging And Splitting Data Columns 00:09:04
    10. Rearranging Columns And Rows 00:07:31
    11. Comparing Multiple Datasets 00:06:43
  4. Excel Tables
    1. Creating A Table 00:05:19
    2. Formatting Made Easy 00:03:14
    3. Managing Rows And Columns 00:05:33
    4. Sorting And Filtering Table Data 00:07:49
    5. Using The Totals Row 00:05:48
    6. Adding A Calculated Column 00:03:54
    7. Adding Charts And Pivot Tables Using Table Data 00:05:26
    8. Data Entry And Editing Through A Form 00:06:43
    9. Filtering Using The Slicer 00:05:28
    10. Advanced Filtering 00:08:00
    11. External Data As A Table Source 00:05:54
  5. Pivot Tables
    1. Introducing Pivot Tables 00:07:26
    2. Recommended Pivot Tables 00:04:16
    3. Use Named Ranges And Rename Column Headings 00:04:27
    4. Change The Data Function And Format The Numbers 00:06:47
    5. Moving And Or Removing A Pivot Table 00:04:04
    6. The Report Filter Option 00:04:16
    7. Sorting And Filtering By Pivot Table Columns 00:05:28
    8. Refreshing A Pivot Table 00:05:30
    9. Drilldown Behind Pivot Table Numbers 00:03:57
    10. Using Pivot Table Styles 00:04:15
    11. Use Of Multiple Fields In Rows And Columns 00:05:18
    12. Grand And Sub Totals 00:06:30
    13. Filtering Columns And Rows Within A Pivot Table 00:06:35
    14. Exploring Additional Options 00:05:12
    15. Using The Data Slicer 00:06:06
    16. Connecting To A SQL Server Database 00:06:00
    17. Using External Connection Files 00:04:49
  6. Charts
    1. Creating And Using Charts 00:05:51
    2. Using Trendlines 00:08:13
    3. Dynamic Named Ranges 00:09:13
    4. Creating A Forecast Sheet 00:05:10
    5. Plotting On Two Axis 00:04:09
  7. Pivot Charts
    1. Creating A Pivot Chart 00:05:38
    2. Altering Chart Design And Location 00:04:07
    3. Filtering And Hiding Elements 00:05:17
  8. Power Query
    1. Introducing Get And Transform The New Power Query 00:07:57
    2. Making The Most Of The Query Editor 00:08:52
    3. Using The Query Editor To Group Or Combine 00:06:06
    4. Using An Odata Feed And Merging Data 00:07:07
    5. Using Google Sheets As Your Source Data 00:05:47
    6. Connecting To Using And Combining Webpage Data 00:07:30
    7. Connecting To And Using Data From Facebook 00:07:12
    8. SQL Server Data Connections 00:06:23
    9. Get And Transform Data From Multiple Files In A Folder 00:06:45
  9. Power Map
    1. Activating And Creating Your First 3D Power Map 00:05:11
    2. A Simple Map Alternative To 3D Power Map 00:04:12
    3. Tours Scenes And Layers 00:06:29
    4. Create A Tour And Customize A Layer 00:06:40
    5. Displaying Additional Data With Additional Layers 00:05:13
    6. Adding And Animating Scenes 00:06:49
    7. Filtering Data Within A Layer And Scene 00:05:04
    8. Customization Options 00:06:18
    9. Exporting Images And Videos Of Your 3D Map Tour 00:05:00
  10. Power Pivot
    1. Introducing Power Pivots 00:05:03
    2. Importing Data And Adding Additional Data To The Data Model 00:04:37
    3. Adding Tables To The Data Model And Creating Relationships 00:06:06
    4. Powerpivot Pivotstables And Pivotcharts 00:06:10
    5. Adding Calculated Fields To Your Data Model 00:08:47
    6. Measures And KPIs In Your Powerpivot 00:08:54
  11. Goal Seek And Solver
    1. Use Goal Seek To Carry Out What If Analysis 00:05:22
    2. Installing And Uninstalling Solver 00:03:12
    3. Use Solver To Carry Out What If Analysis 00:07:09
    4. Using Scenarios 00:08:25
    5. Adding Constraints To Solver 00:05:44
  12. Data Analysis Tools
    1. Introducing The Analysis Toolpack 00:03:40
    2. Calculating And Graphing The Moving Average 00:03:43
    3. Creating A Histogram With A Bell Curve 00:07:55
  13. The End
    1. Sharing Your Big Data 00:05:45
    2. Resume 00:06:28