Fundamentals of Numeric Data Entry
|
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:
|
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:
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.
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:
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:
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:
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:
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:
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:
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:
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:
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.
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.
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.
|
Practical Learning:
Entering a Numeric Value as Text
.
No comments:
Post a Comment