MS Excel – Theory and Practicals

Get to learn ms Excel step by step for free. Both theory and practical is covered. Good explanation and on point instructions. Try this tutorial today.

MICROSOFT EXCEL

This application software is classified as a SPREADSHEET. It is ideal in the mathematical and
analytical calculation.
Another example of a spreadsheet is
Lotus 1-2-3
Spreadsheets specialize in:



  • Data entry
  • Mathematical manipulation of data
  • Graphical representation
  • Elementary database management
  • Data analysis.

The features of the excel screen are the same as those in Ms- Word except for the
following:

  • Workbook title (Filename)
  •  Worksheet tabs
  • Row headers
  • Column headers
  • Formula bar
  • A girded window


Apart from the above features, other features are:

  • Menu Bar
  • Toolbars
  • Scroll bar
  • Status bar
LOADING (STARTING) MICROSOFT EXCEL

The methods are the same as those of Ms-Word.
From the programs menu, click on Ms-Excel to load it.



DEFINITIONS

  • Columns – Vertical Partitions
  • Rows – Horizontal Partitions
  • Cell – The intersection between a column and a row.
  • Cell addresses – this gives the reference to a particular cell
  • Cell Section / Selector – a thick border/frame rectangle around a cell.
  • AutoFill handle – a square at the bottom right-hand corner of the cell selector
  • Cell pointer – The thick white cross moving around the worksheet with the direction of the mouse
Navigating the Worksheet


  • Moving through the worksheet we can use:
  • Arrow keys. – We use this to move towards the direction of the arrow.
  • Mouse. – Click on any particular cell where you want to move to; the cell selector will be shifted to it.
  • Ctrl+Home – Takes you to the 1st cell in the worksheet.
  • Ctrl+End-This takes you to the last edited end in the range.
  • Ctrl+Arrow right- Takes you to the last column in the worksheet.
  • Ctrl+Down- Takes you to last row in the worksheet.
  • Click and hold down the left mouse button and then drag the pointer over the range of cells.
  • Click on the 1st cell in the range then hold down the shift key and click on the last cell in the range.
  • Again Click on the 1st cell in the range and hold down the shift key and move through the range of cells using the arrow keys.
  • Also, Click on the row header to select the whole row or click on the column header to select the whole column.
  • Then Click on the junction of the row and column header to select the whole worksheet, to make a non-continuous selection.
  • Hold down the Ctrl key then select each range by holding and dragging the cell pointer through it.


RE-NAMING OUR WORKSHEET
  • Double click on its sheet tab then type the new name.
    Or
  • Right-click on the worksheet tab
  • Click Rename
  • Type the desired name and press enter
TO COPY / MOVE WORKSHEET
  • Highlight text range
  • Click on edit
  • Click on copy or move worksheet
  • Indicate the new position and whether you want to make a copy or move the
    whole worksheet
  • Click OK.


INSERTING A WORKSHEET
  • This is on insert, then worksheet
DELETING A WORKSHEET

N/B This command is not reversible;

  • Click on edit then delete worksheet.Or
  • Right click on the worksheet tab
  • Click Delete and Ok
a) EDITING

Copying and Pasting

  • Select the data to be copied
  • Click on edit then copy.
  • Click on the destination then (the first cell)
  • Click on edit & Click on paste.


Moving data.
  • Select the data
  • Click on edit then cut
  • Click on the destination
  • Click on edit then paste.
    N/B In case you want to link the destination to the source during copying use paste
    special instead of paste. Then in the dialog box use paste link.
DATA ENTRY

There are three main categories of data that you can enter in our worksheet:

  • Labels, e.g. name,
  • Values, e.g. 20,
  • Formula e.g. =sum(b1:b8)


Other types of entries include the following: –

1) PICTURES
  • Click on insert
  • Point to picture
  • Select the source
  • Select the picture
  • Click on insert or OK.

To view your comment, you can point to the triangle at the top right-hand corner of the cell.

ADDING AND REMOVING WORKSHEET PARTS


a) To insert a Column

  • Click on insert, then column.

b) To insert a Row

  • Click on insert then rows.

c) To insert a Cell

  • Click on insert then cells, indicate the direction of cells and click Ok.


DELETING CELLS

a) Click on edit then delete
b) Indicate direction of cells, Click OK.
N/B: That will delete the selected cells only.

DELETING ROWS OR COLUMNS.
  • Click edit then delete
  • Indicate whether to delete the whole row or whole column
  • Click OK.

1) Adding and removing toolbars

  • Click on viewpoint to toolbars.
  • Click on the toolbar that you would like to add or remove


2) Adding or removing formula bar and status bar

  • Click on view
  • Click formula bar
HIDING / DISPLAYING

To hide a row

  • Select Row
  • Click on format
  • Point to row
  • Click hide; this will hide the selected row.


To hide a Column

  • Select Column
  • Click on format
  • Point to Column
  • Click hide; this will hide the selected column

To unhide a Row or Column

  • Select Row or Column
  • Click on format
  • Point to Row or Column
  • Click unhide; this will hide the selected row or column.

Adjusting row height

  • Click format
  • Point to row
  • Click on row height
  • Adjust the height
  • Click OK


You adjust a row height by pointing in between the row headers and clicking and
holding down the mouse pointer and dragging alternatively can click on the format then point
to row then click on AutoFit selection.

FORMATTING

You can hide a column by:

  • Click on format then point to column
  • Click on hide

The point between the column headers until the mouse pointer becomes a resize pointer then
click and hold down and drag.

  • Click on format
  • Click on column


Formatting cells

Under cell formatting, we can format the following:

  • Number
  • Currency
  • Date & Percentage

We can also adjust alignment, font, and borders

NUMBER

Formatting numbers is used to:

  • Set dates
  • Currency styles
  • Percentages
  • Fractions e.t.c.


ALIGNMENT.

We can adjust horizontal alignment, Vertical, and Orientation.
FONT.
We use this command to adjust font Attributes, i.e. Font type, Style, Color, Size, Underline
etc.

BORDERS.

We use this command to adjust Border style, Color, and Thickness.
PATTERN
This command is used to adjust or add colors and pattern shadings to the selected cells.
Inserting.



AUTO FILLING A FUNCTION

Select the cell, which it is, contained (function).
Point to the autofill handle click, hold down and drag in the desired selection and
INSERTING CHARTS/GRAPHS
This command is found in the insert menu. It is used to convert the tabulated data into a
chart

  • Select the range of data to be plotted, including the labels.
  • Click on insert
  • Click on chart
  • Answer the question in the chart wizard by making the desired selected and
    entries until you finish.


To change the currency symbol.

 

  • Select the range to be formatted
  • Click on format then click on cells.
  • Pick the number tab
  • Click on currency in the category
  • Click on custom in the category
  • In the text box with the word ‘TYPE’ replace the current symbol with the
    desired one, make sure your new entry is enclosed in quotation marks “Kshs.”

Visit here:- Ms Word – Tutorials and Practicals

AUTO-FILLING SERIES (COPY COMMAND)

 

  • Make the first entry in the series
  • Select those entries
  • Auto-fill in the desired direction


SORTING
This command is found under data menu. It is used to arrange entries in ascending or
descending order.

  • Click on data
  • Click on sort
  • Select the column to sort by indicating the sort order.
  • Click Ok.
FILTER

It is found under data menu, and it is used to select the records from the database

  • Click on data
  • Point to filter and click on auto filter
  • Click on the filtering button on the desired column
  • Click on custom
  • Complete the dialog box
  • Click Ok.


GOAL SEEK

This command is used to adjust answers to the desired value by adjusting the results

  • Select the result that you want to adjust
  • Click on tools
  • Click on goal seek
  • Enter the value that you want the cell to assume
  • Enter the precedent or the cell to be adjusted and Click Ok.


RENAMING A SHEET

  • Right-click on the sheet
  • Click on rename
  • Type the desired name
  • Press enter key

DELETING A SHEET

  • Right-click on the sheet
  • Click on delete
  • In the dialogue box, click ok

THE END