Microsoft Excel (Advance)

This course will help participants understand and be able to use various features of Excel at a deep level, such as data manipulation, calculations, data analysis, report generation, using various shortcuts, and techniques for creating macros to assist in various tasks that normally require multiple complex steps. With just a single click, the program automates everything, leveraging the full capabilities of Excel, which significantly reduces the time required for various tasks.

Using Shortcuts and Formulas Techniques for Data Management

• Techniques for entering duplicate data efficiently
• Quick and accurate entry of date data
• Rapid selection of large amounts of data
• Fast selection of data with special characteristics, such as formulas, numbers,
• Selecting all excluding hidden data, selecting only blank cells, etc.
• Techniques for creating formulas that calculate multiple conditions easily without using nested IF statements
• Creating formulas to aid in searching and mapping data
• Techniques for troubleshooting VLOOKUP formulas that are written correctly but cannot find data
• Creating VLOOKUP formulas to search for data across multiple tables, even across multiple sheets or workbooks
• Creating VLOOKUP formulas to search for duplicate values that are on different rows in the table
• Creating formulas to search for data from tables located in multiple sheets for comparison
• Creating formulas to search for data of all types, even images
• Creating formulas to control dropdown lists with two options, where the second list depends on the first
• Using advanced Lookup functions like Index, Choose, Match, Offset, etc.
• For more complex data searches than VLOOKUP

Using Pivot Tables to Create Analytical Reports from Databases

• Principles of designing databases suitable for processing and reporting
• Creating Pivot Tables in both one-dimensional and multi-dimensional formats
• Expanding and collapsing details and hiding detail data
• Using Filters in PivotTables for data filtering, such as
• Showing only the top 10 highest or lowest values, or the top 10% from all data
• Showing data for specific date ranges, quarters, or years as needed
• Displaying data within specified numeric ranges
• Changing summary function of PivotTable results
• Creating and customizing Subtotals in PivotTable
• Creating Subtotals in reports to display multiple functions as needed
• Customizing Grand Total
• Sorting and grouping Fields, such as displaying summary by quarter, by month, or by year, etc.
• Techniques for distributing PivotTable into multiple Reports on each sheet
• Calculating summary results in various formats, such as % of Total, % of Parent, Running Total, % of,
• % Different, or ranking in different formats, etc.
• Creating Calculated fields and Calculated Items for calculations in PivotTable
• Creating and customizing PivotCharts to present data from Pivot Table
• Creating Pivot Table to calculate summary results from data stored separately in multiple Sheets
• Importing data such as Text File, Access, SQL, etc., to create Pivot Table immediately
• Techniques for making Pivot Table automatically see newly entered data in the table later

Automating Work with Macros

• Introduction to Macros and basic usage
• Techniques for creating Macros in Record mode to make them more versatile
• Various methods for using Macros
• Editing and deleting Macros
• Assigning Macros to be invoked from command buttons or Toolbar
• Creating Macros to help consolidate data from multiple Sheets or workbooks into one table

Participants should have some basic knowledge of using Microsoft Excel.