Welcome to a short, easy-to-master, web based advanced MS excel course on basic computer skills. Our goal is to give you a solid foundation. Once you have understood these basics, you will have a clear understanding on which to build your future knowledge of and skill with computers.
Basic Level
Advanced MS Excel
This Excel 2013 advanced training course, follows up from the Beginners Excel 2013 course by delving even deeper into the features and functions of this powerful spreadsheet software. Microsoft Excel 2013 is much more than a quick way to add up numbers. In this our offline & online course, you will learn advanced features such as nesting IF statements, how to calculate the Mean of a group of numbers using AVERAGEIF, SUMIF and more. You will learn about performing lookups with HLOOKUP and VLOOKUP. This tutorial also covers Spark lines, and goes in-depth with Pivot Tables and Charts. Finally, you will learn how to create and record your own Macros. This advanced Excel course is not for beginners. You should have a firm grasp of the basics before taking this Advanced Excel training course. By the conclusion of this advanced computer software tutorial for Microsoft Excel 2013, you will have mastered the advanced features and functions of this software. Working files are included to allow you to follow along with the same Excel files the faculty trains you with.
Full Course Content
- How to recover a file
- Creating Add-ins
- Opening or Viewing the Recent files (Which we have worked)
Conditional Formatting - All Function's Applying Conditions for Highlighting in Using Formula's in Conditional Formatting
- Text
- Numbers
- Date
- Duplicate Values
- Formatting the Table
- Creating a Table
- Using Created Table in Pivot Table
- Creating
- Editing
- Saving
- Printing spreadsheets
- Row
- Column
- Sheet
- Row
- Column
- Sheet
- Applying Formats
- General, Date, Time, Text, Numbers
- Sorting
- Find & Replace
- Pivots
- Charts
- Slicer
- Diagram
- Screenshot & Editing screenshot
- Logical
- Text
- Date & Time
- Lookup & Reference
- Name Manager
- Graphically representing data: Charts & Graphs
- Analyzing data: Data Menu, Subtotal, Filtering Data
- Formatting worksheets, Securing & Protecting spreadsheets
- Use the Function Wizard, Common functions (AVERAGE, MIN, MAX, COUNT, COUNTA, ROUND, INT)
- Nested functions, Name cells /ranges /constants
- Relative, Absolute, Mixed cell references: >, <,=operators
- Logical functions using IF, AND, OR, NOT
- The LOOKUP function, Date and time functions, Annotating formulas
- Sort
- Filters
- Advanced Filter
- Text To Columns
- Remove Duplicates
- Data Validation
- Sub Total Reports, Auto Filter
- Password Protecting Worksheets
- Linking Multiple Sheets
- Linking Between Word/Excel/Ppt
- Functions: - LOOKUP, VLOOKUP, HLOOKUP, COUNTIF, SUMIF
- What-if-analysis, GOAL SEEK
- Absolute Cell References
- Name Manager
- Creating and defining names
- Making a name list
- Advanced technique of using names in formulas
- Using Name Manager
- Using Name Manager
- Excel Pivot Tables
- Data Forms in Excel 2007/2010
- Data Forms in Excel 2007/2010
- Add your own Error Messages
- Excel and Web Integration
- Hyperlinks in Excel
- Object Linking and Embedding
- Spell check
- Insert command
- Protect Sheet
- Protect Workbook
- Protect Share Workbook
- Track Changes
- Gridlines
- Freeze Panes
- The database components
- Using Excel Form feature
- Inputting data
- Deleting data
- Finding records
- Using menu commands to find records
- Multi-level sorting
- Restoring data to original order after performing sorting
- Sort by icons
- Sort by colors
- Multi-level subtotal
- Consolidating and combining several spreadsheets using the operation addition, subtraction
- Synchronizing the consolidated table with the source data
- Lookup()
- VLOOKUP()
- HLOOKUP()
- Application of exact match and approximate match
- Creating an order form using VLOOKUP function
- Files protection
- Protecting cells/documents
- Unprotecting documents
- File linking
- Defining single and multiple criteria
- Combining search criteria
- Deleting criteria
- Extracting records
- Steps to create a pivot table
- Creating pivot table from Excel
- Consolidating data from multiple ranges into a pivot table
- Highlighting data using cell colours, font colours
- Highlighting data using icons
- Define the data input type
- Define the warning message
- Define the error message
- Circle invalid data
- Defining your own scenario
- Preview the result of scenario
- Editing a scenario
- How to apply What-If Analysis
- Creating a hyperlink
- Editing a hyperlink
- Creating a menu system using hyperlink
- Creating a pull-down box to facilitate the data entry process
- Creating and using Macros
