The outline of this training course is designed to provide training in advance functional aspects of MS-Excel. Anyone who know some basic of excel and desire to enhance the skill in Advance Excel can go for this training session.

Topics Covered

Unit 1 – Home Tab *********************** Conditional Formatting Use of format painter Use of paste special Strike through a value Find and Replace Hide/ unhide a tab Color a tab Move /Copy the tab to another sheet/workbook Unit 2 – Insert Tab ********************* Pivots and Pivot Graphs ----------------------------------- Creating of Pivot Table Using Summary function in Pivot Table Pivot Table Calculations Creating own formulas in Pivots Grouping and Ungrouping in Pivot Tables Creating Pivot Graph Custom Filter on Pivot Table Dropping data into the Pivot Table Modifying data Refreshing the Pivot Table Formatting Pivot Table Changing Value field setting Changing Report Layout Applying Slicer (in Excel 2010) Turn off/on Grand total for Rows/Column Drilling down report for Pivot Table values Using Conditional Formatting in Pivot Table Apply Page Report filter, Row and column Filter Number Formatting to Pivot Table values Customized column values (% of Total, % of column etc.) Calculated Fields Calculated Items Working on Objects ----------------------------- Inserting Objects Exploring Formatting options for the inserted objects Hyperlinks and Bookmarks in Excel Providing screen tips in hyper link Hyperlink to send emails Inserting different files into Excel (like .doc, .pdf, .txt files) Inserting symbols Working with Charts ------------------------------ Creating charts Creating different types of charts (Pie, Columnar, Bar, Stacked, Combination, , X-Y, Line chart) Including titles and values in charts Formatting of chart Creating Secondary Axis in Chart Changing display of category on X-Axis Different Formatting options for different Chart components Unit 3 – Formulas ********************** STRING ------------ CHAR/CODE,CLEAN/TRIM CONCATENATE,FIND/SEARCH LEFT/MID/RIGHT,LEN/REPT LOWER/PROPER/UPPER REPLACE/SUBSTITUTE MATHEMATICAL -------------------------- CEILING/FLOOR, EVEN/ODD LARGE/SMALL, MAX/MIN ROUND/TRUNC, AVERAGE/SUM/COUNT PRODUCT/SUMPRODUCT DATE --------- DATE/TIME, DATEDIF/DAYS360 DAY/MONTH/YEAR HOUR/MINUTE/SECOND NETWORKDAYS/NETWORKDAYS.INTL NOW/TODAY WEEKDAY/WEEKNUM/WORKDAY LOOKUP / REFERENCE ------------------------------------ ADDRESS, AREAS, CHOOSE COLUMN/ROW, HLOOKUP/VLOOKUP INDEX/MATCH,INDIRECT LOOKUP/OFFSET,TRANSPOSE STATISTICAL -------------------- GROWTH,INTERCEPT LARGE, PERCENTILE PERCENTRANK ,QUARTILE,RANK INFORMATION ---------------------- CELL, ISBLANK/ISNUMBER ISERR/ISERROR ISLOGICAL,ISNA, ISNONTEXT/ISTEXT LOGICAL ------------- IF, FALSE/TRUE, AND/OR/NOT Name Manager ---------------------- Definition Named Ranges Use of name manager Using formulas in name manager Editing name range Formula Auditing ------------------------- Trace Precedents, Trace Dependents Error Checking Remove arrows Formula Evaluation Unit 4 – Data Tab ********************* Data Connection from External Data From access From SQL From Web From ODBC Data Validation ---------------------- Use Data Validation in Excel Using Input Message in Data validation Using error message in Data Validation Sorting Data ------------------ Sorting tables Using multiple-level sorting Using custom sorting Adding and deleting different levels of sorting Split Texts ---------------- Split texts to different columns Remove special character from a string and split the string What If Analysis ------------------------ Goal Seek Scenarios Filtering the Data ------------------------- Need of Filter application Different kinds of filters Auto and Advance filter Filter condition for Filtering data Using advanced filter options Other usage of filters Filter by color Subtotals -------------- Concept of Subtotal Creating subtotals Multiple-level subtotals Grouping and Outlining Data Data Consolidation --------------------------- Using data consolidation feature to consolidate data Unit 5 - Review & View ************************** View Multiple Files --------------------------- Inserting page breaks for printing Arrange multiple windows to view in one time. Split big sheet into sub sheets for better analysis Freezing and unfreezing panes Security ------------ Protecting your workbook Protecting the worksheet Sharing the workbook Allow users to edit ranges

Who should attend

Engineers, Managers,Executives,BPO Executives, MIS Personals, Accounts Personal, HR, Quality Analyst, Analysis Students etc.

Pre-requisites

Limited knowledge of MS Excel and willing to to Learn Advance Excel, Rest I will take care.

What you need to bring

This is online class so students should have good Laptop or Desktop with good internet connection at least 256 kbps. Internet Connection should be continuous to avoid any interruptions during the session. If any how some topic missed then later on it would be cover.

Key Takeaways

Excellent knowledge in Excel. Training Presentations/Worksheet Tutorials and Exercise.