In our last chapter we have learnt the Adavantages of the Pivot charts/tables over the regular charts in Excel spreadsheets.
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:
| Item | Actual Cost ( $ in Thousands) | Profit ( $ in Thousands) |
| TV | 250 | 50 |
| Fridge | 90 | 20 |
| AC | 210 | 70 |
| Cooler | 200 | 10 |
| Fan | 80 | 7.5 |
| Microwave | 150 | 50 |
| Water Pump | 150 | 80 |
Now, follow the steps one by one to get a clear understanding to generate Pivot Chart/Table.
- Select the entire table in the excel sheet which needs to be converted into a Pivot Chart/Table.
- Navigate to Insert tab in the Menu bar.
- Click on PivotTable drop-down.
- Click to Select PivotChart option from the drop-down.
- 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
- 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:
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.
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:
Related Links:
- Computer Basics -1 || Introduction and Structure of Computer.
- Computer Basics -2 || Types of Computers and Usage.
- Computer Basics -3 || What are the different types of Software?
- Computer Basics -4 || Importance of Operating System(OS).
- Computer Basics -5 || Understanding of Number System.
- Computer Basics -6 || Understanding MS-DOS Commands.
- Computer Basics -7 || Important Features of MS-Word.
- Computer Basics -8 || Let’s learn the usage of MS-Excel.
- Computer Basics -9 || Understand and Implement Data Validation in Excel.
- Computer Basics -10 || How to apply Filter in a data set in Excel?
- Computer Basics -11 || Using Charts in place of Data Tables in Excel.
- Computer Basics -12 || Advantages of PivotCharts over Simple Charts in Excel.
- Computer Basics -13 || Creating pivot charts/tables in Excel.
- Abbreviations to Full-Forms in Computer Basics.
TestNG:
- TestNG – 1 || Introduction and benefits of TestNG Framework.
- TestNG – 2 || Installation process and a sample program of TestNG.
- TestNG – 3 || Create and execute multiple Test Cases.
- TestNG – 4 || Let’s understand @Test Annotation and attributes.
- TestNG – 5 || Understand Assertion in TestNG.
- TestNG – 6 || Use of @BeforeMethod and @AfterMethod.
- TestNG – 7 || Use of @BeforeClass and @AfterClass.
- TestNG – 8 || Creation and execution of Test Suites.
- TestNG – 9 || Let’s move deep into the Test Suites.
- TestNG – 10 || Use @BeforeTest and @AfterTest Annotations.
- TestNG – 11 || Groups attribute with @Test Annotation.
- TestNG – 12 || Use of @BeforeGroups & @AfterGroups.
- TestNG – 13 || Use of @BeforeSuite & @AfterSuite.
- TestNG – 14 || DataProvider annotation & attribute.
- TestNG – 15 || DataProvider with parameters.
- TestNG – 16 || Access data from Excel sheet using DataProvider.
- TestNG – 17 || Passing multiple Parameters in testng xml.
- TestNG – 18 || Multiple Browser and Parallel Execution in TestNG.
- TestNG -19 || Concept of Parallel Execution.
- TestNG – 20 || Run TestNG Program using main() method.
Java Basics:
- Basic Java – 1 || Understand Java before start learning JAVA.
- Basic Java – 2 || Variables and Data Types used in JAVA.
- Basic Java – 3 || Understanding Class, Objects, Methods in Java.
- Basic Java – 4 || More on methods(Return Type and Parameters)
- Basic Java – 5 || Methods- Call by Value and Call by Reference in Java.
- Basic Java – 6 || Understanding of Constructor and Destructor in JAVA.
- Basic Java – 7 || Static Variables and Methods.
- Basic Java – 8 || Lets learn about Arrays in Java.
- Basic Java – 9 || Performing multiple operations using Java Operators.
- Basic Java – 10 || Conditions (If and Switch) in JAVA.
- Basic Java – 11 || for and for-each in Java. (Loops Part-1)
- Basic Java – 12 || Alternate looping concepts while and do-while. (Loops Part-2)
- Basic Java – 13 || Decimal values v/s Octal base(8) values in JAVA.
- Basic Java – 14 || Learn about String literals in Java.
- Basic Java – 15 || Runtime User Input using Scanner Class (Part-1).
- Basic Java – 16 || Runtime User Input using BufferedReader Class (Part-2).
- Basic Java – 17 || Runtime User Input using Console Class (Part-3).
- Basic Java – 18 || Difference between break and continue keywords.
- Basic Java – 19 || Sending Email using Java (Part-1).
- Basic Java – 20 || Sending Email with attachment using Java (Part-2).
- Basic Java – 21 || Stack memory and Heap memory in Java.
- Basic Java – 22 || Let’s learn more about String.
- Basic Java – 23 || String, StringBuffer & StringBuilder in Java.
- Basic Java – 24 || Exception Handling using Try Catch.
- File Handling | Reading data from word document(.doc or .docx) in JAVA.
- File Handling | Reading data from Excel files (.xls or .xlsx) using JAVA.
- File Handling | Writing data into an Excel(.XLSX or .XLS) File.
- File Handling | Implement formatting in Excel using Java.
- File Handling | Copy existing data from one workbook to another workbook in Java.
- File Handling | Reading data from PDF file using JAVA.
- File Handling || Traverse folders and subfolders in Java.
- File Handling || Reading and Writing data from a text file.
- File Handling || Multiple file creation using template based input data.
- Framework || Simple example of Key Driven Framework using excel sheet in Selenium(JAVA).
- QnA || How to use Constructors in Abstract class?
- QnA | Difference between Integer and int keywords.
- QnA | Can main method be overloaded?
- QnA | How do I reverse a String/Sentence in Java?
- QnA | Perform Multiplication and Division without * or / or % operators.
- QnA | How do I get the default value of data type?
- QnA | How to split String if it contains period symbol (.) in between?
- Different ways to Reverse a String in Java.
- Copy formatting & style of cells from one sheet to another.
- Getting IP address and Hostname using InetAddress Class.
- User inputs via Command Prompt using arguments of main() method of a class.
- Program for List and ArrayList in Java.
- Useful methods and implementation under Scanner Class.
- Swapping two variable values without using any third variable.
- Difference between int x= 10 and y=010 in Java.
- Parameterized Constructors v/s Setter and Getter function in JAVA.
- Override a Static Method.
- Selenium-12 || Select Class to handle drop-down.
- Selenium-11 || Use of Actions and Action Classes.
- Selenium-10 || Taking Screenshots using Selenium
- Selenium-9 || Understanding WebDriver API.
- Selenium-8 || Implementing Wait(s) in Selenium.
- Selenium-7 || Let’s learn to create complex XPath.
- Selenium-6 || XPath is the best way to locate web elements.
- Selenium-5 || Locating web elements using various type of Locators.
- Selenium-4 || Handling multiple web browsers.
- Selenium-3 || First program using Selenium Web Driver.
- Selenium-2 || Let’s learn Selenium IDE.
- Selenium-1 || Understanding Selenium and Selenium WebDriver.
- JIRA Tutorials-2 || Implement Search and Filter on JIRA Issues.
- JIRA Tutorials-1 || Basic understanding of JIRA.
