Advanced Level
Excel User Interface
The Ribbon and the levels of Command organization –Tabs, Groups, and Dialogue box launcher,
Office Button and Excel Options
Customizing the Quick Access Tool Bar
Excel 2010 backstage view
Arriving at Keyboard shortcuts
Working with formatting
Formatting different data types, Built-in and Custom Formats
Conditional Formatting of data using highlighting rules, top-bottom rules, data bars, color scales,
Icon sets
Conditional Formatting using formulas
Dynamic Conditional Formatting using data validation
Data Validation
Restricting data entry in cells only to defined valid values
Restricting data types in cells, Restricting duplicate entry of data
Creating drop-down lists using data sources in different worksheets and workbooks
Using formulas for data validation
Creating Pickup Lists using data validation
Sorting and Filtering
Multilevel Sorting, Sorting with and without headers
Sorting using the Custom List, Sorting data in a row
Using Auto Filter, Text and number filters
Advanced data Filtering based on a single criteria, multiple criteria, and using AND and OR
conditions
Filtering unique records
Formulas and Functions
Different ways of giving Formulas
Syntax of a Function - Parameters and Values
Different types of Cell Referencing styles.A1 style -absolute, relative, and mixed references, and
R1C1 Style
Creating and changing cell references
Linking data within worksheets and workbooks
Excel Functions: Text, Logical, Lookup, Statistical, Financial including VLOOKUP, HLOOKUP, INDEX,
MATCH, IF, AND, OR, COUNTIF, COUNTIFS, SUMIF, SUMIFS, CONCATENATE, REPLACE, SUBSTITUTE,
LEFT, RIGHT, MID, FIND, LEN, UPPER, LOWER, SUM, AVERAGE, MIN, MAX, OFFSET, CHOOSE,
INDRECT, INDEX, MATCH, DATEDIF, NETWORKDAYS etc.
Nested Functions and Array Functions, Array VLOOKUP
Formula Auditing, Tracking Formulas, Formula calculation options
Checking errors in formulas, error types, circular reference
Creating Named Ranges, Using Named Ranges in the Formulas, Using the Name Manager
Pivot Tables and Reports
Creating Pivot tables and analyzing data, Pivot Table field list, views, and styles
Grouping Data within a Pivot Table
Refreshing the Pivot Table with Data, Automatic or manual updating of the report layout
Inserting calculated field to perform calculations on elements of the Pivot table
Showing data as percentages, showing data with bars for visual presentation
Pivot table Wizard – creating Pivot table from different sources
Adding slicers to the Pivot table
Creating Pivot Charts
What-if analysis
Scenarios
Creating scenarios and setting variables for different scenarios
Merging different scenarios
Creating a summary of different scenarios
Using Pivot Table in scenarios
Data tables
Creating a one-variable data table
Creating a two-variable data table
Arranging data for creating a data table
Goal Seek
Charts
Plotting data series from worksheet rows and columns
Different chart Types, changing the Chart Type
Working with chart area, plot area, and various chart elements
Adding a secondary series in the chart, Creating Combo Charts
Sparklines (Mini charts)
Creating dynamic charts
Grouping and ungrouping data
Grouping Data in rows and columns
Using Subtotal
Creating Hyperlinks
Creating hyperlinks within a workbook
Creating hyperlinks to Word and Power Point
Using hyperlinks to create an index or menu in an excel file
Data Management
Removing Duplicates in data
Formatting data as a Table
Creating custom lists for Autofill
Paste special - Transposing Columns to Rows, Rows to Columns, performing mathematical
operations while pasting
Workbooks and Worksheets
Grouping Worksheets and working with grouped sheets
Viewing and Editing Workbook Properties
Arranging multiple windows, multiple views
Protecting workbooks, worksheets, and cells
Printing
Print Options – setting print area, printing headers on all pages
Working with Headers and Footers
Macros
Introduction to Macros
Preparing a workbook for Macros,
Recording Macros using Absolute and Relative Reference
Running Macros
Visual Basic Interface and Editor
Elegant IT Services