Lecture 5

1. Lecture 5 Microsoft Excel Basics Prepared By : Md Jakaria

2. What's Microsoft Excel ... Excel GKUw ËþeÞWmyU ËeÞwMÞwi Ggv Z×xi GLwËd †xQËt WwUw kwLËZ ewkËg, eÔYê xpowg I xo¦wìæ xdËZ ewkËg, MÞwxfKøwl WwUw gøgpwk KËk eÞËfmdwl xkËewUê ÐZky KkËZ ewkËg„ GxU AËdKUw GKwDxåUv Gk ËlRwËkk iZ – Zaø kwLwk Rdø Ëkw Ggv KlwËik xMÞW awËK„ Prepared By : Md Jakaria

3. MS Excel Screen Layout GË…Ël KwR Âk× Kkwk AwËM Gk xKQ× xfPwËkk owËa exkxPZ pItw jwK„ Title Bar Menu Bar Column Letter Active Cell Sheet Tab Formula Bar Name Box Row Number Prepared By : Md Jakaria

4. Special Features of MS Excel! Sheet Tabs – gBËtk ewZwk iZ GË…Ëlk ItwKêg×ËK AËdK myU awËK„ myU†Ëlw UøwËgk owpwËjø xdtìèd Kkw jwt„ UøwËg eÞËZøK myËUk dwiI xbËZ ewkËg„ Uøwg†Ëlw ItwKêg×K DBË«wk dyËPk xbËK awËK„ UøwËg xKõK KËk xgxhdí myËU ËjËZ ewkËg„ Chart Toolbar – GË…Ëlk AdøZi o×xgcw pl PwUê I MÞwxf…„ G†Ëlw xdËt KwR Kkwk Rdø Awlwbw GKxU U×lgwk GË…Ël AwËQ„ PwUê gw MÞwxf… xdËt KwR KkËZ ËMËl GxU ÇyËd AwËo„ jxb dw ËbLËZ ewI ZËg xdËPk xdËbêmdw fËlw Kk: View – > Toolbars – > Chart U×lgwkxU ÇyËd ËbLw jwËg„ Prepared By : Md Jakaria

5. Formula Bar in a Microsoft Excel GLwËd AøwKxUh ËoËlk KËåUåU ËbLw I GxWU Kkw jwt„ GLwËd text, number, formula Ggv function ËbLËZ ewËg„ xPË¢ fiê×lw gwËkk xKQ× xfPwk ËbLwËdw pl„ Prepared By : Md Jakaria

6. Excel Column and Row Headings GKxU myUËK ËZwikw row Ggv column G hwM Kkw ËbLËZ ewËg„ eÞËZøKxU ËQwU AvmËK gËl Ëol„ Row-†ËlwËK 1 ËaËK 65536 ejìæê dw¹wxkv Kkw ËbLËZ ewËg„ Awk Column †Ëlw xPxpßZ Kkw pËtËQ A ËaËK Z Ggv Zwk ek AA ËaËK AZ, BA ËaËK BZ GhwËg IV ejìæê„ Row Heading Column Heading Prepared By : Md Jakaria

7. Cell References in MS Excel Klwi I Ëkw Gk ovËjwMþÿlËK cell gËl„ eÞËZøKxU ËolËK Klwi I Ëkw dw¹wk (cell reference) xbËt xPxpßZ Kkw pt„ Ëjid: A1, B5, C20 BZøwxb„ GLwËd Aqk xbËt Klwi Ggv ovLøw xbËt Ëkw dw¹wk ËgwSwËdw pËtËQ„ Aaêwu ËZwiwËK B5 ËoËlk WwUw ËbLËZ glËl B KlwËi xMËt xdËPk xbËK 5 dw¹wk Ëkw ËZ AwoËZ pËg Cell References Prepared By : Md Jakaria

8. Select Excel spreadsheet cells ËoËl WwUw GxåUÞ KkËZ eÞaËi ËolxUËK xoËlƒ KkËZ pËg„ Gk Rdø xdËPk Ëj ËKwd GKxU e¦xZ Ad×okd Kk: 1. ËoËlk Dek iwDo eËtåUwk xdËt xKõK Kk 2. ËþeÞWmyËUk ogxbËK i×hËiËåUk Rdø AøwËkw Ky gøgpwk (up/down, left/right) Kk 3. GKB Ëkw Gk ekgZêy ËoËl ËjËZ Tab Ky gøgpwk Kk 4. GKB KlwËik xdËPk xbËK ËjËZ Enter Ky gøgpwk Kk Prepared By : Md Jakaria

9. Enter Data in Excel ËKwd ËoËlk PwkewËm Mws gWêwk awKËl g×ËS xdËg ËolxU AøwKxUh kËtËQ Ggv GLwËd Z×xi WwUw xlLËZ ewkËg„ To enter data into cells: ËoËl WwUw GxåUÞ KkËZ WwUw UwBe KkËZ awK„ ËZwiwk ËlLw text, number, a formula gw function owËa owËa fki×lw gwËk ËbLw jwËg„ ËlLw Ëmn pËl Uøwg, Enter gw Arrow Ky gøgpwk KËk Adø ËoËl ËMËl xlxLZ WwUw ËolxUËZ GxåUÞ pwËg„ ËZwiwk worksheet G UwBËUl xlLËZ DeËkk xbËK GKxU ËoËl xKõK KËk UwBËUl xlL„ Klwi ËpxWv Gk Rdø Dej×£ß ËoËl ËpxWv xlL„ Ëkw Gk ËqË¢I GKB hwËg xlËL dwI„ Prepared By : Md Jakaria

10. Edit Data in Excel To edit data within a cell: ËoËlk WwUw b×B hwËg GxWU KkËZ ewk: xdxbêÄ Ëol xoËlƒ KËk UwBe Âk× Kk Aagw fiêÔlw gwËk xMËt xlL„ Clearing Cell Contents: GË…Ël ËoËlk WwUw xKõtwk Kkw L×g opR„ KËtK DewËtB Z×xi GUw KkËZ ewk: 1. Ëol xoËlƒ Kkwk ek Delete gwUd ËeÞo Kk 2. Ëol xoËlƒ Kkwk ek fiêÔlw gwËk xMËt Delete gw Backspace Ky ËPËe 3. Ëol xoËlƒ Kkwk ek Edit –> Clear 4. ËoËlk Dek iwDËok kwBU xKõK KËk Clear Contents xoËlƒ Kk„ Prepared By : Md Jakaria

11. Excel Formulas for Beginners fiê×lw og oit Âk× KkËZ pËg = xPpß xbËt„ +, -, * and / xPp߆Ëlw jaw¢ßËi ËjwM, xgËtwM, †d Ggv hwM KkËZ gøgpwk Kkw pt„ GKxU fiê×lwk Dbwpkd: = (B3+C3+2*D3)/4 xdËPk DbwpkËd Qw¢Ëbk ËUwUwl ËÆwk Ggv KõwËok Mr ËbLËZ ewËg„ Prepared By : Md Jakaria

12. Excel SUM Function xgËmn xKQ× KwËRk Rdø (AVERAGE, SUM BZøwxb) AwËM ËaËK xWfwBd Kkw fiê×lw pl fwvmd„ SUM fwvmdxU ogËPËt Kid Ggv GË…Ël ogËPËt Ëgmy gøgpwk pt„ GB fwvmdxU xlLËZ xdËPk xdti fËlw Kk„ Ëj ËKwd fwvmd xlLËZ GUw gøgpwk KkËZ ewk„ =SUM(B3:D3) xdËPk xP¢xU SUM Ggv AVERAGE fwvmËdk gøgpwk ËbLwËâQ„ fwvmd b×xU AwiwËbk fiê×lw gwËk gr KøwlK×Ëlmd (=B3+C3+D3 Ggv =(B3+B4+B5+B6+B7)/5) ËlLwk xgKùe GËd xbËtËQ„ Prepared By : Md Jakaria

13. Excel AVERAGE Function AVERAGE fwvmdxU GK ËoU WwUwk Mr xddêt KËk„ fwvmdxU ËlLwk syntax pl: =AVERAGE(first value, second value gw ËoËlk Ëk™) DeËkk xP¢ Ad×owËk AVERAGE fwvmdxU pËg: =AVERAGE(C1:C4) Prepared By : Md Jakaria

14. Excel IF Function IF Function: IF fwvmd lxRKøwl Kx«md ËPK KËk ËoxU oZø pËl eÞai Value Ggv xiaøw pËl 2nd Value xkUwdê KËk„ fwvmdxU ËlLwk xoËåU… pl: =IF (condition, value-if-true, value-if-false) DeËkk WwUwËoU xdËt KwR KkËZ ewm Ëfl xddêËtk (lxRK pl 179-k ËPËt Ëgmy ËeËl ewm dw pËl Ëfl) Rdø F4 ËoËl Ëj IF fwvmd xlLËZ pËg Zw pl: =IF(E4>179,"Pass","Fail") Prepared By : Md Jakaria

15. Excel MAX Function MAX Function: MAX fwvmd xoËl˃W ËkË™k Ëol†Ëlwk iËcø ogËPËt grxU xkUwdê KËk„ GB function blank gw text value xdËt KwR KËk dw„ Gk xoËåU… pl: =MAX(first value, second value, gw Ëol Ëk™) GB DbwpkËd Test 1 Gk ogËPËt Ëgmy ËÆwk ËeËZ B10 G GhwËg fwvmdxU xlL: =MAX(B4:B9) Prepared By : Md Jakaria

16. Excel MIN Function MIN Function: MIN fwvmd xoËl˃W ËkË™k Ëol†Ëlwk iËcø ogËPËt ËQwUxU xkUwdê KËk„ GB function blank gw text value xdËt KwR KËk dw„ Gk xoËåU… pl: = MIN (first value, second value, gw Ëol Ëk™) GB DbwpkËd Test 1 Gk ogËPËt Ki ËÆwk ËeËZ B10 G GhwËg fwvmdxU xlL: = MIN (B4:B9) ËQwU iwd xoËl˃W ËoËl ËbLËZ ewËg„ (DbwpkËd B10 ËoËl "60" iwdxU ËbLwËâQ„ Prepared By : Md Jakaria

17. Copy Formulas/Functions xoËl˃W ËoËlk xdËP-WwdewËm fill handle awËK„ iwDo eËtåUwk ËoLwËd xdËl ËQwU Mws + xPpß ËbLËZ ewËg„ Formulas/Functions Adø ËoËl WxeöËKU KkËZ GxU xgËmn DeKwky„ GKB Formula eÞËtwM KkËZ pt GkKi ËqË¢ gwkgwk Zw dw xlËL xfl pøwË«l xKõK KËk cËk WÛøwM KËk Adø Ëol†ËlwËZ WxeöËKU Kkw jwt„ Qxg†Ëlw ËbLËl GUw xKhwËg KwR KËk Ëo cwkdw ewËg„ Prepared By : Md Jakaria

18. Format Excel Data Sheet Merge Cells – KËtKxU ËolËK GKxU ËoËl exkdZ Kkw„ Ëol†Ëlw xoËlƒ KËk Merge Cell gwUdxUËZ xKõK Kk„ Number Format – WwUwËZ currency ($), percent (%) gw comma (.00) ýUwBl j×£ß KËk„ xdxbêÄ Ëol xoËlƒ KËk eÞËtwRdyt gwUdxU xKõK Kk„ Decimal Places – ovLøwk ËWxoËil ËeöËok xWxRU gwrwt gw Kiwt„ xdxbêÄ Ëol xoËlƒ KËk eÞËtwRdyt gwUdxU xKõK Kk„ Borders – xoËl˃W ËoËlk PwkxbËK gWêwk xbËZ GxU gøgpwk pt„ xdxbêÄ Ëol xoËlƒ KËk eÞËtwRdyt gwUdxU xKõK Kk„ Fill Color – ËoËl fill color ËjwM KËk„ Ëol xoËlƒ KËk xfl Kwlwk Aemd pËZ GKxU Kwlwk eQëb Kk„ Prepared By : Md Jakaria

19. Formating Numeric Data dw¹wk gw ËU…UËK xgxhdí fkËiËU exkgZêd Kkw jwt„ Z×xi numeric WwUwËK decimal, percentage, fraction, gw currency fËiê kÔewìæk KkËZ ewk„ GRdø Z×xi fkiøwU U×lgwk gøgpwk KkËZ ewk„ fkËiËUk Rdø AwËkw Ëgmy Aemd ËeËZ xdËPk BëoUÞwKmd fËlw Kk: • Format –> Cells KËtKxU Uøwg op fkiøwU Ëol WwtwlM DBË«w AwoËg„ Prepared By : Md Jakaria

20. Formating Numeric Data Number: GB UøwËg ËgmxKQ× KøwUwMxk ËbItw AwËQ„ GLwd ËaËK bkKwky fkiøwU KøwUwMky AemËd xKõK Kk„ H KøwUwMkyk Rdø Ëgm xKQ× Aemd ËbLËZ ewËg„ eQëbdyt fkiøwU xoËlƒ KËk OK gwUËd xKõK Kk„ GB DBË«wk Aemd†Ëlw gøgpwk KËk GË…Ël WwUwk xgxhdí fkiøwU Kkw jwt„ Prepared By : Md Jakaria

21. Merge Excel Cells, Columns, Rows GË…Ël GKwxcK ËolËK GKxU ËoËl iwRê Kkw Rk×xk pËt bwrwt„ b×B hwËg GxU Z×xi KkËZ ewkËg„ To merge cells using the format toolbar: xdËPk ËýUe†Ëlw fËlw Kk: 1. Ëol†ËlwËK xoËlƒ Kk 2. fkiøwU U×lgwk pËZ Merge and Center gwUËd xKõK Kk Merge Excel Prepared By : Md Jakaria

22. Sorting Data in Excel myËU AËdK WwUw pËt ËMËl Ëo†Ëlw xdËt KwR Kkw pxVd pËt jwt„ KwR opR KkËZ Sort Aemd gøgpwk KkËZ ewk„ Ëj KlwËik WwUw oUê KkËZ PwI Zwk Ëj ËKwd WwUw xoËlƒ Kk„ Ggwk U×lgwËkk Sort gwUd xKõK Kk„ Sort Ascending Sort Decending Prepared By : Md Jakaria

23. Using Excel to Create a Chart or Graph WwUw xWoËeö Kkwk ogËPËt hwl Dewt pl PwUê ÐZky Kkw„ GË…Ël AËdK ckËdk PwUê ÐZkyk Aemd AwËQ„ PwUê DBRwWê xbËt opËRB Z×xi PwUê ÐZky KkËZ ewkËg„ xdËPk ËýUe†Ëlw fËlw Kk„ U×lgwk gw Insert Ëid× pËZ Chart Wizard xoËlƒ Kk GKxU Chart Wizard WwtwlM DBË«w AwoËg„ GLwËd Z×xi PwUê UwBe (e.g. column, bar, pie, line BZøwxb) Ggv ýUwBl (e.g. 3D effect, stacked BZøwxb) xoËlƒ KËk Next gwUËd xKõK Kk Prepared By : Md Jakaria

24. Using Excel to Create a Chart or Graph Ggwk Data Range xoËlƒ Kkwk Aemd ewËg„ iwDo xbËt WwUw Ëk™ xoËlƒ KËk bwI„ Prepared By : Md Jakaria

25. Data range wizard in Excel Series UøwËg xMËt PwUê UwBËUl Ggv e X-axis Gk WwUw xoËlƒ KËk xbËZ ewk„ ogËmËn Next gwUËd xKõK Kk„ PwËUêk xgxhdí Aemd xdËt GKxU dZ×d DBË«w AwoËg„ PwUêËK AwKnêdyt I KwjKêk KkËZ GB Aemd†Ëlw gøgpwk Kk„ Ggwk Next gwUËd xKõK KkËl dZ×d GKxU DBË«w ewËg„ GLwËd Z×xi gËl xbËZ ewkËg ËKwawt PwUêxU goËg„ WwUwk owËa GKB myËU Aagw dZ×d myËU PwUêxU ËlwËKU KkËZ ewkËg„ Finish gwUËd xKõK KËk PwUêxU ItwKêg×ËK xdËt Awo„ Prepared By : Md Jakaria

26. That’s all…. Thank you for your attention! Prepared By : Md Jakaria