| View previous topic :: View next topic |
| Author |
Message |
2CV67
General User
Joined: 16 Nov 2004
Posts: 48
Location: Alsace
|
 Posted: Mon Aug 30, 2010 4:15 am Post subject: How to add fresh data to existing chart - easily? |
 |
|
Hi guys!
I am having one of my frequent attempts to shift from MS to OOo...
Today's snag concerns updating charts in OOo Calc.
In Excel, I happen to have lots of spreadsheets with graphs, which I update regularly by adding lines of fresh data.
Having added a line, I just have to select the new data & copy/paste it into the graph, which is immediately updated.
I resaved some Excel sheets as OOo Calc & was quite impressed that most of the graphs appeared in reasonable form.
But copy/pasting new data does not work.
The only way I have found is to right-click the chart a couple of times, selecting 'Edit' then 'Data Ranges' then the 'Data Range' icon then reselect the whole new data range.
This seems pretty inefficient, especially when the existing data is over several hundred lines which all need to be reselected.
Alternatively, I can go into the 'Data Range' icon & increment the latest-line numbers twice, but that is not much more attractive.
Is there a better method?
Thanks! |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Mon Aug 30, 2010 12:36 pm Post subject: |
|
|
| Quote: | | I am having one of my frequent attempts to shift from MS to OOo... |
Forget it. Either you can handle spreadsheets or you can't. In the latter case you should never think of any tool other than the one you know.
| Quote: | In Excel, I happen to have lots of spreadsheets with graphs, which I update regularly by adding lines of fresh data.
Having added a line, I just have to select the new data & copy/paste it into the graph, which is immediately updated. |
What did they do to Excel that you need to perform a copy and paste?
In any spreadsheet application I used since 1987 (incl. Excel up to 2000) enter my data into newly inserted rows and which will update all formulas, conditional formattings, charts and what else may depend on the particular data range.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
2CV67
General User
Joined: 16 Nov 2004
Posts: 48
Location: Alsace
|
| Posted: Tue Aug 31, 2010 12:44 am Post subject: |
|
|
| Villeroy wrote: | | Forget it. Either you can handle spreadsheets or you can't. In the latter case you should never think of any tool other than the one you know. |
If I could handle spreadsheets like a superuser, I wouldn't be asking dumb questions on this forum.
But I am optimistic enough to want to progress & to believe it is possible, even at my age...
Anyway, it is a necessary step in shifting from XP to Ubuntu.
If there is some "recognized" way of adding data to an existing (long) list & having it show up on existing graphs, whether they are on the same page or different pages, then I would be extremely pleased if some kind person would point me to an explanation.
I have spent quite a long time browsing spreadsheet "help & tips" websites without finding anything useful.
| Quote: | | enter my data into newly inserted rows and which will update all formulas, conditional formattings, charts and what else may depend on the particular data range. |
I didn't manage to get either XL or Calc to react to inserting new lines then filling them with data, but if there is a clear explanation somewhere of the process, I would be delighted to have another go.
Thanks for all suggestions! |
|
| Back to top |
|
|
TessaES
OOo Advocate
Joined: 17 Feb 2007
Posts: 228
Location: Solna
|
| Posted: Tue Aug 31, 2010 12:57 am Post subject: |
|
|
| If you originally have your data in A1:B3, select row 3, right-click, insert row and add new data to that row. The new row is now included in your chart. You can sort afterwards if necessary. |
|
| Back to top |
|
|
2CV67
General User
Joined: 16 Nov 2004
Posts: 48
Location: Alsace
|
| Posted: Tue Aug 31, 2010 1:25 am Post subject: |
|
|
| TessaES wrote: | | If you originally have your data in A1:B3, select row 3, right-click, insert row and add new data to that row. The new row is now included in your chart. You can sort afterwards if necessary. |
Thanks, but:
If I do that, my old data from row 3 goes to row 4 & if I enter my new data in new blank row 3 then it is out of order.
Or did I miss something? |
|
| Back to top |
|
|
TessaES
OOo Advocate
Joined: 17 Feb 2007
Posts: 228
Location: Solna
|
| Posted: Tue Aug 31, 2010 1:36 am Post subject: |
|
|
You didn't miss something, but you can sort the data afterwards.
Or: reverse the sorting of your data and add new rows at the top. |
|
| Back to top |
|
|
2CV67
General User
Joined: 16 Nov 2004
Posts: 48
Location: Alsace
|
| Posted: Tue Aug 31, 2010 2:12 am Post subject: |
|
|
OK - that does work.
Thank you very much!
I see I do have to use the Sort function.
At first, I tried to rearrange the rows by "Drag & Drop" but then the new row was again not included in the graph.
Probably obvious to experts...
Not quite as simple as XL's copy/paste, but the result is OK.
Thanks again!  |
|
| Back to top |
|
|
2CV67
General User
Joined: 16 Nov 2004
Posts: 48
Location: Alsace
|
| Posted: Thu Sep 02, 2010 1:02 pm Post subject: |
|
|
By playing around, I found I could extend my data range to include a blank row under the real data - without plotting unwanted zeros on the chart.
Then, to add new data at the bottom, I just need to "insert new row" above that blank row & add my data in that new row.
No need to sort.
Seems OK to me... |
|
| Back to top |
|
|
2CV67
General User
Joined: 16 Nov 2004
Posts: 48
Location: Alsace
|
| Posted: Fri Sep 03, 2010 11:39 am Post subject: |
|
|
| Villeroy wrote: | | In any spreadsheet application I used since 1987 (incl. Excel up to 2000) enter my data into newly inserted rows and which will update all formulas, conditional formattings, charts and what else may depend on the particular data range. |
Not sure I have grasped all of this yet.
When I insert a row, it brings down any formatting from the row above, but does not bring down any values or formulas - does it?
So if my rows include (as they do, at present) some cells of input data, then some cells of formulas (variation from previous row, horizontal sums etc), I need to insert my new row, then add the new data then select & drag down the cells with formulae from the row above?
Or is there a better way?
Thanks for your patience! |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Fri Sep 03, 2010 12:14 pm Post subject: |
|
|
Spreadsheets are so extremely versatile and you do not tell us any details about what you do. I did not know that you can paste data sets into Excel charts. I assumed that all spreadsheet charts are bound to cells.
Why copy values into newly added rows? Do you really want duplicates as new records? What if you insert in row #1?
Well, I've written a macro for better list keeping in Calc. You select some cell anywhere in the list, trigger the macro and it will insert a new row copying down any formulas from above and selecting the remaining blank cells for editing.
[Calc, Python] expand/shrink list ranges
Personally, I prefer databases even for trivial list keeping. From there I can plot full data sets with calculated fields in Calc or in Writer reports. No more formulas to copy, no more trouble with limited ranges of values on a sheet with millions of blank cells. I enter one set in any order of columns and rows and get any filtered set of data in any other order of rows and columns.
Or do you copy and paste data into charts because you want a completely different data set in the same chart? Even in this case scenarios provide a much more elegant solution for both applications Excel and Calc:
http://user.services.openoffice.org/en/forum/download/file.php?id=3004
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
2CV67
General User
Joined: 16 Nov 2004
Posts: 48
Location: Alsace
|
| Posted: Sun Sep 05, 2010 12:49 pm Post subject: |
|
|
| Villeroy wrote: | Spreadsheets are so extremely versatile and you do not tell us any details about what you do...
Why copy values into newly added rows?...
Personally, I prefer databases even for trivial list keeping. |
Well - it's difficult to give sufficient detail without overdoing it.
I never worked with databases & didn't want to start on that learning curve just now.
I used Excel for many years, with no training, and am happy with the results, but now I am migrating from Windows to Ubuntu so I want to get OOo Calc to take over from Excel.
I have a bunch of Excel spreadsheets which I use to keep track of Meteorological, Medical & Financial data.
If I try to illustrate them by a greatly-simplified example, it might look like this:
Typically, each row includes date information, then data which I input (in this case current values of various accounts) and it also has formulas which calculate sums, differences etc.
From this evolving table, in the simplest case, I might want to just plot the Total (column H) against time. (Often I would want to plot several items from each Row, but let's stick to something simple).
To add a new day's data, and see it included in my graph, the method I am using now (after what I have learned in this thread) is:
Define data range to include currently blank Row 8. (Only need to do this once).
Select Row 8.
Insert new Row (which is blank).
Select cells B7-K7.
Copy down to cells B8-K8 (Drag with Ctrl to avoid incrementing).
Update cells D8,E8,F8,G8 with new data.
The Total & Daily Variances are updated automatically & the graph also.
Of course, the real spreadsheets are more complicated than that, but in principal that is what I am trying to do.
I don't see how I would do that without copying values, data & formulas down from Row 7 to Row 8.
I can live with this method, but if it can be simplified - so much the better.
That is why I was asking if "Insert Row" should bring down content from the Row above. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
|
| Back to top |
|
|
shades49
General User
Joined: 24 Nov 2008
Posts: 29
|
| Posted: Mon Sep 06, 2010 6:48 am Post subject: |
|
|
In Excel you can define named ranges that automatically adjust to how many items are in the list, so the formula (using the defined name) never has to be changed and you never have to worry about that last cell being empty.
I worked with Excel in corporate environment for eight years, but have not used Excel now in almost three years,using Calc occasionally. I haven't need to work with named ranges that automatically update, but now you piqued my interest. I may try to find the equivalent.
_________________
OO.org 3.3/Symphony 3/NeoOffice 3.1.2
Mac OS X 10.6.5 |
|
| Back to top |
|
|
2CV67
General User
Joined: 16 Nov 2004
Posts: 48
Location: Alsace
|
| Posted: Mon Sep 06, 2010 7:39 am Post subject: |
|
|
Thanks for all these tips!
Data Pilots look like useful tools.
I have not researched named ranges yet.
In the meantime, I found I could also get the required result by:
Setting data range to include Row 8 (once only).
Select Row 7.
Copy.
Select Row 8.
Paste Special - with "Shift cells Down" option.
- That gives me a non-incremented copy of Row 7 in Row 8.
- Row 9 is included in the data range, ready for the next update.
- Row 8 is included in the data range & plotted on the chart.
I just need to update the data (cells D8-G8) in Row 8.
That seems slightly simpler than the previous method. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Mon Sep 06, 2010 11:09 am Post subject: |
|
|
| shades49 wrote: | In Excel you can define named ranges that automatically adjust to how many items are in the list, so the formula (using the defined name) never has to be changed and you never have to worry about that last cell being empty.
|
Come on, how do you do that in Excel? I think you mean something that works exactly the same in Calc:
Insert>Names>Define... [Ctrl+F3]
Name:Used
Refers to: OFFSET($A$1:$X$65536;0;0;COUNTA($A$1:$X$65536))
[Add]
[OK]
In Excel we can express:
OFFSET($A$A;0;0;COUNTA($A:$X))
Now the named reference "Used" refers to columns A to X, resized to the amount of filled rows in A.
This fails with no errors but wrong results as soon as you have some gaps in A (intentionally or not) or when you add any cell content below the assumed list range or when your list does not start in A1.
Databases are the tools for all list keeping. Spreadsheets are too error prone.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
|
