Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Can I create a chart using every fifth row of data?
I have a spreadsheet with 13,000 rows of data. I need to creat a chart using
every 5th row in order to cut down the amount of data in my chart. Can I easily do this? |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Can I create a chart using every fifth row of data?
I'm not sure of an easy way to do this, but if you hold the CTRL key and
select evert fifth row and then the chart wizzard, it will only use those rows. However, with 13,000 rows of data that means clicking 2,600 times. "Adair72" wrote: I have a spreadsheet with 13,000 rows of data. I need to creat a chart using every 5th row in order to cut down the amount of data in my chart. Can I easily do this? |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Can I create a chart using every fifth row of data?
I recently had to solve this same problem with a change to some data used for
the charts. In my case, i had to chart This Year vs Last Year data, pulling sub-totals out of a large block of cells My VBA solution involved: creating a string of addresses of the required cells, copying the range, pasting them into a contiguous range on a hidden sheet, adding this range as a Named Range, and assigning this named range to the Values property for the seriescollection on the chart You should be able to just step through and create your series string, stepping by 5 As i cannot find my code, here is some pseudo-code from my notes: Dim wkRange as worksheet Dim wkOrig as worksheet Set wkRange = worksheets("InvisibleRangeSheet") Set wkOrig = worksheets("originalSheet") With Range For i = 1 to cells(rows.count, "A").End(xlup).row Step 5 strSeries = strSeries & ", " & .cells(i,1).address Next i wkOrig.range(strSeries).copy .range(.cells(row,col).address).pastespecial paste:=xlpastevaluesandnumberformats .names.add name:="RangeName" refersTo:=.range(.cells(row,col).address & ":" & .cells(row+wkOrig.range(strSeries).count -1,col).address) ActiveChart.seriescollection(1).values = "='" & .name & "'!" & "RangeName" End With HTH sqlfan13 "joebogey" wrote: I'm not sure of an easy way to do this, but if you hold the CTRL key and select evert fifth row and then the chart wizzard, it will only use those rows. However, with 13,000 rows of data that means clicking 2,600 times. "Adair72" wrote: I have a spreadsheet with 13,000 rows of data. I need to creat a chart using every 5th row in order to cut down the amount of data in my chart. Can I easily do this? |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Can I create a chart using every fifth row of data?
Of course, you'll hit a limit based on the maximum number of characters you
can pass to the chart for its .Values, .XValues, or .Formula. The Excel Chart FAQ has a formulaic technique to display every Nth point: http://pubs.logicalexpressions.com/P...?ID=209#jon024 - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sqlfan13" <nospam.dustinmueller_13.at.hotmail.com wrote in message ... I recently had to solve this same problem with a change to some data used for the charts. In my case, i had to chart This Year vs Last Year data, pulling sub-totals out of a large block of cells My VBA solution involved: creating a string of addresses of the required cells, copying the range, pasting them into a contiguous range on a hidden sheet, adding this range as a Named Range, and assigning this named range to the Values property for the seriescollection on the chart You should be able to just step through and create your series string, stepping by 5 As i cannot find my code, here is some pseudo-code from my notes: Dim wkRange as worksheet Dim wkOrig as worksheet Set wkRange = worksheets("InvisibleRangeSheet") Set wkOrig = worksheets("originalSheet") With Range For i = 1 to cells(rows.count, "A").End(xlup).row Step 5 strSeries = strSeries & ", " & .cells(i,1).address Next i wkOrig.range(strSeries).copy .range(.cells(row,col).address).pastespecial paste:=xlpastevaluesandnumberformats .names.add name:="RangeName" refersTo:=.range(.cells(row,col).address & ":" & .cells(row+wkOrig.range(strSeries).count -1,col).address) ActiveChart.seriescollection(1).values = "='" & .name & "'!" & "RangeName" End With HTH sqlfan13 "joebogey" wrote: I'm not sure of an easy way to do this, but if you hold the CTRL key and select evert fifth row and then the chart wizzard, it will only use those rows. However, with 13,000 rows of data that means clicking 2,600 times. "Adair72" wrote: I have a spreadsheet with 13,000 rows of data. I need to creat a chart using every 5th row in order to cut down the amount of data in my chart. Can I easily do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I create a chart to compare multiple data series? | Charts and Charting in Excel | |||
ow to create comparison chart using text as value data? | Charts and Charting in Excel | |||
How do I create a Chart from character data? | Excel Discussion (Misc queries) | |||
How do I create a Pie Chart from a LIST of Data? | Charts and Charting in Excel | |||
Help with data not getting plotted | Excel Discussion (Misc queries) |