Advanced MS EXCEL

1. Tables & Data Management
1.1. Table Guidelines
1.2. Sort Your Worksheet in Ascending or Descending Order
1.3. Create a Custom Sort Based on Multiple Criteria
1.4. Convert Your List to a Table
1.5. Add a Total Row to a Table
1.6. Remove Duplicates from a Table
1.7. Convert a Table to a List
1.8. Work with Automatic Subtotals

2. Highlight Specific Subsets of Data in a Large Worksheet (Conditional Formatting)
2.1. Apply Built-In Conditional Formatting to Cells
2.2. Add Data Bars & Color Scales to a Range of Cells
2.3. Convert Your Excel Data to Icon Sets
2.4. Create a Custom Conditional Format
2.5. Use Expressions in a Custom Conditional Format
2.6. Work with AutoFilter
2.7. Find a Unique Entry Using AutoFilter’s Search Feature Advanced Filter :
2.8. Create an Advanced Filter Using Single Criteria
2.9. Create an Advanced Filter Using Multiple Criteria
3.0 Extarct unique records using Advanced filters

3. Functions
3.1. Reference Cells in Other Worksheets in a Formula
3.2. Simplify Formulas with Names
3.3. Work with Names in the Name Manager
3.4. Create Basic IF Functions & Nested IF
3.5. Other Logical functions to combine multiple conditions – AND(), OR(),NOT()
3.6. Reference Functions : VLOOKUP & HLOOKUP, Index and Match()
3.7. Working with List / ranges using Database functions – Dsum, Dmax,Dmin, Dount, Daverage
3. 8.Text functions: Concatenate(), Trim(),Text(), Len(),Left(), Right(),mid(),exact(),find(),
search() and value()
3.9 Working with Date & Time – Date Functions: Dateif(), eomonth(),edate(),workday()
3.10 Statistical functions : countif(), countifs(),sumif(),sumifs()
3.11 some important financial functions – PMT(), Rate(), PV()

4. Consolidate Data & Find Errors
4.1. About Data Consolidation
4.2. Data Consolidation by Rows
4.3. Data Consolidation by Category
4.4. About Auditing
4.5. Tracing Precedents
4.6. Tracing Dependents
4.7. Tracing Errors

5. Create a Template for Future Worksheets
5.1. About Data Validation
5.2. Validate Data Based on a Range of Numbers
5.3. Validate Data Based on a List
5.4. Customize the Data Validation Input Message
5.5. Customize the Data Validation Error Alert
5.6. Create a New Template
5.7. Edit an Existing Template

6. Work with Scenarios & the Watch Window
6.1. Create Scenarios to See the Effect of Different Cell Values
6.2. Edit, Display & Remove Scenarios
6.3. Create a Scenario Summary Report
6.4. Use the Watch Window to Keep Track of Changing Data
6.5. Working with What if Analysis

7. Create Robust Analysis with PivotTables & Pivot Charts
7.1. About PivotTables & Pivot Charts
7.2. Create a PivotTable 7.3. Edit a PivotTable’s Field Settings
7.4. Redefine a PivotTable’s Data Source
7.5. Control a PivotTable’s Data Calculations
7.6. Format a PivotTable’s Results
7.7. Understand PivotTable Layout & Format Options
7.8. Use a PivotTable’s Totals & Filters Options
7.9. Create a Calculated Column in Pivot Table
7.10. Customize a PivotTable’s Display Options
7.11. PivotTable Printing & Data Options
7.12. Filter Data in a PivotTable with Slicers
7.13. Create a PivotChart

8. Work with Charts in Excel
8.1. Add an Embedded Chart to an Excel Spreadsheet
8.2. Add a Chart Sheet to an Excel Workbook
8.3. Add a Sparkline
8.4. Change the Chart Type
8.5. Edit, Add, and Remove Chart Data
8.6. Change a Chart’s Data Range
8.7. Apply a Chart Layout
8.8. Apply a Chart Style
8.9. Work with Chart Labels
8.10. Adjust a Chart’s Axes & Gridlines
8.11. Change a Chart’s Background

8.12. Add Trendlines & Error Bars to a Chart
8.13. Change the Size of a Chart

9 Data Validation
9.1 Specifying a valid range of values for a cell
9.2 Creating Lists
9.3 Specifying custom validations based on formula for a cell