Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

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

Copper 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.

 

5 Replies

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.

@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?

@Roger895 

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

@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

@Roger895 Everything having to do with charts in Excel is now horribly fubarred. Everything that used to happen automatically (and properly imo) now has its own setting and all those settings are hidden in a dozen places scattered around the place. F'rinstance to add a title to a chart axis one now has to know to click on the little plus sign that appears when you select the chart; exactly which axis labels appear is controlled by another setting that pops up in a sidebar and seems to be set to automatically exclude the last label in a series; and there seems to be no way to specify a range of values for an axis. This is all for one element of what should be, as you state, a simple 2D line chart. What it takes to do anything more complex I shudder to think.