Welcome to the exciting world of Data Analysis and Visualization with Microsoft Excel at IIPD Global in Dubai! In today's data-driven landscape, the ability to decipher and present insights from vast amounts of information is an invaluable skill.
 This comprehensive course is meticulously designed to empower individuals with the proficiency to harness the potential of Microsoft Excel for data analysis and visualization.
Whether you're a novice seeking to develop a strong foundation or a seasoned professional aiming to enhance your analytical capabilities, this course will equip you with practical techniques and strategies to navigate through data challenges effectively.
Course Duration: 3 DaysÂ
Training Methodology
The trainer is a Microsoft Certified Trainer and he has the highest certificate in Microsoft Office and more specifically in Microsoft Excel. The trainer has official practice files that are officially used for the Data Analysis and Visualization with the Microsoft Excel exam. We will use these files to practice on during the course.
The session is completely interactive. The trainer will ask the participants to open every practice file at a time and then we will work on it together. The trainer will be sharing his screen, and after every key press, the trainer waits a little bit to make sure that the trainees are following up with the instructor and they are pressing the same buttons. Moreover, the trainer keeps on asking the participants questions to make sure that they really understood the material.
At the beginning of the course, the trainer asks the trainees to solve a set of problems/challenges in order to evaluate their level. Based on the number of correct answers, the trainer chooses the appropriate level for the participants.
Course Objectives
- At the end of this course, participants will be able to:
- Perform data analysis fundamentals.
- Visualize data with Excel.
- Analyze data with formulas and functions.
- Analyze data with PivotTables.
- Present visual insights with dashboards in Excel.
- Create geospatial visualization with Excel.
- Perform statistical analysis.
- Get and transform data.
- Model and analyze data with Power Pivot.
- Present insights with reports.Â
Course Outlines
- Course Introduction
- What is Data Analysis
- What are the various ways to do Data Analysis
- What are the various software and tools used to do Data Analysis
- What is the difference between Microsoft Excel, Google Sheets, Microsoft Power BI, Tableau …
- What is the Data Analysis Process
- What are the 5 steps of the Data Analysis Process
Topic 1: DEFINE THE QUESTION
- What are the various Analytical questions that can be extracted from the data to get the most insights out of our data
- How to ask questions on one column (Ex. How many products do I have?)
- How to ask questions from two columns (Ex. Which product got the highest sales quantity?)
- How to ask questions from three or more columns (Ex. Which product got the highest sales quantity in the North region?)
- How to ask questions related to the TOP / BOTTOM
- How to ask questions related to time (Ex. In which quarter we had the highest sales?)
- Day 1 | From 01:00 PM To 02:00 PM | Lunch
Topic 2: COLLECT THE DATA
- Which one should be done first: Ask the questions or collect the data?
- What happens if we collect the data before asking the questions?
- What are the various ways to collect the data
- What are the common mistakes that people make when collecting the data
- How to connect to external data sources so data will be automatically connected to Excel / Sheets and it will be automatically refreshed when the data gets updated from our system / ERP
- What is Data Validation? Why do I apply Data Validation? What happens if I don’t apply data validation?
- How to apply a Table? When creating a Table is essential in collecting the data
- How to apply the necessary formatting when collecting the data
- Should I collect First Name Last Name separately, or I collect Full Name all together?
Topic 3: CLEAN THE DATA
- Why do I need to clean the data? Why is the Data Validation itself not sufficient?
- What could go wrong if I don’t clean my data
- Why I should NOT use the cleaning features available by default in Microsoft Excel / Sheets
- What is Power Query? How to properly use the Power Query to clean the data?
- How to clean data by using the first row as header
- How to clean data by splitting the first name and last name
- How to clean data by removing non printable characters and trim extra spaces
- How to clean data by automatically capitalizing the words
- How to clean data by removing duplicates
- How to clean data by removing blank rows
- How to clean data by adding calculated columns
Topic 4: ANALYZE THE DATA
- What are the various ways to do Data Analysis
- How to create a Pivot Table from a data range? How to create a Pivot Table from a table? Which one is better and what is the difference between them
- How to answer all the questions that were posed in STEP 1
- What is the difference between the various Pivot Table areas (Values, Row, Column, Filter)
- How to add additional functions by adding data to the data model
- How to connect multiple data sources by using the Power Pivot
- How to create a slicer to filter data dynamically
- How to create a timeline to filter data by days, months, quarters, and years
- How to group data with time factor by days, months, quarters and years
- How to move a pivot table? How to delete a pivot table?
- How to create multiple Pivot Tables on one sheet
- How to connect all the Pivot Tables together so whenever I filter one item on the slicer, it will be automatically filtered and updated on all Pivot Tables
- How to make our Pivot Tables refresh automatically every 1 minute
- How to analyze data using functions? What is the difference between analyzing data using Functions and using Pivot Tables
- How to apply the SUMIFS, AVERAGEIFS, COUNTIFS
- How to search and retrieve data using the VLOOKUP, HLOOKUP, MATCH AND INDEX? When do I use the Vlookup and when do I use the Match and Index?
- How to use the Nested If Condition
- How to create Named ranges for easier analysis
- Day 4 | From 01:00 PM To 02:00 PM | Lunch
Topic 5: VISUALIZE DATA
- What are the various types of charts?
- What is the difference among Column Charts, Line Charts, and Pie Charts?
- What is a Histogram and when do I use it?
- What are the requirements in order to use every chart?
- How to create an interactive dashboard
- How to create a dashboard that is automatically connected to all tables and filters so whenever we do any update, it will be automatically updated for all the data
- How to present your data as reports
- How to freeze panes
- How to print large reports using page breaks
- How to print small reports using the scale feature
- How to create your own templates to simplify the creation of reportsÂ
Don't miss this golden opportunity to unlock the doors to data-driven decision-making and storytelling. Enroll now in the Data Analysis and Visualization with Microsoft Excel course at IIPD Global in Dubai, and take a decisive step towards becoming a proficient data analyst and communicator. Whether you're looking to elevate your career prospects, enhance your skill set, or simply broaden your horizons, this course is tailored to meet your aspirations.
By registering for this course, you'll gain access to a world-class learning environment, expert instructors, and a supportive community of fellow learners. Equip yourself with the knowledge and skills that are in high demand across industries, and set yourself apart in an increasingly competitive job market.
