hyper link|make Data validation in excel and massage|filter

How to show or hide formula bar, gridlines, column & row headlines?

View > show/hide > click on formula bar, gridlines, headlines. Or, Page layout > sheet option > click on gridlines view/click on headlines view

How to apply any theme in page background?

Design > Themes > Themes group > click on theme style

How to link excel chart to another software?

Select your data > home > click on copy icon > open another software (example : ms word) > home > past > past special > past link > ms excel work sheet object > ok.

How to remove hyper link?

Select the text (“click to see chart” & “click to see comment”) right click on it > remove hyperlink Or, Insert > hyperlink > remove hyperlink.

Data validation

Data validation allows you to control exactly what a user can enter into a cell. In our example, we can use data validation to ensure that the user chooses one of the three possible shipping options. To make things even easier, we can insert a drop-down list of the possible options.

This kind of data validation allows you to build a powerful, fool-proof spreadsheet. Since users won't have to type in data manually, the spreadsheet will be faster to use, and there's a much lower chance that someone can introduce an error. Depending on what spreadsheet program you're using, the process of adding data validation will vary. We'll show you how to add data validation in Microsoft Excel and Google Sheets.

Data validation in Excel

Since we already have a list of shipping options in the Shipping worksheet, we're going to tell Excel to use the data in that list to control which values a user can select. But before we do this, we'll actually need to name the cell range first. Naming cell ranges is one way to keep track of important cell ranges in your spreadsheet.

Notes:

How to create validation & massage?

a)type your data table > data > data tools > data validation >

b)settings > allow > whole number > data > set to between/minimum/maximum etc.

c)input massage (“hey; don’t make a mistake”)

d)error alert > style > stop > error massage > type value minimum number to maximum number > ok.

Serial Number	Name	Address	Salary
1
2
3
4

Remove data validation

You can remove validation on a cell in Excel 2007 so that users are no longer required to enter information or information formatted in specific way.

1. Select the cells where you no longer want to validate data.
   To remove data validation from all similar cells or from all cells that have validation on a worksheet, find the cells.
   How to find cells with data validation
   Do one of the following:
   Find all cells with data validation    
   1. On the Home tab, in the Editing group, click the arrow next to Find & Select, and then click Go To Special.
      
   2. Click Data Validation, and then click All.
      Find cells that match certain data validation settings    
   3. Click a cell that has the data validation settings for which you want to find matches.
   4. On the Home tab, in the Editing group, click the arrow next to Find & Select, and then click Go To Special.
      
   5. Click Data Validation, and then click Same.
2. On the Data tab, in the Data Tools group, click Data Validation.
   
   Issue: The Validation command is unavailable.
   An Excel table may be linked to a SharePoint site     You cannot remove data validation from an Excel table that is linked to a SharePoint site. To remove data validation, you must unlink the Excel table or convert the Excel table to a range.
   You may be currently entering data     The Data Validation command is not available on the Data tab while you are entering data in a cell. To finish entering, press ENTER or ESC.
   The worksheet might be protected or shared     You cannot change data validation settings if your workbook is shared or protected. For more information about how to stop sharing, or to remove protection from a workbook, see Use a shared workbook to collaborate and Change or remove protection of worksheet or workbook elements.
3. Do one of the following:
   1. If you are prompted to erase the current settings and continue, click OK, and then click Cancel.
4. If the Data Validation dialog box appears, click the Settings tab, and then click Clear All.

or, Notes by :

How to clean data validation?

Select your data > data tools > data validation > clear all > ok.

How to filter an Excel

If you're using Excel to schedule events for any projects, chances are you filter those records by specific criteria. If you're using Excel 2007 or 2010, you're in luck, because there are several built-in date filters. In this post, we'll review a simple filter for dates falling within the current week. By current, we mean a Sunday through Saturday week. Then, we'll show you an advanced filter. (Neither of these techniques work in Excel 2003.)

Simple filter

The simple data set shown below comprises two date columns, a start and an end date. Some occur in the same week, some don't. Your first hurdle is to decide whether you're filtering by the start or end date. Neither choice is wrong; it just depends on your needs. To illustrate the simple filter, let's find all the records where the start date falls within the current week, as follows:

1. First, select the entire data range by clicking in any cell in the data range and pressing [Ctrl]+[Shift]+8.
2. Next, apply a simple filter by clicking the Data tab and then clicking Filter in the Sort & Filter group.
3. Click the StartDate column's dropdown filter and choose Date Filters.
4. Then, choose This Week from the resulting submenu.

The filtered group displays only those records where the StartDate value falls within the current week. Before, the filtered records reflect a current day of June 24, 2012 through June 30, 2012. You probably noticed that there are a number of date filters. They're convenient and easy to use-even for users with limited skills. As you might expect, you could apply the same filter to the EndDate column and get a different result (or not, depending on the date values).

To clear the filter, simply click Clear in the Sort & Filter group.

Advanced filter

Now, let's suppose that you need to display only those records where both the start and end date fall within the current week. There are probably a half dozen ways to accomplish this task, but I recommend two helper columns and the WEEKNUM() function. This simple function uses the following form to return the week number:

WEEKNUM(datetext)

Where datetext is a date string. For instance, the function WEEKNUM("6/27/2012") returns the value 26.

To adjust this simple data set for a more advanced filter, add two helper columns based on the following functions in cells D6 and E6, respectively:

=WEEKNUM(B6)

=WEEKNUM(C6)

Then, copy the functions to the remaining cells in each column.

The WEEKNUM() function in column D returns the week number for each start date. Similarly, the function in column E returns the week number for each end date. As you can see, the week numbers for the start and end dates aren't always the same for each record; sometimes the time period extends beyond the current week.

Next, copy the header cells to create a criteria range (A1:E1). Then, enter the following criteria expression into both D2 and E2:

=WEEKNUM(TODAY())

Now you're ready to apply the advanced filter:

1. Click the Data tab.
2. In the Sort & Filter group, click Advanced Filter.
3. In the List Range control, enter A5:E13.
4. In the Criteria Range control, enter A1:E2.
5. Click OK.

If you want to filter for records where either the start or end date falls within the current week, move one of the criteria expressions to row 3, and update the advanced filter's criteria range, accordingly. Placing the criteria in two different rows allows the filter to find records that satisfy either criteria rather than both.

or, step by step following:

How to find out the record by filter?

Select the particular cell area > data > sort & filter > click on filter icon > click on any field drop down key > click on the particular field name (Name/address/salary etc). Or, Select the particular cell area > home > editing > short & filter > filter.