This course is intended for participants who wish to gain more knowledge from the advanced level of Excel. Participants who are working with lots of formulas and create a report to understand the necessary technique on how an electronic spreadsheet works.
Focus Learning is the course that provides additional value by increasing the learning outcomes of your training efforts and providing additional performance returns on your investment. This course is conducted in a workshop format, which is training based entirely on instructor-led hands-on driven classes. Our trainers thoroughly guide and perform hands-on lab exercises with the attendees to give them the maximum learning experience possible.
Training Duration: 2 Days
- Certificate Of Completion Available
- Group Private Class
- VILT Class Available
- SBL-Khas Claimable
This course is intended for participants who wish to gain more knowledge from the intermediate level of Excel. Participants who need handling databases, using filter and sorting. Those who creates Pivot Table to have in depth understanding of how an electronic spreadsheet works. Participants who need to search data using VLOOKUP and nested VLOOKUP function and performs what if analysis and consolidating data.
Upon completion of this program, participants should be able to Create simple to complex formulas and functions, like:
- Validate data in a Worksheet
- Filter data using Auto & Advanced Filters
- Clean Duplicate Records
- TRIM Function
- Data Extraction Function
- Change Case Functions
- Use Subtotal Function
- Create PivotTable
- VLOOKUP Function
- NESTED VLOOKUP Function
- Data Consolidation
- Scenario Manager
- Data Table
- Hyperlink
Basic knowledge of Microsoft Excel is essential with the follow pre-requisites:
- Able to maneuver with the mouse – Point, Click, Drag and Double Click
- Able to switch between tasks applications
- Able to create a spreadsheet with simple formatting
- Able to “Create”, “Save”, “Open”, and “Print” spreadsheet
- Able to use the “Undo” function
- Able to insert and delete Row & Column
- Able to print a spreadsheet with headers and footers applied
- Able to Insert, Delete and Rename a worksheet
- Able to create basic formulas like addition, subtraction, multiplication, and division
- Able to use basic functions like Sum, Average, Count, Max, and Min function
- Able to use functions like COUNTIFS, SUMIFS, IF, Nested IF, DATEDIF, EDATE, and NETWORKDAYS function
- Able to Validate data in a Worksheet
- Able to apply Filter data using Auto & Advanced Filters
- Able to use Clean Duplicate Records
- Able to use TRIM, LEFT, MID, RIGHT, UPPER, LOWER, PROPER Functions
- Able to use Subtotal and Create PivotTable
Module 1: Ensuring Database Integrity and Handling Text Data Functions
Data validation is a feature in Excel used to control what a user can enter a cell.
Topic 1: Data Validation- What Is Data Validation?
- Validating Your Data
- Types of Validation and Alerts
In this topic, you will learn text functions that is used to handle text within Excel. Some these functions will trim off excess spaces, combines text multiple ranges and/or strings, segregating data functions and change case function.
Topic 2: Handling Text Data- Segregate Data Using the Text to Columns Wizard
- TRIM Function
- SUBSTITUTE Function
- CONCATENATE Function
- Data Extraction Function
- Change Case Functions
Module 2: Handling Database and Subtotal Function
Dynamic tables in excel are the tables where when a new value is inserted to it, the table adjust its size by itself. In this topic, you will learn how to filter records using Auto Filter. Normal or simple filter is used to only filter the selected data items.
Topic 1: Creating Table- Creating Table
- Auto Filter
- Styles and Quick Style Sets
- Adding New Record
- Creating New Field
- Customizing Row Display
- Table Modification Options
- Summary Functions in Tables
Advance filter is used to filter selected data item like normal filter along with an additional feature (Copy filtered data in another location. Besides filter, you will learn to remove duplicate records and sorting of data.
Topic 2: Sort and Filter Data- Difference Between Sorting and Filtering
- Sorting Data
- Advanced Filtering
- Removing Duplicate Values
The Microsoft Excel SUBTOTAL function returns the subtotal of the numbers in a column in a list or database.
Topic 3: Subtotals and Grouping Data- Creating Subtotals
- Using Automatic Outlining
- Grouping Data Manually
- Displaying and Collapsing Levels
Module 3: Analyzing Data with PivotTable, Slicer and PivotChart
A pivot table is a data summarization tool that is used in the context of data processing. Pivot tables are used to summarize, sort, reorganize, group, count, total or average data stored in a database. It allows its users to transform columns into rows and rows into columns.
Topic 1: PivotTable- Create PivotTable Report
- Show Values as Functionality of a PivotTable
Slicers provide buttons that you can click to filter PivotTables. In addition to quick filtering, slicers also indicate the current filtering state, which makes it easy to understand what exactly is currently displayed.
Topic 2: Slicer- Insert Slicer Dialog Box
- Filtering Data
Pivot Chart in Excel is an in-built tool in Excel which helps you out to summarize selected rows and column of data in a spreadsheet. It is the visual representation of a pivot table or any tabular data which helps to summarize & analyze the datasets, patterns, and trends.
Topic 3: PivotChart- Creating PivotChart
- Applying a Style to a PivotChart
Module 4: Searching Data with VLOOKUP Functions
VLOOKUP is an Excel function to look up and retrieve data from a specific column in table. VLOOKUP supports approximate and exact matching. The "V" stands for "vertical". Lookup values must appear in the first column of the table, with lookup columns to the right. The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. It is used as helper function in VLOOKUP.
Topic 1: VLOOKUP & MATCH Function- Using VLOOKUP Function
- Finding an Exact Match with VLOOKUP
- Finding an Approximate Match with VLOOKUP
- Using VLOOKUP as an Array Formula
- Using MATCH Function
- Nested VLOOKUP and MATCH Function
Module 5: Data Consolidation and Protection
You can use Excel's Consolidate feature to combine your worksheets (located in one workbook or multiple workbooks) into one worksheet.
Topic 1: Linking, Consolidating, and Combining Data- Linking Workbooks
- Consolidating Workbooks
- Combining Worksheets
To prevent other users from accidentally or deliberately changing, moving, or deleting data in a worksheet, you can lock the cells on your Excel worksheet and then protect the sheet with a password.
Topic 2: Worksheet and Workbook Protection- How to Protect your Worksheet?
- How to Protect your Workbook?
Module 6: What If Analysis and Hyperlinks
Scenario Manager is a built-in Excel tool that allows users to deal with changing up to 32 variables (cells) simultaneously. A Scenario is a set of values that Excel saves and can substitute automatically on your worksheet. You can create and save different groups of values as scenarios and then switch between these scenarios to view the different results.
Topic 1: Exploring Scenarios- Creating a Scenario
- Saving Multiple Scenarios
- Creating a Scenario Summary Report
A data table is one of the What-If Analysis tools that allows you to try out different input values for formulas and see how changes in those values affect the formulas output. Data tables are especially useful when a formula depends on several values, and you would like to experiment with different combinations of inputs and compare the results.
Topic 2: Data Table- 1-Input Table
- 2-Input Table
A hyperlink is a quick access to related information in another file, worksheet tab or on a web page.
Topic 3: Hyperlinks- Inserting Hyperlinks
- Editing Hyperlinks
- Using Hyperlinks in Excel