Creating a simple chart in excel -- why is it so hard?

%3CLINGO-SUB%20id%3D%22lingo-sub-1133561%22%20slang%3D%22en-US%22%3ECreating%20a%20simple%20chart%20in%20excel%20--%20why%20is%20it%20so%20hard%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1133561%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20create%20a%20simple%20chart.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%20--%20I%20want%20to%20chart%20the%20following%20data.%3C%2FP%3E%3CP%3EI%20have%20a%20service%20that%20uses%20a%20freemium%20model.%26nbsp%3B%20The%20first%2020%20accounts%20are%20free%2C%20but%20I%20will%20charge%20%245%20per%20account%20for%20managing%2021%20or%20more.%26nbsp%3B%20I%20will%20cap%20the%20amount%20at%20475%20accounts%20which%20equals%20%242375.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20chart%20should%20look%20like%20this....%20(I%20drew%20this%20on%20PowerPoint)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F167713iBD67338B4DF5ED2F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20data%20from%20Excel%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20288px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F167714iA890760DD2366112%2Fimage-dimensions%2F288x629%3Fv%3D1.0%22%20width%3D%22288%22%20height%3D%22629%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20chart%20this%20here%20is%20what%20I%20get%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F167716iFC028FC1BC0041F9%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_2.png%22%20title%3D%22clipboard_image_2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%3F!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20I%20try%20and%20not%20label%20the%20%22Accounts%22%20column%20--%20see%20below%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F167718i5883E9991E641C32%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_3.png%22%20title%3D%22clipboard_image_3.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20that%20ALMOST%20looks%20right%20BUT%20not%20the%200%20(zero)%20values%20on%20the%20X%20axis.%26nbsp%3B%20They%20are%20the%20count%20of%20the%20cells%20where%20the%20data%20is%20stored%2C%20NOT%20the%20values%20in%20the%20%22Account%22%20colums!%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20475px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F167723i6B562DE0E9418DA7%2Fimage-dimensions%2F475x411%3Fv%3D1.0%22%20width%3D%22475%22%20height%3D%22411%22%20alt%3D%22clipboard_image_4.png%22%20title%3D%22clipboard_image_4.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EReally%3F%26nbsp%3B%20Seriously%3F!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20tired%20of%20feeling%20like%20an%20idiot%20by%20myself%2C%20thought%20I%20would%20share%20and%20see%20if%20someone%20could%20help%20me...%20on%20what%20I%20thought%20would%20be%20a%20SIMPLE%20task.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20to%20anyone%20who%20can%20respond.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1133561%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1133718%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20simple%20chart%20in%20excel%20--%20why%20is%20it%20so%20hard%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1133718%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F536058%22%20target%3D%22_blank%22%3E%40Roger895%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20%22Accounts%22%20column%20enter%20all%20values%201%20thru%205000%20with%20autofill.%3C%2FP%3E%3CP%3EIn%20cell%20C4%20enter%20the%20formula%3A%3C%2FP%3E%3CPRE%3E%3DIF(%24B4%26lt%3B%3D20%2C0%2CIF(B4%26gt%3B%3D475%2C%24E%244*475%2C%24B4*%24E%244))%3C%2FPRE%3E%3CP%3E%26nbsp%3Band%20copy%20down%20using%20autofill.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1133978%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20simple%20chart%20in%20excel%20--%20why%20is%20it%20so%20hard%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1133978%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B--%20thanks%20for%20the%20tip.%26nbsp%3B%20I%20put%20the%20data%20together%20to%20show%20the%20issue%20with%20my%20charting%20problem%20--%20it%20was%20rushed.%26nbsp%3B%20Any%20ideas%20on%20the%20chart%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1134379%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20simple%20chart%20in%20excel%20--%20why%20is%20it%20so%20hard%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1134379%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F536058%22%20target%3D%22_blank%22%3E%40Roger895%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20looks%20like%20you%20need%20to%20click%20Edit%20on%20right%20pane%20and%20assign%20Accounts%20column%20as%20horizontal%20axis%2C%20plus%20remove%20it%20from%20Series.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1134380%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20simple%20chart%20in%20excel%20--%20why%20is%20it%20so%20hard%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1134380%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F536058%22%20target%3D%22_blank%22%3E%40Roger895%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20an%20alternative%20solution%3A%3C%2FP%3E%3CP%3EChange%20your%20chart%20type%20to%20X%20Y%20(Scatter)%20%26gt%3B%20Scatter%20with%20Straight%20Lines.%3C%2FP%3E%3CP%3ERemove%20the%20markers%20by%20left%20clicking%20the%20line%20%26gt%3B%20Series%20Options%20%26gt%3B%20Marker%20%26gt%3B%20Marker%20Options%20%26gt%3B%20None%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I want to create a simple chart.

 

Example -- I want to chart the following data.

I have a service that uses a freemium model.  The first 20 accounts are free, but I will charge $5 per account for managing 21 or more.  I will cap the amount at 475 accounts which equals $2375.

 

The chart should look like this.... (I drew this on PowerPoint)

 

clipboard_image_0.png

 

Here is the data from Excel:

 

clipboard_image_1.png

 

When I chart this here is what I get:

 

clipboard_image_2.png

 

What?!

 

Then I try and not label the "Accounts" column -- see below:

clipboard_image_3.png

 

and that ALMOST looks right BUT not the 0 (zero) values on the X axis.  They are the count of the cells where the data is stored, NOT the values in the "Account" colums!  

 

clipboard_image_4.png

 

Really?  Seriously?!

 

I am tired of feeling like an idiot by myself, thought I would share and see if someone could help me... on what I thought would be a SIMPLE task.

 

Thanks in advance to anyone who can respond.

 

4 Replies
Highlighted

Hello @Roger895,

 

In the "Accounts" column enter all values 1 thru 5000 with autofill.

In cell C4 enter the formula:

=IF($B4<=20,0,IF($B4>=475,$E$4*475,$B4*$E$4))

 and copy down using autofill.

Highlighted

@PReagan -- thanks for the tip.  I put the data together to show the issue with my charting problem -- it was rushed.  Any ideas on the chart?

Highlighted

@Roger895 

It looks like you need to click Edit on right pane and assign Accounts column as horizontal axis, plus remove it from Series.

Highlighted

@Roger895 

 

As an alternative solution:

Change your chart type to X Y (Scatter) > Scatter with Straight Lines.

Remove the markers by left clicking the line > Series Options > Marker > Marker Options > None