Fundamentals of Numeric Data Entry

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

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
     
    

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

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.

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.

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.

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

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

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

1. The CPAR1.xlsx workbook should still be opened
2. Click the Repair Invoice tab
    
   
3. Enter the following information:
    

   Invoice #:	1001
   Name:	Gertrude Monay
   Make:	Honda
   Model:	Accord
   Year:	2002

4. Save the workbook

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:

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

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

1. Click Cell J33
2. Type 5.75% and press Ctrl + Home
    
   
3. Save the workbook

A date is a type of number that measures the number of units, called days, that have occurred since another starting date. To express this number (the date), there are rules you should (must) follow. The rules depend on each language. To know the rules for US English, from the Control Panel, you can open the Regional and Language Options window, click the Customize button, and access the Date tab:

We will come back to some aspects of these rules.

To enter a date in a cell, you use a format. In US English, you use a combination of a month, a day, and a year. These entities must be separated. The separation depends on both you and the way the operating system handles dates.

To express a month, you have a choice between a number and a name. If you decide to use a number, it should (must) be between 1 included and the 12 included. If the month is between 1 and 9, you can precede it with 0 or not. If you want to express a month with a name, you have two choices. You can use a long name or a short name. The long names of month are January, February, March, April, May, June, July, August, September, October, November, and December. Their equivalent short names use three letters each and they are  Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, and Dec respectively.

A day is expressed using a number that starts with 1 and ends with either 30 or 31 depending on the month except for February. The month of February can have either 28 or 29 days depending on something called the leap year. If the day value is between 1 and 9 included, you can enter it with a leading 0 or not.

The year is entered with 2 or 4 digits. If you enter the year with 2 digits, Microsoft Office Excel 2007 may ask you to specify whether you want to use the current century (2000 to 2100) or the previous century (1900 to 1999). To be safe, you should always enter the year with 4 digits.

As mentioned already, when entering a date, you must separate the values of the month, the day, and the year. In US English, the symbol used to separate these entities is the forward slash "/" as you can see from the Date Separator combo box in the Customize Regional Options. An example of entering a date would be 02/18/1998.

In reality, Microsoft Excel is very flexible and understanding with date formats. For example, instead of the forward slash, when entering a date, you can use the dash "-". An example would be 02-18-1998. After entering the date and pressing either Tab or Enter or clicking another cell, if Microsoft Excel can successfully analyze the value and conclude that it is a date, it would convert it to the right format. In this case, the date would be converted to 02/18/1998.

As mentioned already, Microsoft Excel also allows you to use the name of a month. You must follow some rules if you choose this format. If you want to specify the name of a month, use one of the following formats:

• dd-mmm
• dd mmm
• dd-mmmm
• dd mmmm

To use this format, enter the day value using one or two digits. If the day is less than 10, you can enter it with a leading 0. After the day, either leave an empty space or enter -, followed by the short or the long name of the month. Examples are 04-Jan or 16 Apr or 8-December or 26 December. Alternatives to these formats are:

• mmm dd
• mmm-dd
• mmmm dd
• mmmm-dd

In this case, start the date with the month as a short or a long name, followed by either an empty space or -, followed by the day value. These would have the same effect.

After entering the date with only the day and the month, Microsoft Excel would analyze the value. If the application concludes that the value is a date, it would use the current year for that date and convert it to a valid date.

If you want to express the year value, you can use one of the following formats:

• mm-dd-yy
• mm-dd-yyyy

You must start the date with a number that represents the month (a number from 1 to 12). After the month value, enter -. Then type the day value as a number between 1 and 28, 29, 30, or 31 depending on the month and the (leap) year. Follow it with -. End the value with a year in 2 or 4 digits. Here are examples 06-12-08 or 10-08-2006.

You can also use one of the following formats:

This time, enter the day value followed either by an empty space or -. Follow it the short name of the month in the mmm placeholder or the complete name of the month for the mmmm placeholder, followed by either an empty space or -. End the value with the year, using 2 or 4 digits.

As you may know already, in US English, you can start a date with the month. In this case, you can use one of the following formats:

• mmm dd, yy
• mmm dd, yyyy
• mmmm dd, yy
• mmmm dd, yyyy

As seen with the previous formats, mmm represents the short name of a month and mmmm represents the complete name of a month. As mentioned already, the dd day can be expressed with 1 or 2 digits and the single digit can have a leading 0. After the day value, (you must) enter a comma followed by the year either with 2 or 4 digits.

1. Access the CPAR Repair Orders.xlsx workbook
2. Click Cell D5, type November 12, 2008 and, on the Formula Bar, click the Enter button
3. Save the workbook 

As a normal spreadsheet application, Microsoft Excel supports time value. To express a time, you must follow some rules. To check the available rules, you can start the Control Panel and open the Regional Settings Options. Then click the Customize button. The rules for time values are stated in the Time property page:

Microsoft Excel shares a lot of characteristics with window applications: the ability to search through the document for a particular word or phrase, the ability to find a word or group of words and replace it with another word or expression.

A feature unique to Microsoft Excel as a spreadsheet application is the ability to fill out some cells with values that belong to a common series.

Microsoft Excel recognizes series of items so far as they can be clearly identified, either by the common language or by defining them explicitly in a worksheet. Common series include time, dates, weekdays, or months, etc. Whenever Microsoft Excel can identify the content of a cell as being a series, you can use the Fill Handle to copy adjacent values of the series to neighbor cells. If a series is not obvious, you need to clearly define it so the application can recognize the allure you want to use for the series.

To use the AutoFill, first select a cell or the cells that define the series. Position your mouse on the lower right corner of the selected cell or cells, and then drag in the appropriate direction.

1. In Microsoft Excel, press Ctrl + N to start a new empty workbook.
2. In cell B2, type 1st Qtr
3. Position your mouse on the lower right corner of the selected cell, on the squared point. This point is called the Fill Handle. Your mouse will turn into a +
    
   
4. Click on the Fill Handle, hold the mouse down and drag in the right direction until you get to cell E2
    
   
5. Release the mouse. Now you get 1st Qtr, 2nd Qtr, 3rd Qtr, and 4th Qtr
6. In cell B4, type 1st Grade
7. Drag the Fill Handle in the right direction until you get to cell G4. Then release the mouse to see the subsequent grades
8. In cell B6, type 1:00
9. Drag the Fill Handle in the right direction until you get to cell H6. Then release the mouse to see the hours displayed
10. In cell B8, type 9:00 and drag its Fill Handle to cell H8. Notice how Microsoft Excel displays time values in subsequent cells
11. Click Cell B8 and notice the value displayed in the Formula Bar: 9:00:00 AM
12. In cell B10, type 13:00 and drag its Fill Handle to H10. Notice how times get displayed in the other cells.
13. In cell B12, type 9:00 AM and drag its Fill Handle to cell H12. Notice how this time, unlike row 8, Microsoft Excel changed the AM to PM after 11:00 AM:
     
14. In cell B14, type Jan and drag its Fill Handle to cell H14. Notice how the application displays months in subsequent cells
15. In cell B16, type Monday and drag its Fill Handle to cell H16. The computer displays all weekdays
16. In cell B18, type 02/24/2008 and drag its Fill Handle to cell H18. Dates incrementing each day by 1 are displaying in the other cells
17. Click the Sheet2 tab
18. Sometimes Microsoft Excel will not recognize a series with just one value, this happens when the series is not obvious. For example, if you type 1970 in a cell and drag the Fill Handle, the computer would not know whether you want to increment the number by 1, 2, 4, or 10. So you have to specify how the series will be applied. To define a series, type the beginning value, then in the subsequent cell type the incremental values or the value that will be used to define the series.
    In cell B2, type 1970, press Tab
19. In cell C2, type 1974
20. Select cells B2:C2
21. Position the mouse on the Fill handle, which is on the lower right border of cell C2 (because both cells are selected). When the mouse becomes a +, drag the Fill Handle in the right direction up to cell H2. The cell values have been incremented by 1 starting on cell B2
22. In cell B4, type 1
23. In cell C4, type 2
24. In cell B5, type 0
25. In cell C5, type 2
26. In cell B6, type 0
27. In cell C6, type 3
28. In cell B7, type 100
29. In cell C7, type 101
30. In cell B8, type 100
31. In cell C8, type 125
32. Select cells B4:C8
33. Position your mouse on the Fill Handle in the lower right corner of the selected cells (in cell C8). Then drag the Fill Handle to column H and release the mouse:
     
34. Notice that Microsoft Excel could figure out how to handle each series. The first was to increment numbers by 1 starting at 1. The second was asked to list even numbers starting at 0. The third gave a list of odd numbers starting at 0. The third had to count by 1 starting at 100. The fourth was going to find quarter values in a 100 number basis starting at 100
35. In cell B10, type Jan
36. In cell C10, type Apr
37. Select cells B10:C10. Drag the Fill Handle of the selected cells to cell E10. You get the first month of every quarter
38. In cell B12, type 1st Qtr
39. In cell A13, type Jan and drag the Fill Handle of cell A13 to cell C13. That will display the months of the first quarter
40. Select cells A12:C13. Drag the Fill Handle of the selected cells to cell L13

As the AutoFill features provides a quick means of completing cells that can host series data, this feature can be applied in various scenarios.

When creating time sheets, use the weekdays and time periods to complete adjacent cells. In a yearly sales report, Microsoft Excel can recognize series such as months, quarters, and years. In a school’s spreadsheet used to collect students’ grades, a series can be created from 1st Grade, and then dragging the Fill Handle, Microsoft Excel will complete other cells with subsequent class grades.

1. Open the Grier Summer Camp4 workbook
2. Click the Employment Application3 sheet tab
3. Click Cell B19
4. Position the mouse on the lower right corner of the selected cell, on the point, until the mouse pointer becomes a + sign. Click and hold your mouse down, then drag in the right direction to cell H19. That will update the weekdays
5. Save the workbook
6. Access the CPAR1.xlsx workbook and click the Transaction analysis tab
7. Click Cell B6 and position the mouse on the lower-right corner of the cell
8. Click and drag down to Cell B17
9. Save and close the workbook