Sample sales data

Date Color Region Units Sales Kolom wajib tidak ada jeda misal kolom G
3-Jan-16 Red West 1 $11.00 Nama Kolom wajib Unik (tidak double)
13-Jan-16 Blue South 9 $96.00 Tidak boleh ada kolom atau row yang di
21-Jan-16 Green West 2 $26.00
30-Jan-16 Blue North 7 $84.00
7-Feb-16 Green North 8 $104.00
13-Feb-16 Red South 2 $22.00
21-Feb-16 Blue East 5 $60.00
1-Mar-16 Green West 2 $26.00
13-Mar-16 Blue East 8 $96.00
23-Mar-16 Blue North 7 $84.00
28-Mar-16 Green West 2 $26.00
3-Apr-16 Blue South 8 $96.00
12-Apr-16 Green South 1 $13.00
16-Apr-16 Red East 8 $88.00
23-Apr-16 Red West 6 $66.00
30-Apr-16 Green South 5 $65.00
9-May-16 Blue South 7 $84.00
16-May-16 Red South 5 $55.00
25-May-16 Blue South 1 $12.00
30-May-16 Blue North 4 $48.00
4-Jun-16 Blue North 7 $84.00
13-Jun-16 Red East 3 $33.00
21-Jun-16 Blue South 2 $24.00
26-Jun-16 Blue South 6 $72.00
2-Jul-16 Red East 6 $66.00
8-Jul-16 Green West 2 $26.00
12-Jul-16 Blue South 4 $48.00
19-Jul-16 Green South 1 $13.00
26-Jul-16 Blue West 1 $12.00
31-Jul-16 Red South 2 $22.00
7-Aug-16 Green West 5 $65.00
13-Aug-16 Blue West 5 $60.00
20-Aug-16 Green North 8 $104.00
25-Aug-16 Blue West 4 $48.00
1-Sep-16 Red South 6 $66.00
7-Sep-16 Green South 7 $91.00
10-Sep-16 Red North 4 $44.00
13-Sep-16 Green West 6 $78.00
19-Sep-16 Blue West 1 $12.00
26-Sep-16 Blue South 4 $48.00
30-Sep-16 Red South 6 $66.00
 6-Oct-16 Blue South 3 $36.00
9-Oct-16 Green West 2 $26.00
14-Oct-16 Blue South 1 $12.00
19-Oct-16 Blue East 2 $24.00
23-Oct-16 Red North 8 $88.00
26-Oct-16 Green South 6 $78.00
30-Oct-16 Green East 7 $91.00
5-Nov-16 Red South 2 $22.00
10-Nov-16 Blue North 6 $72.00
15-Nov-16 Blue North 6 $72.00
20-Nov-16 Blue West 8 $96.00
23-Nov-16 Red South 3 $33.00
28-Nov-16 Blue North 2 $24.00
30-Nov-16 Blue South 1 $12.00
4-Dec-16 Blue South 8 $96.00
7-Dec-16 Green North 1 $13.00
9-Dec-16 Green East 5 $65.00
14-Dec-16 Blue North 1 $12.00
16-Dec-16 Blue North 1 $12.00
20-Dec-16 Green East 4 $52.00
23-Dec-16 Blue South 5 $60.00
25-Dec-16 Green East 3 $39.00
28-Dec-16 Blue North 8 $96.00
31-Dec-16 Blue West 6 $72.00
5-Jan-17 Blue East 1 $12.00
9-Jan-17 Green East 8 $104.00
11-Jan-17 Blue South 5 $60.00
16-Jan-17 Green North 6 $78.00
19-Jan-17 Blue North 8 $96.00
24-Jan-17 Blue West 3 $36.00
28-Jan-17 Green South 5 $65.00
31-Jan-17 Green West 5 $65.00
3-Feb-17 Green South 1 $13.00
8-Feb-17 Red South 4 $44.00
10-Feb-17 Red West 2 $22.00
14-Feb-17 Green South 6 $78.00
16-Feb-17 Blue East 7 $84.00
18-Feb-17 Blue West 5 $60.00
23-Feb-17 Green East 8 $104.00
27-Feb-17 Red West 3 $33.00
2-Mar-17 Blue West 4 $48.00
5-Mar-17 Blue East 7 $84.00
8-Mar-17 Red West 5 $55.00
13-Mar-17 Green East 8 $104.00
16-Mar-17 Blue West 7 $84.00
21-Mar-17 Blue West 1 $12.00
23-Mar-17 Green North 3 $39.00
28-Mar-17 Green West 5 $65.00
31-Mar-17 Red North 7 $77.00
3-Apr-17 Red West 4 $44.00
6-Apr-17 Blue West 1 $12.00
8-Apr-17 Red East 2 $22.00
10-Apr-17 Blue West 8 $96.00
12-Apr-17 Green North 1 $13.00
15-Apr-17 Red West 3 $33.00
18-Apr-17 Green West 4 $52.00
20-Apr-17 Red East 2 $22.00
23-Apr-17 Red West 5 $55.00
26-Apr-17 Blue East 1 $12.00
29-Apr-17 Red South 2 $22.00
2-May-17 Blue East 7 $84.00
6-May-17 Red North 5 $55.00
10-May-17 Red West 2 $22.00
14-May-17 Green West 3 $39.00
17-May-17 Green North 2 $26.00
19-May-17 Green West 3 $39.00
22-May-17 Blue West 8 $96.00
26-May-17 Red West 1 $11.00
28-May-17 Red West 8 $88.00
30-May-17 Green East 5 $65.00
3-Jun-17 Blue South 1 $12.00
6-Jun-17 Red West 8 $88.00
8-Jun-17 Green West 1 $13.00
10-Jun-17 Blue West 6 $72.00
14-Jun-17 Red North 5 $55.00
17-Jun-17 Blue East 3 $36.00
21-Jun-17 Red East 1 $11.00
18-Jun-17 Green South 8 $104.00
22-Jun-17 Blue North 3 $36.00
25-Jun-17 Blue South 4 $48.00
29-Jun-17 Green East 8 $104.00
3-Jul-17 Red North 7 $77.00
7-Jul-17 Red West 5 $55.00
11-Jul-17 Blue East 2 $24.00
15-Jul-17 Red North 5 $55.00
18-Jul-17 Green West 7 $91.00
20-Jul-17 Green West 1 $13.00
24-Jul-17 Red West 7 $77.00
28-Jul-17 Blue North 4 $48.00
30-Jul-17 Red South 6 $66.00
 3-Aug-17 Blue West 2 $24.00
5-Aug-17 Blue East 6 $72.00
9-Aug-17 Red West 8 $88.00
11-Aug-17 Red West 7 $77.00
14-Aug-17 Blue South 2 $24.00
17-Aug-17 Green East 8 $104.00
20-Aug-17 Green West 6 $78.00
24-Aug-17 Red South 6 $66.00
27-Aug-17 Blue West 3 $36.00
29-Aug-17 Red North 4 $44.00
31-Aug-17 Red South 1 $11.00
2-Sep-17 Red South 1 $11.00
6-Sep-17 Green East 6 $78.00
10-Sep-17 Red East 3 $33.00
12-Sep-17 Blue South 4 $48.00
16-Sep-17 Blue South 4 $48.00
18-Sep-17 Green West 7 $91.00
22-Sep-17 Red East 4 $44.00
25-Sep-17 Green North 2 $26.00
29-Sep-17 Red South 8 $88.00
2-Oct-17 Red North 3 $33.00
4-Oct-17 Green North 5 $65.00
6-Oct-17 Blue North 1 $12.00
10-Oct-17 Green West 4 $52.00
12-Oct-17 Red East 5 $55.00
14-Oct-17 Green West 4 $52.00
17-Oct-17 Red West 6 $66.00
18-Oct-17 Green South 3 $39.00
22-Oct-17 Blue South 6 $72.00
26-Oct-17 Green West 1 $13.00
27-Oct-17 Red South 1 $11.00
28-Oct-17 Red West 7 $77.00
30-Oct-17 Blue West 5 $60.00
31-Oct-17 Red South 4 $44.00
3-Nov-17 Green East 6 $78.00
7-Nov-17 Green South 1 $13.00
11-Nov-17 Green North 7 $91.00
13-Nov-17 Blue North 3 $36.00
17-Nov-17 Blue West 2 $24.00
19-Nov-17 Green West 8 $104.00
23-Nov-17 Blue North 6 $72.00
27-Nov-17 Red West 5 $55.00
28-Nov-17 Blue West 4 $48.00
30-Nov-17 Red West 8 $88.00
1-Dec-17 Blue East 8 $96.00
 3-Dec-17 Green West 5 $65.00
5-Dec-17 Blue North 3 $36.00
6-Dec-17 Blue West 4 $48.00
8-Dec-17 Blue West 5 $60.00
10-Dec-17 Green West 1 $13.00
12-Dec-17 Red North 4 $44.00
13-Dec-17 Green North 5 $65.00
14-Dec-17 Blue North 5 $60.00
16-Dec-17 Blue West 1 $12.00
17-Dec-17 Blue North 6 $72.00
19-Dec-17 Green North 2 $26.00
23-Dec-17 Blue North 1 $12.00
27-Dec-17 Blue East 1 $12.00
30-Dec-17 Green South 8 $104.00
2-Jan-18 Red North 4 $44.00
5-Jan-18 Blue South 8 $96.00
9-Jan-18 Red North 2 $22.00
13-Jan-18 Green North 3 $39.00
17-Jan-18 Silver West 6 $84.00
19-Jan-18 Red North 8 $88.00
20-Jan-18 Blue North 8 $96.00
21-Jan-18 Blue West 4 $48.00
23-Jan-18 Blue West 6 $72.00
24-Jan-18 Silver North 7 $98.00
25-Jan-18 Blue East 3 $36.00
28-Jan-18 Green North 4 $52.00
31-Jan-18 Silver West 6 $84.00
3-Feb-18 Red North 1 $11.00
6-Feb-18 Red South 6 $66.00
7-Feb-18 Blue West 1 $12.00
9-Feb-18 Blue South 3 $36.00
11-Feb-18 Green North 3 $39.00
14-Feb-18 Blue South 8 $96.00
16-Feb-18 Silver East 3 $42.00
19-Feb-18 Blue West 7 $84.00
23-Feb-18 Blue West 5 $60.00
26-Feb-18 Green North 4 $52.00
2-Mar-18 Red North 5 $55.00
5-Mar-18 Red West 1 $11.00
6-Mar-18 Blue North 8 $96.00
9-Mar-18 Blue North 7 $84.00
11-Mar-18 Silver West 5 $70.00
15-Mar-18 Silver West 4 $56.00
16-Mar-18 Blue West 6 $72.00
17-Mar-18 Blue East 4 $48.00
18-Mar-18 Silver North 4 $56.00
19-Mar-18 Green West 1 $13.00
23-Mar-18 Green South 8 $104.00
25-Mar-18 Green West 1 $13.00
27-Mar-18 Blue East 4 $48.00
30-Mar-18 Silver West 1 $14.00
1-Apr-18 Silver South 4 $56.00
4-Apr-18 Red West 8 $88.00
6-Apr-18 Silver West 4 $56.00
7-Apr-18 Green North 6 $78.00
11-Apr-18 Silver East 8 $112.00
15-Apr-18 Silver North 3 $42.00
16-Apr-18 Blue North 2 $24.00
20-Apr-18 Blue North 7 $84.00
21-Apr-18 Green West 1 $13.00
22-Apr-18 Green North 5 $65.00
26-Apr-18 Red North 8 $88.00
30-Apr-18 Silver West 8 $112.00
4-May-18 Blue West 3 $36.00
5-May-18 Red West 2 $22.00
9-May-18 Blue West 1 $12.00
12-May-18 Blue East 6 $72.00
13-May-18 Green South 1 $13.00
16-May-18 Green West 2 $26.00
20-May-18 Silver West 1 $14.00
21-May-18 Blue South 6 $72.00
25-May-18 Red South 1 $11.00
27-May-18 Silver West 5 $70.00
30-May-18 Red North 7 $77.00
2-Jun-18 Silver South 7 $98.00
3-Jun-18 Silver North 7 $98.00
7-Jun-18 Blue South 1 $12.00
10-Jun-18 Green North 2 $26.00
13-Jun-18 Silver South 7 $98.00
15-Jun-18 Red East 2 $22.00
16-Jun-18 Green North 5 $65.00
18-Jun-18 Silver East 3 $42.00
21-Jun-18 Silver East 8 $112.00
23-Jun-18 Green West 6 $78.00
25-Jun-18 Silver South 5 $70.00
26-Jun-18 Silver South 1 $14.00
27-Jun-18 Blue West 3 $36.00
30-Jun-18 Silver East 1 $14.00
2-Jul-18 Blue North 4 $48.00
6-Jul-18 Silver South 6 $84.00
 9-Jul-18 Red East 1 $11.00
12-Jul-18 Silver North 4 $56.00
14-Jul-18 Red North 3 $33.00
17-Jul-18 Red South 8 $88.00
20-Jul-18 Green North 4 $52.00
23-Jul-18 Silver South 6 $84.00
24-Jul-18 Red North 6 $66.00
27-Jul-18 Red East 5 $55.00
31-Jul-18 Silver West 1 $14.00
3-Aug-18 Red North 3 $36.00
5-Aug-18 Silver West 3 $45.00
8-Aug-18 Silver East 3 $45.00
10-Aug-18 Red South 5 $60.00
13-Aug-18 Red West 2 $24.00
15-Aug-18 Red West 3 $36.00
18-Aug-18 Blue North 6 $78.00
20-Aug-18 Blue North 7 $91.00
21-Aug-18 Silver North 2 $30.00
24-Aug-18 Silver West 7 $105.00
27-Aug-18 Silver West 7 $105.00
30-Aug-18 Red North 1 $12.00
2-Sep-18 Red South 7 $84.00
6-Sep-18 Blue North 4 $52.00
7-Sep-18 Silver North 7 $105.00
10-Sep-18 Red East 7 $84.00
13-Sep-18 Red West 4 $48.00
14-Sep-18 Blue North 8 $104.00
17-Sep-18 Silver South 8 $120.00
21-Sep-18 Red North 5 $60.00
25-Sep-18 Silver South 3 $45.00
29-Sep-18 Green West 2 $28.00
3-Oct-18 Silver North 5 $75.00
5-Oct-18 Red West 5 $60.00
7-Oct-18 Green North 7 $98.00
11-Oct-18 Red North 8 $96.00
13-Oct-18 Blue South 1 $13.00
16-Oct-18 Green West 5 $70.00
19-Oct-18 Silver North 7 $105.00
21-Oct-18 Silver North 7 $105.00
22-Oct-18 Blue North 4 $52.00
24-Oct-18 Silver East 3 $45.00
25-Oct-18 Blue West 1 $13.00
26-Oct-18 Green South 5 $70.00
28-Oct-18 Blue East 1 $13.00
30-Oct-18 Red North 5 $60.00
 1-Nov-18 Blue South 5 $65.00
3-Nov-18 Blue West 2 $26.00
6-Nov-18 Green West 7 $98.00
7-Nov-18 Silver North 8 $120.00
10-Nov-18 Silver West 4 $60.00
11-Nov-18 Silver North 4 $60.00
13-Nov-18 Silver South 7 $105.00
15-Nov-18 Red North 2 $24.00
18-Nov-18 Blue West 3 $39.00
21-Nov-18 Blue West 5 $65.00
23-Nov-18 Red South 1 $12.00
25-Nov-18 Red North 4 $48.00
28-Nov-18 Red North 7 $84.00
1-Dec-18 Red North 7 $84.00
2-Dec-18 Silver East 3 $45.00
3-Dec-18 Green West 4 $56.00
5-Dec-18 Green West 7 $98.00
6-Dec-18 Silver West 5 $75.00
8-Dec-18 Red West 3 $36.00
10-Dec-18 Red South 5 $60.00
11-Dec-18 Silver South 5 $75.00
12-Dec-18 Silver North 7 $105.00
14-Dec-18 Silver North 8 $120.00
16-Dec-18 Red North 4 $48.00
18-Dec-18 Red West 6 $72.00
19-Dec-18 Silver North 3 $45.00
20-Dec-18 Blue West 4 $52.00
21-Dec-18 Green East 5 $70.00
24-Dec-18 Silver North 2 $30.00
26-Dec-18 Silver South 2 $30.00
29-Dec-18 Silver South 1 $15.00
30-Dec-18 Blue South 8 $104.00
31-Dec-18 Green West 1 $14.00
1-Jan-19 Silver West 7 $105.00
4-Jan-19 Silver North 4 $60.00
6-Jan-19 Red West 3 $36.00
9-Jan-19 Blue South 2 $26.00
10-Jan-19 Silver East 6 $90.00
13-Jan-19 Green West 5 $70.00
16-Jan-19 Silver West 7 $105.00
17-Jan-19 Green East 7 $98.00
19-Jan-19 Green North 1 $14.00
20-Jan-19 Blue North 2 $26.00
22-Jan-19 Green West 3 $42.00
23-Jan-19 Silver West 7 $105.00
 24-Jan-19 Green West 7 $98.00
26-Jan-19 Green North 3 $42.00
29-Jan-19 Blue North 5 $65.00
1-Feb-19 Green East 3 $42.00
3-Feb-19 Blue East 4 $52.00
4-Feb-19 Silver West 1 $15.00
7-Feb-19 Silver West 8 $120.00
10-Feb-19 Red North 7 $84.00
11-Feb-19 Red North 4 $48.00
14-Feb-19 Silver North 7 $105.00
16-Feb-19 Blue North 8 $104.00
18-Feb-19 Blue West 8 $104.00
21-Feb-19 Silver North 6 $90.00
24-Feb-19 Silver West 5 $75.00
25-Feb-19 Blue West 4 $52.00
28-Feb-19 Green North 7 $98.00
1-Mar-19 Green West 8 $112.00
4-Mar-19 Green South 8 $112.00
7-Mar-19 Blue North 2 $26.00
10-Mar-19 Red South 1 $12.00
12-Mar-19 Blue North 6 $78.00
13-Mar-19 Silver West 1 $15.00
16-Mar-19 Silver West 6 $90.00
18-Mar-19 Blue East 4 $52.00
19-Mar-19 Green West 8 $112.00
21-Mar-19 Red South 6 $72.00
23-Mar-19 Silver South 3 $45.00
25-Mar-19 Green North 1 $14.00
28-Mar-19 Silver East 3 $45.00
31-Mar-19 Blue West 3 $39.00
2-Apr-19 Silver West 4 $60.00
4-Apr-19 Green North 7 $98.00
7-Apr-19 Silver North 8 $120.00
8-Apr-19 Red West 2 $24.00
11-Apr-19 Silver West 2 $30.00
13-Apr-19 Blue West 7 $91.00
15-Apr-19 Silver North 6 $90.00
16-Apr-19 Red East 8 $96.00
17-Apr-19 Green North 6 $84.00
19-Apr-19 Silver North 8 $120.00
20-Apr-19 Green South 1 $14.00
22-Apr-19 Silver West 7 $105.00
23-Apr-19 Blue West 5 $65.00
24-Apr-19 Silver South 1 $15.00
26-Apr-19 Silver North 8 $120.00
 28-Apr-19 Blue East 6 $78.00
29-Apr-19 Silver West 6 $90.00
2-May-19 Green West 5 $70.00
5-May-19 Silver West 5 $75.00
6-May-19 Green North 1 $14.00
7-May-19 Blue West 7 $91.00
9-May-19 Green West 5 $70.00
10-May-19 Blue West 5 $65.00
11-May-19 Red North 5 $60.00
12-May-19 Silver South 4 $60.00
13-May-19 Red West 6 $72.00
16-May-19 Green South 5 $70.00
17-May-19 Silver West 8 $120.00
19-May-19 Silver West 5 $75.00
21-May-19 Green West 8 $112.00
22-May-19 Red North 3 $36.00
23-May-19 Blue East 2 $26.00
25-May-19 Silver North 3 $45.00
28-May-19 Red North 3 $36.00
29-May-19 Silver North 8 $120.00
30-May-19 Silver West 2 $30.00
2-Jun-19 Silver North 7 $105.00
5-Jun-19 Silver West 4 $60.00
6-Jun-19 Red East 1 $12.00
9-Jun-19 Silver North 3 $45.00
12-Jun-19 Silver West 7 $105.00
14-Jun-19 Red East 2 $24.00
17-Jun-19 Red North 3 $36.00
19-Jun-19 Green North 7 $98.00
22-Jun-19 Silver North 7 $105.00
25-Jun-19 Green West 4 $56.00
27-Jun-19 Blue North 6 $78.00
28-Jun-19 Blue West 1 $13.00
29-Jun-19 Red East 3 $36.00
2-Jul-19 Blue North 1 $13.00
3-Jul-19 Silver North 7 $105.00
5-Jul-19 Blue North 7 $91.00
7-Jul-19 Silver West 3 $45.00
9-Jul-19 Green West 1 $14.00
11-Jul-19 Blue South 8 $104.00
12-Jul-19 Red North 2 $24.00
15-Jul-19 Green West 7 $98.00
17-Jul-19 Red South 1 $12.00
19-Jul-19 Silver East 7 $105.00
22-Jul-19 Silver West 6 $90.00
23-Jul-19 Silver North 7 $105.00
24-Jul-19 Silver North 4 $60.00
27-Jul-19 Silver South 3 $45.00
30-Jul-19 Silver South 6 $90.00
1-Aug-19 Red North 8 $96.00
3-Aug-19 Blue South 6 $78.00
tidak ada jeda misal kolom G kosong (tanpa judul) kemudian lanjut ke kolom H
wajib Unik (tidak double)
da kolom atau row yang di merge (gabung)

More pivot table resources:

Pivot table examples

Pivot table tips
Pivot tables vs. formulas
Pivot table training
Region North

Date (Quarter) Values

Qtr1
Color Total Sales Sum of Units Max of Units2 % Sales
Blue 839.00 68.00 8.00 9.63%
Green 571.00 43.00 8.00 6.56%
Red 429.00 38.00 8.00 4.93%
Silver 409.00 28.00 7.00 4.70%
Grand Total 2,248.00 177.00 8.00 25.81%
Qtr2 Qtr3
Total Sales Sum of Units Max of Units2 % Sales Total Sales
354.00 29.00 7.00 4.06% 525.00
567.00 42.00 7.00 6.51% 182.00
443.00 39.00 8.00 5.09% 547.00
1,010.00 68.00 8.00 11.60% 461.00
2,374.00 178.00 8.00 27.26% 1,715.00
 Qtr4
Sum of Units Max of Units2 % Sales Total Sales Sum of Units
41.00 8.00 6.03% 640.00 53.00
14.00 8.00 2.09% 358.00 27.00
48.00 8.00 6.28% 609.00 52.00
31.00 7.00 5.29% 765.00 51.00
134.00 8.00 19.69% 2,372.00 183.00
 Total Total Sales Total Sum of Units Total Max of Units2
Max of Units2 % Sales
8.00 7.35% 2,358.00 191.00 8.00
7.00 4.11% 1,678.00 126.00 8.00
8.00 6.99% 2,028.00 177.00 8.00
8.00 8.78% 2,645.00 178.00 8.00
8.00 27.24% 8,709.00 672.00 8.00
Total % Sales

27.08%
19.27%
23.29%
30.37%
100.00%
Langkah-langkah Pivot Table

1 Highlight data yang ingin dibuat pivot table

2 Pilih menu Insert dan pilih pivot table

Gb. Pivot Table - New Worksheet

di bagian select a table or range --> ini bisa diubah source table nya
Choose where you want the pivottable report to be placed
- New Worksheet : Pivot Table akan terbentuk di sheet baru
- Existing Worksheet : Pilih di sheet yang sudah ada dan pilih cell dimana pivot table akan d
Kemudian klik OK
 GB. Pivot Table

Untuk data-data informasi yang ingin ditampilkan secara menurun letakkan di kolom Rows
Untuk data-data yang ingin ditampilkan secara horisontal (biasanya tahun) letakkan di Colu
Untuk data-data yang akan dilakukan perhitungan letakkan di ∑Values
Untuk data yang ingin digunakan sebagai filter letakkan di Filter

Untuk setting format number, klik di field yang ingin diubah formatnya (lihat gambar dibaw
Akan muncul layar berikut :
Klik di format number untuk mengubah bentuk format number
Setelah selesai melakukan setting, klik OK, kemudian klik OK sekali lagi

Untuk membuat perhitungan Sales sebagai % dari Total Sales maka, di menu Value Field Se
Pilih Tab Show Values as
Di Drop Down Show Values as % of Grand Total

Setelah selesai , Klik OK

 Gb. Pivot table - Existing Worksheet

dimana pivot table akan diletakkan

n letakkan di kolom Rows
ya tahun) letakkan di Columns

atnya (lihat gambar dibawah)

ka, di menu Value Field Settings