Duration : 3 to 4 Months / Type : Diploma / Mode : Part Time
OBJECTIVES: The objective of advanced Excel refers to the goal of mastering and utilizing advanced features and functions within Microsoft Excel, a powerful spreadsheet software. Advanced Excel skills are sought after in various professional settings, and individuals with proficiency in these areas can perform complex data analysis, reporting, and automation tasks.
Course Structure
Advanced Formulas
- Perform vertical or horizontal lookups to retrieve data from tables.
- Use INDEX to retrieve values from a range, and MATCH to find the position of a specified value.
- Perform calculations on arrays of data, enabling more powerful and dynamic computations.
- Combine multiple functions within a single formula for intricate calculations.
- Apply multiple criteria to sum, count, or average data in a range based on specified conditions.
- Implement conditional logic for customized calculations.
PivotTables and PivotCharts
- PivotTables allow you to summarize and aggregate large datasets quickly and efficiently.
- Create PivotTables by dragging and dropping fields into rows, columns, values, and filters areas.
- Arrange data by placing fields in the row and column labels to create a structured view.
- Use the values area to perform calculations on data, such as sum, count, average, etc.
- Apply filters to focus on specific subsets of data within the PivotTable.
- Create new calculated fields or items based on existing data within the PivotTable.
Data Validation
- Specify the type of data allowed in a cell, such as whole numbers, decimals, dates, times, or text.
- Create dropdown lists to offer users a predefined set of choices, reducing errors and ensuring consistency.
- Provide users with input messages that offer guidance or instructions when they select a cell with data validation.
- Display error alerts when users enter data that violates the validation rules, with options for warning or blocking entry.
- Apply data validation rules across multiple sheets in a workbook.
Power Query and Power Pivot
- Power Query allows you to connect to and import data from various sources, including databases, Excel files, text files, and online sources.
- Transform and clean data easily with a user-friendly interface. Filter, remove duplicates, pivot, unpivot, and more.
- Merge tables based on common columns or perform joins to combine data from multiple sources.
- Apply advanced filtering techniques to extract only the relevant data.
- Append multiple queries or combine them to create a unified dataset.