About Data Validation:
It helps you validate data at the time of input. Suppose you have a column
that records that Rate of a product and the minimum rate is 50 and maximum
is 200. You can discard all inputs that are non numeric or numeric values
below 50 and above 200 as follows.
1. Select the cells that you want to validate data entry.
2. Click Data menu and select Validation from the menu. Alternatively
ALT+D
followed by L. The Data Validation dialog opens with Settings tab by
default.
3. Specify the below values in the dialog box:
Allow: Whole Number
Data: Between
Minimum: 50
Maximum: 200
4. Activate the OK button.
5. Now when your cursor is on one of the selected cells, JAWS would
indicate
that is it a whole number input between 50 and 200. Try entering an
invalid
value in it and see how Microsoft Excel behaves.
You can check out the other options to restrict input in a cell by
exploring
various options in the Allow combo box. You can select List in this combo
box and specify the list of items in the Source edit box. For example to
specify months:
1. Select List in the Allow combo box.
2. Specify comma separated list of months in the Source edit box as shown
below:
January, February, March, April
The cells would contain a combo box with the list of specified items.
About Data Filtering:
Data Filtering helps you hide irrelevant data from the current view. This
is
useful when you have a large amount of data and want to view data that
meets
specific criteria. Microsoft Excel provides two options for filtering:
AutoFilter - This option places a combo box on top of each column with
values in that column. Selecting a specific value in this combo box hides
rows that do not match the selected value in that column. In other words,
uncheck values that you wish to hide from the current view. You can apply
AutoFilter by selecting Data menu > Filter > AutoFilter. Alternatively,
ALT+D followed by F F.
Advanced Filter - This option comes to play when you have a complex
criteria
like all products costing 500 and above with a total sales volume of
3000000
and above. Select Data menu > Filter > Advanced Filter or press ALT+D
followed by F A. The Advanced Filter dialog box asks for the following
options:
List Range: This is the source data to filter For example, $A$1:$P$201.
Criteria Range: This specifies the range of cells where you have entered
the
filter criteria. For example $q$1:$r$2.
Here are the steps to apply an advanced filter to list all products that
cost 500 or more with a sales volume of 3000000 and above.
1. Select the list range for example, $A$1:$J$1000.
2. Place the cursor in any cell other than the above range. I choose K1
for
now. Enter values in cells as shown below:
K1 Cost
K2 >=500
L1 Sales
L2 >=3000000
3. Select Data menu > Filter > Advanced Filter. Alternatively, ALT+D
followed by F A.
4. Specify the following list and criteria ranges in the Advanced Filter
dialog box:
List Range $1$1:$J$1000
Criteria Range $K$1:$L$2
5. Activate the OK button.
Your data would display only records that match the criteria specified in
the cell range K1:L2. If you change any criteria then you need to apply
the
filter once again. The data would not auto refresh.
About password protecting and sending a file through email:
You can apply a password in the Save As dialog box as outlined below:
1. Open the File that needs be password protected.
2. Open the Save As dialog box. This dialog box opens when you save a new
file or select Save As from the File menu.
3. Press ALT+L to open the Tools menu within the dialog box and select
General Options from the menu.
4. Specify the password to open or password to modify or both in the
resulting dialog box and activate the OK button. Note that you need to
type
any password twice in the dialog box for ensuring correct entry.
You can send the file as an attachment to an email. Select File > Send >
Email. A blank email opens in the configured email client with the file
listed in attachments and Subject/Body prepopulated. You can edit these
files and send the email to the desired recipients.
Note that I have specified classic menu shortcuts to keep things simple.
Let
me know if you need more inputs.
Best Regards,
Zujar
An optimist laughs to forget while a pessimist forgets to laugh...
-----Original Message-----
From: [email protected]
[mailto:[email protected]] On Behalf Of tiku raj
Sent: Sunday, May 13, 2012 1:52 PM
To: accessindia
Subject: [AI] How to use data validation and filtering options in Excel
2007
file
Hello friends,
Good afternoon. hope all are fine.
1. I would like to know how to use data validation and filtering
options in Excel 2007 file.
2. How to send Excel 2007 file with password protection through mail.
Please write step by step to understand this functions.
With regards,
Tiku
Search for old postings at:
http://www.mail-archive.com/[email protected]/
To unsubscribe send a message to
[email protected]
with the subject unsubscribe.
To change your subscription to digest mode or make any other changes,
please
visit the list home page at
http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in
Search for old postings at:
http://www.mail-archive.com/[email protected]/
To unsubscribe send a message to
[email protected]
with the subject unsubscribe.
To change your subscription to digest mode or make any other changes,
please visit the list home page at
http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in
