MS Excel Training Layout with topics

Module 1: Recapping Basics

• • Absolute, Relative References Basic calculations
  • Inserting and deleting sheets
  • Changing the default number of workbook sheets
  • Naming Cell Ranges

Printing and Page Setup

• • Shrink to Fit
  • Adjust page breaks within Page Break Preview
  • Repeat columns and rows
  • Auto and manual scaling
  • Print Grid-lines
  • Save print settings

Module 2: Formatting Workbooks and Templates and managing tables

• Format as Table
• Templates
• Styles
• Paste Special
• Creating Tables
• Naming the Table / Range
• Changing the Table Style
• Creating a Total Row
• Creating a calculated column
• Using Filtering in Table
• Removing the Duplicate Record

Module 3: Sort and Filter Data with Excel

• Sort and filtering (number, color  and text) data
• Custom filtering
• Removing filters from columns
• Conditional Formatting with custom ( formula )

Module 4: Databases

• Sorting Data
• Database Functions
• Sub-totaling
• Outlining and Grouping
• Filtering
• Advanced Filter
• Remove Duplicates
• Data Validation (list, dependent list, decimal, text length, date, whole number)
• Custom Data validation
• Consolidate data
• Text to column

Module 5: The IF Function

• • The Syntax Of IF and nesting the IF Statement
  • Use The AND & OR Operator
  • SUMIF/S, COUNTIF and AVERAGEIF, Subtotal
  • Multiple Criteria Within The Same SUM AVERAGE And COUNT Functions
  • Trim, concatenate, left, right, change case

Module 6: Create Effective Charts

• Present Data Visually
• Inserting Column, Pie chart etc.
• Create an effective chart with Chart Tools- Design, Format, and Layout options
• Adding chart title
• Changing layouts
• Chart styles

• Editing chart data range
• Editing data series
• Changing chart
• Saving chart as template

Module 7: Analyze Data Using PivotTables and Pivot Charts

• Understand PivotTables, Create a PivotTable
• Framework Using the PivotTable and PivotChart
• Create Pivot Chart from pivot Table
• Removing A Pivot Table
• Making Use Of The Report Filter Option
• Sorting A Pivot Tables Columns
• Displaying Values As A Percentage
• Dealing With Empty (NULL) Cells
• Exploring The Additional Pivot Table Options
• Introducing The Slicer Tool
• Managing Your Slices
• Formatting Your Slices
• With date, yearly / quarterly / monthly summarize

Pivot Charts

• • Creating A Pivot Chart
  • Altering Chart Types Formats And Layouts
  • Advanced Layout Control Of A Pivot Chart
  • Filtering A Pivot Chart
  • Hiding Pivot Chart Elements
  • Moving A Pivot Chart Between Sheets

Module 8: Spark lines and Other Useful Functions

Spark lines

• • Creating A Sparkline
  • Change The Design Of Sparklines
  • Dealing with Empty Cells
  • Comparing One Sparkline To Another by Altering Vertical Scale
  • Removing Sparklines From A Sheet

Outlining

• • Outlining Explained
  • Creating An Outline Automatically
  • Creating An Outline Manually
  • Manually Removing Data From An Outline
  • Removing The Outlining From A Worksheet
  • Adjusting A Grouping Created By Automatic Outlining

Custom Views

• • Creating A Custom View Of A Worksheet
  • Changing From One Custom View To Another
  • Editing A Custom View
  • How To Delete A Custom View

Other Useful Functions

• • Welcome To IS Functions
  • Error Checking With ISERROR ISNA And IFERROR
  • The OFFSET Formula Explained
  • Dynamic Named Ranges Using The OFFSET Function
  • Use The INDIRECT Function To Build Dynamic Formulas
  • Dealing With INDIRECT Errors
  • Use Formulas To Determine An Excel Filename And Or Sheet Name

 

Module 9: Auditing and Troubleshooting Formulas

• • Description Of Tracer Arrows
  • Tracing Precedents And Dependents
  • Remove Tracer Arrows
  • Error Checking Using Auditing Tools
  • Step By Step Processing Of Formula To Help With Troubleshooting
  • Utilizing The Watch Window

Module 10: Protecting and Sharing of workbook

• Protecting a workbook with a password
• Allow user to edit ranges
• Protect to appropriate formula or area
• Track changes
• Working with Comments
• Insert Excel Objects and Charts in Word Document and PowerPoint Presentation

Module 11: Performing Lookups

VLOOKUP Explained

• Applied Examples For VLOOKUP
• HLOOKUP Explained
• HLOOKUP In Action
• Index and match
• Concept about Xlookup

Module 12:Perform Calculations with Functions

• Creating Simple Formulas
• Setting up your own formula
• Date and Time Functions and calculation, Financial Functions
• Find future and past dates
• Logical Functions, Lookup and Reference
• Functions Mathematical Functions
• Working with Templates

Module 13 :Analytical Tools for Scenario analysis

• Goal Seeking
• Data Tables
• Solver
• Setting up a scenario and entering values
• Display the scenario values
• Editing the values of a scenario
• Deleting a scenario

• • Merge scenarios from different sheets
  • Forecasting Trends
  • Trend lines in Charts
  • Getting a Summary of all scenarios

Module 14: Values

• PPMT, ipmt, Future Value (EMI tracking )
• DB (fixed declining balance method ( depreciation), SLN ( straight line method ( depreciation )

Module 15: Prediction and forecast

• • Forecast sheet for prediction
  • Forecast linear / ets

 

This offer is valid only for first 25 students. First come first serve basis

 

Prospective Microsoft CEO, 2014

Make your PC/Laptop Run Faster

1. Update the software at least once in two months
2. Install one antivirus, not two
3. Keep your data in separate drive other than C. eg in D or E Drive not in C Drive
   1. Data: Resume, Joke, Games
   2. Pictures
   3. Videos
   4. Work

1. Backup your data regularly in your server or in the cloud
2. Do not put many files in the desktop
3. Make a backup at least every two weeks
4. Do not keep any software that is not being used for more than one month
5. Be safe in internet
6. Do not download or install and run anything you don’t know
7. Use the correct charger for laptop
8. Do not use the laptop on soft cloth or bed.
9. If you are bringing the laptop/desktop for repair or installation, make sure you back up your data.

Everyday an offer

Monday: 15% off in Graphics

Tuesday: 15% off in Multimedia

Wednesday: 15% off in AutoCAD

Thursday: 15% off in Webpage Designing

Friday: 15% off in Children Package

Saturday: 20% off in any package

Sunday: 15% off in Engineering Programs

Call us for some details, 5542983

News on Internet Crime

Will be posted soon

New Year Offer

Register before Dec 31, 2013 and enjoy 40% less in the selected programs

 

Hacked by ./cOLI