Duration: 16 Hours

Section 1: Managing and Formatting Workbooks and Worksheets

▪ Managing & setting up Sheets & Workbooks
▪ Organizing and Various Formatting Option on Data in WorkSheets
▪ Aligning the Data
▪ Implementing Formula and Calculations in Excel Worksheet
▪ Components of Worksheet like Tools, Ribbons …etc.
▪ Saving and Opening of Documents.
▪ Some Common Tasks Like Copying of Data , Renaming a Worksheet …etc.
▪ Hiding and Unhiding Columns/Rows.
▪ Use of Auto Fill Feature

Section 2: Producing Charts in Excel and Applying Various Formula

▪ Creating Charts in Excel.
▪ Organizing and Various Formatting Option on Charts Like Pie Chart, Histogram, Bar
Chart …etc.
▪ Printing of Chart and Data.
▪ Various Issues to be Discussed before taking out Print outs.
▪ Applying Formula and types of Cell Referencing
▪ Applying Formula across multiple Worksheets.
▪ Applying Conditional Formatting.
▪ Working With Dates.
▪ Applying Comments in Cells.

Section 3: Making Excel reports via various Formulas, Functions and Referencing

▪ Use of Text Functions like Len, Concatenate, Upper, Lower, Proper & Trim.
▪ Use of Logical Functions like If and Multiple If Conditions.
▪ Use of Mathematical and Statistical Functions like Sum, Abs, Round, Round ,Int ,
Count,Max,Min…etc.
▪ Some More Advanced Functions such as Count, Counta, Countblank, Countif,
Countifs, Sumif, Sumifs, Average, Averageifs with proper Lab Exercises.
▪ Lookup Functions such as Vlookup and Hlookup on exact & approximate match
▪ Applying Formula/Function using Multiple WorkBooks.
www.itstechschool.com

Section 4 : Working with Data

▪ Sorting the data in Alphabetical order on single & multiple fields.
▪ Extracting the data using Filters & Advance Filters (Number Filters, Text Filters,
Date & Time Filters, Unique Records, Duplicate records & Colour Filtering).
▪ Creating and Modifying Custom Lists.
▪ Creating Sub Totals
▪ Preparing Pivot Table & Pivot Chart
▪ Freezing Some Rows or Some Columns in Excel.

Section 5 : Working For Data Analysis and Protection of Data

▪ Performing What…If Analysis using Goal Seek & Scenario Manager
▪ Performing What…If Analysis using Data Table
▪ Putting Data Validations and Auditing of Data
▪ Protecting Cells/Worksheet
▪ Protecting File with a Password
▪ Converting an Excel File in CSV / PDF Format.

Section 6 : An Introduction To Macro

▪ What is a Macro ?
▪ Creating and Executing a Macro in Excel Worksheet.
▪ Enabling and Disabling a Macro feature.

Outline for this course