SAS Training Online

Govt. Logos
Excel Training

Excel Training

The goal of this Section is to help you to learn how to use Microsoft Office Excel formulas, functions and macros (VBA) through our free and good quality training video tutorials. Our coming videos will demonstrate how you can use MS-Excel for storing, organizing and manipulating large amounts of data.You should be able to find solutions to many financial calculations and what-if scenarios quickly after studying the presented help articles and content. 

Why learn Excel? Why is MS-Excel so important in today’s work and business environment? Microsoft Office Suite is used by nearly every employer, school and university in the world and the more extensive your skills with the application suite, particularly Excel spreadsheets, the more attractive a candidate you are when applying for jobs. How we have organized our Excel learning course: Our program is based on the fundamental understanding of the working of the computer: Input-Process-Output. Therefore, every work that we do must take this into account. 

Our data entry should be done after finalizing a plan on paper even for relatively uncomplicated analysis. The process should use the relevant formulas, functions and macros and Excel then takes care of optimum CPU and memory usage. Finally we need to get our output for various purposes like printing a hard copy or preparing a presentation for our meetings. With this in mind let’s see how we can benefit from Microsoft’s excellent software.

We share best use of Excel.Program is based on real scenario problem.Entire program is full of illustration and case study. Practical experience of Program Director will help to have a practical view of each function.Program is spread over couple of weekends that give time to practice and raise question in subsequent sessions.

All Courses

Warm-up

  • Essential shortcuts
  • AutoFill options
  • Paste Special (Value, Transpose)
  • Absolute & Relative referencing ($)
  • ROUND()

Data Analytics

  • Sort & Filter
  • Using SUBTOTAL() with filtered data
  • SUMIFS(), COUNTIFS()
  • Pivot Table for multi-variable analysis
  • Computations %, Sum, Max, Min, Average, Count
  • Grouping (Clustering)
  • Generating multiple reports

Data Lookup

  • VLOOKUP() – a practical perspective
  • VLOOKUP() with MATCH()

Logical Statements

  • IF(), Nested IFs, AND(), OR()
  • ISNUMBER(), ISTEXT(), ISBLANK(), ISERROR()

Data Cleaning – I

  • LEFT(), RIGHT(), MID()
  • LEN()
  • UPPER(), PROPER(), LOWER()
  • TRIM(), VALUE()

CONCATENATE(), & Data Cleaning – II

  • Find & Replace (using wildcard character – Asterisk * )
  • Go To (Special)
  • Text-to-Columns (incl. advanced tricks)
  • Remove Duplicates Working with Dates
  • Date correction techniques
  • DAY(), MONTH(), YEAR()
  • EDATE(), EOMONTH()
  • TODAY()

MIS reporting

  • Automatic row-wise Subtotal
  • Conditional Formatting (Blanks, Errors, Cell Values, Duplicates)
  • File Password Select Dashboard Techniques
  • Activate-Deactivate Gridlines
  • Data Validation (list)
  • Cell-Range Naming
  • Grouping
  • Hyper linking
  • Freeze-Unfreeze panes
  • Hide-Unhide Columns & Rows

Data Lookup

  • INDEX() and 2 MATCH()

MIS reporting

  • Formula based Conditional Formatting
  • 3-D Data Consolidation

What-IF Analysis>

  • Data Tables, Goal Seek
  • Form Controls – Spin Button & Scroll Bar

Macros

  • Concept; Macros Recorder
  • Record & Run

Special Charts (with Videos)

  • Thermometer chart
  • Two-axis chart
  • Trend line chart
  • Exploded Pie-chart

Select Formulas & Techniques

  • IFERROR()
  • SEARCH(), SUBSTITUTE()
  • SUMPRODUCT()
  • Cell Protection