Make Pivot Table Show Up Again

In this tutorial y'all will learn what a PivotTable is, detect a number of examples showing how to create and utilise pivot tables in Excel 2016, 2013, 2010 and 2007.

Please Note, because this is a Microsoft Excel functionality, there would be a limit on the level of Back up nosotros would be able to provide with Troubleshooting.

If you are working with large information sets in Excel, pivot table comes in really handy as a quick way to make an interactive summary from many records. Among other things, it can automatically sort and filter different subsets of data, count totals, calculate average as well every bit create cross tabulations.

Some other benefit of using pivot tables is that you tin can ready and change the structure of your summary table simply past dragging and dropping the source table'due south columns. This rotation or pivoting gave the feature its name.

• What is an Excel PivotTable?
• Creating a pin tabular array in Excel: quick first
• Using Excel pivot tables
• Pivot table examples

What is a pivot tabular array in Excel?

An Excel PivotTable is a tool to explore and summarize big amounts of information, analyze related totals and present summary reports designed to:

• Present large amounts of data in a user-friendly way.
• Summarize data by categories and subcategories.
• Filter, group, sort and conditionally format different subsets of information so that you can focus on the about relevant data.
• Rotate rows to columns or columns to rows (which is called "pivoting") to view different summaries of the source data.
• Subtotal and amass numeric data in the spreadsheet.
• Expand or collapse the levels of data and drill down to run into the details behind any total.
• Present concise and attractive online of your Excel information or printed reports.

For instance, you lot may have hundreds of entries in your Excel worksheet with sales figures of local resellers:

One possible way to sum this long list of numbers by one or several weather condition is to utilise Excel formulas as demonstrated in SUMIF and SUMIFS tutorials. All the same, if you lot desire to compare several facts about each effigy, using a pivot table is a far more than efficient way. In but a few mouse clicks, y'all can get a resilient and easily customizable summary table that totals the numbers by whatever field you want.

The screenshots above demonstrate just a few of many possible layouts. And the steps below prove how you tin can quickly create your own pivot table in Excel 2016, 2013, 2010 and 2007.

How to make a pivot table in Excel: quick start

Many people think that creating an Excel pivot table is burdensome and time-consuming. But this is non true! Microsoft has been refining the engineering science for many years, and in the modern versions of Excel, the summary reports are user-friendly are incredibly fast. In fact, y'all can build your own summary table in only a couple of minutes. And hither's how:

one. Organize your source data

Before creating a summary report, organize your data into rows and columns, and then convert your information range in to an Excel Tabular array. To do this, select all of the data, go to theInsert tab and clickTable.

Using an Excel Table for the source data gives y'all a very nice benefit - your data range becomes "dynamic". In this context, a dynamic range means that your table will automatically expand and shrink as you lot add together or remove entries, so won't have to worry that your pivot tabular array is missing the latest data.

Useful tips:

• Add unique, meaningful headings to your columns, they will turn into the field names later.
• Make certain your source table contains no blank rows or columns, and no subtotals.
• To brand information technology easier to maintain your table, you can proper name your source table by switching to theDesign tab and typing the name in theTable Name box the upper right corner of your worksheet.

2. Create a pivot table

Select any prison cell in the source data table, and then become to theInsert tab >Tables group >PivotTable.

This will open theCreate PivotTable window. Brand sure the correct tabular array or range of cells is highlighted in theTable/Range field. Then choose the target location for your Excel pivot tabular array:

• SelectingNew Worksheet volition identify a table in a new worksheet starting at cell A1.
• SelectingExisting Worksheet will place your tabular array at the specified location in an existing worksheet. In theLocation box, click the Collapse Dialog button  to choose the first cell where you want to position your tabular array.

Clicking OK creates a bare pin table in the target location, which volition look similar to this:

Useful tips:

• In most cases, it makse sense to place a pivot tabular array in aseparate worksheet, this is especially recommended for beginners.
• If you are creating a pivot table from the data inanother worksheet or workbook, include the workbook and worksheet names using the following syntax [workbook_name]sheet_name!range, for case, [Book1.xlsx]Sheet1!$A$i:$E$20. Alternatively, y'all can click the Collapse Dialog button  and select a tabular array or range of cells in another workbook using the mouse.
• It might be useful to create apin table andpivot chart at the aforementioned time. To do this, in Excel 2016 and Excel 2013, get to theInsert tab >Charts group, click the pointer below thePivotChart button, and and then clickPivotChart & PivotTable. In Excel 2010 and 2007, click the pointer belowPivotTable, and and then clickPivotChart.

three. Arranging the layout of your pivot table report

The expanse where you work with the fields of your summary report is calledPivotTable Field List. It is located in the right-hand role of the worksheet and divided into the header and body sections:

• TheField Section contains the names of the fields that yous can add to your tabular array. The filed names correspond to the column names of your source table.
• TheLayout Section contains theReport Filter area,Column Labels,Row Labels area, and theValues surface area. Hither you lot can adjust and re-arrange the fields of your table.

The changes that you make in thePivotTable Field List are immediately reflected to your table.

How to add together a field to Excel pivot table

Toadd together a field to theLayout section, select the check box next to the field proper name in theFieldsection.

By default, Microsoft Excel adds the fields to theLayout section in the following way:

• Non-numeric fields are added to theRow Labels area;
• Numeric fields are added to theValues surface area;
• Online Analytical Processing (OLAP) date and fourth dimension hierarchies are added to theColumn Labelsarea.

How to remove a field from a pivot table

To delete a sure field, you can either:

• Uncheck the box nest to the field's name in theField section of the PivotTable pane.
• Correct-click on the field in your pivot table, and then click "Remove Field_Name".

How to arrange pin table fields

You can accommodate the fields in theLayout section in three ways:

1. Drag and drop fields betwixt the 4 areas of theLayout department using the mouse. Alternatively, click and hold the field name in theField department, and then drag information technology to an area in theLayoutsection - this will remove the field from the current expanse in theLayout section and place it in the new area.

   

2. Right-click the field proper name in theField department, then select the area where you lot desire to add it:

   

3. Click on the filed in theLayout section to select it. This will as well brandish the options available for that detail field.

   

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

Past default, Microsoft Excel uses theSum function for numeric value fields that you place in theValues area of the Field Listing. When y'all place non-numeric data (text, date, or Boolean) or blank values in theValues surface area, theCount office is applied.

But of course, you can cull a different summary function if you desire to. In Excel 2016 an 2013, right-click the value field you lot desire to modify, clickSummarize Values By, and choose the summary function yous want.

In Excel 2010 and lower, theSummarize Values By option is also available on the ribbon - on theOptions tab, in theCalculations group.

Beneath you tin can see an example of the pivot table with theAverage function:

The functions' names are mostly self-explanatory:

• Sum - calculates the sum of the values.
• Count - counts the number of non-empty values (works every bit 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.

To get more specific functions, clickSummarize Values By >More Options… Yous can detect the full list of available summary functions and their detailed descriptions here.

5. Show different calculations in value fields (optional)

Excel pin tables provide one more than useful feature that enables you to nowadays values in different means, for instanceprove totals as pct orrank values from smallest to largest and vice versa. The full listing of calculation options is bachelor here.

This feature is chosenShow Values As and it's accessible by right-clicking the field in the table in Excel 2016 and 2013. In Excel 2010 and lower, you tin also discover this selection on theOptions tab, in theCalculations group.

Tip. TheBear witness Values Equally characteristic may prove especially useful if you add the same field more in one case and show, for instance, total sales and sales equally a percent of total at the same time. Run into an example of such a table.

This is how you create pivot tables in Excel. And now it's time for you to experiment with the fields a bit to choose the layout all-time suited for your data fix.

Working with PivotTable Field Listing

The pivot table pane, which is formally calledPivotTable Field List, is the main tool that you employ to adjust your summary table exactly the way y'all want. To make your work with the fields more comfy, you may want to customize the pane to your liking.

Changing the Field List view

If you lot want to change how the sections are displayed in theField Listing, click theTools button, and choose your preferred layout.

You lot tin alsoresize the pane horizontally by dragging the bar (splitter) that separates the pane from the worksheet.

Closing and opening the PivotTable pane

Closing thePivotTableField List is as easy every bit clicking theClose button (X) in the top right corner of the pane.Making it to show upwardly again is not so obvious :)

To display the Field List over again, right-click anywhere in the table, and then selectShow Field Listfrom the context menu.

You can also click theField List push button on the Ribbon, which resides on theAnalyze / Options tab, in theShow group.

Using Recommended PivotTables in Excel 2016 and 2013

As y'all have merely seen, creating a pivot table in Excel is like shooting fish in a barrel. However, Microsoft Excel 2013 takes even a step farther and proposes to automatically make a report most suited for your source information. All you accept to practise is 4 mouse clicks:

1. Click whatsoever prison cell in your source range of cells or table.
2. On theInsert tab, clickRecommended PivotTables. Microsoft Excel volition immediately brandish a few layouts, based on your data.
3. In theRecommended PivotTables dialog box, click a layout to see its preview.
4. If you are happy with the preview, click the OK push button, and get a pin table added to a new worksheet.
   

Equally you run into in the screenshot higher up, Excel was able to suggest just a couple of basic layouts for my source data, which are far inferior to the pivot tables nosotros created manually a moment ago. Of grade, this is just my opinion and I am biased, you know : )

Overall, using the Recommended PivotTable in Excel is a quick way to get started, especially when you take a lot of data and are not certain where to start.

How to use pivot table in Excel

Now that you know the nuts, you can navigate to theAnalyze andDesign tabs of thePivotTable Tools in Excel 2016 and 2013 (Options andPattern tabs in Excel 2010 and 2007) to explore the groups and options provided at that place. These tabs become available as soon as you lot click anywhere within your table.

You lot tin can too access options and features that are available for a specific element past right-clicking on it.

How to blueprint and improve an Excel pivot table

One time you take created a pivot tabular array based on your source data, you may want to refine it further to brand powerful information analysis.

To better the table's design, caput over to theDesign tab where you will find plenty of pre-divers styles. To create your own manner, click theMore button in thePivotTable Styles gallery, and then click "New PivotTable Style...".

To customize the layout of a certain field, click on that field, and so click theField Settings push on theAnalyze tab in Excel 2016 and 2013 (Options tab in Excel 2010 and 2007). Alternatively, you tin can right click the field and cullField Settings from the context menu.

The screenshot below demonstrate a new blueprint and layout for our pin table in Excel 2013.

How to go rid of "Row Labels" and "Column Labels" headings

When you are creating a pin tabular array, Excel applies theCompact layout by default. This layout displays "Row Labels" and "Cavalcade Labels" equally the table headings. Concur, these aren't very meaningful headings, especially for novices.

An easy manner to get rid of these ridiculous headings is to switch from the Compact layout to Outline or Tabular. To do this, get to theDesign ribbon tab, click theReport Layout dropdown, and chooseShow in Outline Class orTestify in Tabular Form.

This will brandish the actual field names, every bit you come across in the table on the right, which makes much more than sense.

Some other solution is to go to theClarify (Options) tab, click theOptions button, switch to theDisplaytab and uncheck the "Display Field Captions and Filter Dropdowns" box. However, this will remove all field captions as well equally filter dropdowns in your table.

How to refresh a pin table in Excel

Although a pivot table written report is connected to your source data, y'all might be surprised to know that Excel does non refresh it automatically. You lot can get any data updates by performing a refresh operation manually, or have it refresh automatically when you lot open up the workbook.

Refresh the pin table data manually

1. Click anywhere in your table.
2. On theClarify tab in Excel 2016 and 2013 (Options tab in earlier versions), in theData group, click theRefresh button, or pressALT+F5.

   Alternatively, y'all tin can right-click the tabular array, and cullRefresh from the context menu.
   

To refresh all pin tables in your Excel workbook, click theRefresh button arrow, and then clickRefresh All.

Note. If the format of your pivot table gets changed afterwards refreshing, make sure the "Autofit column width on update" and "Preserve prison cell formatting on update" options are selected. To check this, click theAnalyze(Options) tab >PivotTable group >Options button. In thePivotTable Optionsdialog box, switch to theLayout & Format tab and you lot will find these check boxes there.

Later on starting a refresh, y'all canreview the condition orcancel it if you've changed your listen. Just click on theRefresh button arrow, and then click eitherRefresh Condition orCancel Refresh.

Refreshing a pivot table automatically when opening the workbook

1. On theAnalyze / Options tab, in thePivotTable grouping, clickOptions > Options.
2. In thePivotTable Options dialog box, get to theData tab, and select theRefresh data when opening the file check box.
   

How to move a pivot table to a new location

If you want to motility your table to a new workbook, worksheet are some other area in the current sail, caput over to theAnalyze tab in Excel 2016 and 2013 (Options tab in Excel 2010 and earlier) and click theMove PivotTable push button in theDeportment group. Select a new destination and clickOK.

How to delete an Excel pin table

If you lot no longer need a certain summary report, you lot can delete it in a number of ways.

• If your table resides in asplit worksheet, but delete that sail.
• If your table is located along with some other information on a sheet, select the entire pivot tabular array using the mouse and press theDelete key.
• Click anywhere in the pivot tabular array that you want to delete, go to theAnalyze tab in Excel 2016 and 2013 (Options tab in Excel 2010 and before) >Actions group, click the footling arrow below theSelectpush, chooseUnabridged PivotTable, and then printingDelete.

Note. If any PivotTable chart is associated with your table, deleting the pivot table volition plough it into a standard chart tin no longer be pivoted or updated.

Pivot table examples

The screenshots below demonstrate a few possible pivot table layouts for the aforementioned source information that might help you to get started on the right path. Feel free to download them get a easily-on feel.

Pivot table instance 1: Two-dimensional table

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

Pivot table case two: Three-dimensional table

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

This pivot tabular array lets you filter the report past calendar month.

Pin tabular array instance iii: 1 field is displayed twice - as total and % of total

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

This summary report shows full sales and sales equally a percent of total at the same time.

Bachelor downloads:

• Pin table examples

You may as well be interested in:

• Tutorial - Basic Techniques for Filtering and Sorting Clio Exports in Excel
• Tutorial - Avant-garde Techniques for Filtering Clio Reports and Exports in Excel

hardiefleapid.blogspot.com

Source: https://support.clio.com/hc/en-us/articles/360008354714-Tutorial-How-to-Use-a-PivotTable-to-Create-Custom-Reports-in-Microsoft-Excel

Share this post