Four Quadrant Chart Help

nniedzielski

Well-known Member
Joined
Jan 8, 2016
Messages
566
Office Version
  1. 2019
Platform
  1. Windows
Hello-

I was given a chart to work with to track impact of projects. It plots a Data Label (yellow circle with number in it) on the chart. It works great, but i want to expand it down to 100 projects. I have played with it all day and cannot figure out how to build more plot points and make the data label look the same: (one is plotted right now at 5,5)

1620330336710.png

This is what the data looks like being plotted, the one plotted above is the test in this data:

1620330390293.png

This is what the Select Data Source Looks Like:

1620330441091.png


When I click Edit on number one, this is what shows in the edit series:

1620330479231.png


What can I do to drag this down to 100, and keep the yellow bubble with number as the data format? I have tried everything, and the closest i got was a dot on the chart with a number.

as always, i appreciate any help
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,968
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
In the Edit Series dialog, change $O$3 to $O$3:$O$100 and $N$3 to $N$3:$N$100.
 

nniedzielski

Well-known Member
Joined
Jan 8, 2016
Messages
566
Office Version
  1. 2019
Platform
  1. Windows
In the Edit Series dialog, change $O$3 to $O$3:$O$100 and $N$3 to $N$3:$N$100.
that gave me the bubble i wanted, but the bubble has the number 1 in it, even though i entered the data in the 46th row. How can I ensure the bubble will have the corresponding # in it?
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,968
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Oh. The data labels by default show the Y value. You can also format them to show the X value or series name.

But if you want them to show something else, you need to use a different approach.

Add a column that has the number you want displayed, 1 to whatever. Not knowing how you got the initial data label in the first point, delete the data labels, then add them again. Select the data labels and click Ctrl+1 to format them. Select the Values from Cells option and select this added range with the point numbers, then uncheck the other boxes.
 

nniedzielski

Well-known Member
Joined
Jan 8, 2016
Messages
566
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Oh. The data labels by default show the Y value. You can also format them to show the X value or series name.

But if you want them to show something else, you need to use a different approach.

Add a column that has the number you want displayed, 1 to whatever. Not knowing how you got the initial data label in the first point, delete the data labels, then add them again. Select the data labels and click Ctrl+1 to format them. Select the Values from Cells option and select this added range with the point numbers, then uncheck the other boxes.
that is perfect and saved me soo much time having to try and do 100 of them manually. thank you!!
 

nniedzielski

Well-known Member
Joined
Jan 8, 2016
Messages
566
Office Version
  1. 2019
Platform
  1. Windows
Oh. The data labels by default show the Y value. You can also format them to show the X value or series name.

But if you want them to show something else, you need to use a different approach.

Add a column that has the number you want displayed, 1 to whatever. Not knowing how you got the initial data label in the first point, delete the data labels, then add them again. Select the data labels and click Ctrl+1 to format them. Select the Values from Cells option and select this added range with the point numbers, then uncheck the other boxes.
i was too quick to say its working, im not getting the number i need in the bubble along with the x and y, but i have things checked properly, i believe

1620342148673.png
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,968
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

i was too quick to say its working, im not getting the number i need in the bubble along with the x and y, but i have things checked properly, i believe

View attachment 38282
What's the data look like? Did you select the right range for the labels?
 

nniedzielski

Well-known Member
Joined
Jan 8, 2016
Messages
566
Office Version
  1. 2019
Platform
  1. Windows
Heres the problem right now, if i go in and check data labels, go to more options, select value from cells, and put in column L, it works, when i close the document, reopen it, try to add another one, it doesnt work, i get the bubble but no number in it, i have to go in and work the data labels portion again. I cant figure out what the saving and closing problem is.

this is what the data looks like:

#Project NameValueEaseSubmitted ByDate
1​
test 1125TBA5/6/2021
2​
test 2136TBA5/6/2021
3​
test 3168TBA5/6/2021
4​
test 4123TBA5/6/2021
5​
test 5103TBA5/6/2021
6​
test 6915TBA5/6/2021
7​
test 7104TBA5/6/2021
8​
test 8143TBA5/6/2021
9​
test 991TBA5/6/2021
10​
test 10108TBA5/6/2021
11​
test 1164TBA5/6/2021
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,968
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Select that same and convert it to a Table (Ctrl+T). When you add a row of data, the new X and Y will be plotted, and the new label will be applied to the point.

Tables are a great data structure in Excel, and unfortunately not enough people know about them.
 

nniedzielski

Well-known Member
Joined
Jan 8, 2016
Messages
566
Office Version
  1. 2019
Platform
  1. Windows
Select that same and convert it to a Table (Ctrl+T). When you add a row of data, the new X and Y will be plotted, and the new label will be applied to the point.

Tables are a great data structure in Excel, and unfortunately not enough people know about them.
I did that, still the same problem, once i save and close, and reopen, then put in a new line of data, i get a bubble but no number in it, then i can do it again, but it wont stay that way when i save
 

Forum statistics

Threads
1,143,840
Messages
5,721,101
Members
422,339
Latest member
SHIVATVM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top