Creating Charts With Formula Not Data
Jun 28, 2006Can i create a chart in excel just using a formula - but not data?
View 4 Replies
ADVERTISEMENT
Creating Bubble Charts On Three Different Columns Of Data
May 23, 2012I'm trying to create a bubble chart created on three different columns of data. I'd like the following:
-The X-axis should just show the first columns of dates I have stored.
-The Y-axis should be the number of lots of a product that I bought of a certain product.
-I'd like the actual bubbles to not only represent the number of lots purchased on the given date, but I'd like the size to be represented by the third column, which is the average price of each lot.
Is there a way to do this? I've been trying to maneuver it around all morning, and can't seem to get it how I want it. I'm not sure if I'm ordering the columns incorrectly or what.
Creating Charts With VBA ..
May 26, 2009I've got a table which has additives in Column A and products on Row 1. There are 13 additives and 10 products. Each combination of product and additive has a price. So I have 130 different prices in my table. The prices change monthly and the new prices are added to the sheet so that last months prices will move to left so that the newest price data table is A1:J13 and previous month is K1:T13 and so on.
I need to make an individual chart to every combination which contains the history of the price changes. I have two cells (A20 and B20) where the user can pick a product and an additive (created a menu by using Data|Validation...). These selections are combined in a cell C20 (=A20&B20). Then I have a macro that is linked to the cell C20 and is basically Case Is = 1st combination --> Call Macro1, Case Is = 2nd combination --> Call Macro2 etc.
That macro will work out just fine, BUT is there any way I could make all the 130 charts easier? Now I've recorded a macro that creates the first chart and then I've just copy-pasted it, changed the attributes (cell numbers, names and so on) that define the data to the next charts. This will take ages...
Every combination will have at least 13 changes (12 monthly price cells and the name of the cart) so in total this will mean about 1500 manual changes. In my case this will mean a high danger of typos.
I know, it would be SO much easier if I could give you the Excel file to look at...hopefully you understood what I mean.
I'll only follow the changes within a year so prices older than a year will not be shown in the charts.
Creating Pie Charts With VBA
Jul 14, 2009Now I need to create multiple pie charts based on data in tables on many sheets.
I need to create the same set of charts, for each sheet separately.
I recorded a macro for one sheet, which created following
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlPie
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='GALLERY ON 4TH'!$B$2"
ActiveChart.SeriesCollection(1).Values = "='GALLERY ON 4TH'!$D$6:$D$8"
ActiveChart.SeriesCollection(1).XValues = "='GALLERY ON 4TH'!$A$6:$A$8"
Range("A11").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlPie
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='GALLERY ON 4TH'!$A$6"
ActiveChart.SeriesCollection(1).Values = "='GALLERY ON 4TH'!$B$6:$C$6"
ActiveChart.SeriesCollection(1).XValues = "='GALLERY ON 4TH'!$B$5:$C$5"
ActiveWindow.SmallScroll Down:=3
Range("A11").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlPie
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='GALLERY ON 4TH'!$A$7"
ActiveChart.SeriesCollection(1).Values = "='GALLERY ON 4TH'!$B$7:$C$7"
ActiveChart.SeriesCollection(1).XValues = "='GALLERY ON 4TH'!$B$5:$C$5"
However I cannot automate. I tried different methods for creating the charts, as found on the site [prefer .chartobjects.add(x,y,z,a) since I can immediately position the chart] , but get stuck at seriescollection everytime.
It looks like when the chart is created by XL (with suggested data in it) I cannot address the series data.
I got it right to generate a chartobject that is empty, then added the series data to it, but when opening the new chartobject, it is again populated with suggested data.
How do I generate an empty chartobject everytime, so I can insert seriescollection info with code?
Any other way to generate charts?
Creating Multiple Charts Via VBA
Apr 11, 2007am trying to create multiple pie charts now but I'm having problems my errors are highlighted in red. What I'm trying to achieve is to create a pie chart for the first table then another for the next table and so on but its not working out.
View 14 Replies View RelatedLooping Through Sheets And Creating Charts?
Jun 18, 2014I have following code which is not behaving the way I want.
[Code] ....
The problem is the last statement, a chart which gets created in an iteration does not "move" to a new sheet but rather same sheet. So end result is a sheet with chart of the last iteration.
How to get this code to create new charts in new sheet and not the same sheet.
Creating Many Excel Charts With A Macro?
Oct 21, 2011I've got quite of few excel charts to make and have been looking for a way to automate the process. The chart types include line, pie, and bar. Basically, I have data for each state in the US that I am comparing with national level data. Thus each chart will have national level data and the data for each state. What I need is a macro that I can easily change the parameters for because different worksheets have slightly different layouts. I started by using the macro recorder to make a chart. Then I attempted to edit it so that it would run loops to make additional charts. Below is some sample data for the first worksheet I have and my attempted macro.
Sample Data:
Code:
State Characteristic Value07 Value08 Value09
Alaska A 11 23 45
Alaska B 13 22 98
Alaska C 99 91 21
Alabama A 23 14 11
Alabama B 44 62 76
Alabama C 75 47 21[code]....
This code doesn't run at all.
Creating Dynamic Charts With Multiple Filters?
Mar 2, 2013building dynamic charts and filters, so What I'm hoping to accomplish to build a bar chart that looks like the following:
________________________________________________
Filter: State | Filter: Region | Filter: Segment |
Revenues |||||||||||||||| + (% of Total Rev)
COGS ||||||||||| + (% of Total COGS)
Margin |||| + (% of Total Margin)
-------------------------------------------------------
My data is built in the following format on a different tab than the proposed chart:
State: | Region: | Segment: | Account: | Amount: |
MN | Midwest | Major | Revenues | $$$
MN | Midwest | Major | COGS | $$$
MN | Midwest | Major | Margin | $$$
MN | Midwest | Major | % of Total Revenues | %%%
MN | Midwest | Major | % of Total COGS | %%%
MN | Midwest | Major | % of Total Margin | %%%
[Code] ........
It seems like it'll be easy to use a pivotchart, but at the same time, there's a lot of formatting to do, which makes it tricky, especially if I have many states to deal with and the multiple combinations.
Creating Multiple Charts From One Table Using Macros?
Jun 11, 2013I have a dynamic set of data, loaded from a server. The columns are FIXED. I will attach a sample of how the data looks.
site_code
report_goup
asm_week
[Code].....
I don't know how to attatch a file here, but the first row is the title row, under it are the values. The columns here are fixed. What i NEED to do, is somehow create something that will make a seperate graph for every different PLATFORM , showing the FPY chaging over time. Time here is the weeks which will always be a rolling 12 weeks. I'm thinking that i need to use MACROS to do this, but i'm not sure where to start.
Creating Sale Closure Charts Using Date Ranges?
Jun 29, 2014I want to find the number of similar values contained in a column based on a date range I specify.
I have a data set that's formatted like the following (you can see the full data set at [URL] but I've hidden the irrelevant columns and highlight the relevant ones)
[Code] .......
I want to use this data to create two types of graphs. One showing the ratio including all values in the Status column. There are 4 total (Open, Lost, Won, Won-Job Cancelled). I want another which excludes the "open" status from the ratio figure. I've create some images demonstrating what I want to accomplish at [URL] .....
Additionally, I want to be able to specificy date ranges for these charts. Specifically 0-30 days from todays date, 30-90 days and older than 90 days.
I want to accomplish this using Google Sheets (the latest version) and know how to use the graph total. It's just creating the data the way I need it which is proving to be the most challenging.
Excel 2010 :: Creating Charts With Average Line?
Apr 5, 2012How I could create a chart (standard column) with an average line in Excel 2010?
View 4 Replies View RelatedCreating Formula Using Data From Tabs
Nov 17, 2008I am trying to create a formula by comparing data in columns on a separate tab.
Basically, I want to take the value of one cell in the primary tab, find the matching value in a column on another tab, then once it is matched, pull in the data from another cell in the second tab on the same row of the matched data.
Formula To Handle 5 Fields Of Data To Avoid Creating Extra Columns?
Nov 27, 2009I am inputting 5 variables in columns A through E:
Family (Y/N)
Adult (1/0)
Child (1/0)
Date of Birth
Gender
I am attempting to generate:
Age Males in families sorted into age brackets
Age Females in families sorted into age brackets
Age Males single sorted into age brackets
Age Females single sorted into age brackets
I did this rather simplistically generating 4 columns (F through I) and then using COUNTIF (although I know there is a better way than the lame way I used COUNTIF but that is not the point right now).
However, my question is, can I generate the data described above (located in D13:I22 on my spreadsheet) without going through the messy, cumbersome step of creating extra columns?
Get Dynamic Charts To Ignore Formula?
Oct 5, 2013I'm running a chiropractic office. In order to see where I've been and where I'm going I keep track of things like new patient visits, patient report visits, cash in, insurance in, etc. I keep track of this on a daily basis on one worksheet. The weekly totals are paste-linked into another sheet. It is this sheet that I would like to use to create a dynamic chart however, in using the OFFSET function to create the chart, the formula counts the 'blank' cells b/c of the paste-link from the previous worksheet.
View 1 Replies View RelatedConverting Static Charts To Dynamic Charts
Jun 27, 2013My DB is in table format . I use this table as source data for 2 barcharts and 1 pie chart.Following are my table headers
Costs|exp heads|Month1|Month2|...|Month n|Spark lines|Average
When i add a month coloumn,Sparklines and Average coloumn should get updated automaticaly.Now this is not happening even if the data is in table format.I also want the graphs to be automaticaly updated.
Copy Charts Trend Formula To A Cell In VBA?
Jun 6, 2012I am having trouble copying a charts trend formula into a cell.
the best I can do is select it as follows:
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
I know you can work it out using linest, but this chart uses a polynomial to the degree of 3 to 5.
Charts Stop Plotting Data Error Bars After Certain Number Of Data Point Selected
Dec 23, 2013In the attached document is a timeline made from a scatter chart. Error bars using custom values are used to show the length of each task, however I can't get the chart to include error bars for the last 2 data points (tasks).
View 1 Replies View RelatedCreate Charts From Same Data But Each Based Ob Data Criteria
Aug 31, 2006Hi, have a question regarding dynamic charts, specifically I am trying to create several dymanic charts based on data in a single column (So i can only say my first set of data will be starting a a set location in the chart, the rest has to shift down based chart data above it). This data should organized in charts depending on the data in several other columns. For instance, need to pull out values from column based some ID and some PartNumber. A sample excel or VBA would be much appreciated. I know VBA but do not know Excel VBA much.
View 9 Replies View RelatedCharts: Use Different Source Data
Feb 6, 2007If there a way to change the souce data so that it changes based on a variable? For example, if a cell's value equals X then use one range and if it is Y then use a different. As far as I can tell you cannot use the "IF" argument in a chart Source field.
One option I came up with is to change the actual data on a worksheet and always have the source data point to that range in the worksheet. The problem is that the source data is not always the same length (Monthly may be 100 columns long while Daily is 1000 columns long - the chart looks starnge).
for example: ...
Pivot Charts Versus Charts
Sep 5, 2012I am trying to put some charts into a report that is pivot table based. I have some code that will work if the pivot tables stay stagnic but the users may change the tables around so that could be an issue of new data. I have the following code where I changed the source to the pivot table name (general name because of numberous report possibilites). I have taken out the other charts because they are just a variation of the chart 1. I am crashing on the line with the * on it.
Sub UWTierChart()
Dim oCell As Range
Dim oChart As Chart
Set ws_data = ActiveSheet
'Chart 1
Sheets(wsPT).Select
Range("B21").Select
[code]....
Customize Data Table In Charts?
Jul 9, 2014excel data table within the charts. I would like to customize the data table placing underneath the chart so I can highlight (say, in red) some of the figures that are greater than certain values for easy reference. Is there anyway I can do with it (since I got more than 800 charts) than that I can only use tools to draw a rectangle and cover that particluar cells up? or is there any ways I can link up the conditional formatting of the data itself with the chart so I can applying certain coloring on the data and reflect on the data table?
View 3 Replies View RelatedFormatting Charts With Different Data Types
Jan 3, 2012I have three items I would like to graph, on one chart. The first is %Complete. This is a number figure. The next is the date the item is due to ship. Obv, this is a Date. The third is today's date. Basically, I would like to display in the same chart so that we can see how far a long a project is, in relation with today's date and how much longer we have until it needs to ship.
This is the current display of the chart:
Is there a way to show the ship date, as the 100% complete mark? In order to make it display half-way decent, I had to format the left y-axis to go above 100%. I first tried to format the right y-axis, but was extremely confused to setting values. I figured out that the end date is somewhere in the 40,000 value mark? WTF is this about? Is 1 equal to 24 hours? I assumed that was the case, but then when I pick the value it should end with, it doesn't seem to react that way. I want the right axis to start with 12/01/2011 and end with 5/01/2012. How can I achieve this?
Dynamic Charts With Horizontal Data?
Oct 3, 2012I am creating dynamic charts with a data sheet that is horitzonal. My x-axis : =OFFSET('chart data'$B$1,0,1,1,COUNT('chart data'$2:$2)-1), y-axis: =OFFSET('chart data'!$A$1,0,1,1,COUNTA('chart data'!$1:$1)-1). My formulas are giving me #VALUE. Not sure why.
(formatted general not as dates)
1980
1981
1982
1983
1984
1985
1986
1987
1988
CONSUMPTION
70
61
67
83
89
75
67
71
71
Breaking The Link Between Charts And Data
Jan 22, 2008I am trying to record a macro so i can click a button and have excel break the link between my charts and the tables that drive them. during the record phase, i click on each chart and use F9. the problem i am facing is that now my macro sets the charts to the data that was there when i recorded the macro, erasing any changes i have made.
View 9 Replies View RelatedLink Colors In Charts To Data
Oct 4, 2006How can i link colors used in charts to companies i have i my database. Now excel provides colors in a certain sequence but this is not what i want. I attached a file in which i for example wants to create pie charts of the company's sales. What do i have to do to give each company its own color?
View 2 Replies View RelatedExcel 2007 :: Alphanumeric Data Charts
Mar 2, 2014I'm currently working on a spreadsheet for my husbands work in a school environment. All was going well until I hit a wall with the alphanumeric data of the current uk stats system. Is there any way of getting my 4c...4b...4a...etc. to chart?
View 1 Replies View RelatedAdjusting Charts To Reflect Data Properly
Feb 2, 2009I've compiled data into a spreadsheet which indicates the number of homes sold over the course of 3 years (from 1/1/05 to now) along with their price per square foot, sale price, year built, bed/bath count, and other pertinent factors regarding each property.
I've extrapolated graphs from these in the past which indicated the trends in each category over time (i.e.: Average quarterly price per square foot over time, and so forth).
The data range I'm working with now, however, is from an area of my region so small, that the number of units sold over the past few years is sporadic. Whereas it's normally 100 to 1000 units that comprise my data, now it's only 15 units.
This has created gaps in my line graphs where the price per square foot will dramatically drop down to zero over the periods in time where no sale was recorded. So the line graph ends up looking like a zig-zaggy range of peaks reflecting the price per square foot of a sale, and valleys representing "$0.00" for periods of time where nothing has sold.
To have a Price per square foot of $0.00 is incorrect since in reality nothing was sold.
I would like to be able to eliminate the portions of the graph that have the "$0.00" so that the line graph can "jump" from one sale to the next when the quarterly data indicates 0 units sold.
I'd like to do this without having to eliminate those areas in my spreadsheets. Is there any "catch-all" tool that might do this or is that just crazy talk?
Transfer Data From Excel (INC Charts) To PowerPoint
May 21, 2012I need to transfer data to powerpoint from my '4 box' reports in excel.
Essentially copying everything in the range A40:U74 inc charts and paste them into a slide as a picture on the click of a macro button.
I have to do alot of reports like this for my project and it takes up alot of time copying and pasting etc.
I have seen different codes for this but they are either for charts only or text... the one i did see for both, didnt work at all.
Automatically Create Set Of Charts From Block Of Data
Jul 12, 2012I have a quite big database as a result of a study in Excel 2010. There is a special block of data for every participant in the study, including 16 data series in 16 lines. I have to make a unique chart for every line.
One block is bulid up in a way, that every row is the input for a chart, with the exception of one "spacer" row, which is empty.
Name
1 2 3 4 ...
A
B
C
D
E
F
For example in the sample above, there should be 6 line-charts (A, B, C, D, E, F) next to the block.
Can a macro be written, so I can automatically create the chart-set by selecting the block's upper left cell and running the macro? Or is there any easier method?
Several Sets Of Data All Which Link To Seperate Charts
Feb 9, 2009I've got several sets of data all which link to seperate charts, the problem i'm having is that i dont want the chart to display the fields with zero values and with the amount of charts and data it is taking ages to re-source the data is there a way of getting the chart to ignore zero fields or of somehow sorting the data in a way only fields with values are shown
View 9 Replies View Related
