Santa Clara University

summer-web

Software Tips & Tricks

Back to Blog

How to create a bell curve-like chart in Excel

Bell curve-like chart in Excel

Excel doesn't have a simple way of creating a bell-curve out of data, but by using the FREQUENCY formula and an XY scatterplot, you can get a similar-looking chart for visual purposes.

  1. Start by setting up your data into columns. Make sure you have a column dedicated to values or "bins" that you want to plot on the chart. In this example of student scores, column E contains bins from 70 (the lowest score) to 100 (the highest score) with increments of 5 in between. The information in column D is optional, but explains to a user what scores fall within a particular bin.
  2. Next, highlight the empty cells adjacent to the bins. In this example, that would be cells F2–F8. The frequency that each value in a bin occurs among the students' scores in column B will be calculated in these answer cells. Remember that the bins represent a range of values. The bin '80' in cell E4  includes all scores that are greater than 75 but less than or equal to 80.
  3. The frequency formula is =FREQUENCY(data_array, bin_array).
  4. With cells F2-F8 highlighted, begin to type the formula =FREQUENCY(
  5. Select the student scores (B2–B21) to add the data_array argument to the formula.
  6. Type F4 (PC) or Command+D (Mac) to make the cell references absolute. This puts a dollar sign in front of the column letter and row number, for example $B$2.
  7. Now type a comma (,) in your formula and then highlight the bins (E2–E8) to add the bin_array argument to the formula.
  8. Your formula should now read: =FREQUENCY($B$2:$B$21, E2:E8).
  9. To enter the formula, press Shift+Control+Return (PC) or Shift+Command+Return (Mac), not just 'Return' or 'Enter'. This is the way all array functions are entered.
  10. Now that you have your data (F2–F8), you can create your chart. Select the bins (E2–E8) and choose the XY (Scatter), Smooth Line chart by using one of the following methods: In Excel 2003 and 2004 click the chart wizard icon on the toolbar; in Excel 2007 click Insert on the ribbon and go to the Charts group; in Excel 2008 click the Gallery and choose Charts.
  11. If you use the chart wizard, follow the steps to create the XY (Scatter), smooth line chart.
  12. Your chart should look like this:

Comments Comments

Cecil said on Apr 10, 2013
the instructions are incomplete....
mathwizard said on Jun 8, 2013
the graph didn't come proper.
chip said on Jun 28, 2013
TO make the graph appear, select the 'value' colum and the 'frequency' column by dragging the mouse across the cells, then select 'Insert' > 'Scatter' > 'Scatter with lines'.
Dongmongler said on Sep 1, 2014
This sort of worked... needs some more clarity
sf said on Sep 17, 2014
Worked fine for me - thanks!
shanica smith said on May 21, 2015
Am very happy to tell every one to hear my testimony. Am a nurse,this is a story of my love life.I have been married for 4years and on the fifth year of my marriage, another woman take my lover away from me and my husband left me and the kids and we have suffered for 4months until i met a post where this man Dr OLOKUM have helped someone and i decided to give him a try to help me bring my lover back home and believe me i just send my picture to him and that of my husband and after 48hours as he have told me, i saw a car drove into the house and behold it was my husband and he have come to me and the kids and that is why i am happy to make every one of you in similar to met with this man and have your lover back to your self.You can contact him with this email address {LAVENDERLOVESPELL@YAHOO.COM} Thank you Dr OLOKUM LAVENDERLOVESPELL@YAHOO.COM
livinus said on Aug 3, 2015
Hello friends, My name is livinus I?m from Canada but i based in Spain. Been living in Spain for years now.I never believed in all spell casting.Have been scammed by many different people.I lost hope in them.My ex left me.I was devastated.I lost my job.My best friend saw my plight Then he introduced me to lord aguma. Never believed until i got in touch with him.He did a spell for me. i recover all my money and My ex came back in less than 72hrs.I was recalled at my place of work. i was even promoted immediately. couldn?t believe there is a real spell caster out there who does free spells.I only paid in appreciation after the spell had worked.He doesn?t charge anybody until the spell has worked.You all shouldn?t be deceived by all these scammed that are here for money. lord aguma doesn?t charge.You can only pay for the items the spell will worked.Try him and see what am saying.These are the few things he does *HE BRING BACK LOST LOVERS IN 24hrs. *REMOTE CONTROL OVER LOVERS. *ALL TYPES OF SPELL CASTING & VOODOO WORKER. IS YOUR HEALER TAKING LONG TO SOLVE YOUR PROBLEMS? THIS IS YOUR CHANCE TO BE HELPED QUICKLY. *GET RIDE OF ANY WITCHCRAFT, BAD LUCK & CURSES. * EXPERT IN DISTANCE HEALING *COURT CASES EVEN IF ONE IS CONVICTED [IF THERE?S A CHANCE OF APPEALING] *IS SOME BODY JEALOUS WITH YOUR RELATIONSHIP? *UNFINISHED JOBS BY OTHER DOCTORS-IF NOT SATISFIED-COME TO ME. *ARE YOU BEING FORCED INTO A DIVORCE? *HE PREVENT BAD LUCK AND JEALOUSY. *TO WIN HORSES AND GAMBLING. *HE CAN STOP SOME ONE INTERFERING WITH YOUR RELATIONSHIP. * DO YOU WANT TO HAVE CHILDREN OF YOUR CHOICE *DO YOU WANT QUICK MARRIAGE PROPOSAL? *HE CAN CLEAN PEOPLE HOUSES,BUSINESSES AND PROPERTIES FROM BAD LUCK. *DO YOU NEED EXCESSIVE SEXUAL POWER AND STRONG ERECTIONS. * WOMEN, COME FOR THE TAMPON. IT WILL HELP YOU NOT TO LOSE YOUR RELATIONSHIPS BECAUSE IT MAKES YOU SWEET, WARM AND TIGHT.
Post a Comment

Tags: Excel

Printer-friendly format