Microsoft Excel Advanced

Who Should Do This Course?

This course is intended for current Microsoft Excel users who wish to extend their knowledge and skills beyond building simple workbooks.


About The Course

This course covers how to work more efficiently with workbooks and worksheets, enhance the appearance of data and worksheets, perform analysis using functions and use the sorting and filtering features to manage and analyse data.


Learning Outcomes

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

  • use the fill operations available to fill a data series
  • understand and use formula cell referencing to create more complex formulas
  • use a range of logical functions
  • apply conditional formatting to ranges in a worksheet
  • create and use labels in a workbook
  • work with various elements of a worksheet
  • use a range of techniques to work with worksheets
  • use a range of find and replace techniques
  • apply a variety of page setup techniques
  • sort data in a list in a worksheet
  • filter data in a table
  • create effective charts in Microsoft Excel
  • use a range of techniques to enhance charts
  • apply formatting techniques to text on charts

Each student will receive

  • Softcopy of training manual, shortcut keys and formulas
  • 3 Months FREE email support for queries

Duration: 2 days

Time: 10:00 am to 5:30 pm


Trainer:

Ramzan Rajani (Microsoft office specialist & Microsoft certified trainer)


Unit 1: Filling Data
  • Understanding Filling
  • Filling A Series
  • Creating A Custom Fill List
  • Modifying A Custom Fill List
  • Deleting A Custom Fill List
Unit 2: Formula Referencing
  • Absolute and Relative Referencing
  • Relative Formulas
  • Problems With Relative Formulas
  • Creating Absolute References
  • Creating Mixed References
  • Other basic formulas
Unit 3: Creating Name range
  • Creating Names for the range
  • Using Names In New Formulas
  • Creating Names Using The Name Box
  • Using Names To Select Ranges
  • Pasting Names Into Formulas
  • Creating Names From A Selection
  • Using The Name Manager
Unit 4: Logical Functions
  • Understanding Logical Functions
  • Using IF To Display Text
  • Using IF To Calculate Values
  • Nesting IF Functions
  • Using IFERROR
  • Using AND
  • Using OR
  • Vlookup function
  • Multiple Vlookup
Unit 5: Conditional Formatting
  • Formatting Cells Containing Values
  • Clearing Conditional Formatting
  • More Cell Formatting Options
  • Top & Bottom Ten Items
  • Working With Data Bars
  • Working With Colour Scales
  • Working With Icon Sets
  • Sparkline (New feature)
  • Creating Sparklines
  • Editing Sparklines
Unit 6: Advance Validation
  • Creating drop down in cells
  • Restriction values from list only
  • Creating error message
  • Creating dependent list
Unit 7: Formulas
  • Count, CountA, CountIF & CountBlank
  • Sum, SumIF & SumIFs
  • Networkdays
  • Networkdays International (For ver 2010)
  • Today & Now function
  • Trim (Removing unwanted spaces)
  • Concatenate (Combining columns)
Unit 8: Dynamic table (New feature)
  • Converting data into table
  • Automation calculation in table
  • Converting table to normal range
  • Using table in Charts & Vlookup
Unit 9: Sorting Data
  • Understanding Lists
  • Performing An Alphabetical Sort
  • Performing A Numerical Sort
  • Sorting On More Than One Column
  • Sorting By Rows
  • Working with subtotal
  • Paste special
  • Hyperlink
Unit 10: Filtering Data
  • Understanding Filtering
  • Applying And Using A Filter
  • Clearing A Filter
  • Creating Compound Filters
  • Multiple Value Filters
  • Creating Custom Filters
  • Using Wildcards
Unit 11: Creating Charts
  • Choosing The Chart Type
  • Creating A New Chart
  • Working With An Embedded Chart
  • Resizing A Chart
  • Dragging A Chart
  • Changing The Chart Type
Unit 12: Charting Techniques
  • Adding A Chart Title
  • Adding Axes Titles
  • Positioning The Legend
  • Showing Data Labels
  • Showing A Data Table
  • Modifying The Axes
  • Showing Gridlines
  • Adding A Trendline
  • Adding A Text Box To A Chart
  • Creating combination chart
  • Creating 2 Axis chart
Unit 13: Pivot Tables
  • Understanding Pivot Tables
  • Creating A PivotTable Shell
  • Dropping Fields Into A PivotTable
  • Filtering A PivotTable Report
  • Clearing A Report Filter
  • Calculation in pivot
  • Formatting A PivotTable Report
  • Dynamic range for pivot
  • Understanding Slicers (New Feature)
  • Creating Slicers (New Feature)
Unit 14: Macros
  • Recording & Running Macros
  • Creating button to run the macro
  • Assigning shortcut to macro
  • Deleting Macros
  • Relative reference in Macros
Unit 15: File / Data Security
  • Open & Modify file password
  • Sheet protection
  • Protecting your sheet from deletion
  • Protecting few cell, rows or cols
  • Protecting your data from copying