WO2001057744A2 - Improvements in or relating to spreadsheets - Google Patents
Improvements in or relating to spreadsheets Download PDFInfo
- Publication number
- WO2001057744A2 WO2001057744A2 PCT/GB2001/000427 GB0100427W WO0157744A2 WO 2001057744 A2 WO2001057744 A2 WO 2001057744A2 GB 0100427 W GB0100427 W GB 0100427W WO 0157744 A2 WO0157744 A2 WO 0157744A2
- Authority
- WO
- WIPO (PCT)
- Prior art keywords
- cell
- spreadsheet
- data
- cells
- deep
- Prior art date
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F40/00—Handling natural language data
- G06F40/10—Text processing
- G06F40/166—Editing, e.g. inserting or deleting
- G06F40/177—Editing, e.g. inserting or deleting of tables; using ruled lines
- G06F40/18—Editing, e.g. inserting or deleting of tables; using ruled lines of spreadsheets
Definitions
- the present invention relates to improvements in or relating to spreadsheets, and is more particularly concerned with extending such spreadsheets to a third dimension.
- Each spreadsheet is two- dimensional and comprises a rectangular grid of cells, each cell containing either numeric or textual data or a mathematical formula for producing an output or outputs usually in dependence upon the values stored in one or more other cells.
- Each cell is conventionally addressed by Al, A2, ..., B 1, B2, ..., C1, C2, ..., etc.
- a spreadsheet comprising a plurality of cells, at least one cell having more than one data value stored therein.
- a method of entering a plurality of data values into a single cell of a spreadsheet comprising:- selecting a range of single value cells one cell wide; and copying said range into the single cell.
- a method of extracting data from a single cell of a spreadsheet which contains a plurality of data values comprising:- selecting the single cell; and pasting the data values into a range of single value cells one cell wide.
- a method of editing data in a single cell of a spreadsheet which contains a plurality of data values comprising:- copying the data values from the single cell into a row or column; editing the data values to form a revised row or revised column; and copying the revised row or column back into the single cell.
- Figure 1 illustrates a conventional spreadsheet
- Figure 2 illustrates a cell from a spreadsheet in accordance with the present invention.
- a conventional spreadsheet 10 is shown. It shows a grid comprising ten rows 1, 2, ..., 10, and ten columns A, B, ..., J, that is, one hundred cells.
- each cell is referenced by its column and row, for example, Al, B4, C3, D2 etc.
- each cell is capable of storing multiple elements or data values, that is, to provide a third dimension. Addressing of such multiple elements or data values could be achieved by introducing a third (optional) index.
- One approach would be to re-use the alphabetic designation so that multiple elements stored in a cell, for example, C7 as shown in Figure 2, are designated C7A. C7B, C7C, C7D, C7E and C7F. However, it will be appreciated that other designations could be used, for example, C7-1, C7-2, C7-3 etc.
- cell C7 comprises six elements or data values.
- the number of elements stored in a cell could vary from cell to cell and any other number of elements can be stored in a cell in accordance with the particular application.
- some of the cells may contain only one element, and it may be the case that the majority of cells in certain applications will each contain only one element.
- a cell contains multiple elements or data values, referred to hereinafter as a 'deep' cell
- the cell itself can be treated as a vector for number computational purposes.
- a formula such as SUM(C7*D3) computes the inner product. It would also be possible to plot C7 against D3 simply by quoting the cell references, but if it is not desired to plot the whole sequence, the full addressing as described above could be used. For example, if C7 has twenty elements C7A:C7T. it would also be possible to address a subset of those elements, for example, C7A:C7P.
- Processing of data for example, entering, viewing and editing third dimension data, in a 'deep' cell can be carried out in a number of ways.
- One approach for entering the data into a 'deep' cell is to select a vertical or horizontal range, one cell wide, of single value cells and select the 'copy' function. Then select a single cell into which the data is to be inserted and select the 'paste special' function under which a new menu item such as "paste into third dimension' or 'paste into 'deep ' cell' can be selected.
- Extracting data from a 'deep' cell can be performed by, for example, selecting a 'deep ' cell followed by the 'copy' function, selecting a vertical or horizontal range of cells of one cell wide and length equal to the depth of the source cell and the applying the 'paste' function.
- a single cell could be selected and followed by the 'paste special' function.
- Editing elements in a 'deep' cell can be done by copying the values from the 'deep' cell into a row or column, performing the editing steps, copying back the revised row or column into the 'deep' cell and then deleting the row or column.
- 'double clicking' on a 'deep' cell could open a new window consisting of a single row or column of entries corresponding to the elements in the 'deep' cell.
- the elements could then be edited in the same way as a conventional spreadsheet.
- an additional delimiter is required.
- a delimiter is a comma where elements separated by commas all go into a single 'deep' cell. Any other suitable delimiter could also be used.
- a two-dimensional 'rich' cell can be created by either collapsing a two-dimensional area of spreadsheet or a row/column of
- the present invention has the potential to allow considerable streamlining of handling large amounts of data in spreadsheets. In many cases, it should be possible to view the top level structure of the data in a single screen without having to scroll.
- the present invention is extensible to any application containing grid or grid-like presentation of data, such as plotting diagrams and, where applicable, database applications.
- a 'deep' or 'rich' cell can be displayed in a number of different ways.
- One way is display the first element of such a cell. However, this is not very useful as the displayed element may not be representative of the remainder of the data and may be confusing.
- An alternative is to allow any text to be typed into a cell containing a 'deep' or 'rich' cell.
- a cell could be identified as 'deep' or 'rich' by the use of distinctive shading, border or both - possibly using unique colouring.
Abstract
Described herein is an improved spreadsheet which has at least the facility of storing data in a third dimension. In such a spreadsheet, multiple elements may be stored in a single cell.
Description
IMPROVEMENTS IN OR RELATING TO SPREADSHEETS
The present invention relates to improvements in or relating to spreadsheets, and is more particularly concerned with extending such spreadsheets to a third dimension.
Conventional spreadsheet applications contain one or more document(s), each document typically containing one or more sheets which may comprise spreadsheets or charts. Each spreadsheet is two- dimensional and comprises a rectangular grid of cells, each cell containing either numeric or textual data or a mathematical formula for producing an output or outputs usually in dependence upon the values stored in one or more other cells. Each cell is conventionally addressed by Al, A2, ..., B 1, B2, ..., C1, C2, ..., etc.
Whilst having multiple sheets of spreadsheet type within one document improves flexibility, it is restricting in that addressing between sheets has extensions and it is laborious to transfer data between sheets.
It is therefore an object of the present invention to provide a spreadsheet which allows the storage of multiple data values in a single cell.
It is also an object of the present invention to allow for the processing of multiple data values in a single cell.
In accordance with one aspect of the present invention, there is provided a spreadsheet comprising a plurality of cells, at least one cell having more than one data value stored therein.
In accordance with another aspect of the present invention, there is provided a method of entering a plurality of data values into a single
cell of a spreadsheet, the method comprising:- selecting a range of single value cells one cell wide; and copying said range into the single cell.
In accordance with a further aspect of the present invention, there is provided a method of extracting data from a single cell of a spreadsheet which contains a plurality of data values, the method comprising:- selecting the single cell; and pasting the data values into a range of single value cells one cell wide.
In accordance with yet another aspect of the present invention, there is provided a method of editing data in a single cell of a spreadsheet which contains a plurality of data values, the method comprising:- copying the data values from the single cell into a row or column; editing the data values to form a revised row or revised column; and copying the revised row or column back into the single cell.
For a better understanding of the present invention, reference will now be made, by way of example only, to the accompanying drawings in which: -
Figure 1 illustrates a conventional spreadsheet; and
Figure 2 illustrates a cell from a spreadsheet in accordance with the present invention. In Figure 1, a conventional spreadsheet 10 is shown. It shows a grid comprising ten rows 1, 2, ..., 10, and ten columns A, B, ..., J, that is, one hundred cells. As is well known, each cell is referenced by its column and row, for example, Al, B4, C3, D2 etc.
In accordance with the present invention, each cell is capable of storing multiple elements or data values, that is, to provide a third dimension. Addressing of such multiple elements or data values could be achieved by introducing a third (optional) index. One approach would be to re-use the alphabetic designation so that multiple elements
stored in a cell, for example, C7 as shown in Figure 2, are designated C7A. C7B, C7C, C7D, C7E and C7F. However, it will be appreciated that other designations could be used, for example, C7-1, C7-2, C7-3 etc. As shown in Figure 2, cell C7comprises six elements or data values. It will readily be appreciated that the number of elements stored in a cell could vary from cell to cell and any other number of elements can be stored in a cell in accordance with the particular application. Naturally, some of the cells may contain only one element, and it may be the case that the majority of cells in certain applications will each contain only one element.
Where a cell contains multiple elements or data values, referred to hereinafter as a 'deep' cell, the cell itself can be treated as a vector for number computational purposes. Thus, if both cells C7 and D3 contain twenty numeric values, a formula such as =SUM(C7*D3) computes the inner product. It would also be possible to plot C7 against D3 simply by quoting the cell references, but if it is not desired to plot the whole sequence, the full addressing as described above could be used. For example, if C7 has twenty elements C7A:C7T. it would also be possible to address a subset of those elements, for example, C7A:C7P.
Processing of data, for example, entering, viewing and editing third dimension data, in a 'deep' cell can be carried out in a number of ways.
One approach for entering the data into a 'deep' cell is to select a vertical or horizontal range, one cell wide, of single value cells and select the 'copy' function. Then select a single cell into which the data is to be inserted and select the 'paste special' function under which a
new menu item such as "paste into third dimension' or 'paste into 'deep' cell' can be selected.
Extracting data from a 'deep' cell can be performed by, for example, selecting a 'deep' cell followed by the 'copy' function, selecting a vertical or horizontal range of cells of one cell wide and length equal to the depth of the source cell and the applying the 'paste' function. Alternatively, a single cell could be selected and followed by the 'paste special' function.
A pair of new menu items would be available in the standard spreadsheet menus - these items being not available unless a 'deep' cell is in the 'copy' buffer. These new menu items could be designated as
'paste 'deep' cell into column' or 'paste 'deep' cell into row' and would be selected accordingly.
Editing elements in a 'deep' cell can be done by copying the values from the 'deep' cell into a row or column, performing the editing steps, copying back the revised row or column into the 'deep' cell and then deleting the row or column.
Alternatively, 'double clicking' on a 'deep' cell could open a new window consisting of a single row or column of entries corresponding to the elements in the 'deep' cell. The elements could then be edited in the same way as a conventional spreadsheet.
Additionally, it could be arranged that either a change in the window is immediately reflected in the 'deep' cell or that such changes are only reflected when the window is closed. Cells containing formulae with references to 'deep* cells would themselves become 'deep' cells if the structure of the formula was such as to return multiple values. In this case, the values obtained would not
be individually editable. Thus, 'double clicking' on such a cell would open a view window but its contents would be uneditable.
It would only be necessary for the program to compute the values in such cells when they are needed for display (either in a view window or in a chart) or as intermediate results which are then used in the formula of one or more further cells. It will be necessary to store results only when they are actually being displayed.
There may also be functions, either already existing or new, which produce a number of numeric values, even though the given argument cell is not itself a 'deep' cell. Any cell containing such a function would become a 'deep' cell.
Current spreadsheets have an underlying text format in which cells are delimited horizontally by 'tabs' and vertically by 'carriage return' characters respectively. Thus, a simple text file with this format can be read as a spreadsheet. Moreover, a spreadsheet, if saved as a text file, will be output in this format.
To accommodate 'deep' cells in such a format, an additional delimiter is required. One example of such a delimiter is a comma where elements separated by commas all go into a single 'deep' cell. Any other suitable delimiter could also be used.
It would be possible to treat a contiguous vertical or horizontal range of 'deep' cells as an array for extended mathematical operations such as matrix inversion. Moreover, for plotting purposes, the depth of the 'deep' cells could either iterate over data points or over multiple plots when multiple 'deep' cells are present.
It will readily be appreciated that the invention described herein is not restricted to adding a third dimension. A single cell could hold a two- or even three-dimensional array of elements, and the principles
described above could readily be extended to accommodate such cells.
For example, a two-dimensional 'rich' cell can be created by either collapsing a two-dimensional area of spreadsheet or a row/column of
'deep' cells. The present invention has the potential to allow considerable streamlining of handling large amounts of data in spreadsheets. In many cases, it should be possible to view the top level structure of the data in a single screen without having to scroll.
Furthermore, the present invention is extensible to any application containing grid or grid-like presentation of data, such as plotting diagrams and, where applicable, database applications.
A 'deep' or 'rich' cell can be displayed in a number of different ways. One way is display the first element of such a cell. However, this is not very useful as the displayed element may not be representative of the remainder of the data and may be confusing. An alternative is to allow any text to be typed into a cell containing a 'deep' or 'rich' cell.
A cell could be identified as 'deep' or 'rich' by the use of distinctive shading, border or both - possibly using unique colouring.
In many spreadsheet applications it is possible to apply a name as a substitute for the address of a cell or range of cells. If a 'deep' or
'rich' cell is labelled with a text field, this could serve as a referencable name for the data contained in the cell.
If the 'deep' or 'rich' cell is created by a formula, it would still be possible to have a field for a label.
Claims
1. A spreadsheet comprising a plurality of cells, at least one cell having more than one data value stored therein.
2. A spreadsheet according to claim 1, wherein each data value in said at least one cell has a unique reference.
3. A spreadsheet according to claim 1 or 2, further comprising at least one new menu item.
4. A spreadsheet according to any one of the preceding claims, having an underlying text format including an additional delimiter.
5. A method of entering a plurality of data values into a single cell of a spreadsheet, the method comprising:- selecting a range of single value cells one cell wide; and copying said range into the single cell.
6. A method of extracting data from a single cell of a spreadsheet which contains a plurality of data values, the method comprising:- selecting the single cell; and pasting the data values into a range of single value cells one cell wide.
7. A method of editing data in a single cell of a spreadsheet which contains a plurality of data values, the method comprising:- copying the data values from the single cell into a row or column; editing the data values to form a revised row or revised column; and copying the revised row or column back into the single cell.
8. A method according to claim 7, further comprising the step of deleting the revised row or column.
9. A spreadsheet substantially as hereinbefore described with reference to the accompanying drawings.
Applications Claiming Priority (2)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
GB0002291.3 | 2000-02-02 | ||
GB0002291A GB2358936A (en) | 2000-02-02 | 2000-02-02 | Improvements in or relating to spreadsheets |
Publications (2)
Publication Number | Publication Date |
---|---|
WO2001057744A2 true WO2001057744A2 (en) | 2001-08-09 |
WO2001057744A3 WO2001057744A3 (en) | 2002-07-18 |
Family
ID=9884751
Family Applications (1)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
PCT/GB2001/000427 WO2001057744A2 (en) | 2000-02-02 | 2001-02-02 | Improvements in or relating to spreadsheets |
Country Status (2)
Country | Link |
---|---|
GB (1) | GB2358936A (en) |
WO (1) | WO2001057744A2 (en) |
Cited By (3)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US9934215B2 (en) | 2015-11-02 | 2018-04-03 | Microsoft Technology Licensing, Llc | Generating sound files and transcriptions for use in spreadsheet applications |
US9990349B2 (en) | 2015-11-02 | 2018-06-05 | Microsoft Technology Licensing, Llc | Streaming data associated with cells in spreadsheets |
US10545942B2 (en) | 2016-06-13 | 2020-01-28 | International Business Machines Corporation | Querying and projecting values within sets in a table dataset |
Families Citing this family (3)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US9047266B2 (en) | 2004-10-07 | 2015-06-02 | International Business Machines Corporation | Methods, systems and computer program products for processing cells in a spreadsheet |
US8745483B2 (en) * | 2004-10-07 | 2014-06-03 | International Business Machines Corporation | Methods, systems and computer program products for facilitating visualization of interrelationships in a spreadsheet |
US10545953B2 (en) | 2015-11-03 | 2020-01-28 | Microsoft Technology Licensing, Llc | Modern spreadsheet arrays |
Citations (6)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US5247611A (en) * | 1989-09-15 | 1993-09-21 | Emtek Health Care Systems, Inc. | Spreadsheet cell having multiple data fields |
US5325478A (en) * | 1989-09-15 | 1994-06-28 | Emtek Health Care Systems, Inc. | Method for displaying information from an information based computer system |
US5632009A (en) * | 1993-09-17 | 1997-05-20 | Xerox Corporation | Method and system for producing a table image showing indirect data representations |
US5926822A (en) * | 1996-09-06 | 1999-07-20 | Financial Engineering Associates, Inc. | Transformation of real time data into times series and filtered real time data within a spreadsheet application |
US6002865A (en) * | 1992-05-28 | 1999-12-14 | Thomsen; Erik C. | Location structure for a multi-dimensional spreadsheet |
US6006240A (en) * | 1997-03-31 | 1999-12-21 | Xerox Corporation | Cell identification in table analysis |
-
2000
- 2000-02-02 GB GB0002291A patent/GB2358936A/en not_active Withdrawn
-
2001
- 2001-02-02 WO PCT/GB2001/000427 patent/WO2001057744A2/en active Application Filing
Patent Citations (6)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US5247611A (en) * | 1989-09-15 | 1993-09-21 | Emtek Health Care Systems, Inc. | Spreadsheet cell having multiple data fields |
US5325478A (en) * | 1989-09-15 | 1994-06-28 | Emtek Health Care Systems, Inc. | Method for displaying information from an information based computer system |
US6002865A (en) * | 1992-05-28 | 1999-12-14 | Thomsen; Erik C. | Location structure for a multi-dimensional spreadsheet |
US5632009A (en) * | 1993-09-17 | 1997-05-20 | Xerox Corporation | Method and system for producing a table image showing indirect data representations |
US5926822A (en) * | 1996-09-06 | 1999-07-20 | Financial Engineering Associates, Inc. | Transformation of real time data into times series and filtered real time data within a spreadsheet application |
US6006240A (en) * | 1997-03-31 | 1999-12-21 | Xerox Corporation | Cell identification in table analysis |
Cited By (9)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US9934215B2 (en) | 2015-11-02 | 2018-04-03 | Microsoft Technology Licensing, Llc | Generating sound files and transcriptions for use in spreadsheet applications |
US9990349B2 (en) | 2015-11-02 | 2018-06-05 | Microsoft Technology Licensing, Llc | Streaming data associated with cells in spreadsheets |
US9990350B2 (en) | 2015-11-02 | 2018-06-05 | Microsoft Technology Licensing, Llc | Videos associated with cells in spreadsheets |
US10031906B2 (en) | 2015-11-02 | 2018-07-24 | Microsoft Technology Licensing, Llc | Images and additional data associated with cells in spreadsheets |
US10503824B2 (en) | 2015-11-02 | 2019-12-10 | Microsoft Technology Licensing, Llc | Video on charts |
US11080474B2 (en) | 2015-11-02 | 2021-08-03 | Microsoft Technology Licensing, Llc | Calculations on sound associated with cells in spreadsheets |
US11106865B2 (en) | 2015-11-02 | 2021-08-31 | Microsoft Technology Licensing, Llc | Sound on charts |
US10545942B2 (en) | 2016-06-13 | 2020-01-28 | International Business Machines Corporation | Querying and projecting values within sets in a table dataset |
US11222000B2 (en) | 2016-06-13 | 2022-01-11 | International Business Machines Corporation | Querying and projecting values within sets in a table dataset |
Also Published As
Publication number | Publication date |
---|---|
GB0002291D0 (en) | 2000-03-22 |
WO2001057744A3 (en) | 2002-07-18 |
GB2358936A (en) | 2001-08-08 |
Similar Documents
Publication | Publication Date | Title |
---|---|---|
US5450536A (en) | Technique for automatically resizing tables | |
US5317686A (en) | Data processing apparatus and method for a reformattable multidimensional spreadsheet | |
US20080005658A1 (en) | Table column spanning | |
US10691883B2 (en) | Infinite canvas | |
US9390059B1 (en) | Multiple object types on a canvas | |
KR100956571B1 (en) | Methods, systems and computer program products for processing cells in a spreadsheet | |
US8230321B2 (en) | System in an electronic spreadsheet for displaying and/or hiding range of cells | |
EP0657830A1 (en) | Method and apparatus for data management | |
US20020091728A1 (en) | Multidimensional electronic spreadsheet system and method | |
US5933833A (en) | Data table structure and calculation method for mathematical calculations of irregular cells | |
US20060095833A1 (en) | Method and apparatus for automatically producing spreadsheet-based models | |
CA2305968C (en) | Method and system in an electronic spreadsheet for introducing new elements in a cell named range according to different modes | |
US7275207B2 (en) | System and method in an electronic spreadsheet for displaying and/or hiding range of cells | |
US20060218483A1 (en) | System, method and program product for tabular data with dynamic visual cells | |
US20090044094A1 (en) | Auto-completion of names | |
US20070050700A1 (en) | Formattable spreadsheet table elements with automatic updating | |
US20150161079A1 (en) | File formats and methods for representing documents | |
WO2001057744A2 (en) | Improvements in or relating to spreadsheets | |
US20140215297A1 (en) | File formats and methods for representing documents | |
US20030120999A1 (en) | Calculating in spreadsheet cells without using formulas | |
CN100573447C (en) | The configuration method of graphic user interface | |
EP0400620A2 (en) | Method for hiding and showing spreadsheet cells | |
WO1997021176A1 (en) | A multidimensional electronic spreadsheet system and method | |
Paradis | Reading Genetic Data Files Into R with adegenet and pegas | |
Beidleman et al. | Plotting human pedigrees |
Legal Events
Date | Code | Title | Description |
---|---|---|---|
AK | Designated states |
Kind code of ref document: A2 Designated state(s): CA US |
|
AL | Designated countries for regional patents |
Kind code of ref document: A2 Designated state(s): AT BE CH CY DE DK ES FI FR GB GR IE IT LU MC NL PT SE TR |
|
121 | Ep: the epo has been informed by wipo that ep was designated in this application | ||
DFPE | Request for preliminary examination filed prior to expiration of 19th month from priority date (pct application filed before 20040101) | ||
AK | Designated states |
Kind code of ref document: A3 Designated state(s): CA US |
|
AL | Designated countries for regional patents |
Kind code of ref document: A3 Designated state(s): AT BE CH CY DE DK ES FI FR GB GR IE IT LU MC NL PT SE TR |
|
122 | Ep: pct application non-entry in european phase |