Education Portal for Students in India

What is the use of pivot table in Excel?

– A PivotTable is an interactive way to quickly summarize large amounts of data. You can use a PivotTable to analyze numerical data in detail, and answer unanticipated questions about your data. A PivotTable is especially designed for:

Querying large amounts of data in many user-friendly ways. Subtotaling and aggregating numeric data, summarizing data by categories and subcategories, and creating custom calculations and formulas. Expanding and collapsing levels of data to focus your results, and drilling down to details from the summary data for areas of interest to you. Moving rows to columns or columns to rows (or “pivoting”) to see different summaries of the source data. Filtering, sorting, grouping, and conditionally formatting the most useful and interesting subset of data enabling you to focus on just the information you want. Presenting concise, attractive, and annotated online or printed reports.

For example, here’s a simple list of household expenses on the left, and a PivotTable based on the list to the right:

Sales data	Corresponding PivotTable

For more information, see Create a PivotTable to analyze worksheet data,

What is PivotTable formula?

– You can create formulas only in reports that are based on a non-OLAP source data. You cannot use formulas in reports that are based on an OLAP database. When you use formulas in PivotTables, you should know about the following formula syntax rules and formula behavior:

• PivotTable formula elements In formulas that you create for calculated fields and calculated items, you can use operators and expressions as you do in other worksheet formulas. You can use constants and refer to data from the report, but you cannot use cell references or defined names. You cannot use worksheet functions that require cell references or defined names as arguments, and you cannot use array functions.
• Field and item names Excel uses field and item names to identify those elements of a report in your formulas. In the following example, the data in range C3:C9 is using the field name Dairy, A calculated item in the Type field that estimates sales for a new product based on Dairy sales could use a formula such as =Dairy * 115%, Note: In a PivotChart, the field names are displayed in the PivotTable field list, and item names can be seen in each field drop-down list. Don’t confuse these names with those you see in chart tips, which reflect series and data point names instead.
• Formulas operate on sum totals, not individual records Formulas for calculated fields operate on the sum of the underlying data for any fields in the formula. For example, the calculated field formula =Sales * 1.2 multiplies the sum of the sales for each type and region by 1.2; it does not multiply each individual sale by 1.2 and then sum the multiplied amounts. Formulas for calculated items operate on the individual records. For example, the calculated item formula =Dairy *115% multiplies each individual sale of Dairy times 115%, after which the multiplied amounts are summarized together in the Values area.
• Spaces, numbers, and symbols in names In a name that includes more than one field, the fields can be in any order. In the example above, cells C6:D6 can be ‘April North’ or ‘North April’, Use single quotation marks around names that are more than one word or that include numbers or symbols.
• Totals Formulas cannot refer to totals (such as, March Total, April Total, and Grand Total in the example).
• Field names in item references You can include the field name in a reference to an item. The item name must be in square brackets — for example, Region, Use this format to avoid #NAME? errors when two items in two different fields in a report have the same name. For example, if a report has an item named Meat in the Type field and another item named Meat in the Category field, you can prevent #NAME? errors by referring to the items as Type and Category,
• Referring to items by position You can refer to an item by its position in the report as currently sorted and displayed. Type is Dairy, and Type is Seafood, The item referred to in this way can change whenever the positions of items change or different items are displayed or hidden. Hidden items are not counted in this index. You can use relative positions to refer to items. The positions are determined relative to the calculated item that contains the formula. If South is the current region, Region is North ; if North is the current region, Region is South, For example, a calculated item could use the formula =Region * 3%, If the position that you give is before the first item or after the last item in the field, the formula results in a #REF! error.

Why VLOOKUP is used in Excel?

Want more? – VLOOKUP function Quick reference card: VLOOKUP refresher Quick reference card: VLOOKUP troubleshooting tips When you need to find information in a large spreadsheet, or you are always looking for the same kind of information, use the VLOOKUP function.

VLOOKUP works a lot like a phone book, where you start with the piece of data you know, like someone’s name, in order to find out what you don’t know, like their phone number. So, as an example, I’ll enter part numbers, the thing I know, and find out Prices, the thing I don’t know. To do that, I’ll click the cell where I want to see the Prices, I’ll enter an = sign, VLOOKUP, and parentheses.

These parentheses will contain a set of arguments, and an argument is just a piece of data that the function needs in order to run. I’ll enter H2 as the first argument, because that is where I’ll type the part numbers. Follow that with a comma, and then, I’ll enter the range of cells that contains the data I want to search.

• That’s this block of data here.
• The part numbers start in cell B3, and if I scroll down, you can see the status values end at cell E52.
• So, I’ll enter B3, a colon, and E52, then I’ll type another comma.
• And you need to do that because the functions won’t work without the colons and commas.
• Next, I’ll type the number 3.

This tells VLOOKUP that the values I want to see are in the third column from the left in the range of cells I want to search. In other words, it’s the third column over from the part numbers, the data I know. Another comma, and I enter FALSE, because that gives me an exact match between part number and price.

And don’t worry, I’ll explain how that works later. When I press Enter to tell Excel I am done, you can see I get an error message because I haven’t entered a value in cell H2. But, when I enter a part number, I get a price. So what just happened? I told Excel, “Here is a value in the left-hand column of my data.

Now look through this range of cells, and in the third column to the right, find the value on the same row.” A lot like a phone book. So up next, I’ll explain each of the arguments – the values inside the parentheses – along with rules for using VLOOKUP,

What is the easiest way to Analyse data in a PivotTable?

Right-click any cell in the pivot table, and choose Refresh from the shortcut menu. Click the Refresh button on the Analyze tab of the PivotTable Tools on the ribbon.

How to use pivot table and why?

What is the use of a Pivot Table? – A Pivot Table is used to summarise, sort, reorganise, group, count, total or average data stored in a table. It allows us to transform columns into rows and rows into columns. It allows grouping by any field (column), and using advanced calculations on them.

How do I add two rows to a pivot table?

– You can choose from a wide variety of PivotTable styles in the gallery. In addition, you can control the banding behavior of a report. Changing the number format of a field is a quick way to apply a consistent format throughout a report. You can also add or remove banding (alternating a darker and lighter background) of rows and columns.

1. Click anywhere in the PivotTable. This displays the PivotTable Tools tab on the ribbon.
2. On the Design tab, in the PivotTable Styles group, do any of the following:
   • Click a visible PivotTable style or scroll through the gallery to see additional styles.
   • To see all of the available styles, click the More button at the bottom of the scroll bar. If you want to create your own custom PivotTable style, click New PivotTable Style at the bottom of the gallery to display the New PivotTable Style dialog box.

Apply banding to change the format of a PivotTable

1. Click anywhere in the PivotTable. This displays the PivotTable Tools tab on the ribbon.
2. On the Design tab, in the PivotTable Style Options group, do one of the following:
   • To alternate each row with a lighter and darker color format, click Banded Rows,
   • To alternate each column with a lighter and darker color format, click Banded Columns,
   • To include row headers in the banding style, click Row Headers,
   • To include column headers in the banding style, click Column Headers,

Remove a style or banding format from a PivotTable

1. Click anywhere in the PivotTable. This displays the PivotTable Tools tab on the ribbon.
2. On the Design tab, in the PivotTable Styles group, click the More button at the bottom of the scroll bar to see all of the available styles, and then click Clear at the bottom of the gallery.

Conditionally format data in a PivotTable Use a conditional format to help you visually explore and analyze data, detect critical issues, and identify patterns and trends. Conditional formatting helps you answer specific questions about your data. There are important differences to understand when you use conditional formatting on a PivotTable:

• If you change the layout of the PivotTable by filtering, hiding levels, collapsing and expanding levels, or moving a field, the conditional format is maintained as long as the fields in the underlying data are not removed.
• The scope of the conditional format for fields in the Values area can be based on the data hierarchy and is determined by all the visible children (the next lower level in a hierarchy) of a parent (the next higher level in a hierarchy) on rows for one or more columns, or columns for one or more rows. Note: In the data hierarchy, children do not inherit conditional formatting from the parent, and the parent does not inherit conditional formatting from the children.
• There are three methods for scoping the conditional format of fields in the Values area: by selection, by corresponding field, and by value field.

For more information, see Apply conditional formatting, Include OLAP server formatting If you are connected to a Microsoft SQL Server Analysis Services Online Analytical Processing (OLAP) database, you can specify what OLAP server formats to retrieve and display with the data.

1. Click anywhere in the PivotTable. This displays the PivotTable Tools tab on the ribbon.
2. On the Analyze or Options tab, in the Data group, click Change Data Source, and then click Connection Properties,
3. In the Connection Properties dialog box, on the Usage tab, and then under the OLAP Server Formatting section, do one of the following:
   • To enable or disable number formatting, such as currency, dates, and times, select or clear the Number Format check box.
   • To enable or disable font styles, such as bold, italics, underline, and strikethrough, select or clear the Font Style check box.
   • To enable or disable fill colors, select or clear the Fill Color check box.
   • To enable or disable text colors, select or clear the Text Color check box.

Preserve or discard formatting

1. Click anywhere in the PivotTable. This displays the PivotTable Tools tab on the ribbon.
2. On the Analyze or Options tab, in the PivotTable group, click Options,
3. On the Layout & Format tab, under Format, do one of the following:
   • To save the PivotTable layout and format so that it is used each time that you perform an operation on the PivotTable, select the Preserve cell formatting on update check box.
   • To discard the PivotTable layout and format and resort to the default layout and format each time that you perform an operation on the PivotTable, clear the Preserve cell formatting on update check box.

   Note: While this option also affects the PivotChart formatting, trendlines, data labels, error bars, and other changes to specific data series are not preserved.

What are the basics of pivottables?

What is a pivot table? – A pivot table is a summary of your data, packaged in a chart that lets you report on and explore trends based on your information. Pivot tables are particularly useful if you have long rows or columns that hold values you need to track the sums of and easily compare to one another.

In other words, pivot tables extract meaning from that seemingly endless jumble of numbers on your screen. And more specifically, it lets you group your data in different ways so you can draw helpful conclusions more easily. The “pivot” part of a pivot table stems from the fact that you can rotate (or pivot) the data in the table to view it from a different perspective.

To be clear, you’re not adding to, subtracting from, or otherwise changing your data when you make a pivot. Instead, you’re simply reorganizing the data so you can reveal useful information.