ADVANCE EXCEL OVERVIEW

 

Mathematical Sum, Sumproduct, Average, Averagea, Min, Mina, Max, Maxa, Small, Large, Abs, Count, Countblank, Counta, Aggregate, Mod, Rank Absolute and Relative Referencing
Paste Special Value, Format, Transpose, Link, Add, Subtract, Product and Many Useful Features
Rounding Functions Round, Roundup, Rounddown, (Also to nearest Tens, Hundreds and Thousands), Ceiling, Floor and Mround
Date Day, Date, Month, Year, Now, Today, Cleaning and Formatting
Text Functions Trim, Concatenate, ‘&’, Lower, Proper, Upper and Value
Advance Text Left, Right, Mid, Replace, Substitute, Find, Len, Working With ‘*’ For Data Cleaning, Text To Column & Other Advance Data Cleaning Techniques
Shortcuts Smart Editing Techniques, Learning Useful Shortcuts The Easy Way and Formatting
Lookup Functions Vlookup, Hlookup & Automation Of The Same
Logical Operators If, Nested If, And, Or, Isna, Iserror, Iserr, Isblank, Istext, Isformula & XOR
Conditional Formatting Basic Conditional Formatting (Formatting using Formulas covered in next Module)
Array Functions Array formulas, Array constant, Create one dimensional and two dimensional constants, Dynamic Transpose

MS WORD OVERVIEW

 

Introduction Basic formatting, navigation pane, quick access toolbar, ribbon, header and footer bullet points, symbols, bookmarks, hyperlinks.
Page Layout Modification Paper margin, inserting columns, page colour, page border, watermark, page break, alignment, line spacing, use of indents, justification, numbering and bullets.
Designing Tables, charts, wrapping, positioning, resizing, smart-art, customizing the document, style set colours, creating quick styles, themes and restrict the formatting, converting a text into table and rows, creating a form, comments, split windows, multiple windows.
Review Grammar and spell check, use of word count, thesaurus and translation, how to set options for print, track changes.
Mail Merge Automation of billing processes using Excel database and mail merge functions.
MS Word Tricks Auto generation of table of contents, cool shortcuts, and quick formatting techniques, spike test, page layouts.

MS PowerPoint OVERVIEW

 

Introduction The Ribbon, Quick access toolbar, customising the ribbon, different views of a presentation (Normal, Outline View, Slide sorter, Notes page, Reading View and Slide show).
Create a Presentation Blank presentations, presentation using templates, searching for templates, importing word document outlines into presentations, adding slides, creating hyperlinks, changing layouts and exploring the outline.
Quick Formatting Techniques Adding text, table, charts graphs, pictures (resizing, cropping, applying effects and styles), media, changing themes and colors, configure presentation to print or save, dos and don’ts of a presentation.
Working with Slides Duplicating existing slides, hiding slides, deleting slides, modifying slide background, applying styles to slides, inserting transitions between slides, order and group slides.
Shapes, Alignment & Distribution Tricks to insert, format, align, group, and distribute shapes in PowerPoint within seconds, format dipper, default shapes, merge shape, etc.
SmartArt Insert, add text to, and format SmartArt graphics to quickly add diagrams to your presentation which will assist you to tell your story.
Adding and Editing Charts Select the right chart for your data, add it to a slide, know the basics of editing charts and quickly format charts.
Slide Master and Sections for Organization How to manipulate PowerPoint presentations with the slide master, creating customised slides, sections, viewing modes, header and footer.
Insert and format Media Insert video from another file, adjusting media window size, trimming timings on media clips, screen recording and adding audio to the presentation.
Animate slide Content Applying animations to shapes and text, adding paths to animations, modifying animation options, recording animations and using the animation pane.
Using Excel and Word Data PowerPoint shortcuts and automation techniques, linking of Excel data with PPT.
Creating Impact Emerging trends on visual effects such as magnifiers, highlighters, de-highlighters, cut-outs, layers, topic trackers.
Speed Tricks Lightning fast shortcuts and custom tricks to perform recurring activities such as alignment, editing text, cloning shapes, working with SmartArt etc.
Delivering the Presentation Using techniques like home slide approach, exploring slide show options, using presenter view, slide notes.

 


DATA MANAGEMENT OVERVIEW

 

Advance Logical Countif, Sumif, Averageif, Sumifs, Countifs, Averageifs, Its application in dashboards
Advance Date Workday, Networkday, Workday.Intl, Networkday.Intl, EOMonth & Datedif
Database Dsum, Dmax & Daverage
Table Table Format, Automatic updation of formulas, Slicer
Cell Naming Name range, Dynamic Naming, Application
Data Validation Data Validation, Lists, Rules, Input and Error Messages and Basic Form Controls, Designing Forms, Dynamic Drop-down
Lookup & Reference Functions 3-D Vlookup, Fuzzy lookup, Index and Match, combining match with Vlookup, Choose, Indirect, Offset, Application
Advance Filter Single and multiple-level subtotals, subtotal formula, group ungroup, unique values, custom filters & Advance Slicer.
Remove Duplicates Various techniques to remove duplicates, extract unique values and to compare lists and compile data
Data Cleaning Goto Special, cleaning techniques for imported data to avoid error, Using Filters to Clean Data
Array Functions Array formulas, Array constant, Create one dimensional and two dimensional constants, Dynamic Transpose
Circular References Understanding model circularity & why some professionals use it and others don’t, Locating and managing circular reference and eliminating non-intentional circular references.
Security & Sharing Unlocking cells & protecting worksheets, Sharing workbooks, Tracking changes
Formula Auditing Techniques Check Formulas, Spot errors, Linking and cleaning Techniques, Tracing Precedents and Dependents
Advance Charts Thermometer, Speedometer, Sparklines, Target Charts, Gantt Charts, Form Controls Based Dynamic Charts
Macros Run, record, use, save and work with them
VBA Code Snippets To be able to use free VBA code snippets available online and use them to perform certain tasks like convert a number in word form
Customizing Ribbon Have your own ribbon like File, View with your designed Functions to perform repetitive tasks, automation and extremely useful time saving technique.
Scenario Manager Scenario analysis, Goal Seek, Data Table, use of macros to make it user friendly
Sensitivity Analysis Running Sensitivity Analysis with Drop-down menus or Data Validation Tools
Dashboard Preparation Dashboard for MIS, Analysis, different dashboards for different business segments, Auto report Generation – 100s with a click, Calculated Fields, Using Slicers, Connect a slicer to pivot tables from different sources and easy formatting techniques (formulas like offset, advance formatting, macros, hyperlink, etc. in creating Dashboards)

 

Course Related Free PDF

Whatsapp Youtube