Computer Basics -13 || Creating pivot charts/tables in Excel.

In our last chapter we have learnt the Adavantages of the Pivot charts/tables over the regular charts in Excel spreadsheets.

Comparison between Simple Chart and Pivot Chart.

Creating Pivot Charts

Creating a Pivot Charts is as simple as we used to generate regular charts in excel. For this, we have to first collect the data in tabular format in an excel sheet as mentioned below table:

ItemActual Cost ( $ in Thousands)Profit ( $ in Thousands)
TV25050
Fridge9020
AC21070
Cooler20010
Fan807.5
Microwave15050
Water Pump15080

Now, follow the steps one by one to get a clear understanding to generate Pivot Chart/Table.

Insert Menu
  1. Select the entire table in the excel sheet which needs to be converted into a Pivot Chart/Table.
  2. Navigate to Insert tab in the Menu bar.
  3. Click on PivotTable drop-down.
  4. Click to Select PivotChart option from the drop-down.
  5. Click OK on “Create PivotTable with PivotChart” dialog box using default selections and values. Still, I am explaining each option for your better understanding.

Create PivotTable with PivotChart Dialog box

Create PivotTable with PivotChart dialog box
  • Select a table or range: Shows Cell range of selected data table. We could customize this range manually or by using the Range picker option present on the right side of the text field.
  • Use an external data source: This option is used to use data table already created outside the excel like SQL, MS Access or any other database management tool.
  • New Worksheet: By default, PivotTable and PivotChart would generate in a new sheet in the same excel document.
  • Existing Worksheet: Sometimes, we need to generate PivotTable/PivotChart in the same sheet where data table present. To achieve this we need to select this option. We also need to give the location, using the Range Picker option where we want to insert a chart.

Note: If we select PivotTable, would be able to create the only Table. However, on selecting a PivotChart option, both PivotTable and PivotChart would be generated.

Once the user clicked on the OK button from the Create PivotTable with PivotChart dialog box with default selections (as in above steps), few components and panel get added to the new sheet as mentioned below:

PivotChart and Table Example

PivotTable Field List

Panel appearing on the right side is divided into two parts.

Choose Fields to add to report: This part would contain all the column headers from the previously selected data table.

  • We just need to select all fields by clicking checkboxes against each column headers like Item, Actual cost, Profit, etc.

Drag fields between areas below: While selecting column headers or fields from the above section we could observe that these fields getting added in this part under various sections.

  • The preview of this selection and placement would get presented in the left side sheet area. It shows PivotTable and PivotChart.
PivotTable Options Description PivotChart Options Description
Report Filter To apply the filter on entire PivotTable or report. Report Filter To apply the filter on entire PivotTable or PivotChart.
Row Labels To display fields as row headers in the PivotTable. Axis Field (Categories) To display the Row Header as the x-axis in the PivotChart.
Column Labels To display fields as columns headers at the top of the report. When we add more than two fields in the “Values” section all the fields automatically get added to this Section. Legend Fields (Series) Use to display the column field as the legends of the chart.
Values To display a summary of numeric data in the PivotTable. Values To display the values in the x-axis of the Chart.

Once we selected all the fields PivotTable and PivotCharts would appear as mentioned below the image. Now we could change the report as per requirements and only for the selected items without regenerating the charts or deleting any data.

PivotChart and PivotTable

Regenerate PivotTable and PivotChart

Suppose we have to regenerate the chart and table only for AC, Fan and TV items. To change the report, click on the Row Labels drop-down in the table and select only the required items. Now your updated report will appear as mentioned below:

PivotTable and PivotChart Formatting

Till now we have learned how to create PivotTable and PivotChart using a simple data table. In the above image we can see on selecting table or chart there are additional sub-menu get to appear in the Menu Panel like Design, Layout, Format, etc.

We could use these options to provide a better look to the tables, charts, change the chart type, Labels, Font color, Background Color, etc. to make our presentation or report more interesting and attractive. A formatted PivotTable with Pie Chart is given below:

Formatted PivotTable with Pie Chart

Related Links:

TestNG:

Java Basics:

Java File Handling:

OOPs Concept:

Java Question And Answer:

Java Programs:

Selenium Tutorials:

JIRA:

Agile:


557total visits,2visits today

Leave a Reply

Your email address will not be published. Required fields are marked *