Computer Data processing in excel

Std. 11                    Chapter -9

Working with Database Data using Microsoft Excel

1. DATA VALIDATION

2. CREATING DATA LIST

3. DATA FORMS

 Delete And Restore buttons in Data forms.

 Finding Records using Criteria.

4. TYPES OF CRITERIA

5. ADVANCED FILTER

  Set up the Criteria Range. (Optional) 
  Set up Extra Range. (Optional)

  Apply the filter.
  Extracting records using And and Or Criteria.

6. USING WILD CARD CHARACTERS.

              The * Wild card.

                 The ? Wild card.

7. ADVANCED FILTER AND FUNCTIONS OF EXCEL.

8. GROUPING AND ADDING SUBTOTALS TO DATA.

9. PIVOT TABLE AND CHARTS.

 Std. 11                          Computer

Chapter – 9        Working With Database Data using Microsoft Excel

	A	B	C	D	E	F	G
2	Roll No	First Name	Middle Name	Last Name	Boy/Girl	Stream	% Percentage
3	1	Puja	S	Sethia	G	Arts	99
4	2	Varun	U	Gangele	G	Commerce	82
5	3	Zakiya	S	Malek	G	Commerce	56
6	4	Harshit	A	Jain	B	Science	74
7	5	Aryan	V	Nair	B	Science	62
8	6	Varun	J	Pathak	B	Science	89
9	7	Prachi	M	Thakkar	G	Arts	80
10	8	Amit	N	Surena	B	Science	67
11	9	Suvidha	M	Arolkar	G	Science	79
12	10	Nihal	P	Shah	B	Arts	45

Table – 9.1

1. DATA VALIDATION :-

Select                    Cell C3..     OR    Cell range C3.. C12

Title Bar –                      DATA

Dropdown Menu -                     VALIDATION

Dialog Box –        

Setting –

                             Allow – Text length

                             Data –    Equal to

                              Length – 1

                  

                   Input Message –

                             Title –                   Middle Initial

                             Input Message –    Enter only Middle Initial

                   Error Alert  -

                             Style –                  Stop

                             Title –                   Incorrect

                             Error Message –    Only single Character is Allowed

                       

Ok

2. CREATING DATA LIST

Select – Sheet 2

	A
1	Arts
2	Commerce
3	Science

  

Select –          Cell A1.. A3      

Select  –         Title Bar –                    INSERT

Dropdown Menu -        NAME

                                                           DEFINE

                                                           Stream

Select - Sheet 1

Title Bar -                              DATA

              Dropdown Menu -                 VALIDATION

Dialog Box -

Setting –

Allow –      LIST

              Source –    =Stream

Ok

3.  Data Forms :-

Title Bar -              DATA

Dropdown menu -     FORM

Close

4. Types of Criteria -

	Operator	Meaning
1	>	Greater than
2	<	Less than
3	>=	Greater than or equal to
4	<=	Less than or equal to
5	=	Equal to
6	<>	Not equal to
7	*	Wild card character

Operators used for specifying criteria in data form.

5. ADVANCED FILTER

	H	I	J	K
1	Roll NO	First Name	Last Name	Stream
2	1
3	5
4	8

Specifying column to be extracted

Select any cell in the database

Title Bar –               DATA

Dropdown Menu – FILTER

                               ADVANCED FILTER

Dialog Box -

                      Copy to another location

                               List range –                   $A$1:$G$11

                               Criteria Range –                     $H$1:$H$4

                               Copy to -                       $I$1:$K$1

Ok

	H	I	J	K
1	Roll NO	First Name	Last Name	Stream
2	1	Puja	Sethia	Arts
3	5	Aryan	Nair	Science
4	8	Amit	Surena	Science

H
% age in 10th Std.
>70

Select any cell in the database

Title Bar –               DATA

Dropdown Menu – FILTER

                               ADVANCED FILTER

Dialog Box -

                      Copy to another location

                               List range –                   $A$1:$G$11

                               Criteria Range –                     $H$1:$H$4

                               Copy to -                       $I$1:$O$1

Ok

Output

	I	J	K	L	M	N	O
1	Roll NO	First Name	Middle Name	Last Name	Boy/Girl	Stream	%age in 10th Std.
2	1	Puja	S	Sethia	G	Arts	99
3	2	Varun	U	Gangele	G	Commerce	82
4	4	Harshit	A	Jain	B	Science	74
5	6	Varun	J	Pathak	B	Science	89
6	7	Prachi	M	Thakkar	G	Arts	80
7	9	Suvidha	M	Arolkar	G	Science	79

Extracting Records using AND and OR criteria

H	I
Stream	%age in 10th Std.
Science	>80

Specifying AND criteria

Select any cell in the database

Title Bar –               DATA

Dropdown Menu – FILTER

                               ADVANCED FILTER

Dialog Box -

                       Copy to another location

                               List range –                   $A$1:$G$11

                               Criteria Range –                     $H$1:$I$2

                               Copy to -                       $J$1:$P$1

Ok

Output

	I	J	K	L	M	N	O
1	Roll NO	First Name	Middle Name	Last Name	Boy/Girl	Stream	%age in 10th Std.
2	6	Varun	J	Pathak	B	Science	89

Extracting Records using AND and OR criteria

H	I
Stream	%age in 10th Std.
Science
	>80

Specifying OR criteria

Select any cell in the database

Title Bar –               DATA

Dropdown Menu – FILTER

                               ADVANCED FILTER

Dialog Box -

                      Copy to another location

                               List range –                   $A$1:$G$11

                               Criteria Range –                     $H$1:$I$3

                               Copy to -                       $J$1:$P$1

Ok

Output

	J	K	L	M	N	O	P
1	Roll NO	First Name	Middle Name	Last Name	Boy/Girl	Stream	%age in 10th Std.
2	1	Puja	S	Sethia	G	Arts	99
3	2	Varun	U	Gangele	G	Commerce	82
4	4	Harshit	A	Jain	B	Science	74
5	5	Aryan	V	Nair	B	Science	62
6	6	Varun	J	Pathak	B	Science	89
7	8	Amit	N	Surena	B	Science	67

H	I
Stream	%age in 10th Std.
Science	>80
Commerce	>75
Arts	>65

Specifying   combined AND & OR criteria

Select any cell in the database

Title Bar –               DATA

Dropdown Menu – FILTER

                               ADVANCED FILTER

Dialog Box -

                      Copy to another location

                               List range –                   $A$1:$G$11

                               Criteria Range –                     $H$1:$I$4

                               Copy to -                       $J$1:$P$1

Ok

Output

	J	K	L	M	N	O	P
1	Roll NO	First Name	Middle Name	Last Name	Boy/Girl	Stream	%age in 10th Std.
2	1	Puja	S	Sethia	G	Arts	99
3	2	Varun	U	Gangele	G	Commerce	82
4	6	Varun	J	Pathak	B	Science	89
5	7	Prachi	M	Thakkar	G	Arts	80

Advanced Filter and functions of Excel

Select cell H3.

Function Bar -       = G3 >= AVERAGE($G$3:$G$12)

cell H3..      TRUE

Title Bar –                       DATA

Dropdown Menu –          FILTER

                                      ADVANCED FILTER

Dialog Box -

                                       Copy to another location

                                       List range –                   $A$1:$G$11

                                       Criteria Range –                     $H$1:$H$2

                                       Copy to -                       $I$1:$O$1

Ok

Output

	I	J	K	L	M	N	O
1	Roll NO	First Name	Middle Name	Last Name	Boy/Girl	Stream	%age in 10th Std.
2	1	Puja	S	Sethia	G	Arts	99
3	2	Varun	U	Gangele	G	Commerce	82
4	4	Harshit	A	Jain	B	Science	74
5	6	Varun	J	Pathak	B	Science	89
6	7	Prachi	M	Thakkar	G	Arts	80
7	9	Suvidha	M	Arolkar	G	Science	79

Grouping and Adding Subtotals to data

	A	B	C	D	E	F	G
1	First Name	Car Model	Jan	Feb	March	April	May
2	Amit Jain	M 800	2	1	4	5	3
3	Rahul Saraf	M 800	4	7	8	5	3
4	Rahul Saraf	M Auto	4	5	2	3	2
5	Shalni Gangele	M Omni	1	1	1	1	1
6	Zakiya Malek	M Omni	2	5	7	10	7
7	Shalini Gangele	M Swift	4	5	3	2	5
8	Amit Jain	M W R	4	2	5	3	6
9	Zakiya Malek	M zen	1	2	4	5	2

                                      Sample data for Subtotals

Select any cell in the database.

Title Bar –                       DATA

Dropdown Menu -                    SUBTOTALS

Dialog box-

                   At each Change in: -        Car Model

                   Use function : -               Sum

                   Add Subtotal to :-             Jan,

Feb,

March,

April,

May

          Page break between Groups

Ok

                             Summary report generated using Subtotal

Title bar – DATA

Dropdown menu – SUBTOTALS.

                             Remove All

Pivot table and Charts

	A	B	C	D
1	Month	Subject	Student	Marks
2	January	Eng	Harshit	87
3	January	Maths	Harshit	65
4	January	Science	Harshit	58
5	January	Computer	Harshit	89
6	January	S S	Harshit	81
7	January	Gujarati	Harshit	62
8	Febuary	Eng	Harshit	51
9	February	Maths	Harshit	72
10	February	Science	Harshit	89
11	February	Computer	Harshit	83
12	February	S S	Harshit	84
13	February	Gujarati	Harshit	57
14	March	Eng	Harshit	41
15	March	Maths	Harshit	71
16	March	Science	Harshit	41
17	March	Computer	Harshit	92
18	March	S S	Harshit	91
19	March	Gujarati	Harshit	56
20	January	Eng	Heena	87
21	January	Maths	Heena	53
22	January	Science	Heena	35

            Sample data for Pivot table

Select range -        A1 .. D22

Title bar –             Insert                           

                             Name

                             Define

                             Data

Title bar -              DATA

                             PIVOTTABLE AND PIVOTCHART REPORTS

1. Dilog box

                             Microsoft Office Excel List or database

                             PivotTable                  

                                                                                      NEXT

2. Dialog box –

                             Range :-    DATA

                                                                                      NEXT

3. Dialog Box –

                                      New worksheet

                                      Layout

 PivotTable and Pivot chart Wizard-Layout

Ok

FINISH

Pivot table result

         Refresh Button