Use conditional formatting rules in Google Sheets

-
Image
  Cells, rows, or columns can be formatted to change text or background color if they meet certain conditions. For example, if they contain a certain word or a number. On your computer, open a spreadsheet in  Google Sheets . Select the cells you want to apply format rules to. Click  Format     Conditional formatting . A toolbar will open to the right. Create a rule. Single color : Under "Format cells if," choose the condition that you want to trigger the rule. Under "Formatting style, choose what the cell will look like when conditions are met. Color scale : Under "Preview," select the color scale. Then, choose a minimum and maximum value, and an optional midpoint value. To choose the value category, click the Down arrow  . Click  Done . Example A teacher can highlight test scores to see which students scored less than 80%. On your computer, open a spreadsheet in  Google Sheets . Select the test scores. Click  Format     Conditional...
Post a Comment

How to use vlookup in excel

-

 What is Vlookup?

Vlookup stands for "vertical lookup". It is function that makes excel search for a certain value in a column in order to return a value from a different column in the same row.


About the vlookup function:

A vlookup function exists of 4 components:

1. The value you want to look up.

2. The range in which you want to find the value and the return value.

3. The number of the column with in your defined range that contains the return value.

4. 0 or false for an exact match with the value you are looking for 1 or true for an approximate match.

=Vlookup([value],[range],[column number],[false or true])


Example:-

In our example we have a list of fruits, the amount in stock and the current price. We want to find the amount of all fruits quickly in this table. 

First put on the cursor where you want to put the amounts:


In our selected cell we start put the formula =vlookup(:


Then we select the value we want to lookup, in this case it's "fruits" in cell B2:



Then select the range of table array in which we want to find the lookup value and the return value. In this case it is (H7:I11) then fix the table array press F4 one time ($H$7:$I$11):



Then the number of the column with in the defined range in which we want to look for the return value. In this case it is column 2, and "0" or "False" for an exact match with the lookup value "Fruits":


Then press enter, we get the corresponding price from the row that contains value "Fruits" with in the selected table array:
Then drag the amount then automatic fill in amount column by fruits:


In this example we only have a small list of fruits, but imagine you have a long list of data and you wish to use a certain value elsewhere in your spreadsheet. Using Vlookup (and Hlookup) the user only has to change a certain value in one worksheet and it will automatically be changed in all othe relevant places.

But what if we are not certain about the lookup value? This is when the approximate match might be useful.

 

Comments

Post a Comment

Popular posts from this blog

HLOOKUP Function in Excel With Example

-
Image
  HLOOKUP in Excel – With Examples HLOOKUP function  in Excel is a sibling of the VLOOKUP function. The  H  in the HLOOKUP stands for “Horizontal” and hence it is often called Horizontal Lookup. HLOOKUP is a very useful function for creating horizontal lookups, but as most of the tables that we deal with are vertical hence this function is not very popular. The task of the HLOOKUP function is to search for a value in the topmost row of a table, and then return a corresponding value in the same column from a row you specify. Definition and Syntax of HLOOKUP Function in Excel Microsoft Excel defines HLOOKUP as a function that “looks for a value in the top row of a table or array of values and returns the value in the same column from a row you specify”. Syntax of Excel HLOOKUP The syntax of the HLOOKUP function in Excel is as follows: =HLOOKUP(lookup_value,table_array,row_index_number,range_lookup) How to Use HLOOKUP in Excel Now, let’s understand how to use this ...
Read more

Use conditional formatting rules in Google Sheets

-
Image
  Cells, rows, or columns can be formatted to change text or background color if they meet certain conditions. For example, if they contain a certain word or a number. On your computer, open a spreadsheet in  Google Sheets . Select the cells you want to apply format rules to. Click  Format     Conditional formatting . A toolbar will open to the right. Create a rule. Single color : Under "Format cells if," choose the condition that you want to trigger the rule. Under "Formatting style, choose what the cell will look like when conditions are met. Color scale : Under "Preview," select the color scale. Then, choose a minimum and maximum value, and an optional midpoint value. To choose the value category, click the Down arrow  . Click  Done . Example A teacher can highlight test scores to see which students scored less than 80%. On your computer, open a spreadsheet in  Google Sheets . Select the test scores. Click  Format     Conditional...
Read more