bdjobs.com :: Largest Job Site in Bangladesh

Microsoft Excel 2007-2010 – Intermediate

About Workshop

Microsoft Excel is most widely used productivity tool in today’s corporate world and education sector. Day to day corporate analysis, reporting, tools and applications are prepared with Excel. It will be an advantage for course attendees to learn practical and advanced usages of Excel for office and academic activities. Excel allows us to accomplish a task in multiple ways which will be shown to attendees with Pros and Cons of the methods.

How participants will benefit after the course: HANDS ON EXPERIENCES. Most widely practiced Excel topics are covered in the course syllabus with practical exercises. Participants will do the live exercises along with the trainer to learn faster and effectively. Learning the required Excel functionalities in course will ensure participants high performance in their career and academics.

Workshop topics to be covered:

DAY 1:

BREAKING THE EXCEL FEAR

Special Operations

• Paste Special
o Values
o Transpose

• Auto Fill Options and Edit Series List

Keyboard Short Cuts
• CTRL Combination Keys
• ALT Combination Keys
• SHIFT Combination Keys
• Function Combination Keys

Cell References
• Relative cell references (A1)
• Absolute cell references ($A$1)
• Mixed Row-Column Lock cell references
• Worksheet cell references
• Workbook cell references
• Shortcuts

Formatting, Style & Custom Formats
• Create, Copy and Clear Formats
• Cell Formatting

• Number Formatting
o Decimal
o Percentage

• Text Formatting
o Font and Size
o Alignment and Direction
o Wrap and Merge
o Center Across Selection

• Date Formatting
• Custom Formats
• Shortcuts

Conditional Formatting - Changing the Appearance of Data Based on Its Value
• Highlight Duplicate Values
• Highlight Unique Values
• Highlight greater than average Values
• Highlight Top 10% Values
• Data Bars
• Icon Sets
• Create, Clear, Manage & Customize Rules

Table
• Convert To Table
• Dynamic Range & Naming
• Add, delete records
• Table Names & Formula Nomenclature
• Table Formatting & Styling
• Convert Back to Range
• Shortcuts

Sort
• Sorting Numbers (Smallest to Largest)
• Sorting Words A to Z in Single Column
• Sorting Words A to Z in Multiple Columns
• Sort by Color

Filter
• Filter with One Criteria
• Remove Applied Filtering
• Filtering on Cell Fill Color
• Filtering Below Average Values
• Filtering Texts
• Filtering Dates
• Shortcuts

Find & Select
• Find and Replace within Worksheet
• Find and Replace within Workbook

Charts
• Categories & Series

• Chart Types
o Pie Charts
o Column Charts
o Bar Charts
o Line Charts
o Area Charts
o Multiple Chart Type

• Creating & Formatting Charts
• Adding Chart Data Labels
• Changing Chart Styles

Pivot Table – Analyze Data Dynamically
• Create Pivot Table with Row Labels, Column Labels & Values Area
• Formatting & Style
• Filtering a Field
• Sorting by Fields
• Subtotals and Grand Totals
• Select Items, Totals
• Show/Hide Items
• Show/Hide Field List, Buttons and Field Headers
• Select, Remove Fields
• Report Filter Area

• Summary Functions
o SUM
o COUNT
o AVERAGE
o MIN-MAX

• Group Dates By Days, Week, Month, Quarter, Year
• Percentage Total & Difference
• Select, Clear and Move Pivot Table
• Change Source Data and Refresh
• Dynamic Pivot Chart
• Shortcuts

DAY 2

Functions (Math)
• SUM Function – Adding all numbers in a range
• COUNT-COUNTA Function – Counting total number of words and numbers.
• MIN- MAX Function – Finding smallest and largest values
• SUMIF-SUMIFS Function - Summarizing data with conditions

Functions (Statistical)
• AVERAGE Function – Averaging numbers

Functions (Text)
• TRIM, UPPER, LOWER, PROPER, CLEAN
• CONCATENATE, &, CHAR(10)
• LEFT, MID, RIGHT

Functions (Logical)
• IF & Nested IFs
• AND-OR-NOT

Functions (Lookup)

• VLOOKUP, HLOOKUP
o Exact lookup
o Partial lookup

• INDEX- MATCH
o INDEX Function
o MATCH Function

Functions (Date/Time)
• DATE
• TODAY
• NOW
• Shortcuts

Protection & Securities
• Password Protect Workbook for Opening & Modification
• Backup Workbook Automatically
• Disallow Worksheet Creation, Renaming, Hiding, Deletion
• Password Protect Worksheets

Errors & Fix
• Pound(#) Sign Error
• #NAME? Error
• #N/A Error
• #REF! Error
• #VALUE! Error
• #NUM! Error
• #DIV/0! Error
• Circular Cell Reference Error
• #NULL! Error

Data Validation
• Create Validation List
• Show Input Message
• Show Error Alert

Consolidation
• Consolidating Multiple Sets of Data into a Single Workbook
• Create Links to Source Data

Data Import-Export
• Importing From Different Open Excel Workbook
• Importing From Comma-Separated or Tab-Separated Data
• Exporting to MS Word
• Exporting to MS PowerPoint
• Exporting to Comma-Separated or Tab-Separated Data

Data Fix & Cleanup
• Fix Number Problems
• Fix Date Problems
• Fix Text Problems
• Find & Remove Duplicates

Back to Workshop Home