Formatting

From DataSelf Knowledge Base
Jump to navigation Jump to search

Template:Toc

Back to Excel Reporting

Report Layouts

To select the layout, select a cell within the pivot table. In the PivotTableTools/Design ribbon, select Report Layout.

Compact Form

In Compact form, any hierarchical drilldown in the rows displays the subheadings within the same column as the main heading. This is Excel’s default setting. It makes it easier to prevent the report width from exceeding printable and viewable areas even when drilling into hierarchy levels.

Creating Additional Calculations to the right of the main pivot table

Using the Compact form and some additional careful setup, you can create a report whose width never changes. This way, you can create comparison and other calculations in the blank columns to the right of the pivot table data. (If the report width were to change, the calculations would no longer be referencing the correct cells, or may even be overwritten by the pivot table data.)

To make a fixed-width report, be sure that:

  • The Report Layout is Compact (or don’t have any drill-down hierarchies in the rows)
  • The report columns do not contain drill-down hierarchies
  • By default, the pivot table will exclude rows and columns with no data. But if a measure column is empty, it needs to be kept as a place-holder, so that the cell-references within the calculations are not disturbed. Set the report options to display all columns: open the PivotTable Options (right-click on the report and select PivotTable Options), in the Display tab, select “Show items with no data on columns”.

Outline Form

Tablular Form

To convert a pivot table into the equivalent of a data table, using Repeat Labels (Excel 2010 and later)

  • Use the Tablular layout
  • Remove subtotals and totals (Select a cell within the pivot table. In the PivotTableTools/Design ribbon, select Subtotals and then Grand Totals to find these options.)
  • In the PivotTableTools/Design ribbon, select Report Layout, Repeat All Item Labels (Excel 2010 and later)

Row and Column Bands

Predefined PivotTable Styles

Selecting a Section to Format

hide field header

options tab “field headers” button

rename columns

repeated names (Excel 2010 and later)

Fill down Labels

In Excel 2010, you can fill down labels in a PivotTable so that you can more easily use the PivotTable. You can also repeat labels in PivotTables to display item captions of nested fields in all rows and columns. You can repeat labels for individual fields, but you can also turn the option to repeat labels on or off for all fields in the PivotTable at the same time.

Select a cell within the pivot table. Then, in the PivotTableTools/Design ribbon, select Report Layout, Repeat All Item Labels (Excel 2010 and later).

Coloring rows and columns

Conditional formatting

http://blogs.office.com/b/microsoft-excel/archive/2005/12/21/pivottable-vii-conditional-formatting-gets-even-better-or-visualizing-your-data-in-pivottables.aspx

Repeat Item Labels

video: http://office.microsoft.com/en-us/excel-help/video-repeat-item-labels-in-a-pivottable-report-VA101812170.aspx?CTT=1

copy to clipboard to preserve formatting

If you want to publish the pivot table as a static report (unable to be refreshed, and no longer containing pivot table programming), you can copy and Paste Special > Values. This copies the labels and numbers, but loses the formatting.

To retain your beautiful formatting as along with the pivot table, highlight the portions desired and Copy. But paste it from the Clipboard:
In the Excel Home ribbon, the below the left corner Paste command, click the arrow to open Clipboard. Paste from there.