If you do well in this unit, you should be able to:
Indicate the names and functions of the Excel interface components.
Enter and edit data.
Format data and cells.
Construct formulas, including the use of built-in functions, and relative and absolute references.
Create and modify charts.
Preview and print worksheets.
Use the Excel online Help feature.
Course Content
Dates and time in Excel
Inserting and formatting dates
Entering date functions
Using dates in formulas
What is a Table?
Creating Tables
Changing the Table Range
Inserting Table Columns
Inserting Table Rows
Deleting Rows or Columns
Sorting and Filtering Tables
Sorting Data in a Table
What is an AutoFilter?
Custom AutoFilters
Removing Filters
Filtering with Wildcard Characters
Using an Advanced Filter
Creating Subtotals
Use Slicers to filter tables
Creating a Chart
Manipulating a Chart
Moving and resizing a chart
Adding a chart title
Adding a chart Axis title
Changing the Type of Chart
Formatting a Chart
Recommended Charts
Quick Analysis Option
Chart formatting control
The IF function
Using the Function Library
Manually entering a function
Conditional Formatting
Applying Conditional Formatting
Creating a Conditional Formatting Rule
Removing Conditional Formatting
Using the Manage Rules Option
Adding Conditional Formatting using the Quick analysis tool
Working with Names and Ranges
What Are Range Names?
Defining and Using Range Names
Using Logical Functions
Using the Function Library
Manually entering a function
The IF function
The AND Function
The OR Function
Working with Nested Functions
Nested IF functions
Applying Logical Functions
Using VLOOKUP to Find Data
How to Find an Exact Match with VLOOKUP
Finding the Closest Match with VLOOKUP
How to use the MATCH Function
Index Function
Lookup using Match and Index Function
Using Multiple Worksheets
Navigating between sheets
Creating -D formulas
Consolidating data
Linking your workbooks
Linking Workbooks
Using the Paste Link option
Creating a formula to link workbooks
Controlling link updates
Manually update all of the links or none of the links in a workbook
Manually update only some of the links to other workbooks
Dealing with Broken Links
Comments
Adding, editing and deleting comments
Excel Workbook and Worksheet Protection
Protecting your Worksheets
Unlocking Cells
Validating Your Data
Creating Data Validation Settings
Using Database Functions
Creating a Criteria Range
Entering the Database Function
What is a PivotTable?
Preparing Data to Create a PivotTable
Creating a PivotTable
Quick Analysis
Adding Fields to the PivotTable
Creating a PivotTable Frame (Classic PivotTable Layout)
Rearranging PivotTable Data
Hiding and Showing Field Data
The PivotTable Tools Ribbon
Modifying Calculations and Data Area Display
Formatting a PivotTable
Refreshing a PivotTable
Time Grouping in PivotTables (Excel )
Using Slicers to Manipulate PivotTables
Use Timelines with Pivot Data
Creating a PivotChart
Using Scenarios in Excel
Creating a Scenario
Using Goal Seek
Using Solver
Checking Solver Installation
Setting Solver Parameters
What is a macro?
Creating a macro
The Developer Ribbon
Recording a macro
Playing a Macro
Macro Security
More Macro Tasks
Recording a Relative Reference Macro
Running a Relative Reference Macro
Assigning a Keystroke to a Macro
Copying a Macro from a Workbook or Template
Viewing and Organising Spreadsheet Data
The Zoom Feature
Hide rows and columns
Using Automatic Outlining
Grouping Data Manually
Printing Large Worksheets
Print Titles
Page Break Preview
Inserting a Page Break
Printing Multiple Sheets
Using Templates
What is a Template?
Creating a Workbook from a Template
Creating a Template
About the instructor
DIPU MAHARJAN
BI Solution Architect
Hi I am Dipu Maharjan. I am working as BI Solution Architect. I have experience more than 10 years as Data Analyst, Database Programmer, Developer and Trainer.
Roshan Shrestha-
-
-
-
-
Senior ManagerAfter the Advanced Excel training it help me to enhace my skill and help me to do work in short time for the comlicataed task.
Roshani Hamal-
-
-
StudentIt was nice experience to have training session with respected Dipu Sir. Thank you