Home / Microsoft Excel

Microsoft Excel

MICROSOFT EXCEL

DAY ONE

Ms Excel  is a spreadsheet program designed to word with predominantly numeric data and support performance of advanced mathematical, scientific, financial e.t.c

Workbooks – in Microsoft excel a work book is a file in which you work and store your data.

Because each work book contains many sheets you can organize various kinds of information in a single file
Worksheet-use worksheet to list and analyze data. You can enter and edit data on several worksheets simultaneously and perform calculations based on data from multiple worksheets. When you create a chart you can place the chart on the

worksheet with its related data or on a separate chart sheet. Cell-an intersection between a column and a row

Range– is a group of neighboring cells or a block of cells

Starting and exiting Ms Excel

Method I

i.)        Click on the Start Button

ii.)        Point on All Programs

iii.)        Click Ms Excel

Method II

i.)        Click on the Start Button

ii.)        Click Run

iii.)        Type excel then click Ok

Parts of an excel window

–     Microsoft office button

–     Quick access toolbar

–     Title bar

–     Ribbon

–     Ruler

–     Sheet area

–     Sheet tab

–     Formula bar

–     Vertical and horizontal scroll bars

–     Status bar

Using excel help

i.)        Click on the Help command button at the far right side of the

ribbon

ii.)        Type your question

iii.)        Click Search

iv.)        Choose a topic

Identifying cells and ranges by their respective addresses

By default excel uses the A1 reference style, which refers to columns  with letters (A through IV, for a total of 256 columns) and refers to  rows with numbers (1 through 65536). This letters and numbers are
called row and column headings. To refer to a cell enter the column letter followed by the row number. For example, D50 refers to a cell that is at the intersection of column D and row 50. To refer to a
range of cells, enter the reference for the cell in the upper left corner of the range,  a colon (:) and the reference to the cell in the lower right corner of the range

Select text, cells, ranges, rows, columns &sheets

Text in cell If editing in cell is turned on, select the cell, double click in it, and then select the text in the cell.

If editing in a cell is turned off, select the
cell and then select the text in the formula
bar.

A single cell Click the cell, or press the arrow keys to move the cell.
A range of cells Click the first cell of the range and then drag to the last cell.
All cells on a on a worksheet Click the select all button
Nonadjacent cells or cell ranges Select the first cell or range of cells then hold down the Ctrl key and select other cells or ranges.
A large range of cells Click the first cell in the range and then hold down shift and click the last cell in the range. You can scroll to make the last cell visible.
Select a sheet Click on the sheet tab
A entire row Click the row heading
An entire column Click column heading

Enter number, text, a date or a time

i.)        Click the cell you want to enter the data

ii.)        Type the data and press enter or tab

iii.)        Use a slash or hyphen to separate the parts of a data; for example type 18/1/1992 or 18-dec-1988

Edit cell contents

i.)        Double click the cell that contains the data you want to edit

ii.)        Make any changes to the cell contents

iii.)        To enter you changes press enter

iv.)        To cancel you changes press esc

Create a new workbook

i.)        On the Office button click New

ii.)        On New Workbook select Blank Workbook the click Create

N/B; to create a new work book based on the default workbook template click new

Save a new unnamed workbook

i.)        On the Office button click Save Us

ii.)        In the save in list select the drive and folder you want to save your workbook

iii.)        In the File Name box type the name of your workbook

iv.)        Click Save

v.)        Save an existing workbook click Office button then select Save

Open a workbook on your hard disk or network

i.)        Click the Office button

ii.)        Select Open or click open        on the quick access tool bar.

iii.)        In the look in list click the drive or folder

iv.)        Double click the workbook you want to open

 

DAY TWO 

Formatting text in cells

i.)        Select whole cells or the specific text in a single cell that you want to format

ii.)        click on the Home tab

iii.)        On the Font group choose the font type, style, size, underline, style, font color e.t.c.

Center, align, indent , rotate data in a cell

In cell that have the default general format, text data is left

aligned and numbers and dates are right aligned changing the

alignment does not change the type of data. To center align data that spans several columns of rows such as column and row
labels. You can merge a selected range of cells and align cells within the resulting merged cell.

i.)        Select the cells you want to format

ii.)        On the on the home tab alignment group, click Align

Text Left for left alignment, Align Text Right for right alignment, Center for center alignment.

Rotate text in cell

i.)        Select the cells in which you want to rotate the text.

ii.)        On the Home tab Alignment group select Orientations

iii.)        In the Orientation drop down menu, click a degree point, or drag the indicator to the angle you want.

Align data at the top center or bottom of a cell

i.)        Select the cells you want to format

ii.)        On the Home tab Alignment group select  Top Bottom or Middle Align

Merge cells to span several columns or rows

i.)        Select more than one cell you want to Merge

ii.)        On the Home tab Alignment group select Merge &Center

iii.)        Select Merge cells

Insert blank cells

i.)        Select cells were you want them inserted and select the same number of cells as u want inserted

ii.)        On Home tab Cell group select Insert

iii.)        Select Cells Insert rows

Insert rows

i.)        Select rows were you want them inserted and select the same number of rows as you want inserted

ii.)        On Home tab Cell group select Insert

iii.)        Select Insert Sheet Rows

Insert columns

i.)        Select columns were you want them inserted and select the same number of columns as u want inserted

ii.)        On Home tab Cell group select Insert

iii.)        Select Insert Sheet Columns Change column width

Change column width

i.)        Drag the boundary on the right side of the column heading until the column is the width you want.

ii.)        To change the column width of multiple columns select the columns then drag on the right side of the column heading until the column is the width you want.

iii.)        To change the boundary of all the column select all columns then drag the column to the width you want.

iv.)        To manually change the width select the column click home

tab and on cells select format on the drop down menu select

column  width and type an appropriate width click ok

Apply borders to cells

i.)        Select the cells you want to apply borders to

ii.)        On Home tab Font group click on Bottom Border then

choose an appropriate border from the drop down menu

Shade cells with solid colors

i.)        Select the cells you want to shade

ii.)        On Home tab Font group click on fill color

iii.)        Select an appropriate color

Hide/unhide rows or column

i.)        Select the row or column you want to hide

ii.)        On Home tab Cells group click on Format

iii.)        Select Hide & Unhide

iv.)        Select Hide Rows to hide a row

v.)        Select Hide Columns to hide a column

Copy format from one cell or range to another

i.)        Select the cell or rage that has the formatting you want to copy

ii.)        On Home tab Clipboard group select Format Painter Tool

iii.)        Drag your pointer to the range or cell to be copied to

 

DAY THREE 

Automatic filling of data in adjacent cells          

You can fill in several types of series by selecting cells and dragging the fill handle e.g.

Initial selection                              Extended series

1,2,3                                                    4,5,6

9:00                                                     10:00, 11:00, 12:00

Mon                                                    Tue, wed, Thu

Monday                                             Tuesday, Wednesday, Thursday

Jan                                                       Feb, Mar, Apr

Jan, Apr                                             Jul, Oct, Jan

Text 1, text A                                   Text2, text B, text3, text C

1st period                                           2nd periond,3rd period ,4th period

1999,2000                                          2001,2002, 2003

Note; you can create a custom filled series for frequently  used text entries, such as your company sales regions

Add a comment to a cell

i.)        Select the cell you want to add a comment to

ii.)        Right click on the cell

iii.)        Click Insert Comment

Edit comment

i.)        Select the cell you want to edit a comment to

ii.)        Right click on the cell

iii.)        Click Edit Comment

iv.)        Type appropriate comment

Add or remove currency symbol

i.)        Select the cells containing the numbers

ii.)        In the Home tab Number group select Accounting Number Format

iii.)        Select an appropriate currency format for your numbers

iv.)        To remove the currency symbol

v.)        Select the cells containing the numbers

vi.)        In the Home tab Number group click Number format

vii.)        Select General

Increase or decrease the number of a decimal place shown

i.)        Click on the cells containing the numbers

ii.)        On Home tab Numbers group select increase decimal for

more decimal places and decrease decimal for few decimal places

Move or copy cell data

i.)        You can copy a cell by dragging or by clicking cut or copy or

paste. Microsoft excel copies the entire cell including

formulas, and their resulting values comments and their cell formats

N/B; Microsoft excel copies hidden cells. If the paste area is hidden u may need to unhide the area to see all of the copied cells.

Move or copy whole cells

ii.)        Select cells you want to move or copy

iii.)        Point to the border of the selection

iv.)        To move the cells, drag the selection to the upper-left cell of

the paste area

v.)        Microsoft excel replaces any existing data in the paste area

vi.)        To copy cells hold down Ctrl as you drag

vii.)        To insert the cells between existing cells hold down SHIFT (if

moving) or shift + ctrl (if coping as you drag.)

viii.)        To drag the selection to a different sheet hold down Alt

N/B: to move your selection to a different work book or a long distance use the copy cut and paste commands

Undo mistakes

i.)        To undo the last action, click undo on the Quick Access bar

ii.)        To undo a series of actions click the arrow next to Undo and

select from the choices below it.

 

DAY FOUR  

 Inserting  a sheet

Method 1

i.)        click the Home tab

ii.)        on the cell group click the Insert command

iii.)        select Sheet

method 2

i.)        right click the Sheet Tab

ii.)        choose Inset from the popup menu

iii.)        on the insert dialogue choose Worksheet

Renaming Sheets

Method 1

i.)        click the Home tab

ii.)        on the Cell group click the Format command

iii.)        select Rename sheet

iv.)        type the name you want then press enter

method2

i.)        double click the Sheet tab

ii.)        type the name you want

iii.)        press enter

method3

i.)        right click the Sheet tab

ii.)        select Rename

iii.)        type the name you want

iv.)        press enter

Deleting unwanted sheets

i.)        click the Home tab

ii.)        on the Cell group click the Delete command

iii.)        select Delete Sheet

Moving or copying sheets

i.)        click the Home tab

ii.)        on the Cell group click the Format command

iii.)        select Move Or Copy sheets

iv.)        on Move Or Copy select the Before Sheet

v.)        check Copy to copy the sheet

vi.)        click Ok to save changes

Changing the sheet background

i.)        Click on Page Layout tab

ii.)        On Page Setup group select Sheet Background

iii.)        Choose an appropriate image

iv.)        Click Ok

Hiding and unhiding sheets

i.)        click the Home tab

ii.)        on the Cell group click the Format command

iii.)        select Hide Sheet

to unhide

i.)        click the Home tab

ii.)        on the Cell group click the Format command

iii.)        select Unhide Sheet

iv.)        on Unhide select the sheet you want to unhide

v.)        click Ok to effect the changes

 

DAY FIVE  

Insert pictures in a sheet

i.)        Select the cell you want to insert

ii.)        Click the Insert tab

iii.)       On Illustration group select Picture

iv.)        Choose a picture of your choice

v.)        Click Ok to effect the changes

Inserting word art

i.)        Select the cell you want to insert

ii.)        Click the Insert tab

iii.)        On Text group select Word Art

iv.)        Select and appropriate Word Art

v.)        Type appropriate words

vi.)        Click Ok

Inserting shapes

i.)        Select the cell you want the shape

ii.)        Click the Insert tab

iii.)       On Illustration group select Shapes

iv.)        Click on the shape you want

v.)        Drag to draw

Creating hierarchy charts

i.)        Select the cell you want the chart

ii.)        Click the Insert tab

iii.)       On Illustrations click SmartArt

iv.)        On Choose A Smart Graphic select Hierarchy

v.)        Select an appropriate chart

vi.)        Click Ok

vii.)        Type appropriate details e.g.  a C.E.O and below you can have his/her juniors and co-workers

Inserting embedded objects

i.)        Click Insert tab

ii.)        On Text group select Object command

iii.)        Select the object type e.g. bitmap e.t.c

iv.)        You can insert or create a new one

v.)        Click Ok to insert

 

DAY SIX

Definition of terms

Calculations

Is the process of computing
formulas and then displaying the results as values in the cells that contain the formulas.

Formulas are equations that perform calculations on value in your worksheet. A formula starts with an equally sign (=). For example the following formula multiplies 2by 3 and then adds  five to the result.

=5+2*3

Function – A pre-written formula that takes a value or values, perform an operation and then returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.

Using Excel Formulas and Calculations

Arithmetic operators.  To perform basic mathematical operations
such as addition, subtraction, or multiplication; combine numbers;
and produce numeric results, use the following arithmetic operators.

Arithmetic operator                            Meaning (Example)

+ (plus sign)                                            Addition (3+3)

-(minus sign)                                          Subtraction (3-1)

Negation (-1)

*(asterisk)                                               Multiplication (3*3)

/(forward slash)                                    Division(3/3)

%(percentage sign)                             Percent (20%)

^(caret)                                                    Exponential(3^2)

Additions, subtractions, division , multiplication e.t.c
Ø  Using excel functions e.g. the if sum max, min , average,

count

Ø  Copying formulas to other cells

Ø  Using the now function to enter date and time Ø  Checking for precedents and dependents

 

DAY SEVEN 

Inserting charts in a worksheet

i.)        Select a range that will supply values to your charts

ii.)        Click on the Insert tab

iii.)        On the Chart group select  Column, Pie, Line, Bar, Area, Scatter Or Others

iv.)        Select an appropriate chart on the drop down menu

Resizing and moving a chart and its contents

i.)        Click on the chart

ii.)        Position the pointer on the resize handles

iii.)        Drag to increase and reduce size

iv.)        Release the mouse button

v.)        To move the chart, position the pointer within the chart area then drag and drop In a different location.

Formatting your charts

i.)        Right click on a given element of the chart e.g. chart area, legend , plot area, data series, chart title etc.

ii.)        Choose format item option

iii.)        Select appropriate fonts under the font tab and patterns under the pattern tab.

Changing from one chart type to another

i.)        Click the chart to select it

ii.)        Click on the Insert tab

iii.)        On the chart group select click on the chart you want to change e.g.  Pie Chart

 

DAY EIGHT

Definition of terms

Ascending – arranging numbers and alphabets from lowest to highest

Descending – to arrange numbers and alphabets from highest to lowest

Sorting data in an excel workbook

i.)        Select the range of data to be sorted

ii.)        Click the Data tab

iii.)        On sort and filter group select sort form lowest to largest or sort from largest to lowest

Filter data using auto filter

i.)        Select the data to be filtered

ii.)        Click the Data tab

iii.)        On Sort & Filter Group select filter command

Removing auto filter

i.)        Select the filtered data

ii.)        Click the Data tab

iii.)        On Sort & Filter group click Filter command

 

DAY NINE

Importing and exporting object and text

Importing

i.)        Open a source program e.g. word program

ii.)        On word cut or copy the information e.g. text or graphic

iii.)        Open excel program

iv.)        Select a cell

v.)        On the Home tab, Clipboard group select Paste

Exporting

i.)        Select the information in a given range

ii.)        On the home tab clipboard group select cut

iii.)        Open the destination program e.g. word

iv.)        Right click on the text area to paste

Page setup

i.)        Click the page layout tab

ii.)        On page setup group select the page orientation, page size, page margins e.t.c by clicking on the commands then selecting an appropriate size from the drop down menu

iii.)        N/B: you can preview your work at this stage by clicking on the page setup launcher on the page setup group.

Printing a work sheet

i.)        Ensure that the worksheet is open

ii.)        Click on the Office button

iii.)        Select the Print option

iv.)        Choose Quick Print for direct printing

v.)        Choose Printer Options to set further settings e.g. number of copies to be printed, page range e.t.c

vi.)        Click the Preview button to preview your work before printing

vii.)        Click Print to obtain hard copies

 

Top