Introduction to Computing LAB 5: Introduction to MS Excel

LAB # 5

Introduction to the MS Excel

Objective

 Learn to create spreadsheets and enter data through form in Microsoft excel
 Learn excel formulas and shortcut keys

Theory

Microsoft Excel:
Microsoft Excel is the most widespread program for creating spreadsheets in the market today.
Spreadsheets allow you to organize information in rows and tables (which create cells), with the
added bonus of automatic mathematics. Spreadsheets have been used for many, many years in
business to keep track of expenses and other calculations. Excel will keep track of numbers you
place in cells, and if you define cells to refer to each other, any changes made in one cell will be
reflected in these referring cells. It sounds a bit complicated, but Excel makes it all a breeze.

OPENING MICROSOFT EXCEL:

Start >> Programs >> Microsoft Office >> Microsoft Office Excel 2010

Microsoft Excel will automatically open with a blank spreadsheet spanning many columns and
rows. You will notice a number of toolbars with many more options included.
1
Introduction to Computing LAB 5: Introduction to MS Excel

ENTER DATA IN TABLE AND FORMAT IT

In all aspects of your university study and professional career, good, clear presentation of data
and information is essential to the success with which your audience receives your information.
Simple features within Excel can really enhance how your data looks and improve comparability
of data sets.
After entering the following data set into Excel we will use features, to improve the presentation
of the data.
From this....

to this…

ENTERING DATA
When you open a workbook, by default, Excel selects cell A1 as the active cell.
1. Begin typing your data into the active cell.
2. Press the Enter key to move the active cell down (e.g. A2) OR Press the Tab key to move
the active cell to the right (e.g. B1)
3. To activate a random cell, move the cursor to that cell with the mouse and click on the
mouse button.
4. To select a group of adjacent cells, click on the first cell and drag the cursor across the
adjoining cells.
5. To select a group of random cells, click the first cell, hold down the CTRL key and click the
additional cells.

2
Introduction to Computing LAB 5: Introduction to MS Excel

COLUMN WIDTHS / ROW WIDTHS

To change the column width to display all the data clearly,

1. Move the cursor with the mouse to the right of
the column heading of the column you want to
widen (i.e. A B C D, etc). The cursor will turn
into a cross symbol.
2. Double click the mouse and the column will
automatically widen to the width of the contents.
OR Click and drag the column to widen manually.
3. Row widths are adjusted the same way, except
you place the cursor between the row headings.

TO INSERT COLUMNS OR ROW

To insert a column within a data set:

1. Select the column to the right of
where you want to position the new
column, by clicking on the column
heading (i.e. A B C D, etc).
2. Right click your mouse
while your cursor is over
the column heading.
3. Click on Insert.

To insert a row within a data

set:
1. Select the row below where you want to position the new row, by clicking on the row
heading (i.e. 1 2 3 4, etc).
2. Right click your mouse while your cursor is over the row heading.
3. Click on Insert.

MERGING CELLS
To center a heading across the
width of the data set
1. Select the cell that contains the
heading and drag the cursor
across the columns that
represent the data set.
2. Click on the Merge and Center
button on the toolbar.

3
Introduction to Computing LAB 5: Introduction to MS Excel

FILLING CELLS WITH THE SAME DATA:

There is a quick way of entering the same data into a range of cells.
 Highlight an area of cells (for example, A1:A6)
 Type some characters or a number
 Press the Ctrl and Enter keys at the same time
 Remove the highlighting by clicking in any cell

WORD WRAP:
A long string of text can be made to wrap onto several lines within a cell using this facility.
 Click in a cell and then select Cells from the Format menu
 Click on the Alignment tab
 Click in the wrap text box, so that it is ticked
 Click OK
 Type some text and note how it wraps
 Press the Enter Key

PAGE SETUP:
The page setup allows you to format the page, set margins,
and add headers and footers. To view the Page Setup select
Windows Button > Print > Print Preview > Page Setup.
Select the Orientation under the Page tab in the Page
Setup dialog box to make the page Landscape or Portrait.
The size of the worksheet on the page can also be formatted
under the Scaling title. To force a worksheet to be printed
on one page, select Fit to 1 page(s).

MARGINS:
Change the top, bottom, left, and right margins under the
Margins tab. Enter values in the Header/Footer fields to
indicate how far from the edge of the page this text should
appear. Check the boxes for centering Horizontally or
Vertically to center the page.

4
Introduction to Computing LAB 5: Introduction to MS Excel

HEADER/FOOTER:
Add preset Headers and Footers to the page by clicking the
drop-down menus under the Header/Footer tab.
To modify a preset Header or Footer, or to make your own,
click the Custom Header or Custom Footer buttons.
A new window will open allowing you to enter text in the
left, center, or right on the page.
Format Text – After highlighting the text click this button
to change the Font, Size, and Style.
Page Number - Insert the page number of each page.
Total Number of Pages - Use this feature along with the
page number to create strings such as "page 1 of 15".
Date - Add the current date.
Time - Add the current time.
File Name - Add the name of the workbook file.
Sheet Name - Add the name of worksheet.
Picture - Add a picture.

SHEET:
Click the Sheet tab and check Gridlines box under the Print
section if you want the gridlines dividing the cells to appear on
the page. If the worksheet is several pages long and only the first
page includes titles for the columns, select Rows to repeat at
the top of the Print titles section to choose a title row that will
be printed at the top of each page.

AUTO SUM FUNCTION

To automatically total a row or column of numbers
For a row of numbers, select the empty cell directly to the right of the numbers,
then:
1. Click the AutoSum button on the right hand side of the Home ribbon
2. The SUM formula including the range of numbers selected will appear in the formula bar
3. Check you have the correct range and then press the Enter button.
OR
For a column of numbers, select the empty cell directly below the numbers, then continue
with steps 1 to 3 above.

5
Introduction to Computing LAB 5: Introduction to MS Excel

ROUNDING
To round a decimal value to a whole number or simply
increase or decrease the number of decimal places a value has:
1. Select the cell/s containing the value/s you want to alter
2. Click on the Decrease Decimal button to remove decimal
places
OR
3. Click on the Increase Decimal button to add decimal
places
SIMPLE CALCULATIONS
To carry out simple calculations: addition +
1. Think about how you would do the problem in your head or on subtraction
your calculator. multiplication *
2. Think about how Excel may carry this out and get Excel to do division /
it by clicking on the relevant cell/s and using the appropriate brackets ()
combination of: to the power of
3. Round appropriately using the “Decrease Decimal” button or
the “Increase Decimal” button as per Rounding instructions
above.

PERCENTAGES
To create a percentage from a proportion (decimal), simply
use the Percent Style button. This will automatically
display the proportion (decimal) to the nearest whole
percent, so:
0.003 will become 0%
0.03 will become 3%
0.3 will become 30%
3 will become 300%
1. Select the cell/s containing the number/s you want to alter
2. Click on the Percent Style button to round to the nearest whole percentage

BORDERS
Borders can be applied individually to each of the 4
sides of a cell or applied to a group of cells. Borders
allow you to separate data groups and highlight
specific sections of data. To add a border to a cell or
group of cells
1. Select the cell(s) that you want to place a border
around
2. Click on the down arrow of the Borders
button on the toolbar. A range of different
border buttons will be displayed.

6
Introduction to Computing LAB 5: Introduction to MS Excel

3. Click on the Borders button you want for the selected cells.

SORTING DATA
To sort data numerically or alphabetically
1. Select the rows of data you want to sort
2. Click on the Sort and Filter button on the Home ribbon.
3. Click on Custom Sort.
4. Next to the Sort By heading, click on the down arrow to
select the column on which you want to sort the data
5. Select either Smallest to Largest or Largest to Smallest.
6. Click OK.

CHARTS
Charts make data visual. With a chart you can transform spreadsheet data to show comparisons,
patterns, and trends.
In Excel 2007, you just select data in your worksheet, choose a chart type that best suits your
purpose, and click. That’s it! You can also use the new Chart Tools to customize the design,
layout, and formatting of your chart. Just like the preview capability you saw in Word 2007 and
PowerPoint 2007, you can see how various options
would look just by pointing at them in the dialog box,
even before you make a choice.

4 Clicks to create a simple chart in Excel 2007

Let’s say we want to see graphically the sales of

beverages in our café in the first 3 months of the year.

1. Select the cells containing the sales data,

including cells with text (e.g. column and row
headings for the data.)

7
Introduction to Computing LAB 5: Introduction to MS excel

2. Select Insert tab.

3. Select Column icon from Charts group
4. Select Clustered Column chart from 2-D Column type

The chart shows the sales of the 4 different beverages grouped together for each month. What if
we want to see what happened in the sales of each beverage month over month?
Select the chart, Chart Tools -> Design -> Data -> Switch Row/Column.

CHART ELEMENTS
A chart has many elements. Some of these elements are displayed by default; others can be
added as needed. You can change the display of the chart elements by moving them to other
locations in the chart, resizing them, or by changing the format. You can also remove chart
elements that you do not want to display.
Here are some standard chart elements.

1. The chart area encompasses the entire

chart and all its elements.
2. The plot area is that area bounded by
the axes, including all data series,
category names, axis titles, and all
markers that represent data points.
3. The data points are the individual
values plotted in a chart and represented
by bars, columns, lines, pie or doughnut
slices, dots, and various other shapes
called data markers. Data markers of
the same color constitute a data series
(i.e. related data points. You can plot
one or more data series in a chart, but pie charts have only one data series.
4. The horizontal (category) or vertical (value) axis is a line bordering the chart plot area used
as a frame of reference for measurement. The y-axis is usually the vertical axis and contains
data. The x-axis is usually the horizontal axis and contains the categories.
5. A legend is a box that identifies the patterns or colors that are assigned to the data series or
categories in a chart.
6. A chart title is the descriptive text for a chart. Note that horizontal and vertical axes usually
have titles too.

8
Introduction to Computing LAB 5: Introduction to MS excel

Lab Task

Lab Task. 5.1 ) Create a table with formulae using MS Excel as follows:

Lab Task. 5.2 ) Create a marks sheet on MS Excel, which calculates the total, percentage and
average of 5 courses of a student. Also create a chart to display the result in graphical form

Note: Attach all printout with your lab manual