Using Pivot table/chart or other to display top 10 companies by quotes/sales on attached

If you want to create a pivot table based off that data, you need to rearrange it so each row represents a single entry, similar to the attached file.

Ignore the formulas in the spreadsheet i attached. The format is all that matters.
I don't know how to construct formulas to pull your data without knowing the structure of the sourced data, but...

Looking at the formula you had in cell E2, you have a reference to cells E$1 and $D2. Just change those to reference column B and C respectively and that should at least correct that portion.

I included a sheet with the formulas you would want to use to reorder your data, but in reality the format of the original data is the format you needed all along.
The Pivot table in the attached file is based right off of your original table.

What you want to do, is highlight cells C3:C35, type in "Company A" and hit "CTRL-ENTER". this will enter the company name in all 33 cells at once.
The "@[Company]" reference is simply referring to the "Company" column. if you change the value in cell C2 to "Company Name", the reference will dynamically change to "@[Company Name]". The values in the column are irrelevant.

I would definitely suggest not including anything in this file if you don't understand how it was constructed. You will simply run into issues when updates/changes need to be made. Do lots of research on any areas you don't understand, and then utilize them in the file.

In reality, making the table via formulas is not the best idea.
Making a Pivot table that gives you that table is much quicker.
The attached file shows how to do that.

I included a sheet with the formulas you would want to use to reorder your data, but in reality the format of the original data is the format you needed all along.
The Pivot table in the attached file is based right off of your original table.

I mentioned in my post when i attached the data, that you don't need the formulas anymore, the structure of your original data was what you needed all along. and that the pivot table was based off the original table.

For future reference, to find out a source of a pivot table, just click on the pivot table, then select Analyze from the top menu tabs, and then select "Change Data Source". You are then able to clarify the source data for the pivot table

If you are still having issues, you will need to clarify what you mean by "it doesn't look like yours does". What specifically is different? Open up the solution I provided, and try and recreate the pivot table. if you run into issues, save and upload it to this post, so I can clearly see where you are having issues.

I included a sheet with the formulas you would want to use to reorder your data, but in reality the format of the original data is the format you needed all along.
The Pivot table in the attached file is based right off of your original table.

I mentioned in my post when i attached the data, that you don't need the formulas anymore, the structure of your original data was what you needed all along. and that the pivot table was based off the original table.

For future reference, to find out a source of a pivot table, just click on the pivot table, then select Analyze from the top menu tabs, and then select "Change Data Source". You are then able to clarify the source data for the pivot table

If you are still having issues, you will need to clarify what you mean by "it doesn't look like yours does". What specifically is different? Open up the solution I provided, and try and recreate the pivot table. if you run into issues, save and upload it to this post, so I can clearly see where you are having issues.

Okay thanks. Sorry, I am not overly familiar with Pivot tables and collating data. Please see attached this is what I get when I create a Pivot table from the master sheet and moving the headers to the same place you have, and amending revenue to sum.
Yours has currency, alternating colours in rows, and is grouped into company with BB, PS or Direct, mine does not.
Hope this makes a little more sense now on how they are different. Thanks, Ian

Your pivot table is based off a different data source, which makes it difficult to compare.
The data source is not in the file, which also makes things difficult.

Could you open the file I posted at 8:25 on August 15th, and try and recreate the exact pivot table i made on a new sheet. We will eliminate other variables, such as incorrect data, or missing columns, that may be causing an issue for you. By adding to the file I posted, I'll easily be able to see the difference between my pivot table and yours, and advise on what you should do differently.

The only thing I believe has changed is the company names in the company name column. I did not want to disclose all the company names along with their quotes etc... to a public forum. So instead of the actual names of the companies I replaced them with Company A, Company B etc......I could email you them personally if that was possible?
Also, when you view "Change Source" it comes up with the Master Quote Sheet tab, but the name is Table 2. I have literally just highlighted the complete table in Master Quote Sheet and selected Pivot table, and it still comes up with Company A, Company B, as attached.
I've amended the company names to show example A, example B, to see if it made a difference, then went to create pivot table based on the data from Master Quote Sheet, and it still shows Company A, Company B. So I don't know what I'm doing wrong.

I have no need for the actual company names. If you try and recreate the pivot table in the file i mentioned, it will be based off of mock date, and that is fine.

Next time just post it as a new attachment, so it is clear a new file has been attached. it took me a while to figure out what you did.
You used the file I posted at 1:51, rather than the one I posted at 8:25.
I will try and make it work

Step #1: Go to the "Your Pivot Table" Sheet
Step #2: Order your fields in the "row" section in this order:
- Customer
- Month
- Month Quoted
Step #3: Go to the "Design" menu at the top right of your menu options, and select the pivot table style you desire.
Step #4: Right-click on one of the Revenue totals in the pivot table - select "Number format" - select the format you want for that field.


The differences between your pivot table and mine were caused by a general lack of understanding of pivot tables.
Search online for some general tutorials, for assistance on specific issues (ex. search "formatting pivot table values"), or check out this link: http://chandoo.org/wp/2009/08/19/exc...bles-tutorial/

The Summary Tab has NO relevance. You were trying to create your pivot table based off of the data in that summary tab. Once you pasted the file with the original data, it became apparent that the original table should be the source of the data. I included the formulas you would need for your own reference, but

in reality the format of the original data is the format you needed all along.

Feel free to ignore it.

To "Refresh the pivot table" to show updated data, go to the "Data" tab menu at the top, and hit the Refresh (or Refresh All) button.
The Pivot table will refresh to show any changes made to the original table.

http://office.microsoft.com/en-ca/ex...101906071.aspx