ADVANCED EXCEL Archives - CMA INDIA GROUP

Learn Microsoft excel 2016 part II

September 7, 2020 by pnautiyal34

In Today topic we will discuss about Microsoft Excel Worksheet, Home Ribbon etc. RibbonsHome RibbonRibbons are tabs that show different commands with respect to what you wish to do. The Home Ribbonshows basic formatting options. Insert RibbonThe Insert Ribbon allows the insertion of any object. Just click on the named ribbon to see list ofcommands … Read more

Microsoft Excel – introduction

September 11, 2020September 2, 2020 by pnautiyal34

IntroductionMicrosoft Excel 2016 is a powerful spreadsheet application that allows users to produce tables containing calculations and graphs. These can range from simple formulae through to complex functions and mathematical models Windows ConceptsMicrosoft is an application that runs under the Windows graphical user interface. When launched, Microsoft Excel sits in its own window” – the … Read more

PIVOT TABLE in excel

September 11, 2020August 17, 2020 by pnautiyal34

Pivot tables are one of Excel‘s most powerful features. A pivot table allows you to extract the significance from a large, detailed data set. The Basic Difference between Normal table & Pivot table: Normal tables are fixed & Pivot tables are Flexible. Normal Tables give you entire data while pivot table give you summarized data or … Read more

HLOOKUP formula

September 11, 2020August 16, 2020 by pnautiyal34

HLOOKUP stands for Horizontal Lookup and can be used to retrieve information from a table by searching a row for the matching data and outputting from the corresponding column. While VLOOKUP searches for the value in a column, HLOOKUP searches for the value in a row. To perform HLOOKUP, prepared a data as given below … Read more

USE OF V LOOKUP IN ADVANCE EXCEL

September 11, 2020July 9, 2020 by pnautiyal34

V Lookup is also called Vector Lookup. A vector is a series of data that only occupies only one column or row. LOOKUP will look through this Row or Column to find a specific Value. For Example, Column B of Spreadsheet contain figure, and the adjacent Column C contain Text. LOOKUP will search for the requested figure of column B and return the Result of text contain in Column C.

In Example worksheet below, Column B Contains Salary Details of particular company mentioned & In Column C Corresponding Company name. in this case, LOOKUP value will find which company is appropriate under salary figure you desire.

So, here, Lookup_Value is the cell where the salary is entered (B16), Lookup_vector is the salary Column (A2:A13) & result_Vector is company Name column (B2:B13). So, the formula is:

=LOOKUP(B16, A2:A13, B2:B13). See the Image below for reference,

The result is the nearest Value of Salary Mentioned in B16. I.e. INFOSYS.

VLOOKUP

VLOOKUP cannot be used on a vector (i.e. On a single, row or Column), but on an array (Multiple Columns or Rows). VLOOKUP searches for the value Vertically down the first Columns of an array.

In case of VLOOKUP, the data we want on current sheet is to be found in another sheet with a single Common data on each sheet. For example, a spreadsheet contains salary sheet of a company, you need to prepare data only with employee name & salary in Hand of each employee.

According to formula syntax, first thing is VALUE – which is common in both sheets i.e. Employee Name, shown in picture below:

Second, after selecting value, select Table you want to grab data from i.e. from another sheet where the common data lies.

Third, Col_index refers to column number, i.e. salary is in second column, select 2 in this reference.

Fourth, range_lookup, select 1 if you want to appropriate Match (True) or 0 for Exact Match (False).

Then final result, will be salary of Referred cell.

DOUBLE VLOOKUP

One of the difficult tasks to perform & one of the important tools of Excel.

We have 2 different types of data in 2 separate sheets. We need to collaborate this data into a new sheet (Sheet 3). One sheet contains Employee name & Laptop Brands & second sheet Contains Laptop details with their Respective Prices. I want a separate sheet, where in front of Employee Name prices of laptop to be mentioned. So, we used double VLOOKUP Formula

Sheet 1 Sheet 2 Sheet 3

Without any hassle we will use Double VLOOKUP Function, but do concentrate.

Step 1: Use V LOOKUP formula where employee name is common & use it the way, we have studied earlier.

In step above, if you will press enter it will provide you Laptop Brand Name only.

In step 2, In current sheet, we need to refer to sheet 2 to get price of Laptops. So, again write VLOOKUP after equal to sign as mentioned below:

Step 3: after bracket ends, refer to sheet 2, & use same Technique to get your values as shown below:

Result will be displayed:

Thank You
I hope you had learnt how to use V Lookup, If you liked it, let us know.
If any Query, you can Contact us or Login to get more updates…!!

For Count and SUM Formula please click on below mentioned links
https://cmaindiagroup.com/how-to-use-count-formula-in-advance-excel/
https://cmaindiagroup.com/sum-formula-advance-excel/

how to use count formula in advance excel

September 11, 2020July 1, 2020 by pnautiyal34

Let’s learn about mostly used function (HOW TO USE COUNT FORMULA IN ADVANCE EXCEL) COUNT formula today, COUNT Function is basically used to calculate Numerical Values & Alphabetical Values by Applying Different version of Formulas. The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. Let’s start with Syntax to be used in COUNT Formula:

Some Tips to Use COUNT Formula:
Arguments that are numbers, dates, or a text representation can be counted. Empty cells, can be counted with COUNTBLANK Function. If you want to count logical values, text, or error values, use the COUNTA function. If you want to count only numbers that meet certain criteria, use the COUNTIF function or the COUNTIFS function.
Let us Discuss what we are going to Learn today
FORMULA TO BE LEARNED TODAY
1. COUNT
2 . COUNTA
3 . COUNTBLANK
4 . COUNTIF
5 . COUNTIFS

FORMULA NO 1: COUNT
To Learn how to apply COUNT Formula, first we need to prepare data, Here we have prepared Sales data of Different brands of Mobiles & LED, as Shown in Figure below:

COUNT formula used to count number of items you have in your list, so if you want to calculate number of items you have entered in your list, you can simply use COUNT formula
First Step is to click on the blank cell where you want your answer, then apply formula =COUNT(Cell range), Select cells you want to Calculate. Reference Shared in Pic below, you can see it has not calculated any alphabets or Blank Cells, Only Numerical value is counted.

FORMULA 2: COUNTA
This function is widely used to calculate count of Numerical as well as alphabetical Values in Selected cells.
You have to select the data you want to count then apply formula =COUNTA(Cell range), it will calculate all values Excluding empty Cells. Example given in picture below:

FORMULA 3: COUNTBLANK
In the formula above, it counts only Blank cells

FORMULA 4: COUNTIF
COUNTIF is used to count the number of cells that meet a certain Criteria:

To count the number of times a particular city appears in a customer list.
For single criteria – We use the above formula
=COUNTIF(Where you want to look & what you want to look for?)
In the example below, lets filter Count of LED in Product list.
First Apply formula =COUNTIF,

Select Range of Product, & then select the Product type you want to Count.

FORMULA 5: COUNTIFS
The COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met.
For Multiple criteria – we use COUNTIFS

=COUNTIFS(range of first criteria, cell reference from first criteria, Range from Second Criteria, cell reference from Second Criteria). If we need to count particular Product under certain Brand name, we will use COUNTIF Function.
In Example in picture below, we will Count Number of Mobiles we have Under Nokia Brand.

Now i hope you will Understand HOW TO USE COUNT FORMULA IN ADVANCE EXCEL. and if your really liked it,

Miss Priyanka Dhiman

How to use sum if formula in ADVANCEd EXCEL

September 11, 2020June 26, 2020 by pnautiyal34

SUM IF FORMULA IN ADVANCED EXCEL – Today, we are going to learn SUM IF in Advance Excel features, so let’s start with basics of Formula. Lets Start with SUM Formula first . SUM formula is used to Add numerical value to a particular cell Horizontally or Vertically i.e. Row wise or column wise. But … Read more