bdjobs.com :: Largest Job Site in Bangladesh

Microsoft Excel 2007/2010 in Depth for Beginner to Advanced

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.

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
Excel Concepts
Understanding Ribbons, QAT, Toolbars , Formula Bar and Name Box
Concepts of Workbook, Worksheet, Row, Column, Cell, Range Excel Customization Options
Common and Special Operations

Worksheet (Insert, Modify, Move, Copy, Delete, Rename, Hide/Unhide)
Rows, Columns (Insert, Copy, Cut, Delete, Hide/Unhide)
Cells and Range (Select, Copy, Cut, Paste, Delete)
Convert worksheets into PDF file
Paste Special tricks by Values, Formulas, Validation and Transpose
Print multiple or all worksheets at once
Copy worksheets in smartest way
Search for a value in the whole workbook
Keyboard Short Cuts
Time saving Shortcuts with Cheat sheet
Cell References, Formatting & Styles
Column and Row locking with Relative (A1) and Absolute($A$1) cell references
Managing Worksheet and Workbook references in formulas
Number formatting for Decimal, Percentage, Thousand
Separator and Currency
Auto increase cell height using Text Wrap
Date, Time custom formatting with full Day Name and Month Name

Day 2:

Conditional Formatting
Mark Duplicate Data using Conditional Formatting
Mark Unique values using Conditional Formatting
Compare data using Data Bar, Color Scale and Icon Set
Instantly change cell color if cell value is changed
Clear or edit existing conditional formatting from cells or sheet
Easily identify Top 10 or Bottom 10 items

Sort, Filter and Advanced Filter
Sort numbers and words with single or multiple columns(level)
Sort by cell color
Remove unwanted blank or error rows using sort
Sorting horizontally from Left to Right
Add or remove Filter Bar instantly
Text and Number filters with Fuzzy Match, Top 10 and above average items
Custom filters with Wildcards and logical AND-OR
Date filtering with Days, Weeks, Months, Quarters and Years
Filter by cell Background color or Font color
Copying filtered data ONLY to a different location
Filter by cell Background color or Font color
Extract Unique Records using Advanced Filter

Views and Comments
Freeze top rows or left columns while scrolling data
Arrange open workbooks as Titled, Horizontal, Vertical, Cascade
Split a worksheet in multiple view areas
Open multiple workbooks at once using Workspace
Insert, Edit, Resize and Hide Comments
Permanently display Comments

Day 3:

Charts and Trend lines
Working with Chart Components (Axis, Categories, Series, Legend)
Resizing, Moving and Copying Charts
Formatting and Styling Charts with 2D, 3D and Round effect
Using multiple combination chart types in a single chart Solving Yearly Chart Problem
Selecting the appropriate Chart Type and Format
Creating Column, Line, Pie, Bar, Area and Scatter Charts
Creating Radar, Bubble and Doughnut Charts
Dynamically update or link a Chart Title text
Copying a Chart Format to other charts
Dual Axis chart (Primary & Secondary Axis)
Dynamically filter Charts using Slicers
Creating a Chart ONLY Sheet
Chart Trend lines (Linear, Exponential, Polynomial and Moving Avg)

Table and Named Range
Using Table as dynamic data source for dropdown list, pivot or chart
Understanding Table Names & Formula Nomenclature
Table Formatting & Styling
Convert table data back to normal data
Auto insert formulas in a newly entered row
Naming a cell or range of cells
Easy to understand Formulas using Named Range
Define multiple Names at once from a Selection
Creating Worksheet Level Name (Local Scope) only

Day 4:

Grouping and Subtotal
Grouping Rows or Columns for quick Expand(Unhide) or Collapse(Hide)
Subtotal data using multiple levels
Navigate Subtotal Levels with Expand or Collapse
Copying or Formatting visible subtotal rows ONLY
Clearing existing Subtotals from worksheet

Pivot-table and Pivot Chart
Arranging Pivot Fields in Report Layouts (Row Label, Column Label, Values)
Grouping and comparing Dates by Month, Year, Quarter and Days
Working with Report Filter options
Subtotalling and Counting data using pivot table
Analyzing Difference, Growth and % of Total with pivot
Working with Subtotal, Row Total and Grand Total options
Pivot Date filtering with Days, Weeks, Months, Quarters and Years
Filtering of Top 10 or Bottom 10 items
Inserting Formulas in pivot using calculated fields
Editing existing calculated fields
Add new data to pivot data source and auto update pivot accordingly
Range Grouping of Integer and Decimal numbers
Range Grouping of weekdays
Custom Grouping of pivot Text data
Removing a pivot tables from a worksheet
Creating more copies of pivots from an existing pivot
Moving a pivot table to existing or new sheet
Create multiple pivots from a single data source
Inserting Sections between pivot data
Sorting pivot data by simply typing
Solving the Report Filter values sorting problem
Pivot table Filter Hack to add more filters
Generate multiple sub-report pages from master report
Working with PivotTable Slicer
Handling Empty Cells and Error values
Removing the blank word from pivot
Consolidate or combine a single pivot from multiple data source
Solving GetPivotData issue on cells
Changing pivot's existing data source
Quickly get Unique values from a list using Pivot
Using IF Condition inside pivot Calculated Fields
Working with dynamic Pivot Charts
Data Drilling or Mining in pivot
Formatting Number, Date and Text
Custom formatting in pivot
Solving pivot Date displaying problem
Time saving tips for pivots with large data source
Working with Pivot-table Options

Day 5:

Formula and Functions
Using Formula Bar for writing formulas
Entering formulas directly in cells
Using the Insert Function dialogue box
Finding the appropriate function for a task
Understanding Function TOOLTIP Architecture

Functions (Math and Statistics)
Adding all numbers in a range using SUM Function
Counting total numbers or texts with COUNT-COUNTA function
Finding smallest and largest values with MIN- MAX function
Finding Nth smallest and largest values
Summing data with multi conditions and criteria using SUMIF-SUMIFS
Rounding decimal numbers
Generate random numbers between a range
Sorting values with RANK function
Separating the integer portion from a decimal number
Converting one measurement systems to different one
Average or Mean of numbers with AVERAGE function
Ignoring ZERO values while averaging
Averaging data with multi conditions and criteria
Finding statistical Mode and Median

Functions (Text and String)
Convert texts to UPPER, LOWER or PROPER case
Technique to add NEW LINEs in cells that have formulas inside
Join multiple cell values with CONCATENATE - &
Break or extract a portion of text with LEFT-MID-RIGHT function
Search for a portion of text with FIND-SEARCH
Replace a portion of existing texts with a new text
Converting text to numbers quickly
Finding total number of characters in a text

Day 6:
Functions (Logical)
IF function to show certain results if specific conditions are met with Nested IFs with multiple If-Else conditions and criteria
Join multiple criteria with logical AND-OR-NOT
Check whether a value is Number or Text
Check whether a cell is Blank or Empty

Functions (Lookup)
Lookup vertically using VLOOKUP
Powerful VLOOKUP instead of Nested IF
Lookup horizontally using HLOOKUP
Single Criteria based LOOKUP
Multiple Criteria based LOOKUP
Multi level lookup using Nested VLOOKUPs
Advanced Lookup using INDEX- MATCH function
Technique for Two-way lookup (vertical-horizontal) at once
Inline lookup using CHOOSE function
Using OFFSET function
INDIRECT function to return a reference indicated by a text value

Day 7:

Functions (FINANCE)
Calculate Future value of an investment with FV function
Calculate Present value of an investment with PV function
Calculate EMI or Periodic Payment with PMT function
Net Present Value with NPV function
Net Present Value with XNPV function

Functions (Date/Time)
Construct a Date from partial Day, Month and Year values
Construct a Date from a text format
Automatically update and show today's Date
Automatically show present Time
Calculate Last Day of a Month
Get number of Days in a Month
Convert month name into month number
Convert month number into month name
Calculate Addition and Difference between two Dates
Show the Weekday Name of a Date
Show age by Year, Month and Day
For a Date get the Quarter number, Quarter End Date and Half Year End Date
Find the latest and oldest date from a date list
Understanding Excel Time formatting architecture
Calculate Addition and Difference between two Times

Day 8:

Protection and Securities
Password Protect Workbook for Opening & Modification
Backup Workbook Automatically
Disallow Worksheet Creation, Renaming, Hiding, Deletion
Password Protect Worksheets and lock Ranges from modification
Create hidden formulas
Password Protect Cells or Ranges

Printing and Graphics
Print Entire Workbook or Selected Sheets or Selected Range or Charts
Print repetitive titles on each page
Header-Footer with Page Number, Current Date, File Name and Location
Print whole worksheet in a single page
Page Setup, Insert Page Break and Set Print Area
Working with Smart Arts, Shapes and Images

Error Fixing and Auditing
Formula Auditing, Error checking and Evaluating
Solving Errors (#NAME?, #N/A, #REF!, #VALUE!, #NUM!, #DIV/0! )
Check whether a formula generates error
Removing Error message from a formula result

Day 9:

What-If Analysis Tools
Goal Seek to solve an financial equation
Scenario and Sensitivity Analysis
Using Solver for finding optimal solutions

Data Validation and Dropdown List
Create dynamic Dropdown selection list for cell inputs
Allow only valid Numbers or Date input in a cell
Apply Data Validation with error alert
Show tooltips while mouse is over a cell
Quickly identify Invalid data in a range

Data Consolidation, Export-Import and Sharing
Consolidate data from multiple data sources
Data Import from various files (CSV, Txt, Access)
Data Export to various formats (CSV, Txt, Word, PowerPoint)
Data Import from WEB using Web Query
Workbook Sharing to allow multiple users to edit excel at the same time
Keep record of each modification by users in Shared Workbook

Day 10:

Data Fix & Cleanup
Find and Remove Duplicate Values or Rows
Remove extra outside and inside spaces from texts
Remove unwanted characters from texts
Find invalid Dates and fix
Extract Date portion from a Text
Solving problem where formula doesn’t calculate
Convert Text into Number
Convert Number to Text

Dashboard, Forecast and Mail Merge
Preparing Dynamic Dashboards
Working with Regression or Forecasting
Mail Merge Letters using Excel List and Word Template

Business Cases
Case - Sales Analysis
Case - Future Value Analysis
Case - Credit Analysis

Day 11:
Macros
Difference between Macro and VBA
Displaying Macro tools by activating Developer Tab Recording a Macro
Modifying a recorded Macro
Running Macro using a Shortcut Key
Running Macro using Buttons
Running a Macro on any file using PERSONAL.XLSB
Managing and customizing Macro security

Techniques
Identify and select all Blank cells at once
Delete Blank rows from a list
Powerful Auto Fill Handle techniques for Numbers and Dates
Custom Auto Fill Handle list creation
Show grey background at right or bottom side of worksheet
Using powerful Array Formulas
Create Hyperlinks to open a different Sheet, Workbook or File
Clear only Formats or Only Contents
Remove all comments and hyperlinks instantly
Remove all unwanted/garbage objects at once
Break cell contents into separate columns by Text To Column
Sort numbers automatically using formulas
Create auto increase Serial Numbers using formulas
Technique for Running Total or Cumulative Total
Count number of Blanks using formula
Using Template to reuse an excel file unlimited times

Day 12:

Advanced Techniques
Matching data sets techniques
Comparing data sets techniques
Merging data sets techniques
Finding Un-matched data sets techniques
Powerful pivot with date range criteria selector
Embed an external files (PDF, Excel) with icon into excel sheet

Back to Workshop Home