Formatting Axis Of Bubble Chart

Aug 2, 2013

I created a bubble chart whose x-axis and y-axis show every 10th value between 0 and 100 (i.e. 0, 10, 20, 30...). The axes intersect at (50, 50) to create 4 quadrants.

I want to erase/hide the values on the axes (i.e. the axes will no longer show 0, 10, 20, 30... and at the same time, the bubbles will still be in the same place) to create 4 blank quadrants and make my bubble chart easier to look at.

View 4 Replies


Conditional Formatting In Bubble Chart

Nov 26, 2013

I have the table: chart1.png

If the bubble size is 0-5, I need the bubble color to be green
If the bubble size is 6-15, I need the bubble color to be yellow
If the bubble size is 16-25, I need the bubble color to be red.

So, right now my chart looks like this: chart.png

As you can see the top bubble is the right color, but based on the bottom bubble value, it should be green.

How do I make this change colors dynamically based on the bubble size value?

View 1 Replies View Related

Conditional Formatting Of Bubble Chart Points Based On Bubblesizes

Jul 26, 2012

I am trying to format the colors of the bubbles on my bubble chart to Green (>5), Yellow (4-5), or Red (<4) based on the value used to create the bubblesize.

The problem that I am having is that .BubbleSizes is property of type String, not range like .xValues, or .Values. So instead of pulling in the value, I am pulling in the reference and getting a "Type Mismatch" Error. The reference of my data for .BubbleSizes is $D$5:$D51. Each row is it's own series and the list is dynamic, so I need to be able to support future rows without a lot of maintenance.

Here is the code that I have so far.

Sub DataSeriesFormat()
' DataSeriesFormat Macro
' Format Data Series based on defined parameters for Green, Yellow, and Red. Created by Derek Steinmetz 7/24/12
Dim x As Integer
Dim val As Variant
Dim Green As Range

[Code] ......

View 1 Replies View Related

Formatting X Axis On Scatter Plot Chart

Jul 27, 2007

Attached is a chart I am trying to create. So basically, I originally used a line chart but it didn't have the flexibility of the scatter chart, but i am having a very hard time formatting the X axis. First of all - I would like the line in the middle at the 0% point to actually go to the bottom so that the x axis labels aren't in the middle of the graph. Second the x axis labels aren't showing up correctly. They should be quarters: 1Q:02, 2Q:02, 3Q:02, 4Q:02... Finally, how can I show the correlation between these two data points plotted - the R-squared. THANKS!!! Auto Merged Post;I actually was able to figure out most of what I asked. What is remaining: how do I calculate the R-Squared on the chart? Isn't there a way to do it automatically in excel on the chart.

View 3 Replies View Related

Conditional Formatting Percentage Axis For Thermometer Chart?

Jun 11, 2014

I currently have a thermometer chart in Excel 2013 that I set up with percentages along the Y-Axis. The thermometers are showing spending based on a budgeted number. Currently, the project is over-budget so I have adjusted the thermometer scale to go far beyond 100%.

I would like to have the percentages past 100% show in red along the lefthand side.

View 2 Replies View Related

Excel 2010 :: Formatting Chart Axis Values?

Oct 8, 2011

Why the recorded code doesn't work? I recorded the code below to format the Y-axis values of a chart with the recorder.

With Selection.Format.TextFrame2.TextRange.Font
.NameComplexScript = "Arial"


View 6 Replies View Related

Conditional Formatting Of Chart Based On Category Axis Value

May 24, 2005

I am trying to format the colour of a column chart based on the value of the x axis (not the y axis value). Firstly I need to know if it is possible. Secondly I would appreciate any hints / VBA code that could be useful.

View 4 Replies View Related

Combine (Overlay) Bubble Chart & Point Scatter Chart

Jun 30, 2009

I would like combine (overlay) a bubble chart with a connected point scatter chart. I understand that, without VBA, this is not possible. However, I understand that, by using VBA, the markers of a scatter chart can be configured as circles with their size proportional to values in a specified column. This pseudo-bubble chart can then easily be combined with a connected point scatter chart.

My question is: does anyone have any VBA code to share that shows how to configure a scatter chart as the type of pseudo-bubble chart described above.

View 2 Replies View Related

Convert Scatter Chart To Bubble Chart

Jun 28, 2009

I would like to combine / overlay a bubble chart and scatter chart with straight connectors.

I understand that, without VBA, it is not possible to combine a bubble chart with a scatter chart.

Unfortunately, I am a VBA newbie, and so I cannot write my own code (though I can usually adapt code to my specific environment).

My question is: does anyone have VBA code to share that will convert a scatter chart series (x,y data in 2 columns) to a bubble-style chart (bubble radius in 3rd column)?

View 2 Replies View Related

Quadrant Bubble Chart

Sep 27, 2007

create a Quadrant Bubble chart. I have found examples of using scatter charts but unfortantely those charts do not satisfy what I am trying to accomplish.

View 5 Replies View Related

Create Chart With Names In Vertical Axis And Values In Horizontal Axis?

Mar 1, 2014

I have a spreadsheet with golfers handicaps, golfers names down the left in column A in rows 3-35. Row 2 has the event numbers titles 1 to 18 (18 events in a year). i need a graph that has the person as the vert axis and the horizontal axis needs to be the event numbers, so i can read down for the person and across to see how there handicaps change from each different event. i have attached the data below.

View 1 Replies View Related

Draw Log Chart Or Edit Axis Intervals And Starting Axis Value

Apr 20, 2007

I have some numeric data that has to be put on a log chart. but there is no option for log charts. so i converted my data to log and then drew a chart but i am unable to edit the axis intervals and starting axis value. whenever i change the value it gets back to its default value,ie 1. can anyone tell me how to draw log chart or how to edit axis intervals and starting axis value?

View 6 Replies View Related

Plotting Cities On Bubble Chart?

Apr 10, 2012

I have the Lat and Long for each of several US cities. I want to use these to create a bubble chart that plots the cities and which I can place over a US map picture.

However, I have the problem that, because of the curvature of the earth, the city locations form the lat and longs are not tying with the map.

how to adjust the lat and longs so that they will correspond to the typical US map?

For example, the base lat and longs for Denver are 39.5742 and -104.8588. What formulas can I use to modify these to match the standard map projection?

View 9 Replies View Related

Automate New Series For Bubble Chart

Apr 15, 2007

Iím trying to take an existing bubble chart, paste new data into the sheet,
then march sequentially from row to row, adding specific cells within each as a new series in the chart until I run out of rows. Below is my latest attempt (not working of course).

Sub setseries1()
Range("A1").Select 'select upper left cell to start
Do Until ActiveCell.Value = "" 'stop when you run out of rows
ActiveChart.ChartType = xlBubble
With ActiveChart.SeriesCollection.NewSeries
'use these values for the next series
.Name = ActiveCell.Offset(1, 0).Select
.BubbleSizes = ActiveCell.Offset(0, 1).Select
.Values = ActiveCell.Offset(0, 1).Select
.XValues = ActiveCell.Offset(0, 9).Select
End With
'go to next row, and repeat
ActiveCell = ActiveCell.Offset(0, -1)
End Sub

View 7 Replies View Related

Adding New Series To Bubble Chart

Jul 12, 2007

I have a worksheet where I am collecting data and a seperat Chart. The first part of this code which adds a new row in my table works perfectly fine, but the latter part (based on a macro recording) does not. I need to specify the relevant row for the new series in the bubble chart. The colums are of course the same.

Private Sub CommandButton1_Click()

Dim LastRow, LastRef As Long


ThisWorkbook.Worksheets("Projektradar - input").Select
LastRef = ActiveCell.Value
Range("Q3").Value = LastRef + 1

View 4 Replies View Related

Bubble Chart: Add To Label Hover Display

Dec 19, 2006

When you hold your mouse over a bubble on a bubble chart, it typically displays the x value, y value and size value. Is there any way to add other captions to display? For example, if my chart is based on sales data and I'm plotting a point based on ease of probability (x axis) and dollar value (y value)....I'd also like to see the initials of the salesperson assigned to that prospect when I hover over the bubble?

View 2 Replies View Related

Change Bubble Chart Color Based On Cell Value

Aug 30, 2008

Let Sheet 1 have data in 3 columns to support a standard bubble chart

Col A: X Values
Col B: Y Values
Col C: Bubble Size

Id like to be able to color the bubbles according to the Y values, whether the Y values fall within the following ranges

Green for Y <=2
Orange for 2<Y<=5
Red for Y > 5

Im assuming some straightforward VB code is in the works but Im not quite there yet

View 4 Replies View Related

Dynamic Bubble Chart - Data Point Naming

Feb 16, 2007

I am looking to create a dynamic bubble chart. To do this I am using offset to create the series formula which without listing all the data names turns out to be something like this (where 297 will adjust to the number of rows with data): =SERIES(Project!$A$5:$A$297, Project!$J$5:$J$297,Project!$P$5:$P$297,2,Project!$Q$5:$Q$297)

My problem is that by creating the chart in this fassion I am unable to get a unique name for each data point. For example, if row A looks like this,

A5: 1
A6: 2
A7: 3

each data point will be named 1 2 3. How do I either create a new series for each row dynamically or get the specific name from column A to associate with the correct data point?

View 2 Replies View Related

Second Category Axis & Value Axis To Current Chart

Sep 27, 2006

how to incorporate another category and value axis to my existing chart.

1. I need to add a rank axis derived from my data column (C6-C15). This column should rank from highest to lowest and create a tie if any number is repeated. I have arranged the rank column in cells B38:L38 as they would appear. I would like this to appear on the top or bottom of the bar graphs.

2. Second I need to add the frequency of hits which = 3 derived from cell J1 This should appear on the chart were it is currently titled. This should create another bar graph colored green and labeled 3.

3. Lastly the numbers axis can remain the same as it appears on the chart or if it would be better to align as shown in cells B43:L43.

View 5 Replies View Related

Horizontal And Vertical Chart Axis As Value Axis

Jan 26, 2008

What is the best way to have both horizontal and vertical axis as value axis?

View 2 Replies View Related

Bubble Chart Function - Click To Open Percentage By Subject Line

May 23, 2014

I am new to excel and to the bubble chart function and need creating one for a presentation.

I was asked to do a bubble chart to show the open and click to open percentage by subject line

Subject line: Win 20% open, 23 % Click to Open, Getaway 20$ Open, 21% CTO etc., to include an industry average for open and CTO in the chart.

X axis: click to open and Y axis: open

I tried the following in the picture and it doesn't look right.


View 1 Replies View Related

Bubble Sizes In Bubble-Charts Macro

Nov 9, 2006

Im trying to create a simple 3D Bubble Chart macro, and as most people who've never dealt with this before (i think), i've hit a wall with BubbleSizes. I've attached the file im trying to get to work. The VBA is quite simple but an error occurs at BubbleSizes. I've read that BubbleSizes takes a different reference style than xValues and such, but haven't had any luck trying to change that. For those who just want to see the code without getting the file:

Sub EmbeddedChartFromScratch()
Dim myChtObj As ChartObject
Dim rngChtData As Range
Dim rngChtXVal As Range
Dim iColumn As Long
' make sure a range is selected
If TypeName(Selection) <> "Range" Then Exit Sub
' define chart data
Set rngChtData = Selection
' add the chart
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=250, Width:=375, Top:=75, Height:=225)
With myChtObj.Chart................................

View 5 Replies View Related

Show Missing Values In Pivot Chart (line Chart) Axis?

Apr 18, 2013

How can I add missing values to the axis in a pivot chart line chart? I have a numerical X-axis with values such as 0,1,5,8,14, etc. I have another set of Y values that correspond to the X values. If this was an XY-scatter plot I'd be able to plot X vs. Y and connect the dots for "gaps" in the X values. Since it's a pivot chart I cannot use an XY-scatter plot, I can only use a line chart. The line chart doesn't give me the ability to add the "missing" values, so it gives the impression that my data is more closely packed than it is. The data source is external to my spreadsheet, so I cannot add the values before creating the pivot table/chart.

View 1 Replies View Related

Basic Chart: Set A Chart Up With The Time Been On The X - Axis

Aug 1, 2007

I got a set of data which is only 2 colums collecting data problems. The output i get is a time stamp and a fault. Im looking to set a chart up with the time been on the X - axis and number of problems per hour on the Y - axis.

View 7 Replies View Related

Chart With Two Axis

May 17, 2007

I have some simple data and i want to create a horisizontal bar chart with 2 axis (X axis) the top one showing number of stocks number up to 270 and the bottom with Market Cap up to 1,200. (See attached). I set up the chart and it looks fine but it has only one axis so in order to create a second axis i right click on the blue series (no of Stocks) and go through the motions of setting up a secondary axis. However when i do that it ends up looking like the chart to the right with the Bars Cojoined (which is not what i want) I cannot seem to achieve 2 axis with separated bars

View 3 Replies View Related

How To Autoscale Chart Y Axis Using VBA

Apr 7, 2014

a_Y-Axis auto

How to autoscale chart using VBA, so i can maximize the viewable area without using the default format axis on chart each time i paste new data to the table,

View 5 Replies View Related

Making Chart With X / Y And Z Axis?

Aug 13, 2002

I would like to make a graph with an x, y and z axis. All of the choices that excel gives me are only x,y graphs. How can I plot a 3-axis graph?

View 9 Replies View Related

VBA Chart - X Axis To Appear As Dates?

Jun 5, 2012

I have a simple piece of code that creates a chart using the the values in column A as the x-axis and the values in column E as the y-axis. The values in Column A are all dates in this format: mm.dd.yyyy. However, when I collect the x-values they always appear as numbers (1,2,3). Even if I format the axis to Dates, they appear as 1.00.1900, 1.02.1901, etc.

Here is the code (also, I would like a VBA solution but if this is with excel's chart tools, so be it).


Sub Macro2()
ActiveChart.ChartType = xlXYScatter
ActiveChart.SeriesCollection(1).Name = "=""Rate of Productivity"""
ActiveChart.SeriesCollection(1).XValues = "='Sheet1'!$A$2:$A$4"
ActiveChart.SeriesCollection(1).Values = "='Sheet1'!$E$2:$E$4"
End Sub

View 4 Replies View Related

0 Value On Both X And Y Axis In Line Chart How

Jan 4, 2008

i want 0 values to start at x and y axis.
basically i want time in secs on x axis starting a 0 upto 4.4 secs

View 9 Replies View Related

Floating Bar Chart With Y Axis

Apr 16, 2008

I have a dataset that looks like this ....

View 9 Replies View Related

Copyrights 2005-15, All rights reserved