This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I have a Sheet that I put a persons name a tip amount and a date. I then created a pivot table that orders the data by person name date and amount of tips for that date. What I would like to do is extract the persons name and the total amount of there tips and put it into 2 other columns that I can sort by largest tip. I am not concerned with the individual tips only the total amount of tips for each person. An example of the pivot table is as follows
| A | B | C | D | E |
|---|---|---|---|---|
| Person | Sum of Tips | Count of Date | Max Tip | Average Tip |
| Name 1 | 50 | 1 | 50 | 50 |
| Name 2 | 5 | 5 | 1 | 1 |
| Name 3 | 75 | 1 | 75 | 75 |
| Name 4 | 1314 | 13 | 333 | 101 |
| Name 5 | 150 | 1 | 150 | 150 |
| Name 6 | 199 | 1 | 199 | 199 |
| Name 7 | 370 | 2 | 320 | 185 |
| Name 8 | 50 | 1 | 50 | 50 |
| Name 9 | 50 | 1 | 50 | 50 |
| Name 10 | 550 | 2 | 500 | 275 |
| Name 11 | 4054 | 51 | 778 | 79 |
| Name 12 | 300 | 1 | 300 | 300 |
| Name 13 | 760 | 3 | 420 | 253 |
| Name 14 | 100 | 1 | 100 | 100 |
| Name 15 | 100 | 1 | 100 | 100 |
| Name 16 | 75 | 1 | 75 | 75 |
| Name 17 | 7563 | 153 | 1000 | 49 |
Each row under person is expandable to show the tips for that person. I'm not concerned about that. I just want to take the name and the value in the Sum of Tip column and extract them to another set of columns that could update if new information is added. My goal here is to have a section where I can sort on sum of tips with the largest sum first. Not sure if this can be done but looking to see what I can do to accomplish this. There is a total of 67 collapsed rows. The Sum of Tips column is based on tips for the person from Jan to Current.. Thus the Count of Date column sums up all the tips for that person.
When new tips come in I add them to the tip sheet and then refresh the Pivot Table to update that. I would like to see if after refreshing the Pivot table if it would update my new set of columns. If not then will have to figure out something.
Thanks for the help with this.
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
Glad you got it working. Please mark the question solve. In fact, it should have been marked as solved after the first couple of posts, because after that we discussed a completely different thing.
You can learn a lot about Pivot Tables at this site: http://www.contextures.com/tiptech.html
As you can see here the columns on the left have 2 dates Jan 1 and Jan 2. If you look at Max tips Number of Tips and Tip total it's showing Jan 15 which is month year format. I want month day format as is shown by the date at top.
Now I am not sure if this is a function of the pivot table wanting to show month totals but that is not what I want. When I change the number format it doesn't stick. I click on the Date entry in Rows and then field settings then number format and choose custom where I have mmm\d stored to tell Excel these need to be displayed as month and day not month and year.
Hello,
you can have more than one pivot tables on a sheet. Just make sure that when the tables expand they don't overlap.
Formatting a date does not change its value and should not matter for the pivot table. Can you provide some more detail? Maybe save a small data sample to your Onedrive public folder, share it and post the link here.
That does work but I thought I could do it on the same sheet. But will go with this. Thank you and updating the main sheet then refreshing the pivots works fine.
Now I am having another issue unrelated to the pivot table. My dates are not being formatted correctly. I want mmmd format and they are doing mmmyy. How can I fix this for all dates in my table. It is giving me incorrect results in my pivot table as it's supposed to treat it as month and day not month and year.
Thank you for the help
Hello,
you could create another pivot table from the same data source. Drag the Name into the row area and the tip into the values and set it to sum. Then sort that pivot table by the values.
When you add more data, refresh both pivot tables. This will ensure that new names will appear in both tables.