• Teaching
    • Montessori Teacher Training 🔥
    • Yoga Teacher Training
  • Coding
    • Full Stack Development Course
    • Data Science Course
    • Python Programming Course
    • Software Testing Course
    • Architecting on AWS
  • Commerce
    • PwC Edge : Strategic Accounting and Finance Programme
    • ACCA Course
    • SAP Fico Course
    • Practical Accounting Course
    • SAP MM Course
    • HR Management Course in Kerala
  • Technical
    • Structural Design Course
    • Embedded Systems Software Engineering in Kerala
    • MEP Course
    • MEP QS Course
    • BIM Training Course
    • Quantity Survey Course
  • Finacademy
    • Forex Trading Course
    • Stock Market Course
    • Mutual Funds Course in Kerala
  • Digital Marketing
    • Digital Marketing Course
    • Performance Marketing Course
  • Language Learning
    • Spoken English Course
    • German Language Course
    • IELTS Exam Coaching
  • Exam Preparation
    • Kerala PSC Coaching
    • Kerala PSC LGS Coaching
    • KAS Coaching
    • Kerala PSC LDC Coaching
    • Kerala PSC LSGS Coaching
    • KPSC Industries Extension Officer Coaching
    • KPSC Technical Mechanical Coaching
    • KPSC Technical Electrical Coaching
    • Kerala PSC Technical Exam Preparation
    • KPSC Technical Civil Coaching
    • SBI PO Coaching
    • IBPS Clerk Coaching
    • IBPS PO Coaching
    • IBPS SO Coaching
    • Bank Exam Coaching
    • Lecturer in DIET Course
    • Kpsc HSA Coaching
    • KPSC LP UP Assistant Coaching
    • EMRS Coaching
    • KTET Coaching
    • SSC Coaching
    • SSC JE Coaching
    • SSC CGL Coaching
    • SSC CHSL Coaching
    • SSC MTS Coaching
    • UPSC Coaching
  • Health Care
    • DHA Course
    • OET Coaching
  • Other Exams
    • GATE Coaching
    • CAT Coaching
    • MAT Online Coaching
    • KMAT Online Coaching
Entri Blog
No Result
View All Result
Friday, July 11, 2025
  • Kerala PSC
    • Kerala PSC General
      • Kerala PSC Exams
      • KAS Exam
      • Kerala PSC VEO Notification
      • Kerala Police SI
      • Kerala PSC LDC Notification
      • Kerala PSC Village Field Assistant Notification
      • Kerala PSC LD Typist Notification
      • Kerala PSC Food Safety Officer
      • Kerala PSC Excise Inspector Notification
      • Kerala PSC BDO Notification
      • Kerala PSC CPO Notification
      • Kerala PSC LGS Notification
      • Fire and Rescue Officer
      • Women Fire and Rescue Officer
    • Kerala PSC Teaching
      • Kerala PSC HSA
      • Kerala PSC HSST
      • KTET
      • LP/UP Assistant
      • KVS
      • Railway Teacher
    • Kerala PSC Technical
      • LSGD AE
      • Draftsman/Overseer
      • IEO
      • ATP
      • Polytechnic Lecturer
      • AE Electronics
      • Assistant Engineer
  • Banking
    • IBPS PO Notification
    • IBPS Clerk Notification
    • SBI PO Notification
    • SBI Clerk Notification
    • SBI SO Notification
    • SBI Apprentice Notification
    • Canara Bank PO Notification
    • Indian Bank PO Notification
    • RBI Assistant Notification
    • RBI Office Attendant Notification
    • IBPS RRB Notification
    • IBPS RRB Office Assistant Notification
  • Skilling
    • Coding
    • Spoken English
    • Stock Market
      • Share Market Today News
      • Share Market Analysis
    • Digital Marketing
    • Accounting Course
    • SAP
    • OET
    • German
    • IELTS
    • Quantity Survey
    • MEP
    • Structural Design
    • Montessori
    • Yoga Teaching
    • Oil and Gas
  • Courses
    • Data Science Course
      • Data Science Malayalam
      • Data Science Course in Tamil
      • Data Science Course in Telugu
      • Data Science Training in Kochi
      • Data Science Training in Trivandrum
      • Data Science Course in Calicut
      • Data Science Training in Thrissur
    • Data Analytics Course
      • Data Analytics Course in Malayalam
      • Data Analytics Course in Tamil
      • Data Analytics Course in Telugu
    • BIM Course in Kerala
    • Quantity Surveying Course
      • Quantity Surveyor Course in Tamil
      • Quantity Surveying Course in Telugu
      • Quantity Surveyor Course in Hyderabad
      • Quantity Surveying Course in Chennai
      • Quantity Surveying Course in Vijayawada
      • Quantity Surveying Course in Guntur
      • Quantity Surveying Course in Warangal
      • Quantity Surveying Course in Karimnagar
    • Structural Design Course
      • Steel Structure Design Course
      • RCC Design Course
    • MEP Course
      • MEP Quantity Surveying Course
      • MEP Design Course in Kerala
      • HVAC Course
      • MEP Course in Kochi
    • Embedded System Course
    • Full Stack Developer Course
      • Full Stack Development Malayalam
      • Full Stack Development Hindi
      • Full Stack Development Tamil
      • Full Stack Development Telugu
      • Full Stack Development Kannada
    • Stock Market Course
      • Stock Market Course in Malayalam
      • Stock Market Course in Tamil
      • Stock Market Course in Kannada
      • Options Trading Course
    • Mutual Funds Course
    • Forex Trading Course
    • Spoken English Course
      • Spoken English Course in Malayalam
      • Spoken English Course in Hindi
      • Spoken English Course in Telugu
      • Spoken English Course in Tamil
      • Spoken English Course in Kannada
    • Python Programming Course
    • AI Powered Flutter
    • Ui Ux Designer Course
    • Software Testing Course
      • Software Testing Course in Malayalam
      • Software Testing Course in Tamil
      • Software Testing Course in Telugu
    • AWS Solutions Architect Associate Course
    • PwC Edge : Strategic Accounting and Finance Programme
    • Practical Accounting Course
      • Accounting Course in Malayalam
      • Accounting Course in Tamil
      • Tally Course
      • Taxation Course
      • UAE Accounting
      • GST Course
      • Zoho Books Training
      • GST Practitioner Course in Kerala
      • GCC VAT Course
    • ACCA Training Online
      • ACCA Course in Kerala
    • SAP Training Courses
      • SAP FICO Course
      • SAP MM Course
      • SAP SD Training
      • SAP FICO Course in Tamil
      • SAP MM Course in Tamil
    • HR Management Course
    • Performance Marketing Course
    • Mern Stack Developer Course
      • Full Stack Developer Course in Kochi
      • Full Stack Developer Course in Trivandrum
      • Full Stack Developer Course in Calicut
      • Full Stack Developer Course in Pune
      • Full Stack Developer Course in Bangalore
      • Full Stack Developer Course in Hyderabad
      • Full Stack Developer Course in Chennai
      • Full Stack Developer Course in Indore
      • Full Stack Developer Course in Jaipur
      • Full Stack Developer Course in Coimbatore
    • Digital Marketing Training
      • Digital Marketing Course in Kochi
      • Digital Marketing Course in Trivandrum
      • Digital Marketing Course in Calicut
      • Digital Marketing Course in Kollam
      • Digital Marketing Course in Thrissur
      • Digital Marketing Course in Kottayam
      • Digital Marketing Course in Kannur
    • Montessori Teacher Training
      • Montessori Teacher Training in Calicut
      • Montessori Teacher Training in Kochi, Ernakulam
      • Montessori Teacher Training in Trivandrum
      • Montessori Teacher Training in Kollam
      • Montessori Teacher Training in Malayalam
      • Montessori Teacher Training in Kannada
    • Other Courses
      • German Language Course
      • OET Coaching
        • OET for Pharmacist
        • OET for Nurses
      • DHA Course
      • MOH Exam Coaching
      • Nurses Recruitment Abroad
      • FMGE Coaching
      • ChatGPT Course
      • Forex Trading Course
      • Yoga Teacher Training Course
      • Diploma in Early Childhood Care and Education
      • Business Administration & Hospital Management Course
      • Hospital and Healthcare Administration
      • AI for Teachers Course
  • Others
    • Railway
      • RRB Technician Notification
      • RRB JE Notification
      • RRB ALP Notification
      • RRB NTPC Notification
      • RRB RPF Notification
      • RRB RPF SI Notification
      • RRB RPF Constable Notification
      • RRB Group D Notification
      • RRB RPSF SI Notification
      • RRB SSE Notification
    • SSC
      • SSC CPO Notification
      • SSC MTS Notification
      • SSC CGL Notification
      • SSC CHSL Notification
      • SSC GD Constable Notification
      • SSC Stenographer Notification
      • SSC JE Notification
      • SSC CAPF Notification
      • SSC JHT Notification
      • Delhi Police Constable Notification
      • Delhi Police SI Notification
    • GATE
    • MAT
    • KMAT
      • KMAT Kerala 2024
    • TET
      • APTET
      • CTET
      • DSSSB
      • Karnataka TET
      • Kerala TET
        • KTET Notification
      • KVS
      • MPTET
      • SUPER TET
      • TNTET
      • TSTET
      • UPTET
    • UPSC
      • UPSC CAPF Notification
      • UPSC CDS Notification
      • UPSC NDA Notification
      • UPSC Civil service Notification
  • Free Learn
    • Free Courses
      • Free Coding Courses
        • C Langauge Courses
        • Free Data Science Courses
        • Free Python Programming Courses
        • Free Full Stack Developer Courses
      • Free Accounting Courses
      • Free Digital Marketing Courses
        • Free SEO Courses
        • Web Development for Marketers
      • Engineering Upskilling Courses
      • Free Hospital Administration Courses
      • Free OET Training
      • Free SAP Courses
      • Free German Courses
      • Free IELTS Courses
      • Free Teachers Training Courses
      • Free Trading Courses
        • Free Stock Market Courses in Malayalam
        • Free Options Trading Course Malayalam
        • Free Forex Trading Courses Malayalam
        • Free Personal Finance Courses Malayalam
    • Question Answer
    • Aptitude Questions
    • Webinars
  • Test Prep
    • Kerala PSC Coaching
      • KPSC Degree Level Exam Preparation
      • Kerala PSC 12th Level Exam Preparation
      • Kerala PSC 10th Level Exam Preparation
      • KAS Coaching
      • Kerala PSC LDC Coaching
      • Kerala PSC LD Typist Coaching
      • Kerala PSC Fireman Coaching
      • KSFE Assistant Exam Preparation
      • Secretariat Office Attendant Coaching
      • Secretariat Assistant Coaching
      • Kerala PSC SI Exam Coaching
      • Khadi Board LDC Exam Preparation
      • Devaswom Board LDC Coaching
      • University LGS Exam Preparation
      • University Assistant Exam Coaching
      • KPSC Scientific Officer Exam Coaching
      • KPSC Probation Officer Grade II
      • KPSC Food Safety Officer Coaching
      • KWA Sanitary Chemist Coaching
      • KPSC Diary Farm Instructor Exam Coaching
      • Kerala PSC KWA Lab Assistant Exam Coaching
      • Kerala PSC ICDS Supervisor Exam Coaching
      • KPSC Finger Print Searcher Coaching
      • Kerala PSC Welfare Officer Gr. II Exam Coaching
      • KPSC Assistant Dental Surgeon Coaching
    • KPSC Teaching Exams
      • HSA Exam Preparation
      • KPSC HSA Social Science Coaching
      • KPSC HSA Physical Science Coaching
      • KPSC HSA English Coaching
      • KPSC Junior Language Teacher Hindi
      • KTET Coaching
      • SET Coaching
      • LP/UP Assistant Exam Coaching
      • HSST Exam Preparation
      • HSST Commerce Exam Coaching
      • Kerala PSC Lecturer in Diet Coaching
      • KVS PGT/TGT Coaching
    • Kerala PSC Technical Exams Coaching
      • Assistant Engineer Electrical Exam Coaching
      • Assistant Engineer Electronics Coaching
      • Assistant Engineer Mechanical Coaching
      • Kerala PSC Industries Extension Officer Coaching
      • KPSC LSGD AE Exam Coaching
      • KPSC Civil Engineering Exam Coaching
      • KPSC Mechanical Engineering Exam Coaching
      • KPSC Electrical Engineering Exam Coaching
      • KPSC Electronics Engineering Exam Coaching
      • KPSC Technical Computer Science Coaching
      • KPSC Tradesman Turning Coaching
      • Kerala PSC Overseer Exam Coaching
      • KPSC Assistant Town Planner Coaching
      • Lecturer in Polytechnic Electronics Coaching
    • Teaching Exams Coaching
      • CSIR NET Exam Coaching
      • EMRS Coaching
      • CTET Exam Online Coaching
    • IELTS Training Online
    • Bank Exam Coaching Online
      • SEBI Grade A Coaching
      • IBPS SO Online Coaching
      • IBPSC PO Online Coaching
      • IBPSC Clerk Online Coaching
      • SBI PO Coaching
      • SBI Clerk Online Coaching
      • SBI Apprentice Class
      • SBI SO Online Coaching
      • RBI Grade B Coaching
      • RBI Assistant Coaching
      • CSEB Exam Coaching
      • IBPS RRB Coaching
      • IBPS RRB PO Coaching
      • Canara Bank Po Exam Coaching
      • Kerala Gramin Bank Coaching
      • Kerala Bank Exam Coaching
        • Kerala Bank Clerk Exam Coaching
        • Kerala Bank Office Attendant Exam Coaching
        • Kerala Bank Assistant Manager Exam Coaching
      • JCI Exam Coaching
      • NABARD Grade A Coaching
      • LIC AAO Coaching Online
    • UPSC Online Coaching
      • UPSC CAPF Online Coaching
    • SSC Online Coaching
      • SSC JE Online Coaching
      • SSC CGL Coaching
      • SSC CGL AAO Coaching
      • SSC CHSL Coaching
      • SSC CGL CBI Inspector Coaching
      • SSC MTS Coaching
      • SSC Stenographer Coaching
      • SSC GD Online Coaching
    • RRB Exams Coaching
      • RRB RPF Exam Coaching
      • RRB NTPC Exam Coaching
      • RRB JE Online Coaching
      • RRB JE Electronics Coaching
      • RRB ALP Exam Coaching
      • RRB Technician Exam Coaching
    • CAT Online Coaching
    • CMAT Online Coaching
    • XAT Exam Coaching
    • GATE Online Coaching
      • GATE CE Online Coaching
      • GATE ME Online Coaching
      • GATE EE Online Coaching
    • NMAT Exam Coaching
      • AAI ATC Jr Executive Coaching
    • Cochin Shipyard Executive Trainee Coaching
    • BEL Probationary Engineer Coaching
    • HPCL Junior Executive Coaching
Free Courses
Entri Blog
  • HTML Tutorial
  • DSA Tutorials
  • Tutorials in Hindi
    • HTML Tutorial for Beginners in Hindi
    • Python Tutorial for Beginners in Hindi
    • GIT and GITHUB Tutorial for Beginners in Hindi
  • Tutorials in Tamil
    • JavaScript Tutorial in Tamil
  • Tutorials in Telugu
    • HTML Tutorial For Beginners in Telugu
    • CSS Tutorial for Beginners in Telugu
    • Bootstrap Tutorial for Beginner in Telugu
  • Tutorials in Kannada
    • HTML Tutorial For Beginners in Kannada
entri exclusive offers entri exclusive offers
Home Articles

Pivot Table in Excel – Introduction, Uses, Example

by Sanvi Mariam
March 21, 2023
in Articles, Data Science and Machine Learning, Entri Skilling
Pivot Table in Excel – Introduction, Uses, Example
Share on FacebookShare on WhatsAppShare on Telegram

Table of Contents

  • What is a Pivot Table?
  • What are the uses of Pivot Tables in Excel?
  • How to make a Pivot Table in Excel
  • How to use Pivot Table in Excel
  • How to refresh a Pivot Table in Excel
  • Pivot Table examples

The pivot table is one of Microsoft Excel’s most powerful and daunting  functions. You can use pivot tables to organize and summarize huge data sets. But, they are also known for being difficult.

You will understand the principles of pivot tables once you have finished reading this essay. You’ll be aware of their internal workings. Also, you’ll discover how to analyze your business data.
This article is for you if you have never made a pivot table or if you can but it seems magical to you.

  Looking for a Data science and Machine learning Career? Explore Here!!

What is a Pivot Table?

A pivot table is a summary of your data presented in the form of a chart that allows you to report on and examine patterns depending on your data. With extensive rows or columns of information that you need to track sums of and quickly compare to one another, pivot tables are especially helpful.

To put it another way, pivot tables interpret the apparently infinite chaos of statistics on your screen. And more particularly, it enables you aggregate your data in multiple ways so you can draw beneficial conclusions more readily.

The “pivot” in a pivot table refers to the ability to spin or pivot the data within the table to see it from various angles. To be clear, when you perform a pivot, you are not increasing, decreasing, or otherwise altering your data. Instead, you are merely rearranging the data to make useful information more visible.

What are the uses of Pivot Tables in Excel?

1: Which of the following algorithms is most suitable for classification tasks?

2: Which metric is generally used to evaluate the performance of a regression model?

3: Which of the following is not a feature scaling method?

4: In machine learning, what is overfitting?

5: Which type of machine learning algorithm is used to predict a continuous output?

    Fill out the form to see the results



    Ever wondered how much you really know? It's time to put your brain to the test!

    The purpose of pivot tables is to provide simple methods for quickly summarizing vast volumes of data. They can be applied to more effectively comprehend, present, and thoroughly evaluate numerical data.

    With this knowledge, you may assist in identifying and responding to unexpected inquiries regarding the data.

    Comparing Sales Totals of Different Products

    Consider that you have a worksheet with information on the monthly sales of three distinct products: product 1, product 2, and product 3. Find out which of the three has been bringing in the most money.

    One approach would be to scan the spreadsheet and, each time product 1 appears, manually add the appropriate sales figure to a running total. Once you have totals for each product, you can repeat the process for products 2 and 3. Simple as pie, right?

    Consider your monthly sales worksheet to have tens of thousands of rows. The amount of time it would take to manually sort through all the essential data is immeasurable.

    With the help of pivot tables, you can quickly and easily total up all of the sales data for products 1, 2, and 3 and determine their individual amounts.

    Pivot Tables Example 1

    Showing Product Sales as Percentages of Total Sales

    When built, pivot tables automatically display the sums for each row or column. Nevertheless, that is not the only figure you may generate automatically.

    Consider creating a pivot table from data that you entered into an Excel sheet as quarterly sales figures for three different products. The pivot table calculates the quarterly sales for each product and displays three totals at the bottom of each column.

    What if, however, you were looking for the proportion of overall business sales that these items’ sales made up as opposed to just their individual sales totals?

    With a pivot table, you may customize each column to show you the percentage of all three column totals rather than simply the column total.

    Let’s assume that sales of three products totaled $200,000. If you want to say that the first product contributed 22.5% of the business sales rather than that it brought in $45,000, you can update a pivot table.

    Simply right-click the cell containing the sales total and choose Display Values As >% of Grand Total to display product sales in a pivot table as percentages of overall sales.

    Pivot Tables Example 2

    Combining Duplicate Data

    In this case, you recently finished redesigning your blog and have to replace numerous URLs. The “view” metrics for individual posts were split across two separate URLs since your blog reporting software didn’t handle the transition correctly.

    You now have two copies of each individual blog post in your spreadsheet. You must add the view totals for each of these duplicates in order to obtain accurate data.

    Pivot Tables Example 3

    You can use a pivot table to summarize your data by blog post title rather than having to manually find and combine all the metrics from the duplicates.

    Suddenly, an automated aggregation of the view metrics from those duplicate posts will take place.

    Getting an Employee Headcount for Separate Departments

    Pivot tables can in handy for automatically calculating things that are difficult to locate in a straightforward Excel table. One of them is counting rows that share a characteristic.

    Let’s imagine, for instance, that you have an Excel document with a list of your employees. The names of the employees’ relevant departments are shown next to their names. This information can be used to build a pivot table that displays the names of all the departments and their respective staff counts.

    The automated features of the pivot table effectively take away your need to manually sort the Excel sheet by department name and count each entry.

    Adding Default Values to Empty Cells

    Not all datasets that you enter into Excel will fill up all of the cells. It’s possible that you have a large number of blank cells that appear confused or require more explanation while you’re waiting for new data to arrive.

    This is where pivot tables come in.

    Pivot table options

    A pivot table can be readily customized to fill blank cells with a default value, such as $0 or TBD (for “to be determined”). When numerous persons are studying the same sheet, being able to easily tag these cells is a useful feature for huge data tables.

    Right-click your pivot table and select PivotTable Options to automatically format the empty cells.

    Check the box next to “Empty Cells As” in the resulting window, then type the text you want to appear when a cell is empty.

    Pivot Table

    🚀 Start Coding Today! Enroll Now with Easy EMI Options. 💳✨

    Equip yourself with in-demand skills to land top-tier roles in the data-driven world.

    Start Learning Now with EMI Options

    How to make a Pivot Table in Excel

    A Pivot Table is thought to be time-consuming and labor-intensive by many individuals. Nevertheless, this is untrue! The technology has been improved by Microsoft over many years, and the summary reports in the most recent Excel versions are both extraordinarily quick and user-friendly. In reality, creating your own summary table only takes a few minutes. And this is how:

    1. Organize your source data

    Organize your data into rows and columns before generating a summary report, and then export your data range as an Excel Table. To do this, pick all of the data, then click Table on the Insert tab.

    Your data range becomes “dynamic” when you use an Excel Table as the source data, which is a really pleasant bonus. In this case, the term “dynamic range” refers to a table that automatically grows and contracts as items are added or removed, so you won’t have to worry about your pivot table not having the most recent data.

    Tips:

    • Give your columns interesting, distinctive titles; they will later serve as the field names.
    • Ensure that your source table has no subtotals, blank rows or columns, or any empty spaces.
    • You can give your source table a name to make it simpler to maintain by selecting the Design tab and entering the name in the Table Name box in the worksheet’s top right corner.

    2. Create a Pivot Table

    Go to the Insert tab > Tables group > PivotTable after selecting any cell in the source data table.

    Create Pivot Table

    The Create PivotTable window will open as a result. Check to see that the appropriate table or cell range is highlighted in the Table/Range field. Next, decide where you want your Excel pivot table to be located:

    • A table will be started in a new worksheet at cell A1 if you choose New Worksheet.
    • When you choose an existing worksheet, your table will be inserted at the chosen spot in the worksheet. Click the Collapse Dialog button range-selection-iconin the Location box. To place your table, select the first cell using the Collapse Dialog button.

    Select data pivot table

    After you click OK, a blank Pivot Table is created at the target place, similar to this.

    blank-pivot-table

    Tips:

    • Most of the time, it makes sensible to include a pivot table in a separate worksheet; beginners are strongly advised to do this.
    • When generating a pivot table from data in another worksheet or workbook, use the notation [workbook name] to include the names of the worksheet and workbook. for instance, [Book1] sheet name!range .xlsx] Sheet1!$A$1:$E$20. As an alternative, press the Collapse Dialog button. Use the mouse to pick a table or range of cells in another worksheet by clicking the Collapse Dialog button.
    • A pivot table and pivot chart could be made at the same time. With Excel 2013 and later, select the Insert tab > Charts group, click the arrow next to the PivotChart button, and then select PivotChart & PivoTable to accomplish this. Click the arrow next to PivotTable in XLS 2010 and 2007 before selecting PivotChart.

    Computer Keyboard – Parts, Functions, Quiz

    3. Arrange the layout of your Pivot Table report

    PivotTable Field List refers to the place where you deal with the fields in your summary report. It is divided into the header and body sections and is situated in the worksheet’s right-hand corner:

    • The names of the fields that you can include in your table are listed in the Field Section. The names of the fields match the names of the columns in your source table.
    • The Report Filter area, Column Labels area, Row Labels area, and Values area are all located in the Layout Section. The fields of your table can be rearranged and arranged here.

    pivot-table-layout

    As you make changes to the PivotTable Field List, your table is updated right away.

    How to add a field to Pivot Table

    Choose the checkbox next to the field name in the Field section to add the field to the Layout section.

    add-pivot-table-fields

    By default, Microsoft Excel includes the following fields to the Layout section:

    • There are now non-numerical fields in the Row Labels section;
    • The Values area now includes additional numerical fields;
    • Date and time hierarchies from Online Analytical Processing (OLAP) are added to the Column Labels section.

    How to remove a field from a Pivot Table

    You have two options for deleting a specific field:

    • In the PivotTable pane’s Field section, uncheck the box next to the field’s name.
    • Choose “Remove Field Name” from the context menu when you right-click on the field in your pivot table.

    Remove pivot table Field

    How to arrange Pivot Table fields

    There are three ways you can arrange the fields in the Layout section:

    • Using the mouse, move fields among the four sections of the Layout section. The field can also be moved by clicking and holding the field name in the Field section while dragging it to a different location in the Layout section. This will shift the field from its present location in the Layout section to the new location.

    drag-pivot-table-fields

    • In the Field section, right-click the field name, then choose the location where you wish to add it:

    arrange-pivot-table-fields

    • To pick a field, click on it in the Layout section. Also, this will show the choices that are accessible for that specific field.

    pivot-table-fields-options

    4. Choose the function for the Values field (optional)

    If you put a numeric value field in the Values section of the Field List, Microsoft Excel will automatically apply the Sum function. The Count function is used when you enter text, date, Boolean, or other non-numeric data or empty values in the Values field.

    But, if you like, you can select an alternative summary function. Choose the desired summary function by selecting Summarize Values By from the context menu when using Excel 2013 or later.

    The Summarize Values By option is also included in Excel 2010 and before on the ribbon, under the Calculations group of the Options tab.

    An illustration of a pivot table using the average function is provided below:

    choose-summary-function

    The names of the functions are generally self-explanatory:

    • Sum – calculates the sum of the values.
    • Count – counts the number of non-empty values (works as the COUNTA function).
    • Average – calculates the average of the values.
    • Max – finds the largest value.
    • Min – finds the smallest value.
    • Product – calculates the product of the values.

    Click Summarize Values By > More Options to get more specific functions. The complete list of available summary functions and their in-depth descriptions may be found here.

      Looking for a Data science and Machine learning Career? Explore Here!!

    5. Show different calculations in value fields (optional)

    Another helpful feature offered by Excel pivot tables is the ability to present values in various ways, such as showing totals as percentages or ranking values from smallest to largest and vice versa.

    In Excel 2013 and later, you may use this function named Display Values As by right-clicking the field in the table. This option is also available in the Calculations group of the Settings tab in Excel 2010 and before.

    show-values-as

    Tip: 

    Values of the Show If you add the same field more than once and display, for instance, total sales and sales as a percent of total at the same time, this functionality might prove to be extremely helpful. View a sample of one of these tables.

    This is how Pivot Tables are made in Excel. Now it’s up to you to play about with the fields a little to find the arrangement that works best for your set of data.

    Working with Pivot Table Field List

    The major tool you employ to set up your summary table exactly how you desire is the Pivot Table pane, which is also known as the PivotTable Field List. You might wish to adjust the pane to your preferences to make working with the fields more comfortable.

    Changing the Field List view

    Click the Tools button and select your preferred layout to alter how the sections are shown in the Field List.

    change-pivottable-fields-sections

    By dragging the bar (splitter) that divides the pane from the worksheet, you can also resize it horizontally.

    Closing and opening the Pivot Table pane

    Simply click the Close button (X) in the top right corner of the pane to close the PivotTableField List. It is not as easy to make it appear once more.

    Right-click anywhere in the table, then choose Show Field List from the context menu to bring up the Field List once more.

    show-pivot-table-pane

    Moreover, you can use the Field List button on the Ribbon, which is found in the Display group of the Analyze / Options tab.

    field-list-button

    Using Recommended Pivot Tables

    As you’ve just seen, it’s simple to create a pivot table in Excel. Modern Excel versions go one step further, though, and allow you to create a report that’s specifically tailored to your source data automatically. You only need to make 4 mouse clicks:

    1. Click any cell in the table or cell range that is your source.
    2. Then select Suggested PivotTables from the Insert tab. Based on your data, Microsoft Excel will immediately present a few layouts.
    3. To view a layout’s preview in the Suggested PivotTables dialog box, click a layout.
    4. Click the OK button when you are satisfied with the preview to add a pivot table to a new worksheet.

    recommended-pivot-tables

    How to use Pivot Table in Excel

    Now that you are familiar with the basics, you may explore the groups and choices offered by the PivotTable Tools in Excel 2013 and later by navigating to the Analyze and Design tabs (Options and Design tabs in Excel 2010 and 2007). You can access these tabs by clicking anywhere in your table.

    pivot-table-tools

    Right-clicking on an element gives you access to the features and settings that are applicable to that element.

    Ready to take your data science skills to the next level? Sign up for a free demo today!

    How to refresh a Pivot Table in Excel

    Although a Pivot Table report is linked to your source data, you might be stunned to learn that Excel does not immediately refresh it. Any updates to the data can be obtained manually, or they can be automatically updated when you access the spreadsheet.

    Refresh the Pivot Table data manually

    1. In your table, click anywhere.
    2. Click the Refresh button or press ALT+F5 in the Data group of the Analyze tab (the Options tab in previous iterations).

    The table can also be selected by right-clicking it and selecting Refresh from the context menu.

    refresh-pivot-table

    Click the arrow next to the Refresh button to open the menu, then select Refresh All to update every Pivot Table in your workbook.

    You can check the status after starting a refresh or, if you’ve changed your mind, stop it. Simply click the arrow next to the Refresh button, and then select Refresh Status or Cancel Refresh.

    Refreshing a Pivot Table automatically when opening the workbook

    1. Click Options > Options under the PivotTable group on the Analyze / Options tab.
    2. Choose the Refresh data when opening the file check box on the Data tab of the PivotTable Settings dialog box.

    refresh-pivot-table-automatically

    How to design and improve Pivot Table

    If you wish to perform powerful data analysis, you might want to further enhance your Pivot Table after you have generated it using your source data.

    Go to the Design tab, where you will find a ton of pre-defined styles, to enhance the table’s appearance. To build your own style, select “New PivotTable Style…” from the menu that appears after clicking the More button in the PivotTable Styles gallery.

    In Excel 2013 and later, click on the field you want to change, then select the Field Settings button on the Analyze tab (Options tab in Excel 2010 and 2007). As an alternative, you can use the context menu that appears when you right-click a field to select Field Options.

    How to get rid of “Row Labels” and “Column Labels” headings

    Excel uses the Compact layout by default when you create a Pivot Table. The table headings in this style are “Row Labels” and “Column Labels”. I agree that these titles aren’t particularly informative, especially for beginners.

    By switching from the Compact layout to Outline or Tabular, you may quickly get rid of these ludicrous headings. To do this, select Show in Outline Form or Show in Tabular Form from the Report Layout dropdown menu on the Design ribbon tab.

    switch-pivot-table-layout

    This makes much more sense because it will show the real field names, as you can see in the table on the right.

    remove-row-column-labels

    🚀 Start Coding Today! Enroll Now with Easy EMI Options. 💳✨

    Equip yourself with in-demand skills to land top-tier roles in the data-driven world.

    Start Learning Now with EMI Options

    Pivot Table examples

    The screenshots below show a few different Pivot Table layouts that might be used with the same source data and could guide you in the right direction.

    Pivot Table example 1: Two-dimensional table

    • No Filter
    • Rows: Product, Reseller
    • Columns: Months
    • Values: Sales

    No Filter Rows: Product, Reseller Columns: Months Values: Sales

    Pivot Table example 2: Three-dimensional table

    • Filter: Month
    • Rows: Reseller
    • Columns: Product
    • Values: Sales

    This Pivot Table lets you filter the report by month.

    pivot-table-example2

    Pivot Table example 3: One field is displayed twice – as total and % of total

    • No Filter
    • Rows: Product, Reseller
    • Values: SUM of Sales, % of Sales

    The overall sales and the sales as a percentage of the total are both shown in this summary report.

    pivot-table-example3

      Looking for a Data science and Machine learning Career? Explore Here!!

    Related Links
    Pivot Table in Excel Top Excel Interview Questions
    Basic Excel Formulas and Functions Keyboard Shortcuts in Excel
    Advanced Excel Formulas Six Sigma
    Share79SendShare

    Sanvi Mariam

    Related Posts

    Kerala PSC Assistant Engineer Shortlist 2025
    Articles

    Kerala PSC Assistant Engineer Shortlist 2025: Check PDF, Link

    July 11, 2025
    What Is an HR Budget Components & Best Practices
    Articles

    What Is an HR Budget? Components & Best Practices

    July 10, 2025
    (No.) Books Every HR Professional Should Read
    Articles

    (No.) Books Every HR Professional Should Read

    July 10, 2025
    Next Post
    World Sparrow Day 2023 - Theme, Quotes, Importance, Quiz

    World Sparrow Day 2023 - Theme, Quotes, Importance, Quiz

    More to Explore

    1. What is Data Interpretation? Methods and Benefits
    2. How Apple Uses AI, Data Science, And ML
    3. How Netflix Uses AI, Data Science, And ML
    4. What is the Scope for Data Science in Kerala
    5. What is Data Modeling? Basic Concepts and Types
    6. How to Build a Career in Data Science and Analytics?
    7. Naive Bayes Classifier in Machine Learning
    8. 100 Machine Learning Interview Questions and Answers

    Free Tutorials For You

    1. SQL Tutorial for Beginners PDF
    2. Python Tutorial for Beginners in Hindi
    3. GIT and GITHUB Tutorial for Beginners in Hindi
    4. HTML Exercises to Practice
    5. JavaScript Tutorial in Tamil
    6. DSA Practice Series
    7. Microsoft Excel Malayalam Tutorial
    8. Learn Bootstrap in Tamil
    9. Introduction to CSS in Malayalam
    10. Introduction to JavaScript in Malayalam
    11. Java Programming Notes PDF
    12. Introduction to HTML in Malayalam

    Data Science & Python Training in Different Cities

    1. Data Science Course in Kochi
    2. Data Science Course in Trivandrum
    3. Data Science Course in Calicut
    4. Data Science Course in Thrissur
    5. Python Training in Kochi
    6. Python Training in Trivandrum
    7. Python Training in Calicut

    More to Learn

    1. Top 200 Data Engineer Interview Questions & Answers
    2. Top 12 Data Science Final Year Project Ideas
    3. Salary of Data Scientist – State Wise in India
    4. Top 100 Data Science Interview Questions and Answers
    5. Exploratory Data Analysis Techniques: Know the Difference
    6. Data Science Vs Data Analytics
    7. Artificial Intelligence and Machine Learning
    8. What is Logistic Regression in Machine Learning?
    9. Understanding Machine Learning Basics

    Courses

    • Data Science Course
    • Full Stack Developer Course
    • Software Testing Course
    • Data Science Course in Malayalam
    • Full Stack Developer Course in Malayalam
    • Full Stack Developer Course in Hindi
    • Full Stack Developer Course in Tamil
    • Full Stack Developer Course in Telugu
    • Full Stack Developer Course in Kannada
    • Python Programming Course
    • Practical Accounting Course
    • Structural Design Course
    • Quantity Surveying Course
    • Embedded Systems Course in Kerala
    • BIM Course
    • MEP Course
    • MEP QS Course
    • Stock Market Course
    • Stock Market Course in Malayalam
    • Stock Market Course in Tamil
    • Stock Market Course in Kannada
    • Forex Trading Course
    • Mutual Funds Course
    • PwC Edge : Strategic Accounting and Finance Programme
    • SAP FICO Training
    • SAP MM Training
    • SAP SD Training
    • ACCA Online Course
    • HR Management Course

    Company

    • Become a teacher
    • Login to Entri Web

    Spoken English Courses

    • Spoken English Course
    • Spoken English Course in Malayalam
    • Spoken English Course in Hindi
    • Spoken English Course in Tamil
    • Spoken English Course in Telugu
    • Spoken English Course in Kannada
    • Spoken English Course for Housewives
    • Spoken English Course for Working Professionals
    • Spoken English Course for School Students
    • Spoken English Course for College Students
    • Spoken English Course for Job Seekers
    • AI Powered Spoken English Course

    Quick Links

    • Explore Free Courses
    • Aptitude Questions
    • Articles
    • Videos
    • Entri Daily Quiz Practice
    • Current Affairs & GK
    • News Capsule – eBook
    • Preparation Tips
    • Kerala PSC Gold
    • Entri Skilling
    • Kerala PSC

    Other Courses

    • OET Coaching Classes
    • DHA Course
    • Nurse Recruitment Abroad
    • Montessori Teachers Training
    • Performance Marketing Course
    • German Language Course
    • Yoga Teacher Training Course
    • Digital Marketing Course
    • Options Trading Course
    • German Language A1 Course
    • German Language A2 Course
    • German Language B1 Course
    • German Language B2 Course
    • Diploma in Early Childhood Care and Education
    • Hospital and Healthcare Administration
    • Business Administration & Hospital Management
    • Hospital and Healthcare Administration

    Popular Exam

    • IBPS Exam
    • SBI Exam
    • Railway RRB Exam
    • Kerala PSC
    • Tamil Nadu PSC
    • Telangana PSC
    • Andhra Pradesh PSC
    • MPPSC
    • UPPSC
    • Karnataka PSC
    • Staff Selection Commission Exam
    • GATE

    © 2023 Entri.app - Privacy Policy | Terms of Service

    No Result
    View All Result
    • Kerala PSC
      • Kerala PSC General
        • Kerala PSC Exams
        • KAS Exam
        • Kerala PSC VEO Notification
        • Kerala Police SI
        • Kerala PSC LDC Notification
        • Kerala PSC Village Field Assistant Notification
        • Kerala PSC LD Typist Notification
        • Kerala PSC Food Safety Officer
        • Kerala PSC Excise Inspector Notification
        • Kerala PSC BDO Notification
        • Kerala PSC CPO Notification
        • Kerala PSC LGS Notification
        • Fire and Rescue Officer
        • Women Fire and Rescue Officer
      • Kerala PSC Teaching
        • Kerala PSC HSA
        • Kerala PSC HSST
        • KTET
        • LP/UP Assistant
        • KVS
        • Railway Teacher
      • Kerala PSC Technical
        • LSGD AE
        • Draftsman/Overseer
        • IEO
        • ATP
        • Polytechnic Lecturer
        • AE Electronics
        • Assistant Engineer
    • Banking
      • IBPS PO Notification
      • IBPS Clerk Notification
      • SBI PO Notification
      • SBI Clerk Notification
      • SBI SO Notification
      • SBI Apprentice Notification
      • Canara Bank PO Notification
      • Indian Bank PO Notification
      • RBI Assistant Notification
      • RBI Office Attendant Notification
      • IBPS RRB Notification
      • IBPS RRB Office Assistant Notification
    • Skilling
      • Coding
      • Spoken English
      • Stock Market
        • Share Market Today News
        • Share Market Analysis
      • Digital Marketing
      • Accounting Course
      • SAP
      • OET
      • German
      • IELTS
      • Quantity Survey
      • MEP
      • Structural Design
      • Montessori
      • Yoga Teaching
      • Oil and Gas
    • Courses
      • Data Science Course
        • Data Science Malayalam
        • Data Science Course in Tamil
        • Data Science Course in Telugu
        • Data Science Training in Kochi
        • Data Science Training in Trivandrum
        • Data Science Course in Calicut
        • Data Science Training in Thrissur
      • Data Analytics Course
        • Data Analytics Course in Malayalam
        • Data Analytics Course in Tamil
        • Data Analytics Course in Telugu
      • BIM Course in Kerala
      • Quantity Surveying Course
        • Quantity Surveyor Course in Tamil
        • Quantity Surveying Course in Telugu
        • Quantity Surveyor Course in Hyderabad
        • Quantity Surveying Course in Chennai
        • Quantity Surveying Course in Vijayawada
        • Quantity Surveying Course in Guntur
        • Quantity Surveying Course in Warangal
        • Quantity Surveying Course in Karimnagar
      • Structural Design Course
        • Steel Structure Design Course
        • RCC Design Course
      • MEP Course
        • MEP Quantity Surveying Course
        • MEP Design Course in Kerala
        • HVAC Course
        • MEP Course in Kochi
      • Embedded System Course
      • Full Stack Developer Course
        • Full Stack Development Malayalam
        • Full Stack Development Hindi
        • Full Stack Development Tamil
        • Full Stack Development Telugu
        • Full Stack Development Kannada
      • Stock Market Course
        • Stock Market Course in Malayalam
        • Stock Market Course in Tamil
        • Stock Market Course in Kannada
        • Options Trading Course
      • Mutual Funds Course
      • Forex Trading Course
      • Spoken English Course
        • Spoken English Course in Malayalam
        • Spoken English Course in Hindi
        • Spoken English Course in Telugu
        • Spoken English Course in Tamil
        • Spoken English Course in Kannada
      • Python Programming Course
      • AI Powered Flutter
      • Ui Ux Designer Course
      • Software Testing Course
        • Software Testing Course in Malayalam
        • Software Testing Course in Tamil
        • Software Testing Course in Telugu
      • AWS Solutions Architect Associate Course
      • PwC Edge : Strategic Accounting and Finance Programme
      • Practical Accounting Course
        • Accounting Course in Malayalam
        • Accounting Course in Tamil
        • Tally Course
        • Taxation Course
        • UAE Accounting
        • GST Course
        • Zoho Books Training
        • GST Practitioner Course in Kerala
        • GCC VAT Course
      • ACCA Training Online
        • ACCA Course in Kerala
      • SAP Training Courses
        • SAP FICO Course
        • SAP MM Course
        • SAP SD Training
        • SAP FICO Course in Tamil
        • SAP MM Course in Tamil
      • HR Management Course
      • Performance Marketing Course
      • Mern Stack Developer Course
        • Full Stack Developer Course in Kochi
        • Full Stack Developer Course in Trivandrum
        • Full Stack Developer Course in Calicut
        • Full Stack Developer Course in Pune
        • Full Stack Developer Course in Bangalore
        • Full Stack Developer Course in Hyderabad
        • Full Stack Developer Course in Chennai
        • Full Stack Developer Course in Indore
        • Full Stack Developer Course in Jaipur
        • Full Stack Developer Course in Coimbatore
      • Digital Marketing Training
        • Digital Marketing Course in Kochi
        • Digital Marketing Course in Trivandrum
        • Digital Marketing Course in Calicut
        • Digital Marketing Course in Kollam
        • Digital Marketing Course in Thrissur
        • Digital Marketing Course in Kottayam
        • Digital Marketing Course in Kannur
      • Montessori Teacher Training
        • Montessori Teacher Training in Calicut
        • Montessori Teacher Training in Kochi, Ernakulam
        • Montessori Teacher Training in Trivandrum
        • Montessori Teacher Training in Kollam
        • Montessori Teacher Training in Malayalam
        • Montessori Teacher Training in Kannada
      • Other Courses
        • German Language Course
        • OET Coaching
        • DHA Course
        • MOH Exam Coaching
        • Nurses Recruitment Abroad
        • FMGE Coaching
        • ChatGPT Course
        • Forex Trading Course
        • Yoga Teacher Training Course
        • Diploma in Early Childhood Care and Education
        • Business Administration & Hospital Management Course
        • Hospital and Healthcare Administration
        • AI for Teachers Course
    • Others
      • Railway
        • RRB Technician Notification
        • RRB JE Notification
        • RRB ALP Notification
        • RRB NTPC Notification
        • RRB RPF Notification
        • RRB RPF SI Notification
        • RRB RPF Constable Notification
        • RRB Group D Notification
        • RRB RPSF SI Notification
        • RRB SSE Notification
      • SSC
        • SSC CPO Notification
        • SSC MTS Notification
        • SSC CGL Notification
        • SSC CHSL Notification
        • SSC GD Constable Notification
        • SSC Stenographer Notification
        • SSC JE Notification
        • SSC CAPF Notification
        • SSC JHT Notification
        • Delhi Police Constable Notification
        • Delhi Police SI Notification
      • GATE
      • MAT
      • KMAT
        • KMAT Kerala 2024
      • TET
        • APTET
        • CTET
        • DSSSB
        • Karnataka TET
        • Kerala TET
        • KVS
        • MPTET
        • SUPER TET
        • TNTET
        • TSTET
        • UPTET
      • UPSC
        • UPSC CAPF Notification
        • UPSC CDS Notification
        • UPSC NDA Notification
        • UPSC Civil service Notification
    • Free Learn
      • Free Courses
        • Free Coding Courses
        • Free Accounting Courses
        • Free Digital Marketing Courses
        • Engineering Upskilling Courses
        • Free Hospital Administration Courses
        • Free OET Training
        • Free SAP Courses
        • Free German Courses
        • Free IELTS Courses
        • Free Teachers Training Courses
        • Free Trading Courses
      • Question Answer
      • Aptitude Questions
      • Webinars
    • Test Prep
      • Kerala PSC Coaching
        • KPSC Degree Level Exam Preparation
        • Kerala PSC 12th Level Exam Preparation
        • Kerala PSC 10th Level Exam Preparation
        • KAS Coaching
        • Kerala PSC LDC Coaching
        • Kerala PSC LD Typist Coaching
        • Kerala PSC Fireman Coaching
        • KSFE Assistant Exam Preparation
        • Secretariat Office Attendant Coaching
        • Secretariat Assistant Coaching
        • Kerala PSC SI Exam Coaching
        • Khadi Board LDC Exam Preparation
        • Devaswom Board LDC Coaching
        • University LGS Exam Preparation
        • University Assistant Exam Coaching
        • KPSC Scientific Officer Exam Coaching
        • KPSC Probation Officer Grade II
        • KPSC Food Safety Officer Coaching
        • KWA Sanitary Chemist Coaching
        • KPSC Diary Farm Instructor Exam Coaching
        • Kerala PSC KWA Lab Assistant Exam Coaching
        • Kerala PSC ICDS Supervisor Exam Coaching
        • KPSC Finger Print Searcher Coaching
        • Kerala PSC Welfare Officer Gr. II Exam Coaching
        • KPSC Assistant Dental Surgeon Coaching
      • KPSC Teaching Exams
        • HSA Exam Preparation
        • KPSC HSA Social Science Coaching
        • KPSC HSA Physical Science Coaching
        • KPSC HSA English Coaching
        • KPSC Junior Language Teacher Hindi
        • KTET Coaching
        • SET Coaching
        • LP/UP Assistant Exam Coaching
        • HSST Exam Preparation
        • HSST Commerce Exam Coaching
        • Kerala PSC Lecturer in Diet Coaching
        • KVS PGT/TGT Coaching
      • Kerala PSC Technical Exams Coaching
        • Assistant Engineer Electrical Exam Coaching
        • Assistant Engineer Electronics Coaching
        • Assistant Engineer Mechanical Coaching
        • Kerala PSC Industries Extension Officer Coaching
        • KPSC LSGD AE Exam Coaching
        • KPSC Civil Engineering Exam Coaching
        • KPSC Mechanical Engineering Exam Coaching
        • KPSC Electrical Engineering Exam Coaching
        • KPSC Electronics Engineering Exam Coaching
        • KPSC Technical Computer Science Coaching
        • KPSC Tradesman Turning Coaching
        • Kerala PSC Overseer Exam Coaching
        • KPSC Assistant Town Planner Coaching
        • Lecturer in Polytechnic Electronics Coaching
      • Teaching Exams Coaching
        • CSIR NET Exam Coaching
        • EMRS Coaching
        • CTET Exam Online Coaching
      • IELTS Training Online
      • Bank Exam Coaching Online
        • SEBI Grade A Coaching
        • IBPS SO Online Coaching
        • IBPSC PO Online Coaching
        • IBPSC Clerk Online Coaching
        • SBI PO Coaching
        • SBI Clerk Online Coaching
        • SBI Apprentice Class
        • SBI SO Online Coaching
        • RBI Grade B Coaching
        • RBI Assistant Coaching
        • CSEB Exam Coaching
        • IBPS RRB Coaching
        • IBPS RRB PO Coaching
        • Canara Bank Po Exam Coaching
        • Kerala Gramin Bank Coaching
        • Kerala Bank Exam Coaching
        • JCI Exam Coaching
        • NABARD Grade A Coaching
        • LIC AAO Coaching Online
      • UPSC Online Coaching
        • UPSC CAPF Online Coaching
      • SSC Online Coaching
        • SSC JE Online Coaching
        • SSC CGL Coaching
        • SSC CGL AAO Coaching
        • SSC CHSL Coaching
        • SSC CGL CBI Inspector Coaching
        • SSC MTS Coaching
        • SSC Stenographer Coaching
        • SSC GD Online Coaching
      • RRB Exams Coaching
        • RRB RPF Exam Coaching
        • RRB NTPC Exam Coaching
        • RRB JE Online Coaching
        • RRB JE Electronics Coaching
        • RRB ALP Exam Coaching
        • RRB Technician Exam Coaching
      • CAT Online Coaching
      • CMAT Online Coaching
      • XAT Exam Coaching
      • GATE Online Coaching
        • GATE CE Online Coaching
        • GATE ME Online Coaching
        • GATE EE Online Coaching
      • NMAT Exam Coaching
        • AAI ATC Jr Executive Coaching
      • Cochin Shipyard Executive Trainee Coaching
      • BEL Probationary Engineer Coaching
      • HPCL Junior Executive Coaching

    © 2023 Entri.app - Privacy Policy | Terms of Service

    Chat