ADVANCED EXCEL

ADVANCED EXCEL

 

TRAINING MATERIAL OUTLINE:

A.    Introduction to MS Excel and Understanding Basic Working with it

  1. Introduction to MS Excel,
  2. Quick Review on MS Excel Options,
  3. Ribbon,Worksheets and ToolbarDifference Between Excel 2003, 2007, 2010 and 2013
  4. Saving Excel File as CSV,
  5. Macro Enable Sheet, PDF and Older Versions
  6. Using Excel Shortcuts with FullList of Excel Shortcuts
  7. Copy, Cut, Paste, Hide, Unhide, Delete and Link the Data in Rows, Columns and Sheets
  8. Using Paste Special Options
  9. Formatting Cells, Rows, Columns and Sheets
  10. Protecting & Unprotecting Cells, Rows, Columns and Sheets with or without Password
  11. Page Layout, Themes, Background and Printer Properties
  12. Inserting Pictures, Hyperlinks, Header/Footers, Shapes and Other Objects in Worksheets

B.     Working with Formulas/Functions

  1. Lookup and Reference Functions:
  2. VLOOKUP, HLOOKUP,  INDEX, ADDRESS, MATCH, OFFSET, TRANSPOSE etc
  3. Logical Function:IF / ELSE, AND, OR, NOT, TRUE, NESTED IF/ELSE etc
  4. Database Functions:DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, DVARP etc
  5. Date and Time Functions: DATE, DATEVALUE, DAY, DAY360, SECOND, MINUTES, HOURS, NOW, TODAY, MONTH, YEAR,YEARFRAC, TIME, WEEKDAY, WORKDAY etc
  6. Information Functions:CELL, ERROR.TYPE, INFO, ISBLANK, ISERR, ISERROR, ISEVEN, ISLOGICAL, ISNA, ISNONTEXT,ISNUMBER, ISREF, ISTEXT, TYPE etc
  7. Math and Trigonometry Functions: RAND, ROUND, CEILING, FLOOR, INT, LCM, MOD, EVEN, SUMIF, SUMIFS etc
  8. Statistical Functions: AVEDEV, AVERAGE, AVERAGEA, AVERAGEIF, COUNT, COUNTA, COUNTBLANK,COUNTIF,FORECAST, MAX, MAXA,MIN, MINA, STDEVA etc
  9. Text Functions: LEFT, RIGHT, TEXT, TRIM, MID, LOWER, UPPER, PROPER, REPLACE, REPT, FIND, SEARCH, SUBSTITUTE,TRIM, TRUNC, CONVERT, CONCATENATE, DOLLAR etc

C.    Conditional Formatting

  1. Using Conditional Formatting
  2. Using Conditional Formatting with Multiple Cell Rules
  3. Using Color Scales and Icon Sets in Conditional Formatting
  4. Creating New Rules and Managing Existing Rules

D.    Data Sorting and Filtering

  1. Sorting Data by Values, Colors, etc
  2. Using Filters to Sort Data
  3. Advance Filtering Options

E.     Pivot Tables

  1. Creating Pivot Tables
  2. Using Pivot Table Options
  3. Changing and Updating Data Range
  4. Formatting Pivot Table and Making Dynamic Pivot Tables

F.      Pivot Charts

  1. Creating Pivot Charts
  2. Types of Pivot Charts and Their Usage
  3. Formatting Pivot Charts and Making Dynamic Pivot Charts

G.    VBA Macro

  1. Introduction to VBA Macro
  2. Recording Macro & Understanding Code Behind
  3. Editing, Writing VBA Code and Saving as Macro or Add-In
  4. Adding Add-Ins in Excel

H.     Case Study: Sales & Financial Reporting

  1. Creating Sales and Financial Reporting
  2. Sales & Financial Data Visualization using Dashboard
  3. Creating Custom Application for Sales and Reporting

 

RECOMMENDATION FOR PARTICIPANT: Participant must bring Laptop for practice sessions

 

VENUE :   Kagum Group Hotel Bandung (Golden Flower, Banana Inn, Serela, Gino Feruci), Amaroossa Hotel, Noor Hotel, Grand Setiabudi Hotel, dll

 

TRAINING DURATION :   4 days

 

TRAINING TIME :  

  1. Januari 2024Februari 2024Maret 2024April 2024
    2 – 5 Januari 20245 – 8 Februari 20244 – 7 Maret 20241 – 4 April 2024
    8 – 11 Januari 202412 – 15 Februari 202412 – 15 Maret 202422 – 25 April 2024
    15 – 18 Januari 202419 – 22 Februari 202418 – 21 Maret 202429 April – 2 Mei 2024
    22 – 25 Januari 202426 – 29 Februari 202425 – 28 Maret 2024
    29 Jan – 1 Feb 2024
    Mei 2024Juni 2024Juli 2024Agustus 2024
    6 – 9 Mei 20243 – 6 Juni 20241 – 4 Juli 20245 – 8 Agustus 2024
    13 – 16 Mei 202410 – 13 Juni 20248 – 11 Juli 202412 – 15 Agustus 2024
    20 – 23 Mei 202419 – 22 Juni 202415 – 18 Juli 202419 – 22 Agustus 2024
    27 – 30 Mei 202424 – 27 Juni 202422 – 25 Juli 202426 – 29 Agustus 2024
    29 Juli – 1 Agus 2024
    September 2024Oktober 2024November 2024Desember 2024
    2 – 5 September 20241 – 4 Oktober 20244 – 7 November 20242 – 5 Desember 2024
    9 – 12 September 20247 – 10 Oktober 202411 – 14 November 20249 – 12 Desember 2024
    16 – 19 September 202414 – 17 Oktober 202418 – 21 November 202416 – 19 Desember 2024
    23 – 26 September 202421 – 24 Oktober 202425 – 28 November 2024
    28 – 31 Oktober 2024

 

INVESTATION PRICE/PERSON :

  1. Rp. 8.500.000/peserta (bayar penuh)  atau
  2. Rp. 8.250.000/peserta (early bird, yang membayar 1 minggu sebelum training)  atau
  3. Rp. 7.950.000/peserta (peserta bergroup yang terdiri dari 3 peserta atau lebih dari 1 perusahaan yang sama)

 

FACILITIES FOR PARTICIPANTS:

  1. Modul Training
  2. Flash Disk berisi materi training
  3. Sertifikat
  4. ATK: NoteBook dan Ballpoint
  5. T-Shirt
  6. Ransel
  7. Foto Training
  8. Ruang Training dengan fasilitas Full AC dan multimedia
  9. Makan siang dan 2 kali coffeebreak
  10. Instruktur yang Qualified