Home

Data Entry With Numbers
 
 

Fundamentals of Numeric Data Entry

 
 

Introduction
 

Besides the strings as we have seen in the previous lesson, a cell can also use a numeric value. Microsoft Excel supports different types of numbers, including integers, decimal values, and currencies, positives and negatives.

All the techniques we have studied for copying, cutting, or moving cells are also available for cells that contain numbers.

To enter a number into a cell, you can click that cell and type the number. To specify the sign of a number, the English language uses the + and the - symbols. If you simply type a number without a sign, the number is referred to as unsigned. The number is considered positive, which means it is equal to or greater than 0. An alternative is to add a sign to the number. Such a number becomes considered "signed".

A number is referred to as negative if it is less than 0. Microsoft Excel provides various ways of giving this information. To enter a negative value in a cell:

  • Type - followed by the number
  • Enter the number in (between an opening and a closing) parentheses

A Numeric Value as Text

When you enter a number in a cell, Microsoft Excel automatically recognizes it as such and aligns it to the right side of the cell. Sometimes, you will want Microsoft Excel to treat the content of a cell as text and not as a normal numeric value.

To treat a number as text:

  • If you are entering the number, start it with ' and follow it by the value
  • If the number exists already:
    • Click the cell to make it active. On the Ribbon, click Home. In the Number section, click the arrow of the combo box and select Text
       
    • Right-click the cell and click Format Cells... In the Format property page of the Format Cells dialog box, in the Category list, click Text and click OK
       

Practical Learning: Entering a Numeric Value as Text

  1. Open the Grier Summer Camp3.xlsx workbook and click the Employment Application1 if necessary to activate it
  2. Click Cell B32 and type '1.
  3. Press Enter
  4. Click Cell B38, type '2. and press Enter
  5. Click Cell B44, type '3. and press Enter

Categories of Numeric Values

 

Introduction

As mentioned already, Microsoft Excel provides support for various types of numbers. This is not just about allowing different types of numbers into cells. The application has default mechanisms to recognize a type of number and sometimes format it appropriately. Still, because it is just a machine-driven operator, it can present results you do not want or even did not intend. You can then work on the cells and make them apply the settings you want. Fortunately, Microsoft Excel provides all the tools you would need. You can also use your own knowledge of computer applications to perform some of the configurations.

Workbooks

To illustrate the various uses of numbers, we will use this section as our introduction to the practice of transaction analysis of accounting. To have an idea of the design of the worksheet we will use, you should take a look at our article on transaction analysis.

Integers

An integer a number that does not have a decimal part. Microsoft Excel supports small to very large numbers. To use an integer, click a cell and type it. If the number is greater that 999, you can simply enter it. If you want such a number to be easily readable and use the thousand separator, you can enter it with that character. The thousand separator in the US English is the comma as it can be verified in the Regional Options from Control Panel. It is represented in the Digit Grouping Symbol combo box:

To display the number with the thousand separator, use it when entering the number.

In the following workbook, the company College Park Auto Park starts with a budget of $18,000. This is already entered in the worksheet in the Cash account as an asset for 18,000. To balance the equation, the capital of the company is recorded as 18,000.

Practical Learning: Introducing Numeric Data Entry
  1. Open the CPAR1.xlsx workbook
  2. The company purchases two large garbage cans for the shop, a medium garbage can for the office, a box of toilet paper, hand soap and lotion for the restroom, four towels, and printing paper. The company spends 320. This amount is recorded in the Supplies account but the Cash decreases for the same amount.
    Click Cell C7 and type -320
  3. Click Cell G7 and type +320
  4. The company subscribes to various magazines that the customers will read while waiting. The owner does not yet pay for them but promises to pay in a few days. The company gets the bills by fax for those subscriptions. The total amount of the subscriptions is 120. Since the amount has not yet been paid (but will be paid), the Accounts Payable receives an increase of 120 and the Capital account decreases by the same amount.
    Click Cell K10 and type +120
  5. Click Cell M10 and type -120
  6. Paul Jeffreys takes 350 from the cashier for personal use. As a result, the Cash account decreases by that amount and the Capital decreases by the same amount.
    Click Cell C13 and type -350
  7. Click Cell M13 and type -350
  8. The company pays the magazines subscription for the total amount owed. As a result, the Cash account decreases by 120 and the Accounts Payable decreases by the same amount.
    Click Cell C15 and type -120
  9. Click Cell K15 and type -120
  10. Press Ctrl + Home

College Park Auto Repair
 
 
 

Decimal Numbers

Generally, a number is referred to as decimal when it is made of two sections separated by a symbol called the decimal symbol. In US English, the decimal symbol is the period.

The left side of the decimal symbol contains one or more digits. If the number on that part is less than 1000, you can just use it like that. If the number is equal to or greater than 1000, if you want, you can make it display the thousand separator. When entering the number in a cell, if the number on the left side of the decimal symbol is 0, you can omit it. On the right side of the decimal symbol, you use digits only.

If the number in a cell appears as an integer and you want to convert it to its decimal equivalent:

  • Click the number. On the Ribbon, click Home. In the Number section, click the arrow of the combo box and select Number
  • Right-click the number and click Format Cells... In the Number tab of the Format Cells dialog box, in the Categories list, click Number and click OK

Practical Learning: Entering Decimal Values
  1. The company acquires a tire changer that retails for $1250. The company promises to pay for it in the future (but the machine is delivered, thus acquired). The Accounts Payable receives an increase of 1250 and the Equipment account receives an increase of 1250.
    Click Cell I8 and type +1,150
  2. Click Cell K8 and type +1,150
  3. The company purchases a computer and an All-In-One printer. The total is 1850. This is recorded in the Equipment account after the Cash account has decreased for the same amount.
    Click Cell C9 and type -1,700
  4. Click Cell I9 and type +1,700
  5. The company replaces tires and makes many other repairs for various cars. The company makes 2650 on that day and all customers pay everything they owe. As a result, the Cash and the Capital accounts increase each by that amount.
    Click Cell C11 and type +2,650
  6. Click Cell M11 and type +2,650
  7. The company performs a tune-up, installs new brakes, and changes the whole muffler system for Customer Gertrude Monay. When presented with the invoice of 1050, the customer pays 650. The rest would be paid at a later date. As a result, the Cash account gets 650 (the amount the customer actually paid). The Capital account gets 1050 (the full amount the customer was supposed to pay). The Accounts Receivable gets the balance (1050 - 650 = 400) that will be paid in the future.
    Click Cell C12 and type +650
  8. Click Cell E12 and type +400
  9. Click Cell M12 and type +1,050
  10. The company performs tune-up and changes the brakes for various customers. The company makes $1750 on that day. Its Cash account increases by 1500 and the Capital also increases by 1500.
    Click Cell C14 and type +1,750
  11. Click Cell M14 under the Capital account and type +1,750
  12. Gertrude Monay comes to pay her balance. Therefore, the Cash account increases for the amount she pays and the Accounts Receivable decreases by the same amount.
    Click Cell C16 and type +400
  13. Click Cell E16 and type -400
  14. The company pays the rent for 2250, pays a guy who came to help at the shop for 350, pays the electric bill for 220. This means that the cash flow decreases by that total. Each expense is subtracted from the Capital account (you must enter each expense on its own line as a negative value in the Capital column).
    Click Cell C17 and type -2,820
  15. Click Cell M17 and type -2,250
  16. Click Cell M18 and type -350
  17. Click Cell M19 and type -220
  18. Press Ctrl + Home

College Park Auto Repair

 

  1. The CPAR1.xlsx workbook should still be opened
  2. Click the Repair Invoice tab
     
    College Park Auto Repair
  3. Enter the following information:
     
    Invoice #: 1001
    Name: Gertrude Monay
    Make: Honda
    Model: Accord
    Year: 2002
  4. Save the workbook

Currency Values

A currency value is a number that displays with the symbol that represents a monetary value. In US English, this symbol is $ as you can see in the Customize Regional Options from the Control Panel: 

To enter a number as currency for US English, click the cell, type $ followed by the number. You can enter the number as an integer or as a decimal value. After entering the number, Microsoft Excel would convert it to currency.

To convert the value of a cell to currency:

  • Click the cell. On the Ribbon, click Home. In the Number section, click the arrow of the combo box and click Currency or Accounting
  • Click the cell. On the Ribbon, click Home. In the Number section, click the Accounting Number Format button
  • Right-click the cell and click Format Cells... In the Categories list of the Number property page of the Format Cells dialog box, click Currency and click OK

By default, if you are using a computer where the US English version of Microsoft Windows is installed, the $ is considered the currency. If you want to use a different currency, click the cell. On the Ribbon, click Home. In the Number section, click the arrow the $ button and select from the list:

Practical LearningPractical Learning: Entering Currency Values
  1. Enter the following information (remember to type the $:
     
    Part # Part Name Unit Price Qty
    759073 Air Filter $20.15 1
    391536 Fuel Filter $50.85 1
    205884 Spark Plugs $4.15 4
     
    Job Performed Rate
    Replaced the air filter, the fuel filter, and the spark plugs $50
    Adjusted the valves $125
  2. Save the workbook

College Park Auto Parts

Percentage Values

One of the most commonly used type of number in a spreadsheet displays as a percentage value. To enter a percentage value in a cell, type the number followed by the % symbol. To convert a number to a percentage:

  • Click the cell. On the Ribbon, click Home. In the Number section, click the arrow of the combo box and select Percentage
  • Click the cell. On the Ribbon, click Home. In the Number section, click the Percent Style button
  • Right-click the cell and click Format Cells... In the Categories list of the Number property page of the Format Cells dialog box, click Percentage and click OK

Practical LearningPractical Learning: Entering Data Into Cells
  1. Click Cell J33
  2. Type 5.75% and press Ctrl + Home
     
    College Park Auto Repair
  3. Save the workbook
 
 
   
 
Home Copyright © 2007-2009 FunctionX, Inc. Next