![how to format pivot tables in excel 2013 how to format pivot tables in excel 2013](https://support.content.office.net/en-us/media/69492290-77e8-4acf-82f1-4ad44a37cf14.png)
Let's create a pivot table using the table above. Here's a better example because it shows you what a pivot table can do with your data. You can add a filter for any category: First Name, Last Name, or Phone number (in the case of our table).
![how to format pivot tables in excel 2013 how to format pivot tables in excel 2013](https://i.ytimg.com/vi/Nv8kSegtqRc/maxresdefault.jpg)
We can also use the filter we created at the top to select a phone number to find out who it belongs to. Now we can see how many phone numbers we had for each person.
![how to format pivot tables in excel 2013 how to format pivot tables in excel 2013](https://i.ytimg.com/vi/e-yuYNgsHAk/maxresdefault.jpg)
Here's what we've done in the Field list on the right: You can always drag and drop to a different section if you want. Your pivot table will appear in your spreadsheet as you do this. Just drag and drop from the top part of the field list to the bottom part and place it in a category: Filters, Columns, Rows, and Values. If you look at your Pivot Table Field list on the right, you can drag and drop from the "Choose Fields to Add to Report" section down to the "Drag Fields Between Areas Below" section. This is what you should now see on your screen: The name field in our table would be arbitrary. In ours, that's the salaries.Īrbitrary fields are fields that are neither data or category. In ours, we grouped category fields by department and position.ĭata fields are fields that contain data that you can add, subtract, multiply, or divide. This is a list of all fields in your table.Ĭategory fields are fields that you can group. On the right, you'll see the Field list open up. To do this, select the table, then go to the Insert tab and click Pivot table. We want a pivot table showing us how many phone numbers are on file for each employee. In tables, columns are fields and rows are records.
How to format pivot tables in excel 2013 how to#
This is also useful when your slicer buttons show values that are no longer in the data set.The best way to learn about a pivot table is to see how to create one. On 'Data' tab, change drop-down menu for 'Number of items to retain per field' to 'None'. To change how the Pivot Table retains items in the Cache,ģ. , but it didn't actually result in Excel recognizing it as a date until after I reset the items retained in the Pivot Table cache. I changed my query to use Mike's post ofĬast(convert(char(11), Bo.Depart, 113) as datetime) Otherwise, since the first values are still in the cache (even though they are no longer in the live data), the whole field gets recognized as text.įor example, I had an existing table where I was having this problem. Important to note that if the dates are first recognized as text, and then you change the query to one of the suggestions, you'll have to tell Excel to not retain the old text-formatted values in the Pivot Table cache (Pivot tables by default retain 'old' values in each field in the cache in the background, even if they're not in the data currently being returned). I get loads of formats but Excel must not like converted dates? B.Depart AS 'Holiday Date time',ĬONVERT(VARCHAR(10), B.Depart,103) AS 'Holiday Date',ĭATENAME(weekday, B.Depart) AS 'Holiday Day Name',ĬONVERT(CHAR(2), B.Depart, 113) AS 'Holiday Day',ĬONVERT(CHAR(4), B.Depart, 100) AS 'Holiday Month',ĬONVERT(CHAR(4), B.Depart, 120) AS 'Holiday Year',ĬONVERT(VARCHAR(10),B.Depart,10) AS 'New date',
![how to format pivot tables in excel 2013 how to format pivot tables in excel 2013](https://www.dummies.com/wp-content/uploads/353360.image0.jpg)
Tried all these but only the original B.Depart (date time) comes through as a date, none of the converted columns are read by Excel as a date. Here you can see how Excel is see it as text on the left and n the right what it should look like in Excel when it recognizes it as a date. When it get to into my Excel pivot table via my SQL Connection it looks the same but the filters do not recognize as a date. I'm pulling data to use in a Pivot table in Excel, but Excel is not recognizing the date format.