MS Excel, COUNT, COUNTA Formulas and Formatting Methods

 

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.