Flux rss

Worksheet - Cells

The Concept of a Cell

A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also used).

Coordinates (called reference styles) are generally grouped into two types, depending on the worksheet:

  • The mode known as L1C1 (Line 1, Column 1), where the cell is located by the line number preceded by the letter L and the column number preceded by the letter C. L12C34 designates the cell at the intersection of the 12th line and the 34th column.

    L1C1 mode

  • The mode known as A1, where line numbers are designated by numbers and columns by letters. Thus AA17 designates the cell at the intersection of the 27th column and the 17th line.

    A1 mode

Nota Bene Most spreadsheets have options to allow both modes to be used. With Excel and StarOffice, go into Tools > Options > General and click the box "L1C1 Reference Style".

Cell References

In order to work with data from cells when performing calculations, it is necessary to reference the cells. Many ways of referencing cells exist:

Absolute References

An absolute reference represents the method of distinguishing a unique cell in a worksheet. Depending on the reference mode (L1C1 or A1), the absolute reference will be written differently:

  • In L1C1 mode: a cell's absolute reference is written by preceding the number by a letter L and the column number by the letter C.
    LLineNumberCColumnNumber
    For example, L12C24 represents the cell located at the intersection of line 12 and column 24.
  • In A1 mode: a cell's absolute reference is written by preceding the line number and the column number by the $ sign.
    $ColumnLetter$LineNumber
    For example, $AC$34 represents the cell located at the intersection of the indicated column, AC, and line, 34.

Relative References

A cell's relative reference is the expression of its position relative to another cell. Thus, the relative reference gives the difference (in terms of the number of lines and columns) between the cell (called reference) and the target cell (called referenced cell). By convention, upward difference along the vertical axis and difference towards the left along the horizontal axis are negative.

  • In L1C1 mode: the relative difference of a cell is shown as cell coordinates in parenthesis:
    L(LineNumber) C(ColumnNumber)
    For example, L(3)C(-2) represents a cell located 3 lines lower and 2 columns left of the reference cell:

    relative reference in L1C1 mode

    When there is no difference, a zero is not required in the parenthesis. Thus, L(0)C(12) may be written as LC(12).

  • In A1 mode, the expression of the difference between the cells is hidden. In effect, a relative reference in A1 mode is implicit: just click on the coordinates of the target cell (referenced) without writing the $ sign:

    relative reference in A1 mode

Mixed References

A mixed reference is a reference where the horizontal position is expressed in an absolute manner and the vertical position in a relative manner, or vice-versa.

  • In L1C1 notation, a mixed reference could look like L2C(3) or L(4)C17.
  • In A1 notation, a mixed reference could look like $C5 or F$18.

Named References

A name may be given to a cell or to a group of cells.

Nota Bene To name a cell in Excel, select the cell or the range of cells to be named and go to Insert > Name > Define

When a cell or the range of cells has a name (the term label is sometimes used), it may be referenced by name. This functionality is especially useful when certain cells or cell ranges contain characteristic data because they may be referenced by name even if the cell or the group of cells has been moved.

For example, on an invoice, using a cell name such as total_bt for the cell that gives the total of an order before taxes is a good idea. You can also create a cell called VAT that contains the value of the VAT. Thus, when you need to calculate the total with taxes included, it is as easy as multiplying the cell called total_bt with the cell called VAT.

Comments

Commentary (sometimes called an annotation) may be added to a cell to provide additional information that we do not wish (or that we cannot) show on the worksheet.

To add a comment to a cell in Excel, select the cell or the range of cells to be named and then go to Insert > Comment or click on the cell or group of cells with the right mouse button and choose Insert a comment.

Inserting comments into a cell

Nota Bene The small red triangle on the upper right of the cell D5 shows that the cell has a comment. This encourages the user to slide the mouse over the cell to read its contents.



Last update on Thursday October 16, 2008 02:43:17 PM.
This document entitled « Worksheet - Cells » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the licence, as this note appears clearly.

Results for Worksheet Cells

Spreadsheets - Cell Selection Spreadsheets are powerful tools for working with data. However, to work with data, it is necessary to have tools to rapidly choose the required cells. An entire line can be chosen by clicking directly on the line header: It is also possible to choose... en.kioskea.net/tableur/tabcellselect-1.php3
Excel tips : How to insert date In an Excel cell Excel tips : How to insert date In an Excel cell Below are some tips on how to insert date and time in an excel cell for a specific purpose:- To insert current date, press “CTRL”+ “;” in the chosen cell. To insert current... en.kioskea.net/faq/sujet-313-excel-tips-how-to-insert-date-in-an-excel-cell
Select a subset of cells within a row (Solved) Hello, I am trying to select a number of cells within a row - I want to use this in a macro. Whatever cell is currently active, I want to select cells from column B to Column M in the current row. Thanks, M en.kioskea.net/forum/affich-22683-select-a-subset-of-cells-within-a-row

Results for Worksheet Cells

Colouring cells on conditionsColouring cells on conditions There are many pratical functions under Excel which is not commonly used. Example: If you wish a cell automatically turns red (or other formatting border, frame etc) under one condition: a result, a... en.kioskea.net/faq/sujet-956-colouring-cells-on-conditions
[VBA] Detecting changes in cell[VBA] Detecting changes in cell The Event Change feature of a sheet will detects the change in the active cell but it gives no information about the content. The example given below will help you to find out if the cell was changed,... en.kioskea.net/faq/sujet-1117-vba-detecting-changes-in-cell
[Excel] Adding a fixed date in a cell[Excel] Adding a fixed date in a cell If you want to insert the current date in a cell in following format DD /MM/YYYY, and that it remains unchanged, without being updated each time the file is opened. Simply type "ctrl" + ";" in the... en.kioskea.net/faq/sujet-1142-excel-adding-a-fixed-date-in-a-cell

Results for Worksheet Cells

Excel active cell macroHello, I'm trying to write a macro in excel which would copy and paste a range from WITHIN THE SAME ROW in the same sheet as the active cell is selected. For example let's say i select a cell within range A6:P36 then i would like the macro... en.kioskea.net/forum/affich-25813-excel-active-cell-macro
Copy data into next empty cell in new sheetHello, I am trying to make a 2 sheet workbook which has the input details in Sheet1 A1 and this is changed weekly but the info in this cell should then be inserted into the first blank cell in Column A in Sheet 2. I have tried this code (below) but it... en.kioskea.net/forum/affich-16567-copy-data-into-next-empty-cell-in-new-sheet
Adding a cell in a list using Excel FormulaHello, I need help using Excel. I would like to add a cell into a list if a value of the previous cell is greater than 0xFF. Here is what I need it to look like: $00 $24C0 $D2 $00 $C0 $24 $D2 Does anyone know of a formula or macro that I could use?... en.kioskea.net/forum/affich-20896-adding-a-cell-in-a-list-using-excel-formula

Results for Worksheet Cells

Download Debt Reduction - Zilch StandardDebt Reduction - Zilch Standard is a manager of debt. It helps the user to get rid of its debts. Very easy to use, simply fill in the blank cells and click a button. In addition, the application does not require financial expertise. The program is... en.kioskea.net/telecharger/telecharger-1588-debt-reduction-zilch-standard
Download Alarm Clock Pro for WindowsAlarm Clock Pro lets you manage your daily timing and automatically perform various tasks such as: read your list of Favorite song iTunes. It will display quotes randomly throughout the day and send them as text message to your cell phone. It also... en.kioskea.net/telecharger/telecharger-1390-alarm-clock-pro-for-windows

Results for Worksheet Cells

Japan to get cellphone 'sommeliers': officialJapan is to start licensing cellphone "sommeliers" to guide consumers through complicated functions as mobile telephones become ever more advanced, an official said Monday. The communications ministry said it was planning to support a private-sector... en.kioskea.net/actualites/japan-to-get-cellphone-sommeliers-official-10006-actualite.php3
Fuel-cell cars still far from showroom: ToyotaKatsuaki Watanabe, president of Japan's auto giant Toyota Motor, gives a press conference at Japan National Press Club in Tokyo. Work is moving ahead to build a next-generation eco-friendly car running on fuel cells but it will take years to... en.kioskea.net/actualites/fuel-cell-cars-still-far-from-showroom-toyota-10201-actualite.php3
Toyota brings fuel cell dream closer to realityToyota Motor Corp. said Friday it had developed a new zero-emission fuel-cell vehicle that has a longer cruising distance than previous models and can operate in freezing temperatures. Toyota Motor Corp. said Friday it had developed a new zero... en.kioskea.net/actualites/toyota-brings-fuel-cell-dream-closer-to-reality-10432-actualite.php3

Results for Worksheet Cells

Spreadsheets - Data Entry A cell of a worksheet can contain a value or be empty. The value of a cell has two essential characteristics: a type, which means the intrinsic type of the data. There are generally three types of values: numeric values, for example 3.1415927, 58%,... en.kioskea.net/tableur/tabsaisie.php3
Spreadsheet - Text Operators Spreadsheets generally use an oeprator, called a concatenation operator , that allows two text values to be added together. The concatenation operator, written as &, is used as follows: In the above example, the cell value B15 ("Total TTC") is... en.kioskea.net/tableur/tabtexte.php3
Spreadsheets - Worksheets Spreadsheets display data and formulas in a table form (lines and columns) called a worksheet. A worksheet is made of lines (numbered with numbers) and of columns (numbered with letters). The intersection of a line and a column is called a cell. A... en.kioskea.net/tableur/tabfeuille.php3