Mehul Shah

Excel Programs:

MS Excel 2010 - Basic Level (Click here for Program Contents)

MS Excel 2010 - Advance Level (Click here for Program Contents)


Program Contents (MS Excel 2010 - Basic Level):

Navigating with Excel 2010 Screen


Parts of the Excel 2010
Hide / Unhide Ribbon
Brief Description about a command
Contextual Tab
Types of command on the Ribbon
Accessing the Ribbon using keyboard
Using Shortcut Menus
Default Commands on Quick Access Toolbar
Using Undo and Redo Commands on QAT
Using the Task Pane
Customizing Quick Access Toolbar
Using tabbed dialog boxes

Working with Workbooks


Creating a new Workbook
Saving a Workbook
Assign items such as author, title, subject etc to the file
Opening an Existing Workbook
Open a workbook as Read Only / Copy
Closing Workbooks
Using Favorite Links for opening a file
Make important files always appear on the recent file list
Remove a file / all files from recent list

Working with Worksheets


Change default number of worksheets
Inserting a worksheet
Deleting a worksheet
Rename a worksheet
Inserting colour in a worksheet tab
Moving around, rearranging & copying worksheets
Hide / Unhide worksheets

Entering / Editing data in a worksheet


Move around a worksheet
Entering text, number, date
Entering current date and time
Displaying more text in the formula bar
Enter text in a new line within same cell
Auto Fill
Selecting Ranges
Selecting complete rows / columns
Copy & Paste
Find and Replace
Cell Height / Width / Autofit
Insert / Delete  Cells / Rows / Columns
Hide / Unhide - rows / columns
Working with Comment
Spelling check

Working with multiple parts of the same worksheet


View multiple windows simultaneously
Split window
Freeze panes
Using Workspace Files

Working with Pictures


Adding a picture
Adujst brightness and contrast
Working with picture colour
Working with picture size in kb/mb
Change picture while keeping existing picture settings
Reset all picture formatting
Working with picture styles / Border / Effects / Layouts
Bring one picture on top of another / send it back
Show / hide individual pictures
Hide all the shapes and objects on the screen
Group pictures / shapes
Rotate picture
Crop picture
Adjust hight / width / size of a picture
Working with picture positioning and size while inserting / deleting cells
Set whether to print or not picture
Lock picture
Insert clip art
Insert shapes
Adding text to a shape
Insert smartart

Working with Charts / Graphs


Create a Chart / Graph
Column Charts
Bar Charts
Line Charts
Area Charts
Pie Charts
XY (Scatter) Charts
Stock Charts
Change chart type
Switch Row/Column
Chart layouts
Chart styles
Insert Shapes
Insert text box
Working with chart / axis title
Working with legends
Show data labels
Show data table
Working with axis / gridlines
Remove Horizonal Axis Label
Display Values in Thousands / Millions / Billions
Working with Gridlines
Adding a trendline to the chart
Adding error bars in the chart
Formating Chart Elements
Set min / max / major / minor units for value axes
Set Major / Minor Tick Mark
Set Axis Label
Formating Shapes

Formatting Worksheet


Worksheet Formatting
General Number Format
Format Cells for Numeric Values
Format Cells for Date Values
Format Cells for Time Values
Format Cells for Percentage Values
Format Cells for Fraction Values
Format Cells for Scientific Values
Format Cells for Text Values
Format cell for USA Zip Code, Phone number & Social Security Number
Horizontal & Vertical Alignment of Text Within a Cell
Indent Text Within a Cell
Wrap Text Within a Cell
Shrink font size to fit text within a cell
Merge Cells
Displaying Text at an Angle Within a Cell
Working with Fonts
Format text for strikthroug, superscript and subscript
Working with Borders
Fill Cell Background with Colours / Effects / Patterns
Quick Formatting using Cell Styles
Adding a Background Image
Copy & Paste only formatting without value
Automatically Highlight Cells based on Values / Text / Date
Automatically Highlight Cells having Duplicate Values
Highlight cells for Top 10 / Bottom 10 / Above or Below Average Value
Generating Bar Charts within Cells based on Values
Generating Icon Indicators within Cells based on Values

Printing Worksheet


Printing with One Click
Workbook Views
Normal View
Page Layout View
Page Break View
Choosing your printer
Specifying what you want to print
Printing multiple copies of a report
Changing page orientation
Scaling printed output
Specifying paper size
Adjusting page margins
Printing row and column titles on every pages
Printing row and column headers
Printing cell gridlines
Change the print resolution / quality 
Printing Comments
Printing Errors
Adjust Page Order
Inserting a page break
Inserting a Watermark
Adding a Header / Footer to Your Reports
Change page no. starts from
Copying Page Setup Settings across Sheets
Preventing Certain Cells from Being Printed
Creating Custom Views

Working with Templates


Exploring Excel Templates
Modifying a template
Custom Excel Templates
Default workbook template
Default worksheet template
Custom workbook templates

Formulas & Functions


Entering Formulas
Operators Used in Formulas
Operator Precedence in Formulas
Parentheses and the Order of Operations
Function Arguments
Copying Formulas
Inserting Functions into Formulas
Quick Count & Sum
Count total number of Cells
Count Blank Cells
Count Non-Blank Cells
Count Numeric Cells
COUNTIF Function
COUNTIFS Function

Data Filtering & Analysis


Working with Table
Analysing data with Pivot table
What-if Analysis
  Manual what-if analysis
Reverse what-if analysis
  Determining the input value for a specific result value

Top


Program Contents (MS Excel 2010 - Advance Level):

Working with Workbooks


Changing the default file format for saving files
Open files automatically when starting excel
Auto Save & Recover wrokbooks

Entering / Editing data in a worksheet


Selecting noncontiguous ranges
Enter same text in a range without using copy & paste
Simultaneously enter data / format cells in more than one work sheets
Avoid typing frequently used long words / sentences
Moving the cell pointer after entering data
Simplify data entry using form
Copying to Adjacent Cell
Copy range to other sheet
Paste from last 24 copied data
Pasting in special ways
Selecting special types of cells
Selecting cells by searching
Working with Date

Working with Charts / Graphs


Chart title as a link to a cell
Handling missing data
Set from where one axes should cross another axes
Creating a combination chart

Formatting Worksheet


Using Custom Number Format

Formulas & Functions


Referencing cells in other worksheets 
Referencing cells in other workbooks
Linking and Consolidating Worksheets
Working with names
View change in destination cells (in other sheets) while changing the source cells
AGGREGATE, NETWORKDAYS.INTL, WORKDAY.INTL (New Functions in Excel 2010)
VLOOKUP Function
HLOOKUP Function
LOOKUP Function
MATCH Function
INDEX Function
Working with look up functions for looking up values to the left
Working with array formulas

Data Filtering & Analysis


Advanced Filter
Analysing data with Pivot table
What-if Analysis
  Effect of change in one variable to multiple variables
  Effect of change in two variable to single variables
  Effect of change in multiple variables to multiple variables (creating scenarios)
Reverse what-if analysis
  Determining various input values for a specific result value (can also specify constraints) 
  Solving linear equations

Protecting your Work


Mark a Workbook as Final
Encrypt a Workbook with Password
Maintaining a Backup of a Workbook
Preventing worksheet actions
Protecting certain cells of a worksheet

Advanced excel features


More than one person working on a single file
Updating equity portfolio every miniute with live prices automatically
Restrict user to enter certain values / select from the drop down


Top