Learning Path
Excel Expert
Skill Level
Advanced
Application Deadline
November 03, 2022
Start Date
November 04, 2022
Instructor-Led Live Session
Live Session Recording
Expert Support
Case Studies & Projects
Dedicated Resource Portal
Limited slots available
06 Weeks (Fri and Sun)
Fri 08:30 PM to 10:30 PM
Sun 01:00 PM to 03:00 PM
(12 Sessions)
Excel dashboards are a powerful way to leverage Excel functionality, create and manage better presentations, and improve your Excel and data visualization skills.
In this advanced Excel Dashboards course, we’ll look at how to prepare your data for analysis, as well as the formulas you’ll need to create dashboards in Excel, Pivot Tables, Pivot Charts, Form Controls, and much more.
Finally, as end-of-course projects, we show you how to use all of your skills to create a Sales Dashboard, an HR Dashboard, a Personal Financial Dashboard, and a Web Analytics Dashboard.
If you need to analyze and present data in Excel, as well as learn how to create awesome dashboards in Microsoft Excel, this course is for you.
We will cover
- key Design principles of dashboards, the creation process, color tips, and layouts.
- Methods of importing data into Excel.
- Data analysis using pivot tables and charts.
- Useful Excel formulas for creating dashboards
- Excel form controls
- Basic and advanced charting techniques, like sparklines, conditional formatting, custom formatting, etc.
- Dynamic dashboard components, like drop-down lists, filters, small multiples, infographics, etc.
- Protect and share the dashboard with the outside world.
You will build 4 complete dashboards.
Prerequisite
Attendees must have Excel proficiency equivalent to our Excel for Everyone course
Course Content
You will get
- Exercise and Project files
- Pre and Post Assessment
- Quizzes and Homework assignments
- 1-month access to the resource portal
- HD Recording of all live sessions
Certificate of Completion
You will be awarded a course completion E-Certificate by TechTAR Solutions after fulfilling the course requirements.
- Excel users who are looking to efficiently analyze large data sets and create dynamic dashboard reports
- Analysts, Data Scientists, or BI professionals who want to develop expert-level data visualization and design skills
- Anyone who wants to learn how to tell clear and compelling stories with data
- Looking to improve the design of your existing Excel reports
- This course is for INTERMEDIATE or ADVANCED Excel users. If you are brand new to Excel with no prior knowledge, this course is NOT for you.
- Classes will be interactive throughout & LIVE on Zoom. You would be able to ask questions & share views with the Technical Assistant, Trainer & class other participants.
Recommendations:
- Computer with internet access, a webcam, microphone, and Zoom App.
- Microsoft Excel 2016+ or Office 365 (ideally for PC/Windows)
Section 1: Data Preparation and Analysis
Data Preparation & ETL
- What is Clean Data?
- 6 steps for preparing Proper Data Set
- Loading Data with Power Query (Get & Transform)
- Overview of Excel Power Query
- Why Power Query?
- Importing External Data from Text File
- Importing Data From Excel Spreadsheet
- Importing Data from Folder
- Merge data in Power Query (for Pivot Table)
- Clean up Messy Excel Data with Power Query
- Remove Blanks and Duplicates
- Applying Proper Data Type
- Dealing with multiple headers to unpivot, etc
Excel Tables
- Introduction to Excel Tables
- Inserting a New Excel Table
- Working with Tables
- Excel Tables Demo
- Rules for using Excel Tables
Analyzing Data with Pivot Tables
- Creating PivotTable
- Choosing Fields
- PivotTable Layout and Style Options
- Filtering PivotTables
- Modifying Pivot Table Data
- Number Formatting
- Value Field Settings
- Show Values As
- Changing Data Source
- Slicers and Timelines
- GETPIVOTDATA Function
- Conditional Formatting
- Create Custom Conditional Formatting Rules
- Manage Conditional Formatting Rules
- ASSIGNMENT 1: Social Media Dashboard
Analyzing Data with Power Pivot
- Understanding Power Pivot
- Data Vs Diagram View
- Understanding Dimension vs Data Table
- Creating Relationships between Tables in the Model
- Managing Relationships
- Hiding Fields from Client Tools
- Defining Hierarchies
- Creating Date Table
Charting Technique and Pivot Charts
- Working with Excel Charts
- Excel Chart Basics for Quick Start
- Default Chart Type
- Change Chart Type
- Add Chart Elements
- Modify Chart Elements
- Switch between rows & columns
- Add a data series to the chart
- Apply Chart Layout
- Apply Chart Style
- Move Chart
- Mastering Common Excel Charts
- Line Chart
- Area Chart
- Pie & Doughnut Charts
- Scatter & Bubble Charts
- Combination Charts
- Sparklines
- Column Chart with Grouped Data
- Dynamic Chart Ranges with Excel Tables
- Chart Formatting Options
- Dynamic Chart Title
- Reversing chart categories
- Vertical Axis Options (Bounds)
- Horizontal Axis Options (Dates)
- Showing the labels horizontally
- Error Bars
- Snap to Grid
- Saving chart as a template
- Charting Techniques
- Overlapped bar charts and Series Gaps
- Negative bars
- Highlighting the maximum or minimum item automatically
- Hidden Source Data
- Adding an average line to a chart
- Chart Properties (Move & Size)
- Building Pivot Charts
- Create PivotCharts
- Manipulate options in existing PivotCharts
- Apply styles to PivotCharts
- Drill down into PivotChart details
- Camera Tool
- Inserting the camera tool
- How to use the camera tool?
- When to use Camera Tool?
- Common Charts Mistakes
Section 2: Dashboard Formula and Tools
Dashboard Formula Toolbox
- Relative and Absolute references in Formulas
- Range Names in Formulas
- Explicit Vs. Structured References
- Define a Named Range
- Name Manager
- Copy And Paste All Named Ranges
- Naming Convention
- Locating Name Range
- Excel Functions
- IF and NESTED IF Formulas
- IF Function
- Nested IF
- Logical Operators
- IF Statements – Conditions
- IFERROR Statements
- N Functions
- Aggregation, Filtering, and Sorting Functions
- COUNTIFS,
- SUMIFS,
- AVERAGEIFS
- SUMPRODUCT
- LARGE
- SMALL
- Math and Trigonometry functions
- INT
- MOD
- TEXT Functions
- CONCATENATE
- UPPER, LOWER, PROPER, LEN
- TRIM
- LEFT,MID,RIGHT
- FIND
- SEARCH
- REPT
- TEXT
- Lookup Function
- VLOOKUP
- HLOOKUP
- INDEX
- MATCH
- CHOOSE
- INDIRECT
- ROW
- COLUMN
- ROWS
- COLUMNS
- OFFSET
- IF and NESTED IF Formulas
Interactive charts with Form Controls
- Introduction to Form Controls – Active X vs. Form Controls
- Combo Box Drop-Down Lists
- Dependent Combo Boxes (2nd list automatically resets)
- Check Box – Check / Uncheck Option in Excel
- Spin Button – Move Up & Down Lists in Excel
- Option Button – Choose One Option Only
- Group Boxes
- List Box – Select From a List of Options in Excel
- Scroll Bar – Scroll Through Excel Graphs & Tables
- Assignment 2: Dynamic Heat Map using Scroll Bar & Formulas
Advanced Excel Chart Techniques
- Progress Doughnut Chart
- Excel Infographics: People Graph
- Funnel Chart
- Emphasizing & Mark Significant Events
- Line Graph: Multiple Lines
- Small Multiples
- Stacked Column Chart with Total Values
- Visual Overlay
- Image Lookup
- Dynamic Chart Titles and Slicer Macros
Protecting and Sharing Dashboard
- Protect your Excel Dashboard
- Linking Your Excel Dashboards to PowerPoint
Section 3: Dashboard Design and Inspiration
Introduction to Dashboard
- What is a Dashboard?
- Why do we Visualize Data?
- Types of Dashboard
Dashboard Setup
- Dashboard Creation Process
- Understanding Business and Data
- Choosing Right Metrics and Visual
- Techniques of Data Storytelling
- Spreadsheet Architecture
- Pro Tip: MVC Rule
Dashboard Design Principles
- The 3 Commandments
- Tools of a Dashboard Developer
- Poor & Good Dashboard Design
- Gestalt Psychology
- Color Choice
- Finding and Telling Story with Data
- Visual Elements
- Adding Context
- Dashboard Layouts
Section 4: Projects
Your FIRST Milestone - Regional Sales Dashboard
Your FIRST major milestone project. In this section, We will apply all the techniques you have learned to create a Regional Sales Dashboard for different real-world scenarios.
Your SECOND Milestone- HR Analytics Dashboard
Your SECOND major milestone project. Here you will combine all the methods you learned, to create an HR Analytics Dashboard.
Your THIRD Milestone- Personal Financial Dashboard
Your THIRD major milestone project. In this section, we will combine all the techniques you have learned throughout the training and create a Personal Financial Dashboard.
Final Project : Your FOURTH Dashboard - Web Analytics Dashboard
Your FIFTH major milestone project. Here you will combine all the methods you learned, to create a Web Analytics Dashboard. It will be a GRADED project, which means that its marks will be included in your performance report.
Individual Price
PKR 12,000
Per Person
Total charges for complete training
Group of Two
PKR 10,800
Per Person
Avail 10% off on a Group of two
Group of Three
PKR 10,200
Per Person
Avail 15% off on a Group of three
Group of Four or more
PKR 9,600
Per Person
Avail 20% off on a Group of four or more
Course Reviews
We have engaged TechTAR Solutions for a number of session in Excel and Power BI for our employees. Sohail Merchant is wonderful to deal with when making the bookings and the high level of experience the trainers hold is evident. We will definitely use them again in the future.!
I was tasked with seeking out a company to assist us in recreating our department’s appraisal Matrix and after meeting with TechTAR Solutions’ consultant, I knew they were the right fit to help get the job done! Their expertise and understanding of our needs made the whole process so simple.
Sohail Merchant from TechTAR Solutions’ [ explained the theory very well, also gave us sufficient room to ask questions and clarify our queries. Enough resources were provided to read and increase our knowledge on individual capacity..
Lots of useful information, Ideas & strategies with many practical examples. I learned a lot throughout the course & implementing it in my business. Sohail Merchant from TechTAR Solutions’ expertise gave me an amazing learning experience. Thank you.
The professionalism and Personalized service that we got from TechTAR Solutions was unlike any other company we worked with and they made us feel so comfortable by their natural communication and let’s do it attitude. Incredibly trustworthy and high value! Thankyou!
Please keep developing the products and solutions to make our lives more easier and work to be more focused efficient targeted and productive. Appreciate the contributions of the entire community in this.