top of page

Power Excel Training Program Outline

Explore Further

Overview & Objectives
Outline
Benefits
Who Should Attend
Program Takeaways
Fee & Schedule
REGISTER NOW
DOWNLOAD BROCHURE
Office Space

Day 1

Module 1:

 

Working with “Excel Table” format

  • From ‘regular range’ to ‘Excel table’

  • Working with ‘Design’ contextual tab

  • Dynamic ‘Slicers’ and ‘Total Row’

  • Table styles, options and configuring custom styles

  • Getting acquainted with the advanced applications of the feature

Module 2:

 

Spotlighting Dynamic data with Conditional Formatting

  • Creating value-based formatting using logical operators

  • Applying Top/bottom rules, using ‘Data bars’ and analyzing variance with ‘color scales.’

  • Creating custom conditional formatting rules

  • Creating rules using formulas, managing, and clearing rules

  • Being pro in applying effective conditional formatting in real business scenarios

  • Getting to know about data visualization and the science of colors

  • Understanding Excel Chart Concepts, elements, types, and ‘Chart Tools’ tabs

  • Creating custom chart templates

  • Working with Specific Chart Types such as Column and bar chart, Line chart, Area chart, Pie & Doughnut charts and creating ‘dual-axis’ charts

  • Fine-Tuning Charts with ‘Design Tab’ Choices, apply chart ‘layouts’ and ‘styles’

  • Changing chart options, modifying axes, adding and modifying chart titles, linking chart titles, adding data labels and data tables, adding gridlines, legends and trend lines

  • Inserting pictures, shapes, and Text Boxes on charts

  • Changing a Chart's Data Source and adding additional data series

  • Creating Specialized Excel Charts e.g. Combo Chart, Gantt Chart, Treemap, Histogram and Pareto chart and Waterfall charts

  • Working with ‘Sparklines’

Module 3:

 

Creating Powerful Dynamic Charts and Graphs

Condensing and refining data with Pivot Tables

  • Developing interactive PivotTable reports for real-time data analysis

  • Data refining rules, creating a Pivot table from an external data source, using ‘Recommended PivotTables’, pivoting and configuring PivotTables

  • Summarizing Data in PivotTable, applying totals, summarizing values, calculated fields and items, grouping data, applying calculations, and drill down Pivot Table results

  • Filtering and Sorting PivotTable, adding dynamic ‘Slicers’ and ‘Timeline’

  • Formatting PivotTables, creating custom styles and modifying the layout

  • Integrating conditional formatting with Pivot Reports

  • Macro integration with Pivot reports

Smart Pivot Charts for data visualization

  • Creating pivot charts for data visualization and moving towards dashboard designing

Module 4:

 

Summarizing Business Information with Pivot Tables

Day 2

  1. Formula and Function tools, tips, shortcuts & working with 3D formulas

  2. Understanding and defining ‘named ranges’

    • Name cells, name data ranges, name tables, manage named ranges

  3. Summarizing data

    • Performing calculations by using the SUM, MIN & MAX functions, performing calculations by using the COUNT, COUNTA, COUNTBLANK & AVERAGE functions

  4. Conditional operations

    • Performing logical operations by using IF, AND, OR, NOT, ISNUMBER & IFERROR functions, performing logical operations by using the SUMIF, AVERAGEIF, COUNTIF, SUMIFS, AVERAGEIFS & COUNTIFS functions

  5. Look up data by using VLOOKUP, HLOOKUP, MATCH & INDEX functions

  6. Applying Mathematical Functions

    • Applying ROUND, MROUND, ROUNDUP, ROUNDDOWN, CEILING & FLOOR & ABS functions

  7. Applying advanced date and time functions

    • Referencing Date and Time by using TODAY, NOW, WEEKDAY, WORKDAY, NETWORKDAYS, EDATE, EOMONTH & DATEDIF functions; serialize numbers by using DATE function

  8. Array Formulas and Functions

  9. Format and modify text by using functions, TRIM, LEFT, RIGHT and MID functions; format text by using UPPER, LOWER, PROPER, LEN & CONCATENATE functions

  10. Troubleshoot formulas

    • Trace precedents and dependents, monitor cells and formulas by using the ‘Watch Window’, validate formulas by using error checking rules

Module 5:

 

Advanced Formulas and Functions

  • Performing extract, transform, and load (ETL) functions with Power Query

  • Smartly automating repetitive and laborious tasks

  • Getting data from Excel, text, and CSV file

  • Extracting all files from a folder to create one data set

  • Creating connection with external data sources and databases

  • Managing rows and columns and understanding ‘Transform’ and ‘Add Column’

  • Text Specific Tools, formatting, merge, split and extract columns

  • Number Specific tools, adding new column using standard functions, applying rounding

  • Date specific tools, creating a full calendar from a single date field

  • Removing duplicates, adding an index, and conditional column

  • Grouping, filtering, Transposing, and reverse rows features

  • Editing, deleting, renaming, and duplicating queries

  • Un-pivoting columns to create tabular data

  • Merging and Appending queries

  • Managing data types to avoid errors in the data model

  • Introduction to Power Query “M” language & ‘Query Editor’

Module 6:

 

Connecting and transforming data with “Power Query”

  • Exploring Excel’s Data Model

  • Understanding what Data Model is and why we need it

  • Data vs Diagram view

  • Normalization and Denormalization

  • Facts vs dimensions tables

  • In-depth understanding regarding Primary vs Foreign keys

  • Creating & modifying table relationship

  • Creating a clear understanding regarding relationships vs Merged Tables

  • Learning cardinality of relationships & filter direction

  • Creating ‘Star Schemas’

  • Defining hierarchies and hiding fields from client tools

Module 7:

 

Managing table relationships and Data Models

Day 3

  • Launching Power Pivot and touring the interface

  • Creating a Power PivotTable

  • Learning Power Pivots vs Normal Pivots

  • Calculated columns & calculated fields

  • Getting external data directly in Power Pivot

  • Using ‘Sort by’ the command to sort any column with your own choice

  • Creating dashboards in Excel directly from Power Pivot

Module 8:

 

Power Pivot in Excel

  • Intro to Data Analysis Expressions (DAX) formula language

  • Similarities and differences with Excel formula language

  • Calculated Columns vs DAX Measures

  • Implicit vs Explicit Measures

  • Row Context vs Filter Context

  • Knowing DAX Formula Syntax & Operators

  • Learning Measure calculation Step-by-Step

  • Creating & managing KPIs to measure business performance

  • Getting to know the best practices in writing DAX queries

  • Common DAX functions with their category

​

  1. Basic math and stat functions

    • SUM, AVERAGE, MAX, MIN, DIVIDE, COUNT, DISTINCTCOUNT functions

  2. Logical functions

    • IF, AND, OR, SWITCH & SWITCH(TRUE) functions

  3. Iterator (X) Functions

    • SUMX & RANKX functions

  4. Table Functions and CALCULATE

    • CALCULATE function

    • FILTER & ALL function, Adding Filter Context with FILTER function, RELATED function

  5. Time Intelligence with DAX functions

    • Understanding time intelligence & Calendar Table

    • SAMEPERIODLASTYEAR, DATEADD, DATEDIFF, DATESYTD, DATESQTD, DATESMTD, PARALLELPERIOD, TOTALYTD, TOTALQTD, TOTALMTD functions

Module 9:

 

Solving Data Analysis Problems with DAX

Module 10:

 

Interactive Dashboard Designing for Decision Making

  • Cleaning up data and starting with the end in mind

  • Creating dashboards for data-driven decision-making

  • Making the dashboard easier to review, understand and conclude

  • Creating Dashboards using Pivot tables and Pivot Charts

  • Creating Dashboards using Power Pivot

  • Creating Dashboards using Power View

  • Creating Dashboards using Data validation and logical functions

  • Enabling Power View and installing “Silver Light”

  • Creating Matrix, table, and card visuals to present data

  • Adding ‘callouts’ cards to put the focus on an important info

  • Drill down data using hierarchy

  • Managing data with perspectives

  • Adding power view charts to bring data to life

  • Adding report filters to slice and dice the data visually

Module 11:

 

Bring your data to life with Power View Dashboards

bottom of page