bdjobs.com :: Largest Job Site in Bangladesh

Microsoft Excel 2007/2010 for Beginners

About Workshop

Introduction
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 functionality in course will ensure participants high performance in their career and academics.

Workshop topics to be covered:

Session: 1

Excel Concepts
Introducing new features in Excel 2007 and 2010
Understanding Ribbons, QAT, Toolbars , Formula Bar and Name Box
Concepts of Workbook, Worksheet, Row, Column, Cell, Range
Excel Customization Options

Common and Special Operations
Workbook (New, Open, Save, Save As, Close)
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
Smartest and Fastest way to Paste as Values
Copy worksheets in smartest way
Search for a value in the whole workbook
Quickly zoom or zoom out cell text

Keyboard Short Cuts
Time saving Shortcuts with Cheat sheet
Quickly convert to Date, Decimal, Time and Percentage format
Quickly enter Today's Date and Current Time
Show all formulas in a worksheet at once
Fastest way to use SUM function
Instantly display Format Dialog box
Create a column chart on the fly
Go to last or first cell in a column or row
Select data up to last row, last column quickly
Enter values in many cells at once
Repeat an excel command many times
Insert new lines inside a same cell
Select current visible cells in filtered or grouped data
Fast checking for Spelling errors

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
Changing text entry direction in a cell
Solving the Merged Cells Errors
Border formatting with inside, outside, thick, double, line color and style

Session: 2
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
Add or remove Filter Bar instantly
Text and Number filters with Fuzzy Match, Top 10 and above average items
Date filtering with Days, Weeks, Months, Quarters and Years
Filter by cell Background color or Font color

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
Insert, Edit, Resize and Hide Comments
Permanently display Comments

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 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
Naming a cell or range of cells
Easy to understand Formulas using Named Range

PivotTable and PivotChart
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
Subtotaling and Counting data using pivot table
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
Sorting pivot data by simply typing
Handling Empty Cells and Error values
Changing pivot's existing data source
Working with dynamic Pivot Charts
Data Drilling or Mining in pivot
Formatting Number, Date and Text

Session: 3
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
Rounding decimal numbers
Average or Mean of numbers with AVERAGE function

Functions (Text and String)
Convert texts to UPPER, LOWER or PROPER case
Join multiple cell values with CONCATENATE - &
Break or extract a portion of text with LEFT-MID-RIGHT function
Converting text to numbers quickly
Finding total number of characters in a text

Functions (Lookup)
Lookup vertically using VLOOKUP
Lookup horizontally using HLOOKUP
Single Criteria based LOOKUP

Functions (Date/Time)
Construct a Date from partial Day, Month and Year values
Automatically update and show today's Date
Automatically show present Time
Calculate Addition and Difference between two Dates
Understanding Excel Time formatting architecture
Calculate Addition and Difference between two Times

Session: 4
Protection and Securities
Password Protect Workbook for Opening & Modification
Backup Workbook Automatically
Password Protect Worksheets and lock Ranges from modification

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
Solving Errors (#NAME?, #N/A, #REF!, #VALUE!, #NUM!, #DIV/0! )

Data Validation and Dropdown List
Create dynamic Dropdown selection list for cell inputs
Apply Data Validation with error alert

Data Export-Import
Data Import from various files (CSV, Txt, Access)
Data Export to various formats (CSV, Txt, Word, PowerPoint)

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
Solving problem where formula doesn’t calculate
Convert Text into Number

Techniques
Identify and select all Blank cells at once
Delete Blank rows from a list
Powerful Auto Fill Handle techniques for Numbers and Dates
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

Back to Workshop Home