How to come up with the percentage and chart?

Copper Contributor

Hello, 

 

I'm trying to get the percentage of each group shown in the screenshot (A, B, and C). 

I have the percentage by manually calculating each category, but I want to know if there is a feature or a formula to get the percentages easier and faster. 

 

GroupA: 55%

GroupB: 27%

GroupC: 14%

 

Also, when I try to make a chart (column chart) to show the percentages between the groups (A, B, and C), It doesn't work... I just highlighted everything and insert>selected 2D column chart but please let me know how I can do this correctly.

 

Thank you so much for reading my questions.  

 

 

3 Replies

@excelforjeff 

 

It would be easier to help you if you posted not an image but the actual spreadsheet from which you took that image. Otherwise you're essentially asking us to re-create the spreadsheet from your image.

 

So I'll try to give a verbal picture of what I would do if I actually had the spreadsheet here. Part of the problem (and this is not a unique instance of it) is that you've done the work of separating the groups into A, B and C, rather than letting Excel do that for you. So here are the steps I'd first do.

  1. Create a column on the left of all these (assuming you're at the left edge of the sheet already)
  2. Make the Header for that new column "Group"
  3. Insert the letter A for each row that is part of A, B for each B-row, C for each C-row
  4. Eliminate those rows that are no longer necessary (that are redundant) (the ones that head up Groups B and C)

 

At this point you'll have the essence of an Excel Table, and if you'd like, you can put  your cursor anywhere in those rows and columns and do an Insert....Table command.

 

From there, I'm pretty sure that you'll find that you can create a Pivot Table to produce the summaries you're looking for.

Excel excels at doing the kind of analysis you're wanting to do...but you have to let IT do the work of breaking apart the data. When you manually create the kind of visually distinct sets of data, as you've done, you actually interfere with its ability to do so. It's a parallel of Obi-wan's plea to Luke, "Trust the force, Luke" ---- Trust Excel, Jeff.

 

If you don't know how to create a Pivot Table, come back with that cleaned up spreadsheet and somebody here will show you how.

@mathetes 

 

Thanks much! @mathetes 

So I've attached my spreadsheet (thanks for pointing that out) and created a new sheet (sheet2) based on the info you advised. 

 

I think it's starting to make more sense now. I just started to learn excel, so I will need to review more materials to get a better understanding of pivot table (I just know that pivot let's you rearrange, sort data in a much efficiently way but I haven't really used it before). 

 

But as for charting, do you have any recommendation, of what I can do? I just want to show the audience the difference of impact per the system environment (version, hardware etc) using charts. 

 

@excelforjeff 

 

There are now two pivot tables on your last sheet here. And a graph. And a large text box describing what I did.

 

I really think you'll enjoy Pivot tables. You'll quickly see that Excel can work with a database such as you now have to do the kind of summary analyses that you were trying to do.

 

There are on-line resources that could help you learn the ins and outs of Pivot Tables. YouTube has, I've discovered, lots of very good resources.

 

Here, as well, is an on-line resource I've found helpful. https://exceljet.net/excel-pivot-tables