Duration : 1 Month

Fees : 6000 /-

Basics

Ribbon: Excel selects the ribbon's Home tab when you open it. Learn how to minimize and customize the ribbon.

  • Tabs : The tabs on the ribbon are: File, Home, Insert, Page layout, Formulas, Data, Review and View. The Home tab contains the most frequently used commands in Excel.
  • Customize the Ribbon: Excel 2010 makes it possible to easily create your own tab and add commands to it. If you are not new to Excel, you can skip this paragraph
  • Quick Access Toolbar
  • Developer Tab

Workbook: A workbook is another word for your Excel file. Excel automatically creates a blank workbook when you open it.

  • Open an Existing Workbook
  • Save in different format
  • View Multiple Workbooks - View Tab - > Arrange all
  • Auto Recover
  • File -> Info -> Manage version

Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. By default, each Excel workbook contains three worksheets.

  • Select a Worksheet
  • Rename a Worksheet
  • Insert a Worksheet
  • Move a Worksheet
  • Delete a Worksheet
  • Copy a Worksheet
  • Zoom
  • Split
  • Freeze Panes
  • Group Worksheets
  • View Multiple Worksheets
  • Spelling

Format Cells: When we format cells in Excel, we change the appearance of a number without changing the number itself.

  • Decimal Places
  • Date and Time Formats
  • Fractions
  • Currency vs Accounting
  • Custom Number Format
  • Format Painter
  • Cell Styles
  • Themes

Find & Select: Learn how to use Excel's Find, Replace and Go To Special feature.

  • Find Features
  • Delete Blank Rows
  • Row Differences
  • Copy Visible Cells Only

Templates: Instead of creating an Excel workbook from scratch, you can create a workbook based on a template. There are many free templates available, waiting to be used.

  • Calendar
  • Budget
  • Meal Planner
  • Invoice
  • Automated Invoice
  • Default Templates

Data Validation: Use data validation in Excel to make sure that users enter certain values into a cell.

  • Reject Invalid Dates
  • Budget Limit
  • Prevent Duplicate Entries
  • Drop-down List

Print: This chapter teaches you how to print a worksheet and how to change some important print settings in Excel.

  • Print a Worksheet
  • Multiple Copies
  • Orientation
  • Page Margins
  • Scaling
  • Workbook Views
  • Page Breaks
  • Headers and Footers
  • Page Numbers
  • Print Titles
  • Center on Page
  • Print Gridlines & Headings
  • Print Area

Protect: Encrypt an Excel file with a password so that it requires a password to open it.

  • Protect Workbook
  • Protect Sheet
  • Lock Cells
  • Read-only Workbook
  • Mark as Final

Functions/Formulas

Count and Sum: The most used functions in Excel are the functions that count and sum. You can count and sum based on one criteria or multiple criteria.

  • Count
  • Countif
  • Countifs
  • Sum
  • Sumif
  • Sumifs
  • COUNTBLANK
  • COUNTA
  • SUMPRODUCT

Logical: Learn how to use Excel's logical functions such as the IF, AND and OR function.

  • IF
  • AND
  • OR

Cell References: Cell references in Excel are very important. Understand the difference between relative, absolute and mixed reference, and you are on your way to success.

  • Relative Reference
  • Absolute Reference
  • Mixed Reference
  • Copy Exact Formula
  • 3D-reference
  • External References
  • Hyperlinks

Date & Time: To enter a date in Excel, use the "/" or "-" characters. To enter a time, use the ":" (colon). You can also enter a date and a time in one cell.

  • Year, Month, Day
  • Date Function
  • Current Date & Time - NOW()
  • Hour, Min, Sec
  • Time Function

Text: Excel has many functions to offer Excel when it comes to manipulating text strings.

  • Join Strings
  • Left
  • Right
  • Mid
  • Len
  • Find
  • Substitute
  • Text to Columns
  • Lower/Upper Case
  • Remove Unwanted Characters
  • Compare Text
  • Offset

Lookup & Reference: Learn all about Excel's lookup & reference functions such as the VLOOKUP, HLOOKUP, MATCH, INDEX and CHOOSE function.

  • Vlookup
  • Hlookup
  • Match
  • Index
  • Choose

Statistical: An overview of some very useful statistical functions in Excel.

  • Average
  • Averageif
  • Median
  • Mode
  • Standard Deviation - STDEV()
  • Min
  • Max
  • Large
  • Small
  • Random Numbers
  • Rank

Round: This chapter illustrates three functions to round numbers in Excel. The ROUND, ROUNDUP and ROUNDDOWN function.

  • Round
  • RoundUp
  • RoundDown

Formula Errors: This chapter teaches you how to deal with some common formula errors in Excel.

  • ##### error
  • #NAME? error
  • #VALUE! Error
  • #DIV/0! Error
  • #REF! error
  • IfError
  • IsError
  • Circular Reference
  • Formula Auditing

Data Analysis

Sort: You can sort your Excel data on one column or multiple columns. You can sort in ascending or descending order.

  • One Column
  • Multiple Columns
  • Sort by Color
  • Reverse List

Filter: Filter your Excel data if you only want to display records that meet certain criteria.

  • Number and Text Filters
  • Date Filters
  • Remove Duplicates
  • Outlining Data

Conditonal Formatting: Conditional formatting in Excel enables you to highlight cells with a certain color, depending on the cell's value.

  • Highlight Cells Rules
  • Data Bars
  • Color Scales
  • Icon Sets
  • Find Duplicates
  • New Rule
  • Shade Alternate Rows
  • Compare Two Lists
  • Conflicting Rules
  • Checklist

Charts: A simple Excel chart can say more than a sheet full of numbers. As you'll see, creating charts is very easy.

  • Create a Chart
  • Change Chart Type
  • Switch Row/Column
  • Chart Title
  • Legend Position
  • Data Labels
  • Types of Chart

Pivot Tables: Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract the significance from a large, detailed data set.

  • Insert a Pivot Table
  • Drag fields
  • Sort
  • Filter
  • Change Summary Calculation
  • Two-dimensional Pivot Table
  • Multi-level Pivot Table
  • Frequency Distribution
  • Calculated Field/Item
  • Update Pivot Table
  • Slicers

Tables: Tables allow you to analyze your data in Excel quickly and easily.

  • Insert a Table
  • Sort a Table
  • Filter a Table
  • Total Row

Macro: Introduction to macro.