Some Basic Excel Formulae

We may have used Excel already in our lives before in our day-to-day operation. However, with the rise of data sets and automation via Excel, we need to use Formulae even though these functions may be similar to the ones we do via GUI. When we use formulae, we can generalize the script so that data can be added independently from the operational flow of Excel operations.

CEILING

This function is used in a multiplication operation when we have an integer in a 'Cellname', and we want to equate it to the nearest HIGHEST multiple values.

CEILING(Cellname,Multiple number)

This 'Cellname' can be A4 for eg. And you want it for a prime number in 'Multiple Number' like 13.

So excel will convert the result to the closest multiple of 13.

FLOOR

This is the opposite of the Ceiling function, where we have to convert an integer to the nearest LOWEST multiple values

FLOOR(Cellname,Multiple number)

CONCATENATE

Here we join the text from two different cells into a third cell. For example, you are collecting surveys for the first name and last name differently but want to view their full name also. You can use concatenate at that time.

Below is the formula

 =CONCATENATE(Cell1, " ", Cell2)

There is a second formula as well using the & operator instead of,

=CONCATENATE(Cell1&" "&Cell2)

LEN

This returns the total character limit on a cell

=LEN(CellNumber)

For example, you have a survey where you asked to describe in a google form, an essay of 100 words, so you can easily check how many entries have their entries within this criteria

LEFT, RIGHT, MID

This is to return the values from a predetermined character value in the cell.

=LEFT(Cellnumber,Character value)

All characters before the Character value will be displayed

=RIGHT(Cellnumber,Character value)

All characters after the Character value will be displayed

=MID(Cellnumber,Character value1,Charactere value2)

All character between character value1 and character value2 will be displayed

UPPER, LOWER, PROPER

Probably the most often used command in Excel, especially in my Project :)

=UPPER(Cellnumber)

Converts all characters to uppercase for the said cell number

=LOWER(Cellnumber)

Converts all characters to lowercase for the said cell number

=PROPER(Cellnumber)

Here the first letter in each word will be in uppercase, and all the others will be in lowercase

NOW

I've used this simple command so many times in excel because of its simplicity. It simply returns the Time and Date as per your system

=NOW(CellNumber)

COUNTIF

The function COUNTIF() is used to count the total number of cells within a range that meets the given condition. 

=COUNTIF(CellRange)

ADDRESS:

The Excel ADDRESS function returns the address for a cell based on a given row and column number

=ADDRESS (row number, column number, [absolute number], [a1], [sheet])

Here the rest are self-explanatory, let us focus on the absolute numbers.

The absolute number can have values between 1-4.

1:Absolute Row and column value

2:Absolute row; relative column

3 Relative row; absolute column

4 Relative

Suffice to say, you can remember between 1 and 4.

a1 refers to the reference style

If you use TRUE or omit the a1 argument, the result is shown in A1 styles, such as "B15".

If you use FALSE, the result is shown in R1C1 styles, such as "R15C2"

Sheet value is optional in case you are referring to different sheets.

SUBSTITUTE:

=SUBSTITUTE (text, old_text, new_text, [instance])

text - The text to change.

old_text - The text to replace.

new_text - The text to replace with.

instance - [optional] The instance to replace. If not supplied, all instances are replaced.

This is used to update or change values in cells.

VLOOKUP:

Probably the most important Excel function when trying to analyze data, we use VLOOKUP when trying to search for a particular value in a particular column range

=VLOOKUP(ResultCell,Cell range seperated by :,Column to look in, range lookup)

So if you want your result in Cell A10, that will be your ResultCell, followed by the range of the data set needed, and then the column that the value is stored in. The value in Cell A10 will just be a repeat from the existing value in the dataset.

Note that this can only be a single column. And Finally, range lookup, which basically means if your value has to be approximate ie. 1 or TRUE or exact, 0 or FALSE.

Then excel will return the value stored against your ResultCell value.

In case this is too complicated to understand, you can refer to the below example

HLOOKUP:

This is similar to VLOOKUP, except that this is done for Horizontal rows instead of vertical columns as in the earlier case.

=VLOOKUP(ResultCell,Cell range seperated by :,Column to look in, range lookup)

MATCH AND INDEX:

The MATCH function looks up a value in an array of cells and returns the position # where that value is found.

=MATCH("STRING",CellRange seperated by :)

The INDEX function returns a value from an array of cells based on the provided position #.

=INDEX(Cell Range,Exact Value)

Thus INDEX can be combined with MATCH for a powerful combination as below

=INDEX(Cell Range, MATCH("String", Cell Range))

So for example in the below data,

Your result will be Jack's Salary because the Match command is returning his cell position to Index, which will, in turn, fetch you the salary in Colum D which is also highlighted for the Index command.

A LITTLE MORE...

Now a few more examples with nested commands,

=RIGHT(A2,LEN(A2)-SEARCH("@",A2))

Here you can search for email IDs in column A (say). So we are searching in Column A for email which can be full gmail.com for example

and the right command will display only gmail.com and nothing before @gmail.com

PULLING A HISTOGRAM FROM PIVOT TABLE:

To load data from a CSV file and create a Pivot Table:

1) In the Data tab, select “From Text/CSV”

2) Select the file you want to load and click “Import”

3) In the pop-up window, click “Transform Data”

4) Apply any transformations in the Query Editor (if needed)

5) In the Home tab, select the “Close & Load” dropdown and click on “Close & Load To…”

6) Select “Only Create Connection” and check the “Add this data to the Data Model” option

7) In the Insert Tab, insert a Pivot Table from the Data Model

8) Start dragging fields into your Pivot!

WHEN WE WANT TO CONVERT the COLUMN NUMBER TO TEXT

Here we are trying to convert the numbers into text values

As already discussed above, we will use Substitute and Address commands to first find the cell values, and then using a substitute to change the returned address value to a

=SUBSTITUTE(ADDRESS(3,B3,4),"3","")

Once the cell address is obtained from the Address function as R1C1, we use the substitute Function to replace the row number “3” with a blank string of text (“) leaving only the column letter.

-BasuDeo Dubey