> Select empty columns and rows and deletes them to make the table retable by pivot table.
>Convert the table to an “excel table” by clicking anywhere within the table and pressing CTRL+T, then OK/Enter: table will now look like Figure 2.
Figure 2.
Clean data and prepared for PivotTable and easy to manipulate or filter.
Changing the table to Figure 2 format makes it easy to filter and manipulate the data record by clicking the arrow by the column heading (label).
Figure 2A (Figure 2 Revised).
Figure 2 with names revised.
Converting the table into an Excel table allows changes to reflect on the corresponding Pivot table. Click anywhere within the table and click on Insert, then select Pivot table, and choose FROM Table/Range: By selecting any cell on the table, the entire range of the Excel table will automatically be selected without the need manually to select the entire table range, this because we initially created the “Excel table” using the Crt+T key.
Choose where the Pivot table will be imported to (New Sheet or the Existing Sheet). Click anywhere on the same sheet where the pivot table is located; that is when the table has to be on the same sheet as the original datasets. The Pivot table provides the tooling to perform aggregation by dragging field labels (column heading) to the summation (∑) section right below.
Changes can be made to the Pivot table by dragging the field to the respective section below the “PivotChat Fields” to obtain the desired result. This section helps to make changes to the resultant pivot table: For example, the “Filter” provides features that enable the filtering of records and categories for graphs, and sum (∑) can aggregate the amount or counts.
!!!Feel free to move fields around to get a different perspective on the results: exploring is the best way to learn!!!
Note that any field box selected will show on the Pivot table. The “Rows” and “∑ Values” consolidate values for each categorifies: drag column names to ,” ∑ Values,” and “Columns” to see various results. Drag to replace back to the “PivotTables Fields” if not needed.
Figure 3
Shows how much did each person spend each month.
Figure 4.
Buyers and Total amount spent throughout the periods.
Select anywhere in your Pivot table and click Insert on the toolbar, then select recommend chart to select a befitting Chart for the table.
Right-click on the Pie chart and select data “Add Data Labels,” then choose “Add data Callouts” for percentage representation or “Add Data Labels” for actual values representing tables as it shows on the pivot table.
Figure 5
Graph from Pivot table
Updating Values
If for some reason the value within the original table gets updated, we need to right-click on the pivot table and refresh it, which will automatically update both the pivot table and the graph.
Figure 6
Updating table and graph.
Adding Record to Existing table
Now “Dad” has been added to the record and the Pivot table and graph need to be updated.
Just right-click the pivot table and refresh, and both the table and the graph will automatically be updated.
Figure 7
New record added to the table.
Using the Filter
By dragging the “Month” field and placing it in the filter section, particular months can be selected to view interactively view from the graph by clicking on the arrow by the funnel.
Figure 8
Using the filter to be selective of the month
Interactive Dashboard
With the same approach of creating a single chart as shown in previous Figures, multiple Pivot charts can be added with their respective interactive chart to create a dashboard consisting of different metrics or measures of interest.
Figure 9
Dashboard
