Viewers we have already covered creating
Microsoft Excel, Inserting Database of students in our previous articles. We
have also illustrated AutoSum and SUBTOTAL formula of MS Excel. Proceeding
further to functioning and formulas of Excel, we will here illustrate to other
functions of MS Excel.
To count no of student we have already
inserted serial numbers in 1st column of database yet to avoid mistake in
serial numbering we should set formula for counting actual students studying in
the school. we should apply Count Formula for the this purpose. Following is
procedure for setting count formula.
Count Formula
We set count formula with the aim to
count the number of figure available in the range of cells. Following are the
methods for Count Formula
Method No 1
Place cursor on Cell A122 of the
worksheet and type following formula (=count (A2:A121) and press enter. We will
get our desired figure.
Method No 2
Place cursor on Cell A122 and click on
dropdown options of AutoSum icon at home menu then, click on count numbers.
This command will also give you desired result.
Counting Cells having Text
As we already described that count
formula only counts digits and it does not functions on text. But, to count
texts as we should apply another formula to count cells that have text or
digit. Name of that formula is COUNTA:
COUNTA Formula
COUNTA formula counts any type of data
i.e. text, digits, formula or any other error or information in the cells. This
formula does not count blank cells. Suppose you have to apply this formula
'name column of students’ worksheet. Apply following procedure for COUNTA
Formula:-
Place cursor on B122 and type COUNTA
formula i.e =COUNTA(B2:B21). you will get desired output.
We recommend that you may also set
counting formulas through subtotal formula. This will assist us in filtering
and sorting database as already mentioned. Following is procedure for setting
COUNTA formula through SUBTOTAL:
=SUBTOTAL (3,H2:H121).
Formatting Cells
We can change formatting of these cells
as per our desire as already have mentioned. But, we will describe formatting
of different cells. It is highlighted that these formats do not change the
value but only describe text or number type for easiness of use. We can change
these formatting through different icons shown Home Tab or format cells window
after clicking (CTRL + 1)
General Format
Default formatting of cells which is
applied on data insertion is called general formatting. In case the cells are
not wide enough for showing entire number, General formats round numbers
with decimals. General number formats also use scientific (exponential)
notation for large numbers (12 or more digits).
Comma Formats
Comma Format displays the value of cells
with thousand separators. This format will change format without currency
symbol. It is applied on numbered values. We will apply it after clicking on
comma symbol shown in center on home tab. Which will show figures as
(259,600.00), however we can increase or decrease decimal numbers by clicking
on adjacent icons to the comma.
Currency Symbol
We use this format to display type of
currency. US Dollar ($) will set on after clicking on dollar icon on home tab.
We can change currency type format cells window by pressing (Ctrl + 1). Which
will open format window from click on currency and you can change it from
symbol dropdown list.
Accounting
Accounting formatting is also used for
monetary values however; it aligns currency symbols and decimal points in
column.
Date
Date formats are used to display dates in
different formats or shows numbers dates.
Time
Time formats are used to display dates in
different formats or shows numbers times.
Percentage
This format multiples the cell value with
100 and shows it in %age with the symbol %. We can specify decimal place as per
our desire.
Text
This format treats every value as text
and will not allow applying formula
Special
MS Excel provides us easiness to insert
postal codes, zip codes, mob numbers in different formats like 000-0000000 etc
through this option of formatting
Custom
We can insert numbers in different formats as per our desire. We can set customs formats up to 200 and 250 number formats as per our need.
0 Comments
Please do not enter any spam link in the comments box