Entering and editing worksheet data


Excel has three layers


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

Entering worksheet data


Alexander (2019, gp29) list four 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.
  4. An error, such as #N/A. An error is return value

Previous editions of Alexander (Walkenbach) included only data types 1 to 3


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 Alexander's (2019) 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: 42370

    • 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. When used with Boolean logic, FALSE is 0 and TRUE can be any positive, non-zero integer. Note: VBA allows data type conversion. FALSE is converted to 0, and TRUE is converted 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.


  3. 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 ([✗], see figure 2) on the formula bar, 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




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
^ Exponentiation
% 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.


4. Operator (Reference)Description
: colon characterRange - 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.


Fig 3: Excel Web App #1: Excel operators

References