When you’re working with External Data, Excel provides three options that deal with data outside the External Data Range. These options determine how the range is updated when the data changes and can affect how other data in your spreadsheet is handled. The options are on the External Data Range Properties dialog box, the pertinent section is shown below:
To demonstrate how these options work, I’ve set up a table in Access with three records. This the whole table after creating a new query in Excel:
I limit the table to one row by using a parameter and I add some extra data around the table so we can see what happens.
Insert cells for new data, delete unused cells
This option will insert cells (not rows) when the table grows and delete cells (not rows) when it shrinks. When the parameter changes to show all of the data (table grows), the “Extra row data” is unaffected because Excel only inserts cells. Nothing to the left or right of the table will be affected with this option. The “Extra column data” will be pushed down to make room for the inserted cells.
You may notice that the blank row between the external data and the extra data is now gone. When an external data table only has one record, Excel puts an extra row at the bottom. I have no idea why. So the “Extra column data” is still in the next row although it doesn’t look like it.
Insert entire rows for new data, clear unused cells
This option will insert whole rows when the table grows, but only clear the contents of cells that it no longer needs (it doesn’t delete the cells or the rows). That means that both “Extra row data” and “Extra column data” will move down when the table grows.
When the table shrinks, none of the extra data moves. Excel only clears the cells, it doesn’t delete them nor does it delete the rows.
Overwrite existing cells with new data, clear unused cells
With this option, “Extra column data” says bye-bye because it’s in the path of the new data. No, it doesn’t come back when the table shrinks, overwrite means overwrite. Nothing happens to “Extra row data” because nothing happens to the rows, just the cells. When the table shrinks, it acts like the second option.
I think these options are generally ignored (sometimes at great peril), but now that you know they are there and how they work, you can choose the right one for you.
Dear Author,
The aricle looks very good and interesting. I want to know that, if a chart has already there pointing to the data, and external query increases/decreases the no. of rows then how is the chart gets affected.
Thanks in advance
Regards,
Srikanth L.
Srikanth
I think that if I’ve understood your question properly then a dynamic named range should do what you require.
http://www.dicks-blog.com/archives/2004/06/11/dynamic-named-ranges/
To use one in your existing chart you just need to change the source data for your chart. The references need to be changed, e.g from ‘Sheet1’!$A$1:$A$10 to something like ‘Workbook Name.xls’!DynamicNamedRange.
Hope this is what you were looking for.
John
The Lists in Excel 2003 have obsoleted a great many of the dynamic ranges and dynamic charting techniques. If the data is in a list, the chart will update as the list changes in size, and so will a defined name that refers to a column in the list.
‘Sheet1’!$A$1:$A$10 turns to ‘Sheet1’!$A$1:$A$11 when a row is added, without you having to edit a single formula. If a defined name refers to ‘Sheet1’!$A$1:$A$10, hard coded, without OFFSETs, etc., it will point to ‘Sheet1’!$A$1:$A$11 after adding a row, as long as ‘Sheet1’!$A$1:$A$10 is part of a list. A chart based on ‘Sheet1’!$A$1:$A$10 will change to ‘Sheet1’!$A$1:$A$11 when a row is added.
I rarely work in Excel 2003, since most of my clients are still running 2000. I just discovered lists this morning, quite without looking, and I’ve decided one thing about them:
Excel 2003 Lists are cool.
The thing that bothers me about web queries is that I see no option to retain data from past queries. To me it would seem logical that if I want a say 5 minute refresh rate that I might want to have the records retained from every query so that I might build a history of changes. I mean if you don’t have past data then you are limited to what you can do with this unless you are good with VBA. Am I wrong? Is there a way to do what I am talking about? Adding rows or cells when a table grows on a site is nice, but a lot of tables stay the same size, and only update information within the fixed range of cells.
This was very helpful, thank you. I like the way in which you illustrate with screen shots. I was interested in what Jon said about lists, so I tried to convert my external data range to a list, so I could have a dynamic chart, but it said that If I did so, I would lose all external links. Does this mean that LISTS don’t work with external data?
I guess I have to fall back to dynamic ranges, which have always been tough to understand for me, but I’m sure your tutorial will be helpful.
Scott –
I just did a quick test with an external data source (a simple CSV file) in Excel 2003. I used the data range filled by the external data as the source range for a chart. I then added data to the external source (the CSV file) and refreshed the external data range, and the chart automatically updated to include the additional data. So there is no need to convert an external data range to a list to get a dynamic chart based on the external data.
That’s very interesting, John. I’m currently reworking a spreadsheet from someone else to use external data (SQL Server queries), and the charts don’t update when data is added (I had to use Dynamic named ranges as the data sources). Maybe it is because the charts were already there when i added the QueryTable?
BTW, enjoy your website – very helpful.
I only tested in 2003. If the workbook predates that, it might be worth trying to rebuild the workbook. Also, if the chart doesn’t use all records of the external data set, it might not automatically refresh (charts using only part of an Excel list may not update).
Hi Jon,
That might be it b/c I am using 2003. Actually the workbook is from 2007 (which I am salivating to get). I wonder if it had to do with how you selected the data range as the source of the chart? Did you select the whole column(s)?
I probably selected one cell in the range and ran the chart wizard, which is my usual approach. The range already contained the external data, and the chart wizard automatically selected the entire range.
I wonder if different types of external data work differently with the chart.
Is the workbook a 2007 one that you opened with the compatibility pack, or was it saved from 2007 in 2003 format? I suspect in either case, it might not work the same as a native 2003 workbook saved from 2003.
It was saved from 2007 in 2003 format. Yeah, it seems a little funky – for instance, when I change the location of an embedded chart to being on it’s own tab, it looks very wrong (horizontally compressed). Maybe better to open with compatibility pack.
There seem to be issues with compatibility. I was trying to fix an Excel 2007 line chart the other day that had up-down bars (http://peltiertech.com/Excel/ChartsHowTo/StockChartsAndLineChartTricks.html). Points in between the up and down values were hidden behind the bars, very unusual, because the same chart made in Excel 2003 shows the points in front of the bars. In fact, if you make the chart in 2003, then open it in 2007, the points remain in front of the up-down bars, even if you save, close, and reopen the workbook containing the chart. This path-dependence of the appearance of a chart is unsettling, to say the least, and it’s one reason I’m not rushing out to upgrade to 2007 for my important everyday work. On the other hand, if a client wants work done in 2007, I’m happy to oblige, but so far that’s been one client, about 5% of my work.
Hi,
I am using Excel 2003 and importing data from SQL Server 2005 Express. I would like to add extra columns to the spreadsheet in the middle of the imported query. I can do this, and it allows me to include calculations. What I would like to do is use this column to add specific comments about each record. There are a few issues with this:
1. If I have an autofilter active and it is sorted by a different column than the original import query, then it moves all the comments against different records. That is, it seems to import the data straight over the information, regardless of if I have option 2 or 3 active.
2. If a record in the middle of the data is deleted, then the comments I have input against each record no longer line up to the correct record. As in your post above, this happens regardless of if I have option 2 or 3 active.
Is there a way to get around these problems as I would like the users of the data to be able to add extra columns and use excel as per normal, but be able to import updated information/records.
Thanks
James,
I am facing the very same issue as you did and was wondering if you ever got a workaround for it that you could share.
Thanks
Hugo and James described my problem exactly. Would love to find a workaround.
The way I do it is to make all the extra columns formulas. So if I want to insert a comment, I put the comment on a separate worksheet with a unique identifier to the record, then make the inserted data a VLOOKUP to the comments range. That way, the comments always stay with their records. Putting the comments on a separate worksheet isn’t very intuitive for other users, but it’s the best I’ve got, so a little training is required.
You can use the customdocumentproperties to store any comments independent of sheets, columns, rows. etc.
You can start each comment with it’s range name, e.g.
“sheet1!A10_This is the comment;Data!H4_this is the monthly result”
You can retrieve this information and show as comments using:
sub example()for each it in split(thisworkbook.customdocumentproperties(1),";")
range(split(it,"_")(0)).comments(1).text split(it,"_")(1)
next
end sub