Class type 2 days a week online 1 hr each
Days: Monday-Friday
Time slots: Evening: 4:00-5:00 pm, 5:00-6:00 pm, 6:00-7:00 pm, 7:00-8:00pm
| Mastering Excel for Data Analytics: Beginner tPr- Complete Course Syllabus |
| Module 1: Excel Fundamentals for Data Analysis |
| 1.1 The Excel Interface and Basic Navigation |
| Understanding the Ribbon, Worksheets, and the Formula Bar. |
| Essential keyboard shortcuts for efficiency. |
| Setting up your workspace for data analysis. |
| 1.2 Data Entry, Formatting, and Data Types |
| Entering and organizing raw data. |
| Formatting cells timprove readability. |
| Recognizing and handling different data types: text, numbers, dates, and logical values. |
| 1.3 Working with Tables |
| The benefits of converting data ranges intExcel Tables. |
| Using structured references and the Table Design tab. |
| 1.4 Basic Data Operations |
| Using AutoFill and Flash Fill tsave time. |
| Sorting and filtering data tquickly find insights. |
| Module 2: Core Functions and Formulas |
| 2.1 Aggregate and Statistical Functions |
| Calculating totals with SUM. |
| Finding averages, minimum, and maximum values with AVERAGE, MIN, and MAX. |
| Counting values with COUNT, COUNTA, and COUNTBLANK. |
| 2.2 Logical and Conditional Functions |
| Making decisions with the IF function. |
| Using AND and OR for multiple conditions. |
| Handling errors gracefully with IFERROR. |
| 2.3 Conditional Aggregations |
| Calculating sums and averages based on criteria with SUMIF, SUMIFS, AVERAGEIF, and AVERAGEIFS. |
| Counting cells that meet specific criteria with COUNTIF and COUNTIFS. |
| 2.4 Text Functions |
| Manipulating text strings with LEFT, RIGHT, MID, LEN. |
| Combining text from multiple cells with CONCAT and TEXTJOIN. |
| Module 3: Data Cleaning and Preparation |
| 3.1 Identifying and Removing Data Inconsistencies |
| Trimming excess spaces with the TRIM function. |
| Cleaning non-printable characters with the CLEAN function. |
| Converting text case with UPPER, LOWER, and PROPER. |
| 3.2 Structuring Data for Analysis |
| Using the Text tColumns feature tparse data. |
| Splitting and combining columns tcreate a clean, organized dataset. |
| 3.3 Data Validation |
| Restricting data entry ta specific type or list. |
| Creating dropdown menus tensure data consistency. |
| 3.4 Removing Duplicates |
| Using the built-in "Remove Duplicates" tool. |
| Highlighting duplicates with Conditional Formatting. |
| Module 4: Advanced Lookup and Reference Functions |
| 4.1 The Power of VLOOKUP and HLOOKUP |
| Understanding how VLOOKUP works tretrieve data from a vertical table. |
| Best practices and common limitations of VLOOKUP. |
| 4.2 Mastering INDEX and MATCH |
| Combining INDEX and MATCH for a more flexible and robust lookup solution. |
| Understanding the advantages of this combination over VLOOKUP. |
| 4.3 Introduction tXLOOKUP (Modern Excel) |
| How XLOOKUP simplifies lookups and overcomes the limitations of VLOOKUP and HLOOKUP. |
| 4.4 Practical Applications |
| Using lookup functions tmerge datasets and enrich data. |
| Creating dynamic reports with data from different worksheets. |
| Module 5: Data Summarization with PivotTables |
| 5.1 Introduction tPivotTables |
| What is a PivotTable and why is it essential for data analysis? |
| Creating your first PivotTable from a raw dataset. |
| 5.2 Customizing PivotTables |
| Using the Rows, Columns, Values, and Filters areas. |
| Summarizing data with different functions (sum, average, count, etc.). |
| Grouping data by date, numbers, and text. |
| 5.3 Advanced PivotTable Features |
| Creating calculated fields and items. |
| Working with multiple data sources. |
| Refreshing and updating PivotTable data. |
| Module 6: Data Visualization and Interactive Dashboards |
| 6.1 Choosing the Right Chart |
| Understanding different chart types: bar, line, pie, scatter, combcharts. |
| Best practices for creating clear and effective visualizations. |
| 6.2 Customizing and Enhancing Charts |
| Adding chart elements like titles, axes, and data labels. |
| Formatting charts for a professional look. |
| 6.3 Building Interactive Dashboards |
| Using PivotCharts tvisualize PivotTable data. |
| Adding Slicers and Timelines tcreate interactive reports. |
| Arranging charts and tables tcreate a cohesive and insightful dashboard. |
| Module 7: Advanced Data Tools |
| 7.1 Introduction tPower Query |
| What is Power Query and its role in modern Excel? |
| Importing data from various sources (CSV, web, other Excel files). |
| Using the Power Query Editor ttransform and clean data. |
| 7.2 Data Transformation in Power Query |
| Merging and appending queries. |
| Unpivoting and pivoting data. |
| Automating your data cleaning process. |
| 7.3 What-If Analysis Tools |
| Using Goal Seek tfind a specific result. |
| Exploring different scenarios with the ScenariManager. |
| 7.4 The Analysis ToolPak |
| Enabling the Analysis ToolPak add-in. |
| Performing descriptive statistics and regression analysis. |
| Module 8: Introduction tPower Pivot and DAX |
| 8.1 Introduction tPower Pivot |
| Understanding the data model and its benefits. |
| Creating relationships between tables. |
| 8.2 Data Analysis Expressions (DAX) |
| Introduction tDAX as a formula language for data models. |
| Creating calculated columns and measures. |
| Key DAX functions: SUMX, CALCULATE, and FILTER. |
| 8.3 Data Modeling with Power Pivot |
| Building a simple data model. |
| Creating PivotTables and PivotCharts from the data model. |
| 8.4 Best Practices for Reporting and Storytelling |
| Designing professional dashboards. |
| Communicating your findings effectively. |
| Data governance and documentation. |
View this Course
Certified
You have 
