Advanced Excel

User Avatar
Free

Advanced Excel Training In Bangalore

Advanced Excel is today the most popular Office Data Analysis and Reporting software. This program enables users to acquire the necessary knowledge and skills to use work advance features of Microsoft Excel 2007 effectively

 

Intended audience

Working professionals from any function (Accounts, finance, process etc.),who work on large data and do extensive calculations, data analysis and prepare complex reports on regular, periodic basis.

 

Prerequisites

  • Participants need to have some working experience in any of the Windows operating systems.
  • Participants must have understanding of basic-intermediate (L1) level of MS Excel They must be well versed with basic concepts used in daily office work.

 

Key Skills

Upon successful completion of this course, learners will be able to:

  • Work with formula and functions
  • Efficient use of What-if-Analysis features of Excel
  • Data Analysis with Pivot Tables
  • Record Macros for daily life Routine work in excel

Instructional method

  • This course provides classroom lecture topics and the practical application of MS Excel through demonstrations, practices, and hands-on exercises, followed by a small case study.

Course Duration : two days 8hrs per day

 

Course content

 

DAY 1 MORNING SESSION

module 1 : CELL REFERENCING

 

  • Relative cell referencing within in formulas
  • Absolute cell referencing within formulas
  • Usage of Cell references
  • How Implement the cell references

module 2 : NAMING RANGES

 

  • Defining a Range Name
  • Using a Range Name in a Formula
  • Moving to a Named Range

module 3 : EXCEL FUNCTIONS

 

  • SUM,AVERAGE,MIN,MAX
  • LARGE,SMALL,
  • COUNT,COUNTA,COUNTBLANK
  • COUNTIF ,COUNTIFS
  • SUMIF,SUMIFS,AVERAGEIF,AVERAGEIFS
  • IF ,NESTED IF, IF USING LOGICAL OPERATOR LIKE AND ,OR
  • SUBTOTAL

 

Practical Assignments

module 4 : AFTERNOON SESSION

 

  • Consolidation the excel sheets,workbooks using formulas
  • Date Functions TODAY,WEEKDAY,YEARFRAC,NOW,NETWORKDAYS,DAYS360
  • Text formulas
  • PROPER,UPPER, LOWER, CONCATENATE, LEFT, RIGHT, MID,LEN,REPT,TRIM,SUBSTITUTE,LEN,ISTEXT,EXACT

module 5 : COPYING DATA

 

  • Using the Office Clipboard
  • Using Paste Special
  • Using Paste Special with content from Excel
  • Using Paste Special with content from other application

module 6 : CONDITIONAL FORMATTING

 

  • Using conditional formatting
  • Changing conditional formatting
  • Deleting conditional formatting
  • Condition formatting using formulas

module 7 : USING AUTOFILTERS

 

  • Filtering data
  • Filtering data with AutoFilter
  • Specifying a conditional filter with AutoFilter
  • Using the Top 10 AutoFilter
  • Removing all AutoFilters

module 8 : USING ADVANCED FILTERS

 

  • Using Advance Filters
  • Filtering the records using specified criteria

module 9 : WORKING WITH FILTERED DATA

 

  • Manipulating Filtered Data
  • Totaling fields within a filtered database list
  • Creating Subtotals
  • Remove Subtotals

module 10 : CHARTING IN EXCEL

 

  • Charts Refresher
  • Moving, Sizing & Copying Charts
  • Formatting Charts
  • Formatting a Series
  • Exploring the Home & Format Ribbons
  • Deciding What Chart Format to Use
  • Show a Time Series with Column or Line Charts
  • Using Combination Charts
  • Using Line Chart Accessories
  • Using Bar Charts to Show Comparisons
  • Using Component Charts
  • Using Correlation Charts
  • Exploring Other Charts
  • Chart Lies Revealed & Advanced Chart Types
  • Creating a Custom Layout 

module 11 : EMBEDDING AND LINKING OBJECTS

 

  • Embedding data
  • Embedding an existing document
  • Linking data
  • Creating a link to an existing file
  • Linking data from Microsoft application

module 12 : USING HYPERLINKS

 

  • Using Hyperlinks
  • Creating a Hyperlink

 

IMPORTING DATA

module 13 : IMPORTING DATA FROM EXTERNAL SOURCES

 

    • Using Database Terminology
    • Importing Data into Excel

</ul

module 14 : IMPORTING TEXT FILES

 

  • Importing text files into Excel
  • Importing text using the Text Import Wizard
  • Refreshing data from imported text files

module 15 : USING MICROSOFT QUERY

 

  • Adding a Data Source
  • Creating a Query

module 16 : REFRESHING DATA

 

  • Refreshing external data without losing the formatting
  • Refreshing external data automatically

module 17 : GETTING VISUAL

 

  • Using SmartArt Functions
  • Using Different SmartArt Graphics
  • Embedding a Formula into a Shape

 

DAY 2 MORNING SESSION

module 18 : ADVANCED EXCEL FORMULAS

 

  • Array formulas
  • LOOKUP,HLOOKUP,VLOOKUP,INDEX,MATCH,OFFSET
  • ISERROR ,IFERROR
  • Nested functions

 

Practical Assignments

AFTER NOON SESSION

module 19 : USING DATA VALIDATION

 

  • Setting data validation
  • Creating the Input Message
  • Displaying an Input Message
  • Creating the Error Alert
  • Displaying an Error Alert
  • Validation using formulas

module 20 : AUDITING

 

  • Tracing precedent cells
  • Tracing the dependants of a cell
  • Displaying all formulas within a worksheet
  • Adding comments
  • Displaying comments
  • Removing comments
  • Editing comments
  • LINKING & CONSOLIDATING DATA
  • Linking individual cells within a worksheet
  • Linking charts to data within a worksheet
  • Linking a cell range on one worksheet to another worksheet (within the same workbook)
  • Linking data on one worksheet to a chart in another worksheet (within the same workbook)
  • Linking data from one workbook to another
  • Linking a chart from one workbook to another
  • Copying data from Excel into a Word document
  • Linking data from Excel into a Word document
  • Copying a chart from Excel into a Word document
  • Linking a chart from Excel into a Word document
  • Consolidating data over several worksheets or worksheet pages

 

ANALYZING DATA

module 21 : WHAT-IF ANALYSIS

 

  • Using What-If Analysis

module 22 : GOAL SEEK

 

  • Using Goal Seek
  • Applying Goal Seek

module 23 : SCENARIO MANAGER

 

  • Using Scenario Manager
  • Adding a Scenario
  • Showing a Scenario
  • Deleting a Scenario
  • Editing an existing Scenario
  • Summarizing Scenarios

module 24 : SOLVER

 

  • Using Solver
  • Installing Solver
  • Applying Solver
  • Changing a Constraint
  • Deleting a Constraint

 

IMPORTING DATA

module 25 : WORKING WITH PIVOT TABLES

 

  • Creating a Pivot Table
  • Rearranging Fields in a Pivot Table
  • Explaining the Report Layout Options
  • Using the Report Filters Feature
  • Using Top 10 & Date Filters
  • Handling Blank Cells
  • Drilling Down in the Pivot Table
  • Sorting a Pivot Table
  • Formatting a Pivot Table
  • Creating Custom Formats
  • Explaining the Grouping Options
  • Adding Formulas to a Pivot Table
  • Changing a Calculation in a Pivot Table
  • Replicating a Pivot Table
  • Counting with a Pivot Table
  • Using Pivot Charts

module 26 : PROTECTION AND SECURITY

 

  • Specifying a password for opening a workbook
  • Using the “read-only recommended” option
  • Protecting a worksheet or worksheet elements
  • Allowing selective editing of a protected worksheet

module 27 : MACROS

 

  • Displaying the Developer tab
  • Recording and running macros
  • Lowering your macro security level
  • Customizing the Quick Access Toolbar
  • Changing the Quick Toolbar Macro icon
  • Removing a macro icon from the quick access toolbar
  • Raising your macro security level

 

 

Course Features

  • Lectures 0
  • Quizzes 0
  • Duration 50 hours
  • Skill level All levels
  • Language English
  • Students 0
  • Assessments Yes

You have 10 weeks remaining for the course

Curriculum is empty
Free

Leave A Reply

Your email address will not be published. Required fields are marked *