Advanced Excel
Overview
Excel is the spreadsheet application in the Microsoft Office suite. This course will show you how to use the powerful tools in Excel for organizing, visualizing and calculating you data.
Microsoft Excel is the most widely used productivity tool, Microsoft Excel allows us to accomplish a task in multiple ways allowing us to accept more challenging tasks resulting in an efficient and effective performance.
You will learn advanced features such as nesting IF statements, how to calculate the Mean of a group of numbers using AVERAGEIF, SUMIF and more. You will learn about performing lookups with HLOOKUP and VLOOKUP. This course also covers Sparklines, and goes in-depth with Pivot Tables and Charts and you will learn how to create and record your own Macros. Finally, you will learn how to put validation and protection on cells, protecting & hiding your workbook.
This advanced Excel training course is not for beginners. You should have a firm grasp of the basics before taking this Advanced Excel training course. By the conclusion of this advanced Excel training, you will have mastered the advanced features and functions of this software.
Prerequisites:
Basic knowledge in Microsoft Excel and Office package
Course Benefits
- To gain a solid understanding of the more powerful features and functions in Excel
- To be able to use Microsoft Excel in a commercial environment
- Course Manual
- Course Completion Certificate
Who should attend this course?
- Individual who want to become more competitive in the job market
- Organizations planning to upgrade their employees on the Advanced Excel Reporting
- Anyone who wants to become proficient in Microsoft Excel
Click on Semester for more detail.
Introduction to Spreadsheet
- Spreadsheets packages what they are and how they work?
- Application of excel
- User interface of Microsoft excel
Format Cell
- Applying formatting to numbers
- Creating custom number formats
- Alignment options
Functions
- Looking at auto sum tricks
- Utilizing fill handle tricks
- Creating and using an array formula
- Count(if), Sum (if) etc
- Logical functions
- How to apply what-if analysis
SmartArt
- Using SmartArt functions
- Using different SmartArt options
Date and time functions
- Find current day, weekday, month & year
- Calculate No of days b/w two dates
Auto format
- Sheets
- Cells
- Format painter
Conditional formatting
- Define single and multiple criteria
- Delete criteria
Paste special
- Paste link
- Transpose
Cell comments
- Display comments
- Create, edit & delete comments
Data sorting
- Simple sorting
- Multiple column sorts
- Custom sorts
Data filter
- Custom data filter
- Advanced filtering
- Extracting filtered data
Subtotal report
- Adding subtotals
Lookup functions
- Vlookup() & Hlookup()
Graphical representation of data
- Creating, Formatting & Modifying charts
- Deciding what chart format to use
- Show a time series with column or line charts
- Using line chart accessories
- Using bar charts to show comparisons
- Using pie chart to whole analysis
Lists
- Creating a list
- Filtering lists
Macros
- Recording macro
- Running a macro
Auditing
- Trace precedents
- Trace dependents
- Tracing errors
- Freeze titles
Pivot table
- Creating pivot table
- Consolidating data from multiple ranges into a pivot table
- Grouping data in pivot tables
- Insert slicer
Protection
- Define the data validation
- Define the warning message
- Define the error message
- Worksheet and cell protection
- Hiding rows and columns
- Workbook protection
- Hiding worksheets & workbooks
- Assigning a password
Fees and Durations
Duration : 5 Days
Timing : 7:00pm to 9:30pm
Days : Five Days a Week (Monday to Friday)
Course Fee : Rs.6,000/-
Student Benefits : Course Manual, Participation Certificate