Excel - Make a graph that shows number of occurrences of each value in a column

0 votes
I'm attempting to create a graph that displays each value in a column along with how frequently it occurs, similar to a bar graph where the number of occurrences is on the y-axis and the value is on the x-axis. How will I go about doing this?

I'm utilizing Excel 2013.
Oct 21, 2022 in Others by Kithuzzz
• 27,940 points
306 views

1 answer to this question.

0 votes

There is probably a better way to do this, but this is a working example. Let's assume this is your data:

+---+
| 4 |
| 4 |
| 5 |
| 6 |
| 7 |
| 7 |
| 7 |
| 8 |
| 9 |
+---+

This column should be copied and pasted into column B. Click Remove Duplicates after selecting it. Add the following formula to C1: =COUNTIF (A:A;B1) (Use a; in Excel 2010 and later, use a, otherwise. Click and drag the black square from C1's bottom right corner all the way down to column B's bottom.

Now, your spreadsheet ought to resemble the following (with the formula result instead of the formula itself):

+---+---+------------------+
| A | B |        C         |
+---+---+------------------+ // Actual values of column C
| 4 | 4 | =COUNTIF(A:A;B1) | // 2
| 4 | 5 | =COUNTIF(A:A;B2) | // 1
| 5 | 6 | =COUNTIF(A:A;B3) | // 1
| 6 | 7 | =COUNTIF(A:A;B4) | // 3
| 7 | 8 | =COUNTIF(A:A;B5) | // 1
| 7 | 9 | =COUNTIF(A:A;B6) | // 1
| 7 |   |                  |
| 8 |   |                  |
| 9 |   |                  |
+---+---+------------------+

Finally, create a graph as you normally would. Make your Legend Entries (Series) your column C, and your Horizontal (Category) Axis Labels column B.

This will result in a graph looking like this:

Resulting Graph

answered Oct 21, 2022 by narikkadan
• 51,600 points

Related Questions In Others

0 votes
1 answer

Is there a maximum number of formula fields allowed in Excel (2010)

See http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx for limits on specs it doesn't indicate ...READ MORE

answered Sep 30, 2022 in Others by narikkadan
• 51,600 points
93 views
0 votes
1 answer

Is there any way in python to auto-correct spelling mistake in multiple rows of an excel files of a single column?

Use Spellchecker for doing your stuff: import pandas ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 51,600 points
295 views
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 51,600 points
142 views
0 votes
1 answer

Get number of columns of a particular row in given excel using Java

Use: int noOfColumns = sh.getRow(0).getPhysicalNumberOfCells(); Or int noOfColumns = sh.getRow(0).getLastCellNum(); There ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 51,600 points
468 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
523 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

answered Oct 3, 2018 in Python by Priyaj
• 58,100 points
471 views
0 votes
1 answer

In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

answered Oct 15, 2018 in RPA by Priyaj
• 58,100 points
3,488 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
2,996 views
0 votes
1 answer

How to unmerge multiple cells and transpose each value into a new column in Pandas dataframe from excel file

Try this: df = pd.read_excel("Sample_File.xlsx", header=[0,1,2,3,4,5], index_col = ...READ MORE

answered Jan 8 in Others by narikkadan
• 51,600 points
91 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP