एक्सेल में पाइवोट टेबल सीखें

This topic has been explained in great detail in the video shown below, to learn this topic just play the video and the notes are mentioned below the video.

एक्सेल में पाइवोट टेबल सीखें

एक्सेल में पाइवोट टेबल सीखें

पाइवोट टेबल के जरिये आप बहुत सारा डेटा एकसाथ अनायलाज करके के बेहतरीन रिपोर्ट्स बना सकते हैं।  पाइवोट तबके को इस्तेमाल करना सीखने के लिए यह वीडियो देखे.


In This video Guru teaches how to create Pivot in Excel to tackle, organize and meaningfully interpret large data in Excel

Use this free online tutorial to learn Microsoft Excel 2007. Click here to start the MS Excel tutorial. If you want to learn Excel online then click here for our Excel tutorial. Click here to learn Excel In Hindi This Excel tutorial has been created to help you learn microsoft excel online. On http://www.myelesson.org you get excel help and online file download facility. This video lesson covers the entire topic in simple and easy to understand step by step approach. ”

Create a PivotTable to analyze worksheet data

Being able to quickly analyze data can help you make better business decisions. But sometimes it’s hard to know where to start, especially when you have a lot of data. PivotTables are a great way to summarize, analyze, explore, and present your data, and you can create them with just a few clicks. PivotTables are highly flexible and can be quickly adjusted depending on how you need to display your results. You can also create PivotCharts based on PivotTables that will automatically update when your PivotTables do.For example, here’s a simple list of household expenses, and a PivotTable based on it:

Household expense data Corresponding PivotTable
Sample household expense data to create a PivotTable with Months, Categories and Amounts Sample PivotTable with Categories in the Rows section and Months in the columns section

Next, here’s a PivotChart:

Example of an Excel PivotChart

NOTE: The screen shots in this article were taken in Excel 2016. If you have a different version your view might be slightly different, but unless otherwise noted, the functionality is the same.

Discover more Excel training at LinkedIn Learning >

Before you get started

  • Your data should be organized in a tabular format, and not have any blank rows or columns. Ideally, you can use an Excel table like in our example above.
  • Tables are a great PivotTable data source, because rows added to a table are automatically included in the PivotTable when you refresh the data, and any new columns will be included in the PivotTable Fields List. Otherwise, you need to either manually update the data source range, or use a dynamic named range formula.
  • Data types in columns should be the same. For example, you shouldn’t mix dates and text in the same column.
  • PivotTables work on a snapshot of your data, called the cache, so your actual data doesn’t get altered in any way.

Create a PivotTable

If you have limited experience with PivotTables, or are not sure how to get started, a Recommended PivotTable is a good choice. When you use this feature, Excel determines a meaningful layout by matching the data with the most suitable areas in the PivotTable. This helps give you a starting point for additional experimentation. After a recommended PivotTable is created, you can explore different orientations and rearrange fields to achieve your specific results. The Recommended PivotTables feature was added in Excel 2013, so if you have an earlier version, follow the instructions below for how to manually create a PivotTable instead.

Recommended PivotTable Manually create a PivotTable
  1. Click a cell in the source data or table range.
  2. Go to Insert > Tables > Recommended PivotTable.Go to Insert > Recommended PivotTables to have Excel create a PivotTable for you
  3. Excel analyzes your data and presents you with several options, like in this example using the household expense data.Excel Recommended PivotTables dialog
  4. Select the PivotTable that looks best to you and press OK. Excel will create a PivotTable on a new sheet, and display the PivotTable Fields List.
  1. Click a cell in the source data or table range.
  2. Go to Insert > Tables > PivotTable.Go to Insert > PivotTable to insert a blank PivotTableIf you’re using Excel for Mac 2011 and earlier, the PivotTable button is on the Data tab in the Analysis group.Data tab, Analysis group
  3. Excel will display the Create PivotTable dialog with your range or table name selected. In this case, we’re using a table called “tbl_HouseholdExpenses”.Excel Create PivotTable dialog
  4. In the Choose where you want the PivotTable report to be placed section, select New Worksheet, or Existing Worksheet. For Existing Worksheet, you’ll need to select both the worksheet and the cell where you want the PivotTable placed.
  5. If you want to include multiple tables or data sources in your PivotTable, click the Add this data to the Data Model check box.
  6. Click OK, and Excel will create a blank PivotTable, and display the PivotTable Fields list.

Working with the PivotTable Fields list

In the Field Name area at the top, select the check box for any field you want to add to your PivotTable. By default, non-numeric fields are added to the Row area, date and time fields are added to the Column area, and numeric fields are added to the Values area. You can also manually drag-and-drop any available item into any of the PivotTable fields, or if you no longer want an item in your PivotTable, simply drag it out of the Fields list or uncheck it. Being able to rearrange Field items is one of the PivotTable features that makes it so easy to quickly change its appearance.

PivotTable Fields list Corresponding fields in a PivotTable
Example of the Excel PivotTable Fields list dialog Example of a PivotTable and how the Fields correlate to the Fields list.

PivotTable Values

  • Summarize Values ByBy default, PivotTable fields that are placed in the Values area will be displayed as a SUM. If Excel interprets your data as text, it will be displayed as a COUNT. This is why it’s so important to make sure you don’t mix data types for value fields. You can change the default calculation by first clicking on the arrow to the right of the field name, then select the Value Field Settings option.Excel Value Field Settings dialogNext, change the calculation in the Summarize Values By section. Note that when you change the calculation method, Excel will automatically append it in the Custom Name section, like “Sum of FieldName”, but you can change it. If you click the Number Format button, you can change the number format for the entire field.Excel Value Field Settings dialog for Summarize Values By options

    TIP: Since the changing the calculation in the Summarize Values By section will change the PivotTable field name, it’s best not to rename your PivotTable fields until you’re done setting up your PivotTable. One trick is to use Find & Replace (Ctrl+H) >Find what > “Sum of“, then Replace with > leave blank to replace everything at once instead of manually retyping.

  • Show Values AsInstead of using a calculation to summarize the data, you can also display it as a percentage of a field. In the following example, we changed our household expense amounts to display as a % of Grand Total instead of the sum of the values.PivotTable example with Values displayed as a percentage of the Grand TotalOnce you’ve opened the Value Field Setting dialog, you can make your selections from the Show Values As tab.PivotTable Value Field Settings > Show Values As dialog
  • Display a value as both a calculation and percentage.Simply drag the item into the Values section twice, then set the Summarize Values By and Show Values As options for each one.

Refreshing PivotTables

If you add new data to your PivotTable data source, any PivotTables that were built on that data source need to be refreshed. To refresh just one PivotTable you can right-click anywhere in the PivotTable range, then select Refresh. If you have multiple PivotTables, first select any cell in any PivotTable, then on the Ribbon go to PivotTable Tools > Analyze > Data > Click the arrow under the Refresh button and select Refresh All.

Refresh all PivotTables from the Ribbon > PivotTable Tools > Analyze > Data, click the arrow under the Refresh button and select Refresh All.

Deleting a PivotTable

If you created a PivotTable and decide you no longer want it, you can simply select the entire PivotTable range, then press Delete. It won’t have any affect on other data or PivotTables or charts around it. If your PivotTable is on a separate sheet that has no other data you want to keep, deleting that sheet is a fast way to remove the PivotTable.

The video has been made by me and the text is from a microsoft blog

Our Other Services

Read Free Ebooks


Myebook has 1000's of free ebooks for you. On productivity, health, online income, learning, music and many more, it's free of course!

Buy Excel Course


Become an Excel Guru, Buy the best Excel course. Learn on your mobile, tv & laptop without internet. For COD, Call 9752003788

Create Best Looking Resume


At Makecv.org you get to create a amazingly professional looking resume in just 3 steps with our intuitive resume builder!

Download Excel Crash Course

This Excel Crash Course contains the list of topics that cover the most important and used features and formula of Excel . This Excel Crash Course lists 43 major topics in Excel so that you can start using Excel like a pro in the shortest possible time.

Learn Photoshop Web Designing

You can Buy the Photoshop Course to learn Photoshop and start a career in Web Designing and Photo Editing. .


You can Buy the HTML CSS Course to learn HTML CSS and start a career in Web Designing and Photo Editing. .

Download Full list of Excel Shortcuts

This Excel Shortcuts Complete list is to help you become save time and use Excel like a Master level user. Excel is best used when you know the Keyboard shortcuts because the use of keyboard shortcuts in Excel helps you in completing tasks faster.

I have created a Excel Shortcuts complete list for you to use Excel better, this list has 242 excel keyboard shortcuts which means that it covers almost everything that can be done using shortcuts in Excel.

Full List of Excel Formulas

This is a complete alphabetical list of all the Formula in Excel with a description. You can learn all these formulas on www.Myelesson.org

Convert Numbers to Text in Excel

Now easily convert numbers to Text in Excel with this inbuilt sheet from myelesson.org .  Many times we need the amount in figures to be converted into words. This is a typical requirement for writing checks or any other financial reports. Microsoft Excel does not have standard function available for this requirement. Just download this sheet and then you can convert any number to text in Excel.

List of Most Commonly Asked Excel Questions in Interviews

Here is the list of most asked excel related questions in interviews.  Prepare for this excel questions and you would be able to most of the excel interviews. This list of excel questions covers excel formula, excel analysis, excel reporting, excel charts and more.

Myelesson © 2010 - 2014. All rights reserved.

Do You Want to Become a Expert in Excel ?
Buy The Full Excel Course Now
Enter Your Details & We Will Contact You
Great Choice
Thank You