MDP on MS-Excel 2013

Faculty member(s) offering the MDP
Prof Malathi Sriram

Area: Systems

Topic Coverage / Outline

MS-Excel Workbook Environment

  • Opening a workbook
  • The Excel 2007 Ribbon, The Quick Access Toolbar
  • Worksheets
  • Moving Around a Worksheet and Workbook
  • Printing a Worksheet / Saving a Workbook File
  • Hands-On Exercise

Working with Data: Basic Techniques

  • Cells and Ranges
  • Selecting Ranges / Selecting All Cells in a Dataset Using Shortcut Keys
  • Selecting All Cells on a Worksheet / Selecting Non-contiguous Ranges
  • Selecting Cells and Define Named Ranges
  • Selecting Otherwise Difficult to Select Cells with Go To Special
  • Auto Filling
  • Copying and Moving Cell Entries
  • Hands-On Exercise

Enhancing Spreadsheet Readability

  • Working with Rows and Columns
  • Formatting
  • Cell Formatting
  • Number Formatting
  • Conditional Formatting
  • Formatting and Other Options with Paste Special
  • Set Up a Worksheet for Printing.
  • Hands-On Exercise

Excel Formulas

  • Copying Formulas
  • AutoComplete Formula Option
  • Entering Formulas from Menu
  • Editing or Correcting Formulas – Showing the Actual Formula in a Cell
  • Other Ways to Copy Formulas
  • Relative & Absolute Addressing
  • Using the F4 Key
  • Hierarchy of Mathematical Operations
  • Hands-On Exercise

Useful Excel Functions

  • Functions – SUM AVERAGE, MIN and MAX Aggregation Icon
  • IF Statements / Text Functions / Flash Fill
  • Basic Date and Time Functions
  • Lookup (VLOOKUP & HLOOKUP) Functions
  • Other Lookup & Reference functions
  • Error Trapping / Rounding Functions
  • Excel Financial Functions
  • COUNT, COUNTIF, COUNTA, and COUNTBLANK Functions
  • Excel Statistical Functions
  • Conditional Counts, Conditional Sums, Conditional Formatting
  • Removing Duplicates
  • Sorting and Filtering Data in Excel
  • Subtotals, Pivot Tables, Pivot Charts
  • Hands-On Exercise

Charts

  • Creating Charts
  • Resizing and Moving Charts
  • Basic Formatting of Charts
  • Formatting Axes and Data Series, Customizing Charts
  • Hands-On Exercise

Importing Data into Excel

  • From a Text or Word File / Using Web Queries to Import Data From the Web
  • Hands-On Exercise

Spreadsheet Modelling

  • Model Building Blocks
  • Model Terminology
  • Spreadsheet Model Building Process – Building a Model – Set up the Spreadsheet Model- Model Limitations
  • Data Tables, Two-Way Data Tables
  • Comments and Text Boxes
  • Error Trapping, Evaluate Formula Feature, Scenarios, Spinners

Pedagogy

Hands-on Sessions (Using MS – Excel), Case Discussions, and Group Activities.

Participants Expected Profile:

Open for all

Fees Proposed per participant

Rs.1500