Textbook: Spreadsheet- Data Analysis | Computer Science for Class 7 PDF Download

 Page 1


Arrange the customers in the alphabetic order. 1. 
Find the top five customers based on their expenditure in the last month, to give them a 2. 
discount of 8% on their bill.
Find the fast selling items among chocolates, soft drinks, ice creams and biscuits. 3. 
Find out which of the fast selling item, is sold most. (Example: Ice creams are in various sizes. 4. 
Which size is sold most?)
Enter and format data to make it easy to read and print. 5. 
Print various portions of the spreadsheet. 6. 
Tejas: Our teacher said that the local Kirana shop owner wanted some help with his business and 
asked us to become his consultants!
Moz: What kind of help does he need? 
Jyoti: He records customers’ information, sales and inventory data in various spreadsheets. He 
wants us to teach him how to organize and analyze this data. He wants to know how to:
Moz: How are you planning to proceed?
Tejas: We have taken sample data from the shop owner. 
Jyoti: We have entered the data in the spreadsheet. We applied our previous knowledge of 
formatting to select Font colour and highlight selected columns.      Spreadsheet 
Jyoti: Now, we will explore and learn the features required for the analysis of the data. 
Moz: Good strategy. Let us start.
Spreadsheets are used to enter and analyse numeric data. They can be used to make projections and draw graphs based on data. 
1
1
Aim: In this lesson, you will learn:
- To Organize the given data in a spreadsheet.
- To Calculate percentage, sum, average using functions option in a spreadsheet.
- To Represent data in multiple ways.
Spreadsheet: Data Analysis
7
Page 2


Arrange the customers in the alphabetic order. 1. 
Find the top five customers based on their expenditure in the last month, to give them a 2. 
discount of 8% on their bill.
Find the fast selling items among chocolates, soft drinks, ice creams and biscuits. 3. 
Find out which of the fast selling item, is sold most. (Example: Ice creams are in various sizes. 4. 
Which size is sold most?)
Enter and format data to make it easy to read and print. 5. 
Print various portions of the spreadsheet. 6. 
Tejas: Our teacher said that the local Kirana shop owner wanted some help with his business and 
asked us to become his consultants!
Moz: What kind of help does he need? 
Jyoti: He records customers’ information, sales and inventory data in various spreadsheets. He 
wants us to teach him how to organize and analyze this data. He wants to know how to:
Moz: How are you planning to proceed?
Tejas: We have taken sample data from the shop owner. 
Jyoti: We have entered the data in the spreadsheet. We applied our previous knowledge of 
formatting to select Font colour and highlight selected columns.      Spreadsheet 
Jyoti: Now, we will explore and learn the features required for the analysis of the data. 
Moz: Good strategy. Let us start.
Spreadsheets are used to enter and analyse numeric data. They can be used to make projections and draw graphs based on data. 
1
1
Aim: In this lesson, you will learn:
- To Organize the given data in a spreadsheet.
- To Calculate percentage, sum, average using functions option in a spreadsheet.
- To Represent data in multiple ways.
Spreadsheet: Data Analysis
7
Arrange the customers in the alphabetic order. 1. 
Tejas: Here is the list of frequent customers. Let us sort the list to arrange customers in  
alphabetic order.
Moz: Yes, you can sort both textual and numerical data in 
spreadsheets. You can also sort in either ascending or 
descending order. 
Jyoti: Let us sort customer names in ascending order.
Sorting	is	the	process	of	placing	the	data	in	some	well	defined	order. •	
Sorting can be performed on data in one or more columns. •	
The	 order	 is	 defined	 for	 each	 column	 (ascending	 or	 descending),	 and	 •	
the	sort	is	performed	on	the	column	in	the	defined	order.
Sorting
Concept Skill
Sorting of data
Click	 on	 the	 column	 which 	 needs	 to	 be	 sorted.	 The	 first	 row	 data	 is	 assumed	 as	 the	 1. 
header of the column. 
Now select ‘Data’ from the menu 2. 
bar, in the drop down list select 
‘Sort’.
In the displayed window, select the sort order 3. 
whether ascending or descending. You can 
sort the same data in three levels.
Click OK to sort the data. 4. 
Page 3


Arrange the customers in the alphabetic order. 1. 
Find the top five customers based on their expenditure in the last month, to give them a 2. 
discount of 8% on their bill.
Find the fast selling items among chocolates, soft drinks, ice creams and biscuits. 3. 
Find out which of the fast selling item, is sold most. (Example: Ice creams are in various sizes. 4. 
Which size is sold most?)
Enter and format data to make it easy to read and print. 5. 
Print various portions of the spreadsheet. 6. 
Tejas: Our teacher said that the local Kirana shop owner wanted some help with his business and 
asked us to become his consultants!
Moz: What kind of help does he need? 
Jyoti: He records customers’ information, sales and inventory data in various spreadsheets. He 
wants us to teach him how to organize and analyze this data. He wants to know how to:
Moz: How are you planning to proceed?
Tejas: We have taken sample data from the shop owner. 
Jyoti: We have entered the data in the spreadsheet. We applied our previous knowledge of 
formatting to select Font colour and highlight selected columns.      Spreadsheet 
Jyoti: Now, we will explore and learn the features required for the analysis of the data. 
Moz: Good strategy. Let us start.
Spreadsheets are used to enter and analyse numeric data. They can be used to make projections and draw graphs based on data. 
1
1
Aim: In this lesson, you will learn:
- To Organize the given data in a spreadsheet.
- To Calculate percentage, sum, average using functions option in a spreadsheet.
- To Represent data in multiple ways.
Spreadsheet: Data Analysis
7
Arrange the customers in the alphabetic order. 1. 
Tejas: Here is the list of frequent customers. Let us sort the list to arrange customers in  
alphabetic order.
Moz: Yes, you can sort both textual and numerical data in 
spreadsheets. You can also sort in either ascending or 
descending order. 
Jyoti: Let us sort customer names in ascending order.
Sorting	is	the	process	of	placing	the	data	in	some	well	defined	order. •	
Sorting can be performed on data in one or more columns. •	
The	 order	 is	 defined	 for	 each	 column	 (ascending	 or	 descending),	 and	 •	
the	sort	is	performed	on	the	column	in	the	defined	order.
Sorting
Concept Skill
Sorting of data
Click	 on	 the	 column	 which 	 needs	 to	 be	 sorted.	 The	 first	 row	 data	 is	 assumed	 as	 the	 1. 
header of the column. 
Now select ‘Data’ from the menu 2. 
bar, in the drop down list select 
‘Sort’.
In the displayed window, select the sort order 3. 
whether ascending or descending. You can 
sort the same data in three levels.
Click OK to sort the data. 4. 
Moz: Good observation. You should not select and sort on a single column when you have 
multiple columns of data. Instead, select all the columns in the table while sorting.
Tejas: Since expenditure is our focus now, let us sort in ascending order of expenditure (in sort 
criteria tab: see the figure below ).
Tejas: If only the Monthly expenditure column is selected and the first column remains unchanged, 
then the amounts will not match the customer names.
Jyoti: We have to extend the selection to customers too.
Jyoti: We have to now compute the discount amount for the top 5 customers and also provide the 
final bill amount for each customer. 
Tejas: In the next two columns, we can calculate the discount and the final amount payable by the 
customer. We need to write the formulas for each of these columns. 
Find the top five customers based on their expenditure of last one month. Then give discount 2. 
of 8% on their bill.
Tejas:  We also have data on the monthly expenditure of these customers.  Let us enter this data 
as the next column. 
Jyoti: We have to find the top 5 customers using the monthly expenditure. We can select the data 
in the Monthly expenditure column and sort it in the descending order.
Page 4


Arrange the customers in the alphabetic order. 1. 
Find the top five customers based on their expenditure in the last month, to give them a 2. 
discount of 8% on their bill.
Find the fast selling items among chocolates, soft drinks, ice creams and biscuits. 3. 
Find out which of the fast selling item, is sold most. (Example: Ice creams are in various sizes. 4. 
Which size is sold most?)
Enter and format data to make it easy to read and print. 5. 
Print various portions of the spreadsheet. 6. 
Tejas: Our teacher said that the local Kirana shop owner wanted some help with his business and 
asked us to become his consultants!
Moz: What kind of help does he need? 
Jyoti: He records customers’ information, sales and inventory data in various spreadsheets. He 
wants us to teach him how to organize and analyze this data. He wants to know how to:
Moz: How are you planning to proceed?
Tejas: We have taken sample data from the shop owner. 
Jyoti: We have entered the data in the spreadsheet. We applied our previous knowledge of 
formatting to select Font colour and highlight selected columns.      Spreadsheet 
Jyoti: Now, we will explore and learn the features required for the analysis of the data. 
Moz: Good strategy. Let us start.
Spreadsheets are used to enter and analyse numeric data. They can be used to make projections and draw graphs based on data. 
1
1
Aim: In this lesson, you will learn:
- To Organize the given data in a spreadsheet.
- To Calculate percentage, sum, average using functions option in a spreadsheet.
- To Represent data in multiple ways.
Spreadsheet: Data Analysis
7
Arrange the customers in the alphabetic order. 1. 
Tejas: Here is the list of frequent customers. Let us sort the list to arrange customers in  
alphabetic order.
Moz: Yes, you can sort both textual and numerical data in 
spreadsheets. You can also sort in either ascending or 
descending order. 
Jyoti: Let us sort customer names in ascending order.
Sorting	is	the	process	of	placing	the	data	in	some	well	defined	order. •	
Sorting can be performed on data in one or more columns. •	
The	 order	 is	 defined	 for	 each	 column	 (ascending	 or	 descending),	 and	 •	
the	sort	is	performed	on	the	column	in	the	defined	order.
Sorting
Concept Skill
Sorting of data
Click	 on	 the	 column	 which 	 needs	 to	 be	 sorted.	 The	 first	 row	 data	 is	 assumed	 as	 the	 1. 
header of the column. 
Now select ‘Data’ from the menu 2. 
bar, in the drop down list select 
‘Sort’.
In the displayed window, select the sort order 3. 
whether ascending or descending. You can 
sort the same data in three levels.
Click OK to sort the data. 4. 
Moz: Good observation. You should not select and sort on a single column when you have 
multiple columns of data. Instead, select all the columns in the table while sorting.
Tejas: Since expenditure is our focus now, let us sort in ascending order of expenditure (in sort 
criteria tab: see the figure below ).
Tejas: If only the Monthly expenditure column is selected and the first column remains unchanged, 
then the amounts will not match the customer names.
Jyoti: We have to extend the selection to customers too.
Jyoti: We have to now compute the discount amount for the top 5 customers and also provide the 
final bill amount for each customer. 
Tejas: In the next two columns, we can calculate the discount and the final amount payable by the 
customer. We need to write the formulas for each of these columns. 
Find the top five customers based on their expenditure of last one month. Then give discount 2. 
of 8% on their bill.
Tejas:  We also have data on the monthly expenditure of these customers.  Let us enter this data 
as the next column. 
Jyoti: We have to find the top 5 customers using the monthly expenditure. We can select the data 
in the Monthly expenditure column and sort it in the descending order.
 
Sample data of the products, stock and sale:
Moz: Suppose the shopkeeper decides to change the discount percentage, all you have to do is 
change the formula in the cell number: C2 and copy the same formula in the other cells. Alternately, 
you can also use the handle and drag the formula to the other cells (Illustrated in 3 above.)
Tejas: We had earlier used the Sum icon to compute the sum. Is there any other  
way of doing this?
Moz: You can type in the formula in the 
cell where you want to display the sum. 
For example, if you want to find the total 
discount given to the top five customers, type 
in the formula: =SUM(C2,C3,C4,C5,C6) 
or =SUM(C2:C6)
Jyoti: For each product, we have to compute the percentage of the item sold to the stock of the 
item. Then compare the data and decide which are the fast selling products.
Moz: Correct. 
Jyoti: I have also seen other built in 
Functions in Spreadsheets like Average 
(AVE), Maximum (MAX), Minimum 
(MIN), etc. MAX(D2:D6) will give us the 
maximum amount spent by any customer.
Find the fast selling items among chocolates, soft drinks, ice creams and biscuits. 3. 
1. Calculate discount for one customer
3. Drag handle to repeat calculation for  
    other customer.
2. Calculate final amount for one customer.
4. Final amounts for each customer.
Page 5


Arrange the customers in the alphabetic order. 1. 
Find the top five customers based on their expenditure in the last month, to give them a 2. 
discount of 8% on their bill.
Find the fast selling items among chocolates, soft drinks, ice creams and biscuits. 3. 
Find out which of the fast selling item, is sold most. (Example: Ice creams are in various sizes. 4. 
Which size is sold most?)
Enter and format data to make it easy to read and print. 5. 
Print various portions of the spreadsheet. 6. 
Tejas: Our teacher said that the local Kirana shop owner wanted some help with his business and 
asked us to become his consultants!
Moz: What kind of help does he need? 
Jyoti: He records customers’ information, sales and inventory data in various spreadsheets. He 
wants us to teach him how to organize and analyze this data. He wants to know how to:
Moz: How are you planning to proceed?
Tejas: We have taken sample data from the shop owner. 
Jyoti: We have entered the data in the spreadsheet. We applied our previous knowledge of 
formatting to select Font colour and highlight selected columns.      Spreadsheet 
Jyoti: Now, we will explore and learn the features required for the analysis of the data. 
Moz: Good strategy. Let us start.
Spreadsheets are used to enter and analyse numeric data. They can be used to make projections and draw graphs based on data. 
1
1
Aim: In this lesson, you will learn:
- To Organize the given data in a spreadsheet.
- To Calculate percentage, sum, average using functions option in a spreadsheet.
- To Represent data in multiple ways.
Spreadsheet: Data Analysis
7
Arrange the customers in the alphabetic order. 1. 
Tejas: Here is the list of frequent customers. Let us sort the list to arrange customers in  
alphabetic order.
Moz: Yes, you can sort both textual and numerical data in 
spreadsheets. You can also sort in either ascending or 
descending order. 
Jyoti: Let us sort customer names in ascending order.
Sorting	is	the	process	of	placing	the	data	in	some	well	defined	order. •	
Sorting can be performed on data in one or more columns. •	
The	 order	 is	 defined	 for	 each	 column	 (ascending	 or	 descending),	 and	 •	
the	sort	is	performed	on	the	column	in	the	defined	order.
Sorting
Concept Skill
Sorting of data
Click	 on	 the	 column	 which 	 needs	 to	 be	 sorted.	 The	 first	 row	 data	 is	 assumed	 as	 the	 1. 
header of the column. 
Now select ‘Data’ from the menu 2. 
bar, in the drop down list select 
‘Sort’.
In the displayed window, select the sort order 3. 
whether ascending or descending. You can 
sort the same data in three levels.
Click OK to sort the data. 4. 
Moz: Good observation. You should not select and sort on a single column when you have 
multiple columns of data. Instead, select all the columns in the table while sorting.
Tejas: Since expenditure is our focus now, let us sort in ascending order of expenditure (in sort 
criteria tab: see the figure below ).
Tejas: If only the Monthly expenditure column is selected and the first column remains unchanged, 
then the amounts will not match the customer names.
Jyoti: We have to extend the selection to customers too.
Jyoti: We have to now compute the discount amount for the top 5 customers and also provide the 
final bill amount for each customer. 
Tejas: In the next two columns, we can calculate the discount and the final amount payable by the 
customer. We need to write the formulas for each of these columns. 
Find the top five customers based on their expenditure of last one month. Then give discount 2. 
of 8% on their bill.
Tejas:  We also have data on the monthly expenditure of these customers.  Let us enter this data 
as the next column. 
Jyoti: We have to find the top 5 customers using the monthly expenditure. We can select the data 
in the Monthly expenditure column and sort it in the descending order.
 
Sample data of the products, stock and sale:
Moz: Suppose the shopkeeper decides to change the discount percentage, all you have to do is 
change the formula in the cell number: C2 and copy the same formula in the other cells. Alternately, 
you can also use the handle and drag the formula to the other cells (Illustrated in 3 above.)
Tejas: We had earlier used the Sum icon to compute the sum. Is there any other  
way of doing this?
Moz: You can type in the formula in the 
cell where you want to display the sum. 
For example, if you want to find the total 
discount given to the top five customers, type 
in the formula: =SUM(C2,C3,C4,C5,C6) 
or =SUM(C2:C6)
Jyoti: For each product, we have to compute the percentage of the item sold to the stock of the 
item. Then compare the data and decide which are the fast selling products.
Moz: Correct. 
Jyoti: I have also seen other built in 
Functions in Spreadsheets like Average 
(AVE), Maximum (MAX), Minimum 
(MIN), etc. MAX(D2:D6) will give us the 
maximum amount spent by any customer.
Find the fast selling items among chocolates, soft drinks, ice creams and biscuits. 3. 
1. Calculate discount for one customer
3. Drag handle to repeat calculation for  
    other customer.
2. Calculate final amount for one customer.
4. Final amounts for each customer.
Tejas: We can provide a chart which can give a clear idea about the fast moving items. We will make 
a bar chart which shows the stock and sale and another chart that shows the percentage sold, for 
easy comparison.      Multiple representations
Jyoti: By looking at this graph the shopkeeper will get an idea about how much of each product 
he should stock.
Moz: Good idea. 
Tejas: The stock and sale comparison clearly shows that gap is wider for soft drinks.
Moz: Which product is of highest demand?
Jyoti: Ice creams, because the percentage sold is highest and the stock is also highest.
Moz: Right. Now see which type of ice creams are in most demand. The shop keeper can then 
decide how much to stock of each ice cream.
Stock and sale of products Percentage of stock sold
Tejas: We can again apply the knowledge of calculating the percentage sold for different  
types of ice cream.
Jyoti: Family pack ice cream is sold least while the cone ice creams and cup ice creams are 
sold the most. This means there is little demand for family pack but high demand for cup and  
cone ice creams.
Moz: Very well interpreted. Now show me how you formatted the tables to make them  
look so neat.
For the fast selling item, find out the kind and size that is sold  most? (Example: Ice creams 4. 
are in various sizes. Which size is sold most?)
Information can be represented in lists, tables, mind map, graphs or charts. Choose a form of representation that matches the nature of 
information and purpose of use.
1
1