Big Data in Excel - Excel 2010/2013 Power Pivot

Name: Big Data in Excel - Excel 2010/2013 Power Pivot
Rating: 3.7 (10 reviews)

Don't just crunch Data - Crush it!

Created byMangirdas Adomaitis

Last updated 5/2014

English

English [Auto],

What you'll learn

Use excel to work with big data
Work with diverse multiple data sources
Better understand how databases works
Creation of pivot tables and pivot charts
Build custom dashboards to answer business questions

Course content

9 sections • 26 lectures • 1h 23m total length

Power Pivot overview3:48
Introduction to the course. How and what we will learn!
Course Logistics1:56
Let's talk a bit about how the learning will take place. Some tips to even better understand the topics.
Downloadable material0:15
Here are the data used later in the course. If you consider recreating example yourself or just trying things out - please download these files and find file you are interested in.

Good luck crunching big data!

Installing Power Pivot for Excel 2010 or 20134:05
Installing Power Pivot is a breeze:

Excel 2010:

Go here.

Press download and select "1033\x64\PowerPivot_for_Excel_amd64.msi" for 64 bit machine or "1033\x86\PowerPivot_for_Excel_x86.msi" for 32 bit machine.

After downloaded open the install file.

Use default settings for installation.

After installation is completed - open excel and Power Pivot ribbon will be available.

Excel 2013:

Power Pivot comes pre-installed with Office Professional Plus or Office 365. However, it should be still enabled:

Go to File > Options -> Add-Ins.

In the Manage box, click COM Add-ins -> Go.

Check the Microsoft Office Power Pivot in Microsoft Excel 2013 box, and then click OK.

Power Pivot ribbon will be enabled.

Text Files3:16
Import text data into Power Pivot to build a product table from text files and compute revenue by subtracting cost from price, with live refresh when sources change.
Excel Files3:23
Access Database5:10
Import and integrate Access databases into Excel using Power Pivot, choosing between table import or custom queries, adjust prices, edit connections, and build pivot tables from imported data.
SQL Database4:00
Learn how to connect to a SQL database from Power BI, configure the connection wizard, and import data from multiple tables or write a query.
Other Possibilities0:22

Single Table4:59
Create calculated columns in Power Pivot using DAX, calculate revenue per row with unit price 98, and apply logical filters. Learn table references, naming, quotes, using results in pivot tables.
Example4:00
Aggregate transactional sales data by year or month and visualize trends in Excel using pivot table and Power Pivot. Create year and month fields, add slicers, and chart units sold.
Multiple Tables4:14
Learn to fetch data from another table using the related function and establish relationships between tables to compute revenue and exact profit from units sold.
Assignment - "Forest data analysis"1:09
Additional material0:10

Simple Tables4:31
Learn to build pivot tables in Power Pivot, analyzing revenue by region, year, and store with measures like average revenue, using filters and subtotals.
Customization4:06
Explore pivot table customization in Excel: toggle subtotals and grand totals for rows and columns, adjust layout to tabular form, show banded rows, and enable per-column filters for easy copying.
Slicers5:43
Explore how slicers enable interactive exploration in Excel Power Pivot, linking multiple pivot tables, filtering by region and product, and customizing slicer connections for consistent totals.
Filters6:11
Apply slicers and filters in Excel Power Pivot to connect multiple pivot tables, filter by store name and year, and synchronize revenue and units across tables.
Assignment - "Building dashboards!"1:08

Requirements

Computer with excel version 2010 or higher
Introductory knowledge of excel would be good to have, but not required

Description

Learn big data analytics directly in Excel 2010 or 2013.

Power Pivot is free Excel add-in developed by Microsoft. It is designed to deal with large complex data sets. No programming required!

Always working on real examples - the course is specifically based on real world use cases. No long slides, but rather hands on examples. All material used in lectures is available for download. Students are highly encouraged to experiment with data themselves.

Comprehensive assignments will be available after each section. These will let you check newly acquired knowledge in practice.

At any time I will be available to answer questions or simply discuss some interesting topic.

At the end of the course you will feel comfortable in multiple areas:

Working with large datasets
Combine data from totally different data sources much more effectively
Best practices on working with pivot tables and pivot charts
Creating interactive dashboards to answer business questions
Better understanding how databases are designed

Taking into account exercises and assignment, course could be completed in a ~4 days 2-3 hours per day.

Let's get ready for era of big data.

Who this course is for:

Ones interested in better ways to extract value from the data
Excel practitioners looking for ways to leverage Excel more effectively
Anyone who would like to sharpen their analytical skills

Big Data in Excel - Excel 2010/2013 Power Pivot

What you'll learn

Explore related topics

Course content

Introduction3 lectures • 6min

Installation1 lecture • 4min

Data Import5 lectures • 16min

Tables and Data Model2 lectures • 8min

Relationships3 lectures • 11min

Calculating Columns5 lectures • 15min

Building Pivot Tables5 lectures • 22min

Additional material1 lecture • 1min

Final Thoughts1 lecture • 1min

Requirements

Description

Who this course is for: