Unleashing the Power of Excel Functions

18 Nov 2017
Excel Functions

MS Excel is probably the most important and commonly used software tool in the business world ever, because of rich set of excel functions.

You can do almost anything with it that entails calculations, lists, planning, forecasting and querying data from external data sources. The flexibility of the workbook enables us to quickly adapt the worksheets to meet our demands. Plenty of third party add-ins for all sorts of specialist requirements is an added plus.

In this article we will try to cover all the important Excel Functions, which can be used to simplify day-to-day operations in Excel.

Basic Excel Functions:

COUNT

What Does It Do ?

This function counts the number of numeric entries in a list. It will ignore blanks , texts and errors.

Syntax

=COUNT(Range1,Range2,Range3... through to Range30)

----------------------------------------------------------------------------------

COUNTA

What Does It Do ?

This function counts the number of numeric or text entries in a list. It will ignore blanks.

Syntax

=COUNTA(Range1,Range2,Range3... through to Range30)

-----------------------------------------------------------------------------------

COUNTIF

What Does It Do ?

This function counts the number of items which match criteria set by the user.

Syntax

=COUNTIF(RangeOfThingsToBeCounted,CriteriaToBeMatched)

The criteria can be typed in any of the  following ways.          
To match a specific number type the number, such as =COUNTIF(A1:A5,100)     
To match a piece of text type the text in quotes, such as =COUNTIF(A1:A5,"Hello")     
To match using operators surround the expression with quotes, such as =COUNTIF(A1:A5,">100")
-------------------------------------------------------------------------------------------------

CONCATENATE

What Does It Do ?

This function joins separate pieces of text into one item.

Syntax

 =CONCATENATE(Text1,Text2,Text3...Text30)

Up to thirty pieces of text can be joined. You can achieve the same result using "&" operator.

------------------------------------------------------------------------------------------------

DATE

What Does It Do ?

This function creates a real date by using three normal numbers typed into separate cells.

Syntax

 =DATE(year,month,day)

Formatting
The result will normally be displayed in the dd/mm/yy format.
By using the Format,Cells,Number,Date command the format can be changed.

------------------------------------------------------------------------------------------------

IF

What Does It Do?
This function tests a condition.
If the condition is met it is considered to be TRUE.
If the condition is not met it is considered as FALSE.
Depending upon the result, one of two actions will be carried out.
 

Using IF

Syntax

=IF(Condition,ActionIfTrue,ActionIfFalse)

The Condition is usually a test of two cells, such as A1=A2.
The ActionIfTrue and ActionIfFalse can be numbers, text or calculations.

------------------------------------------------------------------------------------------------

HLOOKUP

What Does It Do ?
This function scans across the column headings at the top of a table to find a specified item.
When the item is found, it then scans down the column to pick a cell entry.

Excel Functions Using HLookup
 

Syntax

=HLOOKUP(ItemToFind,RangeToLookIn,RowToPickFrom,SortedOrUnsorted)

The ItemToFind is a single item specified by the user.
The RangeToLookIn is the range of data with the column headings at the top.
The RowToPickFrom is how far down the column the function should look to pick from.
The Sorted/Unsorted is whether the column headings are sorted. TRUE for yes, FALSE for no.

-----------------------------------------------------------------------------------------------

VLOOKUP

What Does It Do ?
This function scans down the row headings at the side of a table to find a specified item.
When the item is found, it then scans across to pick a cell entry.

Using Vlookup

Syntax

=VLOOKUP(ItemToFind,RangeToLookIn,ColumnToPickFrom,SortedOrUnsorted)

The ItemToFind is a single item specified by the user.
The RangeToLookIn is the range of data with the row headings at the left hand side.
The ColumnToPickFrom is how far across the table the function should look to pick from.
The Sorted/Unsorted is whether the column headings are sorted. TRUE for yes, FALSE for no.

------------------------------------------------------------------------------------------------

TODAY

What Does It Do?
Use this to show the current date.

Syntax

=TODAY()

Formatting
The result will normally be displayed using the DD-MMM-YY format.

Example
The following example shows how the Today function is used to calculate the number
of days since a particular day.
Today

------------------------------------------------------------------------------------------------

SUBSTITUTE

What Does It Do ?
This function replaces a specified piece of text with a different piece of text.
It can either replace all occurrences of the text, or a specific instance.
The function is case sensitive.

Syntax

=SUBSTITUTE(OriginalText,TextToRemove,TextToInsert,InstanceToUse)

The InstanceToUse is optional, if it is omitted all instances will be substituted.

Note
To cope with upper or lower case in the substitution you can use other text functions
such as =UPPER(), =LOWER() or =PROPER() to ensure that the substitution will take place.

Excel Function Substitute

There are hundreds of excel functions and other advanced features like VBA , charts, pivots making it even more powerful. Keep watching this section for more articles on Excel, Excel functions and its advanced features in the forthcoming posts.