LASZTICS Ltd > Services > Sub > Basic and advanced Excel

Basic and advanced Excel

Introduction to EXCEL SPREADSHEET

  • Data entry, Importing, sorting and coding
  • Descriptive analysis – Frequency tables, Custom tables, Charts, Graphs, Cross tabulations
  • Understanding the basic concepts of a spreadsheet, including: • the idea of templates • cells, rows and columns • cell coordinates • entering data into a spreadsheet cell • Setting up labels, including:
  • Setting column widths • aligning data in cells • entering column and row labels •
  • Creating and copying formulas
  • Making changes in a spreadsheet • inserting rows and columns • deleting rows and columns
  • Producing a printed copy of the contents of a spreadsheet file
  • Saving a backup copy of your work
  • Appreciating the power of spreadsheet templates
  • Create a basic worksheet by entering text, values, and formulas.
  • Change the appearance of worksheet data by using a variety of formatting techniques.

Intermediate EXCEL

  • Advanced Excel Shortcuts
    1. Extensive exercise on excel shortcuts
    2. Doing away with the mouse
    3. Mastering the shortcut window
  • Filter and sort Excel data.
  • Create formulas by using some of Excel’s built-in functions.
    1. Upper, Lower, Proper and TRM
    2. Left, Mid, And Right
    3. CONCATENATE & (Ampersand)
    4. CONCATENATE Vs. & (Ampersand)
    5. Roundup, And ROUNDDOWN
    6. VLOOKUP And HLOOKUP
    7. IF
    8. Nested IF
    9. If and AND
    10. SUMIF
    11. Error Values in Excel…
    12. Rand and Rand between
  • Pivot Tables
  • Charts
    1. Ribbon Tour
    2. Creating a chart
    3. Chart Layout Options
    4. Multiple Series within a chart
    5. Modifying Gridlines
  • Filters
    1. Ribbon Tour
    2. Creating a chart
    3. Chart Layout Options
    4. Multiple Series within a chart
    5. Modifying Gridlines
  • Text to Columns (Data Parsing)
    1. UPPER, LOWER, PROPER AND TRIM

Advanced EXCEL

  • Understanding References
    1. Relative, Absolute & Mixed reference
    2. Referencing different workbooks & Merging
    3. Consolidating data, linking cells in different work books
    4. Utilizing Dynamic Named Ranges for writing formulas and functions
  • Data Analysis Report Pivot Tables & Pivot Charts
    1. Analyzing and generating reports of large data using PivotTables.
    2. Creating PivotTables, editing and updating changes in PivotTable.
    3. Using Slicers in Pivot Table
    4. Creating Pivot Chart
  • Data Validations and Protection
  • Financial Analysis and Formulas
    1. Using IRR
    2. Using NPV
  • Budgeting under Scenarios
  • Excel Macros and VBA for Programming
    1. Introduction to Excel Macros
    2. Recording and Running a Macro
    3. Absolute vs Relative
    4. Assigning Buttons to Macros
    5. Assigning Macros to an Auto Shape and to an icon on a Tool bar
    6. Sheet Controls and User Form Controls
    7. Deleting a Macro