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

ADVANCE FILTER IN EXCEL

-

 Advanced filtering

To filter using search:

Searching for data is a convenient alternative to checking or unchecking data from the list. You can search for data that contains an exact phrase, number, date, or simple fragment. For example, searching for the exact phrase Saris X-10 Laptop will display only Saris X-10 laptops. Searching for the word Saris, however, will display Saris X-10 laptops and any other Saris equipment, including projectors and digital cameras.

  1. 1- From the Data tab, click the Filter command.
  2. 2- Click the drop-down arrow in the column you want to filter. In this example, we'll filter the Equipment Detail column to view only a specific brand.
  3. 3- Enter the data you want to view in the Search box. We'll enter the word Saris to find all Saris brand equipment. The search results will appear automatically.
    Entering a search
  4. 4- Check the boxes next to the data you want to display. We'll display all of the data that includes the brand name Saris.
  5. 5- Click OK. The worksheet will be filtered according to your search term.
    Worksheet filtered using Search

Using advanced text filters

Advanced text filters can be used to display more specific information, such as cells that contain a certain number of characters or data that does not contain a word you specify. In this example, we'll use advanced text filters to hide any equipment that is related to cameras, including digital cameras and camcorders.

  1. 1- From the Data tab, click the Filter command.
  2. 2- Click the drop-down arrow in the column of text you want to filter. In this example, we'll filter the Equipment Detail column to view only certain types of equipment.
  3. 3- Choose Text Filters to open the advanced filtering menu.
  4. 4- Choose a filter. In this example, we will choose Does Not Contain to view data that does not contain the text we specify.
    Selecting a text filter
  5. 5- The Custom AutoFilter dialog box appears.
  6. 6- Enter your text to the right of your filter. In this example, we'll enter cam to view data that does not contain these letters. That will exclude any equipment related to cameras, such as digital cameras, camcorders, camera bags, and the digicam printer.
    Entering filter text
  7. 7- Click OK. The data will be filtered according to the filter you chose and the text you specified.

Using advanced date filters

Advanced date filters can be used to view information from a certain time period, such as last year, next quarter, or between two dates. Excel automatically knows your current date and time, making this tool easy to use. In this example, we'll use advanced date filters to view only the equipment that has been checked out this week.

  1. 1- From the Data tab, click the Filter command.
  2. 2- Click the drop-down arrow in the column of dates you want to filter. In this example, we'll filter the Checked Out column to view only a certain range of dates.
  3. 3- Choose Date Filters to open the advanced filtering menu.
  4. 4- Click a filter. We'll choose This Week to view equipment that has been checked out this week.
    Selecting a date filter
  5. 5- The worksheet will be filtered according to the date filter you chose.
    Worksheet filtered by date
  6. If you're working along with the example file, your results will be different from the images above. If you want, you can change some of the dates so the filter will give more results.

Using advanced number filters

Advanced number filters allow you to manipulate numbered data in different ways. For example, in a worksheet of exam grades you could display the top and bottom numbers to view the highest and lowest scores. In this example, we'll display only certain types of equipment based on the range of ID #s that have been assigned to them.

  1. 1- From the Data tab, click the Filter command.
  2. 2- Click the drop-down arrow in the column of numbers you want to filter. In this example, we'll filter the ID # column to view only a certain range of ID #s.
  3. 3- Choose Number Filters to open the advanced filtering menu.
  4. 4- Choose a filter. In this example, we'll choose Between to view ID #s between the numbers we specify.
    Selecting a number filter
  5. 5- Enter a number to the right of each filter. In this example, we'll view ID #s greater than or equal to 3000 but less than or equal to 4000. This will display ID #s in the 3000-4000 range.
    Entering filter numbers
  6. 6- Click OK. The data will be filtered according to the filter you chose and the numbers you specified.
    Worksheet filtered by number

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