Get 20% discount

Mastering Pivot Tables – MS Excel 2010/2013

Introduction

These days almost any job requires data analysis & presentation of results. While anyone can put a list of values in Excel & sum them up, not everyone can do advanced analysis, create charts, make them interactive, summarize data intelligently, present output in an intuitive dashboard or slice & dice data using Pivot tables & Power Pivot. Having these vital skills can make you invaluable to your organization & expose you to new opportunities. Not to mention the amount of time, money & effort you can save by efficiently using Excel.

Who should go for this course?
This course is ideal for data analysts, reporting & MIS professionals, business analysts, managers & dashboard makers.

Power Pivot courses are for you if you are an advanced user of Excel & looking for ways to enhance your skills in data analysis, pivoting & reporting areas.

Explore the most popular and some of the lesser known features of Excel – PivotTables, PowerPivot, and PowerView!

These lessons will ensure that you can harness the power of Excel faster and more easily than ever before! The Course will increase your productivity and optimize Excel's best tools and features. Expert Sami simplifies powerful features including Formatting, Calculations, Sort & Filter, DAX Calculated Fields, Troubleshooting, Animation and more! Truly harness the power built into Excel! You’ll master high-powered skills at your own pace–and get specific answers and solutions immediately, whenever you need them!

Methodology

This will be an instructor led program delivered through hands on labs with real life example. Audience will taste a new level of experience by attending the course.

Contents of Training:

The Basics
Learn how to create your first PivotTable and then learn how to change that PivotTable! You'll look at the tools available and explore the interface. You'll learn how to:

• Use a Pivot Table to Summarize Detailed Data
• Your Manager Wants Your Report Changed
• Add or Remove Fields from an Existing Pivot Table
• Summarize Pivot Table Data by Three Fields
• Why Does the Pivot Table Field List Dialog Keep Disappearing?
• Move or Change Part of a Pivot Table
• Update Data Behind a Pivot Table
• Why Does This Look Different from Excel 2003? Return to the Classic Pivot Table Layout

Formatting
Discover PivotTable formatting! Learn how to overcome the system defaults to create useful-looking reports. You'll find out how to:
• Compare Four Ways to Show Two Values Fields in a Pivot Table
• Kill Compact View
• Eliminate Blanks in the Outline Format of a Pivot Table
• Suppress Grand Totals in a Pivot Table
• Replace Blanks in a Pivot Table with Zeros
• Specify a Number Format for a Pivot Table Field
• Format Pivot Tables with the Gallery
• Add a Format to Gallery
• Selecting Parts for Formatting
• Icon Set in a Pivot Table

Applying Conditional Formatting to PivotTables
• Highlighting cells by applying a rule
• Highlighting the top or bottom values in a PivotTable
• Formatting PivotTable cells using data bars
• Formatting PivotTable cells using color scales
• Formatting PivotTable cells using icon sets
• Editing conditional formatting rules
• Controlling how multiple rules are applied
• Deleting a conditional formatting rule

Calculations
This is all you need to cover powerful PivotTable calculations like percentage of column and rank! You'll learn how to:

• Create a Report That Shows Count, Min, Max, Average, Etc
• Percent of Parent, Rank, Running Total In
• Calculated Fields in a Pivot Table
• Add a Calculated Item for Budget - Actuals
• Instead of Using Calculated Items Group Text Fields

Sort & Filter
Who knew sorting & filtering could be so powerful! These partshows you useful ways to manipulate the data, including a new feature called a Slicer and Time Line. You'll learn how to:

• Present a Pivot Table in High-to-Low Order by Revenue
• Manually Re-sequence the Order of Data in a Pivot Table
• Limit a Pivot Report to Show Just the Top 12 Customers
• Filtering a Pivot Table
• Add Visual Filters to a Pivot Table with Slicers
• Timelines vs Multiple Slicers

Features
These part explore Sami's personal favorite features of PivotTables, including an amazing way to create a year-over-year report!

• See Detail Behind One Number in a Pivot Table
• Group Daily Dates by Month in a Pivot Table
• Create a Year-over-Year Analysis
• Stop GetPivotData from Appearing
• Group by Week in a Pivot Table
• Group Numbers to Create Frequency Chart
• Grouping one pivot table groups all pivot tables; new Cache

Some Other Use of Pivot Table
What other thing can PivotTables do for anyway? Sure, they're good for taking 100,000 rows of data and summarizing it down to a one-page report; but what are some real life uses of PivotTables? Sami will guide you through four prime examples in this section. You'll focus on how to:

• Create a Unique List of Customers with a Pivot Table
• Use a Pivot Table to Compare Two Lists
• Fixing Customer Name problems Using a Pivot Table
• Show Text Fields for a Pareto Chart

Tips
These eight tips will save you time and frustration! Here you'll learn how to:

• Base your pivot table on a Table When New Data Might Be Added
• Show Yes/No As the Values in a Pivot Table
• Create Perfectly Formatted Pivot Tables Using GETPIVOTDATA
• Create a Worksheet for Every Customer Using Show Report Filter Pages
• Controlling Multiple Pivot Tables with One Set of Slicers
• Reduce Pivot Table Workbook Size by 50% Before Sending
• Create a Pivot Table from Access Data
• Create a chart from a pivot table with Pivot Charts

Troubleshooting
• The five most common pitfalls are handled in this twenty minute segment on Troubleshooting. They are:
• Compatibility between versions
• Force Missing Months to Show in a Pivot Table
• Whatever Happened to Multiple Consolidation Ranges in Pivot Tables?
• Cleaning up bad data before creating a pivot table
• Pivot Data Where the Source Data Has Dates Stretching Across the Columns

Manipulating PivotTables Using Macros
• Recording and reviewing a macro
• Running a macro
• Creating a simple PivotTable presentation kit

PowerPivot
PowerPivot is the best thing that Microsoft has produced in last 20 years. These nine lessons will help you harness the power of PowerPivot! The topics include:

• Using the Data Model in Excel 2013 to Join Two Tables
• Distinct Count in Excel 2013
• True Top Five in Excel 2013
• Using Named Sets for Asymmetric Reports
• Getting PowerPivot
• Load Data into PowerPivot
• Define Relationships in the PowerPivot Window or Diagram View
• Creating the Pivot Table - Compare Choices
• Changing the Calculation from Sum & Other Differences

DAX Calculated Fields
Data Analysis Expressions! DAX is a new function language that's used with PowerPivot and PowerView. It makes Calculated Fields in PivotTables look sophomoric. The problem is, it's hard to learn. It's like going to the dentist. Everyone knows they have to do it, they just dont want to do it. Well, in these lessons, Sami gives you enough sample calculated fields, called Measures, to get you started.

• DAX Measures
• Use DAX Measures to Calculate a Range
• Use DAX Measures to un-apply a Filter
• Using a Calendar Table for Time Intelligence
• Time Intelligence Functions
• Disconnected Parameter Tables with FILTER()
• Actuals vs Budget
• Using IF(VALUES in a Measure
• Build Slicers on Lookup Tables
• First Initial Slicer (and turning off cross-slicer filtering)
• Convert the Pivot Table to Cube Formulas

Power View
Get Amazed by this New Feature of Excel 2013. Build Amazing Interactive Dashboards using PowerView! These twelvetopicswill make you up and running with PowerView:

• Preparing your data for PowerView
• Adding a Power View Worksheet
• Adding a Dashboard Element as a Table
• Converting Table to a Chart
• Explicit and Implicit Slicers
• Understanding the Filter Pane
• Using Tile Boxes
• Replicating Charts Using Multiples
• Showing Data on a Map
• Showing Images in Table or Card View
• Formatting Power View
• Animating a Scatter Chart over Time

Related Courses

Mostofa Monower
  • 26 May - 3 Jun 2017

This training program builds upon advanced level knowledge ...

IT
Price 3,000 Tk + VAT
Khan Mohammad Mahmud Hasan
  • Saturday, May 27, 2017

IT
Md. Shafiul Alam
  • 28 May - 3 Aug 2017

This hands-on object oriented PHP5 and MySQL training ...

IT
Price 18,000 Tk + VAT
Muhammad Rabiul
  • 30 May - 18 Jul 2017

IT

Contact

9140345, 9117179

workshop@bdjobs.com

Bdjobs.com Ltd.

19 th floor (East), BDBL Building (Old BSRS)
12 Karwan Bazaar
Dhaka - 1215

This course is ideal for data analysts, reporting & MIS professionals, business analysts, managers & dashboard makers.
Arrangement for Certificate, lunch and two tea-break would be made by the Organizer during the workshop.