CONCATENATE FORMULA

CONCATENATE : -

Concatenate Formula is used for combine data from two or more columns into a single column.

When you concatenate cells in Excel, you combine only the contents of those cells. In other words, Concatenate in Excel is the process of joining two or more values together.

EXAMPLE :-

The simplest CONCATENATE formula to combine the values of cells A1 and B1 as follows

=concatenate(A1,B1)

As in below picture there are some texts in A & B column.

Now we want to combine result in Column C.

So we use CONCATENATE formula

=concatenate(A1,B1)

As per below...

And the Result come.

After that apply this formula to other cells for combine values.

As per below...

HOPE THIS INFORMATION IS HELPFUL FOR YOU.

THANKS

HOW TO USE EXCEL'S VLOOKUP FUNCTION

VLOOK UP - 

VLOOKUP is the main frame formula of Excel. Vlookup is an extremely useful tool.

Basically, VLOOKUP lets you search for specific information in your spreadsheet.

Here we learn how to use VLOOKUP function in easier way, with example. 

The below example is simple. Once you learn how to use VLOOKUP, you will be able to use it with, larger more complex spreadsheets and that's when it will become truly useful.


USE OF VLOOKUP :-

As you see in above picture there are Products & how much Order Place for them. This is the Sheet 1 of spreadsheet. Here are 9 Products Catagory.

Now we are on Sheet 2 of same spreadsheet, You can also check this with another spreadsheet.
As you see in above picture that Product Catogories are same but the locations are random, they are not at same place as which shown in 1st picture.
Now If there are 2 - 4 Product Catagories then you find from another spreadsheets and wrote down the Order Place quantity manually.
But if the Product Catogory is in thousands (,000) then it is possible to find one by one and wrote down the Order place quantity.

So now we use VLOOKUP Function.

As shown in above picture, Where we want to place the value (Here Order Place Quantity), On that cell write =vlookup( as shown in picture or you can take this from formula Bar.

Now, as shown in above picture select the look_value which is are here are products from (B2:B10) cells. 

After selecting lookup_up value we need a table array

For table_array now we are on Original Sheet , here which is Sheet 1, Now after lookup_table put a comma (,) and select table_array which is original sheet from (B2:C10).

After selecting table_array, now we have to put collumn index no.

As shown in above picture we have put 2 for collumn index no. 2 is here for collumn from which vlookup function took the value randomly.

If you have a spreadsheet which also has 3rd collumn and and you want to take value from 3rd collumn then you put here No. 3

After this col_index_no. we have to put range no. which is 0 as shown in above picture.

After this close the ) (bracket)

Here we found how much order we place for DVD which is "4".

In original sheet (Sheet 1) DVD was on 7th Place, and in another sheet (Sheet 2) DVD is on 1st Place. But from the VLOOKUP function it automatically place the value of DVD.

Now go to formula and put Dollar sign ($) as shown in above picture for lookup_value and for table_array.

Dollar ($) sign is for freeze the cell. If we don't freeze the cell and when locations get change the formula change the cell location.

So it is necessary to freeze the cell.

 After then copy the C2 cell and paste it from C2 : C10 here we found all the quantity (Order Place Value) as we have in 1st sheet.



Hope you understand how to use VLOOKUP function,  and this information is useful for you.

Thanks

Excelhelpsu

Hello Friends,

Here we learn how to work on excel, & increase our excel skills for good Job performance & also in studies. We offer a tutorial (Introduction, Basics, Function, Data Analysis) on how to use Excel. Below you can find a complete overview. Want to learn much more Excel? You can find related examples and features. We keep it easy!

Microsoft Excel is a spreadsheet developed by Microsoft. It features calculation, graphing tools, pivot tables and etc.

Open an Excel window as shown in above picture.

Click on Start Button at your task bar then Accessories after then MS OFFICE and then MS EXCEL.

START - ACCESSORIES - MS OFFICE - MS EXCEL

Or

You can search at your task bar button, just type EXCEL.

Now, we are going to start work on excel.

FORMULAS FOR EXCEL : -

• Sum -

Summation, the addition of a sequence of numbers. Addition, the elementary mathematical operations of arithmetic that totals amount of quantities combined.

Sum, an old name for the Finns in East Slavic languages, derived from the work Suomi, "Finland".

A sum is the result of an addition. For example, adding 1, 2, 3, and 4 gives the sum 10, written

As shown in above picture, which cells(quantity) you are going to be add, go to blank cell write =sum(select cells which you want to add)

There is also a shortcut for adding cells

ALT + +(ADD KEY)

• Count - 

1. COUNT IF -

If you need to count the number of cells that contain text (i.e. not numbers, not errors, not blank), you can do so with the COUNTIF function and a wildcard.

COUNTIF counts the number of cells that match the supplied criteria. In this case, the criteria is supplied as thewildcard character "*" which matches any number of text characters.

A few notes:

• The logical values TRUE and FALSE are not counted as text
• Numbers are not counted by "*" unless they are entered as text
• A blank cell that begins with an apostrophe (') will be counted.

A)  To count the number of cells that contain exactly fruit.

B) To count the number of cells that contain exactly fruit + 1 character. A question mark (?) matches exactly one character.

C) To count the number of cells that contain exactly fruit + a series of zero or more characters. An asterisk (*) matches a series of zero or more characters.

D) To count the number of cells that contain fruit in any way. No matter what is before or after fruit, this function finds all the cells that contain fruit in any way.

E) To count the number of cells that contain text.