# Yet another Excel question **URL:** \ **Category:** Factual Questions\ **Created:** [December 28, 2011, 7:32pm UTC](https://boards.straightdope.com/t/yet-another-excel-question/607658 "2011-12-28T19:32:48Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![Rick](https://avatars.discourse-cdn.com/v4/letter/r/9f8e36/32.png) [@Rick](https://boards.straightdope.com/u/Rick)\ **Post date:** [December 28, 2011, 7:32pm UTC](https://boards.straightdope.com/t/yet-another-excel-question/607658/1 "2011-12-28T19:32:48Z")
OK, I have a 21,000 row spreadsheet. (Yeah I know :eek:) I have one column of information I am concerned with. I want to know how many times each discrete entry shows up on this list. For example some of the values in that column are 7500, 7500S, \*, 15000, 15000S, etc. I want to know how many times each of those show up. I know I could sort the values in the column and start counting, but with 21,000 rows, I don’t want to invest that kind of time. So how can I do this? ---
**Author:** ![sandra\_nz](https://avatars.discourse-cdn.com/v4/letter/s/eada6e/32.png) [@sandra\_nz](https://boards.straightdope.com/u/sandra_nz)\ **Post date:** [December 28, 2011, 7:33pm UTC](https://boards.straightdope.com/t/yet-another-excel-question/607658/2 "2011-12-28T19:33:47Z")
You want to make a pivot table. Have you worked with pivot tables before? ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [December 28, 2011, 7:42pm UTC](https://boards.straightdope.com/t/yet-another-excel-question/607658/3 "2011-12-28T19:42:36Z")
Yup - a pivot table will crank that out instantly. Which version of Excel do you have? ---
**Author:** ![mbetter](https://avatars.discourse-cdn.com/v4/letter/m/53a042/32.png) [@mbetter](https://boards.straightdope.com/u/mbetter)\ **Post date:** [December 28, 2011, 7:51pm UTC](https://boards.straightdope.com/t/yet-another-excel-question/607658/4 "2011-12-28T19:51:56Z")
A pivot table is the right answer but, just for the hell of it, here’s a way to do it with a formula. Assuming the values you are interested in are in column A, starting at row 1, put this in B1: ```auto =COUNTIF($A:$A,A1) ``` ---
**Author:** ![Tim\_R.Mortiss](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/tim_r.mortiss/32/142_2.png) [@Tim\_R.Mortiss](https://boards.straightdope.com/u/Tim_R.Mortiss)\ **Post date:** [December 28, 2011, 7:52pm UTC](https://boards.straightdope.com/t/yet-another-excel-question/607658/5 "2011-12-28T19:52:02Z")
You could probably do it more easily with a histogram. ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [December 28, 2011, 7:52pm UTC](https://boards.straightdope.com/t/yet-another-excel-question/607658/6 "2011-12-28T19:52:58Z")
If it’s 2007, here’s what we do. Let’s work with the following sample data: ```auto A B C 1 Name Data 1 Data 2 2 Albert apples 5 3 Albert oranges 8 4 Brian apples 2 5 Candy oranges 9 6 Dan apples 4 7 Eddie oranges 5 8 Frank apples 8 9 Frank oranges 3 10 Frank pears 1 11 Frank grapes 7 ``` 1. Highlight the table - I’d drag my mouse from A to C, since you have a ginormous data set to work from with your real file. 2. Go to the “Insert” tab on the ribbon, click under “PivotTable” on the far left, and select “PivotTable”. 3. Confirm that the table selected is correct, and that you’re placing it in a new worksheet. 4. For what you want to do (count the instances of “apples” v. “oranges” v. “pears”, etc.), drag “Data 1” down to the “Row Labels” box, and then drag it again to the “Values” box. That should do it, and bring up the following: ```auto Row Labels Count of Data 1 apples 4 grapes 1 oranges 4 pears 1 (blank) Grand Total 10 ``` If you wanted to know how many times each name came up, you’d do the same step #4, but with the “Name” column, and get this: ```auto Row Labels Count of Name Albert 2 Brian 1 Candy 1 Dan 1 Eddie 1 Frank 4 (blank) Grand Total 10 ``` ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [December 28, 2011, 7:58pm UTC](https://boards.straightdope.com/t/yet-another-excel-question/607658/7 "2011-12-28T19:58:17Z")
And where the pivot really gets value is when you plug in “Name” and “Data 1” for the Row Label, “Data 2” for the Value, and then change the value from Count to Sum (by clicking on “Count of Data 2”, selecting “Value Field Settings” and selecting “Sum”. Feel free to try it at home. ---
**Author:** ![Rick](https://avatars.discourse-cdn.com/v4/letter/r/9f8e36/32.png) [@Rick](https://boards.straightdope.com/u/Rick)\ **Post date:** [December 28, 2011, 8:49pm UTC](https://boards.straightdope.com/t/yet-another-excel-question/607658/8 "2011-12-28T20:49:33Z")
I’m on 2007, and I’m reading up on piviot tables right now. What is a histogram? I’ve never heard the term. ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [December 28, 2011, 9:03pm UTC](https://boards.straightdope.com/t/yet-another-excel-question/607658/9 "2011-12-28T21:03:30Z")
Rick, the nice thing about pivot tables is that you can move things around on a whim without doing a thing to your data. Feel free to start one up and play around with dragging headers around, into different boxes, etc. It’s a really worry-free tool that you’ll get more out of the more you tinker with. ---
**Author:** ![Chessic\_Sense](https://avatars.discourse-cdn.com/v4/letter/c/7c8e57/32.png) [@Chessic\_Sense](https://boards.straightdope.com/u/Chessic_Sense)\ **Post date:** [December 28, 2011, 9:05pm UTC](https://boards.straightdope.com/t/yet-another-excel-question/607658/10 "2011-12-28T21:05:31Z")
The thing about pivot tables is you’ve got to drag the right thing to the right spot. In order to get things in the data field, drag the dependent variable to the top left corner. Not the top, not the left, but the top-left. Have fun. ---
**Author:** ![Rick](https://avatars.discourse-cdn.com/v4/letter/r/9f8e36/32.png) [@Rick](https://boards.straightdope.com/u/Rick)\ **Post date:** [December 28, 2011, 9:13pm UTC](https://boards.straightdope.com/t/yet-another-excel-question/607658/11 "2011-12-28T21:13:27Z")
OK, I have managed to create a pivot table and report showing the data I want. I have two columns in the report, the data and the count. looks like this: ```auto Col A Col B 094 3 093 5 * 1,587 15K 200 ``` Now I want to sort the data so that is is in either ascending or descending order of Column B. So far that solution has eluded me. ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [December 28, 2011, 9:23pm UTC](https://boards.straightdope.com/t/yet-another-excel-question/607658/12 "2011-12-28T21:23:55Z")
If you really need it in that order, you can just copy the data and paste it into a new chart and sort from there. ---
**Author:** ![Rick](https://avatars.discourse-cdn.com/v4/letter/r/9f8e36/32.png) [@Rick](https://boards.straightdope.com/u/Rick)\ **Post date:** [December 28, 2011, 9:37pm UTC](https://boards.straightdope.com/t/yet-another-excel-question/607658/13 "2011-12-28T21:37:04Z")
Got it, Thanks!