by contextures.com
When you show subtotals for a pivot table date field, the dates might not be formatted like the rest of the dates. We’ll take a look at why this happens, and how you can fix it.
For example, in the screen shot below,
When you create a pivot table from your source data, it only shows the items that have data. For example, if you put Customers and Products in the Row area, it only lists the items that each customer has bought.
When you create a pivot table, and select a cell in it, by default, a pivot table field list should appear, at the right of the Excel window.
Most of the time, you probably use a pivot table on your computer, but occasionally you might need to print one, to share with other people in your company.
Onscreen, pivot tables can get quite wide, and you can scroll back and forth, to see everything. In the pivot table shown below, there are 3 Values fields, and they are repeated for each of the items in the Column field.
Continue reading “Arrange Value Fields Vertically for Printing”
When you add fields to the row labels area in a pivot table, the field’s items are automatically sorted. See how you can manually move those labels, to put them in a different order. There’s a video and written steps below.
A pivot table shows a summary of the source data, and in the screen shot below you can see all the products that were sold, and the quantities for each colour.
When you filter the pivot table, some of that data might disappear. For example, in the pivot table shown below, the Customer field has been filtered to show XYZ Inc. orders.
They didn’t order all the colours for each product, so the pivot table is smaller. It’s only showing the products and colours that XYZ Inc. ordered.
Instead of having the pivot table change size, so it shows only the applicable items, you can change a field setting, to keep it consistent.
To make all the items appear, even if the pivot table is filtered, you can change a Layout setting in the pivot table. This setting applies to a single field, so you’ll have to make the following change to each field in which you want to see all the items.
To show all items for a pivot field in Excel 2010 or 2007:
After the setting is changed, all the colours are listed for each product, even if they weren’t sold for the selected customer.
To see the instructions for changing the setting in Excel 2003, please go to my Contextures website: Pivot Table Field Settings
Watch this video to see the steps for changing the layout setting in Excel 2010 or 2007.
_____________________
In Excel 2003, and earlier versions, when you added multiple data fields to a pivot table, they were automatically arranged vertically. The data headings showed up in the Row area, stacked one on top of the other.
If you wanted the data headings arranged horizontally, you could drag the data button in the Column area.
Here is the same pivot table, with horizontal data headings.
Pivot tables changed quite a bit in Excel 2007, and the default layout for multiple data fields is now horizontal. Using the same data, this is the default layout in Excel 2010.
If you want to change the data to a vertical layout, you can drag the Values button in the Pivot Table Field List, from the Column Labels area to the Row Labels area.
Usually, the Values button would be positioned below the other fields in the Row Labels area.
After you move the Values label, the data fields will be rearranged, and they will show vertically, with headings in the Row Labels area.
To see the steps for rearranging the data in Excel 2010, please watch this short video tutorial.
_____________
When you have a long list of fields in an Excel 2003 pivot table, it can take a long time to drag them into the pivot table layout, using the PivotTable Wizard.
For a quicker way to add the fields, don’t go into the Layout screen in Step 3 — just click the Finish button.
When the PivotTable Wizard closes, you’ll see a blank pivot table on the worksheet.
Note: If the PivotTable Field List isn’t visible, click the Show Field List button on the PivotTable Toolbar.
Repeat Steps 1 and 2, for all the layout areas, leaving the Data Area to fill in last.
____________
In the PivotTable Field List, the fields usually appear in the same order that they appear in the source data. If the pivot table source data has lots of fields, it might be hard to find a specific field in the list.
To make it easier to find the fields in the long list, you would like the field list in alphabetical order.
You can change a pivot table option, to make the PivotTable Field List show the fields in alphabetical order.
Later, if you want to return the field list to its original order, select Sort in Data Source Order, instead of Sort A to Z.
_______________
Occasionally, you might need to change a column heading in the pivot table’s source data. For example, you could change a column heading from Qty to Quantity, to make it easier to understand.
However, when you refresh the pivot table after making the changes, the renamed field will disappear from the pivot table layout. In our example, the Qty field is removed from the layout. It isn’t automatically replaced by the Quantity field, even though it’s in the same column heading cell in the source data.
If you change the column headings, you’ll have to add the revised field to the pivot table layout again. If you had number formatting in the old pivot field, you’ll have to reapply the number formatting you had previously applied.
If possible, instead of changing the column headings in the source data, create custom names for the fields in the pivot table instead.
For example, to create a custom name for the Qty field:
_____________________
For more information on Pivot Tables, please see the Pivot Table Tutorials on the Contextures Website.
_________________
