Advance Excel

Course provides training based on creating and managing Spreadsheets for Daily Expense Report, Supplier / Customer Details, Labour Shift / Timing Details, Stock / Inventory Details, Payroll Information and Statistical Charts. After learning this short term course you can be an asset of any organization! Best advanced excel training in Puducherry with expert practical experience.

Content:

• INTRODUCTION TO EXCEL
• What and why is excel | Introduction about workbook and worksheet | Basic Arithmetic Calculations, BODMAS and SIGMA | Daily Transaction report
• DATA FORMATTING
• All Formatting and Format Painter | Alignments, Merging and Wrapping the text | Data Type (Number)
• Conditional Formatting | Insert / Delete workbook components (Cell, Row, Column and Sheet) | Move or copy sheet and Visibility | Filling series of Numbers and Dates | Sorting and Filtering
• PIVOT TABLES & PIVOT CHART
• What is Pivot Table | Usage of Formulas in Pivot Tables | Filters in Pivot Table | Pivot Table Consolidation | SLICERS and Timelines in Pivot Table | Table with Sales and purchases of companies and their branches
• CHARTS
• All type of Charts | Usage of Charts (column, Pie, Bar, Line) | Usage of dynamic Ranges in charts | Slicers and Sparklines | Table of yearly product annual sales in month-wise
• LINKS
• Inserting Hyperlinks | Linking Sheets, Cells, Workbook, Range and Mail
• PAGE SETUP
• Paper settings like Paper Margin, Orientation and size | Layout settings like Print Area, Page Breaks, Print Titles & applying Background | Scaling, Sheet options (Gridlines & Headings) | A table of Employees Salary statement
• CONNECTIONS and DATA TOOLS
• Data importing (form Access, Web and Text), Other sources (SQL Server, Oracle and XML) | Advance Sorting and Filtering | Text to Columns and Removing Duplicates | Data validation by Customization of Error Alert and Input Box | Types of alerts (Stop, WARNING and Information), Highlighting the Invalid data  | List Box using Data Validation | Data Consolidation and Goal Seek
• PROOFING, COMMENTS and PROTECT
• Inserting and Editing Comments | Spell check, Track changes (highlight, accept and reject changes), Thesaurus | Protect Sheet, Protect Workbook | Sharing Workbook
• FORMULAS (FUNCTIONS)
• Logical Functions (IF, AND, OR, TRUE, FALSE, IFERROR AND NESTED IF FUNCTIONS) | Expense Budget check using IF condition | Checking values using AND & OR condition | Products consumption report using IFERROR | Computing electricity charges using Nested IF
• Math and Trigonometry Functions (CELLING, FLOOR, ROUND, SUM, SUMIF, SQRT and RANDBETWEEN) | Example stating different rounding methods on decimal digit numbers | Branch wise sales (outward) using SUMIF | Using Square root function | Generating random numbers between specific range
• Text Functions (CONCATENATE, DOLLAR) | Concatenating words to a single cell value | Setting a Number or sum of Numbers to Dollar currency.
• Date & Time Functions (TODAY, NOW, MINUTE, HOUR) | Finding Age using Today and year function | Getting system current data & time using Now Function | Worker Timing Sheet using Hour and minute function
• Statistical and Information Function (AVERAGE, COUNT, COUNTIF, MAX, MIN, RANK) | Finding sum, average, count, maximum and minimum value for range of cells | Finding Rank for the series of data | Branch wise sales and no of sales using SUMIF and COUNTIF
• Lookup and References Function (VLOOKUP, HLOOKUP, HYPERLINK) | Single Pay Slip of Employee using VLOOKUP | Picking product count from table using HLOOKUP | Creating various hyperlinks | Finding the position of a value from table using match
• Defining & managing the name to the range | Trace Precedents / Trace Dependents | Evaluate Formulas & ERROR Checking | Defining new function SPELLNUMBER
• OUTLINE
• Group and Ungroup | Adding SUBTOTALS to list
• VIEW SETUP
• Split window and Freeze panes | Workbook views (Normal, Page Break, Page Layout) | Arranging the window (Vertically and Horizontally) | Recording and Running a MACRO
• HINTS
• Calculating Percentage | Custom date formats in regional languages | Gantt chart on duration of work progress | Error occurs | Cell Reference (Relative, Absolute and Mixed Reference) |Multiple methods to Round a decimal number | Formatting Delimitation from Millions to Lakhs (Comma Separation)