WO2001057744A2 - Improvements in or relating to spreadsheets - Google Patents

Improvements in or relating to spreadsheets Download PDF

Info

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
Application number
PCT/GB2001/000427
Other languages
French (fr)
Other versions
WO2001057744A3 (en
Inventor
Anthony Peter Hulbert
Original Assignee
Roke Manor Research Limited
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by Roke Manor Research Limited filed Critical Roke Manor Research Limited
Publication of WO2001057744A2 publication Critical patent/WO2001057744A2/en
Publication of WO2001057744A3 publication Critical patent/WO2001057744A3/en

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

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.
PCT/GB2001/000427 2000-02-02 2001-02-02 Improvements in or relating to spreadsheets WO2001057744A2 (en)

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)

* Cited by examiner, † Cited by third party
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
US9990350B2 (en) 2015-11-02 2018-06-05 Microsoft Technology Licensing, Llc Videos 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)

* Cited by examiner, † Cited by third party
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)

* Cited by examiner, † Cited by third party
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

Patent Citations (6)

* Cited by examiner, † Cited by third party
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)

* Cited by examiner, † Cited by third party
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
US9990350B2 (en) 2015-11-02 2018-06-05 Microsoft Technology Licensing, Llc Videos associated with cells in spreadsheets
US9990349B2 (en) 2015-11-02 2018-06-05 Microsoft Technology Licensing, Llc Streaming data 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
WO2001057744A3 (en) 2002-07-18
GB0002291D0 (en) 2000-03-22
GB2358936A (en) 2001-08-08

Similar Documents

Publication Publication Date Title
US9645991B2 (en) Table column spanning
US5450536A (en) Technique for automatically resizing tables
US5317686A (en) Data processing apparatus and method for a reformattable multidimensional spreadsheet
US10691883B2 (en) Infinite 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
US20150161079A1 (en) File formats and methods for representing documents
CA2330260C (en) Tabular range editing mechanism
WO2001057744A2 (en) Improvements in or relating to spreadsheets
US20110138265A1 (en) Method and apparatus for presenting date in a tabular format
US20140215297A1 (en) File formats and methods for representing documents
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
US20230214587A1 (en) Methods for polymorphic data in spreadsheets
CN116955883A (en) HTML text table rendering method and storage medium

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