what does applying a banded row format do to a pivot table?

Important:The PivotTable Tools tab on the ribbon comes with two tabs - Analyze (in Excel 2013 and later versions) or Options (Excel 2010 and Excel 2010) and Design. Annotation that the procedures in this topic mention both Analyze and Options tabs together wherever applicable.

Change the layout form of a PivotTable

To make substantial layout changes to a PivotTable or its diverse fields, you can apply one of three forms:

• Compact course    displays items from different row expanse fields in one column and uses indentation to distinguish between the items from different fields. Row labels take up less infinite in meaty grade, which leaves more room for numeric information. Expand and Collapse buttons are displayed and so that y'all tin display or hide details in compact form. Compact form is saves space and makes the PivotTable more than readable and is therefore specified as the default layout form for PivotTables.

  

• Tabular form    displays one column per field and provides space for field headers.

  

• Outline course    is like to tabular form but it can brandish subtotals at the top of every group because items in the side by side cavalcade are displayed ane row below the current detail.

  

Change a PivotTable to meaty, outline, or tabular form

1. Click anywhere in the PivotTable.

   This displays the PivotTable Tools, tab on the ribbon.

2. On the Design tab, in the Layout group, click Written report Layout, and then practise one of the following:

   • To keep related data from spreading horizontally off of the screen and to help minimize scrolling, click Show in Meaty Form.

     In compact form, fields are contained in one column and indented to show the nested cavalcade relationship.

   • To outline the data in the archetype PivotTable style, click Show in Outline Class.

   • To see all data in a traditional tabular array format and to easily re-create cells to another worksheet, click Prove in Tabular Form.

     

Change the style item labels are displayed in a layout form

1. In the PivotTable, select a row field.

   This displays the PivotTable Tools tab on the ribbon.

   You can also double-click the row field in outline or tabular form, and continue with step three.

2. On the Clarify or Options tab, in the Active Field group, click Field Settings.

   

3. In the Field Settings dialog box, click the Layout & Print tab, and so nether Layout, do one of the following:

   • To prove field items in outline form, click Show item labels in outline form.

   • To display or hide labels from the next field in the same column in compact course, click Show particular labels in outline grade, and and so select Brandish labels from the next field in the same cavalcade (compact form).

   • To show field items in table-similar form, click Show item labels in tabular course.

Change the field arrangement in a PivotTable

To become the terminal layout results that yous want, you lot can add, rearrange, and remove fields by using the PivotTable Field List.

If you don't see the PivotTable Field Listing, make certain that the PivotTable is selected. If yous still don't encounter the PivotTable Field List, on the Options tab, in the Bear witness/Hide grouping, click Field List.

If yous don't meet the fields that y'all want to use in the PivotTable Field List, yous may demand to refresh the PivotTable to display whatsoever new fields, calculated fields, measures, calculated measures, or dimensions that you take added since the last operation. On the Options tab, in the Data group, click Refresh.

For more data about working with the PivotTable Field List, see Utilize the Field List to conform fields in a PivotTable.

Add fields to a PivotTable

Practice one or more of the post-obit:

• Select the bank check box next to each field proper name in the field section. The field is placed in a default area of the layout section, simply yous can rearrange the fields if you want.

  By default, text fields are added to the Row Labels area, numeric fields are added to the Values area, and Online Belittling Processing (OLAP) date and time hierarchies are added to the Column Labels area.

• Right-click the field name and and then select the appropriate command — Add to Report Filter, Add to Cavalcade Label, Add to Row Characterization, or Add together to Values — to identify the field in a specific area of the layout section.

• Click and hold a field proper noun, and then drag the field between the field section and an surface area in the layout department.

Copy fields in a PivotTable

In a PivotTable that is based on data in an Excel worksheet or external data from a not-OLAP source data, you may want to add together the same field more than than once to the Values area and so that yous can display different calculations by using the Show Values As feature. For case, you may desire to compare calculations side-by-side, such as gross and net profit margins, minimum and maximum sales, or client counts and per centum of total customers. For more information, see Show unlike calculations in PivotTable value fields.

1. Click and concur a field name in the field department, and then drag the field to the Values area in the layout section.

2. Echo stride 1 as many times every bit you want to copy the field.

3. In each copied field, alter the summary function or custom calculation the way you desire.

   Notes:

   • When y'all add together two or more fields to the Values area, whether they are copies of the aforementioned field or unlike fields, the Field List automatically adds a Values Cavalcade label to the Values surface area. Yous can apply this field to motion the field positions up and downward within the Values area. You tin can even move the Values Cavalcade label to the Cavalcade Labels area or Row Labels areas. However, you lot can't movement the Values Column label to the Study Filters surface area.

   • Y'all can add a field only in one case to either the Report Filter, Row Labels, or Column Labels areas, whether the data blazon is numeric or non-numeric. If you effort to add the same field more than than once — for example to the Row Labels and the Column Labels areas in the layout department — the field is automatically removed from the original area and put in the new area.

   • Another way to add the aforementioned field to the Values area is by using a formula (also called a calculated column) that uses that same field in the formula.

   • You cannot add together the same field more than once in a PivotTable that is based on an OLAP information source.

Rearrange fields in a PivotTable

You tin can rearrange existing fields or reposition those fields by using one of the four areas at the bottom of the layout section:

PivotTable study	Description	PivotChart	Description
Values	Apply to display summary numeric data.	Values	Use to display summary numeric data.
Row Labels	Use to display fields as rows on the side of the report. A row lower in position is nested inside another row immediately above information technology.	Centrality Field (Categories)	Employ to display fields as an axis in the nautical chart.
Column Labels	Employ to brandish fields as columns at the peak of the written report. A column lower in position is nested within another column immediately higher up it.	Legend Fields (Series) Labels	Use to display fields in the fable of the chart.
Report Filter	Use to filter the entire report based on the selected item in the report filter.	Report Filter	Employ to filter the entire written report based on the selected particular in the report filter.

To rearrange fields, click the field name in one of the areas, and and so select one of the following commands:

Select this	To
Move Upwardly	Motility the field up one position in the area.
Move Down	Motility the field down position in the expanse.
Move to Beginning	Motility the field to the beginning of the surface area.
Move to Cease	Move the field to the terminate of the surface area.
Move to Report Filter	Move the field to the Report Filter area.
Motion to Row Labels	Move the field to the Row Labels area.
Move to Cavalcade Labels	Movement the field to the Column Labels area.
Move to Values	Motility the field to the Values area.
Value Field Settings, Field Settings	Display the Field Settings or Value Field Settings dialog boxes. For more information about each setting, click the Help button at the elevation of the dialog box.

You tin can also click and agree a field name, and then drag the field between the field and layout sections, and between the unlike areas.

Remove fields from a PivotTable

1. Click the PivotTable.

   This displays the PivotTable Tools tab on the ribbon.

2. To brandish the PivotTable Field Listing, if necessary, on the Clarify or Options tab, in the Show group, click Field Listing.

   

3. To remove a field, in the PivotTable Field Listing, do one of the following:

   • In the PivotTable Field List, articulate the cheque box side by side to the field name.

     Notation:Clearing a check box in the Field List removes all instances of the field from the report.

   • In a Layout area, click the field name, and then click Remove Field.

   • Click and hold a field name in the layout section, so elevate information technology outside the PivotTable Field List.

Change the layout of columns, rows, and subtotals

To further refine the layout of a PivotTable, you can brand changes that affect the layout of columns, rows, and subtotals, such every bit displaying subtotals above rows or turning column headers off. Y'all can also rearrange individual items within a row or cavalcade.

Turn column and row field headers on or off

1. Click the PivotTable.

   This displays the PivotTable Tools tab on the ribbon.

2. To switch between showing and hiding field headers, on the Clarify or Options tab, in the Prove group, click Field Headers.

   

Brandish subtotals higher up or below their rows

1. In the PivotTable, select the row field for which you desire to brandish subtotals.

   This displays the PivotTable Tools tab on the ribbon.

   Tip:In outline or tabular form, you lot tin can also double-click the row field, and then go along with step 3.

2. On the Analyze or Options tab, in the Agile Field group, click Field Settings.

   

3. In the Field Settings dialog box, on the Subtotals & Filters tab, nether the Subtotals, click Automatic or Custom.

   Note:If None is selected, subtotals are turned off.

4. On the Layout & Impress tab, under Layout, click Show detail labels in outline form, and then exercise one of the following:

   • To brandish subtotals higher up the subtotaled rows, select the Display subtotals at the elevation of each group cheque box. This option is selected by default.

   • To display subtotals beneath the subtotaled rows, clear the Display subtotals at the acme of each group check box.

Change the order of row or column items

Do whatsoever of the following:

• In the PivotTable, right-click the row or cavalcade label or the detail in a label, point to Motility, and then use one of the commands on the Movement menu to movement the item to some other location.

• Select the row or column label item that you lot desire to move, and then point to the bottom edge of the cell. When the pointer becomes a four-headed pointer, drag the item to a new position. The following illustration shows how to motion a row item past dragging.

  

Adjust cavalcade widths on refresh

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 grouping, click Options.

   

3. In the PivotTable Options dialog box, on the Layout & Format tab, under Format, practise one of the following:

   • To automatically fit the PivotTable columns to the size of the widest text or number value, select the Autofit column widths on update bank check box.

   • To proceed the current PivotTable column width, clear the Autofit column widths on update check box.

Move a column to the row labels surface area or a row to the column labels area

Y'all might desire to move a cavalcade field to the row labels area or a row field to the column labels expanse to optimize the layout and readability of the PivotTable. When you move a column to a row or a row to a column, you are transposing the vertical or horizontal orientation of the field. This performance is too called "pivoting" a row or cavalcade.

Do whatever of the post-obit:

• Right-click a row field, point to Move <field name>, and then click Move <field name> To Columns.

• Correct-click a cavalcade field, so click Move <field name> to Rows.

• Elevate a row or column field to a different area. The following illustration shows how to move a column field to the row labels expanse.

  

  i. Click a column field

  ii. Drag it to the row area

  three. Sport becomes a row field similar Region

Merge or unmerge cells for outer row and column items

You lot can merge cells for row and column items in social club to center the items horizontally and vertically, or to unmerge cells in lodge to left-justify items in the outer row and cavalcade fields at the top of the item group.

1. Click anywhere in the PivotTable.

   This displays the PivotTable Tools tab on the ribbon.

2. On the Options tab, in the PivotTable group, click Options.

   

3. In the PivotTable Options dialog box, click the Layout & Format tab, and then under Layout, select or clear the Merge and center cells with labels bank check box.

Note:You cannot employ the Merge Cells check box under the Alignment tab in a PivotTable.

Modify the display of blank cells, blank lines, and errors

There may exist times when your PivotTable data contains blank cells, blank lines, or errors, and y'all desire to change the way they are displayed.

Change how errors and empty cells are displayed

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 grouping, click Options.

   

3. In the PivotTable Optionsdialog box, click the Layout & Format tab, and then under Format, do ane or more of the post-obit:

   • To change the error display, select the For error values bear witness check box. In the box, blazon the value that yous want to display instead of errors. To brandish errors as blank cells, delete whatsoever characters in the box.

   • To change the brandish of empty cells, select the For empty cells testify bank check box, and and so type the value that you want to display in empty cells in the text box.

     Tip:To display blank cells, delete whatever characters in the box. To display zeros, articulate the check box.

Display or hibernate bare lines after rows or items

For rows, do the following:

• In the PivotTable, select a row field.

  This displays the PivotTable Tools tab on the ribbon.

  Tip:In outline or tabular class, y'all can besides double-click the row field, and so continue with step 3.

• On the Analyze or Options tab, in the Active Field grouping, click Field Settings.

  

• In the Field Settings dialog box, on the Layout & Print tab, under Layout, select or clear the Insert bare line after each item label bank check box.

For items, do the following:

• In the PivotTable, select the detail you want.

  This displays the PivotTable Tools tab on the ribbon.

• On the Design tab, in the Layout group, click Blank Rows, and then select the Insert Blank Line after Each Item Label or Remove Blank Line after Each Particular Characterization bank check box.

  

  Annotation:You tin can apply character and cell formatting to the blank lines, simply you lot cannot enter information in them.

Change how items and labels with no data are shown

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 Display tab, under Display, do one or more of the post-obit:

   • To show items with no information on rows, select or clear the Prove items with no data on rows check box to display or hide row items that have no values.

     Note:This setting is only available for an Online Analytical Processing (OLAP) data source.

   • To show items with no data on columns, select or clear the Show items with no data on columns cheque box to brandish or hibernate column items that take no values.

     Notation:This setting is only bachelor for an OLAP data source.

   • To display item labels when no fields are in the values surface area, select or clear the Brandish item labels when no fields are in the values surface area cheque box to display or hide particular labels when there are no fields in the value area.

     Notation:This check box only applies to PivotTables that were created by using versions of Excel before than Part Excel 2007.

Modify or remove formatting

You tin can choose from a wide variety of PivotTable styles in the gallery. In addition, you tin control the banding behavior of a report. Irresolute the number format of a field is a quick way to apply a consequent format throughout a written report. You can also add or remove banding (alternating a darker and lighter background) of rows and columns. Banding can make it easier to read and browse data.

Utilize a style to format a PivotTable

You can quickly modify the await and format of a PivotTable by using one of numerous predefined PivotTable styles (or quick styles).

1. Click anywhere in the PivotTable.

   This displays the PivotTable Tools tab on the ribbon.

2. On the Blueprint tab, in the PivotTable Styles grouping, do any of the following:

   • Click a visible PivotTable style or scroll through the gallery to see additional styles.

   • To run into all of the available styles, click the More button at the lesser of the scroll bar.

     

     If you want to create your own custom PivotTable mode, click New PivotTable Style at the bottom of the gallery to brandish the New PivotTable Style dialog box.

Utilise banding to alter the format of a PivotTable

1. Click anywhere in the PivotTable.

   This displays the PivotTable Tools tab on the ribbon.

2. On the Pattern 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 cavalcade with a lighter and darker colour format, click Banded Columns.

   • To include row headers in the banding style, click Row Headers.

   • To include column headers in the banding manner, 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 push button at the bottom of the scroll bar to see all of the available styles, so click Clear at the lesser of the gallery.

   

Conditionally format information in a PivotTable

Employ a conditional format to help you visually explore and clarify data, detect critical issues, and place patterns and trends. Conditional formatting helps you reply 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 every bit the fields in the underlying data are not removed.

• The scope of the conditional format for fields in the Values surface area can be based on the data hierarchy and is determined by all the visible children (the side by side lower level in a hierarchy) of a parent (the side by side higher level in a hierarchy) on rows for one or more columns, or columns for one or more than rows.

  Note:In the data bureaucracy, children do not inherit provisional formatting from the parent, and the parent does non inherit conditional formatting from the children.

• At that place are three methods for scoping the provisional format of fields in the Values area: by selection, by corresponding field, and by value field.

For more information, run across Apply conditional formatting.

Change the number format for a field

1. In the PivotTable, select the field of involvement.

   This displays the PivotTable Tools tab on the ribbon.

2. On the Analyze or Options tab in the Agile Field group, click Field Settings.

   

   The Field Settings dialog box displays labels and report filters; the Values Field Settings dialog box displays values.

3. Click Number Format at the bottom of the dialog box.

4. In the Format Cells dialog box, in the Category list, click the number format that you lot want to use.

5. Select the options that y'all adopt, and so click OK twice.

   Yous tin also right-click a value field, and then click Number Format.

Include OLAP server formatting

If you are continued 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 Clarify or Options tab, in the Data group, click Modify Data Source, then click Connection Properties.

   

3. In the Connection Backdrop dialog box, on the Usage tab, and and then nether the OLAP Server Formatting section, do one of the post-obit:

   • To enable or disable number formatting, such as currency, dates, and times, select or articulate the Number Format check box.

   • To enable or disable font styles, such as bold, italics, underline, and strikethrough, select or articulate the Font Style cheque 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 1 of the following:

   • To save the PivotTable layout and format so that it is used each fourth dimension that y'all perform an operation on the PivotTable, select the Preserve prison cell formatting on update bank 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, articulate the Preserve cell formatting on update check box.

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

maguirehadmingesen.blogspot.com

Source: https://support.microsoft.com/en-us/office/design-the-layout-and-format-of-a-pivottable-a9600265-95bf-4900-868e-641133c05a80

Share this post