Advanced Spreadsheet

Course Objective

This course furthers spreadsheet application skills to an expert level, allowing the person to master the advanced functions of spreadsheets application, enables to produce more sophisticated reports, and perform complex mathematical and statistical calculations.

Learning Outcomes

After successful completion of the program, you will be able to

  • Apply advanced formatting options, such as conditional formatting and customized number formatting, and handle worksheets.
  • Use functions such as those associated with logical, statistical, financial and mathematical operations.
  • Create charts and apply advanced chart formatting features.
  • Work with tables and lists to analyze, filter and sort data. Create and use scenarios.
  • Validate and audit spreadsheet data.
  • Enhance productivity by working with named cell ranges, macros and templates.
  • Use linking, embedding and importing features to integrate data.
  • Collaborate on and review spreadsheets. Apply spreadsheet security features.

Course Description

Download Brochure

Advanced Spreadsheets course is fully updated to cover all skills and techniques and enables person to maximize the output from the course. The course includes using conditional and custom formats, templates, charting features, creating pivottables, using auditing tools, linking data using advanced functions, tracking and merging and using worksheet protection and imparts technical skills and detailed domain knowledge through a well-developed curriculum.


USING CONDITIONAL AND CUSTOM FORMATS
  • Applying an Autoformat/Table Style to a Cell Range
  • Applying Conditional Formats
  • Changing a Conditional Format
  • Changing a Conditional Format
  • Creating a Custom Online Conditional Format
  • Using Data Bars
  • Deleting a Conditional Format
  • Creating a Custom Number Format
  • Review Exercise
USING TEMPLATES
  • Copying Worksheets
  • Hiding Columns and Rows
  • Unhiding Columns and Rows
  • Split a window. Move, remove split bars
  • Saving a Workbook as a Template
  • Using a Template
  • Editing a Template
  • Inserting a New Worksheet
  • Hiding/Unhiding a Worksheet
  • Deleting a Template
  • Finding Templates
  • Review Exercise
SORTING AND VALIDATING DATA
  • Creating Subtotals in a List
  • Removing Subtotals in a List
  • Sorting Data by Multiple Levels
  • Using Custom Sort
  • Using Data Validation
  • Validating Data using a List
  • Creating a Custom Error Message
  • Removing Data Validation
  • Review Exercise
WORKING WITH ADVANCED FILTERS
  • Automatically Filter a List in Place
  • Creating a Criteria Range
  • Using a Criteria Range
  • Showing all records
  • Using an advanced AND Condition
  • Using an advanced OR condition
  • Copying Filtered Records
  • Using Database Functions
  • Finding unique records
  • Removing duplicates from a table
  • Review Exercise
CHARTING FEATURES
  • Create a combined column and Line Chart
  • Re-position chart title, legend, data lebels
  • Formatting an Axis
  • Changing the axis scaling
  • Formatting the data series
  • Adding data from different worksheets
  • Using a secondary axis
  • Changing data series chart types
  • Changing source data range
  • Review Exercise
CREATING/REVISING PIVOT TABLES
  • Creating a Pivot Table Report
  • Adding Pivot Table report fields
  • Selecting a report filter item
  • Refreshing a Pivot Table report
  • Changing the summary function
  • Adding new fields to a Pivot Table Report
  • Moving Pivot Table Report Fields
  • Hiding/Unhiding/Sorting Pivot Table Report Items
  • Deleing Pivot Table Report Fields
  • Creating Report Filter Pages
  • Formatting a Pivot Table Report
  • Creating a Pivot Chart Report
  • Grouping data manually
  • Review Exercise
USING AUDITING TOOLS AND RANGE NAMES
  • Jumping to a named range
  • Assigning names
  • Using range names in formulas
  • Creating range names from headings
  • Apply range names
  • Deleting range names
  • Using range names in 3-D formulas
  • Displaying/removing dependent arrows
  • Displaying/removing precedent arrows
  • Showing formulas
  • Review Exercise
IMPORTING AND LINKING DATA
  • Linking data within a spreadsheet
  • Importing data from text files
  • Removing linked data
  • Creating a Hyperlink
  • Editing a Hyperlink
  • Removing a Hyperlink
  • Review Exercise
USING ADVANCED FUNCTIONS
  • Using the VLOOKUP function
  • Using the HLOOKUP function
  • Using the IF function
  • Using nested IF function
  • Using an AND condition with IF
  • Using an OR condition with IF
  • Using the ROUND function
  • Using Date functions
  • Using the COUNTIF function
  • Using the COUNTBLANK function
  • Using the SUMIF function
  • Using the RANK function
  • Using financial functions
  • Using text functions
  • Using a 3-D reference within a SUM function
  • Use mixed references in formulas
  • Review Exercise
USING SCENARIOS
  • Creating a scenario
  • Displaying a scenario
  • Editing a scenario
  • Creating a scenario summary report
  • Working with data tables
  • Placing formulas in data table
  • Creating a one-variable table
  • Creating a two-variable table
  • Review Exercise
USING WORKSHEET PROTECTION
  • Unlocking Cells in worksheet
  • Hide, unhide formulas
  • Protecting a worksheet
  • Unprotecting a worksheet
  • Creating Allow-Editing Ranges
  • Deleting Allow-Editing Ranges
  • Assigning a Password
  • Opening a password-protected file
  • Removing a password
  • Review Exercise
TRACKING AND MERGING WORKBOOKS
  • Shaving a shared Workbook
  • Viewing users sharing a Workbook
  • Viewing shared workbook changes
  • Changing the update frequency
  • Highlighting Changes
  • Managing conflicting changes
  • Resolving conflicting changes
  • Setting change history options
  • Adding a history worksheet
  • Reviewing tracked changes
  • Merging Shared Workbook Files
  • Review Exercise
RECORDING MACROS
  • Recording a Macro
  • Saving a Macro-Enabled Workbook
  • Running a Macro
  • Assigning a shortcut key
  • Using a shortcut key
  • Deleting a Macro
  • Adding a Macro to the Quick access toolbar
  • Deleting a Macro to the Quick access toolbar
  • Review Exercise
COMMENTS AND PASTE SPECIAL
  • Inserting Comments
  • Viewing Comments
  • Reviewing Comments
  • Printing Comments
  • Paste Special
  • Review Exercise

Course Extract

  Level Beginner
    Methodology Online / Offline / Self Learning
    Duration 8 hrs
    Price ₹ 4999 – Offline
₹ 3499 - Online
₹ 1999 - Self Learning
  Pre-requisites None
  Batch size 10-15 students
  Classes On weekend / Sunday
  Certificate Yes
  Placement Assistance Yes

Early bird offer

We're ready to listen...

* Indicates a required field

Complete the form below or call us on 784 007 8366.

What services are
you interested in?

We're ready to listen...

* Indicates a required field

Complete the form below or call us on 784 008 4266.

What services are
you interested in?