Topics Covered:

Basic Microsoft Excel, MIS – Reporting , Lookup, H- Lookup, V-Lookup, Reverse Lookup, INDEX, Match, Offset, ADDRESS, INDIRECT, Data Analysis, What If Analysis, Advance Sorting, Advance Filter, Array Formula, Nested IF, Advance Chart, Advance Validation, Advance Formula, Conditional Formula, Macro Recording, Pivot Table & Data Analysis, Calendar Creation, Other Tools & Reports…

Syllabus in details

Basic Editing

Move, find and selecting in a spreadsheet; making various changes to the look of cells (e.g. fill colors, border, border colors) and cell font colors and styles)

Editing Row & Column

Playing with cells, rows, columns and entire worksheets, special paste effect inserting cells, rows, columns, sheets, renaming, hiding, un hide etc.

View Menu

Normal and Page Break Preview, freezing and unfreezing panes, arranging windows, new windows, Zooming effect, uses of ruler and gridlines Print Setup, Page Layout, effect of Split etc.

Cell Formatting (Size, Color & Alignment)

Format painter; horizontal, vertical, top, bottom, centre, middle alignment, word wrapping, merging cells, orientation, settings of row heights & column widths

Work with Graphs & Charts

Use of chart & chart types; how to create them; formatting charts to requirement; converting charts to output in Excel or Power point presentation or other source.

Print Preview, Print Setup

Use of Page Setup – setting margins, orientation, scaling-to-fit a page; how to print very large sheets of data; how to repeat titles, page numbering etc.


Excel Dashboard

Use of Excel interface including: tabs, the Ribbon, the spreadsheet itself, Quick Access toolbar, and the Formula and Status bars, Excel add ins etc…Changing permanent and default Excel settings including, number of worksheets, Inserting worksheet or delete worksheet, use & importance of auto recovery, what do or not do with recovery menu Autocorrect

Sharing and Protecting

How to share a file to other users working on different computers or devices, resolving conflicts; protecting cells, sheets and workbooks, how to unprotect option.

Grouping and Ungrouping

Grouping columns and rows, how we can see a huge database, category of a reference by using grouping option, again we can see how to ungroup such category in not needed.

Date & Time Functions

Use of NOW, TODAY, Time, Hour, Minute, Second, Date, YEAR functions; Dated if, NETWORKING days etc..

Text Functions

FIND, LEN, LEFT, MID, RIGHT, Upper, Lower, Proper case Functions; CLEAN and TRIM & and CONCATENATE. Use of Text functions with Date and time.

Mathematical Functions

Sum, Sumif, Sumproduct , use of brackets, Count, Counta, Countif, Use of Average, Average if, Max, Min, Large and Small to find 3D Search, Use of common Financial Function etc.

Number Formatting

The main formats - General, Percentage, Accounting, Comma Style, decimal place, date format, custom setting for all formats; Apart from we can create our own Format for future use or even current use.

Conditional Formatting

Very important how to play with conditional formatting for Greater than, Equal to,Less than, play with icons data bars, duplicate highlighter, Conditional formatting with text and number.


Connecting to Many Workbooks

How to connect one workbook to another Excel workbook, Links with other files even link with different programs files.

Data Validation

Us of Data validation is mainly restrict to do wrong entry or un permitted entry in the given sheet, event also permit with the warnings as we are using at the ATM machin. We can use this to provide Loans, extra leave or other logical function.

Sorting & Filtering

This is very important; we can’t do anything without using Sorting and Filter. We can filter our database by using Advance Filter and Conditional shorting. We can use this on different heads like Sales, Sale person, profit, city, or other heading rows.

Lookup & Reference Functions

Lookups, VLOOKUPs and HLOOKUPs, MATCH (exactly) and INDEX, Index Match, Offset, Address, Indirect etc are the extremely powerful find functions.

Logical & Information Functions

TRUE, FALSE, ISNUMBER, ISERROR, IF functions and comparison operators like “<, >, =, <>, etc use of AND, OR, nested functions (multiple if functions)

Named Ranges

This is very useful task, we can use this by direct or indirect with the formula, Also Print Area as a named range, This is almost same as range ( as defined by any user with one name.


Developer tab by using macro, enabling macros, using Relative References, use of recording macros will more important if anyone do task repeatedly and developing macro VBA code (using VBA programming).

Pivot Tables

Quick, efficient and powerful tool, we can analysis of large datasets, preparing source data e.g. headings; playing with layout; Column Labels, Row Labels, Values, Use of refresh button, other settings like group of quarter, month, days year, weeks etc.

What-If Analysis

Scenario, Goal Seek, and Data table with the help of what if analysis we can use. It always works with function and formula to budgeting some task, Loans, Interest, Targets for the quarter/month etc.

Copyright © 2013 Omni Solutions