Microsoft Excel from Beginner to Advanced Level

Objectives of Training on Basic to Advanced Excel Course

BASIC MICROSOFT EXCEL OBJECTIVES

· The Basic Excel level introduces students to the Microsoft Excel application and how to use it to do normal day to day spreadsheet tasks in the office.

· To ensure that all participants are familiar with Data handling and storage using the Spreadsheet program.

· To expose participants to the power of using Excel functions and formula for Calculation, performing logical decisions, and data control.

· To enhance participants reporting ability with good knowledge in the use of charts for representing Data.

· To give participants a basic knowledge of how to use Ms-Excel to handle all office tasks that are in spreadsheet or table format, perform calculations on data, format the worksheet, plot charts, do simple data analysis, and many more.

. To work with Images, Charts, Shapes and Smart Arts objects.

. To work with Worksheets; rename, group, delete, colour, hide, lock and perform worksheet - Workbook structure protections.

. To work with the AutoFill Command to automatically fill cells with required values.

. To understand the Excel Keyboard shortcuts, Sort data, filter records, perform quick analysis on a dataset, and worksheet printing commands.

. To understand Microsoft Excel cell referencing and applications.

. To understand Formulas and Functions, use Maths, Statistical Functions in performing calculations on numbers.

. To understand the TEXT and DATE Functions in Excel and applications.

. To understand Excel Worksheet Formatting and Rules (Text, Number, Cell and Worksheet Formattings).

INTERMEDIATE AND ADVANCED MICROSOFT EXCEL OBJECTIVES

This course outline is designed to explore the more detailed features of Excel. It advances the user's knowledge of functions, demonstrates how to manage data with Excel, and explores how Excel is used to present data using tables and charts.

Intermediate  to Advanced Excel Training Objectives

· The intention of this training is to further expand participants’ understanding of the working power of the Microsoft Excel application.

· It is also expected that at the end of the training participants will understand how to link, relate, and reference data within a workbook.

· Further understanding of how to graphically represent data without much complexity.

· Enhance participants’ understanding of the usefulness of financial functions for handling mortgage loans.

· Show participants how to sort, extract and filter large data for analysis purposes.

. To understand how to Protect Worksheets, Workbook protection and Excel File encryption.

. To understand how to work with a range of data and convert them into database Tables for extended data analysis.

. To understand the Microsoft Excel Database, Date, Statistical, Maths, Financial, Lookup and Reference Functions at Intermediate Level.

· To show participants how to create organograms, graphics, logos, and background images in excel.

. Participants would learn how to summarize large data using Pivot Table and Chart Tools, Sub Total, and the Filter Tool.

. Consolidate periodic, regional, or departmental reports, generate executive reports from a large set of data.

. Learn Excel Functions from Intermediate to Advanced Level (VLOOKUP, INDEX, MATCH, SUMIF, COUNTIF, SUM, COUNT, AVERAGE, IF, OR, AND, etc).

. To Learn Data Validation Rules, Conditional Formating Rules from Intermediate to Advanced Level.

. To Create links to other worksheets within a workbook.

. To Use the Sparkline Chart Tools within the Excel Cells.

. To Work with Sensitivity analysis using the What-if-Analysis tools (Goal Seek, Solver, Scenarios and Data input Tables).

. To Use the VLOOKUP, MATCH & INDEX Function for Practical Task (Balance Sheet Statement).

. To Use the Financial Functions (PV, FV, NPV & IRR for Project Evaluation Techniques)

. To Work with the SUMIFS, COUNTIFS and AVERAGEIF (Case Study - Income Statement)

. To do Simple Dashboard Creation using the Excel Worksheet.

After completing this course, participants will know how to:

• Start Microsoft Excel and identify the components of the Excel interface; open an Excel workbook; use the Help window; and navigate worksheets.

• Enter and edit text, values, and formulas; insert pictures; use AutoFill; save and update a workbook, and save a workbook as a PDF file.

• Move and copy data and formulas; use the Office Clipboard; work with relative and absolute references; and insert and delete ranges, rows, and columns.

• Use the SUM function, AutoSum, and the AVERAGE, MIN, MAX, COUNT, and COUNTA and other functions to perform calculations in a worksheet.

• Use SUMIF, COUNTIF, VLOOKUP, MATCH, INDEX, IF, OR, AND Functions and many others.

• Generate Executive Reports using the PIVOT TABLE, SUBTOTAL, DATA CONSOLIDATION, FILTER and SORT Tools

• Format cells, rows, and columns; merge cells; apply colour and borders; format numbers; create conditional formats; copy formatting; and apply table styles.

• Check spellings; find and replace text and data; preview and print a worksheet; set page orientation and margins; and create headers and footers.

• Create, format, modify, and print charts based on worksheet data; work with various chart elements; apply chart types and chart styles.

• Freeze panes and split a worksheet; hide and unhide data; set print titles and page breaks to optimize print output; manage multiple worksheets.

. Use the Sparkline Chart Tools within the Excel Cells.

. Work with Sensitivity analysis using the What-if-Analysis tools (Goal Seek, Solver, Scenarios and Data input Tables).

. Use the VLOOKUP, MATCH & INDEX Function for Practical Task (Balance Sheet Statement).

. Use the Financial Functions (PV, FV, NPV & IRR for Project Evaluation Techniques)

. Working with the SUMIFS, COUNTIFS and AVERAGEIF (Case Study - Income Statement)

. Simple Dashboard Creation using the Excel Worksheet.