ADVANCED EXCEL
Acara
18 – 21 November 2019 | Rp 8.500.000/ peserta di Bandung
09 – 12 Desember 2019 | Rp 8.500.000/ peserta di Bandung
JADWAL TRAINING 2020
27 – 30 Januari 2020 | Rp 8.500.000/ peserta di Bandung
Jadwal Training 2020 Selanjutnya …
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 : Bandung (Golden Flower, Banana Inn, Serela, Gino Feruci), Amaroossa Hotel, Noor Hotel, Grand Setiabudi Hotel, dll
TRAINING DURATION : 4 days
- 27 Jan 2020-30 Jan 2020
- 17 Feb 2020-20 Feb 2020
- 09 Mar 2020-12 Mar 2020
- 27 Apr 2020-30 Apr 2020
- 04 Mei 2020-07 Mei 2020
- 15 Jun 2020-18 Jun 2020
- 27 Jul 2020-30 Jul 2020
- 18 Agust 2020-21 Agust 2020
- 28 Sep 2020-01 Okt 2020
- 19 Okt 2020-22 Okt 2020
- 16 Nop 2020-19 Nop 2020
- 28 Des 2020-31 Des 2020
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)
ADVANCED EXCEL