This section will make your ICT practicals in Microsoft Excel a bit easy!
| Function | Syntax | Example |
| Sum | =sum(startvalue:endvalue) | =sum(A1:G1) |
| Average | =Average(startvalue:endvalue) | =Average(A1:G1) |
| Max | =Max(startvalue:endvalue) | =Max(A1:G1) |
| Min | =Min(startvalue:endvalue) | =Min(A1:G1) |
| count (for nos.) | =count(startvalue:endvalue) | =Count(A1:G1) |
| count (for text) | =countA(startvalue:endvalue) | =CountA(A1:G1) |
| Integer | =Int(value) | =Int(25.789) -> 25 |
| Round | =Round(value,decimal place) | =Round(35.6782,2)-> 35.68 |
| IF | =IF(value1,value2,criteria) | =IF(D2>50,”A”,”B”) |
| countif | =countif(range,criteria) | =countif($A$1:$A$10,A1) |
| sumif | =sumif(range,criteria,sum range) | =sumif($A$!:$A$10,A1,$B$1:$B$10) |
| nestedIF | =IF(criteria,value1, IF(criteria,value2, IF(criteria,value3,value4))) |
=IF(D2>80,”A”, IF(D2>70,”B”, IF(D2>60,”C”,”D”))) |
| Vlookup | =Vlookup(loopup value,Table array,cot index no.Range Lookup) | =Vlookup(A2,sheet2$A$1:$B$10,1) |
- How to insert header and footer?
- To insert header and footer:
- Click on insert
- Click on header and footer
- Select blank 3 columns
- Insert name, center no., roll no., date and time, Page no., file name, file path
- To insert header and footer:
- How to display function?
- To display function, press control and Alt keys together
- Page setup
- Orientation – Portrait, Landscape
- Margins – Top, Bottom, Left and Right
- Papersize – A4
- Insert row, Insert column, delete row, delete column
- Printouts – 1) For specific range
- e.g. A1 to D15
- Select range A1 to D15
- Click on File, click print active sheet
- select print, select print selection
- How to adjust worsheet or spreadsheet to one page wide?
- To adjust worksheet to one page wide.click on Page Layout, this opens Page setup tab, select the ‘Page’ tab, select Fit under Scaling and choose 1 page wide by 1 tall
- Decrease margin from left and right, adjust page to 85% or 90%.
- How to extract data?
- To extract data – select all headings, click on home tab, select filter,, apply filter
- How to sort data?
- To sort data, click on home tab, select option sort and filter, select – sort A to Z, sort Z to A, custom sort
***This is the end of this guide. Hope you enjoyed it! Thanks for using www.igcsepro.org! We hope you will give us a chance to serve you again! Thank you!***