The default PivotTable layout in excel is in the compact form, where the row labels are stacked, sharing the same column. Two other layouts are available, providing each row label with its own column. You can also repeat the labels using these other layouts.
• Compact—This is the default configuration. All the fields in the row labels area share the same column. The total appears in the same row as the field.
• Outline—The fields in the row labels area each have their own column. The total appears in the same row as the field.
• Tabular—The fields in the row labels area each have their own column. The total appears in its own row beneath its group.
Choose a New Layout of PivotTable
You can replace the default Compact Form with a different layout. If you choose Outline or Tabular, you can also repeat the item labels.
2. On the PivotTable Tools, Design tab, from the Report Layout drop-down, select a different layout, such as Outline Form or Tabular Form.
3. On the PivotTable Tools, Design tab, from the Report Layout drop-down, select Repeat All Item Labels to fill the cells beneath a label.
Excel automatically sorts text data alphabetically when building a PivotTable. You can click and drag the fields to a new location or use the standard sorting tools to re-sort it.
Click and Drag:
Any row label, column label, or record can be dragged to a new location.
1. Select the row or column label you want to move.
2. Place the pointer on the edge of the selection. When it turns into a four-headed arrow, hold down the mouse button and drag it to a new location.
Use Quick Sort:
The quick sort buttons offer one-click access to sorting cell values.
1. Select a cell in the column to sort by.
2. On the Data tab, select AZ to sort lowest to highest or ZA to sort highest to lowest.
Note:There are other ways to sort data, including the following:
Right-clickthe cell and select Sort, Sort A to Z or Sort Z to Aif your data is text; select Sort Smallest to Largest or Sort Largest to Smallest if the data is numerical; select Sort Oldest to Newest or Sort Newest to Oldestif the data is a date.
From the filter drop-down of a row or column field, select Sort A to Z or Sort Z to A. If you’re using the compact report layout, select the field to sort from the pivot label field drop-down. The sort descriptions change based on whether the label is text, numeric, or date.
Just as you can create Pivot Table in excel that you can reorganize on the fly to emphasize different aspects of the data in a list, you can also create dynamic charts, or PivotCharts, to reflect the contents and organization of a PivotTable.
Creating a Pivot Chart in excel is fairly straight forward. Just click any cell in a list or Excel table that you would use to create a PivotTable, and then click the Insert tab. In the Charts group, click the PivotChart button to create the chart. When you do, Excel 2013 (or later) opens the Create Pivot Chart dialog box.
To create Pivot Chart from an existing PivotTable, click a cell in the PivotTable, display the Insert tab and then, in the Charts group, click the type of chart you want to create. After you complete either of these procedures, Excel displays a new PivotChart in your workbook.
Any changes to the PivotTable on which the PivotChart is based are reflected in the PivotChart. For example, if the data in an underlying data set changes, clicking the Refresh button in the Data group on the Analyze tool tab will change the PivotChart to reflect the new data. Also, if you filter the contents of a PivotTable, the filter will be reflected in the PivotChart. For instance, if you click 2009 in the Year list of a revenue analysis PivotTable and then click OK, both the PivotTable and the PivotChart will show only revenues from 2009.
A Pivot Chart in excel has tools with which you can filter the data in the PivotChart and PivotTable. Clicking the Year arrow, clicking (All) in the list that appears, and then clicking OK will restore the PivotChart to its original configuration.
If you ever want to change the chart type of an existing chart, you can do so by selecting the chart and then, on the Design tab, in the Type group, clicking Change Chart Type to display the Change Chart Type dialog box. When you select the type you want and click OK, Excel re-creates your chart.
In this exercise, you’ll create a PivotTable and associated PivotChart, change the underlying data and update the PivotChart to reflect that change, change the PivotChart’s type, and then filter a PivotTable and PivotChart.
Please Note one thing:The example given below is the same Data we take to create or format Pivot tables in our previous lectures. So, to create pivot chart in Excel, follow the steps below.
1. On the worksheet that you want to create a pivot chart, click any cell in the Excel table.
2. On the Insert tab, in the Chartsgroup, click the Pivot Chartbutton to open the Create a Pivot Chart dialog box.
3. Verify that the quarterly revenue table appears in the Table/Range field and that New Worksheet is selected.
4. Click OK to create the PivotChart and associated PivotTable.
5. In the Pivot Chart Fieldspane, drag the Centerfield header from the Choose fields to add to report area to the Legend (Series) area.
6. Drag the Year field header from the Choose fields to add to report area to the Axis (Category) area.
7. Drag the Quarterfield header from the Choose fields to add to report area to the Axis (Category)area, positioning it below the Year field header.
8. Drag the Revenue field header from the Choose fields to add to report area to the Values area. Excel updates the PivotChart to reflect the field placements.
9. Click the 2013 sheet tab to display that worksheet.
10. Select the data in cells B2: E10, and then press Ctrl+C. Excel copies the data to the Microsoft Office Clipboard.
11. On the tab bar, click the Through 2012 sheet(example sheet) tab to display that worksheet.
12. Select cell B147, and then press Ctrl+V to paste the data into the worksheet and include it in the Excel table.
13. Click the tab of the worksheet that contains the PivotTable and the PivotChart. The PivotChart appears.
14. Select the PivotChart and then, on the Analyze tool tab, in the Data group, click Refresh to add the data to your PivotChart.
15. On the Design tool tab, in the Type group, click Change Chart Type to open the Change Chart Type dialog box.
16. ClickLine, click the first Line chart subtype and then click OK to change your PivotChart to a line chart.
17. In the Pivot Table Fields pane, in the Choose fields to add to report area, point to the Centerfield header. Click the filter arrow that appears and then, in the filter menu, clear the Select All checkbox to remove the checkboxes from the filter list items.
18. Select the Northeast check box, and then click OK to filter the PivotChart.
Moving pivot charts to separate sheets
Although Excel automatically creates all new pivot charts on the same worksheet as the pivot table, you may find it easier to customize and work with it if you move the chart to its own chart sheet in the workbook. To move a new pivot chart to its own chart sheet in the workbook, you follow these steps:
1. Click the Analyze tab under the PivotChart Tools contextual tab to bring its tools to the Ribbon.
If the PivotChart Tools contextual tab doesn’t appear at the end of your Ribbon, click anywhere on the new pivot chart to make this tab reappear.
2. Now, Click the Move Chart button in the Actions group. Excel opens a Move Chart dialog box.
3. Click the New Sheet button in the Move Chart dialog box.
4. (Optional) Rename the generic Chart1 sheet name in the accompanying text box by entering a more descriptive name there.
5. Click OK to close the Move Chart dialog box and open the new chart sheet with your pivot chart.
The image shown below is a clustered column pivot chart after moving the chart to its own chart sheet in the workbook.
Filtering Pivot Chart in Excel
When you graph the data in a pivot table using a typical chart type, such as column, bar, or line, that uses both an x‐ and y‐axis, the Row labels in the pivot table appear along the x‐axis at the bottom of the chart and the Column labels in the pivot table become the data series that are delineated in the chart’s legend. The numbers in the Values field are represented on the y‐axis that goes up the left side of the chart. You can use the drop‐down buttons that appear after the Filter, Legend fields, Axis fields, and Values field in the PivotChart to filter the charted data represented in this fashion like you do the values in the pivot table. As with the pivot table, remove the check mark from the (Select All) or (All) option and then add a check mark to each of the fields you still want to be represented in the filtered pivot chart.
Click the following drop‐down buttons to filter a different part of the pivot chart:
▶✓ Axis Fields (Categories) to filter the categories that are charted along the x‐axis at the bottom of the chart.
▶✓Legend Fields (Series) to filter the data series shown in columns, bars, or lines in the chart body and identified by the chart legend.
▶✓Filter to filterthe data charted along the y‐axis on the left side of the chart.
▶✓Values to filterthe values represented in the PivotChart.
Formatting Pivot Chart in Excel
The command buttons on the Design and Format tabs attached to the PivotChart Tools contextual tab makes it easy to further format and customize your pivot chart. Use the Design tab buttons to select a new chart style for your pivot chart or even a brand new chart type. Use the Format tab buttons to add graphics to the chart as well as refine their look.
Although most of the time you will create Pivot Table from external data stored in Excel worksheets, you can also bring data from outside sources into Excel. For example, you might need to work with data created in another spreadsheet program by using a file format that Excel can’t read directly. Fortunately, you can export the data from the original program into a text file, which Excel then translates into a worksheet. In this tutorial, you will learn how to create pitov Table from external data in excel.
Spreadsheet programs store data in cells, so the goal of representing spreadsheet data in a text file is to indicate where the contents of one cell end and those of the next cell begin. The character that marks the end of a cell is a delimiter, in that it marks the end (or “limit”) of a cell. The most common cell delimiter is the comma, so the delimited sequence 15, 18, 24, 28 represents data in four cells. The problem with using commas to delimit financial data is that larger values—such as 52,802—can be written by using commas as thousands of markers. To avoid confusion when importing a text file, the most commonly used delimiter for financial data is the Tab character.
Import Data From Text File
To import data from a text file, on the Data tab, click Get External Data group, and then click From Text to display the Import Text File dialog box.
From within the Import Text File dialog box, browse to the directory that contains the text file you want to import. When you double-click the file, Excel launches the Text Import wizard.
On the first page of the Text Import wizard, you can indicate whether the data file you are importing is Delimited or Fixed Width; Fixed Width means that each cell value will fall within a specific position in the file. Clicking Next to accept the default choice, Delimited (which Excel assigns after examining the data source you selected), advances you to the next wizard page.
On this page, you can choose the delimiter for the file (in this case, Excel detected tabs in the file and selected the Tab checkbox for you) and you can preview what the text file will look like when imported. Clicking Next advances you to the final wizard page.
On this page, you can change the data type and formatting of the columns in your data. Because you’ll assign number styles and PivotTable Quick Styles after you create the Pivot Table from external data, you can click Finish to import the data into your worksheet. After the data is in Excel, you can work with it normally.
In this exercise, you’ll Learn to import data into Excel from a text file and then create a PivotTable from external data in Excel, based on that data.
1. Create a new Excel workbook. On the Data tab, click the Get External Data button, and then click From Text to open the Import Text File dialog box.
2. Navigate to any practice file, and then double-click the Creatingtext file to start the Text Import wizard.
3. Verify that the Delimited option is selected, and then click Next to display the next page of the wizard.
4. In the Delimiters area, verify that the Tab check box is selected and also verify that the data displayed in the Data preview area reflects the structure you expect.
5. Click Finish to skip the third page of the wizard, which has commands you can use to assign specific data types to each column. Excel assigns data types for you, so you don’t need to do so. After you click Finish, the Import Data dialog box opens.
6. Verify that Existing worksheet is selected, and then click OK to import the data into your workbook.
7. On the Home tab, in the Stylesgroup, click Format as Table, and then click the first table style to display the Format As Table dialog box.
8. Verify that the My table has headers check box is selected and that the range =$A$1:$H$6571 appears in the Where is the data for your table? box, and then click OK.
9. In the confirmation dialog box, click Yes to confirm that you want to create the Excel table and break its link to the external data source. Excel creates an Excel table from your imported data.
10. On the Insert tab, click PivotTable to open the Create PivotTable dialog box.
11. Verify that Select a table or range is selected, that Table1 appears in the Table/Range field and that the New Worksheet option is selected.
12. Click OKto create the PivotTable on a new worksheet.
13. In the PivotTable Fields pane, drag the Volume field header to the Values area.
14. Drag the Weekday field header to the Columns area.
15. Drag the Centerfield header to the Rows data area.
The data import technique shown here isn’t exclusive to PivotTables. You can use this procedure to bring data into your worksheets for any purpose.
Pivot Table Formatting in excel are the ideal tools for summarizing and examining large data collections, even those containing more than 10,000 or even 100,000 rows. Even though PivotTables style often end up as compact summaries, you should do everything you can to make your data more comprehensible. One way to improve your data’s readability is to apply a number format to the PivotTable Values field. To apply a number format to a field, right-click any cell in the field, and then click Number Format to open the Format Cells dialog box. Select or define the format you want to apply, and then click OKto enact the change.
Analysts often use PivotTables to summarize and examine organizational data with an eye to making important decisions about the company. For example, chief operating officer Adrew Penor might examine monthly package volumes handled by Consolidated Messenger and notice that there’s a surge in package volume during the winter months in the United States.
Excel extends the capabilities of your PivotTables by enabling you to apply a conditional format to the PivotTable cells. What’s more, you can select whether to apply the conditional format to every cell in the Values area, to every cell at the same level as the selected cell (that is, a regular data cell, a subtotal cell, or a grand total cell) or to every cell that contains or draws its values from the selected cell’s field (such as the Volume field in the previous example).
To apply a conditional format to a PivotTable field, click a cell in the Values area. On the Home tab, in the Styles group, click Conditional Formatting, and then create the desired conditional format. After you do, Excel displays a Formatting Options action button, which offers three options for applying the conditional format:
⇒ Selected Cells Applies the conditional format to the selected cells only.
⇒ All Cells Showing Sum of field_name Values Applies the conditional format to every cell in the data area, regardless of whether the cell is in the data area, a subtotal row or column, or a grand total row or column.
⇒ All Cells Showing Sum of field_name Values for Fields Applies the conditional format to every cell at the same level (for example, a data cell, subtotal, or grand total) as the selected cells.
In Excel, you can take full advantage of the Microsoft Office system enhanced formatting capabilities to apply existing formats to your PivotTables. Just as you can create Excel table formats, you can also create your own PivotTable formats to match your organization’s desired color scheme.
To apply a PivotTable style, click any cell in the PivotTable and then, on the Design tool tab, in the PivotTable Styles group, click the style that you want to apply. If you want to create your own PivotTable style, click the More button to display a menu containing a gallery, and then click New PivotTable Style below the gallery to open the New PivotTable Style dialog box.
Enter a name for the style in the Name field, click the first table element you want to customize, and then click Format. Use the controls in the Format Cells dialog box to change the element’s appearance. After you click OK to close the Format Cells dialog box, the New PivotTable Quick Style dialog box Preview pane displays the style’s appearance. If you want Excel to use the style by default, select the Set As Default PivotTable Style For This Document check box. After you finish creating your formats, click OK to close the New PivotTable Quick Style dialog box and save your style.
The Design tool tab contains many other tools that you can use to format your PivotTable, but one of the most useful is the Banded Columns check box, which you can find in the PivotTable Style Options group. If you select a PivotTable style that offers banded rows as an option, selecting the Banded Rows checkbox turns banding on. If you prefer not to have Excel band the rows in your PivotTable, clearing the checkbox turns banding off.
In this exercise, you’ll apply a number format to a PivotTable values field, apply a PivotTable style, create your own PivotTable style, apply banded rows to your PivotTable, and apply a conditional format to a PivotTable.
1. On the Sheet2 worksheet, right-click any data cell, and then click Number Format to open the Format Cells dialog box.
2. In the Category list, click Number to display the Number page of the dialog box.
3. In the Decimal places field, enter 0.
4. Select the Use 1000 Separator (,) check box.
5. Click OK to reformat your PivotTable data.
6. If necessary, on the Design tool tab, in the PivotTable Style Options group, select the Banded Rows check box.
7. On the Design tool tab, in the PivotTable Styles group, click the More button. Then, in the top row of the gallery, click the third style from the left (Pivot Style Light 2.). Clicking the style causes Excel to apply it to your PivotTable.
8. In the lower-right corner of the PivotTable Stylesmenu, click the More button to display the gallery.
9. Click New PivotTable Style to open the New PivotTable Style dialog box.
10. In the Name field, enter Custom Style 1.
11. In the Table Element list, click Header Row, and then click Format to open the Format Cells dialog box.
12. On the Font page, in the Color list, click the white swatch.
13. On the Border page, in the Presets area, click Outline.
14. On the Fill page, in the BackgroundColor area, click the purple swatch in the lower right corner of the color palette.
15. Click OKto close the Format Cells dialog box. The style change appears in the Preview pane of the New PivotTable Quick Style dialog box.
16. In the Table Element list, click Second Row Stripe, and then click Format to open the Format Cells dialog box.
17. On the Fill page, in the middle part of the Background Color area, click the eighth swatch in the second row (it’s a light, dusty purple).
18. Click OK twice to close the Format Cells dialog box. Your format appears in the PivotTable Styles group.
19. Click the More button in the lower-right corner of the PivotTable Styles group, and then click your new style to reformat the PivotTable.
20. On the Design tool tab, in the PivotTable Style Options group, clear the Banded Rows check box. Excel removes the banding from your PivotTable and from the preview of the custom style.
21. Select the cell ranges K6:K17 and K19:K30.
22. On the Hometab, in the Styles group, click Conditional Formatting, point to Color Scales, and in the top row, click the second three-color scale from the left to apply the conditional format to the selected cells.
Please Keep Practising This exercise to remember pivot table formatting in excel
When you create a Pivot Table in Excel, you can rename it, edit it to control how it summarizes your data, and use Pivot Table cell data in a formula. To explain this we can take an example, consider a Pivot Table named Pivot Table 2 in the image below that summarizes package volumes for every Consolidated Messengers regional distribution hub.
(See Image Below)
Excel displays the Pivot Table name when you click the Pivot Table button on the Analyze tool tab. The name Pivot Table 2 doesn’t help you or your colleagues understand the data the Pivot Table contains, particularly if you use the Pivot Table data in a formula on another worksheet. To give your Pivot Table a more descriptive name, click any cell in the Pivot Table and then, on the Options tool tab, in the Pivot Table Options group, enter the new name in the Pivot Table Name field.
When you create a Pivot Table with at least one field in the Rows area and one field in the Columns area of the Pivot Table Fields pane, Excel adds a grand total row and column to summarize your data. You can control how and where these summary rows and columns appear by clicking any Pivot Table cell and then, on the Design tool tab, in the Layout group, clicking either the Subtotals or Grand Totals button and selecting the desired layout.
After you create a Pivot Table, Excel determines the best way to summarize the data in the column you assign to the Values area. For numeric data, for example, Excel uses the SUM function. If you want to change a Pivot Table summary function, right-click any data cell in the Pivot Table values area, point to Summarize Values By, and then click the desired operation. If you want to use a function other than those listed, click More Options to display the Value Field Settings dialog box. On the Summarize Values By page of the dialog box, you can choose the summary operation you want to use.
(See Image Below)
You can also change how the Pivot Table displays the data in the Values area. On the Show Values As a page of the Value Field Settings dialog box, you can select whether to display each cell’s percentage contribution to its column’s total, its row’s total, or its contribution to the total of all values displayed in the Pivot Table.
(See Image Below)
If you want, you can create a formula that incorporates a value from a Pivot Table cell. To do so, you click the cell in which you want to create the formula, enter an equal sign, and then click the cell in the Pivot Table that contains the data you want to appear in the other cell. A GETPIVOTDATA formula appears in the formula box of the worksheet that contains the Pivot Table. When you press Enter, Excel creates the GETPIVOTDATA formula and displays the contents of the Pivot Table cell in the target cell.
In this exercise, you’ll rename a Pivot Table, specify whether subtotal and grand total rows will appear, change the Pivot Table summary function, display each cell’s contribution to its row’s total, and create a formula that incorporates a value in a Pivot Table cell.
1. On the Pivot Table worksheet, click any cell in the Pivot Table.
2. On the Analyze tool tab, click the Pivot Table button and then, in the Pivot Table Name field, enter volume summary and press Enter to rename the Pivot Table.
3. On the Design tool tab, in the Layout group, click Subtotals, and then click Do Not Show Subtotals. Excel removes the subtotal rows from the Pivot Table.
4. On the Design tool tab, in the Layout group, click Grand Totals, and then click On for columns only. Excel removes the cells that calculate each row’s grand total.
(See Image Below)
5. On the Quick Access Toolbar, click the Undo button to reverse the last change.
6. Right-click any data cell in the Pivot Table, point to Summarize Values By and then click Average to change the Value field summary operation.
(See Image Below)
7. On the Quick Access Toolbar, click the Undo button to reverse the last change.
8. Right-click any data cell in the Pivot Table, and then click Value Field Settings to open the Value Field Settings dialog box.
9. Click the Show Values As tab to display the Show Values As page.
10. In the Show Values As list, click % of Row Total.
11. Click OKto change how Excel calculates the values in the Pivot Table.
(See Image Below)
12. On the Quick Access Toolbar, click the Undo button to reverse the last change.
13. On the Designtab, in the Layoutgroup, click Subtotals, and then click Show All Subtotals at Bottom of Group to display subtotals in the workbook.
14. Click the Package Summary sheet tab to display the Package Summary worksheet.
15. In cell C4, enter =, but do not press Enter.
16. Click the PivotTable sheet tab to display that worksheet.
17. Click cell K32, and then press Enter. When you do, Excel creates the formula =GETPIVOTDATA(“Volume”, Sheet2!$A$3, ”Year”,2013)in cell C4.
(See Image Below)
Changing the pivot table options
Here we may also teach you some more things. You can use the Pivot Table Options dialog box to change the settings applied to any and all pivot tables that you create in a workbook. You open this dialog box by clicking the Pivot Table command button on the Pivot Table Tools tab’s Analyze tab followed by the Options menu item on the Options drop-down button or by simply pressing Alt+JTTT.
The Pivot Table Options dialog box contains the following six tabs:
Layout & Format with options for controlling the various aspects of the layout and formatting of the cells in the pivot table.
Totals & Filters with options for controlling the display of the subtotals and grand totals in the report, and filtering and sorting the table’s fields.
Display with options for controlling the display items in the table and the sorting of the fields in the Pivot Table.
Printing with options for controlling print expand and collapse buttons when displayed in the pivot table, and print titles with the row and column labels on each page of the printout.
Datawith options for controlling how the data that supports the pivot table is stored and refreshed.
Alt Textwith options for adding alternate, text-based titles and descriptions of the information in the pivot table for those with vision impairments who then hear the title and description read aloud.
In our previous tutorial, you will able to Create PivotTable in excel in detail. Excel PivotTable often summarize huge data sets in a relatively small worksheet. The more details you can capture and write to a table, the more flexibility you have in analyzing the data. As an example, consider all the details captured in a table in which each row contains a value that represents the distribution center, date, month, week, weekday, day, and volume for every day of the year. (See Image Below)
Each column, in turn, contains numerous values: there are nine distribution centers, data from two years, 12 months in a year, seven weekdays, and as many as five weeks and 31 days in a month. Just as you can filter the data that appears in an Excel table or other data collection, you can filter the data displayed in a PivotTable by selecting which values you want the PivotTable to include.
To filter a excel PivotTable based on a field’s contents, click the field’s header in the Choose Fields To Add To Report area of the PivotTable Fields pane. When you do, Excel displays a menu of sorting and filtering options. (See Image Below)
The Excel PivotTable displays several sorting options, commands for different categories of filters, and a list of items that appear in the field you want to filter. Every list item has a checkbox next to it. Items with a check mark in the box are currently displayed in the PivotTable, and items without a check mark are hidden.
The first entry at the top of the item list is the Select All checkbox. This checkbox can have one of three states: displaying a check mark, displaying a black square, or empty. If the Select All check box contains a check mark, then the PivotTable displays every item in the list. If the Select All checkbox is empty, then no filter items are selected. Finally, if the Select
All checkbox contains a black square, it means that some, but not all, of the items in the list, are displayed. Selecting only the Northwest checkbox, for example, leads to a PivotTable configuration in which only the data for the Northwest center is displayed.
(See Image Below)
If you’d rather display as much PivotTable data as possible, you can hide the PivotTable Fields pane and filter the PivotTable by using the filter arrows on the Rows and Columns headers within the body of the PivotTable. When you click either of those headers, you can select a field by which you can filter; you can then define the filter by using the same controls that are available when you click a field header in the PivotTable Fields pane.
Excel indicates that a PivotTable has filters applied by placing a filter indicator next to the Columns or Rows header, as appropriate, and the filtered field name in the PivotTable Fields pane.
So far in this example, all the fields by which the Excel PivotTable has been filtered has changed the organization of the data in the PivotTable. Adding some fields to a PivotTable, however, might create unwanted complexity. For example, you might want to filter a PivotTable by weekday, but adding the Weekday field to the body of the PivotTable expands the table unnecessarily. (See Image Below)
Instead of adding the Weekday field to the Rows or Columns area, you can drag the field to the Filters area near the bottom of the PivotTable Fields pane. Doing so leaves the body of the PivotTable unchanged, but adds a new area above the PivotTable in its worksheet. (See Image Below)
When you click the filter arrow of a field in the Filters area, Excel displays a list of the values in the field. When you click the filter arrow, you can choose to filter by one value at a time. If you’d like to filter your PivotTable by more than one value, you can do so by selecting the Select Multiple Items check box.
If your PivotTable has more than one field in the Rows area, you can filter values in a PivotTable by hiding and collapsing levels of detail within the report. To do that, you click the Hide Detail control (which looks like a box with a minus sign in it) or the Show Detail control (which looks like a box with a plus sign in it) next to a header.
For example, you might have your data divided by year; clicking the Show Detail control next to the 2012 year header would display that year’s details. Conversely, clicking the 2013 year header’s Hide Detail control would hide the individual months’ values and display only the year’s total. (See Image Below)
Excel 2013 also lets you filter PivotTables using search filters. With a search filter, you can enter in a series of characters for Excel to filter that field’s values. To create a search filter, click a field’s filter arrow and enter the character string that you want to search for in the filter menu’s Search box. (See Image Below)
For example, if the PivotTable’s Centerfield contains the values Atlantic, Central, Midwest, Mountain West, North Central, Northeast, Northwest, Southeast, and Southwest, entering the character string No limits the values to North Central, Northeast, and Northwest.
In this exercise, you’ll focus the data displayed in a PivotTable by creating a filter, by filtering a PivotTable based on the contents of a field in the Filters area, by showing and hiding levels of detail within the body of the PivotTable, and by using the Search box.
1. On the worksheet shown below, click any cell in the PivotTable.
2. In the Choose fields to add to report area of the PivotTable Fields pane, click the Center field header, click the Center field filter arrow, and then clear the (Select All) check box. Excel clears all the checkboxes in the filter menu.
3. Select the Northwest check box, and then click OK. Excel filters the PivotTable.
(See Image Below)
4. On the Quick Access Toolbar, click the Undo button to remove the filter.
5. In the PivotTable Fields pane, drag the Weekday field header from the Choose fields to add to report area to the Filters area in the Drag fields between areas below area.
6. In the PivotTable Fields pane, click the Close button to close it.
7. In the body of the worksheet, click the Weekday filter arrow, and then, if necessary, select the Select Multiple Items check box. Excel adds check boxes beside the items in the Weekday field filter list.
8. Clear the Allcheckbox to clear every checkbox in the list.
9. Select the Tuesday and Thursday check boxes, and then click OK. Excel filters the PivotTable, summarizing only those values from Tuesdays and Thursdays.
10. In cell A5, click the Hide Detail button. Excel collapses rows that contain data from the year 2012, leaving only the subtotal row that summarizes that year’s data. (See Image Below)
11. In cell A5, click the Show Detail button to redisplay the collapsed rows.
12. If the PivotTable Fields pane isn’t displayed, click the Analyze tool tab, click Show, and then click Field List.
13. In the PivotTable Fields pane, click the Month field header arrow to open the filter menu.
14. In the Search box, enter Ju. Excel displays the months June and Julyin the filter list. (See Image Below)
15. Click OK to apply the filter.
16. On the Analyze tool tab, click the Actions button, click Clear, and then click Clear Filters to remove all filters from the PivotTable.
⇒ Rememberone thing it is the practice who able you to master excel pivottable. So, We advise you to Keep Practising these exercises.
In this tutorial, you will learn how to make excel pivot table easily. This is the short and to the point tutorial about creating excel pivot tables but you can find in details about creating pivot tables by visiting this tutorial.
You can more easily analyze a large amount of data by creating an Excel Pivot table from that data. A PivotTable is a powerful data analysis tool because it automatically groups large amounts of data into smaller, more manageable categories, and it displays summary calculations for each group. You can also manipulate the layout of or pivot the PivotTable to see different views of your data. Although you can create a PivotTable in excel from a normal range, for best results, you should convert your range to a table before creating the PivotTable.
Following are the steps to make excel pivot table step by step.
1. Click in the table, select the Insert tab, and then click the PivotTable button, in the Tables group.
2. Choose the location for the PivotTable report, either a new worksheet or an empty portion of the current worksheet, e.g. A100, and then click OK.
3. An empty PivotTable report is added there.
4. Check the boxes to select fields from the list (for example, Title, Artist, and Length).
5. Selected fields will appear in the ROWSarea by default, but they can be moved to other areas.
You can also Select Insert and click Recommended PivotTables to see the suggestions that Excel 2016 makes for the data in your worksheets.
6. Rearrange the fields by dragging between areas, or right-click a name and select the area where it should appear. For example, move the Length field to VALUES.
7. Click any field’s arrow button to move it, or to change its Field Settings, for example, any Value.
8. Choose how to summarize the values (e.g. Sum, Count, Average).
9. Choose a Custom Name for the field if you like, then click OK to see the Pivot Table report – Analyze and Design tabs now get added to the Ribbon.
On the Analyze tab click Show, then Field List to hide or reveal the Excel Pivot Table Fields list. From there, you can also collapse or expand the report details.
Excel 2016 provides automatic relationship detection among the tables used for your workbook’s data model.
A PivotTable report, commonly referred to as a PivotTable, is an interactive table that uses calculations to consolidate and summarize data from a data source into a separate table. PivotTables enable you to analyze data in a dataset without altering the dataset itself. PivotTables are dynamic: You can easily and quickly pivot, or rearrange, data to analyze data from different viewpoints. Looking at data from different perspectives helps identify trends and patterns among the variables that might not be obvious from looking at hundreds or thousands of rows of data yourself. In this tutorial, you will able to create pivot table in excel.
Create a Pivot Table in Excel
You will learn how to organize and group data into rows and columns, remove and rearrange fields, and change the settings for value fields. So Let’s Get Started:
Before we start to create PivotTable in Excel, your data must be collected in a list. Excel tables mesh perfectly with PivotTable dynamic views; not only do Excel tables have a well-defined column and row structure but the ability to refer to an Excel table by its name also greatly simplifies PivotTable creation and management.
The data in the worksheet that’s shown in the image below is organized so that each row represents a distribution center and each column represents a month of the year. When presented in this arrangement, the monthly totals for all centers and the yearly total for each distribution center are given equal billing: neither set of totals stands out.
To reorganize and redisplay your data dynamically, you can use the Excel PivotTable tool. In Excel, you can create Recommended PivotTables. To open the Recommended PivotTables dialog box for a data set, click any cell in the data range that you want to summarize and then, on the Insert tab of the ribbon, click Recommended PivotTables. When you do, Excel displays a set of PivotTables that you can create quickly. Pointing to a recommended PivotTable shows a preview image of what that PivotTable would look like. To create it, just click the image of the arrangement you want to create and click OK.
If you don’t find the Recommended PivotTables that’s meet your needs, you can create a PivotTable by adding individual fields. For instance, you can create a PivotTable with the same layout as the worksheet described previously, which emphasizes totals by month, and then change the PivotTable layout to have the rows represent the months of the year and the columns represent the distribution centers. The new layout emphasizes the totals by the regional distribution center.
In the Excel table used to create the distribution PivotTable, each row of the table contains a value that represents the distribution center, date, month, week, weekday, day, and volume for every day of the years 2012 and 2013.
Excel needs that data when it creates the PivotTable so that it can maintain relationships among the data. If you want to filter your PivotTable so that it shows all package volumes on Thursdays in January, for example, Excel must be able to identify January 10, 2013, as a Thursday. After you create an Excel table, you can click any cell in the table, display the Insert tab and then, in the Tables group, click PivotTable to open the Create PivotTable dialog box.
In this dialog box, you verify the data source for your PivotTable and whether you want to create a PivotTable on a new worksheet or an existing worksheet. After you click OK, Excel displays a new or existing worksheet and displays the PivotTable Fields pane.
If the PivotTable Fields pane isn’t visible, you can display it by clicking any cell in the PivotTable, which displays the PivotTable Tools tabs. On the Options tool tab, in the Show/Hide group, click Field List.
To assign a field, or column of data, to an area of the PivotTable, drag the field header from the Choose Fields To Add To Report area at the top of the PivotTable Fields pane to the Drag Fields Between Areas Below area at the bottom of the pane. For example, if you drag the Volume field header to the Values area, the PivotTable displays the total of all entries in the Volume column.
It’s important to note that the order in which you enter the fields in the Rows and Columns areas affects how Excel organizes the data in your PivotTable. As an example, consider a PivotTable that groups the PivotTable rows by distribution center and then by month.
The same PivotTable data could also be organized by month and then by distribution center.
In the preceding examples, all the field headers are in the Rows area. If you drag the Center header from the Rows area to the Columns area, the PivotTable reorganizes (pivots) its data to form a different configuration.
To pivot a PivotTable, you drag a field header to a new position in the PivotTable Fields pane. As you drag a field within the pane, Excel displays a blue line in the interior of the target area so you know where the field will appear when you release the mouse button. If your data set is large or if you based your PivotTable on a data collection on another computer, it might take some time for Excel to reorganize the PivotTable after a pivot. You can have Excel delay redrawing the PivotTable by selecting the Defer Layout Update check box in the lower-left corner of the PivotTable Fields pane. When you’re ready for Excel to display the reorganized PivotTable, click Update.
If you expect your PivotTable source data to change, such as when you link to an external database that records shipments or labor hours, you should ensure that your PivotTable summarizes all the available data. To do that, you can refresh the PivotTable connection to its data source. If Excel detects new data in the source table, it updates the PivotTable contents accordingly. To refresh your PivotTable, click any cell in the PivotTable and then, on the Options tool tab, in the Data group, click Refresh.
In this exercise, you’ll create a PivotTable by using data from a table, add fields to the PivotTable, and then pivot the PivotTable.
1. Click any cell in the Excel table.
2. On the Insert tab, in the Tables group, click Recommended PivotTables to open the Recommended PivotTable dialog box.
3. Click the second icon, labeled Sum of Volume by Weekday.
4. Click OK to create the PivotTable.
5. Click the Sheet1 worksheet’s sheet tab to display that worksheet.
6. If necessary, click any cell in the Excel table.
7. On the Inserttab, in the Tables group, click the PivotTable button to open the PivotTable dialog box.
8. Verify that the DailyVolumes table name appears in the Table/Range field and that the New Worksheet option is selected.
9. Click OK to create a PivotTable on a new worksheet.
10. In the PivotTable Fields pane, drag the Center field header to the Rows area. Excel adds the Center field values to the PivotTable row area.
11. In the PivotTable Fields pane, drag the Yearfield header to the Columns area. Excel adds the Year field values to the PivotTable column area.
12. In the PivotTable Fields pane, drag the Volume field header to the Values area. Excel fills in the body of the PivotTable with the Volume field values.
13. In the PivotTable Fieldspane, in the Columns area, drag the Year field header to the Rows area, and drop it beneath the Centerfield header. Excel changes the PivotTable to reflect the new organization.
Note: This Is Detailed Tutorial About Creating PivotTable in Excel. If you want to remember this tutorial then please go through this exercise ( given above).
Sign up for new Howexcel Latest Post & Training,
Subscribe To Our Newsletter
Join our mailing list to receive the latest Tutorials From Howexcel.