Advanced Excel Training in Bangalore | Blue Ocean Learning
Advance Excel classes in jp nagar 7th phase Banglore

Advanced Excel

CELL REFERENCING..

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

NAMING RANGES

  • Defining a Range Name
  • Using a Range Name in a Formula
  • Moving to a Named Range
  • 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

    • 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

    COPYING DATA

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

CONDITIONAL FORMATTING

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

USING AUTOFILTERS

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

USING ADVANCED FILTERS

  • Using Advance Filters
  • Filtering the records using specified criteria

WORKING WITH FILTERED DATA

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

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

EMBEDDING AND LINKING OBJECTS

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

USING HYPERLINKS

  • Using Hyperlinks
  • Creating a Hyperlink

IMPORTING DATA

IMPORTING DATA FROM EXTERNAL SOURCES

  • Using Database Terminology
  • Importing Data into Excel

IMPORTING TEXT FILES

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

USING MICROSOFT QUERY

  • Adding a Data Source
  • Creating a Query

REFRESHING DATA

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

GETTING VISUAL

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

Practical Assignments

ADVANCED EXCEL FORMULAS

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

Practical Assignments

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

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

WHAT-IF ANALYSIS

  • Using What-If Analysis

GOAL SEEK

  • Using Goal Seek
  • Applying Goal Seek

SCENARIO MANAGER

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

SOLVER

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

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

PROTECTION AND SECURITY

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

MACROS

  • Displaying the Developer tab
  • Recording and running macros
  • owering 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