Introduction to Programming

Unit 1.3 - Process and Information Layout

Presentation 2

Objectives

At the end of this presentation, you will be able to:

• Create workbooks using data information layout

• Publish workbook as web page

Data Information Layout

• Helps in creating spreadsheets.

Spreadsheet Basics

Scroll Buttons Worksheet Tabs Column Row Cell

Cell Number Box Active Cell Formula Box

Single Excel Sheet

• Workbook is a collection of worksheets.

Hands-On!

• Perform the steps to format the text and change the column width in the worksheet.

Returns the smallest value for the selected values.

MIN

Returns the largest value for the selected values.

MAX

Calculates the average of the selected values.

AVERAGE

Calculates the total of all the selected values.

SUM

DescriptionFormula Name

Entering Formula

Multiplies all the selected values and returns their product.

PRODUCT

Counts the number of cells that contains the specified value.

COUNT

DescriptionFormula Name

Entering Formula

Hands-On!

• Perform the steps to perform calculations such as sum and average.

Activity 1.3.7

• Perform the following steps:

Step 1: Open the data file Sales.xls.Step 2: Calculate the total number of Music CDs

sold by typing the formula =sum(f11,f18,f25) on the cell F34.

Step 3: Copy this formula to other cells to calculate the total number of Movies and Games CDs sold.

Step 4: Save the workbook and identify the change in code.

Formatting the Cells

• It is possible to change the way a text or number that appears in a cell by formatting the cell.

Hands-On!

• Perform the steps to format the cell.

Activity 1.3.8

• Perform the following steps:

Step 1: Open the data file Perso_Detail.xls.Step 2: Enter your name, date of birth

(dd/mm/yyyy) and birth time in the corresponding columns.

Step 3: Change the format of the F14 such that DOB will be displayed as <dd>-<month in words>-<yy> by choosing Format Cells Date 14-Mar-01.

Example: 19/10/1986 19-oct-86

Activity 1.3.8

Step 4: Change the format of the F15 such that time of birth will be displayed in 24hrs

clock by choosing Format Cells Time 13:30.

Example: 1:30 pm 13:30

Step 5: Save the file and identify the changes in the code.

Lab Exercise

• Open the data file Drinks.xls and perform the following: • In the Total column, calculate Cost/Item * Total

Item. • Calculate the total cost and store it in the

column J21.• Apply the formatting and change the columns

F and J such that the cost will be displayed as R<cost>. Key: Use Format Cells.

• Identify the changes in the code.

Creating Charts

• Charts are the graphical representation of the data.

Hands-On!

• Perform the steps to create a chart.

Lab Exercise

• Open the data file Form4_ Results.xls. Create bar chart for the Maths marks.

Multiple Excel Sheets

• When you have data in two excel sheets in a workbook, you can perform calculation using the data present in both the sheets.

• This can be achieved by using the format sheetname!cellname.

Hands-On!

• In a work book, sheet1 stored the marks scored in test1 and sheet2 stores the marks scored in test2. Perform the steps to compare the marks obtained by each student in the English tests and store the maximum mark in the third sheet.

Activity 1.3.9

• Perform the above steps for the Form5_ Results_Multiple.xls.

Creating Hyperlink Across Worksheets

• It is possible to create hyperlink across worksheets with a workbook.

Hands-On!

• Perform the steps to create hyperlink across worksheets in a work book.

Multiple Workbooks

• You can create hyperlinks across workbooks.

Hands-On!

• Perform the steps to create hyperlink across workbooks.

Publishing the Workbook as Web Page

• You can convert the workbook into HTML format and publish the document on web.

Hands-On!

• Perform the steps to publish the workbook as web page.

Summary

In this presentation, you learnt the following:• Data information layout helps in creating

spreadsheets.• Workbook is a collection of worksheets. • A worksheet consists of number of rows and

columns present in it. • The intersection of a row and a column is

defined as a cell.• Alphabets and numbers help in identifying the

corresponding cell is termed as label.

Assignment

1. Describe the following functions:a. SUMb. MAXc. COUNT