Lab works-2(MS Excel)
->Know about menus and tools.
->Know about basic terms spreadsheet, workbook, cell, cell address, pointer, function, formulas etc.
A.
1. create following data with formatting(font,font size,bold, underline, alignment, color, orientation, copy &paste, cut & paste, clipboard, conditional formatting, inserting and deleting new row and column etc.).
2. Save the file. Convert the file into pdf format.
B. create a table with following data.
1. Represent this data graphically (in chart form (bar, pie, scatter, line etc.)).
2. Filter and sort the data under different conditions.
C. Use validation to validate data inserted in above table. The condition is marks must be in range 1-100.
D.
1. Show how to use formulas in excel(use sum(),sumif(),average(),max(),min(),count(), countif, if, and, if and ‘and’ ,date, time etc.)
2. Use formulas to prepare salary sheet and marks-sheet as given here.
Take reference of book.
3. Prepare an electricity bill for a customer.
Condition: Unit consume = current unit - previous unit
Calculate Amount as if unit consume following criteria meets:-
- If unit consumes is less than or equal 20 unit , amount = Rs. 80
- If unit consumes is greater than 20 and below than 250 units, the amount is Rs. 7.3 per unit for the extra unit.
- If unit consumes is greater than 250 units, the amount is Rs. 11.5 per unit for extra unit.
E.
1. Use goal seek to find number of guest in following question.
2. Use scenario manager to create multiple scenes for different guests in following question.
Take the number of guests as 20, 10 and 40.
3. Use data table to find interests for different principles.
F. Prepare a pivot table for following entered data.
1. Find the average of amount
2. Find the products exported to countries
3. Find the products exported to Germany in specific date
4. Represent the data graphically.
Case study
Use word and excel to merge data of marks-sheet. The data or subjects marks are stored in excel in different rows. We will merge these data with data stored in word.
Assume that we have stored name and marks of five subjects in excel. To import data from excel, we will use mail merge in word.
->For this, make a layout of mark-sheet in word.
->Store your data/records in excel. Merge them.