Learn New Technologies 4everyone

Excel and Big Data 

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.

Introduction

Welcome
Work Files

What is Big Data?

Working with Large files
Connecting to the Web
Databases and Text Files
The Connections Dialog Box
File Size Connections

Reviewing the Basics

Tables
Filters and Sort
Filtering Tables with Slicers
Subtotal Troubleshooting
Subtotal Tricks
Patterns and Sparklines

Pivot Tables

Data Extraction and Creation
Calculations and Summarization
Formulas
Slicers and Filters
Pivot Charts and Menu Formats

PowerPivot

Introduction to PowerPivot
Relationships
Adding Data
PowerPivot KPI
Installing PowerPivot
Option Add Ins

Working with PowerPivot DAX

DAX Overview
Basic Syntax
Dax in the Pivot Table

Dynamic Charting

Review of Charting Features
Dialog Boxes
Intro to Dynamic Charts
Using the Offset Command
Dynamic Chart Continued

Interpreting and Summarizing Data

Conditional Formatting and IF
Use IF and Formulas
The AND operator and IF
Using a List with a VLOOKUP
VLOOKUP
VLOOKUP with Approximate
VLOOKUP with IF

Matching Functions

Create a Table of Contents
Finding Duplicates
MATCH
INDEX
MATCH and INDEX together

Functions for Manipulating Text

String Extractions
Case Functions and CONCATENATE
REPLACE and SUBSTITUTE

Data Cleaning

Cleaning Empty Cells
Cleaning continued
Text to Column
ISERROR Function

Arrays

About Arrays
Creating
Including IF Statements
Array Exercises from the Help Menu

Macros

Getting Started with Macros
Enhancing Usage and Automation
Absolute vs Relative
Viewing the Macro in VBA
Invoking macros with Form controls
Forms continued
Option Buttons
Security
R1C1 Reference

VBA

The VBA Window
Simple Code and WorkFiles
Creating a Form
Creating a Form Continued
Adding Code
Fine tuning the Spreadsheet Form

Custom Functions

Understanding Custom Functions
Creating a Function
Properties
Adding Descriptions

Auditing and Other Features

Tools for Formulas
Tracing calculations
Tracing calculations continued
Watches and Formula Bar

Analysis Tools

Toolpak
Goal seek
Solver
Add Ons
Remembering the User

Scenarios

The Power of Scenarios
Using Named Ranges
Creating a Scenario
Scenario Summary

Dashboards

Power of the Dashboard
Templates
Creating Dashboards
Business Intelligence

About the Author

Wrap Up