CM 7 - Excel Advanced Features

CM 7 - Excel Advanced Features

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 spread sheets. He wants us to teach him how to organize and extract information from this data as follows:

Enter and format (split cell, merge cell, highlight selected portion, word wrap) data.
Arrange the customers in the alphabetic order.
Find the top five customers based on their expenditure of last one month and give a discount of 8% on their bill.
Find the fast moving items among choclates, soft drinks, ice creams and instant noodles in the shop.
Find which one of the fast moving item, is sold most? (Example: Ice creams are in various sizes. Which size is sold most?)
Printing (selected area, file).

Moz: Good. How are the consultants planning to proceed?

1. Enter and format (split cell, merge cell, highlight selected portion, word wrap) data.

Tejas: We have taken some sample data from the Shop keeper.

Jyoti: We have entered the data in the spreadsheet. We applied our previous knowledge of formatting to select font colour and highlight selected column.

Tejas: But we are not able to increase the size of the cell to enter the title of the table.

Moz: You can use the feature of merge cells for this.

Jyoti: While entering address of the customers, the text spreads across far and becomes difficult to read. There must be some option to format it.

Tejas: Let us select the cell and do a right click. We can explore the different options that open up.

Jyoti: I see that Format cells option allows us to do several actions. Let us check each of these one by one.

Tejas: I found the 'wrap text automatically' option under alignment option. Clicking on this, we can shrink the text to fit the width of the column.

Moz: Good work!

Skill box: To format a cell

Select the cell that needs to be formatted and right click the mouse.
Click on the use 'Format Cells' option.

Explore various options available.

End of skill box: To format a cell

Jyoti: Using the sample data first we want to explore and learn the features required for the analysis of the data.

Moz: Good strategy. Let us start with the next question.

Arrange the customers in the alphabetic order.

Tejas: Here is the list of frequent customers. Let us sort the list to arrange customers in alphabetic order.

Moz: Yes, you can sort a column of both textual and numerical data in spreadsheets. When you want a sorted list alphabetically, then it is in ascending order. When you want to find the top five customers then what is the order that you should define?

Jyoti: Descending order.

Concept: SORTING

Sorting is the process of placing the data in some well defined order.

• Sorting can be performed on data based on one or more columns.

• Some order is defined for each column (example: ascending or descending), and the sort is performed on the column in the defined order.

Skill box Sorting data

Keep the cursor on the column which needs to be sorted. The first row data is assumed as the header of the column.

Now select 'Data' from the menubar, in the drop down list select 'Sort'.

In the displayed window, select the sort order whether ascending or descending.

You can sort the same data in three levels.

Click OK to sort the data.

Skill box Sorting data --- End

3. Find the top five customers based on their expenditure of last one month and give discount of 8% on their bill.

Tejas: We also have data on the monthly expenditure of these customers. Let us add this in the next column.

Jyoti: We have to find the top 5 customers using the monthly expenditure. Let us sort the expenditure column in descending order.

Jyoti: We have to extend the selection to customers too. If the first column remains unchanged , then the amounts do not match the customers !

Moz: Good observation. You cannot select and sort on a single column when you have multiple columns of data. You should select the full table.

Tejas: Since expenditure is our focus now, in sort criteria let us sort in descending order of expenditure.

Jyoti: We have to now calculate/compute the discount amount for the top 5 customers and also provide the final bill amount of the customer.

Tejas: Let us add two columns, one to calculate the discount and the other to calculate the final amount. We need to write the formulas for each of these columns. [ CONNECT AVE FORMULA USED EARLIER].

Moz: Suppose the shop keeper decides to change the discount percentage, all you have to do is change the formula in the cell number: C1 and all other new values are automatically calculated.

Tejas: We had earlier used the Sum icon to compute the sum , is there any other way of doing it?

Moz: Yes there are built in Functions in Spreadsheets like SUM,AVE,MAX,MIN,=SUM(F7:F14)skill box

4. Find the fast moving items among choclates, soft drinks, ice creams and instant noodles in the shop.

Sample data of the products, stock and sale

Jyoti: We have to compute percentage sold to compare and show which is a fast moving product.

Moz: Correct.

Tejas: We can provide a chart which can give a clear idea about the fast moving item. A bar chart will show the clear comparison of percentage of each product sold.

Jyoti: Let us also give a chart which shows the stock and percentage sold. Looking at this the shopkeeper will get an idea about how much of each product he should stock.

Moz: Good idea.

Tejas: The stock and percent sold comparison clearly shows that the shop keeper can stock lesser number of soft drinks and choclates.

Moz: Which product is of high demand?

Jyoti: Ice creams.

Moz: Right. Now check out which type of icecreams are in most demand. The shop keeper can then decide about his stock of icecream.

5. Find which type of the fast moving item, is sold most? (Example: Ice creams are in various sizes. Which size is sold most?)

Ice cream and cool drinks sale data in a month
Variety / Stock / Sold / Remaining / Percentage sold
Family Pack ice cream / 75 / 15 / 60 / 20
Cup ice cream / 250 / 225 / 25 / 90
Cone ice cream / 200 / 175 / 10 / 97
Stick ice cream / 175 / 160 / 65 / 57

6. Printing (selected area, file).

Jyoti: Now let us print our spreadsheet. It must be similar to printing other office files.

Tejas: But a spreadsheet file has several coumns and rows that we have not used for our data.In addition, there are multiple sheets. We would end up printing empty columns and rows if we print all sheets.

Jyoti: I see an option of prinitng selected cells. I think with this, we can select this and print only the table that we want.

Moz: It is great to see you explore and learn the features of application on your own.

Skill box: Printing

Select 'Print' from the menubar option 'File'. In the window displayed , select the required options and click OK.

Skill box: Printing---end

--lesson ends-----

Worksheets

1) Following is a line graph for the temperatures in 3 cities, namely New Delhi, Mumbai andBangalore and Jodhpuron a given day.

Study the above graph and answer the following questions.

a) From the above graph, fill the data in the table given below.

Time / Temperature
New Delhi / Mumbai / Bangalore / Jodhpur
12 Midnight
03:00:00
06:00:00
09:00:00
12:00:00
15:00:00
18:00:00
21:00:00

b) What is the temperature difference in New Delhi between midnight and noon?

c) At 3 pm which city was the hottest?

d) In New Delhi, did the temperature rise or fall from 9 pm to 12 am?

e) In which city was the lowest temperature recorded? What was the lowest temperature?

f) A city is comfortable, when the temperature is below 30 degrees in the daytime. Which is the most comfortable city?

g) Calculate the average temperature in New Delhi on the said day?

h) What is the difference in average temperatures of Jodhpur and Bangalore?

2) The price variation for 1 kg of vegetables in the month of March is given in the table below. Study the graph and answer the following questions.

a) Using the data given in the graph, fill in the table give below.

Price in the month of March
Sl. No / Name of the vegetable / week1 / week2 / week3 / week4
1 / Onion
2 / Potato
3 / Tomato
4 / Cabbage

b) Which are the two vegetables that maintained the same price for two weeks ?

c) What is the difference between the price of tomato in the beginning of March and end of March?

d) Name the vegetable whose price is consistently falling in March?

e) Which vegetable was cheapest in the 2nd week of March?

f) What is the difference in prices of cabbage and tomato in week 3?

g) Riju is buying vegetables in week 3. He has got 80 rupees with him. The shop has all the above vegetables. He has to buy three types of vegetables. The minimum quantity he can buy is 1 kilogram. If he buys:

a) Onion he has to get Potato also.

b) With tomato you can't buy cabbage

Can you find out the various options available to him.

3) Here is the data of a survey conducted among 45 students of a class on their favorite food.

Food Item / Frequency
Pizza / 15
Dosa / 6
Noodles / 9
Burger / 10
Soup / 5

Draw a pie chart for this data.

4) Given pie chart represents the types of movies preferred by moviegoers. 60 moviegoers were interviewed for the survey. Study the pie chart.

a) Fill in the table for the data given in the pie chart.

Type of movies / Number of people
who like this

Activity

1)A fun trip to a water park is arranged for the students in Class 7. There are two divisions in Class 7 each with 40 students. A contribution of Rs.150 is to be collected from the students going on the trip.

10% of the contribution goes to the entrance fee of the water park

20% of the contribution goes towards food expenses

Bus charges for the trip is Rs.2000. Half of the bus charges will be borne by the school.

It is not sure how many will be going. A table is made to compare the expenses and the money collected, according to the number of students going for the trip.

Number of students / Total contribution
(Number of students x 150) / Entrance fee
(10% of total contribution) / Food expenses
(20% of total contribution)
5 / 750
10 / 1500
15 / 2250
20 / 3000

a. Enter the above table in a spreadsheet application and answer the following questions:

b. What is the total expense, if all the students go for the trip?

c. What is the minimum number of students needed so that all expenses are met?

2)Given are the height and weight of some of the students in a school.

Name of the student / Height / Weight
Aayush / 5 ft 4 in / 55 kgs
Pallavi / 5 ft 2 in / 50 kgs
Robin / 4 ft 11 in / 45 kgs
Sameer / 5 ft 5 in / 70kgs
Vrinda / 5 ft 6 in / 60kgs
Priya / 5 ft 7 in / 75 kgs
Jaya / 5 ft 4 in / 58 kgs
Pranav / 4 ft 7 in / 40 kgs
Dileep / 4 ft 6 in / 50 kgs
Riya / 5 ft 6 in / 65 kgs
Raju / 6 ft / 73 kgs

1. Enter this data into an excel sheet.

Now do the following activities:

2. a) Arrange these data in:

a) Names in alphabetic order

b) Weight in ascending order

b) Plot line graphs of:

Name against height

Name against weight

3. Now answer the following questions

a) Name the students who have the same weight.

b) How many students are there whose height is more than 5 ft 5 in.

c) Find the average height and average weight of the students?

Explore:

a. In spreadsheet application; in a chart how you can insert:

title
labels to x and y axis