Excel Help for Pie Graph of Frequency of Appearance of Values in a Column?

May 19, 2006 11:33 AM

ExcelFilter: I need help creating a pie chart as a separate worksheet in Excel that counts how often values appear in a column and creates a pie graph based on that data. I'm stumped, I'm not using the right Google terms, and my company's help desk couldn't guide me through the process, either. (That sounds awkward, so I describe it more comprehensibly inside.)

I am a legal assistant whose station changes from day to day and week to week (a "floater"). I keep an assignment log as an Excel spreadsheet, detailing where I worked each day, and any personal notes about the desk.

The horizontal subject headers are "date," "secretary," "department," "practice area" (a sort of 'subdepartment'), "attorney 1", "attorney 2", "attorney 3", "attorney 4", and "notes." The vertical headers are the dates I work.

I'm seeking to figure out how I can have Excel create, and automatically update, a pie chart based on the "department" column, and, optionally, to help me break down further in some clickable way the practice area off of the respective departments. The latter is not entirely necessary if it would add too much of a complicating factor.

Let's say, for example, that the department column of this spreadsheet looks like this:

Rotor-Scooting
RealityDistortionField-Creating
GatesAssassin
Metafilter-Reading
Widget-Making
Widget-Making
Widget-Making
Widget-Making
Rotor-Scooting
GatesAssassin

I'm looking to figure out how I can get Excel to create a pie chart that would have 40% widget-making, 20% rotor-scooting, 20% GatesAssassin, 10% Metafilter-Reading, and 10% RealityDistortionField-Creating ... based on its own, updated count of the frequency of appearance of the departments as compared against the total non-blank entries in the column in question.

And if all the following week, I worked for the BallmerAssassin department, it would automatically add in the appropriate slice and udpate the chart.

Keep in mind that since the number of entries in that column will change on a daily basis, I'd prefer not to "hardcore" into any existing formula or chart or what-have-you the rows part of the range, unless it can't be done otherwise.

Is what I'm talking about possible? I thought it'd be something that Excel could do with its eyes closed, but not only did I dredge up a big fat zero when trying to figure this out, so did my business' help desk. Any ideas?

posted by WCityMike to computers & internet (9 comments total)

Gah. ^udpate^update.
posted by WCityMike at 11:33 AM on May 19, 2006

You want to look up "CountIf" and the functions that are related to it, to find exactly what you want.

To give an example, the percentage, of say, Widget-Making in the range A1:A50 can be given with:

=CountIf(A1:A50,"Widget-Making")/CountA(A1:A50)

Just do that for each thing you're doing, and use those numbers as the datasource for the chart. You can easily have it update by using the Excel trick of having the range go one row past the end of your list and then when you go to insert a new row of data, use Insert Row and Excel will expand the range.
posted by skynxnex at 11:46 AM on May 19, 2006

Here's how I would do it (caveat: no doubt there's a way to do this 10 times faster/easier):

1. create a new worksheet
2. in one column, list all the possible departments you might work in.
3. in the column next to that, type in this formula:

=countif(('NameOfReferenceSheet'!XA:XN),"-departmentname-")

where NameOfReferenceSheet is the sheet where you're logging the data, XA:XN is the column where they reside (the full column including empty cells that one day will be populated with a name), and -departmentname- is the department name exactly as you would spell it in the main list. You can do this easily if you start typing in the =countif(( into the cell where the result should appear, then click back to the tab where you have your list, and select the entire colum where the department names are.

You can copy this formula down the column, changing it each time to reflect the proper -departmentname-.

This will give you a count of how many times that string (departmentname) appears in the column on the original sheet.

Then you can tally them, and make another column dividing up the counts from each of the results above into the total, which gives you the %. And from this table you can make your pie chart.
posted by contessa at 11:47 AM on May 19, 2006

I agree with the above posters ideas,

For more esoteic questions about excel, there is also the great info at www.mrexcel.com (no affiliation, just like it).
posted by defcom1 at 11:55 AM on May 19, 2006

The feature you want to use is Pivot Table. It's super flexible yet easy and requires no formulas. Then create your chart based on that. Get someone in your office to quickly demo it to you -- it's much easier to understand visually than from a written explanation.
posted by randomstriker at 12:25 PM on May 19, 2006

See, I knew there was a better way...I wish I knew how to use pivot tables.
posted by contessa at 12:31 PM on May 19, 2006

Actually, the COUNTIF stuff did the trick. Very much obliged to all! :)
posted by WCityMike at 12:35 PM on May 19, 2006

The ideal is a PivotChart.

1. Highlight columns A-C (Date, Secretary, Department)
2. Select Data > PivotTable and PivotChart Report
3. Check Microsoft Office Excel list or database and check PivotChart report
4. The range you highlighted should already be in the Range field
5. Click Next and select a "New Worksheet" for the chart
6. In the new Chart worksheet, right-click on a blank area and set the Chart Type to Pie
7. From the PivotTable Field List popup window, click on Department and drag it onto the blue box on the right side for Category Fields.
8. Grab the Date field from popup window and drop it over the gray area in the center of the pie chart.

Voila! No formulas to write and you don't have to insert new rows.
posted by junesix at 4:33 PM on May 19, 2006

Turns out $C:$C works well to avoid creating new rows. Thanks again, all. :)
posted by WCityMike at 3:01 PM on May 20, 2006