• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Can we do in excel??

K

KiKi

Member
Hi I have attached data in "Graph" below sheet and want to know if attached graph, "9 customer-reviews dashboard" can be created in excel if yes the how?

Thanks for all help
 

Attachments

  • 9-customer-review-dashboard.png
    9-customer-review-dashboard.png
    116.3 KB · Views: 6
  • Graph.xlsx
    8.8 KB · Views: 3
Sure could. The image is a little small, but I'll make some guesses:

Pie chart:
Use a PivotTable or formulas to determine count of each item divided by total count. This gives you percentages. Plot as Pie Chart (personal note, I'd suggest a bar chart rather than pie. Much easier to read)

Top right-column chart:
Uses a straight COUNTIF type logic to figure out how many of each item there is.

Mid-left column chart:
Your example data didn't have a date field, but if it did, you could do another COUNTIF here

Mid-right table:
A COUNTIF for # of reviewers, and AVERAGE to get the 2nd column

Bottom table:
Use an AVERAGEIF for first line, AVERAGE for 2nd (note that it's the same number all the time), and COUNTIF for last line. Use some conditional formatting to get the arrows.

If you know what the groups/dates are going to be, I'd suggest using pre-built labels and categories w/ formulas. If it could vary widly, go with a PivotTable to generate the data, and then a chart based off of that.

Based on that dashboard, your ideal raw data layout would be:
Date | Company | Rating
 
Luke M said:
Sure could. The image is a little small, but I'll make some guesses:
Thanks. I forgot to mention that i want to draw a chart for part of pivot table. Do i need to write VBA code for it?

Thanks

Pie chart:
Use a PivotTable or formulas to determine count of each item divided by total count. This gives you percentages. Plot as Pie Chart (personal note, I'd suggest a bar chart rather than pie. Much easier to read)

Top right-column chart:
Uses a straight COUNTIF type logic to figure out how many of each item there is.

Mid-left column chart:
Your example data didn't have a date field, but if it did, you could do another COUNTIF here

Mid-right table:
A COUNTIF for # of reviewers, and AVERAGE to get the 2nd column

Bottom table:
Use an AVERAGEIF for first line, AVERAGE for 2nd (note that it's the same number all the time), and COUNTIF for last line. Use some conditional formatting to get the arrows.

If you know what the groups/dates are going to be, I'd suggest using pre-built labels and categories w/ formulas. If it could vary widly, go with a PivotTable to generate the data, and then a chart based off of that.

Based on that dashboard, your ideal raw data layout would be:
Date | Company | Rating
Click to expand...
 
I think it didn't get pasted..

I forgot to mention that i want to draw a chart for part of pivot table. Do i need to write VBA code for it?
 
Not necessarily. Depending on what part of the PT you want to chart, you could either use the GETPIVOTDATA function, or just a regular direct link (ie =B5) to extract the necessary information. Then, base your chart off of this extract.
 
Ohh thats really nice. I didn't get the second part : just a regular direct link (ie =B5).

Can you give a little bit detail on that?
 
I meant just a basic link formula. E.g., let's say you always want the first 5 rows of the PivotTable, which is in A4:B7. Somewhere else, make a cell with formula:
=A4
and copy down/right as needed. Formula is the msot basic, just looks at another cell.
 
You must log in or register to reply here.
Back
Top