+91 72087 69880 info@landdedutech.com
Advanced Excel

What you'll learn

⦁ Good understanding of Excel at MIS level
⦁ Expertise in Text Function
⦁ Expertise in Logical Function
⦁ Expertise in Math Function
⦁ Expertise in Lookup and Reference Function
⦁ Expertise in Date and Time Function
⦁ Mastery in Pivot Table and Chart Preparation
⦁ Mastery in ‘What if Analysis’ tools
⦁ Print Option in Excel
⦁ Data Validation, Filter and Conditional Formatting
⦁ Mastery in Data organising Tools in Excel
⦁ Mastery in Data creation and Data Manipulation in Excel
⦁ Managing data protection and data sharing in Excel
⦁ Work with Macro Recording

Pre-Requisite

Basic Computer Knowledge

Description

Microsoft Excel is a spreadsheet application developed by Microsoft Inc for Microsoft Windows and MAC OS X. Its use is to do advanced calculation, graphing tools, pivot tables, and a macro programming language referred to as Visual Basic for Applications. The course curriculum is one of the most comprehensive and most advanced.

INTRODUCTION

⦁ An overview of the screen, navigation and basic spreadsheet concepts
⦁ Various selection techniques
⦁ Shortcut Keys

CUSTOMIZING EXCEL

⦁ Customizing the Ribbon
⦁ Using and Customizing AutoCorrect
⦁ Changing Excel’s Default Options

USING BASIC FUNCTIONS

⦁ Using Functions – Sum, Average, Max,Min, Count, Counta
⦁ Absolute, Mixed and Relative Referencing

FORMATTING AND PROOFING

⦁ Formatting Cells with Number formats, Font formats, Alignment, Borders, etc
⦁ Basic conditional formatting

MATHEMATICAL FUNCTIONS

⦁ SumIf, SumIfs CountIf, CountIfs AverageIf, AverageIfs

PROTECTING EXCEL

⦁ File Level Protection
⦁ Workbook, Worksheet Protection

TEXT FUNCTIONS

⦁ Upper, Lower, Proper
⦁ Left, Mid, Right
⦁ Trim, Len, Exact
⦁ Concatenate
⦁ Find, Substitute

DATE AND TIME FUNCTIONS

⦁ Today, Now
⦁ Day, Month, Year
⦁ Date, Date if, DateAdd
⦁ EOMonth, Weekday

ADVANCED PASTE SPECIAL TECHNIQUES

⦁ Paste Formulas, Paste Formats
⦁ Paste Validations
⦁ Transpose Tables

New in Excel 2013 / 2016 & 365

⦁ New Charts – Tree map & Waterfall
⦁ Sunburst, Box and whisker Charts
⦁ Combo Charts – Secondary Axis
⦁ Adding Slicers Tool in Pivot & Tables
⦁ Using Power Map and Power View
⦁ Forecast Sheet
⦁ Sparklines -Line, Column & Win/ Loss
⦁ Using 3-D Map
⦁ New Controls in Pivot Table – Field, Items and Sets
⦁ Various Time Lines in Pivot Table
⦁ Auto complete a data range and list
⦁ Quick Analysis Tool
⦁ Smart Lookup and manage Store

Sorting and FILTERING

⦁ Paste Formulas, Paste Formats
⦁ Paste Validations
⦁ Transpose Tables

PRINTING WORKBOOKS

⦁ Setting Up Print Area
⦁ Customizing Headers & Footers
⦁ Designing the structure of a template
⦁ Print Titles –Repeat Rows / Columns