Advanced Excel Skills - AES
Participants will learn to use functions moving from average to advanced level in Excel to improve work productivity, enhance spreadsheets with templates, charts, graphics, and formulas and streamline their operational work. They will apply visual elements and advanced formulas to worksheets to display data in various formats.
Participants will also learn how to automate common tasks, apply advanced analysis techniques to more complex data sets, collaborate on worksheets with others, and leverage on Excel’s functionality to simplify and streamline their day-to-day work.
Upon completion of the training program, participants will be able to:
- Calculate with advanced functions & formulas
- Organize worksheet and table data using multiple techniques
- Create and modify charts & graphs
- Analyze data using Pivot Tables and Pivot Charts
- Insert graphic objects
- Customize and enhance workbooks and the Microsoft Excel environment
Participants will also learn how to deploy Advanced Excel techniques to increase productivity and improve efficiency by streamlining the workflow, collaborate with others using workbooks, audit worksheets, analyze data, Work with multiple worksheets & workbooks, Import and export data in Excel.
- Excel is the most popular and preferred spreadsheet software
- It is a hands-on tool for businesses to generate memos, track sales trends and other business data and to do financially-related activities
- It is used by individuals to organize their information
- Business professionals
- Developers
- Data analysts
- Project managers
- IT professionals
- CLICK HERE for date of next course
- Type: Classroom
- Duration: 18 hours
- Language(s): English and Arabic
This training course aims to give a hand to the participants in mastering:
- Building great charts
- Using conditional formatting
- Identifying trends
- Having an online access to Excel
- Collecting, verifying and analyzing business data
- Administrating and managing duties
- Accounting and budgeting
- Reporting and visualizations
- Forecasting
Topics:
- Creating Your First Custom Format
- Understanding the Date & Time Format Strings
- Using Date & Time Custom Format
- Understanding Conditional Formatting
- Assigning Names to Groups of Cells
- Managing Named Range
- Using Names in Formulas
- Understanding Formulas & Functions
- Create a Relative & Absolute Reference
- Working with Logic Functions
- Working with Text Functions
- Using More Text Function
- Working with Lookup Functions
- Understanding VLOOKUP Function
- Understanding HLOOKUP Function
- Challenge Using Formulas & Functions
- Create and Modify Tables
- Sorting and Filtering Data in a Table
- Build an Advanced Filter
- Getting Summary Information in a Table
- Calculate Total Row in a Table
- Display Special Formatting for First or Last Column
- Summing with Subtotals & Grand Totals
- Validating Data During Entry
- Working with Database Functions
- Challenge 04: Organizing Worksheet & Table Data
- Summarizing Data Visually Using Charts
- Customizing Chart Data
- Format Chart Legend and Titles
- Changing the Chart Body
- Saving the Chart as a Template
- Creating a Pie Chart
- Creating Combination Charts
- Challenge 05: Working with Chart
- What is a Pivot Table
- Anatomy of a Pivot Table
- How does a Pivot Table Works
- Create a Basic Pivot Table Report
- Rearranging a Pivot Table Report
- Customizing a Pivot Table
- Changing Summary Calculations
- Adding and Removing Subtotals
- Sorting in a Pivot Table
- Filtering in a Pivot Table
- Working in a Group Environment
- Commenting in Cells
- Tracking Changes in Workbooks
- Turn on Track Changes
- Accepting or Rejecting Changes
- Review Changes
- Keeping a Backup of your Changes
- Create a Change History
- Linking to Other Workbook
- Create a Link to Other Workbooks
- Consolidating Multiple Sets of Data
- Consolidate Multiple Worksheets
- Protecting Your Worksheet
- Protect a Worksheet
- Unprotect a Worksheet
- Allowing Access to Parts of a Worksheet – I
- Allowing Access to Parts of a Worksheet – II
- Protecting Your Formulas
- Protecting Your Workbook
- Save Your Workbook with Password
- Encrypting your Workbook
- Recording & Testing a Macro
- Using the Developer Tab
- Create a Macro
- Relative and Absolute Recording
- Testing the Macro
- Running and Deleting Macros
- Using the Macro dialog box
- Using a Shortcut key
- Placing a Macro on the Quick Access Toolbar
- Delete a Macro
- Saving a Workbook with a Macro
- Saving a Macro to the XLSM Workbook
- Saving a Macro to the Personal Macro Workbook
- Macro Security
- Creating Practical Macros
- Inserting a Header
- Participants should have some knowledge about the basic data warehousing and data modeling concepts.
- Participants are also preferred to have some working knowledge with Excel.
