3.9 SUMMARY
Spreadsheets enable working with data easy and effective. It has ability to store,
manipulate, format, sort, filter, retrieve, organize, represent and analyze data as
per your requirements.
You can save any kind of data, in any format in cells of a worksheet. Multiple
worksheets in a workbook enable you to store a large amount of data and manage
it efficiently. Formulas and Functions allow easy calculation and manipulation of
data. Tables facilitate uncomplicated organization and retrieval of data. Charts are
visual display of the information. Additionally, there are many formatting and
design features in the Excel program to create and print a professional looking
workbook.
A Spreadsheet program is useful is any kind of area, since it is associated with
data and information which is an important aspect of all of our lives. You can use
it for small purposes like maintaining birthday lists, home budgets or for big ones
like creating reports, preparing dashboards, for stock management, shipment
planning, as analytical tool in large corporate environments. A Spreadsheet
program can be used for any data related purpose.
3.10 LAB EXERCISE
1. Using ‘Excel Options’ do the following customizations:
a. Set default two worksheets in a workbook. Original number is three.
b. Disable autorecover for your current workbook.
c. Set the Recent number of documents to be displayed to two.
d. Don’t display grid lines.
e. Enable show page breaks.
f. Set the Enter key direction so that the cursor moves to right when you
press enter in a cell
2. Open a new workbook. Create a table with two columns. Use columns A and
B of the worksheet. The headings for first one should be First Name. Give the
heading as Last Name for the second column. Add records in the table. Please
ensure that you enter all the data in the lower case. For example, you should
enter first name as rahul and second name as gandhi. Your data should
contain multiple records with the same first or last name. Save workbook as
Student.xlsx
3. Open Student.xlsx. Create another table using column E and F. Again the
heading would be First Name and Last Name. Using string functions, change
the names to proper case (for example to Rahul or Gandhi) and store in E and
F. Original data should remain. Save and Close the worksheet.
4. Open Student.xlsx. Hide Columns A and B. Rename Sheet1 as Student Name.
Delete all the worksheets in the workbook except Student Name. Add Borders
to the table. Format heading as: Bold, center aligned, increase font size,
change the font colour and the fill of the heading. Save and close.
5. Open Student.xlsx. Sort the records in alphabetical order of the first name.
Filter all records with last name Agarwal.
Now use a string function to concatenate the two names (first name and last
name) and store in the column D (give heading as Name).
6. Create a new workbook containing Student Marks. Add column headings :
Name, English, Hindi, Maths, Science, Social Science, Total Marks,
Percentage Marks. Add records to the table. You may copy name from
Student.xlsx. Add title to the table. Format the table properly. Use functions to
calculate Total Marks and Percentage. Freeze the panes, so that headings
don’t scroll. Add Headers and Footer to the worksheet. Preview the
worksheet. Save as StudentMarks.xlsx and close.
7. Create a table of records with columns Name and Donation Amount.
Donation amount should be formatted with two decimal places. There should
be at least twenty records in the table. Create a conditional format to highlight
top 3 donations with blue colour and lowest 3 donations with red colour. The
table should have a a heading
8. Use Auto fill feature to fill column B with odd numbers and column C with
even numbers. There should be twenty records in each column. Save the
workbook as EvenOdd.xlsx
9. Using the workbook EvenOdd.xlsx, create a formula in column D1 to add B1
and C1. Copy the formula from D1 to all other following rows. Also use
formula to display the sum of all the values in column B in cell B35.
Similarly, for column C in cell C35. Add the label ‘Sum’ in cell A35. It
should be bold and double underlined. Save the workbook.
10. Open workbook EvenOdd.xlsx. Go to Sheet 3. Type the value 1.5 in cell A1.
Come back to Sheet1. Create a formula in E1 to multiply the value is D1 with
the value in cell A1of Sheet3. Copy the formula to other rows.
11. Create a table of expenses for a house hold. The table will have two column :
Expense name and Expense value in percent (it will the total percent spend
under this head). Create a Pie Chart for the same data. Examples of Expense
heads can be Food, education, utilities, clothing, house rent. The chart should
have proper Title, labels and legends.
12. Create a list of names with all the names in column A, stored in the format
Last name, First name, for example: Gandhi,Rahul. Use Convert to Text
feature to separate the first name and the last name. The original data should
not be lost.
