The objective of this course is to help the audiences develop their skills and knowledge on MS Excel on the purpose that they can manage their data related tasks more efficiently and productively.
This training is organized by BITM. Training will be held in BITM.
The objective of this course is to help the audiences develop their skills and knowledge on MS Excel on the purpose that they can manage their data related tasks more efficiently and productively, and add more value to their organization.
Specialties of This Course:
Course Content:
SL No |
Lead |
Components |
1 |
Manage Workbook and Worksheet |
a. Manage Workbook: Save a Workbook as Template, Office Theme, Customize Ribbon, Quick Access Toolbar b. Manage Worksheet: Create a New Sheet, Format Sheet, Move or Copy Sheet, Delete Sheet c. Navigation: Cell Movement |
2 |
Commands of Main Tabs |
a. Home Tab: Paste, Paste Special, Format Painter, Pattern Fill Orientation, New Line, Merge Across, Data Formatting, Format Clear, Fill, Find and Select b. Insert Tab: Hyperlink c. Page Layout Tab: Page Set up and Print, Theme d. Formula Tab: Name Manager, Formula Auditing, Watch Window e. Data Tab: Filter, Text to Columns, Remove Duplicates, Data Validation, Goal Seek Group by Subtotal f. View Tab: Navigation, New Window & Arrange All, Freez Panes, Hide and Unhide, Switch Windows |
3 |
Tips and Notes on Formula, Cell Reference, and Formula Copy |
a. Some Important Tips on Excel Formula b. Some Important Tips on Excel Cell Reference c. Other Important Notes |
4 |
Basic Mathematical Calculation |
a. Summation, Subtraction, Multiplication, Division, Percentage, Average b. Simplification Rule c. Round- Roundup and Rounddown |
5 |
Let’s Start with Basic Formulas |
a. Find Max and Min Value b. Rank Value in a Range c. Find Top Largest and Smallest Value d. Difference between Count and Counta e. Make SL/Index number using Row and Column Formula |
6 |
Manipulating Text |
a. Setting text to sentence case b. Flash fill c. Join text string d. Removing space from a text string e. Counting characters in a cell f. Extracting parts of a text string g. Finding a particular character h. Substituting text string i. Finding similar multiple characters j. Make Extracting parts of a text string automatic |
7 |
Conditional Analysis |
a. Simple and multiple conditions b. Logical condition with AND & OR c. Summing and Counting that meet conditions |
8 |
Matching and Lookups |
a. Looking up on a left lookup column b. Looking up horizontally c. Hiding errors returned by lookup functions d. Looking up on a two-way matrix e. Looking up by changing the referencing column f. Looking up from multiple tables g. Looking up by returning a reference to a range |
9 |
Array Formulas |
a. Finding Large and Small value b. Transposing in an array c. Summing in a range d. Summing Largest and Smallest value in a range e. Summing in a range that meet conditions f. Finding Max and Min in an array |
10 |
Conditional Formatting |
a. Highlight cells that meet criteria b. Manage rules |
11 |
Dates and Times |
a. Current date and time b. Extracting parts of a date c. Text Function d. Finding Next month and End of Month e. Make a date f. Length of Service g. Find deadline and Working Days |
12 |
Sorting |
a. Data Sorting d. Row and column sorting |
13 |
Data Validation |
a. Different validation criteria b. Input message and Alert criteria c. Checking duplicate value |
14 |
Protect Work |
a. Unlock cells b. Sheet protection options |
15 |
Making Formulas Error-Free |
a. Finding and Correcting Formula Error b. Formulas returning an error |
16 |
VBA Macro |
a. How to record Macro b. How to run Macro c. How to assign Macro d. Dynamic Advance Filtering |
17 |
VBE |
a. Call Macro b. Protect Coding c. Unhide multiple sheets |
18 |
Introducing Pivot Table |
a. Creating a Pivot Table b. Analysing Data with Pivot Table |
19 |
Slicer |
a. Creating a Slicer b. Report Connections |
20 |
Creating Chart |
a. Creating Chart b. Modifying and designing chart using format task |
21 |
Dashboard Project |
Making a beautiful dashboard |
Dynamic MIS Reporting by MS Excel | Dynamic MIS Reporting by MS Excel | 24 Hrs |