Advanced Microsoft Excel & Power BI – IQhub Certification

This program will help you to master all the essential functions, tools, and features of Microsoft Excel and Power BI, taking your data analysis and visualization efficiency from basic to advanced level. Microsoft Excel & Power BI allows users to store, analyze, and visualize big data. Learn these game-changing technologies to analyze massive business data with ease. This program is designed with a practical, easy-to-learn approach toward unleashing the clout of Microsoft Excel & Power BI.

 

Learning Outcomes:

Here is the breakdown of lectures I plan to teach for the Winter Semester 2024:

  1. Data Import from Multiple Sources (online & offline) for data automation
  2. Data formatting using
    • Simple formatting
    • Custom formatting
    • Conditional Formatting
  3. Formulas in Microsoft Excel
    • Basic Formulas
    • Absolute & Relative freezing of cells for advanced formulas
    • Work with data cleaning functions (text functions)
    • Logical functions (IF & AND)
    • Lookup & Match formulas
  4. Data Visualization in Excel
  5. Work with Pivot Tables
  6. Best Practices in Microsoft Excel
    • Password protect data
    • Data printing best-practices
    • Data validation
  7. Microsoft Power BI
    • Download & Install Power BI
    • Data storage in Databases & Power BI (concepts)
    • How to import and connect data in Power BI
    • Working with formulas and functions (Measures & Calculated Columns
    • Create a dashboard

Exam Certification Files:

Please complete all the necessary files and submit them to CourseCart for the Advanced Microsoft Excel and Power BI Certification. Upon successful completion, you will receive certification from IQhub, a prestigious training and certification provider registered in the UK. IQhub is renowned for its high-quality programs and credentials, ensuring that your certification reflects advanced proficiency in both Microsoft Excel and Power BI.

1. Getting Started

1.1 _ Basics of Microsoft Excel

2. Data Import from multiple sources

2.1 _ Data import from the online (websites) sources
2.2 _ Connect live data frrom Microsoft Forms
2.3 _ Connect live data frrom Google Forms
2.4 _ Get data from this PDF File using Power Query
2.5 _ Get Data from this Picture using Power Query
2.6 _ Get data from another Excel File
2.7 _ Combine Data from Multiple Excel Tabs

2.8 _ Combine Data from Multiple Excel Files

2.9 _ Combine Data from All Files in a Folder

3. Simple Data Formatting in Excel

3.1 _ Simple Formatting of the data
3.2 _ Cells & Table Styles for Quick Formatting

3.3 _ Filter View & Slicer in Microsoft Excel

3.4 _ Advanced and Custom Data Sorting

3.5 _ Data Grouping & Subtotals in Excel

4. Custom Data Formatting in Excel

4.1 _ Custom Formatting in Numbers

4.2 _ Using Shapes in Custom Formatting

4.3 _ Other Custom Formatting in the Cells

5. Conditional Data Formatting in Excel

5.1 _ Rules of Highlighting the Cells

5.2 _ Add Data Bars to the Cell Values

5.3 _ Add Color Scales & Data Bars in Values

5.4 _ Highlight Duplicates Values in the Data

5.5 _ Highlight the Row Based on Cell Value

5.6 _ Highlight Rows Based on Multiple Criteria

6. Formulas & Functions in Microsoft Excel

6.0 _ Learn Basics of the Formulas (Practice File)

6.1 _ Basic Formulas in Microsoft Excel

6.2 _ Text Functions for Data Cleaning & Formatting

6.3 _ Logical Functions in Excel (IF & AND)

6.4 _ Lookup & Index Functions

6.5 _ DATE Functions in Microsoft Excel

6.6 _ Advanced functions in X365

7. Charts (Data Visualization) in Excel

7.0 _ Advanced Chart Formatting (Practice)

7.1 _ Bar & Column Charts

7.2 _ Line Charts & Forecasting

7.3 _ Pie & Donut Charts

7.4 _ Area Charts

7.5 _ Histograms

7.6 _ Waterfall Chart

7.7 _ Heat Maps

7.8 _ Combo Charts

8. Pivot Tables in Microsoft Excel

8.1 _ Create a Pivot Table

8.2 _ Data Analysis with Pivot Tables

9. Data Security, Printing, & Data Validation

9.1 _ Password Protection of Data in Excel

9.2 _ Password Protection of Tabs and Formatting

9.3 _ Put a password 12345 on opening of the File

9.4 _ Data Printing in Excel

9.5 _ Data Validation for Accuracy of Data Entry

10. Create a Power BI Report

10.1 _ Sample HR Dashboard

10.2 _ Dataset for the Startup Industries