How to format pivot tables in excel 2013
In Excel 2013, you find controls to customize the pivot table in myriad places: the Analyze tab, Design tab, Field Settings dialog, Data Field Settings dialog, PivotTable Options dialog, and context menus. These tweaks range from making cosmetic changes to changing the underlying calculation used in the pivot table. In this case, you can use many powerful settings to tweak the information in your pivot table.
Changing the Calculation in a Value Field 67Īlthough pivot tables provide an extremely fast way to summarize data, sometimes the pivot table defaults are not exactly what you need.Customizing the Pivot Table Appearance with Styles and Themes 59.Once you get familiar with modifying Pivot Table, you will be able to analyze large amounts of data and create all kinds of data summaries with effortless ease.
It is recommended that you spend quality time to play around with PivotTable Field items and get used to modifying a given Pivot Table. However, the only way to master Pivot Tables is to play around and make mistakes. Once the PivotTable Fields list is active, you will be able to modify the Pivot Table by adding Field Items and dragging the Field Items between Columns, Rows and Values areas.Īt first, you may find things going horribly wrong when you try to modify the Pivot Table layout. Modify Pivot Table LayoutĮven after creating PivotTable using the suggested layout, you can modify the PivotTable to suit your own requirements.Ĭlick on any Cell within the Pivot Table and this will open the PivotTable Field List. Once you click on OK, Excel will insert a Pivot Table in a new worksheet. A layout will not be inserted, until you click on the OK button.Ĥ. Note: You can actually click on the suggested PivotTable Layouts to see them in larger view. On Recommended PivotTables screen, scroll down the suggested list to view them > select the PivotTable Layout that you want to use and click on OK. Select any Cell in Source Data > click on Insert > Tables > Recommended PivotTables option.ģ. Open the Excel File containing Source Data that you want to include in the Pivot Table.Ģ. Once the Source Data is in the right-format, you can follow the steps below to Create Pivot Table in Excel.ġ. To explain the basic steps to Create Pivot Table in Excel, we will be making use of Sales Data recorded at two computer stores, conveniently labelled as Store#1 and Store#2.Īs you can see in above image, the Source Data is well organized with unique column labels and it has no blank columns, blank rows or cells.
How to format pivot tables in excel 2013 free#
Once the Source Data for Pivot Table is properly organized and meets the above requirements, you should have no problem creating error free Pivot Tables. Make sure that you apply formatting (Date, Number, etc.) to cells within the Source Data. Do not include Totals, Subtotals and Averages (Column or Row Totals) when you select Source Data Range in a Pivot Table.ĥ.
Avoid blank Cells and blank Rows in Source Data, as they can lead to errors and confusion in Pivot Table.Ĥ. In-fact, you won’t be able to Create Pivot Table, if there is blank Column in the Source Data.ģ. To avoid confusion, column Labels need to be unique and not repeated.Ģ. Each and every column in Source Data needs to have a heading or column Label. In general, the Source data for Pivot Table needs to comply with the following requirementsġ. In our opinion, going with a Suggested Pivot Table and modifying it is easier and quicker than working with a blank Pivot Table. Once the Source Data is ready, you can either insert a blank pivot table or insert one of the suggested PivotTables into the worksheet.