learn microsoft excel

Microsoft Excel for Beginners Bori

Mobile:- +2348035997597 1

Microsoft ExcelM Congratulations! Now that you are done learning Microsoft word. The next stage will be to learn Microsoft excel. Microsoft excel is used by;

1. Accountant in Banks to calculate money 2. Store keepers to calculate stock 3. Engineers to do scientific calculation 4. Traders 5. Production/ Manufacturing companies to take record of goods produced. 6. Military to take records of their ammunitions.

For you to use Microsoft excel very well, you need to know how to use and manipulate a table. You also need to know simple mathematics. As you must have known by now, Microsoft word is basically use to type or prepare documents. But when it comes to handling numbers, one of the best applications to use is Microsoft Excel. Microsoft Excel is a general-purpose electronic spread sheet used to organize, calculate, and analyze data. The task you can complete with Excel ranges from preparing a simple family budget, preparing a purchase order, create an elaborate 3-D chart, or managing a complex accounting ledger for a medium size business.

TO LAUNCH EXCEL:TO LAUNCH EXCEL:TO LAUNCH EXCEL:TO LAUNCH EXCEL:

Click on the Start button Move up to Programs Across to select Microsoft Excel

How to start Microsoft Excel.


Mobile:- +2348035997597 2

BASICSBASICSBASICSBASICS An active cell is outlined in black and the location appears in the Name box (D2 is active).

Before entering data, you must first tell the computer which cell should be receiving the information. Only active cells receive information. An active cell has a thick black border around it (called the cell selector). Notice that the cell selector moves from cell to cell when you press any of the four arrow ( ) keys.

Each worksheet is composed of rows (numbered) and columns (lettered).

Name Box


Mobile:- +2348035997597 3

CELLSCELLSCELLSCELLS The intersection of each row and column creates a cell. The cell is identified by its location on the worksheet (i.e. D3).

CELL REFERENCESCELL REFERENCESCELL REFERENCESCELL REFERENCES Cell references identify individual cells, rows, and/or columns in a worksheet. They tell Excel where to look for values to use in a formula.

CUTTING AND PASTINGCUTTING AND PASTINGCUTTING AND PASTINGCUTTING AND PASTING CELLSCELLSCELLSCELLS If you wish to move the data contained in the A column of your spreadsheet to column C, you highlight the A column, cut (Ctrl-X) the information, and paste (Ctrl-V) it in the C column.


Mobile:- +2348035997597 4

CHANGING THE WIDTH OR HEIGHT OF A COLUMN OR ROWCHANGING THE WIDTH OR HEIGHT OF A COLUMN OR ROWCHANGING THE WIDTH OR HEIGHT OF A COLUMN OR ROWCHANGING THE WIDTH OR HEIGHT OF A COLUMN OR ROW At some point, your data may exceed the width allowed by the cells. In such a case, you may need to lengthen (or reduce) the width of a column. For example: a column in your spreadsheet is designated for the addresses of everyone in your history class. It is likely that the cell is too narrow to show the entire address. Although the entire address is stored in the cell, you may not be able to visibly see all of the information at a glance. 1. To change the length of a column, bring the mouse cursor to the grey vertical line

between the lettered cells (A, B, C, etc.) of the column you wish to extend (or shorten). Youll notice the mouse pointer will change to the resize cursor .

2. Hold down the left mouse button and move the cursor to the right or left to increase or decrease the width. In the example below, we see the spacing has now been resized to 132 pixels. 3. To change the height of a row, bring the mouse cursor to the grey horizontal line between the numbered cells (1, 2, 3, etc.) of the row you wish to heighten (or reduce).

USING THE FILL HANDLEUSING THE FILL HANDLEUSING THE FILL HANDLEUSING THE FILL HANDLE The fill handle is a tool used to fill a selected range with formulas, values, or labels. The fill handle saves time by automatically filling cells with information. It can be used to duplicate a pattern or series. For example: If you wish to have your columns headed by the months of the year, you can type January in one cell, then use the fill handle to fill in the remaining eleven months.

FORMATTING TEXT, NUMBERS, AND CELLSFORMATTING TEXT, NUMBERS, AND CELLSFORMATTING TEXT, NUMBERS, AND CELLSFORMATTING TEXT, NUMBERS, AND CELLS

Selecting Rows or Columns: To select an entire row, click on the number of the row in the gray column on the left. To select an entire column, click on the letter of the column in the gray row at the top.


Mobile:- +2348035997597 5

Formatting Text: Font/Point Size/Bold/Underline- Once the desired cell(s) have been selected, click the appropriate formatting button in the toolbar at the top of the screen to alter the appearance of the text in that cell(s).

Subscripts/superscripts- To add subscripts or superscripts to a cell, highlight the letter/number in the formula bar. Under the Format menu, select Cells. Check the box for superscript or subscript as desired.

Alignment- Left/Center/Right: To change the default horizontal alignment of a cells

contents, select the cell(s) or row(s)/column(s), and click the appropriate alignment button on the tool bar.

Top/Center/Bottom: To change the default vertical alignment of a cells contents, select the cell(s). Under the Format menu, select Cells. Click on the Alignment tab, and choose the desired alignment from the Vertical drop down menu.

Wrapping text- In some cases, the content in a cell will exceed the amount of space available. If there is extra text, only the beginning of the text will be visible. If a number is too long to fit, the cell will display #####, rather than the number. To fix this problem


Mobile:- +2348035997597 6

you could increase the width of the column (see Formatting Cells), or you can format the cell to allow the text to wrap onto a second line within the cell.

Select the cell(s) that need to have text on multiple lines. (Entire columns or rows can also be selected.)

Under the Format menu, select Cells.

Click on the Alignment tab. Check the box for Wrap Text.

Merging and Centering cells- Sometimes text needs to be centered over multiple columns. Rather than trying to guess which column is the central one and typing the text in that columns cell, the entire set of cells in that row can be combined.

Select the cells. Click the Merge and Center button on the toolbar. The same process can be done via the Alignment window by checking the Merge cells box and horizontally centering the text. .

Formatting Numbers: Number of decimal places- Select the

cell(s). Under the Format menu, select Cells and click on the Number tab. Click on Number to see the decimal place options.

Decimal places alignment- Since Excel does not retain zeros at the end of a number (i.e. 2.0), It can be difficult to line up a column of numbers with inconsistent numbers of decimal places. To increase or decrease the number of decimal places for a column (and thereby unify the number of decimal places), select the column and click on the appropriate button in the toolbar.

Special notation (currency, dates, etc.)- Additional formatting options for currency ($), times, dates, zip codes, social security numbers, and phone numbers are found under the Format menu, Cells, and Number.

Increase decimals Decrease decimals


Mobile:- +2348035997597 7

Formatting Cells: Column width/Row height- To expand or contract the width of a column, select

the column. Placing the cursor in the gray row at the top of the column, drag the right or left edge to make it wider or narrower. Double-clicking on this edge will automatically adjust the width to fit the widest content in that column. Row height can be altered in the same manner, dragging or double-clicking on the top or bottom edge in the gray column on the left side of the worksheet.

Borders and Patterns- Borders can be placed around a cell or group of cells using the border option. Under the Format menu, select Cells. Select Border. The number of sides and the type of line can be selected from this window.

Patterns and colors are also available in the adjacent tab. These options should be used cautiously as it can be difficult to read data when the cell also contains a color or pattern.


Mobile:- +2348035997597 8

EXERCISE 1EXERCISE 1EXERCISE 1EXERCISE 1

Use Fill handle to create the calendar below.


Mobile:- +2348035997597 9


Mobile:- +2348035997597 10


Mobile:- +2348035997597 11

S/No. Name of Student

Reg

.

No

.

Eng

Ma

ths

BK.K

Lit

Eco

n

Tota

l

Av

g

1. Sunny Ade 001 54 25 73 62 54 269 53.8 2. Luke Judith 002 18 28 25 39 45 157 31.4 3. Mienye J. 003 36 65 26 12 15 157 31.4 4. Sese, Ebi 004 36 35 53 57 16 201 40.2 5. Polo, Eres 005 65 63 64 63 65 325 65 6. Meb, Tonye 006 32 60 47 65 55 265 53


Mobile:- +2348035997597 12


Mobile:- +2348035997597 13


Mobile:- +2348035997597 14

USING FORMULAS USING FORMULAS USING FORMULAS USING FORMULAS Excel Functions are preprogrammed formulas that make the task of writing complex formulas easier. There are a total of 333 functions in Excel. Excel allows you to assign formulas to cells to calculate values. To build a formula, you must first designate which cell(s) the formula should affect. Formatting can also apply to rows or columns. To format a cell using a formula, select the cell to be formatted. Type an equals sign (=) (This tells the computer it is about to calculate a value.), followed by the formula. An example of this would be to sum a group of cells in a column and provide a total in an empty cell. Here we see a simple SUM formula (lower left) that calculated all the values for cells B1 through B7 and the process of entering a PRODUCT formula (lower right).

Formulas can use standard arithmetic operations, values, cell addresses, and cell names. Arithmetic operations are used to carry out addition, subtraction, multiplication, and division. The table below lists the keys used for arithmetic functions.

For exponent functions, use the carat key (^) followed by the exponent. For example: 82 is entered as 8^2 For exponents, use the capital letter E. For example: 7 1024 would be entered as 7E24. In using complex formulas, it is important to know that Excel follows algebraic rules regarding parentheses and order of operation. Below are some examples of formulas you would use to carry out arithmetic functions.


Mobile:- +2348035997597 15

Arithmetic operator Meaning Example

+ (plus sign) Addition 3+5

(minus sign) Subtraction

Negation

71

3

* (asterisk) Multiplication 3*8

/ (forward slash) Division 6/3

% (percent sign) Percent 40%

^ (caret) Exponentiation 3^2

Comparison operator Meaning Example

= (equal sign) Equal to A1=B1

> (greater than sign) Greater than A1>B1

< (less than sign) Less than A1= (greater than or equal to sign) Greater than or equal to A1>=B1


Mobile:- +2348035997597 16

Remember to precede any formula with an equals sign. 2+2 =2+2 682 =6 + (8^2) (89+67)

=(89+67) + (12/35)3

For example For example For example For example

Lets say you were sent to purchase some item in the market as illustrated below.

If you look at cell F4, what we expect to have is 5 120 as amount for polish. Cell D4 has 5, while E4 has 120 Therefore, the formular you will enter in cell F4 will be =D4 E4. Press Enter key Once you have done that, you fill down

To calculate the Total, you enter =SUM(F4 : F10)

The word SUM, is called an excel function. While :, represent range.


Mobile:- +2348035997597 17

Excel has so many functions, and they are very useful Note: If you wanted to add the values of cells F4 through F10, you could type the formula

= F4+F5+F6+F7+F8+F9+F10 A shorter way would be to use the SUM function and simply type =SUM(F4:F10).

The following table contains the definitions and examples of several other available functions:

The Autosum feature allows you to quickly generate descriptive statistics for a range of data.


Mobile:- +2348035997597 18

The basic Autosum functions include:

Troubleshooting Error Messages When Using Formulas and Functions

Other commonly used excel functions are Counting Functions There are four functions which let you count up the number of cells matching certain criteria: COUNT tells you how many cells contain numbers COUNTA tells you how many cells are not empty


Mobile:- +2348035997597 19

COUNTBLANK tells you how many cells are empty COUNTIF tells you how many cells match a certain criterion

Logical Functions IF gives you alternative results depending on a condition AND allows you to check several criteria are all true OR checks whether any criteria are true NOT changes true to false and vice-versa

IF Function Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE. Use IF to conduct conditional tests on values and formulas.

Syntax IF(logical_test,value_if_true,value_if_false)

Logical_test is any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.

IF function The IF function as the name implies is a conditional function. When use in an excel formula, enables you to get results base on the condition on ground.

A B

1 Actual Expenses Predicted Expenses

2 2000 1000

3 600 1000

4 600 1000

Formula Description (Result)

=IF(A2>B2,"Over Budget","OK") Checks whether the first row is over

budget (Over Budget)

=IF(A3>B3,"Over Budget","OK") Checks whether the second row is over

budget (OK)


Mobile:- +2348035997597 20

Let say you are working on list of admitted student from JAMB. And you want to use excel to type their name and their school fees according to idigiens and non-indigenes

From the above diagram, to make use of the IF function to automatically assign fee to students (25,000 for Rivers Student and 35,000 for non-Rivers). We use the formula; =IF(G11=rivers,25000,35000)

Other use of IF function, is in grading system/result. Lets say in a school result, its expected that grade should be allocated to students as follows Scores Grade 75 100 A 70 - 74 AB 65 - 69 B 60 64 BC 55 59 C 50 54 CD 45 49 D 40 44 E Below 40 F

To fill in the grade for the first student, before filling it into other, we simply type the formula in cell G11 =IF(F11>=75,"A",IF(F11>=70,"AB",IF(F11>=65,"B",IF(F11>=60,"BC",IF(F11>=55,"C",

IF(F11>=50,"CD",IF(F11>=45,"D",IF(F11>=40,"E","F")))))))) Note that cell G11 is where the grade will appear. While in the formula, cell F11 is where we have to Total for each subject.

Microsoft Excel provides additional functions that can be used to analyze your data based on a condition. For example, to count the number of occurrences of a string of text or a number within a range of cells, use the


Mobile:- +2348035997597 21

COUNTIF and COUNTIFS worksheet functions. To calculate a sum based on a string of text or a number within a range, use the SUMIF and SUMIFS worksheet function. Example the COUNTIF function

A B 1 Salesperson Invoice 2 Buchanan 20,000 3 Buchanan 10,000 4 Suyama 9,000 5 Suyama 30,000 6 Buchanan 6,000 7 Dodsworth 24,000 Formula Description (Result) =COUNTIF(B2:B7,">10000") Numbers above 10000 (3) =COUNTIF(B2:B7,"


Mobile:- +2348035997597 22

Example OR function- Use when the result relies on a Logic condition. It can also be combined with other functions, such as IF and SUM. The OR function determines if a condition is True or False. If student is a Rivers OR Bayelsan, then True, else False Eg. = OR(State= Rivers, State = Bayelsan)

1. =IF(OR(State=Rivers,State=Bayelsa),pay bursary,not qualified) The above formula determines if a student will be paid bursary on the condition that the student is either from Rivers OR Bayelsa.

EXAMPLE Below is a table created with the help of Nested IF, OR with AND Functions.

The formula is as illustrated below. Try and create the same table as shown

below.

Formula Instruction

Fees = IF(OR(State="RIVER", State ="BAYELSA"),25000,35000)

Hostel =IF(AND(State="RIVERS", Sex="F"),"FREE","PAY")

Bursary =IF(OR(State="RIVERS", State ="BAYELSA"),"QAULIFIED","NOT QUALIFIED")

DATE: 5TH OCTOBER, 2011

S/N NAME SATE OF ORIGIN SEX AGE FEES HOSTEL Bursary

1 AJUKWUM, Stanley E. RIVERS M 19 35000 PAY QAULIFIED

2 CHIDI Oguguo RIVER M 19 25000 PAY NOT QUALIFIED

3 CLETUS Aniefiok U. LAGOS M 18 35000 PAY NOT QUALIFIED

4 EBIRIKA Stephen C KADUNA M 19 35000 PAY NOT QUALIFIED

5 EDWARD Victor S. RIVERS F 20 35000 FREE QAULIFIED

6 ELEBOR Douglas BAYELSA F 22 25000 PAY QAULIFIED

7 AMBUK ARINAM E. DELTA F 20 35000 PAY NOT QUALIFIED

8 EZENWA Sabinus O KANO F 21 35000 PAY NOT QUALIFIED

9 INAKAN Emmanuela LAGOS M 23 35000 PAY NOT QUALIFIED

10 IREN Monday A OYO F 19 35000 PAY NOT QUALIFIED

11 ISAIAH Samuel B RIVERS M 19 35000 PAY QAULIFIED

12 JUMBO Yerebabari RIVERS F 18 35000 FREE QAULIFIED

13 KALAGBOR Charles J ONDO M 20 35000 PAY NOT QUALIFIED

14 KIRI Igbikiyemiari ONDO F 12 35000 PAY NOT QUALIFIED

JOMAT UNIVERSITY OF ART AND SCIENCEJAMB ADMISSION LIST AND FEES


Mobile:- +2348035997597 23

DATA VALIDATION Data Validation can be used to limit the data that can be entered into a cell. For example, you might want the user to enter only values between 1% and 99%. You might also use this tool to enable data input to a drop down list. This has two advantages in that it can be faster and more accurate. Start with the dialog box below to create your drop down list functionality.

After making all the necessary selections in the validation list dialog box, your worksheet will behave as shown below.

You can also provide messages to define what input you expect for the cell, and instructions to help users correct any errors. For example, in a marketing workbook, you can set up a cell to allow only account numbers that are exactly three characters long. When users select the cell, you can show them a message such as this one: Data validation helps to prevent spelling error, by selecting an item, you dont need to type again.


Mobile:- +2348035997597 24

VLook-up Function Using VLook-up can be use along side with validation in an excel sheet. In Microsoft Excel, the VLOOKUP function searches for value in the left-most column of table_array and returns the value in the same row based on the index_number.

Lets say we want the computer to automatically indicate the price of an item immediately we select from our validated list. So in Cell C10 we enter this formula = Vlookup (A10,F4:G8,2)

Cell A10 is the item, F4:G8 is the lookup location. As you can see we will not need to enter the price and also the total for each item Total will simply be = Quantity Price i.e. = B10 C10


Mobile:- +2348035997597 25

INDEX-MATCH Function The Index-Match Function is another powerful Excel function. Its so powerful that most users prefer using it than Vlookup. It has certain features that make it superior to VLOOKUP in many situations. Regardless of which method you think is better, (I have my opinion) it is definitely worth it to learn both formulas and have both at your disposal.

When we combine both the INDEX formula and the MATCH formula, the number that the MATCH formula returns becomes the row number for your INDEX formula. =INDEX ( Column I want a return value from , ( MATCH ( My Lookup Value , Column I want to Lookup against , Enter 0 ))

As you can see, it returns the value like VLOOKUP In my own Opinion, when using Index-Match Function, you should make use of ID number as your reference point.

Try This Emp ID Name Department Age Marks

A101 Amit QA 32 154

S251 Santosh Planning 28 102

J214 John Operations 41 86

R541 Rishi Manufacturing 25 98

A153 Alex IT 31 110

H219 Harry QA 45 87

R236 Rajesh Planning 45 92

A265 Anu Operations 26 84


Mobile:- +2348035997597 26

EXERCISE 2

Instruction 1. Use Fill-Down handle to create the ID column, Type the Sales Date and Kg

Sold Manually. 2. Use Validation method to create the Name of Sales Person 3. Use Validation method to create Category 4. Use Validation method to create Fruits. First prepare a two Colum Label List

A and List B. Use this to create your validation using IF-function for the

source depending on the Category. 5. Use Automatic method to create Commission% using IF-function

(4%=Imported, 2%=Local) 6. Use Look-up to automatically determine Unit Price, 7. Order value $ = Unit Price Kg Sold, Sales Tax = 15% Order Value $

and Commission $ = Commission% Kg Sold


Mobile:- +2348035997597 27

EXERCISE 3 The table below is a list of item. Each item is under a category. Use Vlookup to create an Excel receipt as illustrated below. Under the Price column, assign a price to each of the item. Note. Each Item has a code. So, when a customer buys any of the goods, the item is identified using a code.


Mobile:- +2348035997597 28

The above table should be use to create the receipt below. Use Index Match Function for the Code and Price. Amount= Quantity Price.

Customer Name:

Address:

Phone: Date:

N K

Thank you for your patronage, Good bye!

JOMAT SUPER MARKET, BORI

Total

Amount Code Description of Good Quantity Price


Mobile:- +2348035997597 29

EXERCISE 4 S/N Staff

Name Basic Salary

Allowance

Gross Pay

Deduction Total deductions

Net pay

Transport (1% Basic)

Lunch (1%

Basic)

Utility (2%

Basic)

Security (2%

Basic)

Housing (2%

Basic)

Tax (5%

Gross Pay)

Loans (20% Gross)

1 Jame S. 175,000.00 - - - - - ? - ? ? 2 Peter H. 62,083.00 - - - - - ? - ? ? 3 Harry B. 82,684.00 - - - - - ? - ? ? 4 Gift R. 45,981.00 - - - - - ? - ? ? 5 Jude F. 45,981.00 - - - - - ? - ? ? 6 Mercy B 17,840.00 - - - - - ? - ? ? TOTAL ? ? ? ? ? - ? - ? ?

Instruction Transport Lunch Utility Security Housing Gross Pay Total Deduction Net Pay

= 1% of Basic = 1% of Basic = 2% of Basic = 2% of Basic = 2% of Basic = Basic salary + Transport+ Lunch + Utility + Security + Housing = Tax + Loans = Gross Pay Total Deduction

Tax Loans

= 5% of Gross Pay = 20% of Gross Pay


Mobile:- +2348035997597 30

EXERCISE 5

Instruction

1. Use IF and AND Function to calculate the Total score (TL) for each student and use Fill handle to complete the rest

2. Use Nested IF Function to enter the Grade (GD) and Fill handle to complete the rest. Bursary

Scores Grade

75 A

70 AB

65 B

60 BC

55 C

50 CD

45 D

40 E

39 F


Mobile:- +2348035997597 31

EXERCISE 6

Validate the State and L.G.A Federal Capital Territory Gwagwalada , Kuje , Abaji , Abuja Municipal, Bwari , Kwali, Abia Aba North , Aba South , Arochukwu , Bende , Ikwuano , Isiala-Ngwa North , Isiala-Ngwa South , Isuikwato , Obi Nwa , Ohafia , Osisioma , Ngwa , Ugwunagbo , Ukwa East , Ukwa West , Umuahia North , Umuahia ,South Umu-Neochi Adamawa Demsa , Fufore , Ganaye , Gireri , Gombi , Guyuk , Hong , Jada , Lamurde , Madagali , Maiha , Mayo-Belwa, Michika , Mubi North , Mubi South , Numan , Shelleng , Song , Toungo , Yola North , Yola South Akwa Ibom Abak , Eastern Obolo , Eket , Esit Eket Essien Udim Etim Ekpo Etinan Ibeno Ibesikpo Asutan Ibiono Ibom Ika Ikono Ikot Abasi Ikot Ekpene Ini Itu Mbo Mkpat Enin Nsit Atai Nsit Ibom

Nsit Ubium Obot Akara Okobo Onna Oron Oruk Anam Udung Uko Ukanafun Uruan Urue-Offong/Oruko Uyo

Anambra Aguata Anambra East Anambra West Anaocha Awka North Awka South Ayamelum Dunukofia

Ekwusigo Idemili North Idemili south Ihiala Njikoka Nnewi North Nnewi South Ogbaru Onitsha North Onitsha South Orumba North Orumba South Oyi

Bauchi Alkaleri Bauchi Bogoro Damban Darazo Dass Ganjuwa Giade Itas/Gadau Jama'are

Katagum Kirfi Misau Ningi Shira Tafawa-Balewa Toro Warji Zaki Bayelsa Brass Ekeremor Kolokuma/Opokuma Nembe Ogbia Sagbama Southern Jaw Yenegoa Benue Ado Agatu Apa Buruku Gboko Guma Gwer East Gwer West Katsina-Ala Konshisha

Kwande Logo Makurdi Obi Ogbadibo Oju Okpokwu Ohimini Oturkpo Tarka Ukum Ushongo Vandeikya

Bornu Abadam Askira/Uba Bama Bayo Biu Chibok Damboa Dikwa Gubio Guzamala Gwoza

Hawul Jere Kaga Kala/Balge Konduga Kukawa Kwaya Kusar Mafa Magumeri Maiduguri Marte Mobbar Monguno Ngala Nganzai Shani

Cross River Akpabuyo Odukpani Akamkpa Biase Abi Ikom Yarkur Odubra Boki Ogoja Yala

Obanliku Obudu Calabar South Etung Bekwara Bakassi Calabar Municipality Delta

Oshimili Aniocha Aniocha South Ika South Ika North-East Ndokwa West Ndokwa East Isoko south Isoko North Bomadi Burutu Ughelli South Ughelli North Ethiope West Ethiope East Sapele Okpe Warri North Warri South Uvwie Udu Warri Central Ukwani Oshimili North Patani

Ebonyi Afikpo South Afikpo North Onicha Ohaozara Abakaliki Ishielu lkwo Ezza Ezza South

Ohaukwu Ebonyi Ivo Edo

Esan North-East Esan Central Esan West Egor Ukpoba Central Etsako Central Igueben Oredo Ovia SouthWest Ovia South-East Orhionwon Uhunmwonde Etsako East Esan South-East

Ekiti Ado Ekiti-East Ekiti-West Emure/Ise/Orun Ekiti South-West Ikare Irepodun Ijero, Ido/Osi Oye Ikole Moba Gbonyin Efon Ise/Orun Ilejemeje.

Enugu Enugu South, Igbo-Eze South Enugu North Nkanu Udi Agwu Oji-River Ezeagu IgboEze North Isi-Uzo Nsukka Igbo-Ekiti Uzo-Uwani Enugu Eas Aninri Nkanu East Udenu.

Gombe Akko Balanga Billiri Dukku Kaltungo Kwami Shomgom Funakaye Gombe Nafada/Bajoga Yamaltu/Delta.


Mobile:- +2348035997597 32

Imo Aboh-Mbaise Ahiazu-Mbaise Ehime-Mbano Ezinihitte Ideato North Ideato South Ihitte/Uboma Ikeduru Isiala Mbano Isu Mbaitoli Mbaitoli Ngor-Okpala Njaba Nwangele Nkwerre Obowo Oguta Ohaji/Egbema Okigwe Orlu Orsu Oru East Oru West Owerri-Municipal Owerri North Owerri West

Jigawa Auyo Babura Birni Kudu Biriniwa Buji Dutse Gagarawa Garki Gumel Guri Gwaram Gwiwa Hadejia Jahun Kafin Hausa Kaugama Kazaure Kiri Kasamma Kiyawa Maigatari Malam Madori Miga Ringim Roni Sule-Tankarkar Taura Yankwashi

Kaduna Birni-Gwari Chikun Giwa Igabi Ikara jaba Jema'a Kachia Kaduna North Kaduna South Kagarko Kajuru Kaura Kauru Kubau Kudan Lere Makarfi Sabon-Gari Sanga Soba Zango-Kataf Zaria

Kano Ajingi Albasu Bagwai Bebeji Bichi Bunkure Dala Dambatta Dawakin Kudu Dawakin Tofa Doguwa Fagge Gabasawa Garko Garum Mallam Gaya Gezawa Gwale Gwarzo Kabo Kano Municipal Karaye Kibiya Kiru kumbotso Kunchi Kura Madobi Makoda Minjibir Nasarawa Rano Rimin Gado Rogo Shanono Sumaila Takali Tarauni Tofa Tsanyawa Tudun Wada Ungogo Warawa Wudil

Katsina Bakori Batagarawa Batsari Baure Bindawa Charanchi Dandume Danja Dan Musa Daura Dutsi Dutsin-Ma Faskari Funtua Ingawa Jibia Kafur Kaita Kankara Kankia Katsina Kurfi Kusada Mai'Adua Malumfashi Mani Mashi Matazuu Musawa Rimi Sabuwa Safana Sandamu Zango

Kebbi Aleiro Arewa-Dandi Argungu Augie Bagudo Birnin Kebbi Bunza Dandi Fakai Gwandu Jega Kalgo Koko/Besse Maiyama Ngaski Sakaba Shanga Suru Wasagu/Danko Yauri Zuru

Kogi Adavi Ajaokuta Ankpa Bassa Dekina Ibaji Idah Igalamela-Odolu Ijumu Kabba/Bunu Kogi Lokoja Mopa-Muro Ofu Ogori/Mangongo Okehi Okene Olamabolo Omala Yagba East Yagba West

Kwara Asa Baruten Edu Ekiti Ifelodun Ilorin East Ilorin West Irepodun Isin Kaiama Moro Offa Oke-Ero Oyun Pategi

Lagos Agege Ajeromi-Ifelodun Alimosho Amuwo-Odofin Apapa Badagry Epe Eti-Osa Ibeju/Lekki Ifako-Ijaye Ikeja Ikorodu Kosofe Lagos Island Lagos Mainland Mushin Ojo Oshodi-Isolo Shomolu Surulere

Nasarawa Akwanga Awe Doma Karu Keana Keffi Kokona Lafia Nasarawa Nasarawa-Eggon Obi Toto Wamba

Niger Agaie Agwara Bida Borgu Bosso Chanchaga Edati Gbako Gurara Katcha Kontagora Lapai Lavun Magama Mariga Mashegu Mokwa Muya Pailoro Rafi Rijau Shiroro Suleja Tafa Wushishi

Ogun Abeokuta North Abeokuta South Ado-Odo/Ota Egbado North Egbado South Ewekoro Ifo Ijebu East Ijebu North Ijebu North East Ijebu Ode Ikenne Imeko-Afon Ipokia Obafemi-Owode Ogun Waterside Odeda Odogbolu Remo North Shagamu

Ondo Akoko North East Akoko North West Akoko South Akure East Akoko South West Akure North Akure South Ese-Odo Idanre Ifedore Ilaje Ile-Oluji Okeigbo Irele Odigbo Okitipupa Ondo East Ondo West Ose Owo

Osun Aiyedade Aiyedire Atakumosa East Atakumosa West Boluwaduro Boripe Ede North Ede South Egbedore Ejigbo Ife Central Ife East Ife North Ife South Ifedayo Ifelodun Ila Ilesha East Ilesha West Irepodun Irewole Isokan Iwo Obokun Odo-Otin Ola-Oluwa Olorunda Oriade Orolu Osogbo

Oyo Afijio Akinyele Atiba Atigbo Egbeda IbadanCentral Ibadan North Ibadan North West


Mobile:- +2348035997597 33

Ibadan South East Ibadan South West Ibarapa Central Ibarapa East Ibarapa North Ido Irepo Iseyin Itesiwaju Iwajowa Kajola Lagelu Ogbomosho North Ogbmosho South Ogo Oluwa Olorunsogo Oluyole Ona-Ara Orelope Ori Ire Oyo East Oyo West Saki East Saki West Surulere

Plateau Barikin Ladi Bassa Bokkos Jos East Jos North Jos South Kanam Kanke Langtang North Langtang South Mangu Mikang Pankshin Qua'an Pan Riyom Shendam Wase

Rivers Abua/Odual Ahoada East Ahoada West Akuku Toru Andoni Asari-Toru Bonny Degema Emohua Eleme Etche Gokana Ikwerre Khana Obia/Akpor Ogba/Egbema/Ndoni Ogu/Bolo Okrika Omumma Opobo/Nkoro Oyigbo Port-Harcourt Tai

Sokoto Binji Bodinga Dange-shnsi Gada Goronyo Gudu Gawabawa Illela Isa Kware kebbe Rabah Sabon birni Shagari Silame Sokoto North Sokoto South Tambuwal Tqngaza Tureta Wamako Wurno Yabo

Taraba Ardo-kola Bali Donga Gashaka Cassol Ibi Jalingo Karin-Lamido Kurmi Lau Sardauna Takum Ussa Wukari Yorro Zing

Yobe Bade Bursari Damaturu Fika Fune Geidam Gujba Gulani Jakusko Karasuwa Karawa Machina Nangere Nguru Potiskum Tarmua Yunusari Yusufari

Zamfara Anka Bakura Birnin Magaji Bukkuyum Bungudu Gummi Gusau Kaura Namoda Maradun Maru Shinkafi Talata Mafara Tsafe Zurmi

JAMB RESULT SCORE SHEET


Mobile:- +2348035997597 34

Instruction

1. Use the table above and create a link of your total scores and grade on Sheet 2 ( Use INDEX-MATCH Function)

2. Use the sample below to create a form for each student

3. Print a copy.

Enter your Exam Number Here.


Mobile:- +2348035997597 35

CREATING A CHARTS

Excel provides a variety of chart types that you can choose from when you create a chart. For most charts, such as column (vertical bar) and bar (horizontal bar) charts, you can plot the data that you arrange in rows or columns on a worksheet in a chart. Some chart types, such as pie chart, require a specific data arrangement.

To create a chart: 1. On the worksheet, arrange the data that you want to plot in a chart. 2. Select the cells that contain the data that you want to use for the chart.

Note: If you select only one cell, Excel automatically plots all cells containing

data that directly surround that cell into a chart. If the cells that you want to

plot in a chart are not in a continuous range, you can select nonadjacent cells

or ranges as long as the selection forms a rectangle. You can also hide the

rows or columns that you don't want to plot in the chart.

3. On the Insert tab, in the Charts group, do one of the following: Click the chart type, and then click a chart subtype that you want to

use. To see all available chart types, click a chart type, and then click All

Chart Types to display the Insert Chart dialog box, click the arrows to scroll through all available chart types and chart subtypes, and then clicks the type you want to use.

The chart is placed on the worksheet as an embedded chart. If you want to place the chart in a separate chart sheet, you can change its location.

To change chart location: 1. Click the embedded chart or the chart sheet to select it and to display the chart tools. 2. On the Design tab, in the Location group, click Move Chart.


Mobile:- +2348035997597 36

3. Under Choose where you want the chart to be placed, do one of the following:

To display the chart in a chart sheet, click New sheet. Note: If you want to replace the suggested name for the chart, you can type a new name in the New sheet box. To display the chart as an embedded chart in a worksheet click Object in, and then click a worksheet in the Object in box. To create a chart that is based on the default chart type Select the data that you want to use for the chart. IF you press ALT+F1, the chart is displayed as an embedded chart; when you press F11, the chart is displayed on a separate chart sheet.

Supposing, you are a teacher and you

gave a short test to a class. You counted

the numbers of students obtaining the

possible mark and made a table and

thereafter plots a column chart (vertical

bar chart) in Microsoft Excel as shown

below.

Chart 1 is a default chart created. Chart 1:

0

2

4

6

8

10

12

14

16

18

20

1 2 3 4 5 6 7 8 9 10 11

Number of students


Mobile:- +2348035997597 37

To change the scale of horizontal axis: Select the horizontal axis. On the Design tab, in the Data group, click select Data icon

In the Select Data Source dialog box, click Edit under Horizontal(category)Axis Labels

In the Axis Labels dialog box, type the active worksheet name (worksheet that contain the data),an exclamation symbol and cell range that contain the data in the Axis label range box and then click OK. Observe that the original scale value in the Horizontal (category) Axis Labels is exchanged with data in the range of cells you specified. Click OK.

To slant the Horizontal axis scale: Select the horizontal axis label (scale). On the Layout tab, in the Current Selection group, click the drop down button in the Chart Element box and then click Horizontal (category) axis. In the Current Selection group, click on Format Selection.


Mobile:- +2348035997597 38

In the Format Axis dialog box, click Alignment. Under Alignment, select a suitable angle value in the Custom angle box and then click Close.

To change the unit of the vertical axis. Select the vertical axis. On the Layout tab, in the Current Selection group, click the drop down-button in the Chart Element box and then click Vertical (category) axis. In the Current Selection group, click on Format Selection.

Under Axis Options, locate Major unit and click Fixed. Type in a suitable unit and then click Close.

To close the gap between column chart: In the chart area, select the Column chart (Series) only. On the Layout tab, in the Current Selection group, click the drop down-button in the Chart Element

box . Click the series.

Slant


Mobile:- +2348035997597 39

In the Current Selection group, click on Format Selection.

Under Series Options, locate the Gap width area. Adjusting the gap to No Gap (i.e. 0%) closes the gap between the charts while adjusting the gap to Large Gap creates gap between the charts. Click Close.

To manually label the chart and horizontal titles: On the Layout tab, in Insert group, click Text box. Click the chart area, hold down the mouse button and then draw the text box to size.

Type text in the text box.

To manually label vertical axis: On the Layout tab, in Insert group, click Text box. Click the chart area, hold down the mouse button and then draw the text box to size. Type text in the text box. On the Format tab, in the Alignment group, click Rotate and then click Rotate Left 900.

To add background color to plot area: Select the plot area. On the Layout tab, in the Background group, click Plot Area icon. Click More Plot Area Options.

In the dialog box, under Fill, select either Solid fill, Gradient, or Picture or texture fill options to add background color to your chart.


Mobile:- +2348035997597 40

To add background color to the chart area: Click the chart area and it is selected. On the Format tab, in the Shape Styles group, click the Shape Fill arrow.

Click on any color of your choice.

To color chart, legend, vertical and horizontal axis title:: Select the text box. On the Format tab, in the WordArt Styles group, click the Text Fill arrow. On the F

Click on any color of your choice. Chart 2:

0

2

4

6

8

10

12

14

16

18

20

0 1 2 3 4 5 6 7 8 9 10

Number of students

Marks

ND II Students test score

Number of

students

Vertical Axis Title

Chart Title Plot Area

Series

Legend

Horizontal Axis

Chart Area

Horizontal Axis Title Vertical Axis

learn microsoft excel

Documents