MICROSOFT ADVANCED EXCEL (2-day)
This course will provide you with the knowledge on more specialized and advanced capabilities of Excel by automating some common tasks, applying advanced analysis techniques to more complex data sets, collaborating on worksheets with others, and sharing Excel data with other applications.
November 29-30, 2019
Basic Microsoft Office Excel
This course is intended for novice information workers who want to learn advanced-level Excel skills.
THIS TRAINING COVERS:
Useful Function and Formula
Delved into Excels’ deep and sophisticated formula features . Because there are multiple ways to get results, you can decide which method works best for you. By using the formula bar, you can analyze for formula and find out exactly what sheet and cell the information came from. It will be easier for your supervisor to test the logic behind your calculations . If you need to make a change, you will be able to make it faster and more reliable.
Impress your management with the right excel’s techniques and skills on how to manage data efficiently and perform the right analysis.
Maximizing Pivot Table
One of the best tool to translate raw data to meaningful information in spreadsheet.
It reduces the possibility of human error that increases with many repetitive keystrokes and tasks. Macros reduce the amount of time that must be spent performing basic computing tasks, freeing users up for more complex problem solving and idea generating activities.
MODULE 1: IDENTIFY AND DEFINE THE ESSENTIAL COMPONENTS OF EXCEL FORMULAS
- Using Relative value for cell reference as primary source of formula
- Using Absolute value for cell reference as primary source of formula
- Managing Names of Cell Reference by Using Name box to calculate data across worksheets
MODULE 2: PERFORMING ADVANCED FORMULAS
- Simplify the ff. Number Functions SUM, COUNT AVERAGE,MAX MIN with Name Manager
- Create Solutions in TEXT data using the ff. text functions Change Case, Trim Case, Combine Case and Find and Replace text methods.
- Formulate Date and Time functions to streamline aging report
- Using IF condition and Logical functions like SUMIF, COUNTIF, AVERAGEIF to create dynamic reporting in Excel
- Use Lookup Functions to simplify IF condition formulas.
- Application for Lookup functions with Name Manager and Data Validation.
MODULE 3: IDENTIFY AND UNDERSTAND HOW TO FORMAT FINANCIAL STATEMENTS AND OTHER EXCEL-BASED REPORTS MORE EFFECTIVELY
- Advanced Conditional Formatting and Filtering
- Apply Custom Data Formats
- Apply Custom Styles and Templates
MODULE 4: PERFORM DATA SECURITY AND VALIDATION
- Create user input validation using data validation method
- Create a user-friendly drop-down tool for data validation
- Manage a read-only worksheet using Protect Sheet and Protect Workbook capabilities
- Add security passwords to your excel file using file protection method.
MODULE 5: CREATE EXCEL-BASED CHARTS AND DASHBOARDS TO AID IN COMMUNICATING FINANCIAL DATA INFORMATION
- Application of basics of Sort and Filter
- Creating Charts and Dashboard
- Application of Subtotal per Data Category
MODULE 6: MANAGING BIG DATA IN EXCEL
- Application of Advanced Filter with Name Manager and logical symbols
- Data Consolidation from different References
- Using Database Functions
MODULE 7: DATA ANALYSIS TOOLS USING PIVOT FEATURES
- Describe how to merge database to PIVOT
- Add available fieldnames to different data labels of Pivot Area to generate different report scenario
- Manipulate and re-define PIVOT data resources
- Manage and Derive Formulas in Pivot Area using Value Field settings and Calculated Field feature.
- Re-create PIVOT reports with dashboards using Slicer feature
MODULE 8: INTRODUCTION TO MACRO
- Introduction to Visual Basic
- Macro Settings
- Macro Recording and Assignments
- Macro Execution
- Macro with Excel Functions and Features