Login
Introduction:
Microsoft Excel is one of the most powerful and popular data analysis desktop application on the market today. By participating in this Microsoft Excel Data Analysis and Dashboard Reporting course you’ll gain the widely sought after skills necessary to effectively analyse large sets of data. Once the data has been analysed, clean and prepared for presentation, you will learn how to present the data in an interactive dashboard report
What you’ll learn
- Understand and identify the principles of data analysis
- Effectively summarize and present data clearly
- Use effective design principles when creating and presenting data
- Take full advantage of the built-in tools Microsoft Excel offers in order to analyze and build dashboard reports
- Build interactive dashboard reports
- Analyse Excel data using Excels TEXT based Functions
- Data Mine using Excels Array Functions
- Build Interactive Dashboard Reports with Buttons and Drop-Down Menus
- Streamline and analyze data with Excel PivotTables and Pivot Charts
Requirements
- Experience working with Microsoft Excel on a Windows-based operating system is mandatory.
Contents:
Overview of Data Analysis:
- Understand the Data and explore the magic of dashboard
- Doing the Necessary data Cleansing to Prepare the data for Next level
Analysing the Data Effectively with Microsoft Excel :
- Bring the Data Consistency with usage of Formulae (Text, Clean, Trim, Len, FIND, Mid, Search, Indirect)
- Exploring the Paste Special features Of Microsoft Excel
- Extract date Values with TEXT Function
Data Mining with Excel Powerful Functions Combo:
- Preparing the Data with Format As table to give a life.
- Creating Drop-Down Menu with Data Validation
- Looking up data with XLOOKUP with all the arguments in detail (O365)
- Cleaning up data with Logical Functions IF , IFS, IF with AND , IF with OR
- INDEX with MATCH usage to overcome the constrains of VLOOKUP or HLOOKUP
Building the Excel Dashboard: Filtering Data
- Using Excel’s Advanced Filter feature to work around with Multiple filters,
- Using the Filter and Unique Function as a dynamic tool
Building the Excel Report (Dynamically):
- Understanding the SUBTOTAL Function
- Usage of the Subtotal Function with Peculiarity.
Building the Excel Dashboard: Pivot Table & Pivot Charts:
- Usage of Pivot Table
- Summarising Data with Pivot Table and Preparing different reports
- Working with Multiple Pivot Table and Connecting them with Visual Filter
- Prepare Pivot Chart Connecting Slicers and Pivot Table.
- Create Interactive Charts with Slicers
- Modifying the Chart Slicers
Building the Excel Dashboard: Final Touch
- Hiding Extra Rows, Columns, Gridlines, Sheet Tabs.
- Usage of Icons (O365 only) to turn into Infographic Presentation
- Controlling the Interface from backstage options
- Cleaning up Excel’s Default Settings
- Protecting the Dashboard
Target Audience: Business leaders, managers, L&D professionals, analysts, finance, HR, operations, sales & marketing teams. (No hardcore coding. High business impact. Excel-friendly.)
Trainer Profile:
Prabhas Panda – Microsoft Product Trainer
He has been providing comprehensive training in advanced Excel, professional PowerPoint, Google Sheets, Power BI, Tableau, Excel VBA and Macros, MS Office, MS Access, MS Project, Google Slides, data analysis and visualization, financial modelling with Excel, and finance for non-finance professionals for over 17 years. His mission is to empower individuals and organizations with the skills they need to succeed in today’s data-driven world.
He has worked across multiple domains like Logistics, Procurement, Banking, Finance, HR Reporting, Learning, Performance, Recruitment process. Building Automation projects, Six sigma projects, Dashboard creation, Power BI reports.
Delivery: ZOOM Meeting
