ADVANCE - EXCEL

  • Curriculum
  • OVERVIEW
DAY 1 & 2 : Introduction to Excel
  • Short Keys
  • Formatting (Applying Borders, Colors and Font styles)
  • Conversion of Formats
  • Merging and Wrapping the text
  • Conditional Formatting
  • Format as Table
  • Inserting and deletion of Rows, Columns and Sheets
  • Row height and Column width
  • Formulae based Conditional Formatting
DAY 3 &4: Tables, Illustrations and charts
  • Hide and Unhide of Rows, Columns and Sheet
  • Protecting sheet and Workbook
  • Move or Copy, Rename sheet and Tab Color
  • Filling series of Numbers and Dates
  • Sorting and Filtering
  • Pivot Tables
  • Usage of Formulas in Pivot Tables
  • Inserting pictures, Clip art, Text box, Shapes and Smart Art
  • Usage of Charts (Column, Pie, Bar, Line)
DAY 5 & 6: Proofing, Comments and Changes
  • Inserting Hyperlinks
  • Linking sheets, Cells, Workbook, Range and Mail
  • Header-Footer, Word Art
  • Inserting Objects
  • Protect Sheet
  • Protect Workbook
  • Hiding Formulas
  • Track Changes (Highlight, Accept and Reject Changes)
  • Inserting and Editing Comments
DAY 7 & 8 : Page setup, Scale to fit and arrange
  • Inserting Comments and Spell check
  • Freeze Panes (Rows and Columns)
  • Save workspace, Switch windows and Split window
  • Arranging the window (Vertically and Horizontally)
  • Page Layout, Gridlines and Formula bar
  • Paper margins, Gridlines and Paper size
  • Page breaks, Applying background and Print titles
  • Row repeat at top and print preview
DAY 9 & 10: Connections and Data Tools
  • Data Importing (From Access, Text)
  • Advance Sorting and Filtering
  • Text to Columns
  • Removing Duplicates
  • Data Validation
  • List box, Formula based restrictions
  • Customization of error alert and Input box
  • Highlighting the Invalid data
DAY 11 & 12: Data Tools and Outline
  • Data Consolidation
  • Scenario Manager
  • Goal Seek
  • Data Table
  • Group and Ungroup
  • Adding subtotals to the list
  • Defining name to the range
  • Name manger editing
  • Trace precedents/Trace Dependents
  • Evaluate Formulas
DAY 13 & 14: Text, Arithmetical Functions
  • UPPER, LOWER, PROPER, LEN, LEFT, RIGHT, MID, FIND, TRIM, CLEAN, CHAR, CODE, CONCATENATE, SUBSTITUTE, EXACT, REPT, REPLACE, SEARCH, VALUE and TEXT.
  • ABS, ROUND, SUM, SUMIF, SUMIFS, SUMPRODUCT, SUBTOTAL and RANDBETWEEN Activities based on the above functions
DAY 15 & 16 &17: Date & Time and Logical Functions
  • DATEDIF, DATE, TODAY, NOW, WEEKDAY, MONTH, YEAR, YEARFRAC, NETWORKDAYS, DAYS360, MINUTE, HOUR, SECOND, WEEKNUM, EDATE and EOMONTH.
  • IF, AND, OR, NOT, TRUE, FALSE, IFERROR Activities based on the above functions
DAY 18 & 19: Statistical and Information Function
  • AVERAGE, AVERAGEIF, AVERAGEIFS, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, LARGE, SMALL, MAX, MIN, RANK, ROWS, ROW, COLUMN and COLUMNS
  • ISBLANK, ISERROR, ISNUMBER, ISTEXT and ISNA
DAY 19 & 20 & 21: Lookup and References
  • VLOOKUP, HLOOKUP, INDEX, MATCH, OFFSET, CHOOSE and INDIRECT.
  • VLOOKUP with MATCH, IFERROR and CHOOSE.
  • Combination of Formulas
  • Array Formulas
  • VLOOKUP and COLUMN
  • VLOOKUP with IF and VLOOKUP with AND
  • Activities based on the above functions
DAY 22 & 23 & 24 & 25: Activities
  • INDEX and MATCH
  • OFFSET function
  • SUM and CHOOSE
  • Sorting the data using formula
  • DOUBLE LOOKUPS
  • RIGHT LOOKUPS
  • LEFT LOOKUPS
  • Multiple INDEX
DAY 26 & 27: Macros
  • Working with Macros
  • Run & Edit Macros
  • Using VBA Code to Macros
  • Working with Developer Tools box
  • Creating User forms in Excel
DAY 28 & 29 & 30
  • Excel options & settings
  • String manipuctations
BATCH TIME:
FEES: RS.2500/-
DURATION: 30 DAYS

Advanced Excel is a comprehensive tutorial that provides a good insight into the latest and advanced features available in Microsoft Excel

The online Microsoft Excel 2013 Beginner, Intermediate & Advanced course will provide learners with a core understanding of how to effectively operate and use the Excel spreadsheet software. Whether you need to learn the skills for work, want to fill the gaps in your CV or simply fancy learning something new, this Excel beginner course is perfect to do so.