Skip to main content

Basics of Excel functions and relations.

Through this post, I would like to introduce the Built in Functions of Microsoft Excel to a beginner. Let me put some excel functions that could be of use to those who what to make a start.

Before touching upon some of the functions, some introductions. The first thing that I would recommend for a beginner is to browse through the list of functions and its uses. At this stage, forget how it to be used, but keep an eye one what all you can do using functions. For that, go to INSERT -> FUNCTIONS. You will see the following dialog. Here you can either select ALL, or a specific category like TEXT. Now select a function and at the bottom, you will see what it will do. To find more detailed explanation, click HELP ON THIS FUNCTION.

Note: Click the cell with Function(s) and press F2 and you will be able to see the references in different colors.

Some simple functions:

1: Sum.
To find sum of a few numbers.
Eg: SUM(A1, D5, G1:G15)

2: Min.
Minimum of a list of numbers

3. Max.
Maximum of a list of numbers

Some more functions:

4: Concatenate:
Joins several strings into one single string.
Here each string can be a reference to another cell (like A5) or a manual one (like "Hello"). Note that the manual entries should be encapsulated in double-quotes.

NOTE: there is a simpler way to achieve this. just type & in between each string entry. So..
=Concatenate(A5," is the son of ", A10) is equivalent to
=A5&" is the son of "&A10

5: Left, Mid and Right
Left function allows you to get a specified number of characters from the left side.
Right function allows you to get a specified number of characters from the right side.
Mid function allows you to get a specified number of characters from a specified starting position.

Assume A5 => "I am not a fool"
Assume A6 => "Joe"

Left(A5, 5) => "I am "
Left(A5, 5)&": "&A6 => "I am : Joe"

This following is an exercise for you.
Left(A5, 5)&Right(A5,6)

6: Substitute
Replaces an old text with a new one.

Assume you have to change a part number format.
What have is 75423.5638.6453 and what you want is 75423-5638-6453. (Replace all .s with -s)
=Substitute(75423.5638.6453, ".", "-") => 75423-5638-6453

=Substitute(75423.5638.6453, ".", "") => 7542356386453

7: Replace
Replaces a text with another one.
The difference from Substitute is that here you are specifying the positions of the text to be replaced.

8: Upper, Lower and Proper.
Changes the case of the string to the respective ones.

Upper(AbcD) => "ABCD"

Some Special Functions:

8: VLookUp
Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table.

VLookUp(Value In Column 1, Range of the Table, The Column number of the data to be taken)
based the feed value, the row will be selected and the data from the correct column is extracted.

VLookUp(0.675, A1:C10, 3) => 250


A15 => 0.946
A16 => 2

VLookUp(A15, A1:C10, A16) => 2.17
is the viscosity for a density of 0.946.

NOTE: Alternately, you can use HLookUp to fix the column and get the data from row.

9: IF
Gets a value based on whether a condition is true or false.

If(Condition, command if condition is true, command if condition is false).

A1 => 10
A2 => First
A3 => Second

If(A1>5, A2, A3) => First
If(A1<=5, A2, A3) => Second

10: SumIf
Adds the cells based on a given criteria.

SumIf(Criteria Range, Criteria, Sum Range)
if Sum Range is not specified, Criteria Range is taken as Sum Range.

SumIf((A2:A5, ">160000", B2:B5) => 63,000
Add cells in column B if corresponding cell in Column A >160000.
SumIf((A2:A5, ">160000") => 900,000
Add cells in column A if corresponding cell in Column A >160000.

NOTE: A similar function COUNTIF counts based on condition.

11: ISBlank
Checks if a cell is blank or not.

A1 = ""
A2 = 10

IsBlank(A1) = TRUE
IsBlank(A2) = FALSE

If( IsBlank(A1), "", 100 / A1) => ""
If( IsBlank(A2), "", 100 / A2) => 10

What if you don't do that check!!!
100/A1 => #DIV/0!
100/A2 => 10.

See the first one returns an error, which you normally don't want to happen. So the check helps to prevent that.

Enough of the functions. You can browse the remaining yourself.
Couple of other related points also.

Relative motion of references.

This is a very interesting aspect of how you can use the functions effectively. And interestingly, less commonly used.
The normal references that you take move with the copying. I will explain.

let B2 is A1 + A2 + B1
If you copy B2 to B3, the relation will automatically be changed as A2+A3+B2.
If you copy B2 to C2, the relation will automatically be changed as B1+B2+C1.
This is the normal way it functions. If copy one cell to the right, all references move one cell to the right. If you copy 2 cells to the bottom, all references move 2 cells to the bottom as well.

You can control this movement. In the sense, you can restrict some or all the movement by putting a $symbol.

a reference that is A1 is free to move in both directions.
a reference that is $A$1 is prevented from moving in both directions.
a reference that is $A1 is allowed to move up or down but not right or left.
a reference that is A$1 is allowed to move right or left but not up or down.

I will take the original example again.
let B2 is $A$1 + $A2 + B$1

copy that to C2 and you will get this.
$A$1 + $A2 + C$1

copy that to B3 and you will get this.
$A$1 + $A3 + B$1

If Row-1 and Column-A are the heading elements, this relation will give you the sum of the heading cells + A1.

Pressing F4 repeatedly will cycle through each type.

I hope that this will allow you to jump start your excel learning.
Excel is a vast ocean. But don't let that bother you. Keep it simple, Make one step at a time and don't forget to make one new step each day. After sometime, you will be amused at what you are able to do.

Let me know if you have any comments / Qns.


Aashish said…
Hello Sir,

I have been calling you now and then. Any ways..... all the best for your new assignements,

Hope you changed for good only.

Please stay in touch.

(i though this is the best way to contact you)

Popular posts from this blog

TreeView Structure in Excel VBA

TreeView is extreamly useful in specific cases but it can be bit tricky sometimes to implement.  Last Few days I was working on a TreeView Structure and thought I will share the knowledge I gained...  This post takes you through the basic operations to create and operate a TreeView. It will be like the Folder tree window of the windows explorer.  Moreover, you can have it dynamically updated based on the data in excel.... Here we go..

Controlling LED through Octolapse for 3D print timelapse

Today's post is about one of the 3D Printing adaptation that I learned. This is specifically about how to control an LED light automatically through OctoLapse so that it is switched on only when the timelapse photo is taken. Just as a background, I have been learning to use 3D printer and its customizations options for a few months now and was a lot of learning since then. The best thing was OctoPi, a platform for controlling the 3D printer and the many plugin options that are developed by the community.  Most of them are like open source. Today we will discuss specifically about an adaptation to one such plugin, OctoLapse. OctoLapse is the plugin for taking timelapse photos, with many options for further customisations. One of the best timelapse method is taking photo after each layer is complete, by moving the head out of the way. You can use standard Pi Camera or a DSLR, which is great to get good resolution videos. One small flash back before we dive into the topic.  I keep the

PTC Creo | my Mapkeys for free

I have created a list of frequently used mapkey shortcuts for the PTC Pro Engineer Creo. This is the macro equivalent in creo.  I am copying them below.If you need them, copy paste the content to the "" file in your startup folder. My favourites are highlited and greatly improves the workflow.. For ex, to reach MEASURE. need to go to another menu and click.  Instead, maypkey from any selected menu on the ribbon will work.. Thats wonderful to me... Also, Edit Sketch (ES) is overloaded and will work for Extrusion, Revelution, Sweep etc.. So is aa, pp, zz..  really helps me a lot.. Hope you will start using them as well and get benefited! Let me know in comments, your feedback and issues.... Sketch View           > sv Show and Erase        > se Working Directory     > wd Hiddel Line View      > hi Close (quit) Window   > qw Measure               > mm Erase Session         > ee Insert Mode @ Sel     > is Cancel Insert Mode    > ic edit Sketch