What does Excel do when you go over the limit for total data point

The maximum number of total data points in a plot is 256,000.  However, I 
have setup plots with > 1.5 million data points.  Excel does not complain 
about this, but I'm guessing it is still only showing 256,000 points.  The 
thing is, I can't figure out which 256,000 it is showing.  I don't think it's 
just the first 256,000 because the data is sorted and it would look abruptly 
cutoff after the 256,000th point.

I'm using a scatterplot if that helps.  The plot looks correct, with 
gradually decreasing density towards the outer edges of the plot.  But if 
it's only showing 17% of the data points, is Excel intelligently filtering 
the data evenly?
0
Carl (99)
4/8/2009 1:31:03 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
815 Views

Similar Articles

[PageSpeed] 17

Nevermind.  I thought I had the data sorted, but I didn't.  I can clearly see 
it is cutting it off now, so I will have to filter the data in my application 
first.

Please delete/lock this thread.
0
Carl (99)
4/8/2009 1:41:01 PM
Usually I ask what's the use of showing more points in a chart than there 
are pixels. But his time I thought I'd check it out.

I filled A1:L32001 (12 columns) with data. I tried to make a series with 
A1:A32001, and Excel warned me that the limit was 32000 points per series.

I made a chart using A1:H32000 (8*32000=256000), which supposedly reaches 
the maximum number of points in the chart. Fine, glad I'm using Excel 2003 
and not 2007, because in the latter it would take a long time to redraw. 
Then I extended the data range to I32000. No warning, and the new series was 
added as if there was nothing wrong. I extended the range to column L, so I 
have 12 series in the chart, each with 32000 points, which exceeds the limit 
by 50%.

In VBA I ran command this from the Immediate window, with the following 
results:

For Each s in ActiveChart.SeriesCollection : p = p + s.Points.Count : ? 
s.Name, s.Points.Count, p : Next
Series1        32000         32000
Series2        32000         64000
Series3        32000         96000
Series4        32000         128000
Series5        32000         160000
Series6        32000         192000
Series7        32000         224000
Series8        32000         256000
Series9        32000         288000
Series10       32000         320000
Series11       32000         352000
Series12       32000         384000

So Excel thinks there are 384k points and is continuing happily along. I 
don't know of any feasible way to tell which points are showing and which 
(if any) have been filtered out.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/>
Advanced Excel Conference - Training in Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______


"Carl" <Carl@discussions.microsoft.com> wrote in message 
news:2B3AF515-913F-43F4-82F2-E21E2C560A04@microsoft.com...
> The maximum number of total data points in a plot is 256,000.  However, I
> have setup plots with > 1.5 million data points.  Excel does not complain
> about this, but I'm guessing it is still only showing 256,000 points.  The
> thing is, I can't figure out which 256,000 it is showing.  I don't think 
> it's
> just the first 256,000 because the data is sorted and it would look 
> abruptly
> cutoff after the 256,000th point.
>
> I'm using a scatterplot if that helps.  The plot looks correct, with
> gradually decreasing density towards the outer edges of the plot.  But if
> it's only showing 17% of the data points, is Excel intelligently filtering
> the data evenly? 


0
jonxlmvpNO (4558)
4/8/2009 1:47:53 PM
Thanks for the help, Jon.  I would totally agree that it's pointless to plot 
more points than can be viewed on the screen, but you don't always know how 
many pixels the user's monitor displays.  Plus, they may want to zoom in on a 
specific area.

Also, it would be much easier to let Excel figure out how to plot it all 
rather than writing my own code to filter the data before giving it to Excel. 
 Or at least it would be if I could be sure Excel was displaying it properly. 
 

"Jon Peltier" wrote:

> Usually I ask what's the use of showing more points in a chart than there 
> are pixels. But his time I thought I'd check it out.
> 
> I filled A1:L32001 (12 columns) with data. I tried to make a series with 
> A1:A32001, and Excel warned me that the limit was 32000 points per series.
> 
> I made a chart using A1:H32000 (8*32000=256000), which supposedly reaches 
> the maximum number of points in the chart. Fine, glad I'm using Excel 2003 
> and not 2007, because in the latter it would take a long time to redraw. 
> Then I extended the data range to I32000. No warning, and the new series was 
> added as if there was nothing wrong. I extended the range to column L, so I 
> have 12 series in the chart, each with 32000 points, which exceeds the limit 
> by 50%.
> 
> In VBA I ran command this from the Immediate window, with the following 
> results:
> 
> For Each s in ActiveChart.SeriesCollection : p = p + s.Points.Count : ? 
> s.Name, s.Points.Count, p : Next
> Series1        32000         32000
> Series2        32000         64000
> Series3        32000         96000
> Series4        32000         128000
> Series5        32000         160000
> Series6        32000         192000
> Series7        32000         224000
> Series8        32000         256000
> Series9        32000         288000
> Series10       32000         320000
> Series11       32000         352000
> Series12       32000         384000
> 
> So Excel thinks there are 384k points and is continuing happily along. I 
> don't know of any feasible way to tell which points are showing and which 
> (if any) have been filtered out.
> 
> - Jon
> -------
> Jon Peltier, Peltier Technical Services, Inc.
> http://PeltierTech.com/WordPress/>
> Advanced Excel Conference - Training in Charting and Programming
> http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
> _______
> 
> 
> "Carl" <Carl@discussions.microsoft.com> wrote in message 
> news:2B3AF515-913F-43F4-82F2-E21E2C560A04@microsoft.com...
> > The maximum number of total data points in a plot is 256,000.  However, I
> > have setup plots with > 1.5 million data points.  Excel does not complain
> > about this, but I'm guessing it is still only showing 256,000 points.  The
> > thing is, I can't figure out which 256,000 it is showing.  I don't think 
> > it's
> > just the first 256,000 because the data is sorted and it would look 
> > abruptly
> > cutoff after the 256,000th point.
> >
> > I'm using a scatterplot if that helps.  The plot looks correct, with
> > gradually decreasing density towards the outer edges of the plot.  But if
> > it's only showing 17% of the data points, is Excel intelligently filtering
> > the data evenly? 
> 
> 
> 
0
Carl (99)
4/8/2009 2:16:07 PM
Reply:

Similar Artilces:

limit of named ranges
Hello do you know ? How many cells can man call under the same name in a worksheet? Can we expand the limit with vba ? How many "named range" (plage de cellules fusionn�es auxquelles j'ai donn� un nom) can we put in a worksheet? thanks http://www.scalpa.info Homework? -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Pascal" <scalpanospm@wanadoo.rf> wrote in message news:45f6fa38$0$27390$ba4acef3@news.orange.fr... > > Hello > do you know ? > How many cells can man call under the same name in a worksheet? > Can we expand the limi...

Excel Stock List
Hi all Im trying to make a stock list using excel that shows Product, Product Code, Amount in Stock and Minimum Stock to Keep. What i want to do is when someone takes some stock and reception remove it from the Amount in Stock list, as soon as the Amount in the Stock List matches the amount in the Minimum Stock to Keep column a pop up appears and tells us we need to re-order more stock of the item that is running low. Anyone know how i would go about doing this? many thanks in advance Matt Send me a small file showing the layout of your data and I'll help you develop this thin...

V lookup in Excel 2003
Hello, I have recently installed fresh copy of Office 2003, where I am unable to find Vlookup function, (I couldnt see it in pop up also while I click on fx sign too....) Please help me on this issue as soon as possible, as heaps of work is pending on my desk due to that. Mail me at baroda@outsourcingindiainc.com Thanks What happens if you just try using the function by typing it in? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------...

Excel 2000 #24
When I left click in a cell and then move my mouse, it highlights all cells and I can't click on any menu items or anything. The only way to close Excel is to go to task manager and end task. Help please! I've seen this type of thing happen with a mouse with a scroll wheel. Sometimes the scroll wheel gets 'stuck'. Press down on the scroll wheel a couple of times to see if this clears up the problem. Gary >-----Original Message----- >When I left click in a cell and then move my mouse, it >highlights all cells and I can't click on any menu items >o...

Importing external data in plain text row format
I have data in the following format: -------------------Begin Sample Data---------------------- Fernando E. Amuchastegui Office of the County Attorney 115 S. Andrews Avenue Suite 423 Fort Lauderdale 33301 954-357-7600 Voice 954-357-7641 Fax famuchastegui@broward.org Ronald J. Anania Law Offices of Ronald J. Anania 10910 S W 10th Court Davie 33324 954-476-9616 Voice 954-476-9693 Fax law1492aol.com Eric J. Anders Haley Sinagra Paul & Toland, P.A. 100 SE 3 Avenue Suite 1900 Fort Lauderdale 33394 954-467-1300 Voice 954-467-1372 Fax eanders@hsptlaw.com Hal B. Anderson Billing Cochran H...

Olap data views
Hello, I've deployed a farm solution with SQL Server 2005, Project server 2007 and windows Server 2003 SP2. I configured Project Server 2007 default cube sucsessfuly and I can create views and see them in PWA Data Analysis. I created a new project Data Analysis Services in Visual Studio. I deployed it remotely and I can see the New cube deployed if I connect to Analysis Services SQL Services Management Studio. Problem start in PWA, because i can't see the newly deployed cube. I only see the default cube created in pwa interface if i go to "server settings...

In Excel
I want to effect a mass change to the sign of numeric values in a range of cells (rows and columns). put -1 in an empty cell, copy it, select the range of cells to be modified, select Paste Special and pick Multiply. MaryKaye wrote: > I want to effect a mass change to the sign of numeric values in a range of > cells (rows and columns). Put -1 in an un-used cell and copy it. Then select the cells you want to update and: Edit > PasteSpecial > multiply -- Gary''s Student - gsnu201001 "MaryKaye" wrote: > I want to effect a mass ch...

smartlist export to excel with large record sets
I read with interest a posting on 2/19/07 and the responses. The issue raised was how long it took to export a large record set (20,000 records) from Smartlist to Excel. I wish that speed was my only problem. In our case, when attempting to export as few as 2000 smartlist records, the computer freezes, and both excel and GP have to be restarted. We are using GP version 9 and Excel 2003. Similar thing happened with GP 8, and happens on other workstations, not just mine. Woudl really appreciate some pointers. thanks. Your problem is probably memory. First RAM should be 2 gig or b...

Multiple Instances of Excel on 64-bit Win7
Cross Posted: microsoft.public.windows.64bit.general microsoft.public.excel (NOTE: This is not a question about getting each Excel file to open in a new instance. Please read carefully) Hello all, On my old system (WinXP Pro, Excel 2003, and Excel 2007): - If no instances of XL are open and I open an XL file from Win Explorer, XL would open (don't recall if it is same version every time or if it would switch), and then the file would open. - If an instance of XL is open and I open an XL file from Win Explorer, the file would be opened in the existing instance of XL. - If a...

Excel: How do I set up a cell to click it to link a database?
I keep track of pricing info in a spreadsheet. I would like to "click on a cell" to be directed to somewhere that I can keep a (purchsase) history attached to that cell. Any ideas how I might be able to do that? in the following, the counter - which determines the row the historical data is entered - is maintained in cell (1,30). The data you wish to periodically record is in cell (10,1). when you run this macro, the data is recorded in the next cell of row 3. :-) Sub recordhistory() etcount = Cells(1, 30).Value etcount = etcount + 1 Cells(1, 30).Value = etc...

Outlook sharing data
A friend has a Novell 4.11 office network with several PCs running Windows 98 SE. I guess he means they have a Novell server and other PCs running Windows 98 SE. One PC has a broadband modem and runs MS Outlook for email. What would it take to give Outlook running on one or more of the other PCs access to the incoming emails and the ability to send emails. I think the idea is that there would be only one email address but several people could look through the incoming emails. -- Brian Gregory. (In the UK) ng@bgdsv.co.uk To email me remove the letter vee. Dear Brian, What Outloo...

data migration framework errors
We are using the data migration framework to migrate into a fresh CRM 1.0 installation. We are at the step where we run the CDFMigration Tool. It runs and completes with no errors. It only migrates contacts, no accounts, incidents, etc. Steps 1. A clean restore of the cdf_mscrm, and CRM databases(which were backed up right after we finished cleaning the data, before we started to migrate) 2. The cdf_"entity"_info table for account, activity, annotation, contact, incident were empty: followed TechKBArticle #31094 from MSBTechKnowledge which said to run: Declare @Id As int Declar...

Exported File truncating cell data after 255 characters
I have exported a file in Excel format from MS Access. When I open the file in Excel, the cells containing greater than 255 characters have been truncated. Any way around this? Excel will not display any characters if you put more than 255 in a cell. Instead you'll get only # signs. So..exporting more than 255 (or maybe even 254) in a field is not likely to help you. How many characters in the fields you're trying to export? If not too many more than the 255 limit - you could use a query to break it into two columns - first one getting the LEFT([textfield],200) and the next...

Excel and Notepad: how avoid additional inverted commas after copy and paste?
Hi, If I write a multi-line text in a cell (then go down with alt+enter) and after copy the cell pasting on Notepad, it display before inverted commas and after textual content. Instead, if I select directly the content from the formula bar, it isn't happen. Do exist a way for copy and paste directly from the cell without select from the formula bar? Maybe with a macro? Thank Marco __________________ http://www.idee-regalo.biz/catalogo-stereogrammi-3.html http://www.ghisirds.it/ http://scuo.la/ http://www.righettofabrizio.com/pantografo-taglio-plasma.html I used the PutOnClipboard rout...

Format date in excel 2000 like '31st January 2005' #6
Trying to set up field in Excel Data Source file so that the current date can be input in letters typed in this format '31st January 2005' Is it possible or am I stuck with dd/mmm/yy format? Sorry for the repeated posts but I received a message that the system was unable to send post. Can any unanswered ones be deleted please. "Malcolm Agingwell" wrote: > Trying to set up field in Excel Data Source file so that the current date can > be input in letters typed in this format '31st January 2005' > > Is it possible or am I stuck with dd/mmm/yy format?...

Copies of sent mail go to my Sent Items folder instead of other mailbox Sent folder.
Experts, I have two Exchange 2000 mailboxes, of which I use the Outlook client to access. In this other mailbox, from which I occasionally send email, I noticed copies of sent mail go to my own mailbox Sent Items folder instead of the other mailboxes Sent Items folder. Is this a feature that can be changed? -- Regards, Spin ...

how can i make a line chart from matrix(both row and col) of data
Select the data, and create the chart. By default, Excel is likely to use the first column as X values (or category labels) and the first row as series names, if the data is plotted by column. If Excel has trouble with this, clear the top left cell of the range (the intersection of series names and category labels/X values) and try again. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Gourav" <Gourav@discussions.microsoft.com> wrote in message news:CEA158CD-95F4-4903-BFBB...

Method to Calculate APR in Excel?
Any suggestions or solutions would be appreciated. Thanks & regards, PJF Your question is very broad - more detail would have helped, with an example Here is one site that might help http://banking.about.com/od/loans/a/calculateapr.htm best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "PJF" <pjf10@earthlink.net> wrote in message news:uz7dOTJDKHA.5780@TK2MSFTNGP03.phx.gbl... > Any suggestions or solutions would be appreciated. > > Thanks & regards, > > PJF > > "PJF" <pj...

Count Images in an Excel Sheet
I have saved excel sheets with screen prints in it. I need to know if I can get the total number of screen shots in a particular excel sheet. Try: Sub HowMany() MsgBox ActiveSheet.Shapes.Count End Sub -- Gary''s Student - gsnu201001 "Pankaj" wrote: > I have saved excel sheets with screen prints in it. I need to know if I can > get the total number of screen shots in a particular excel sheet. > PicCount = Activesheet.Pictures.Count Regards, Peter T "Pankaj" <Pankaj@discussions.microsoft.com> wrote in message ...

Excel Workspace
Is there a way to cause Excel to display multiple rows of worksheet tabs at the bottom of the excel workspace rather than just one? When a workbook has many worksheets it would be easier to switch between different worksheets if they were visable. I am aware that right clicking on the worksheet scroll bar displays a list of the worksheets and that I can drag the scroll bar on the lower right side of the workspace to the right to allow more woorksheet tabs to be displayed but these solutions don't really meet my needs. Thanks to anyone that can help. Marc Marc, Try Right Click...

extra quotation marks when excel ws is saved as txt file
I am trying to automate a process that creates a txt file needed for an application. However, this text files needs a set of double quotations, and also has some cells that will have commas. When I save the excel worksheet that contains this information, the text file ends up with this """abcd""" instead of the original "abcd". Any cell with commas is found in the text file with double quotes surrounding it as well. Is there anyway to save the excel worksheet as a text file and receive exactly what is entered into the cells? Note: I am a...

Function Macros inspired by the recent "date in excel" thread.
I vaguely remember the use of function macros from an early version of Excel. The date in excel thread reminded me of that because that might offer a simple macro with programming not much different from just writing a spreadsheet. IIRC this mini-spreadsheet could use data from the calling spreadsheet to control its calculation. IIRC, the function macro function is called with arguments just like an ordinary Excel function is called. The return value or values of the function is just what you can create using spreadsheet statements. For example, suppose you have a starting point A and ...

total in a frontsheet
Im having 20 subsheets that are build the same way. Is there a function to sum cells for all 20 subsheets into the frontsheet ? "Bo" <bo@kjrevision.dk> wrote in message news:09c301c38d70$79710750$a401280a@phx.gbl... > Im having 20 subsheets that are build the same way. > > Is there a function to sum cells for all 20 subsheets > into the frontsheet ? Something along these lines will work: =SUM(Sheet1:Sheet20!A1) Bo, Sounds like it is better to use Data|Consolidate. This is in help file "about consolidating data." Consolidating by position Consoli...

how to find last cell in column with data
I have a very simple checkbook register. I would like to be able to display the ending balance in a specific cell at the top of the sheet. How can I do this? Thanks Let's say your balance is in column A from A2 to somewhere below. Put in A1 =LOOKUP(2,1/(A2:A65356>0),A2:A65356) (suggested by Sandy Mann) -- Gary's Student "keyser_Soze@usa.com" wrote: > I have a very simple checkbook register. I would like to be able to > display > the ending balance in a specific cell at the top of the sheet. How can > I do this? > > Thanks > > Gary&#...

Trust access to the VBA project: Excel bug?, workaround required.
I'm fairly sure this is a VBA bug (comments welcome) and need a workaround. In Excel 2007 'm using the the function below in an add-in to return the access oft the VBA project. It appears to work correctly. Private Function IsVBProjectAvailable() As Boolean Dim lngAccessTest As Long On Error Resume Next lngAccessTest = ThisWorkbook.VBProject.VBComponents.Count If lngAccessTest > 0 Then IsVBProjectAvailable = True Else IsVBProjectAvailable = False End If End Function ....UNTIL you open a workbook containing macros. At ...