Data Analytics Tutorial: Transaction Analysis Using Excel Pivot Tables and Charts, Slides of Transaction Processing for E Commerce

Download Data Analytics Tutorial: Transaction Analysis Using Excel Pivot Tables and Charts and more Slides Transaction Processing for E Commerce in PDF only on Docsity!

Data Analytics Tutorial:

Transaction Analysis Using

Excel Pivot Tables and Charts

City of Somerville, MA dataset

City of Somerville, MA dataset •

Using real‐life checkbook data from City ofSomerville, MA, for 2013 – 2016

In this tutorial, we are using a small, 22‐record data set - For the actual activity, you will be using thefull data set so answers will be different butthe process will be similar

Start by opening Excel workbook

General instructions For each of the requirements, create a newpivot table in a new worksheet. Name eachnew worksheet as “Req 1,” “Req 2,” etc.Format the dollar amounts in each pivot tableor pivot chart using the accounting formatwith zero decimal places. Format non‐currency numbers in each pivot table or pivotchart using the number format with zerodecimal places.

Req 1: Total spending 2013 ‐ 2016

#1: Click anywhere in the data in theData worksheet

Req 1: Total spending 2013 ‐ 2016

#2: Click on Insert tab

Req 1: Total spending 2013 ‐ 2016

#4: Click on OK (use defaults)

Req 1: Total spending 2013 ‐ 2016

#5: Right‐click theworksheet name torename it as “Req 1”

Req 1: Total spending 2013 ‐ 2016

Notice the column labelsnow appear in the pivottable

Req 1: Total spending 2013 ‐ 2016

By the way, if this panelever disappears, you canbring it back by clickinganywhere in the pivottable you have created

Req 1: Total spending 2013 ‐ 2016

To change the amounts in thepivot table to be the sum ratherthan the count, click on “Countof…” in the PivotTable Fieldspanel, select Value Field Settings,and then select Sum and click OK.

Req 1: Total spending 2013 ‐ 2016

Now the pivot table hastransactions amounts

Req 1: Total spending 2013 ‐ 2016

#9: SelectNumberFormat

Req 1: Total spending 2013 ‐ 2016

#10: SelectAccountingformat with zerodecimal placesand click OK