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

Product information

  • Title: Big Data Analytics with Excel
  • Author(s): Guy Vaccaro
  • Release date: March 2016
  • Publisher(s): Infinite Skills
  • ISBN: 9781771375795