Lab # 3: Creating SpreadSheets Using MS-EXCEL

SSUET/QR/114

LAB # 3
CREATING SPREADSHEETS USING MS-EXCEL

OBJECT
To understand and to familiarize with MS-Excel. To create and edit professional documents using functions and charts.

THEORY
Excel can be used for organizing, calculating and analyzing data. A user may work on one or more worksheets in a workbook. A user can save time by using formulas to calculate values automatically. A user can also make its worksheet attractive by formatting it. A user may add charts, and may save and print workbook. Workbooks are collection of worksheets in the same file on disk. Sheets may contain different types of information. Usually the sheets in a workbook contain related information, such as budgets. A standard worksheet contains 256 columns and 16,384 rows. The intersection of a row and column forms a cell in which a user can enter text or values.

Creating New Workbook

To create a new workbook the following two methods are employed. When Excel is already running click File New To create a new workbook by using an icon from desktop right on the desktop to display context-sensitive menu, and click New Microsoft Excel Worksheet.

Opening Existing Workbook

To open a workbook that has been previously saved to disk, click File Open.
CE-101 : Computing and Programming Fundamentals 20

Lab # 3: Creating SpreadSheets Using MS-EXCEL

SSUET/QR/114

Saving Workbook
To save the changes, click File Save.

Closing Workbook
For closing the current workbook click File Close

Activating Worksheets
To activate a worksheet, click on the worksheet tab at the bottom of the workbook.

Moving or Copying Worksheets Within The Same Workbook

To move a worksheet within the same workbook, first activate the worksheet then use either of the following methods: Click on the worksheet tab, then drag and drop it to a new location along the row of worksheet tabs. Click Edit Move or Copy Sheet. Select the insertion point before Sheet drop-down list and click OK. To copy a worksheet within the same workbook, use following method. With the mouse, select the tab, then hold down the Ctrl key while you drag and drop the tab. With the menu command, check the Create A Copy check box on the Move or Copy dialog box. A copy of the new worksheet is inserted.

Moving or Copying Worksheets to a different Workbook

To move a worksheet to a different workbook, either drag and drop the worksheet or use the following method. Click Edit Move or Copy Sheet. The Move or Copy Dialogue box appears. Specify the destination workbook in the To Book drop-down list. Specify in the Before Sheet list where the sheet will be placed in the destination workbook. Check the Create A Copy setting to copy the sheet, or uncheck it to move the sheet. Click OK.

Entering Data Into a Cell

To enter data into a cell, use following method.
CE-101 : Computing and Programming Fundamentals 21

Lab # 3: Creating SpreadSheets Using MS-EXCEL

SSUET/QR/114

Select the cell by clicking on it. Type numbers, text, or a combination of both. Press Enter

Moving or Copying Cells

The easiest way of moving cells is to use the mouse to drag and drop them using following method: Select the cells you want to move (the range must be contiguous). Point to an outside border of the selected range using the mouse. The mouse pointer will turn into an arrow. Click on the selection; drag the cells to a new location, then release the mouse button. The cell will now be positioned to the new location. Copying via drag and drop is the same as moving, except you hold down the Ctrl key while you drag. The cursor arrow displays a plus (+) to show that you are copying rather than moving.

Inserting Cells
To insert cells, select the cells where you want to perform the insertion. This can be a cell or a range of cells, entire rows or entire columns using following method: Right-click on the selection to display the short cut menu, and choose insert. From insert menu do one of the following Choose the rows command to insert entire rows. Choose the Columns command to insert entire columns. Choose the cells command to insert a range of cells. The insert dialog box is displayed, asking how the insertion should occur.

Deleting Cells
To delete a cell or cells, follow these steps: Select the cells that you want to delete. Click Edit Delete

Finding a Value
To find a value on an active worksheet, follow these steps: Choose Edit Find. The Find dialogue box appears. Type the characters you want to find in the Find What box. Click on Find Next to find the next cell containing the search value.

Replacing a Value
The replace option works like Find, but allows you to replace the found value with another value. Try the following exercise:
CE-101 : Computing and Programming Fundamentals 22

Lab # 3: Creating SpreadSheets Using MS-EXCEL

SSUET/QR/114

Enter the following data into a blank worksheet: Nasir Karim Karim Jamal Fazal Ahmed Kashif Ahmed Click Edit Replace. The replace dialogue box appears Type Karim in the Find What box. Type Rahim in Replace With. Click on Replace All to replace all instances of Karim with Rahim.

Formatting Columns
A user can improve the appearance of worksheet by adjusting column widths using following method: Select cells in the column that you want to change. Click the Format Column option Type in the width and then choose OK.

Formatting Rows
A user may change row height to create more space for titles using following method. Select a cell in each row you want to change. Click Format Row Height option. Enter the height in the Row Height box. Click OK.

Adding Colors and Patterns

Colors and patterns can enhance the worksheet style. A user can apply these features using following method: Select the cell you want to add color or pattern. Click the Format Cells option. Select the Patterns tab. Select a pattern from the Pattern pull-down list. If you want a colored pattern, select a background color from the pattern pull-down list. Click OK.

CE-101 : Computing and Programming Fundamentals

23

Lab # 3: Creating SpreadSheets Using MS-EXCEL

SSUET/QR/114

Fig 3.1: Excel Screen Layout

Fig 3.2: Excel File Menu

Fig 3.3: Excel View Menu

CE-101 : Computing and Programming Fundamentals

24

Lab # 3: Creating SpreadSheets Using MS-EXCEL

SSUET/QR/114

Fig 3.4: Excel Format Menu

Fig 3.5: Excel Data Menu

Fig 3.6: Excel Auto format box

CE-101 : Computing and Programming Fundamentals

25

Lab # 3: Creating SpreadSheets Using MS-EXCEL

SSUET/QR/114

THEORY
A spreadsheet is only as good as the operations a user can perform on it. Excel offers its users a wealth of options for charting and calculating the data in worksheets. Formulas provide the real power when doing analysis and modeling, creating functioning spreadsheet systems, in Excel. A user can perform a wide variety of numeric calculations, including addition, subtraction, multiplication and division. A user can also manipulate text and lookup values in tables.

Part I : Formulas and Functions

A formula is essentially a sequence of values and operators that begins with an equal sign (=) and produces a new value. The built-in formulas of Excel are called functions. The functions are designed to perform many different kinds of calculations.

Entering Formula
To enter a formula into a cell, simply select the cell in which you want the formula. The first character must be an equal sign. Select the desired cell on a blank worksheet. Enter =<formula> then press Enter. The returning value will appear in the cell.

Editing Formula
A formula can be edited either in the formula bar or in the cell.

Entering Functions
To enter function within a formula or as a formula, use following method: Select the cell where you want to enter the function. Click Insert Function to display the function wizard dialogue box.

CE-101 : Computing and Programming Fundamentals

26

Lab # 3: Creating SpreadSheets Using MS-EXCEL

SSUET/QR/114

Editing Functions
To edit an existing function, use the following method: Select the cell containing the text. Click Insert Function. After editing the function, click OK.

Part II : Charts
When working with charts, it is important to understand the difference between a data series and data points. These concepts are crucial both for understanding how the data is being represented in the chart and for creating the chart that will display properly. A data point is an individual value that originated in a single worksheet cell. When data points are plotted onto a chart, they are represented by columns, bars, dots, slices or other shapes called markers. A data series is a group of related data point that represents a single row or column of data. Each series is distinguished on a chart by a unique color or pattern.

Creating a Chart
To create a chart use the following method: Select the data you want to chart. Click Insert Chart or click the Chart Wizard.

Formatting a Chart
A user can apply different number formats, fonts, patterns and colors to the various chart elements. The chart element must be selected in order to format it. Double-click the chart element you want to format to display the format dialogue box (or right-click on the element, and choose the format command from the short cut menu). The name of the selected element is displayed in the Name box on the left part of the formula bar. Select the formatting option you want and then click OK.

Deleting a Chart
To delete a chart element, select the element and press Delete (or choose Clear from the shortcut menu).
CE-101 : Computing and Programming Fundamentals 27

Lab # 3: Creating SpreadSheets Using MS-EXCEL

SSUET/QR/114

Changing the Chart Type

When a user selects a chart element, the data menu on the main menu is replaced by a chart menu (right-click on the chart also displays the chart type option on the shortcut menu). Select the Chart Click Chart Chart Type The chart type dialog box displays the types in graphical format Click one of the chart type and then click OK.

ASSIGNMENT
Create and edit a workbook containing history (e.g. debut, country, last match) and carrier records (e.g. total runs, total wickets, batting average, runs per over, best performance) of famous cricket players.

Fig 3.7: Excel Function Wizard Box

CE-101 : Computing and Programming Fundamentals

28

Lab # 3: Creating SpreadSheets Using MS-EXCEL

SSUET/QR/114

Fig 3.8: Excel Chart Wizard Box

CE-101 : Computing and Programming Fundamentals

29