Overview
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
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