Then a PivotTable Field List pane appears, and drag the Row and Column fields to the Row Labels section, and Value field to Values section. Then in the popping dialog, check the option you need under Choose where you want the PivotTable report to be placed section.Ĩ. Select the whole new table, and click Insert > PivotTable > PivotTable.ħ. Then create a new pivot table based on this new table. Now a new pivot table is created, and double click last cell at the right down corner of new Pivot table, then a new table is created in a new worksheet. Click Next to go to the last step of the Wizard, check the option you need under Where do you want to put the PivotTable report section. Select your base data, then click Add to add the data range to the All ranges list. Click Next to go to the next dialog to check I will create the page fields option, and click the Next.ģ. Note: You can also add the PivotTabe and PivoChart Wizard command into the Quick Access Toolbar, and click to open the dialog.Ģ. Press Alt + D + P shortcut keys to open PivotTable and PivotChart Wizard dialog, then, check Multiple consolidation ranges option under Where is the data that you want to analyze section and PivotTable option under What kind of report do you want to create section.
To reverse the pivot table, you need to open PivotTable and PivotChart Wizard dialog first and create a new pivot table in Excel.ġ.
Reverse pivot table with PivotTable and PivotChart Wizard (7 steps)Reverse pivot table with Kutools for Excel’s Transpose Table Dimensions (11 steps)Reverse pivot table with PivotTable and PivotChart Wizard Now I will tell you the quick ways to reverse a pivot table in Excel. 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.Have you ever wanted to reverse or transpose the pivot table in Excel just like the below screenshots shown. 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 USE PIVOT TABLES IN EXCEL 2007 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.
0 kommentar(er)
