Excel Training
Excel For Data Analyst
23 November 2024
@ardyfir
[Link]
Business Peformance & Analytics | Excel Trainer
Moch Ardi Firmansyah
Business Performance & Analytics | Excel Trainer
Commercial Finance Analyst Ass. Manager at URC Indonesia (Jack n Jill Group)
Business Performance & Analytics Ass. Manager at Erajaya Group
Business Analyst Manager at PT. Surya Dharmagati (Carvil Group)
Data Analyst Supervisor at PT. Borwita Citra Prima (Distributor P&G)
[Link]
Trainer Ms. Excel For Data Analyst
Trainer VBA Excel
100++ Session Online + Offline
5000++ Participant ASN, Swasta, Student etc.
Background Education
- Applied Mathematics (Bachelor of Science) – Institut Teknologi Sepuluh Nopember
2
Poin Belajar
01 Introduction Pivot Table
02 Calculation Field
03 Relationships Pivot Table
04 Pivot Chart
05 Slicer & Timeline
06 Dashboard Reporting
Simple Dashboard Analytics
Simple Dashboard Analytics
Poin Belajar
01 Introduction Pivot Table
02 Calculation Field
03 Relationships Pivot Table
04 Pivot Chart
05 Slicer & Timeline
06 Dashboard Reporting
Tipe Data Analisis
Introduction Pivot Table
Mendapatkan data total Sales terhadap Category
Pivot Table adalah sebuah tabel yang sudah dikategorikan, bertujuan untuk mendapatkan nilai
aggregasi dari data, terutama supaya dapat angka yang dibutuhkan, sangat powerful jika
penggunaannya untuk data yang cukup besar.
Interface Pivot Table
Customer Type (All)
Sum of Profit Margin Column Labels
Row Labels Bandung Jakarta Surabaya Grand Total
2016 531450 12488550 32144929.5 45164929.5
Furniture 1313100 1313100
Office Supplies 531450 1287600 14006250 15825300
Technology 11200950 16825579.5 28026529.5
2017 3323550 17327850 60617700 81269100
Furniture 218100 2727000 1422150 4367250
Office Supplies 1007100 5055750 20489700 26552550
Fields
Technology 2098350 9545100 38705850 50349300
2018 9388200 23787150 68353500 101528850
Furniture 100800 2735250 2836050
Office Supplies 1214250 7219500 21404250 29838000
Technology 8073150 16567650 44214000 68854800
2019 7435800 17004000 55637400 80077200
Furniture 100800 1514700 1615500
Office Supplies 2907750 5948850 15864000 24720600
Technology 4528050 10954350 38258700 53741100
2020 1063650 4914300 13773450 19751400 Areas
Furniture 2424600 1212300 3636900
Office Supplies 834300 1589100 3486150 5909550
Technology 229350 900600 9075000 10204950
Grand Total 21742650 75521850 230526979.5 327791479.5
Tips & Trick Preparation Data
• Gunakan data tabular yang bersih untuk hasil terbaik.
• Menata data Anda dalam kolom, bukan baris.
• Pastikan semua kolom memiliki header, dengan satu baris label unik yang tidak kosong untuk setiap kolom.
Update & Refresh Data
Refresh digunakan untuk update data setelah Sumber datanya
diubah
Change Data Source untuk mengganti sumber
data/tabelnya bisa dari worksheets lain/workbook lain
Tab Menu Design
` ` `
`
Group & Ungroup
Category2 Category Sum of Total Belanja
Category Sum of Total Belanja Group1 Dapur 76,903,000
Dapur 76,903,000 Laundry 196,157,000
Laundry 196,157,000 Olahraga 125,292,000
Olahraga 125,292,000 Group1 Total 398,352,000
Peralatan Elektronik Peralatan Elektronik 128,410,000
Peralatan Elektronik 128,410,000
Peralatan Elektronik Total 128,410,000
Peralatan Kebersihan 72,934,000 Peralatan Kebersihan Peralatan Kebersihan 72,934,000
Grand Total 599,696,000 Peralatan Kebersihan Total 72,934,000
Grand Total 599,696,000
Grouping data pada pivot table dapat membantu untuk menunjukan subset dari data untuk di
Analisa, tanpa mengubah source data/ menambahkan mappingan yang baru untuk new group.
Tab menu Group & Ungroup
• Tab Menu Pivot Table Analyst
• submenu group
•Group Selection
•Ungroup
•Group Field
Example Group/Ungroup
Years (Tanggal) Quarters (Tanggal) Months (Tanggal) Total Belanja
2023 Qtr1 Jan 40,763,000
2023 Qtr1 Feb 35,082,000
2023 Qtr1 Mar 37,828,000
2023 Qtr1 Total 113,673,000
2023 Qtr2 Apr 29,476,000
2023 Qtr2 May 31,925,000
2023 Qtr2 Jun 27,909,000
2023 Qtr2 Total 89,310,000
2023 Total 202,983,000
Grand Total 202,983,000
Jika source data field yang kita ambil format “date”, maka akan otomatis membuat grouping by year,
quartal dan months, jadi jika memang kita tidak ingin membuat summary dengan grouping tersebut
maka kita bisa langsung ungroup atau custom sesuai keinginan kita.
Poin Belajar
01 Introduction Pivot Table
02 Calculation Field
03 Relationships Pivot Table
04 Pivot Chart
05 Slicer & Timeline
06 Dashboard Reporting
Pengenalan Calculation Field
Cara Manualnyaa…….
Source Data yang sudah di tambah rumus %Margin, selanjutnya dibuat Pivot Table
Membuat Calculation Field
Solusinya, jika kalian ingin custom source data, tanpa mengubah ke raw data, adalah
Calculation Field, hal ini akan membuat report lebih otomatis
Contoh Output Calculation Field
Poin Belajar
01 Introduction Pivot Table
02 Calculation Field
03 Relationships Pivot Table
04 Pivot Chart
05 Slicer & Timeline
06 Dashboard Reporting
Introduction
Employee ID First Last
E1001 Raina Aryani
E1002 Tari Anggraini
E1003 Karen Riyanti
E1004 Titin Aryani
E1005 Ilsa Utami
E1006 Jessica Hariyah
E1007 Almira Hasanah
E1008 Sari Astuti
E1009 Aurora Anggraini
E1010 Vivi Andriani
E1011 Eka Nashiruddin
E1012 Adiarja Sirait
Jika belajar beberapa materi sebelumnya, untuk membuat relasi antar table, kita biasa menggunakan
Vlookup, Index Match, Offset Match,
Kelemahannya: Jika Olah data besar, maka processor dan memori yang dipakai akan lebih besar sehingga
data akan calculated lebih lama, sehingga proses ini sangat penting
Membuat Pivot Table Relationships
Step yang sama saat membuat
pivot table, bedanya centang
“Add this data to the data model” Data lain yang akan di buat relationship harus
berupa table
“CTRL + T”
Membuat Pivot Table Relationships
Pivot Table Analyze → Relationships
Manage Relationships → Klik New
Membuat Pivot Table Relationships
Membuat Relationship antara table Range dan Table1 dengan kolom yang memiliki Nilai yang sama,
secara konsep seperti Vlookup, tapi kita tidak perlu membuat Fungsi Vlookup Pada Data kita, namun
langsung bisa kita buat otomatis di Pivot Table
Poin Belajar
01 Introduction Pivot Table
02 Calculation Field
03 Relationships Pivot Table
04 Pivot Chart
05 Slicer & Timeline
06 Dashboard Reporting
Komponen Chart Judul Chart Chart Area
Plot Area
Income Statement Data Series
Rp40,000,000 0.1
Rp35,000,000 Gridlines 0.09
0.08
Rp30,000,000
0.07
Profit Margin
Rp25,000,000 0.06
Income
Rp20,000,000 0.05
Rp15,000,000 0.04
0.03
Rp10,000,000
0.02
Rp5,000,000 0.01
Rp0 0
Jan Feb Mar Apr May Jun
Bulan
X-axis Secondary
Y-Axis Y-Axis
Income Profit Margin
Legend
Combo Chart Income vs Profit margin
Step membuat Chart
• Tab Menu insert
• Pilih sub menu chart
• Pilih chart yang diinginkan
Chart di Excel
Sesuai dengan macam-macam jenis data visualisasi di Excel juga memiliki berbagai macam chart yang bisa kita
sesuaikan dengan Analisa kita, dan dari berbagai source yang ada, bisa dari table / pivot table
Pivot Chart
Line Chart dengan Source Pivot table, jika pivot berubah maka
chart akan berubah otomatis
Order Qty City
Order Year Order Date Jakarta Surabaya
2019 Jan 91 172
2019 Feb 199 298 700
2019 Mar 64 232
2019 Apr 55 169 600
2019 May 159 519
2019 Jun 135 434 500
2019 Jul 202 379
400
2019 Aug 267 222
2019 Sep 33 423 Jakarta
300
2019 Oct 74 446 Surabaya
2019 Nov 5 249 200
2019 Dec 154 467
2020 Jan 228 397 100
2020 Feb 165 599
2020 Mar 145 463 0
Grand Total 1976 5469 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar
2019 2020
Table Chart
Dalam Pivot Chart, seperti halnya pada materi visualisasi sebelumnya, bisa disesusaikan dengan keinginan untuk penyajian
datanya apakah line chart, donut chart, bar chart etc. walaupun bisa saja kita tidak perlu pakai pivot table untuk buat chart,
tapi hal ini akan membantu jika data dinamis / dalam pembuatan Dashboard
Poin Belajar
01 Introduction Pivot Table
02 Calculation Field
03 Relationships Pivot Table
04 Pivot Chart
05 Slicer & Timeline
06 Dashboard Reporting
Slicer Dan Timeline
Fungsinya mirip dengan Filter yang ada di pivot table, tapi secara visual lebih seperti tombol-tombol yang akan
menambah report lebih menarik, dan hal ini akan membantu untuk report dashboard yang akan dibaca oleh level
manager ke atas jika diperlukan untuk melihat kondisi dari berbagai cabang dari tahun ke tahun.
Slicer Pivot Table
Tab Menu Analyze →
Insert Slicer → Centang City
Hasil Chart yang muncul ketika
Kita hanya klik button surabaya
Kita juga bisa mengkoneksikan semua report dengan pivot table dengan sumber data yang sama, jadi ketika kita
klik slicer “Surabaya”, semua table terfilter Surabaya saja
Timeline Pivot Table
Sum of Order Quantity Column Labels
Customer Type Bandung Jakarta Surabaya Grand Total
Consumer 288 1,417 3,118 4,823
Corporate 1,138 2,525 5,402 9,065 Tab Menu Analyze →
Home Office 792 1,840 3,849 6,481 Insert timeline → Centang Order
Small Business 409 1,802 3,439 5,650 Data
Grand Total 2,627 7,584 15,808 26,019
Order Date
All Periods MONTHS
2020
JUN JUL AUG SEP OCT NOV DEC
Kita bisa memilih Data Penjualan Per Customer Type setiap detail waktunya, dengan memanfaatkan fitur timeline
ini , selain memudahkan saat filter , secara tampilan report kita akan lebih menarik
Poin Belajar
01 Introduction Pivot Table
02 Calculation Field
03 Relationships Pivot Table
04 Pivot Chart
05 Slicer & Timeline
06 Dashboard Reporting
Dashboard
Dashboard bisnis adalah tampilan grafis dari berbagai indikator, yang menampilkan
status operasi sebuah perusahaan secara real-time dan memvisualisasi data yang telah
terkumpul, Dashboard disiapkan untuk mengkomunikasikan wawasan dari data
melalui grafik dan diagram, serta membantu user membuat keputusan yang lebih baik
dan terinformasi.
Sales Dashboard
P&L Dashboard
Storytelling
is very
important!
Adverity
Type Of Data Viz Data Visualization Types: Everything a Marketer Needs to Know - TapClicks
There are so many type of data viz, starting from the simplest until the hardest viz,
and maybe some of you are not familiar with these.
Check !!!
Check !!!
Check !!!
Excel Case
Chart Title Budget vs Actual (Case: March)
25000000 25 jt
due to covid
20000000 19441000 19103000 20 jt
18603000 18 jt
18104000 18130000
17390000
16428000
15749000 15399000
15 jt
15000000
12491000
10691000 10 jt
10103000 10 jt
10000000
5 jt
5000000
0 jt
0 Jan Feb Mar Apr May Jun
Jan Feb Mar Apr May Jun
1. Tidak ada keterangan JUDUL
2. Data point membingungkan
3. Tidak Jelas tanpa Legend
4. Grindlines Menggangu
5. Y-Axis terlalu banyak 0
THANK
YOU