In our scenario, we want to group by Weeks, rather than by Months. Note that the Months option has been chosen by default. You can then choose what you want to group your data by. In our case, we'll leave the dates unchanged. You can change these dates at this point so that the grouping only covers a given date range. If you have attempted to group on a valid field type (date in our example), then you will see the following options dialog box where you can choose how you want to group the data in your pivot table:Įxcel will automatically select the start and end dates for the grouping based on the dates in your data. Don't bother about clicking the "Was this information helpful?" link unless you want to give Microsoft some feedback on this message. In the latter case you'll need to review your source data and fix it. Note that if you see a message like this, then either you didn't click the correct date field or some of your dates are not valid date. Once you are ready to group your data, select a date field from within the pivot table and click either the Group Selection button or the Group Field button on the toolbar (both will work in this scenario):Īlternatively, simply right-click on one of the dates in the first column, and select the Group option as shown here: When we group by Date, we will see data for each Salesperson aggregated into the date period that we chose: In our example, our Row fields look like the following. You need at least on additional field after the Date field in your pivot table. The Date field can be either a Row or a Column in your Pivot Table, although you may find that placing it in a Row is the most practical option if your source table includes multiple records over a long period of time, since your pivot table will then spread across many columns.If it doesn't, you may not be able to group your data. The Date field in your source data should only include valid dates.To group your data by Date, make sure you have a valid Date field in your pivot table. The work we'll do in this lesson will focus exclusively on the Options toolbar. Then, click either Options or Design to see the related toolbar. To access the Pivot Table Tools toolbar, follow these steps:Ĭlick on a cell anywhere inside your pivot table.Ĭheck the ribbon toolbar for this option:
HOW DO YOU CREATE PIVOT TABLES IN EXCEL 2013 HOW TO
This toolbar isn't essential to complete this lesson, but it helps to know how to find it when you need it. We'll start by reviewing how to access the Pivot Table Tools toolbar.
Now, let's look at what else we can do with this pivot table. We then turned that data into a Pivot Table that looked like this:Īs you can see, the pivot table above has been designed to show product sales broken down first by day and then by sales person. In that first lesson, we took a data table that looked like this (note how many rows there are in the table - this table covers a three month period for four sales people): In this lesson we will extend that pivot table by grouping our data by date into weeks, months, quarters or years. This lesson picks up on the work we did in our first lesson on creating a Pivot Table, which introduced Pivot Tables and showed you how to create a basic Pivot Table from a table of source data. Maybe this is one step too far for you at this stage, but it shows you one of the many other powerful pivot table features Excel has to offer.This lesson shows you how to group data in your pivot table if you have included a date field in the pivot table. To easily compare these numbers, create a pivot chart and apply a filter. Next, to get the total amount exported to each country, of each product, drag the following fields to the different areas.īelow you can find the two-dimensional pivot table. If you drag a field to the Rows area and Columns area, you can create a two-dimensional pivot table. 16 out of the 28 orders to France were 'Apple' orders. Choose the type of calculation you want to use. Right click and click on Value Field Settings.ģ.
Click any cell inside the Sum of Amount column.Ģ. To change the type of calculation that you want to use, execute the following steps.ġ. Change Summary Calculationīy default, Excel summarizes your data by either summing or counting the items. Note: you can use the standard filter (triangle next to Row Labels) to only show the amounts of specific products. Apples are our main export product to France. Click the filter drop-down and select France. For example, which products do we export the most to France?ġ. Because we added the Country field to the Filters area, we can filter this pivot table by Country.