Training syllabus
ADVANCED EXCEL
TRAINING MATERIAL OUTLINE:
A. Introduction to MS Excel and Understanding Basic Working with it
- Introduction to MS Excel,
- Quick Review on MS Excel Options,
- Ribbon,Worksheets and ToolbarDifference Between Excel 2003, 2007, 2010 and 2013
- Saving Excel File as CSV,
- Macro Enable Sheet, PDF and Older Versions
- Using Excel Shortcuts with FullList of Excel Shortcuts
- Copy, Cut, Paste, Hide, Unhide, Delete and Link the Data in Rows, Columns and Sheets
- Using Paste Special Options
- Formatting Cells, Rows, Columns and Sheets
- Protecting & Unprotecting Cells, Rows, Columns and Sheets with or without Password
- Page Layout, Themes, Background and Printer Properties
- Inserting Pictures, Hyperlinks, Header/Footers, Shapes and Other Objects in Worksheets
B. Working with Formulas/Functions
- Lookup and Reference Functions:
- VLOOKUP, HLOOKUP, INDEX, ADDRESS, MATCH, OFFSET, TRANSPOSE etc
- Logical Function:IF / ELSE, AND, OR, NOT, TRUE, NESTED IF/ELSE etc
- Database Functions:DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, DVARP etc
- Date and Time Functions: DATE, DATEVALUE, DAY, DAY360, SECOND, MINUTES, HOURS, NOW, TODAY, MONTH, YEAR,YEARFRAC, TIME, WEEKDAY, WORKDAY etc
- Information Functions:CELL, ERROR.TYPE, INFO, ISBLANK, ISERR, ISERROR, ISEVEN, ISLOGICAL, ISNA, ISNONTEXT,ISNUMBER, ISREF, ISTEXT, TYPE etc
- Math and Trigonometry Functions: RAND, ROUND, CEILING, FLOOR, INT, LCM, MOD, EVEN, SUMIF, SUMIFS etc
- Statistical Functions: AVEDEV, AVERAGE, AVERAGEA, AVERAGEIF, COUNT, COUNTA, COUNTBLANK,COUNTIF,FORECAST, MAX, MAXA,MIN, MINA, STDEVA etc
- Text Functions: LEFT, RIGHT, TEXT, TRIM, MID, LOWER, UPPER, PROPER, REPLACE, REPT, FIND, SEARCH, SUBSTITUTE,TRIM, TRUNC, CONVERT, CONCATENATE, DOLLAR etc
C. Conditional Formatting
- Using Conditional Formatting
- Using Conditional Formatting with Multiple Cell Rules
- Using Color Scales and Icon Sets in Conditional Formatting
- Creating New Rules and Managing Existing Rules
D. Data Sorting and Filtering
- Sorting Data by Values, Colors, etc
- Using Filters to Sort Data
- Advance Filtering Options
E. Pivot Tables
- Creating Pivot Tables
- Using Pivot Table Options
- Changing and Updating Data Range
- Formatting Pivot Table and Making Dynamic Pivot Tables
F. Pivot Charts
- Creating Pivot Charts
- Types of Pivot Charts and Their Usage
- Formatting Pivot Charts and Making Dynamic Pivot Charts
G. VBA Macro
- Introduction to VBA Macro
- Recording Macro & Understanding Code Behind
- Editing, Writing VBA Code and Saving as Macro or Add-In
- Adding Add-Ins in Excel
H. Case Study: Sales & Financial Reporting
- Creating Sales and Financial Reporting
- Sales & Financial Data Visualization using Dashboard
- Creating Custom Application for Sales and Reporting
RECOMMENDATION FOR PARTICIPANT: Participant must bring Laptop for practice sessions
INSTRUCTOR : Abdullah Fajar, S.Si.,M.Sc.
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 :
- 02 Jan 2023-05 Jan 2023
- 30 Jan 2023-02 Feb 2023
- 27 Feb 2023-02 Mar 2023
- 03 Apr 2023-06 Apr 2023
- 01 May 2023-04 May 2023
- 12 Jun 2023-15 Jun 2023
- 03 Jul 2023-06 Jul 2023
- 07 Aug 2023-10 Aug 2023
- 04 Sep 2023-07 Sep 2023
- 02 Oct 2023-05 Oct 2023
- 30 Oct 2023-02 Nov 2023
- 11 Dec 2023-14 Dec 2023
INVESTATION PRICE/PERSON :
- Rp. 8.500.000/peserta (bayar penuh) atau
- Rp. 8.250.000/peserta (early bird, yang membayar 1 minggu sebelum training) atau
- Rp. 7.950.000/peserta (peserta bergroup yang terdiri dari 3 peserta atau lebih dari 1 perusahaan yang sama)
FACILITIES FOR PARTICIPANTS:
- Modul Training
- Flash Disk berisi materi training
- Sertifikat
- ATK: NoteBook dan Ballpoint
- T-Shirt
- Ransel
- Foto Training
- Ruang Training dengan fasilitas Full AC dan multimedia
- Makan siang dan 2 kali coffeebreak
- Instruktur yang Qualified
- Transportasi untuk peserta dari hotel penginapan ke hotel tempat training – PP (jika peserta minimal dari satu perusahaan ada 4 peserta)