Entering and editing worksheet data

Excel has three layers

The Excel worksheet has three virtual layers as illustrated in figure 1.

  • Bottom (formula layer). As shown in the Formula Bar. This is the region where data is entered (when in cell editing is disabled)
  • Top layer (the value layer). The formatted contents visible in a cell, such as a number of the result of a formula
  • Drawing layer (a floating layer). Usually populated by items from the Insert tab. The items can be moved any where on the drawing layer, and usually have an associated Contextual tab
  • xlf-excel-layers-v2
    Fig 1: Excel worksheet layers – drawing (object), top (cell), and bottom (formula bar)

Entering worksheet data

Walkenbach (2013, p29) list three types of data.

  1. Numeric values (numbers)
  2. Text, also referred to as labels (any data that is not a number or a formula)
  3. A formula (commences with an equals (=) sign. Note: formulas can contain both numbers and text.

In contrast, the Excel TYPE function returns five types, but some of these exist only after the data is entered. Syntax: TYPE(value). The TYPE function returns the type of value (in a reference or cell).

  1. Number – return value 1
  2. Text – return value 2
  3. Logical value – return value 4
  4. Error value – return value 16
  5. Array – return value 64

Following Walkenbach’s (2013) classification:

  1. Numbers
    • Excel stores all numbers in computer memory as double precision floating point
    • This means that Excel numbers have a maximum of 15 digit precision. In most cases this is not a limitation, but a credit card number with 16 digits must be entered as text, other wise 1234123412341234 will have the value 1234123412341230 in Excel
    • By default, number are Left Aligned in a cell
    • The appearance of a number in a cell is determined by its format, e.g. General, Currency, Accounting, Date, …
    • Number is Excel can take a number of forms, which are mostly identified by their format. These include integers, floating point numbers, dates, times, and Boolean true or false
    • Integers (whole numbers) eg. 100, -45
    • Numbers are entered WITHOUT the thousands separator (described as the digit grouping symbol in a Windows system), or currency symbol. A trailing percentage character is permitted
    • Formula bar: -45 returns -45
    • A large number such as 10,000,000 may appear in the cell in scientific number format as 1E+07. Entered in the Formula bar as 10000000, the return value is interpreted as 1 Exponent 10 to the power +7, \(10,000,000=1 \times 10^{7}\)
    • Floating point numbers (numbers with a decimal mark or separator)
    • The decimal mark or separator is described as the decimal symbol in a Windows system
    • The decimal symbol depends on your Windows Regional Settings, and is a period or full stop (.) for Australia, whilst regions such as the Euro zone use a comma (,)
    • Formula bar: 23.56 returns 23.56
    • Formula bar: 2.71828 returns 2.71828. This is Euler’s number to 5 decimal places
    • Formula bar: 1.496E+8 returns 1.50E+8 (2 decimal place scientific format). This is the approximate distance from earth to the sun in kilometers!
    • Formula bar: 0.1 or .1 returns 0.10. Used to enter interest rates (10%) in decimal format
    • Leading zeros are not permitted except for a zero preceding a decimal symbol, or some cases with dates and times. To enter a phone number with a leading zero, say 0419395555, it must be entered as TEXT
    • Floating point numbers – percentages
    • Formula bar: 5.6% returns 5.6% to the cell. The is stored as the decimal value 0.056 in computer memory. This can be verified by viewing the XML version of the file in a zip program
    • Floating point numbers – fractions
    • Excel fractions are a number with a space separating the integer and its fractional part
    • Formula bar: 1 7/8 returns 1 7/8 to the cell, and displays the decimal value 1.875 in the formula bar
    • Formula bar: 0 15/16 returns 15/16 to the cell, and displays the decimal value 0.9375 in the formula bar
    • Dates
    • Excel for Windows dates begin at 0 January 1900. This point in time is termed the epoch
    • A particular date is the serial number of the day (serial date) count since the epoch. Thus 1 January 1900 is serial date 1
    • 12 July 2015 is serial date number 42,197
    • Excel allows date data entry in date format or serial date format
    • To enter the date 1 January 2016, with the Windows Region set to Australia, possible entries include:

      • Formula bar: 1Jan16
      • Formula bar: 1-jan-16
      • Formula bar: 1/jan/16
      • Formula bar: jan16
      • Formula bar: 1January2016
      • Formula bar: 1/1/16
      • Formula bar: 42372
    • All return the value 1-Jan-16 to the cell
    • After entering the data, the Formula Bar displays 1/01/2016
    • Time
    • Excel time is based on the of 0 January 1900 value, and is the fractional part or mantissa value of the date serial number 0.
    • For example, 0.5 is 12:00 noon, 1/2 of day in Excel date / time format
    • Time data entry is identified by the colon (:) character
    • Excel allows date data entry in time format or serial date / time format
    • Various times are entered, again the Windows Region is set to Australia:

      • Formula bar: 9:00, returns 9:00 (Right Aligned) and displays 9:00:00 AM in the formula bar
      • Formula bar: 15:15:30, returns 15:15 (Right Aligned) and displays 3:15:30 PM in the formula bar
      • Formula bar: 10:00 pm, returns 10:00 PM (Right Aligned) and displays 10:00:00 PM in the formula bar
      • Formula bar: 0.4523, returns 10:51 AM (Right Aligned) and displays 10:51:19 AM in the formula bar
    • A combination of date and time
    • Formula bar: 1 Jan 26 3:00 pm, returns 1/01/2016 15:00 (Right Aligned) and displays 1/01/2016 3:00:00 PM in the formula bar
    • Conditions TRUE and FALSE Boolean values
    • Boolean values are the foundation of many decision models
    • Boolean values in a cell are Center Aligned, and uppercase
    • In Excel FALSE is equivalent to 0, and TRUE is equivalent to 1 Note: In VBA FALSE is equivalent to 0, and TRUE is equivalent to -1
    • Examples:

      • Formula bar: true, returns TRUE (Center Aligned, and uppercase) and displays TRUE in the formula bar
      • Formula bar: false, returns FALSE (Center Aligned, and uppercase) and displays FALSE in the formula bar
      • To coerce an Excel Boolean to its integer equivalent
      • Multiply by 1: If the range BoolT = TRUE, then BoolT * 1 returns 1. Also BoolT + 0 returns 1, or
      • Add zero: If the range BoolF = FALSE, then BoolF * 1 returns 0. Also BoolF + 0 returns 0, or
      • Employ the unary minus operator: – an example with TRUE. If the range BoolT = TRUE, then --BoolT returns 1. The logic is, -BoolT returns -1, and the second minus sign restores the original sign
      • Equivalent to -(-(BoolT)) = -(-1) = 1, or
      • The N function: If the range BoolT = TRUE, then =N(BoolT) returns 1
      • Similarly, if the range BoolF = FALSE, then =N(BoolF) returns 0
      • [The N function converts a value to a number]
  2. Text
    • Text is any data that is not a number
    • Maximum number of characters in a cell 32,767 characters. i.e. \(2^{15}-1\) characters
    • By default, text is Right Aligned
    • Formula bar: This is text, returns This is text
    • Formula bar: '01419395555 returns 01419395555
    • Numeric values can also be formatted as TEXT by applying the format before the value is entered
  3. Number and text entries are often referred to as constants. Their values are hard coded, unchanging, and constant.

  4. Formulas
    • The first character of a formula is an equals (=) sign
    • Maximum length of a formula is 8,192 characters. i.e. \(2^{13}\) characters
    • Formulas often use numerical constants, mathematical operators, functions, and reference. Mathematical operators are discussed in the working with numbers section
    • An Excel formula cannot contain any white space, except for an Intersection operator, entry of a fraction, or entry of a date or time
    • Formulas can also include text, which must be enclosed in double quotes – “text”
    • To include quoted text in a formula, use the double quote escape character. ="Formula ""Max characters"" is: " & 2^13 returns Formula "Max characters" is: 8192. Or Char(34) , ="Formula " & CHAR(34) & "Max characters" & CHAR(34) & " is: " & 2^13
    • Formula bar: =1+2 (in the bottom layer) returns 3 (to the top layer)
    • Formula bar: ="1 plus 2 equals: "&1+2 returns 1 plus 2 equals 3
    • Recognising the Lotus 1-2-3 heritage of Excel it is possible to enter a formula as: Formula bar: +1+2. However, after completing the entry in the Formula bar, Excel replaces the leading + sign with an = sign

To include line breaks in Text of Formulas, use the Alt + Enter keyboard shortcut.

Completing the entry

To complete the manual entry of data, either press the Enter key (this will also move the Active Cell down one row) , or click the Enter (tick) button on the Formula Bar (to stay in the same cell). To cancel an incomplete entry, click the Cancel ([x], see figure 2) on the formula, or press the Escape (Esc) key on the keyboard.

To stay in the same cell after pressing the Enter key, use the keyboard shortcut Ctrl + Enter

xlf-formula-bar-enter
Fig 2: Formula bar Cancel / Enter – Press Enter to complete, or Escape to Cancel

Edit the entry

Note – 90045 session workbooks have Direct Editing in Cell disabled. This is designed to focus attention on the Formula bar. To change the setting select File > Options > Advanced > Editing Options and look for Allow editing directly in cells.

Clear the entry, or clear format

  • To clear the values in the entry – select the cell(s) to be cleared.
  • Press the Delete key, or on the ribbon select Home > Editing > Clear > Clear Contents
  • This is not the same as deleting cell(s), rows or columns
  • To clear format
  • Select the cell(s)
  • Then select Home > Editing > Clear > Clear Formats
  • You can clear values and formats with Home > Editing > Clear > Clear All

Working with numbers

Formulas involve the use of mathematical operators. These are described in the following four panels – arithmetic, comparison – part of the Boolean TRUE / FALSE values, text, and reference. The operation of most is obvious and is demonstrated in the figure 3 – Excel Web App #1. Several operators are discussed after panels 3 and 4.

Mathematical operators

1. Operator (Arithmetic) Description
+ Addition
Subtraction and negation
* Multiplication
/ Division
^ Exponention
% Percentage

Arithmetic operators – see figure 3 – Excel Web App #1 rows 9 to 27.

2. Operator (Comparison) Description
= equals (eq) Returns TRUE if Value1 is equal to Value2. eg. Value1=Value2
< less than (lt) Returns TRUE if Value1 is less than Value2. eg. Value1<Value2
> greater than (gt) Returns TRUE if Value1 is greater than Value2. eg. Value1>Value2
<= less than or equals (lte) Returns TRUE if Value1 is less than or equal to Value2. eg. Value1<=Value2
>= greater than or equals (gte) Returns TRUE if Value1 is greater than or equal to Value2. eg. Value1>=Value2
<> not equal (ne) Returns TRUE if Value1 is not equal to Value2. eg. Value1<>Value2

Comparison operators – see figure 3 – Excel Web App #1 rows 29 to 57.

3. Operator (Text) Description
& ampersand character Concatenates, or joins multiple strings to a single string

Text operators – see figure 3 – Excel Web App #1 rows 59 to 63.

  • The concatenation operator joins multiple text strings
  • Formula bar: ="a"&"b"&"c", where a b c are individual text characters, thus the use of double quotes. The formula returns a single string abc
4. Operator (Reference) Description
: colon character Range – identifies the top left and bottom right cells in a range
(space) space character Intersection – the intersection of two ranges
, comma character Union – the union of two ranges

Reference operators – see figure 3 – Excel Web App #1 rows 65 to 85.

  • The Range operator (:) is frequently used in Excel, and is contrasted to the Date (:) because of the context in which it is used. The range operator identifies an array of cells by the top left and bottom right cells in the range. To return the SUM of the Source data in the range C68 to C71, the Formula Bar entry is =SUM(C68:C71) to return the value 10
  • A second area of Source data is provided in the range G79:K83. Two subsets are created I80:I83, shown by the red border with sum 80, and G81:K82, shown by the green border with sum 316
  • The Intersection operator returns the range where the two ranges overlap. This is the values 3, and 13 in the range I82:I82. In the Formula Bar: =SUM(I80:I83 G81:K82) returns 16
  • The Union operator returns a range as the combination of two. The ranges can also be contiguous. In this example the range with the red border sums to 80, and the range with the green border sums to 316. In the Formula Bar: =SUM(I80:I83,G81:K82) returns 396. This means that the overlap, the union, is counted twice with the intersection operator. The range G79:K83 sums to 380
  • Fig 3: Excel Web App #1: Excel operators

    References

    • Excel 2013 specifications and limits (n.d.) referenced 12 July 2013 Link
    • O’Connor I (n.d.), Region settings Link
    • Walkenbach J (2013), Excel 2013 Bible, Wiley.
      • This example was developed in Excel 2013 Pro 64 bit.
      • Last modified: 7 Mar 2018, 12:56 pm [Australian Eastern Standard Time (AEST)]