Friday, November 29, 2013
Thursday, November 28, 2013
Wednesday, November 27, 2013
Tuesday, November 26, 2013
Sunday, November 24, 2013
Wednesday, November 20, 2013
Tuesday, November 19, 2013
Monday, November 18, 2013
Sunday, November 17, 2013
GADGIL committe report
പശ്ചിമഘട്ടത്തിന്റെ പാരിസ്ഥിതിക സേവനങ്ങള്‍ കൊണ്ട് നിലനില്ക്കുന ഒരു ജനതയാണ് മലയാളി. അതുകൊണ്ടാണ് കേരളത്തെ ‘ദൈവത്തിന്റെ സ്വന്തം നാടാ’യി നാം കൊട്ടിഘോഷിക്കുന്നതും. പശ്ചിമഘട്ടത്തിന്റെ ഇന്നത്തെ സ്ഥിതി ഏറെ ആശങ്കാജനകമായ സാഹചര്യത്തിലാണ് കേന്ദ്ര വനം പരിസ്ഥിതി മന്ത്രാലയം പ്രശ്‌നപരിഹാരത്തിനായി ഒരു വിദഗ്ധസമിതിയെ ചുമതലപ്പെടുത്തിയത്.
പ്രശസ്ത ശാസ്ത്രജ്ഞനായ ശ്രീ.മാധവ് ഗാഡ്ഗില്‍ അധ്യക്ഷനായ സമിതിയുടെ റിപ്പോര്‍ട്ട് ആണ് ഗാഡ്ഗില്‍ കമ്മിറ്റി റിപ്പോര്‍ട്ട് എന്നപേരില്‍ അറിയപ്പെടുന്നത്. ഖനന മാഫിയയുടെയും മറ്റും സമ്മര്‍ദ്ദം മൂലം ഈ റിപ്പോര്‍ട്ട് കേന്ദ്ര പരിസ്ഥിതി മന്ത്രാലയത്തില്‍ പൂഴ്ത്തിവെയ്ക്കപ്പെട്ടപ്പോള്‍ വിവരാവകാശ നിയമപ്രകാരം റിപ്പോര്‍ട്ട് നല്‍കാന്‍ കേന്ദ്ര വിവാരവകാശ കമ്മീഷന്‍ ആവശ്യപ്പെടുകയായിരുന്നു. അങ്ങനെയാണ് ഇത് പൊതുസമൂഹത്തിനു ലഭിച്ചത്.
തങ്ങളുടെ നിര്‍ദ്ദേശങ്ങള്‍ പ്രാദേശിക തലത്തില്‍ ചര്‍ച്ച ചെയ്ത് വേണ്ടുന്ന മാറ്റങ്ങള്‍ വരുത്തി നടപ്പാക്കണം എന്നാണു കമ്മിറ്റിയുടെ നിര്‍ദ്ദേശം.
പരിസ്ഥിതി സൌഹൃദമായ വികസനം പ്രോത്സാഹിപ്പിക്കണമെന്നും അശാസ്ത്രീയ സമീപനം അവസാനിപ്പിക്കണം എന്നുമാണ് റിപ്പോര്‍ട്ടിന്റെ പൊതുസ്വഭാവം. ഗാഡ്ഗില്‍ കമ്മിറ്റി റിപ്പോര്‍ട്ടിനെതിരെ മലയോര മേഖലയിലെ ക്രിസ്തീയ സഭകളുടെയും രാഷ്ട്രീയ കക്ഷികളുടെയും മറ്റും എതിര്‍പ്പ് ആദ്യമുയര്‍ന്നു.
ആളുകളെ കുടിയോഴിപ്പിക്കുമെന്നും വികസനം തടയുമെന്നുമുള്ള ആശങ്കയാണ് ഉന്നയിക്കപ്പെട്ടത്. വിവിധ രാഷ്ട്രീയ പ്രസ്ഥാനങ്ങള്‍ റിപ്പോര്‍ട്ടിനെതിരെ പരസ്യമായി വന്നു. റിപ്പോര്‍ട്ടിനെതിരായ അഭിപ്രായം കേരള സര്‍ക്കാര്‍ തന്നെ കേന്ദ്രത്തെ അറിയിച്ചു കഴിഞ്ഞു.
ഇന്ന് കേരള നിയമസഭയില്‍ മാധവ് ഗാഡ്ഗില്‍ കമ്മിറ്റി റിപ്പോര്‍ട്ട് ചര്‍ച്ചയ്ക്കു വെയ്ക്കുകയാണ്. കേരളത്തിന്റെ സുസ്ഥിര വികസനത്തിന് ഏറെ പ്രാധാന്യമുള്ള റിപ്പോര്‍ട്ടാണ് ഇത്.
ഈ പശ്ചാത്തലത്തില്‍, വെളിച്ചമാണ് ഇരുട്ട് അകറ്റാനുള്ള ഏക മാര്‍ഗ്ഗം എന്നതിനാല്‍, ഗാഡ്ഗില്‍ കമ്മിറ്റി റിപ്പോര്‍ട്ടിന്റെ സംക്ഷിപ്തരൂപം വിശദീകരണം ഉള്‍പ്പെടെ ‘ഡൂള്‍ ന്യൂസ്’ മലയാളത്തില്‍ പ്രസിദ്ധീകരിക്കുകയാണ്. വായനക്കാര്‍ റിപ്പോര്‍ട്ടിനെ സശ്രദ്ധം വിലയിരുത്തുമല്ലോ.
പശ്ചിമഘട്ട പരിസ്ഥിതി വിദഗ്ധസമിതി റിപ്പോര്‍ട്ട്
സമിതിയ്ക്ക് നല്‍കിയ ഉത്തരവാദിത്വങ്ങള്‍
എ) പശ്ചിമഘട്ടത്തിന്റെ ഇപ്പോഴത്തെ പാരിസ്ഥിതികാവസ്ഥ വിലയിരുത്തുക.
ബി) പശ്ചിമഘട്ടത്തിലെ ഏതൊക്കെ പ്രദേശങ്ങള്‍ 1986 ലെ പരിസ്ഥിതി സംരക്ഷണ നിയമപ്രകാരം പരിസ്ഥിതി ലോല പ്രദേശമായി പ്രഖ്യാപിക്കണമെന്ന് അടയാളപ്പെടുത്തുക
സി) എല്ലാ താല്‍പ്പര കക്ഷികളുമായി ചര്‍ച്ച നടത്തി പശ്ചിമഘട്ടത്തിന്റെ സംരക്ഷണത്തിനും പുനരുജ്ജീവനത്തിനും ആവശ്യമായ നിര്‍ദ്ദേശങ്ങള്‍ നല്‍കുക.
ഡി) ബന്ധപ്പെട്ട എല്ലാ സംസ്ഥാനങ്ങളുടെയും സഹായത്തോടെ പശ്ചിമഘട്ടത്തിന്റെ സുസ്ഥിര വികസനത്തിനും സംരക്ഷണത്തിനുമായി പശ്ചിമഘട്ട പാരിസ്ഥിതിക അതോറിറ്റി സ്ഥാപിക്കുന്നതിനുള്ള മാതൃകകള്‍ നിര്‍ദ്ദേശിക്കുക.
ഇ) കേന്ദ്ര വനംപരിസ്ഥിതി മന്ത്രാലയം നിര്‍ദ്ദേശിക്കുന്നതടക്കമുള്ള, പശ്ചിമഘട്ടം നേരിടുന്ന മറ്റേതൊരു ഗൗരവ പരിസ്ഥിതി പ്രശ്‌നങ്ങളും കൈകാര്യം ചെയ്യുക.
എഫ്) താഴെ പറയുന്നവ വിലയിരുത്തി റിപ്പോര്‍ട്ട് നല്‍കുക.
1)അതിരപ്പിള്ളി ജലവൈദ്യുത പദ്ധതി
2) ഗുണ്ടിയ ജലവൈദ്യുത പദ്ധതി
3.രത്‌നഗിരി, സിന്ധുദുര്‍ഗ് ജില്ലകളില്‍ (മഹാരാഷ്ട്ര) ഖനികള്‍, ഊര്‍ജ്ജ പദ്ധതികള്‍, മാലിന്യ പദ്ധതികള്‍, എന്നിവ തുടര്‍ന്നും വികസിപ്പിക്കുന്നത് സംബന്ധിച്ച് മാര്‍ഗ്ഗ നിര്‍ദേശം സമര്‍പ്പിക്കുക.
താഴെ പറയുന്ന കാരണങ്ങളാല്‍ പശ്ചിമഘട്ടം മുഴുവനും പാരിസ്ഥിതിക പ്രാമുഖ്യമുള്ള പ്രദേശമായി വിദഗ്ദ്ധസമിതി കാണുന്നു.
1) ജൈവവൈവിധ്യ മൂല്യം: ഭൂമിയിലെ 35 ജൈവവൈവിധ്യ ഹോട്ട് സ്‌പോട്ടുകളില്‍ ഒന്നാണ് പശ്ചിമഘട്ടം.
2) ഗുജറാത്ത്, മഹാരാഷ്ട്ര, ഗോവ, കര്‍ണ്ണാടക, തമിഴ്‌നാട്, കേരളം എന്നീ 6 സംസ്ഥാനങ്ങളില്‍ ആയുള്ള 25 കോടിയിലധികം ജനങ്ങള്‍ പ്രധാനമായും കുടിക്കാനും കൃഷി ചെയ്യാനുമുള്ള ജലത്തിന് ആശ്രയിക്കുന്നത് പസ്ചിമാഘട്ടത്തിനെയാണ്.
പശ്ചിമഘട്ട പരിസ്ഥിതി വിദഗ്ധസമിതി ചെയ്തത്
പാനല്‍ യോഗങ്ങള്‍ 15
നിയോഗിക്കപ്പെട പ്രത്യേക ഗവേഷണ പ്രബന്ധങ്ങള്‍ 42
ബൗധികാതിഷ്ടിത ചര്‍ച്ചകള്‍ 7
വിദഗ്ധ കൂടിയാലോചനാ യോഗം 1
സര്‍ക്കാര്‍ വകുപ്പുകളുമായി കൂടിയാലോചനാ യോഗം 8
സന്നദ്ധ സംഘടനകളുമായുള്ള കൂടിയാലോചന 40
പ്രാദേശിക സന്ദര്‍ശനങ്ങള്‍ 14.
വിദഗ്ധസമിതി ചെയ്തത്
പശ്ചിമഘട്ട മേഖലയിലെ അശാസ്ത്രീയ വികസന പദ്ധതികള്‍ മൂലം വര്‍ധിച്ചു വരുന്ന വെല്ലുവിളികള്‍ പരിഗണിച്ച്, പാരിസ്ഥിതിക സുസ്ഥിരത ഉറപ്പു വരുത്തിക്കൊണ്ടുള്ള ഉപജീവനം പ്രോത്സാഹിപ്പിക്കുന്നതിനു ഓരോ പ്രദേശത്തിന്റെയും സാധ്യതകള്‍ കണ്ടെത്തുന്നതിനുള്ള ശാസ്ത്രീയമായ ഒരു തീരുമാന സഹായ സംവിധാനം വികസിപ്പിച്ചു.
ബഹുതല കാഴ്ചപ്പാട്
മുഴുവന്‍ പശ്ചിമഘട്ടവും പരിസ്ഥിതി ലോല പ്രദേശമായി പ്രഖ്യാപിക്കെണ്ടാതാണെങ്കിലും എല്ലാ പ്രദേശവും ഒരേ അളവില്‍ കാണാനാകില്ല. സംരക്ഷണത്തിനും വികസനത്തിനുമായി പ്രദേശങ്ങള്‍ വേര്‍തിരിച്ചു അടയാളപ്പെടുത്തേണ്ടതുണ്ട്. എന്നാല്‍ മനുഷ്യരെ കുടിയോഴിപ്പിക്കണമെന്നോ മനുഷ്യര്‍ പോകാത്ത പ്രദേശങ്ങള്‍ ഉണ്ടാകണമെന്നോ റിപ്പോര്‍ട്ടില്‍ പറയുന്നില്ല. പരിസ്ഥിതി സംരക്ഷണവും വികസനവും കൈകോര്‍ത്തു പോകണമെന്നു കരുതുന്നതിനാല്‍ പശ്ചിമഘട്ടത്തെ 3 വിഭാഗങ്ങളാക്കി തരാം തിരിച്ചിരിക്കുന്നു.
1. അതീവ പ്രാധാന്യ മേഖല (പരിസ്ഥിതി ലോല മേഖല 1)
2. മിത പ്രാധാന്യ മേഖല (പരിസ്ഥിതി ലോല മേഖല 2)
3. കുറഞ്ഞ പ്രാധാന്യ മേഖല (പരിസ്ഥിതി ലോല മേഖല 3)
2. മിത പ്രാധാന്യ മേഖല (പരിസ്ഥിതി ലോല മേഖല 2)
3. കുറഞ്ഞ പ്രാധാന്യ മേഖല (പരിസ്ഥിതി ലോല മേഖല 3)
ഏതൊക്കെ പ്രവര്‍ത്തനങ്ങള്‍ ഈ മേഖലകളില്‍ ആകാമെന്നും ഏതൊക്കെ നിയന്ത്രിക്കപ്പെടണമെന്നും തീരുമാനിക്കാന്‍ തക്കവണ്ണം തദ്ദേശസ്വയംഭരണ സ്ഥാപനങ്ങള്‍ ശക്തിപ്പെടുത്തണം എന്ന് റിപ്പോര്‍ട്ടില്‍ ഊന്നല്‍ നല്‍കി പറയുന്നു.
എങ്ങനെയാണു ESZ തിരിച്ചറിഞ്ഞത്?
മൊത്തം പശ്ചിമഘട്ടത്തെ സമിതി 2200 ചതുരങ്ങളായി തിരിച്ച് ഓരോ ചതുരവും 9100 ഹെക്ടര്‍ സ്ഥലത്തെ പ്രതിനിധീകരിക്കുന്നു. ഓരോ മാനദണ്ഡവും അനുസരിച്ച് ഓരോ ചതുരത്തിനും 1 മുതല്‍ 10 വരെ മാര്‍ക്ക് നല്‍കി. ഒടുവില്‍ ഓരോ മാനദണ്ഡത്തിനും ലഭിച്ച മാര്‍ക്കുകളുടെ ശരാശരി ഓരോ ചതുരത്തിനും കണക്കാക്കി. 3 മാര്‍ക്കില്‍ കുറവ് ലഭിച്ച ചതുരങ്ങള്‍ ESZ 3 ആയും 3 മുതല്‍ 5 വരെ മാര്‍ക്ക് ലഭിച്ചവ ESZ 2 ആയും 5 നു മുകളില്‍ മാര്‍ക്ക് ലഭിച്ചവ ESZ 1 ആയും തെരഞ്ഞെടുത്തു.
ESZ 1 15 താലൂക്കുകള്‍
ESZ 2 2 താലൂക്കുകള്‍
ESZ 3 8 താലൂക്കുകള്‍
ESZ 2 2 താലൂക്കുകള്‍
ESZ 3 8 താലൂക്കുകള്‍
ഏതെങ്കിലും താലൂക്ക് പരിസ്ഥിതി ലോല മേഖലയാണെന്ന് പറഞ്ഞാല്‍ ആ താലൂക്ക് മുഴുവന്‍ പ്രസ്തുത മേഖലയിലാണെന്നു അര്‍ഥമില്ല പരിസ്ഥിതി ലോല മേഖലയായി സംരക്ഷണം അര്‍ഹിക്കുന്ന പ്രദേശങ്ങള്‍ ആ താലൂക്കിലുണ്ട് എന്ന് മാത്രമാണ് അതിനര്‍ത്ഥം. അതെവിടെയാണെന്ന് കണ്ടെത്തേണ്ടതും അടയാളപ്പെടുത്തേണ്ടതും ബന്ധപ്പെട്ട പഞ്ചായത്തുകളാണ്. അതും ജില്ലാ പരിസ്ഥിതി സമിതി മുതല്‍ പശ്ചിമഘട്ട പരിസ്ഥിതി അതോറിറ്റി വരെ രൂപീകരിക്കപ്പെടുന്ന ഘട്ടത്തില്‍ മാത്രം. പരിസ്ഥിതി പ്രാധാന്യ സ്ഥലങ്ങള്‍ (ESL) ഇപ്പോള്‍ അടയാളപ്പെടുത്തിയിരിക്കുന്നത് പുനപ്പരിശോധിക്കാവുന്നതാണ്. 25 താലൂക്കുകളിലായി ആകെ 18 പരിസ്ഥിതി പ്രാധാന്യ സ്ഥലങ്ങള്‍ കണ്ടെത്തിയിട്ടുണ്ട്.
ഏതേതു പ്രദേശങ്ങളില്‍ ഏതേതു പ്രവര്‍ത്തനങ്ങള്‍ പ്രോത്സാഹിപ്പിക്കണം എന്നും, ഏതേതു പ്രവര്‍ത്തനങ്ങള്‍ നിയന്ത്രിക്കണം എന്നും അതതു പ്രദേശത്തിന്റെ ഗ്രാമാതിര്‍തികളും സൂക്ഷ്മ നീര്‍ത്തടവും കണക്കിലെടുത്ത് പശ്ചിമഘട്ട പരിസ്ഥിതി അതോറിട്ടിയുടെയും സംസ്ഥാനതല പരിസ്ഥിതി അതോറിട്ടിയുടെയും ജില്ലാതല പരിസ്ഥിതി സമിതികളുടെയും മേല്‍നോട്ടത്തില്‍ ഗ്രാമ പഞ്ചായത്ത്, ജില്ലാ പഞ്ചായത്ത്, നഗരസഭാ പോലുള്ള അതതു പ്രാദേശിക ഭരണകൂടങ്ങള്‍ക്ക് തീരുമാനിക്കാവുന്നതാണ് എന്ന കാര്യം റിപ്പോര്‍ട്ടില്‍ എടുത്തു പറയുന്നുണ്ട്. (പേജ് 40, ഭാഗം ഒന്ന്)
പ്രാദേശിക സംസ്ഥാന സര്‍ക്കാരുകളുടെ സഹായത്തോടെ പശ്ചിമഘട്ടത്തിന്റെ പ്രദേശങ്ങളുടെ പരിസ്ഥിതി സംരക്ഷവും സുസ്ഥിര വികസനവും ഉറപ്പാക്കുന്നതിന് പരിസ്ഥിതി സംരക്ഷണ നിയമത്തിന്റെ കീഴില്‍ പശ്ചിമഘട്ട പരിസ്ഥിതി അതോറിറ്റി കൊണ്ടുവരുന്നതിനുള്ള മാതൃക നിര്‍ദ്ദേശിക്കുക എന്നതായിരുന്നു സമിതിയുടെ മറ്റൊരു കടമ.
പശ്ചിമഘട്ട പരിസ്ഥിതി സമിതി
ചെയര്‍മാന്‍ ഒരു റിട്ട സുപ്രീം കോടതി ജഡ്ജി അല്ലെങ്കില്‍ ഒരു കഴിവുറ്റ പരിസ്ഥിതി ശാസ്ത്രജ്ഞന്‍.
33 അംഗങ്ങള്‍
ബന്ധപ്പെട്ട മന്ത്രാലയങ്ങളുടെ പ്രതിനിധികള്‍
ഫോറസ്ട്രി, ഹൈഡ്രോളജി, മണ്ണ് ശാസ്ത്രം, കൃഷി, ഭൂവിനിയോഗം, പരിസ്ഥിതി, സാമൂഹികശാസ്ത്രം,സാമ്പത്തികശാസ്ത്രം എന്നീ മേഖലകളില്‍ നിന്നും വിദഗ്ധര്‍
.
ഗോത്ര വിഭാഗങ്ങളുടെ പ്രതിനിധികള്‍ (ഓരോ സംസ്ഥാനത്ത് നിന്നും മൂന്നു വര്ഷം വീതം മാറി) ഓരോ സംസ്ഥാനത്ത് നിന്നും സന്നദ്ധ സംഘടനകളുടെ പ്രതിനിധി
.
ഗോത്ര വിഭാഗങ്ങളുടെ പ്രതിനിധികള്‍ (ഓരോ സംസ്ഥാനത്ത് നിന്നും മൂന്നു വര്ഷം വീതം മാറി) ഓരോ സംസ്ഥാനത്ത് നിന്നും സന്നദ്ധ സംഘടനകളുടെ പ്രതിനിധി
സംസ്ഥാനതല സമിതി
ചെയര്‍മാന്‍ ഒരു റിട്ട ജഡ്ജി അല്ലെങ്കില്‍ ഒരു കഴിവുറ്റ പരിസ്ഥിതി ശാസ്ത്രജ്ഞന്‍.
10 അംഗങ്ങള്‍
പരിസ്ഥിതിനിയമ വിദഗ്ധന്‍
ആ പ്രദേശത്തെ പരിസ്ഥിതി വിദഗ്ധന്‍
സന്നദ്ധ സംഘടനകളുടെ 3 കഴിവുറ്റ പ്രതിനിധികള്‍
മലിനീകരണ നിയന്ത്രണ ബോര്‍ഡ് ചെയര്‍മാന്‍, വനംപരിസ്ഥിതി വകുപ്പ് പ്രിന്‍സിപ്പല്‍ സെക്രട്ടറി, പ്ലാനിംഗ് ബോര്‍ഡിന്റെ പ്രതിനിധി, ജൈവ വൈവിധ്യ ബോര്‍ഡിന്റെ ചെയര്‍മാനും മെമ്പര്‍ സെക്രട്ടറിയും.
ജില്ലാതല പരിസ്ഥിതി സമിതി
സംസ്ഥാന സര്‍ക്കാരുമായി കൂടിയാലോചന നടത്തി പശ്ചിമഘട്ട പരിസ്ഥിതി അതോറിറ്റിയാണ് ഇത് രൂപീകരിക്കേണ്ടത്.
പരിസ്ഥിതി ഓംബുട്‌സ്മാന്‍ ആയിരിക്കും ചെയര്‍മാന്‍.
സാമ്പത്തികശാസ്ത്രം, നിയമം, സാമൂഹികശാസ്ത്രം, വനശാസ്ത്രം, മണ്ണ് ശാസ്ത്രം, കൃഷി, ഭൂവിനിയോഗം, പരിസ്ഥിതി തുടങ്ങിയ മേഖലകളിലെ വിദഗ്ധരും ബന്ധപ്പെട്ട വകുപ്പുകളിലെയും സന്നദ്ധ സംഘടനകളിലെയും പ്രതിനിധികളും.
പരിസ്ഥിതി ഓംബുട്‌സ്മാന്‍ ആയിരിക്കും ചെയര്‍മാന്‍.
സാമ്പത്തികശാസ്ത്രം, നിയമം, സാമൂഹികശാസ്ത്രം, വനശാസ്ത്രം, മണ്ണ് ശാസ്ത്രം, കൃഷി, ഭൂവിനിയോഗം, പരിസ്ഥിതി തുടങ്ങിയ മേഖലകളിലെ വിദഗ്ധരും ബന്ധപ്പെട്ട വകുപ്പുകളിലെയും സന്നദ്ധ സംഘടനകളിലെയും പ്രതിനിധികളും.
പരിസ്ഥിതി ലോല മേഖലകളില്‍ പ്രോത്സാഹിപ്പിക്കപ്പെടേണ്ടതും നിരുത്സാഹപ്പെടുത്തേണ്ടതും നിരോധിക്കേണ്ടതുമായ പ്രവര്‍ത്തനങ്ങള്‍
1. പശ്ചിമഘട്ടത്തില്‍ ജനിതകമാറ്റം വരുത്തിയ വിളകള്‍ പാടില്ല.
(കേരള സംസ്ഥാനത്തിന്റെ നേരത്തെയുള്ള നയവും സ്വാമിനാഥന്‍ കമ്മിറ്റിയുടെ ശുപാര്‍ശയും ഇതു തന്നെയാണ്)
(കേരള സംസ്ഥാനത്തിന്റെ നേരത്തെയുള്ള നയവും സ്വാമിനാഥന്‍ കമ്മിറ്റിയുടെ ശുപാര്‍ശയും ഇതു തന്നെയാണ്)
2. കടകളില്‍ നിന്നും ടൂറിസ്റ്റ് സ്ഥലങ്ങളില്‍ നിന്നും കച്ചവട സ്ഥാപനങ്ങളില്‍ നിന്നും 3 വര്ഷം കൊണ്ട്, ഘട്ടം ഘട്ടമായി, മുന്‍ഗണനാ ക്രമത്തില്‍ പ്ലാസ്റ്റിക് ബാഗുകളുടെ ഉപയോഗം ഒഴിവാക്കുക.
(പ്ലാസ്റ്റിക് നിരോധനമല്ല)
(പ്ലാസ്റ്റിക് നിരോധനമല്ല)
3. പുതിയ പ്രത്യേക സാമ്പത്തിക മേഖലകളും ഹില്‍ സ്‌റ്റേഷനുകളും അനുവദിക്കരുത്.
4. പൊതു ഉടമസ്ഥതയിലുള്ള ഭൂമി ഇനി സ്വകാര്യ ഭൂമിയാക്കരുത്.
(അതിനര്‍ത്ഥം 1977 വരെയുള്ള കയ്യേറ്റ/കുടിയേറ്റക്കാര്‍ക്ക്, നേരത്തെ പട്ടയം കൊടുക്കാന്‍ തീരുമാനിച്ചിരുന്നവര്‍ക്ക് പട്ടയം കൊടുക്കേണ്ടതില്ല എന്നല്ല. പുതുതായി കയ്യേറ്റങ്ങള്‍ അനുവദിക്കരുത് എന്നാണ്)
5. വനഭൂമി വനേതര ആവശ്യങ്ങള്‍ക്കും കൃഷിഭൂമി കാര്‍ഷികേതര ആവശ്യങ്ങള്‍ക്കും വകമാറ്റരുത്. എന്നാല്‍ കൃഷി ഭൂമി വനമാക്കുന്നതിനോ, നിലവിലുള്ള പ്രദേശങ്ങളിലെ ജനസംഘ്യാ വര്‍ധനവിന് ആവശ്യമാകുന്ന വിധത്തില്‍ വികസനം കൊണ്ടുവരുന്നതിനോ വീടുകള്‍ വെയ്ക്കുന്നതിനോ ഈ നിയന്ത്രണം ബാധകമല്ല.
(വികസനം മുരടിക്കും, കുടിയോഴിപ്പിക്കും എന്ന ആശങ്കകള്‍ക്ക് യാതൊരു അടിസ്ഥാനവുമില്ല)
6. ഭൗതിക വികസനം പാരിസ്ഥിതിക മൂല്യതകര്‍ച്ചയെയും പൊതുഗുണത്തെയും ആസ്പദമാക്കി നടത്തുന്ന പാരിസ്ഥിതിക ആഘാത പഠനത്തിന്റെ അടിസ്ഥാനത്തില്‍ ആയിരിക്കണം.
7.പരിസ്ഥിതി സൗഹൃദ നിര്‍മ്മാണ വസ്തുക്കളുടെയും, നിര്‍മ്മാണ രീതികളുടെയും, മഴവെള്ള സംഭരണിയുടെയും, പാരമ്പര്യേതര ഊര്‍ജ്ജത്തിന്റെയും മാലിന്യ സംസ്‌കരണത്തിന്റെയും എല്ലാം അടിസ്ഥാനത്തില്‍ പശ്ചിമഘട്ട പരിസ്ഥിതി അതോറിറ്റി കെട്ടിടനിര്‍മ്മാണ മാര്‍ഗ നിര്‍ദ്ദേശങ്ങള്‍ ഉണ്ടാക്കേണ്ടതാണ്.
(അതിനര്‍ത്ഥം കമ്പിയും സിമന്റും നിരൊധിക്കുമെന്നല്ല, ലഭ്യത കുറയുന്ന വിഭവങ്ങള്‍ ബുദ്ധിപരമായ അളവിലുള്ള ഉപയോഗമേ പാടുള്ളൂ എന്നാണ്)
8. മാരകമോ വിഷലിപ്തമോ ആയ രാസപദാര്‍ഥങ്ങള്‍ സംസ്‌കരിക്കുന്ന പുതിയ ശാലകള്‍ സോണ്‍ ഒന്നിലും രണ്ടിലും പാടില്ല. ഇപ്പോള്‍ ഉള്ളവ, 2016 നുള്ളില്‍ ഒഴിവാക്കപ്പെടെണ്ടതാണ്.
മലിനീകരണ നിയന്ത്രണങ്ങള്‍ കര്‍ശനമായി പാലിച്ചുകൊണ്ട് അവ മൂന്നാം സോണില്‍ പ്രവര്‍ത്തിപ്പിക്കാവുന്നതാണ്.
മലിനീകരണ നിയന്ത്രണങ്ങള്‍ കര്‍ശനമായി പാലിച്ചുകൊണ്ട് അവ മൂന്നാം സോണില്‍ പ്രവര്‍ത്തിപ്പിക്കാവുന്നതാണ്.
9.പ്രാദേശിക ജൈവ വിഭവങ്ങള്‍ ഉപയോഗിച്ചുള്ള വ്യവസായങ്ങള്‍ നിര്‍ബന്ധമായും പ്രോത്സാഹിപ്പിക്കണം.
10. നിയമവിരുദ്ധ ഖനനം അടിയന്തിരമായി നിര്‍ത്തലാക്കണം.
11. ജല വിഭവ പരിപാലനം തദ്ദേശ സ്വയംഭരണ സ്ഥാപനങ്ങളുടെ തലത്തില്‍ വരെ വികേന്ദ്രീകരിക്കണം.
(ജലം ഒരു മൂലധന ചരക്കായി കാണണമെന്നും പൊതുസ്വകാര്യ പങ്കാളിത്തത്തോടെ വില്‍പ്പന നടത്താമെന്നും ഉള്ള നിലവിലെ ദേശീയ ജല നയത്തിന്റെ വെളിച്ചത്തില്‍ ഈ നിര്‍ദ്ദേശം ജനോപകാരപ്രദമാണ് )
(ജലം ഒരു മൂലധന ചരക്കായി കാണണമെന്നും പൊതുസ്വകാര്യ പങ്കാളിത്തത്തോടെ വില്‍പ്പന നടത്താമെന്നും ഉള്ള നിലവിലെ ദേശീയ ജല നയത്തിന്റെ വെളിച്ചത്തില്‍ ഈ നിര്‍ദ്ദേശം ജനോപകാരപ്രദമാണ് )
12. ഉയര്‍ന്ന പ്രദേശങ്ങളിലുള്ള സ്വാഭാവിക ജല സംഭരണികളും മറ്റും സംരക്ഷിക്കുക.
13. ശാസ്ത്രീയ പരിഹാര സംവിധാനങ്ങളുടെ സഹായത്തോടെ, ജനകീയ പങ്കാളിത്തത്തില്‍ ജല ത്തിന്റെ ഗുണവും പുഴയുടെ ഒഴുക്കും മെച്ചപ്പെടുത്തുക.
14. രാസകീടനാശിനികളുടെയും കളനാശിനികളുടെയും ഉപയോഗം സോണ്‍ ഒന്നില്‍ 5 വര്‍ഷത്തിനകവും സോണ്‍ രണ്ടില്‍ 8 വര്‍ഷത്തിനകവും സോണ്‍ മൂന്നില്‍ 10 വര്‍ഷത്തിനകവും പൂര്‍ണ്ണമായി ഒഴിവാക്കിക്കൊണ്ട് ജൈവകൃഷി രീതികള്‍ പ്രോത്സാഹിപ്പിക്കുക.
(സംസ്ഥാനത്തിന്റെ ജൈവകൃഷി നയം തന്നെയാണ് ഇത്. ദേശീയ ദാരിദ്രനിര്‍മ്മാര്‍ജന മിഷന്റെ സഹായത്തോടെ ആന്ധ്രയില്‍ 35 ലക്ഷം ഏക്കറില്‍ രാസകീടനാശിനി ഇല്ലാതെ കൃഷി നടത്തുന്നത് ഉത്തമ മാതൃകയാണ്)
15. രാസകൃഷിയില്‍ നിന്നും ജൈവ കൃഷിയിലേക്ക് മാറുന്ന ഘട്ടത്തില്‍ കര്‍ഷകര്‍ക്ക് സാമ്പത്തികവും സാങ്കേതികവുമായ എല്ലാ സഹായവും ലഭ്യമാക്കണം.
16. കാലിത്തീറ്റ ആവശ്യകത പരിപാലിക്കുന്നതിനും അതിന്റെ ഉത്പാദനം വര്‍ദ്ധിപ്പിക്കുന്നതിനും ഉള്ള ആസൂത്രണത്തിന് പ്രാദേശിക സമൂഹങ്ങള്‍ക്ക് സഹായം നല്‍കുക.
17.രണ്ടു കന്നുകാലിയെങ്കിലും ഉള്ള കുടുംബത്തിനു ബയോഗ്യാസ് പ്ലാന്റ് നിര്‍മ്മിക്കാന്‍ ആവശ്യമായ സഹായം നല്‍കുക. ഗ്രാമതലത്തില്‍ വലിയ ബയോഗ്യാസ് പ്ലാന്റ് നിര്‍മ്മിക്കാവുന്ന സാധ്യതകള്‍ അന്വേഷിക്കണം. (പേജ് 47) (രണ്ടിലധികം കന്നുകാലികളെ അനുവദിക്കില്ല എന്ന ആശങ്ക അടിസ്ഥാന രഹിതമാണ്)
18. തീവ്ര അതിതീവ്ര മലിനീകരണമുള്ള വ്യവസായങ്ങള്‍ സോണ്‍ ഒന്നിലും രണ്ടിലും പാടില്ല. നിലവിലുള്ള വ്യവസായങ്ങള്‍ 2016 നുള്ളില്‍ മലിനീകരണം പൂര്‍ണ്ണമായി ഒഴിവാക്കുകയും സോഷ്യല്‍ ഓഡിറ്റിനു വിധേയമാക്കുകയും ചെയ്യുക.
19. സോഷ്യല്‍ ഓഡിറ്റിനും കര്‍ശന നിയന്ത്രണങ്ങള്‍ക്കും വിധേയമായി സോണ്‍ മൂന്നില്‍ പുതിയ വ്യവസായങ്ങള്‍ അനുവദിക്കാം.
20. സൗരോര്‍ജ്ജ ഉപയോഗം പ്രോത്സാഹിപ്പിക്കുക.
21. സോണ്‍ ഒന്നില്‍ പ്രാദേശിക ഊര്‍ജ്ജാവശ്യം കണക്കിലെടുത്ത്, പാരിസ്ഥിതികാഘാത പഠനം നടത്തി, പരമാവധി 3 മീറ്റര്‍ വരെ ഉയരമുള്ള റണ്‍ ഓഫ് ദി റിവര്‍ പദ്ധതിയും,
സോണ്‍ രണ്ടില്‍ 10 മുതല്‍ 25 വരെ മെഗവാട്ട് വൈദ്യുതി (പരമാവധി 10 മീറ്റര്‍ ഉയരം) ഉത്പാദിപ്പിക്കാവുന്ന പുതിയ ജലവൈദ്യുത പദ്ധതികളും,
സോണ്‍ മൂന്നില്‍ പാരിസ്ഥിതികാഘാത്ത പഠനത്തിനു ശേഷം വന്‍കിട ഡാമുകളും അനുവദിക്കാവുന്നതാണ്.
സോണ്‍ രണ്ടില്‍ ജനങ്ങളുടെ ഉടമസ്ഥതയില്‍ ഓഫ് ഗ്രിഡ് ആയി ചെറുകിട ജലവൈദ്യുത പദ്ധതികള്‍ പ്രോല്‌സാഹിപ്പിക്കപ്പെടെണ്ടതാണ്.
22. വികേന്ദ്രീകൃത ഊര്‍ജ്ജാവശ്യങ്ങള്‍ക്ക് ജൈവ മാലിന്യ/സോളാര്‍ ഉറവിടങ്ങള്‍ പ്രോത്സാഹിപ്പിക്കുക.
23.എല്ലാ പദ്ധതികളും ജില്ലാതല പരിസ്ഥിതി സമിതിയുടെ മേല്‍നോട്ടത്തില്‍ തദ്ദേശ സ്വയംഭരണ സ്ഥാപനങ്ങളുടെയും ഊര്‍ജ്ജ ബോര്‍ഡുകളുടെയും സംയുക്ത ശ്രമത്തില്‍ പ്രവര്‍ത്തിപ്പിക്കേണ്ടതാണ്.
24. സ്വാഭാവിക ജീവിതകാലം അതിക്രമിച്ചുകഴിഞ്ഞ താപനിലയങ്ങളും ഡാമുകളും (ഡാമുകളുടെ സാധാരണ കാലാവധി 30- 50 വര്‍ഷമാണ്) ഘട്ടം ഘട്ടമായി ഡീക്കമ്മീഷന്‍ ചെയ്യണം. (പേജ് 46, ഭാഗം 1)
അംഗീകരിക്കാന്‍ കഴിയുന്ന പരിധിയിലധികം ചെളി അടിഞ്ഞതോ പ്രവര്‍ത്തന ക്ഷമം അല്ലാത്തതോ ഉപയോഗശൂന്യമായതോ കാലഹരണപ്പെട്ടതോ ആയ ഡാമുകള്‍ ഘട്ടം ഘട്ടമായി ഡീക്കമ്മീഷന്‍ ചെയ്യാന്‍ ശുപാര്‍ശ ചെയ്യുന്നു.
(മുല്ലപ്പെരിയാര്‍ പോലുള്ള ദുരന്ത ആശങ്ക വരുന്നതുവരെ കാക്കാതെ കാര്യങ്ങള്‍ ദീര്‍ഘവീക്ഷണത്തോടെ സമീപിക്കുന്നു)
25. മത്സ്യ സഞ്ചാര പാതകള്‍ തടസ്സപ്പെട്ടിട്ടുണ്ടെങ്കില്‍ മത്സ്യ പ്രജനനം നടക്കാന്‍ അവിടെയൊക്കെ മത്സ്യ ഏണി പ്രദാനം ചെയ്യുക.
26. സ്‌ഫോടക വസ്തുക്കള്‍ ഉപയോഗിച്ചുള്ള മത്സ്യബന്ധനം നിരോധിക്കുക.
27. വനാവകാശ നിയമത്തിനു കീഴില്‍ ചെറുകിട, പാരമ്പര്യ ഭൂവുടമകളുടെ അവകാശം അംഗീകരിക്കുക.
28. വനാവകാശ നിയമം അതിന്റെ ആത്മാവ് സംരക്ഷിക്കുന്ന രീതിയില്‍ സാമുദായിക വനപരിപാലനത്തോടെ നടപ്പാക്കുക.
29. ഒന്നും രണ്ടും സോണുകളില്‍ പുതുതായി ഖനനത്തിന് അനുമതി നല്‍കാതിരിക്കുക. നിലവിലുള്ളവ 2016 ഓടെ നിര്‍മ്മാര്‍ജ്ജനം ചെയ്യുക. സോണ്‍ രണ്ടില്‍ ഓരോരോ കേസുകളായി പുനപ്പരിശോധിക്കാവുന്നതാണ്. പ്രാദേശിക ആദിവാസി സമൂഹങ്ങളുടെ മുന്‍കൂര്‍ അനുമതിയും സോഷ്യല്‍ ഓഡിറ്റും കര്‍ശന മാനദണ്ഡങ്ങളും അനുസരിച്ച് മറ്റിടങ്ങളില്‍ ലഭ്യമല്ലാത്ത ധാതുക്കള്‍ക്കായി സോണ്‍ മൂന്നില്‍ ഖനനം പുതുതായി അനുവദിക്കാം.
30. വളരെ അത്യാവശ്യത്തിനല്ലാതെ, സോഷ്യല്‍ ഓഡിറ്റിനും കര്‍ശന നിയന്ത്രണത്തിനും പരിസ്ഥിതി ആഘാത പഠനത്തിനും ശേഷമല്ലാതെ, ഒന്നും രണ്ടും സോണുകളില്‍ പുതിയ വന്‍കിട റോഡുകളോ റെയില്‍വേ പാതകളോ അനുവദിക്കരുത്. സോണ്‍ മൂന്നില്‍ അനുവദിക്കാം.
31. എല്ലാ പുതിയ ഡാം, ഖനന, ടൂറിസം, പാര്‍പ്പിട പദ്ധതികളുടെയും സംയുക്ത ആഘാത പഠനം നടത്തി, ജലവിഭവങ്ങള്‍ക്ക് മേലുള്ള അവയുടെ ആഘാതം അനുവദനീയമായ അളവിനകത്തു മാത്രം ആണെങ്കിലേ അനുവാദം നല്‍കാവൂ.
32. തൊഴിലുറപ്പ് പദ്ധതിയുടെ ഗുണം ചെറുകിട ഇടത്തരം കര്‍ഷകര്‍ക്ക് ലഭ്യമാക്കണം. (പേജ് 40 ഭാഗം 2).
33. വന്‍കിട തോട്ടങ്ങളില്‍ കള നിയന്ത്രണത്തിനുള്ള യന്ത്രങ്ങള്‍ക്കു സബ്‌സിഡി ലഭ്യമാക്കുക. (പേജ് 40 ഭാഗം 2).
34. പാവപ്പെട്ടവന്റെ ജീവനോപാധി നിലനിര്‍ത്തുകയും എല്ലാവര്‍ക്കും സുസ്ഥിര വികസനം സാധ്യമാക്കുകയും ചെയ്യുക എന്നതാണ് പശ്ചിമഘട്ട പരിസ്ഥിതി വിദഗ്ധസമിതി റിപ്പോര്‍ട്ടിന്റെ ഊന്നല്‍ .
35. താഴെ പറയുന്ന കാര്യങ്ങള്‍ക്ക് ‘സംരക്ഷണ സേവന വേതനം’ (പണമായി) നടപ്പാക്കുക.
മ). പാരമ്പര്യ വിത്തുകള്‍ കൃഷി ചെയ്യുന്ന കര്‍ഷകര്‍ക്ക്.
യ). പാരമ്പര്യ കന്നുകാലി വര്‍ഗ്ഗങ്ങളെ വളര്‍ത്തുന്ന കര്‍ഷകര്‍ക്ക്
ര). നാടന്‍ മത്സ്യ വര്‍ഗ്ഗങ്ങളെ ടാങ്കില്‍ വളര്‍ത്തുന്ന കര്‍ഷകര്‍ക്ക്
റ). കാവുകള്‍ സംരക്ഷിക്കുന്നവര്‍ക്ക്
ള). സ്വാഭാവിക പ്രകൃതി സംരക്ഷിക്കുന്നവര്‍ക്ക്
36. വികസന പദ്ധതികള്‍ തീരുമാനിക്കുന്നത് ഗ്രാമാസഭകളിലൂടെയുള്ള പങ്കാളിത്ത സംവിധാനത്തിലൂടെ ആയിരിക്കണം (പേജ് 32, ഭാഗം 2)
37. പരിസ്ഥിതി പരിപാലനത്തിനുള്ള കഴിവുണ്ടാക്കുന്നതില്‍ പഞ്ചായത്തുകളെ ശക്തരാക്കുക.
38. ഖനനത്തില്‍ നിന്നും ലഭിക്കുന്ന വരുമാനം പ്രാദേശിക പഞ്ചായത്തുകളുമായി പങ്കുവെയ്ക്കുക.
39. തങ്ങളുടെ സ്ഥലത്തിന്റെ നല്ലൊരു ശതമാനം ഭാഗം വനസംരക്ഷണത്തിനായി നീക്കി വെയ്ക്കുന്ന പശ്ചിമഘട്ട സംസ്ഥാനങ്ങള്‍ക്ക് നഷ്ടപരിഹാരം നല്‍കാന്‍ പ്രത്യേകം സംവിധാനമൊരുക്കുക.
40. കൃഷിഭൂമിയില്‍ പിടിച്ചു വെച്ച് അന്തരീക്ഷ കാര്‍ബണ്‍ കുറയ്ക്കുന്ന ജൈവകൃഷിയിലേക്ക് മാറുന്നവര്‍ക്ക് പ്രത്യേക ആനുകൂല്യം നല്‍കുക.
Friday, November 15, 2013
office 2007
Page | 1
Unit 5: MS-EXCEL 2007 Introducing Microsoft Office Excel 2007
LEARNING OBJECTIVES
Opening Ms-Excel
Working with Spreadsheet
Using data in spreadsheet
Active cell
Microsoft Excel is a spreadsheet program that provides with powerful tools and features to analyze share and manage your data with least effort. A Spreadsheet is defined as a large sheet containing information arranged in a matrix of rows and columns. Spreadsheet software is a program or a set of programs for entering, calculating, analyzing and manipulating sets of number. A computer spreadsheet automatically recalculates all the formulae whenever you make a change in the value or number. There are so many other spreadsheet programs such as Lotus 1-2-3, Quattro Pro etc. are available, but Excel is by far the most popular and has become the world standard.
Steps to Open Microsoft Office Excel
1) Click Start MenuAll ProgramsMicrosoft OfficeMicrosoft Office Excel 2007
Alternative Way
1) Right-clickChoose New from the pop-up menu.
2) Click Microsoft Office Excel Worksheet from the sub-menu.
3) Double-click on the Microsoft Office Excel Worksheet icon to open.
When you open Microsoft Office Excel 2007, the first thing you will notice is the new interface. Now that we’ve opened Excel, let’s take a look at what’s on our screen. Refer to figure below.
A brief discussion about the different components of Excel’s opening Screen:
Home: You’ll probably spend most of your time with the Home tab selected. This tab contains the basic Clipboard commands, formatting commands, style commands, commands to insert and delete rows or columns, plus an assortment of worksheet editing commands
Insert: Select this tab when you need to insert something in a worksheet—a table, a diagram, a chart, a symbol, and so on.
Page Layout: This tab contains commands that affect the overall appearance of your worksheet, including settings that deal with printing.
Formulas: Use this tab to insert a formula, name a range, access the formula auditing tools, or control how Excel performs calculations.
Data: Excel’s data-related commands are on this tab.
Review: This tab contains tools to check spelling, translate words, add comments, or protect sheets.
View: The View tab contains commands that control various aspects of how a sheet is viewed. Some commands on this tab are also available in the status bar.
Control Buttons: At the right end of the title bar are the three familiar buttons that have the same functions in all windows programs. You can temporarily hide the word window by clicking the Minimize button, adjust the size of the window with the Restore Down/Maximize Button,
and close the active document or quit excel with the close button.
Name Box: Address bar shows the address of the active cell. In the above figure active cell is A1 that is why we are getting A1 in the name box.
Formula Bar: The formula bar is located beneath the toolbar at the
top of the Excel worksheet. Use the formula bar to enter and edit
worksheet data. The contents of the active cell always appear in the
formula bar.
Rows, Columns and Cells: Every worksheet consists of rows (numbered 1 through 1,048,576) and columns (labeled A through XFD). After column Z comes column AA, which is followed by AB, AC, and so on. After column AZ comes BA, BB, and so on. After column ZZ is AAA, AAB, so on. Each worksheet has 16,384 columns and
10,48,576 rows. So, each worksheet contains 16384 X 1048576 numbers of cells.
Home Ribbon
Ribbon Tabs
A cell is the intersection of a row and a column. For example, the first cell is referred to as A1 (column A, row 1). Data and formulas are entered into the cells. Each cell can hold only one piece of data at a time.
Sheet Tabs: Sheet tabs holds list of worksheets by default it provides three Worksheets named Sheet1, Sheet2, and Sheet3
respectively.
Horizontal and Vertical Scroll Bar: The Scroll Bar is used roll up/down/right and left and see the column/row number changes until you release the mouse button.
Zoom Slider: A slider that magnifies or reduces the content in the worksheet area.
Groups: On each tab, buttons are organized into groups. Depending on the size of the program window, in some groups the button you are likely to use most often is bigger than the rest. Dialog box launcher: Related but less common commands are not represented as buttons in the group. Instead they are available in a dialog box, which you can display by clicking the Dialog Box Launcher at the right end of the group’s title bar
Workbook and Worksheet
When you open Excel, the Excel application window opens with a new Excel workbook. A Workbook is a multi-page Excel document. After saving the Excel file or workbook it takes the default extension .XLSX in its filename. Each page in the workbook is called a Worksheet. The active worksheet is displayed in the document window. By default, a new workbook has 3 sheets. You can add or remove sheets from a workbook to suit your need. At the left end of the horizontal scroll bar are sheet tabs and navigation buttons. Click a sheet tab to move to another worksheet or use the navigation buttons to scroll through the sheet tabs.
Types of Data you can Enter in Excel
An Excel workbook can hold any number of worksheets, and each worksheet is made up of more than 17 billion cells. A cell can hold any of three basic types of data:
Numerical values
Text
Formulas
A worksheet can also hold charts, diagrams, pictures, buttons, and other objects.
About Numerical Values
Numerical values represent a quantity of some type: sales amounts, number of employees, atomic weights, test scores, and so on. Values also can be dates (such as Nov-11-2009) or times (such as 3:24 p.m.).
About text entries
Most worksheets also include text in their cells. You can insert text to serve as labels for values, headings for columns, or instructions about the worksheet. Text is often used to clarify what the values in a worksheet mean.
Text that begins with a number is still considered text. For example, if you type “12 Employees” into a cell, Excel considers the entry to be text rather than a value. Consequently, you can’t use this cell for numeric
Calculations. If you need to indicate that the number “12” refers to employees, enter 12 into a cell and type Employees into the cell to the right.
About formulas
Formulas are what make a spreadsheet a spreadsheet. Excel enables you to enter powerful formulas that use the values (or even text) in cells to calculate a result. When you enter a formula into a cell, the formula’s result appears in the cell. If you change any of the values used by a formula, the formula recalculates and shows the new result.
Entering Data in Cell
Entering data in Excel sheet is very easy. You need not concentrate a lot to learn this thing. There are many types of data that you can enter into your worksheets including text, numbers, dates, times, formulae, and functions. Text is any combination of letters, numbers, and spaces. By default, text is automatically left aligned in a cell. However, Numbers and Dates are right aligned.
Active Cell
In an Excel worksheet, the active cell is the cell with the black border. Data can only be entered into the active cell. Even if more than one cell is selected, there is still only one active cell. Use the mouse pointer or the arrow keys to change the location of the active cell.
Number Entered
Date Entered
Text Entered
Also Known As: Current Cell
Examples: Click on cell B2 with the mouse pointer to make it the active cell before entering the data.
Moving Around a Worksheet
As you probably already know, you can use the standard navigational keys on your keyboard to move around a worksheet. These keys work just as you’d expect: The down arrow moves the active cell down one row, the right arrow moves it one column to the right, and so on. PgUp and PgDn move the active cell up or down one full window. (The actual number of rows moved depends on the number of rows displayed in the window.)
Active Cell has a black border.
Name Box, Copy & Paste, Cell Editing, Allignment and Saving File
LEARNING OBJECTIVES
Using name box
Copy and paste function
Cell editing
Alignment
Saving workbook Navigating using Name box
For larger worksheets (remember there are over 16,000 columns and more than one million rows in a single Excel 2007 worksheet) there has to be a better way to get around then endless scrolling with the mouse - and there is - by using the Name Box. Learn how to jump from one area of your spreadsheet to the next in this book.
Editing Cells in Excel
You can change a data in a cell directly by overwriting the same using mouse and keyboard.
Using Mouse
Suppose you have entered a wrong word “Janury” in Cell A1. You need to edit the text as “January”. Now follow the steps to edit the data using mouse.
Method
1) Double-click on Cell A1 for editing. Edit the part of the cell you want to change using Backspace key.
2) Finally type in the new data and press enter key.
Type the destination cell reference in the Name box.
Copying and Pasting data in Excel
You can save a great deal of time in Excel by copying data rather than repeating the steps needed to recreate or reapply them over and over. Learn how to use Excel's copy and paste options to copy items to different areas of the worksheet.
Using Mouse
You can copy an entry into surrounding cells by using copy and paste command under Home Tab using mouse.
Steps:
1) In the cell A1, type January and press enter key to accept the data.
2) Keep your cursor on the cell A1 and click on Copy command under Home tab.
(Suppose you want to copy the text January in B2, C2 & D2 Cells.)
Position the mouse pointer on the cell B2 and drag till Cell D2 as shown above.
3) Now click Paste command.
Moving data in Excel
Sometimes we don't need to copy data, headings or formulas, but just move them to a new location. Learn how to do this using Excel's cut and paste features.
Using Mouse
You need to move the data in Column-A to Column-C as shown below using mouse.
Steps:
1) Place the cursor on the Cell A1 and drag till Cell A4 by holding Left mouse button.
2) Click Cut (CTRL+X) command under home tab.
3) Place the cursor on target cell (C1) and then Click Paste Command to move the content from Column-A to Column-C.
Paste Command (CTRL+V)
Copy Command (CTRL+C)
Format Data using Font Group
There are many ways of smartening up a worksheet, for example changing font, font size, font style or using white text on a black background. Excel mainly supports two types of data: Text and Number. You can format them using font group. The font group provides the quickest way for controlling various fonts attributes such as the font and the font size, colour etc. To access the font group, just click the Home tab. All of these commands are covered below.
1) In cell A1 type the heading ‘Monthly Sales’.
2) Select the text and click Font drop lists to make it Stencil, 12 point and Bold or Click Increase/Decrease Font command.
3) To make white text on a black background. Choose white font colour and Black fill colour from Font colour drop lists and Fill colour drop lists respectively.
Border
4) Adding lines and borders is a quick way to format important information in Excel. For example, type amount 20000, 75000, 40000 and 60000 in Cell A2:A5. Select cell A3, Click Border drop lists and select ‘Thick Bottom Border’. The effect you can see below.
Alignment Group Options
There are many ways of formatting cell alignment, for example changing the alignment of the cell contents, position the cell contents in the cell, and change the direction of the cell contents. Combines two or more selected cells into a single cell and wraps text into multiple lines in a cell. All of these commands are covered below.
1) Here you can use another type of alignment command called Merge & Center. The Merge and Center button on the Alignment group lets you quickly center text in the left-most cell across the entire range of cells you select.
Select Cell A1:C1 and Click Merge & Center command. This feature is good for centering a title and subtitle at the top of a worksheet as shown below.
2) In the above example, Salesman names are very long and the full name is not visible. To make all content visible without increasing the column width, you need to use Wrap Text command under Alignment group. Wrap text command is use to place a long text on a multiple lines within a single cell. Select Cell A3:A6 and Click Wrap Text button under Alignment group of Home Tab.
Before Merging Cells
After Merging Cells
3) Now, you need to keep the month and Amount in proper alignment like center, Left, Top etc. Select Cell B3:B6 and Click Center and Middle Align button. Similarly the Cell C3:C6 and Click Middle Align button.
4) Now you want to rotate text to a diagonal angle or vertical orientation, which is often used to narrow columns. Place the cursor on Cell B2 and click Orientation drop lists to select the required button. You can also use Increase/decrease Indent command to move the content from any edge of the cell depending on your choice. Each increment in the Indent box is equivalent to the width of one character. To use indent, keep the cursor on a target cell and click Increase/decrease indent button.
Number Group Options
Use the options on the Number tab to apply a specific number format to numbers in cells on the worksheet. To type numbers in worksheet cells, you can use the number keys, or you can press NUM LOCK, and then use the number keys on the numeric keypad. Let’s take an example to explain the use of number group options as shown below.
To learn all the above commands, let take a database as shown below.
1) To change the amount in Indian currency (Rs.). First go to StartControl PanelDouble-Click on Regional and Language Options.
2) Click Customize button to open Customize Regional Options dialog box.
3) Then Click Currency Tab. Change the currency symbol as “Rs” instead of “$” symbol and Click OK.
4) Now, select Cell C3:C6 and choose Indian Currency from Accounting Number Format drop lists.
If you want to display thousands separator instead of Indian currency, then click Comma style button. You can also increase or decrease decimal places by clicking Increase/Decrease decimal place button as shown below.
5) To allow a Percentage (%) sign as suffix with the numeric data and multiply by 100, you need to click percentage style button. Select Cell D3:D6 and click percentage style as shown below.
Adjusting the System Date Settings
If you dates are being interpreted as April 1st instead of January 4th, this has to be altered in the Control Panel. You probably won’t be able to do this on a network but you can do it on your home machine or get the network manager to correct the settings.
1) Go to StartControl Panel.
2) Double-Click on Regional and Language Options.
3) Click Customize button to open Customize Regional Options dialog box.
4) Click Date Tab. Change the Short date format as “dd/mm/yy” and Click OK.
5) Save and reopen an existing workbook for new setting to take effect.
Saving a Workbook using Keyboard
Press key to display Save As dialog box as shown above.
Type a new file name and click Save button.
Opening an Existing Workbook
To open an existing file of Excel from the permanent disk:
1) Select Office Button Open or press.
2) The Open dialog box will appear, choose your file from relevant path and click Open button.
Closing an Excel Workbook.
To exit the active workbook window:
Go to Office Button Click Exit Excel.
To Close Excel Workbook using Keyboard, press key
Class Exercise
1) Open a new worksheet and put the following information in Sheet-1.
2) Format field names (second row) with Font face: Cambria; Font Size: 12 points; Font Style: Bold; Colour: Blue; Border: All border; Fill Color: Olive Green and records (third to sixteen rows) with Font face: Arial; Font Size: 10 points.
3) Merge and center first seven columns fo the first row for the title ‘Student Details’.
4) Format the heading of the database “Student Details” with Font face: Bernard MT Condensed; Font Size: 18 points; Border: Thick Box border; Fill Color: Black and Font Colour: White.
5) Apply Indian Currency (Rs.) format on Fees column with two decimal places.
6) Apply long date format on Date of Admission column.
7) Create a new folder with your name followed by ‘ExcelWork’ under C:\Candidates folder (e.g. Arjun_ExcelWork) and Save the current workbook with a name ‘Excel Assignment’ under your folder.
Place the mouse pointer on the separator of two column letters or row numbers when mouse pointer converts a symbol like below double click. Column Width, Math Function, Workspace and Saving File
LEARNING OBJECTIVES
Column width
Mathematical function
Workspace creation Column width and default size
If the cell width is not adequate for the contents of a column, you can change it to its exact required size using either Best Fit feature or AutoFit option. Best Fit is used not only to change the cell width as well as to change the row height also.
Place the mouse pointer on the separator of the column letters or row numbers, then double-click on it, immediately the Column width or Row height will be changed to display the widest entry within that particular column or row. Always you have to consider the separator between the current column or row and next column or row. This is known as Best Fit feature.
How to select a cell, a range, or text in a cell
Simple Mathematical Calculation
Formulas are used to obtain answers based on mathematical equations that you design. Formulas can be as simple as "2+2" or as complex as calculating the depreciation of fixed assets. When creating formulas, you may use actual values, cell addresses or a combination of the two.
The equal sign (=) is used to indicate to Excel that you are about to enter a formula. This also ensures that formulas beginning with a cell address are not mistaken for text.
Next, you must enter the actual formula for Excel to calculate. When entering these formulas, the following basic mathematical operators are used:
+ (Addition)
- (Subtraction)
* (Multiplication)
/ (Division)
When a formula is complete and the key has been pressed, the result will be displayed in the current cell. The formula itself is displayed in the formula bar (located in the upper-left of the screen next to the cell address).
Let’s take a database to explain the above mathematical operator’s.
1) In the above example, to calculate amount, place the cursor on Cell E3 and type “=” sign, then click Cell C3, type asterisk sign (*) and then click Cell D3 and press enter to accept the formula. Now drag the fill handle till Cell E5 to copy the formula in the respective cells.
2) Calculation of Total Amount, Vat, Discount and Net Total is discussed below. Operators Used in Formulas Operator Name Formula What It Does
+
Addition
=5+6 will return 11
–
Subtraction
=6-5 will return 1
*
Multiplication
=6*5 will return 30
/
Division
= 6/2 will return 3
^
Exponentiation
=6^3 will return 216
Drag the fill handle
Let’s take another database to explain the “/” Division operator as shown below.
Working with Worksheet
Changing Sheet Name
You can change the name of the sheet tabs:
1) Double-click on the sheet tab of the worksheet you want to rename or right-click on the tab, type the name and press enter.
2) Select Rename option from the context sensitive popup menu , type the name and press enter
3) The current name will be highlighted. Type a new name and press Enter key.
Adding a New Worksheet
1) Right-click on the sheet tab
2) Choose and click Insert option from the popup menu.
To quickly insert a new worksheet at the end of the existing worksheets, click the Insert Worksheet tab at the bottom of the screen. Click the drop lists of Insert from the Cell group under home tab.
Removing a Worksheet
1) Right-click on the sheet tab
2) Choose and click Delete option from the popup menu.
Caution: Be careful when deleting worksheets as you cannot undo the deletion.
OR
1) Click the drop lists of Delete from the Cell group under home tab.
2) Choose and click Delete sheet.
Working with Rows and Columns
Rows and columns make up an Excel worksheet. Every worksheet has exactly 1,048,576 rows and 16,384 columns, and these values can’t be changed. You can insert/delete Row(s) and Column(s) but here rows and columns will be deleted automatically from the last according to the number of rows or columns inserted.
Inserting rows in excel is very simple. Right click on row number before which you want to insert the row and Select insert option from the popup menu that appears. Accordingly you can insert column(s) right clicking on the selected columns and clicking on the insert option. Columns always inserts just before your selected column(s).
Alternatively,
You can also insert Row(s) or columns from the home Tab ribbon clicking on the Insert button and clicking on the Insert sheet rows or columns option.
Deleting Rows or Columns
Row(s) or Column(s) can be deleted by right clicking on the selected Row(s) or Column(s) and clicking on the Delete option.
Right click on the selected rows and click on Delete option from the Dropdown list, selected row(s) or column(s) will be deleted. Here one row is selected naturally one row will be deleted.
Accordingly you can delete column(s) right clicking on the selected columns and clicking on Delete option from the Drop down list.
Alternatively,
You can also delete selected row(s) or column(s) clicking on Delete sheet Rows or Columns from the insert button under the home tab ribbon’s Cells group.
Right click on the selected rows and click on Delete option from the Dropdown list, selected row(s) or column(s) will be deleted. Here one row is selected naturally one row will be deleted.
Accordingly you can delete column(s) right clicking on the selected columns and clicking on Delete option from the Drop down list.
Hide/Unhide rows or columns
To get the options of hide and unhide rows and columns, go to format button of Home tab
To hide the selected rows Click on Hide Rows and for hiding columns click on Hide Columns. Contents of the hidden rows/columns will not be printed. To get back the hidden row(s)/column(s), select the previous and next rows of the hidden row(s)/column(s) or select the entire worksheet, and then choose any of these options accordingly. All the hidden row(s)/column(s) will come back immediately.
Adjusting Row Height
Row Height – This option is used to change the row height of the selected row or rows to the defined value. The following dialog box appears, when you click this option, type the value and click on OK button.
AutoFit Row Height – This feature is used to change the height of the selected cell(s) to fit the contents accordingly. This can be done by Best Fit option also.
Column Width – This option changes the width of the selected column or columns. The following dialog box will appear, define the value and then click on OK button.
Class Exercise
1) Open your last saved workbook and go to Sheet-1.
2) Best fit the columns and rows if required.
3) Rename the Sheet-1 as ‘Student List’.
4) Add two more field name after Fees field as ‘Service Tax @10.3%’ and ‘Total Fees’. Apply similar cell formatting in this two field using format painter.
5) Adjust column width in such a way, so that the text ‘Service Tax’ only appears in a single line and the rest text in the second line of the same cell.
6) Adjust row height as required.
7) Calculate Service tax @ 10.3% on Fees.
8) Calculate Total Fees as Fees+Service Tax.
9) Merge and center first cell containing text ‘Student Details’ till Cell I1.
10) Go to a new worksheet and prepare the table (electricity) as follows:
Meter no
Opening reading
Closing reading
Metered units
Rate/unit
Total amt
Rental charge
Service tax 10.3%
Bill Amount
DE/023
4568
5528
2.25
84.40
AP/153
3210
3285
2.25
84.40
FE/092
1102
1125
2.25
84.40
JU/120
6521
8543
2.25
84.40
11) Save and Close the workbook.
Style, Table & Conditional Formatting and Format Painter
LEARNING OBJECTIVES
Style formatting
Table formatting
Conditional formatting
Using format painter Style Group
A style in Excel 2007 is a combination of formatting options that is named and saved as part of your current spreadsheet file. The new style can then quickly be applied to data and cells in the spreadsheet. Let’s take an example to explain the use of Style group options as shown below.
Cell Styles
Cell styles make it very easy to apply a set of predefined formatting options to a cell or range. In addition to saving time, using named styles also helps to ensure a consistent look. You can also defined you own style.
1) Select the desired cells (D3:D6).
2) Click on the Cell Styles option on the Style Group under Home Tab to open the Cell Styles gallery.
3) Click on any style name at the top of the gallery as required.
4) The style's formatting is immediately applied to the selected cells.
Format as Table
Pre-defined table style offers a quick way to apply a standard format to all or part of a worksheet. Before you use Pre-defined table style, select the cells to be formatted. Choose Home Tab Styles group Format as Table and then select any desired style from the display list.
After applying Cell Styles Before Applying Style
After Applying Style
Simple Conditional Formatting
Conditional formatting has improved significantly in Excel 2007, and it’s now a useful tool for visualizing numeric data. Conditional formatting enables you to apply cell formatting selectively and automatically, based on the contents of the cells.
Specifying Conditional Formatting
To apply a conditional formatting rule to a cell or range, select the cells and then use one of the commands on the Home Tab Style group Conditional Formatting drop-down to specify a rule. The choices are:
Highlight Cell Rules: Examples rules includes highlighting cells that are greater than a particular value, between two values, contain specific text string, or are duplicated.
Top Bottom Rules: Examples include highlighting the top 10 items, the items in the bottom 20 percent, and items that are above average.
Data Bars: Applies graphic bar directly in the cell, proportional to the cell’s value.
Color Scales: Applies background colour, proportional to the cell’s value
Icon Sets: Displays icons directly in the cells. The icons depend on the cell’s value.
When you apply conditional formatting rules, Excel displays a dialog box that’s specific to that rule. These dialog boxed have one thing in a common: a drop-down list with common formatting suggestions.
Above Figure shows the dialog box that appears when you choose Home TabStylesConditional FormattingHighlight Cells Rules Between. This particular rule applies the formatting if the value in the cell falls between two specific values. In this case, you enter the two values, and then use the drop-down control to choose the type of formatting to display if the condition is met.
Figure below shows a worksheet with nine ranges, each with different types of conditional formatting rule applied. Here‘s a brief explanation of each.
Type a Value
Select any format
Greater than 10: Values greater than 10
are highlighted with a different
background colour. This rule is just one
of the many numeric value related rules
that you can apply.
Above Average: Values that are higher
than the average value are highlighted.
Duplicate Values: Values that appear
more than one time are highlighted.
Words that contain X: If the cell contains
X (upper or lower case), the cell is
highlighted.
Data Bars: Each cell displays a horizontal
bar, proportional to its value.
Color Scale: The background colour varies, depending on the value of the cells. You can choose from several different colour scales or create your own.
Icon Set: One of many icon sets. It displays a small graphic in the cell. The graphic varies, depending on the cell value.
Removing Conditional Formats
If you no longer want to conditionally format your data, you can remove any rules applied to it and return it to its original format. To remove conditional formats from the selected cells, sheet, or table, click Conditional Formatting in the Styles group of the Home tab. Point to the Clear Rules option and select the appropriate option from the fly-out menu.
Format Painter
Format Painter option is use to copy the formats from one part of a sheet to another, or to another sheet in the same workbook. This option is available under Home TabClipboard Group. Example: 1
To use Format Painter to copy formatting options to a second cell as shown below.
1) Add all the formatting options you want to use to the first cell range.
2) Click on that cell with the mouse pointer to make it the active cell.
3) Click on the Home tabFormat
Painter icon under Clipboard
group.
4) Clicking on a second cell range will
copy the formats from the active
cell to this second cell.
Class Exercise
1) Open your last saved workbook and go to Sheet-1.
2) Highlight course B.COM using conditional formatting.
3) Highlight Fees between Rs.50,000 to 100,000 using conditional formatting.
4) Apply Cell Style named ‘Assent 4’ on all the centre names.
5) Select the table leaving the first row and apply Table Style Light 20 format.
6) Delete all un-used worksheets.
7) Save and close the workbook.
Cell Reference, Fill Series, Custom List and Formatting Numbers
LEARNING OBJECTIVES
Cell reference
Filling series
Custom list
Formatting numbers Cell Reference
Most formulas you create include references to cells or ranges. These references enable your formulas to work dynamically with the data contained in those cells or ranges rather than being restricted to fixed values. For example, if your formula refers to cell A1 and you change the value contained in A1, the formula result changes to reflect the new value. If you didn’t use references in your formulas, you would need to edit the formulas themselves in order to change the values used in the formulas.
When you use a cell (or range) reference in a formula, you can use three (3) types of references:
1) Relative Reference: It refers to the cell by their position in relation to the cell that contains the formula. The row and column references can change when you copy the formula to another cell because the references are actually offsets from the current row and column.
2) Absolute Reference: It refers to the cell by their position in relation to the cell that contains the formula. The row and column references do not change when you copy the formula because the reference is to an actual cell address.
3) Mixed Reference: Any of the row or column reference is relative, and the other is absolute.
By default, Excel creates relative cell references in formulas. The distinction becomes apparent when you copy a formula to another cell. The formula in cell D2, which multiplies the quantity by the price, is =B2*C2. This formula uses relative cell references. Therefore, when the formula is copied to the cells below it, the references adjust in a relative manner. For example, the formula in cell D3 is =B3*C3
Filling Series
Excel supports a wide variety of fill options. Go to Home Tab Editing Group Fill Series to display the series dialog box.
Creation of Linear Series
Linear series is for the series that increases by adding the same value to each successive cell.
Creation of Growth Series
Growth series is for the series that increases geometrically multiplying by a value.
Custom List
Entering a Series is similar to fill the content of cell for a group of cells. Suppose you want to create a series of days, such as Sunday, Monday, and so on. Type Sunday in a cell and drag the fill handle of the cell in any direction, and check a series of days will be generated. Similarly you can create a series of months, i.e.,
Figure demonstrates the use of mixed references. The formulas in the C3:E7 ranges calculate the phone bill for various call made and phone rate. The formula in cell C3 is =$A3*C$2
Select any one of the options from the series in options according to your requirement. Here we are selecting columns.
Select the option Growth from the type list.
Input step value and stop value as your requirement. Here we are using 5 as step value and 400 as stop value. It will create a series like 3, 15, 75, 375
Click on OK to finish.
Select the cell based on which you want to generate the series.
Show Multiplication of 5
January, February, and so on using same way. These series are generated because they are already defined in Excel. You can create your own series with text or number etc.
1) To define your own list Go to Office Button Excel Options Popular Tab Edit Custom Lists
2) Click on NEW LISTType the List itemsClick on Add.
Formatting numbers using the Format Cells dialog box
In most cases, the number formats that are accessible from the Number group on the Home tab are just fine. Sometimes, however, you want more control over how your values appear. Excel offers a great deal of control over number formats through the use of the Format Cells dialog box, shown in Figure below. For formatting numbers, you need to use the Number tab.
Number Formats
Select Number control tab, to apply any number formatting.
General – The default format of number will be applied. Mainly it is required to remove or cancel any number format from the selection.
Number – This type of formatting allows you to set number of decimal places, with a thousand separators, and formatting of negative numbers.
Currency – Numbers are preceded with a default Currency sign immediately. You can also set here number of decimal places and the formatting of the negative numbers. Zero values are displayed.
Accounting – Currency sign and decimal places can set with the numbers. But the setting of Currency symbol with number is different from Currency format. Currency symbols are coming with left alignment whereas numbers or values are right aligned in a cell.
Date – This formatting gives a variety of Date formats.
Time – Different time formats provided by this option.
Percentage – This formatting attached a percentage sign with the number as suffix and multiply the number by 100 (hundred).
Fraction – Includes formats based on either the number of digits to display in the divisor (1, 2, or 3) or the fractional unit (halves, quarters, tenths, and so on).
Scientific – Numbers are displayed in scientific notation. For example: 1.01E+03.
Text – Changes a number to text without adding other formatting. This is useful for numeric labels that may include leading zeros. All the regular numeric formats strip off leading zeros. You must apply the Text format before entering the cell’s contents.
Special – This is mainly used for Telephone numbers, Zip Codes etc. those are mainly having some special formats.
Example of cell formatting:
In the following sections, you see how to use many of Excel’s formatting options to quickly improve the appearance of your worksheets.
Text Alignment
Horizontal: Select an option in the Horizontal list box to change the horizontal alignment of cell contents. By default, Microsoft Office Excel aligns text to the left, numbers to the right and logical and error values are centered. The default horizontal alignment is General. Changing the alignment of data does not change the data type.
Vertical: Select an option in the Vertical box to change the vertical alignment of cell contents. By default, Excel aligns text vertically on the bottom of a cell. The default horizontal alignment is General.
Indent: Indents cell contents from any edge of the cell, depending on your choice under Horizontal and Vertical. Each increment in the Indent box is equivalent to the width of one character.
Orientation: Select an option under Orientation to change the orientation of text in selected cells. Rotation options may not be available if other alignment options are selected. Degrees: Sets the amount of text rotation in the selected cell. Use a positive number in the Degree box to rotate the selected text from lower left to upper right in the cell. Use negative degrees to rotate text from upper left to lower right in the selected cell.
Text control
Wrap text: Wraps text into multiple lines in a cell. The number of wrapped lines is dependent on the width of the column and the length of the cell contents.
Shrink to fit: Reduces the apparent size of font characters so that all data in a selected cell fits within the column. The character size is adjusted automatically if you change the column width. The applied font size is not changed.
Merge cells: Combines two or more selected cells into a single cell. The cell reference for a merged cell is the upper-left cell in the original selected range.
Text direction: Select an option in the Text direction box to specify reading order and alignment. The default setting is Context, but you can change it to Left-to-Right or Right-to-Left.
In this table there are some examples of formatted and unformatted text. There it is also mentioned type of format we have used for that particular cell.
Font Control Tab
1) Select the font from the font list.
2) Select font style as required.
3) Select font size to increase/decrease the font size.
4) Select underline style.
5) Choose font colour.
6) Apply text Effects.
7) See the preview of your applied formats.
Border and Fill Control Tab
Class Exercise
1) Open the last saved workbook.
2) Insert a new worksheet . Name the worksheet as ‘Reference’ and prepare the following table in it with necessary formatting.
3) Complete the above table using Mixed Cell Referencing concept with necessary formatting.
4) Prepare another table just beside the City-Wise Conveyance chart as shown below. Fill Tab
Background Color - Select a background color for selected cells by using the color palette.
Fill Effects - Select this button to apply gradient, texture, and picture fills to selected cells.
More Colors - Select this button to add colors that are not available on the color palette.
Pattern Color - Select a foreground color in the Pattern Color box to create a pattern that uses two colors.
Pattern Style - Select a pattern in the Pattern Style box to format selected cells in a pattern that uses the colors that you select in the Background Color and Pattern Color boxes.
Sample - See a sample of the color; fill effects, and pattern options that you select.
5) Calculate Bill Amount using Absolute Cell Referencing concept.
6) A Partnership firms earned a profit of Rs.800,000 in the year 2009. The partners decide to share 40% of the profit amongst themselves in the ratio of their capital. Calculate their individual shares of profit in the same sheet.
7) Change the value of Akbar’s capital to Rs.600,000 and check whether the table of Partner’s profit has been reflected or not.
Mathematical Functions
LEARNING OBJECTIVES
Mathematical Function
Sum
Average
Max
Min
Round
Sqrt
Power
Some Popular Functions of Excel
Excel includes many math, trigonometry and text functions. These functions perform a wide variety of Calculations such follows:
1) SUM
2) AVERAGE
3) MAX
4) MIN
5) ROUND
6) SQRT
7) POWER
SUM Function
General mathematical built-in functions are provided with Excel to carry out calculations on data within the spreadsheet and can take the place of certain types of formulas.
Functions begin with the = sign just as formulas do. For example, if you had a large column of numbers to be added (A1:A100), you might think you need a long formula to include all of the addresses (=A1+A2+A3....+A99+A100). However, Excel provides a mathematical function which is used primarily to add blocks of numbers. The formula could be re-written as =SUM (A1:A100) which is much shorter.
AVERAGE Function
This function returns the average value for a block of cells. The syntax is: =AVERAGE (FIRST CELL : LAST CELL)
Example:
=AVERAGE (23,89,34,20) The return value will be 41.5.
=AVERAGE (D2:D10) It will return the average value of the range D2 to D10.
Note: You can use non-contiguous cell references also using comma (,) separator.
MAX Function
This function returns the largest value in a block of cells. The syntax is: =MAX (FIRST CELL : LAST CELL)
Numbers are asking for the values or numbers from which you want to find out the maximum value.
Example:
Cell Range from A1 to A10 (A1:A10)
SUM is one of the most commonly used functions. It adds all the numbers in the range of cells. The syntax of the SUM function is: =SUM (FIRST CELL : LAST CELL)
Range is a group of cells to be calculated by the function. You can also provide values as arguments.
Example:
=SUM (15,20, 45,83) You can use values in the function
=SUM (A1:A10) For contiguous cells, give the reference of the first cell, then a colon sign (:) and then cell reference of the last cell of the range. (See figure).
=SUM(B5:B10,B15,B20:B25) For non-contiguous references, use comma as a separator.
Using the Average function to find the Average value in the range of cells
=MAX (45, 12, 78) This function will returns the value 78 which is the highest value out of the given
number.
=MAX (D2:D10) It will return the largest value from the defined range.
MIN Function
This function returns the smallest value in a block of cells. The syntax of the function is: =MIN (FIRST CELL : LAST CELL)
Numbers are the arguments from which the minimum value will be finding out.
Example:
=MIN (45,12,56) The return value will be 12.
=MIN (D2:D10) It will return the minimum value from the defined range.
ROUND Function
This function Rounds a number to a specified number of digits. The syntax of the function is: =ROUND (<Number>, <Num_digits>)
Number is the number you want to round.
Num_digits specifies the number of digits to which you want to round number
Example:
=ROUND (235.75,1) The return value will be 235.8, because you want to round off the number to one decimal place. Since the second decimal value is 5, Round function will add 1 with the first decimal value.
SQRTFunction
This function returns the square root value of a given numbers. The syntax of this function is: = SQRT (NUMBER)
Example:
=SQRT(25) Square root of 25 is 5.
POWER Function
This function returns the result of a number raised to a power. The syntax of this function is: =POWER (Number,Power)
The "^" operator can be used instead of POWER to indicate to what power the base number is to be raised, such as in 5^2.
Example:
=POWER(5,2) The return value will be 25, due to 5 squared.
=POWER(3,1/3) The return value will be 1.44225 , due to 3 raised to the power of 1/3.
Concept of Range
A group of cells is called a range cells. The cells in a range may all be in one column, or one row, or any combination of columns and rows, as long as the range forms a rectangle. A range also can be a single cell. Ranges are referred to by their anchor points, means the upper-left cell address and its lower-right cell address, separated by colon. Some examples of range addresses C24
A range that consists of a single cell. A1:B1
Two cells that occupy one row and two columns. A1:A100
100 cells in column A. A1:D4
16 cells (four rows by four columns). C1:C1048576
An entire column of cells; this range also can be expressed as C:C. A6:XFD6
An entire row of cells; this range also can be expressed as 6:6. A1:XFD1048576
All cells in a worksheet.
Adding Comments to Cells
In excel a note can be added on a certain cell through inserting a comment. You can edit the comment and delete those comments which are no longer useful.
Select the cell and then go to Review tab Comments group New Comment. ~Or~
Right-click on the cell and choose Insert Comment from the popup menu.
After you finished writing, click anywhere outside the comment area to hide the comment box.
A small red arrow at the top right corner of the cell indicates that a comment is inserted on that cell.
Class Exercise
1) Open your last saved workbook and go to the Student List sheet.
2) Delete all the Student Code list and set the Student Code range (A3:A16) in such a way that entering ‘1’ will automatically display ‘ST/0001’. [Hint: Use Custom number format]
3) Feed student code as 1, 2, 3, ……., 14 in the respective cell.
4) Insert a new worksheet after Reference sheet. Name the worksheet as ‘Result ’ and prepare the following dataset in it.
5) Calculate Total, Avearage, Maximum and Minimum marks in the above table.
6) Insert a note ‘Fundamental of Accounts’ on cell F1.
7) Prepare the following table with in ‘Result’ worksheet and do the necessary operations to give resulted figures under appropriate column head as per given instructions.
a. Find out the Square root of the original numbers under the head Square Root.
b. Round off the square root figures upto 2 (two) decimal places and keep under Rounded Figure head.
c. Find out Power of 2 of the Rounded figures under the Power head.
8) Save and close the workbook.
Date Value Function, Workspace Creation and Paste Special
LEARNING OBJECTIVES
Date value function
Workspace creation
Paste special Date-Related Functions
Excel has quite a few functions that work with dates. These functions are accessible by choosing Path: FormulasFunction Library Date & Time.
DATE Function
Returns the sequential Excel date / time serial number that represents a particular date. Syntax: =DATE (Year, Month, Date)
The result will normally be displayed in the format set in regional setting of your computer. By using the number format this format can be changed.
TODAY Function
This function is used to show the current date in a cell. Syntax: =TODAY()
Creation of Workspace
If you frequently work with the same group of workbooks you may need to create a customized workspace. A Workspace allows you to open a group of workbook in one step. Information about the open files, such as their locations, window size, and screen positions, are stored in a workspace file. Therefore, instead of opening individual workbook you can open the workspace file, all the individual workbooks forming a part of the file are opened.
1) To create a Workspace, first open the workbooks you want to include in the workspace
2) Go to View Tab Window Group Save Workspace, the saving dialog box will appear. Enter a name for the Workspace file in the File name box. The default extension for a workspace file is .XLW.
Using the Paste Special Dialog box
For maximum flexibility in what gets pasted, choose Home Clipboard Paste Paste Special to display the Paste Special dialog box (see Figure below). You can also right-click and select Paste Special to display this dialog box. This dialog box has several options, which is explained in the following list.
Data Validation
Excel’s data validation feature enables you to set up certain rules that restrict an user to enter invalid data. For example, you may want to limit data entry in a particular cell to whole numbers between 3000 and 20000. If the user makes an invalid entry, you can display an error message. Excel makes it easy to specify the validation criteria, and you can also use a formula for more complex criteria. Types of Validation Criteria You Can Apply
The Settings tab of the Data Validation dialog box enables you to specify a wide variety of data validation criteria. The following options are available in the Allow drop-down box. Keep in mind that the other controls in the Settings tab vary, depending on your choice in the Allow drop-down box.
Any Value: Selecting this option removes any existing data validation. Note, however, that the input message, if any, still displays if the check box is checked in the Input Message tab.
Whole Number: The user must enter a whole number. You specify a valid range of whole numbers by using the Data drop-down list. For example, you can specify that the entry must be a whole number greater than or equal to 100.
Decimal: The user must enter a number. You specify a valid range of numbers by using the Data drop-down list. For example, you can specify that the entry must be greater than or equal to 0 and less than or equal to 1.
List: The user must choose from a list of entries you provide. In situations where the some few items are to be entered into a column, you can create a drop-down list containing the options from which the user must choose. You can define the values within Source text box using comma separator or a range of data can be defined using an equal sign (=) as prefix.
Date: The user must enter a date. You specify a valid date range by using the Data drop-down list. For example, you can specify that the entered data must be greater than or equal to January 1, 2007, and less than or equal to December 31, 2007.
Time: The user must enter a time. You specify a valid time range by using the Data drop-down list. For example, you can specify that the entered data must be greater than 12:00 p.m.
Text Length: The length of the data (number of characters) is limited. You specify a valid length by using the Data drop-down list. For example, you can specify that the length of the entered data be 1 (a single alphanumeric character).
All: Pastes the cell’s contents, formats, and data validation from the Windows Clipboard.
Formulas: Pastes values and formulas, with no formatting.
Values: Pastes values and the results of formulas (no formatting). The destination for the copy can be a new range or the original range. In the latter case, Excel replaces the original formulas with their current values.
Formats: Copies only the formatting.
Comments: Copies only the cell comments from a cell or range. This option doesn’t copy cell contents or formatting.
Validation: Copies the validation criteria so the same data validation will apply. Data validation is applied by choosing Data ➪ Data Tools ➪ Data Validation.
Custom: To use this option, you must supply a logical formula that determines the validity of the user’s entry (a logical formula returns either True or False). You can enter the formula directly into the Formula control (which appears when you select the Custom option), or you can specify a cell reference that contains a formula. This session contains examples of useful formulas.
The Settings tab of the Data Validation dialog box contains two other check boxes:
Ignore Blank: If checked, blank entries are allowed.
Apply These Changes to All Other Cells with the Same Setting: If checked, the changes you make apply to all other cells that contain the original data validation criteria. Specifying Validation Criteria
To specify the type of data allowable in a cell or range, follow these steps:
1) Select the cell or range.
2) Choose Data tab Data Tools group Data Validation. Excel displays its Data Validation dialog box.
3) Click the Settings tab (see Figure).
4) Choose an option from the drop-down box labeled Allow. The contents of the Data Validation dialog box will change, displaying controls based on your choice. To specify a formula, select Custom.
5) Specify the conditions by using the displayed controls.
6) (Optional) Click the Input Message tab and specify which message to display when a user selects the cell.
7) (Optional) Click the Error Alert tab and specify which error message to display when a user makes an invalid entry.
8) Click Ok.
Class Exercise
1) Open the last saved workbook.
2) Insert a new worksheet after Result sheet. Name the worksheet as ‘Drugs ’ and prepare the following dataset in it.
Allowing only whole no. to selected cells
Setting conditions
Message box Title
Error Message
3) Find out Manufacture date using date function.
4) Change Expiry Date range as text and feed the following dates in the respective cell.
12-Jun-2010; 25-May-2010; 01-Sep-2012; 14-Mar-2009; 11-Nov-2011; 11-Apr-2012; 03-Dec-2009; 01-Sep-2008; 31-Jul-2009; 18-Jan-2012.
5) Days of today column should display the current date.
6) Calculate the number of expiry days as of today in ‘Days Until Expiry’ column.
7) Save and close the workbook.
In this example, we have to check two conditions to offer discount to the customer, one is whether the product is on Special Offer and another one is whether the Order Value is Rs.1000 or above. That can be checked through AND() function [ =AND(B3=”Yes”,C3>=1000)]. Now based on that conditions we have to take decision; whether to offer discount or not. So this above function has to be incorporated with if() function. Thus the final formula is: =IF(AND(B3=”Yes”,C3>=1000),C3*10%,0) Logical Functions
LEARNING OBJECTIVES
Logical Function
IF function
OR Function
Logical Function
Excel’s logical functions are used to perform logical tests, as to reveal whether a statement is true or false. Depending on the outcome of the logical test, a specific result is returned. If Function
This function returns a value depending on the logical test given as an argument of the function. The syntax of the function is: =IF( Logical_test, Result_if_true, Result_if_false).
Logical_test is any value or expression that can be evaluated to true or false. Result_if_true is the value that will return if the Logical_test is true. Result_if_false is the value that will return if the Logical_test is false. Example-1: Simple IF
OR Function
The OR functions checks whether any of the arguments are TRUE. It returns a FALSE only if all the arguments are FALSE. If any argument is TRUE, the function returns TRUE. Syntax: =OR(Logical_test 1,Logical_test 2,……) Example-1:
A handling charge of Rs.50/- is made on all orders paid by Visa or Delta cards. The =OR() function has been used to determine whether the charge needs to be applied.
The figure shows a worksheet with student grades in column B. Formulas in column C use the IF function to return text: either Pass (a score of 65 or higher) or Fail (a score below 65). The formula in cell C2 is: =IF (B2>=65,”Pass”,”Fail”)
NOT Function
NOT function is used to reverse the value of its argument. NOT function accepts a logical value as its argument.
Class Exercise
1) Open the last saved workbook.
2) Create a new worksheet. Rename the sheet as ‘Payroll’ and prepare the following dataset in it. Provide all required formatting and use series concept at employee code.
3) Calculate DA @ 40% of Basic pay.
4) Calculate TA @ 10% of Basic or Rs.1000/- which ever is higher.
5) Provide House for Audit department only and “No” for others.
6) Calculate HRA @ 15% of Basic pay for those who do not get accommodation from company and for other it will be 0.
7) Calculate Gross salary (Basic+DA+TA+HRA).
8) Calculate P.F @ 12.5% of Basic pay.
9) Calculate P.Tax as per slab:
Upto Rs.5000 --- Nil
Rs.5001 – Rs.6000 --- Rs.60/-
Rs.6001 – Rs.10,000 --- Rs.80/-
Rs.10,001 – Rs. 15,000 --- Rs.100/-
Rs.15,001 – Rs.20,000 --- Rs.150/-
Rs.20,001 and above --- Rs.200/-
10) Calculate Net Salary (Gross-P.F-P.Tax)
11) Now replace HRA amount with 20% of Basic whose Basic is more than 15,000 and also who do not posses a house. For other who do not posses house and who’s Basic is below 15,000, don’t replace their HRA.
12) Save and close the workbook.
In this example, any of the two conditions (whether the payment type is Visa or Delta) has to be satisfied to charge Handling Charge of Rs. 50. So, Or() function has to be used within If() function. The final formula is: IF(OR(C2=”Visa”,C2=”Delta”),50,0).
Other Logical Functions, Data Form and Freeze Panes
LEARNING OBJECTIVES
Other Logical Functions
o Sumif
o Countif
o Count
o Isblank
Data Form
Freeze panes Use of Sumif Function
The SUMIF function calculates the total of only those cells that meet a given condition or criteria. The syntax for the SUMIF function is: =SUMIF (Range, Criteria, SumRange)
Range: The range containing the values that determine whether to include a particular cell in the sum.
Criteria: An expression that determines whether to include a particular cell in the sum.
Sum_range: The range that contains the cells you want to sum
Based on the above table, to find out total cost of all Mouse bought:
=SUMIF(B2:C9,"Mouse”,”C2:C9)
COUNT Function
This function counts the number of numeric entries in a list. It will ignore blanks, text and errors. The syntax of this function is: =COUNT(Range1,Range2,Range3... through to Range30)
The following figure evaluates the use of Count Function:
Range
Criteria
Sum Range
COUNTIF Function
The COUNTIF function counts the number of cells in a given range that meet a specific condition. The syntax for the function is:=COUNTIF (Range, Criteria)
Range is used to declare a group of cells from which you want to count those cells, which meet the criteria. Criteria is used to declare the searching matter, it can be a number as well as a value. ISBLANK FUNCTION
This function will determine if there is an entry in a particular cell. Syntax: =ISBLANK(CellToTest)
It can be used when a spreadsheet has blank cells which may cause errors, but which will be filled later as the data is received by the user will be filled later as the data is received by the user. DATA FORM
Many users use Excel to manage lists in which the information is arranged in rows. Excel offers a simple way to work with this type of data through the use of a data entry form that Excel can create automatically.
Data Form command is not available in the Ribbon, to use the data form, you must add it to your Quick Access Toolbar (QAT): Freeze Panes
If you set up a worksheet with row or column headings, these headings will not be visible when you scroll down or to the right. Excel provides a handy solution to this problem: freezing panes. Freezing panes keeps the headings visible while you’re scrolling through the worksheet.
1) To freeze panes, start by moving the cell pointer to the cell below the row that you want to remain visible as you scroll vertically, and to the right of the column that you want to remain visible as you scroll horizontally.
2) Then, Choose View Window Freeze Panes and select the Freeze Panes option from the dropdown list.
3) Excel inserts dark lines to indicate the frozen rows and columns. The frozen row and column remain visible as you scroll throughout the worksheet.
To remove the frozen panes, choose ViewWindow Freeze Panes, and select the Unfreeze Panes option from the drop-down list.
Class Exercise
1) Open your last saved workbook.
2) Go to the Payroll sheet and calculate the following at the end of the dataset:
a. Calculate the Total take home money of Audit department
b. Calculate the Total take home money of Sales department
c. Calculate the gross salary of Media department.
d. Total number of employee in Media department
e. Total number of employee in Acct department
3) Create a new worksheet. Rename the sheet as ‘Installment’ and prepare the following dataset by copying St.Code , Name, Course and Fees from ‘Student List’ sheet.
4) Calculate Outstanding Fees (Fees-Installment), if Date of Installment Paid is mentioned otherwise full Fees amount will appears as outstanding fees. [Hint: Use ISBLANK function]
5) Save and close the workbook.
Financial Functions
LEARNING OBJECTIVES
Financial Function
Loan Calculation
o PV
o FV
o PMT
o PPMT FINANCIAL FUNCTION
Excel is often used to perform calculations involving money, from basic home budgets to complex financial decision making. Consequently, Excel’s function library contains over 50 financial functions that can be used to calculate everything from loan repayments to the future value of an investment. LOAN CALCULATIONS
This section describes how to calculate various components of a loan. Think of a loan as consisting of the following components:
The loan amount
The interest rate
The number of payment periods
The periodic payment amount
If you know any three of these components, you can create a formula to calculate the unknown component. Note: The loan calculations in this section all assume a fixed-rate loan with a fixed term.
This section describes six functions: PMT, PPMT, IPMT, RATE, FV and PV. The PMT Function
The PMT function returns the loan payment (principal plus interest) per period, assuming constant payment amounts and a fixed interest rate.
The syntax for the PMT function is PMT(Rate,Nper,Pv,Fv,Type) Function Argument Description
Rate
The interest rate per period. If the rate is expressed as an annual interest rate, you must divide it by the number of periods.
Nper
The total number of payment periods.
PV
This is the present value, or the loan amount; it is also known as the principal.
FV
The future value after the last payment is made. If you omit FV, it is assumed to be 0. (The future value of a loan, for example, is 0.)
Type
Indicates when payments are due—either 0 (due at the end of the period) or 1 (due at the beginning of the period). If you omit type, it is assumed to be 0.
Let’s take an example as shown below:
You need to find out the monthly payment amount for a Loan of Rs.500,000 with a 14% annual interest rate. The loan has a term of four years (48 months).
The procedure to calculate monthly payment amount are as follows:
1) Place your cursor on Cell B6. Type <=PMT> and press <Ctrl+A> to display function argument dialog box.
2) Fill the following function arguments box: Rate
Cell B3/12 (14%/12). It is divided by 12 as the interest is given in annual basis and the payment is in monthly basis. Nper
Cell B4*12 (4*12). As payment is made monthly for the period of four (4) year. PV
-Cell B2 (-500,000). This will be negative for a loan. FV
Omit FV as it is assumed to be 0. Type
‘0’. It indicate the payment is due at the end of the period.
3) Finally click OK button to display monthly payment amount. This formula returns Rs.13,663.24 (approx) as the EMI.
The PPMT function
The PPMT function returns the principal part of a loan payment for a given period, assuming constant payment amounts and a fixed interest rate.
The syntax for the PPMT function is PPMT(rate,per,nper,pv,fv,type). Function Argument Description
Rate
The interest rate per period. If the rate is expressed as an annual interest rate, you must divide it by the number of periods.
Per
This is the period for which you want to find the principal
Nper
The total number of payment periods.
PV
Present value, the amount you have invested or you have taken as loan.
FV
The future value after the last payment is made. If you omit FV, it is assumed to be 0. (The future value of a loan, for example, is 0.)
Type
Indicates when payments are due—either 0 (due at the end of the period) or 1 (due at the beginning of the period). If you omit type, it is assumed to be 0.
Let’s take an example as shown below:
In the above example, you need to find out the principal amount for the first month of the EMI that you have calculated in the earlier example.
The procedure to calculate Principal amount are as follows:
1) Place your cursor on Cell B8. Type <=PPMT> and press <Ctrl+A> to display function argument dialog box.
2) Fill the following function arguments box as shown below.
Per: Particular number of Installment. Suppose you have total 48 installments, among them you need to know the principal amount of 1st installments. Then type “1” in Per argument box. The period must be less than or equal to nper.
3) Click OK button to get the Principal value (PPMT). This formula returns Rs.7,829.90 (approx) as the principal amount.
The PV function
The PV function returns the present value (that is, the original loan amount) for a loan, given the interest rate, the number of periods, and the periodic payment amount.
The syntax for the PV function is: PV(rate,nper,pmt,fv,type) Function Argument Description
Rate
The interest rate per period. If the rate is expressed as an annual interest rate, you must divide it by the number of periods.
Nper
The total number of payment periods.
PMT
The payment made each period (a constant value that does not change).
FV
The future value after the last payment is made. If you omit FV, it is assumed to be 0. (The future value of a loan, for example, is 0.)
Type
Indicates when payments are due—either 0 (due at the end of the period) or 1 (due at the beginning of the period). If you omit type, it is assumed to be 0.
Let’s take an example as shown below:
You need to find out the original loan amount for four (4) years, which has a monthly payment of Rs.13,663.24.
The procedure to calculate the present value of loan is as follows:
1) Place your cursor on Cell B6. Type <=PV> and press <Ctrl+A> to display function argument dialog box.
2) Fill the following function arguments box as shown below. Rate
Cell B3/12 (14%/12). It is divided by 12 as the interest is given in annual basis and the payment is in monthly basis. Nper
Cell B4*12 (4*12). As payment is made monthly for the period of four (4) year. PMT
-Cell B2 (-13,663.24). It is the amount of the periodic payments FV
Omit FV as it is assumed to be 0. Type
Omit type. It indicates the payment is due at the end of the period.
3) Click OK button to get the present value (PV) of loan. This formula returns Rs.500,000 as the initial loan amount.
The FV function
In financial models the future value of an investment is equal to the present value plus interest or the combined value of all the deposits over the term of the investment plus any interest.
The FV function will return the future value of an investment based on periodic, constant payments and a constant interest rate.
The syntax for the FV function is: FV(rate,nper,pmt,pv,type)
Function Argument Description
Rate
Rate is interest rate per period; if payments are monthly this will be divided by 12.
Nper
Nper is the total number of payments.
PMT
Pmt is the fixed amount deposited each period (assumed to be zero).
PV
PV is the initial deposit or present value (assumed to be zero).
Type
Type is 0 if payment is due at the end of a period or 1 if payment is due at the beginning of a period (the default is 0).
Let’s take an example as shown below:
The above example shows that you are depositing Rs.1000 at the beginning of each month for 5 years with 2.13% quarterly interest. Here you need to find out the matured amount. To do this task you need to use FV function.
The procedure to calculate the future value of loan is as follows:
1) Place your cursor on Cell B6. Type <=FV> and press <Ctrl+A> to display function argument dialog box.
2) Fill the following function arguments box as shown below. Rate
Cell B3/4 (2.13%/4). It is divided by 4 as the interest is given in quarterly basis and the payment is in monthly basis. Nper
Cell B4*12 (5*12). As payment is made monthly for the period of five (5) year. PMT
-Cell B2 (-1,000). It is the amount of the monthly payment. PV
0 ( No initial deposit) Type
1 (payment is due at the beginning of a period)
3) Click OK button to get the future value (FV) of investment. This formula returns Rs.70,849.10 as the matured amount.
Class Exercise
1) Open your last saved workbook and insert a new worksheet after ‘installment’, name the sheet as ‘Financial’.
2) You can get Rs.500,000/- in 3 years time by investing certain amount on monthly basis at 7% p.a. Find out the monthly investment amount. [ Rs.12,522 approx ]
3) If I deposit Rs.1000 at the beginning of each month (starting today) in an account earning 1% per month, how much will I have after two years? [ Ans: 27,243.20 ]
4) If you have deposited Rs.100,000 with compounded interest @ 6.25% per annum for 8 years, find out the return value at the end of 8 Years. [ Rs.162,417 approx ]
5) If the previous deposit follows an MIS (Monthly Income Scheme) then how much amount will you receive at the end of each month? [ Rs.1326 approx ]
6) In every MIS, find out how much amount is paid back as Principal and Interest seperately. Summation of Principal parts would be Rs.100,000 and summation fo Interest parts would be Rs.27,329.56.
7) As per the investment scheme, you have to deposit Rs.835 per month to get Rs.150,000/- at the end of 10 years, find out rate of interest per annum. If you are investing sum of amount to get the same return value after 10 years with same rate of interest per annum, then find out the amount to be deposited now. [ 7.56 (approx) ; Rs.72,379 approx ]
8) Suppose you are going to deposit Rs.2.646 quarterly in a recurring account to get back Rs.1,00,000 after a certain period with 8% interest per annum. Find out how many years are required to get back the amount. [ 7 approx ]
9) Save and close the workbook.
Sorting, Subtotal and Filtering Data
LEARNING OBJECTIVES
Sorting a table
Subtotal
Filtering data Sorting a table
Sorting a table rearranges the rows based on the contents of a particular column. You may want to sort a table to put names in alphabetical order. Or, maybe you want to sort your sales staff by the totals sales made. Excel’s Sort feature helps organize the data in a list. Column fields can be quickly sorted in ascending or descending order. To sort the data in a column, select any cell in the column by which you want to sort.
To sort in ascending order, click the Sort Ascending button on the Data Tab (Data Tab Sort & Filter Tab). To sort in descending order, click the Sort Descending button on the Data Tab. If you want to sort a list by more than one field, you can use the Data Tab Sort & Filter Tab Sort.
After applying sort command the data table will look like as shown below.
Note: You can sort on any number of columns. The trick is to sort the most significant column first and then proceed until the least significant column is sorted. For example, in the real estate table, you may want the list to be sorted by agent. And within each agent’s group, the rows should be sorted by area. And within each area, the rows should be sorted by list price. For this type of sort, first sort by the List Agent column, and then sort by the Area column, and then sort by the List Price column. Figure above shows the table sorted in this manner.
Calculating Subtotals
The most convenient way to create an outline for your worksheet data and to insert subtotals is to use the Subtotal command, which is now situated in the Outline group of the Data tab.
To create automatic subtotals, follow the steps:
1) Sort the data by the column that you want to base your calculation on (this will allow excel to group the data correctly).
2) Click the Subtotal command in the Outline group on the Data tab. The Subtotal dialog box will open.
3) Select the column that you want to base the summary calculations on from the ‘At each Change in’ dropdown list.
4) Select ‘SUM’ function that you want to use the summary calculation from the ‘Use function’ dropdown list.
5) From the ‘Add Subtotal to’ list, select the columns for which you want to include summary calculation.
6) Select or deselect the ‘Replace current subtotals’, ‘Page break between groups’, and ‘Summary below data’ options as appropriate. If you deselect the ‘Summary below data’ option, the subtotals will appear above their respective groups, with the grand total at the top of the data.
7) When you have finished making your selections, click OK. Excel will outline your data according to values in the specified column and will insert the appropriate subtotals and grand total into your worksheet.
Filtering of Data
Many a times, perhaps you need to work with a particular group of records in the database only. A Filter is used to select records that meet specific criteria and temporarily hide all other records. Excel supports mainly two ways to find out specific records by defining criteria: AutoFilter and Advanced Filter. AutoFilter
Use to get the necessary data from a huge database.
1) After selecting the database, click on Filter.
2) Dropdown Menu will come automatically. Start filtering from that menu as per as the criteria.
3) To filter product, click on the product drop lists button to appear a dialog box as shown below. Now deselect all products and select the only Mobile and click on Ok button. (See figure below)
Shows appropriate Subtotals and Grand total.
ADVANCED FILTER
Advanced Filter is identified for its role in searching for specific records meeting a certain criteria. With an advanced filter you can denote the exact criteria to be found. This option helps to find records within database as well as to create another database based on criteria to another location (i.e. Subset of the original database).
Class Exercise
1) Open your last saved worksheet. Go to Student List sheet.
2) Find out the records from the Student List who has taken admission in B.COM course. ( Use Filter)
3) Remove filter from the database.
4) Insert a new worksheet after Student List sheet. Form a database in this worksheet only for B.COM and B.B.A student extracting from Student List database. (Use Advanced filter). Rename the sheet as Extract.
5) Insert a new sheet, name the sheet as “ITEM LIST” and prepare the following dataset.
6) Complete the above table using suitable calculation. Now prepare a data form for the Item List and insert the following records. Don’t put or calculate the values for Amount, Discount Amount and Net Amount field.
7) Go to the Student List sheet and sort the records as per student name in A to Z order.
8) Freeze first two rows and columns of Student Details database.
Filtered data based on Product: Mobile
9) Save and close the workbook.
Charts and Protection
LEARNING OBJECTIVES
Type of charts
Creating chart
Worksheet protection
Workbook protection Creating Charts
A chart is a visual representation of numeric values. A chart is also known as graphs. A chart can be created from the data of a database to provide more visual presentation of the data. Excel’s chart feature provides a variety of charts including Bar, Column, Pie, Area and many more.
Types of Charts
You can create different types of charts. The chart type you choose depends on the kind of data you are trying to chart, and on how you want to present that data. The followings are the major chart types.
Column – Similar to the bar chart; use this chart type to emphasize the difference between items.
Pie – Use this chart type to show the relationship among parts of a whole.
Bar – Use this chart type to compare values at a given point in time.
Line – Use this chart type to emphasize trends and the change of values over time.
Scatter – Similar to a line chart; use this chart type to emphasize the difference between two sets of values.
Area – Similar to the line chart; use this chart type to emphasize the amount to change in values over time. Most of these basic chart types also come in three-dimensional varieties.
Creating a Column or Bar Chart from Data in a Worksheet
Once you have entered the data you want to display as a chart into a worksheet and decided which type of chart you require, you can create it with just a few mouse clicks. Let’s take a database to create column or bar chart.
1) Select Cells A1:B7, Click the drop lists of Column.
2) Go to Insert tabCharts groupClick the drop lists of Column or Bar command to display subtypes.
3) Choose and click the appropriate Sub-type charts. (See figure below)
Creating of Pie Chart from Data in a Worksheet
1) To create a pie chart enters the data into the worksheet as shown below. Let’s create a pie for Wipro.
2) Select Cells A1:A6 and Cells C1:C6 holding Ctrl key.
3) Go to Insert tabCharts groupClick the drop lists of Pie and choose and click the appropriate pie chart type. (Refer to figure below)
4) Click Chart Tools Layout TabClick the drop lists of Data LabelsChoose and click Inside End.
Wipro
2008
2007
2006
2005
2004
Sheet Protection
Excel users protect a sheet for a variety of reasons. One reason is to prevent himself or others from accidentally deleting formulas or other unwanted modifications.
1) To protect a sheet, activate the sheet and choose ReviewChanges Protect Sheet.
2) On clicking Protech Sheet, the following screen appears. Enter a password, although a password is optional. If you enter a password, that password will be required to unprotect the sheet.
3) Turn on the required option and click OK button to display Confirm Password dialog box. Retype the same password for confirmation and click OK button. Finally save the worksheet. Protecting a Workbook
To protect a worksheet’s structure:
1) Choose ReviewChangesProtect Workbook to display the Protect Workbook dialog box
2) In the Protect Workbook dialog box, place a check mark next to Structure.
3) Enter a password and Click OK.
4) Reenter the password again for confirmation and Click OK button.
5) Finally save the workbook.
To unprotect the workbook’s structure, choose ReviewChangesUnprotect Workbook. If the workbook’s structure was protected with a password, you are prompted to enter the password. Feed the correct password to unprotect workbook.
When a workbook’s windows are protected, the user can’t change anything related to the window size or position. For example, if the workbook window is maximized when the windows are protected, the user cannot unmaximize the window. The windows can, however, be zoomed.
To unprotect the workbook’s windows, go to Review TabClick the drop lists of Protect Workbook. Choose and click Protect Structure & Windows to open the following dialog box.
If the workbook’s windows were protected with a password, you are prompted to enter the password. Feed the correct password to unprotect the workbook. Protecting an Excel File
Excel lets you save a workbook with a password. After doing so, whoever tries to open the workbook must enter the password.
To add a password to a workbook:
1) Go to Office button PrepareClick Encrypt Document to open the following dialog box as shown below.
2) Type a password and click OK.
3) Type the password again for confirmation and click OK button.
4) Finally Save and close the workbook.
Now, when you reopen the same excel file, it will ask for a password to open. You need to feed the correct password to open the file as shown below.
Class Exercise
1) Open your last saved worksheet. Go to Installment sheet set an image to display as the background of the sheet.
2) Go to payroll sheet and set first row to repeat at top on each printed page.
3) Insert a new worksheet name “Age group” and prepare the following data set:
4) Produce a Column chart with the above data in the same sheet.
5) Insert a new worksheet name “Trading Status” and create the following database.
6) Produce a Pie chart to get at a glance of trading status for the month of march in the same sheet.
7) Go to the Payroll sheet and protect the entire worksheet. Now try to change any basic salary of any employee.
8) Save and close your workbook with password for opening and modification level.
9) Try to open the workbook without supplying the password.
10) Close your workbook.
Page Setup, Lookup Functions and Splitting Worksheet
LEARNING OBJECTIVES
Page setting for printing
Lookup & reference functions
Splitting worksheet Page Setup Group Options
In Excel, you can print your worksheets just the way they look after you enter the data, or you can enhance the printout using several page layout options. When you select Excel's Page Layout Tab, the Page Layout tab contains several options as shown below.
Margin Option
This option is used to select the margin sizes for the entire worksheet.
1) To set a margin, Click the drop lists of Margins under Page Layout tab and then click Normal, Narrow, or Wide as required.
2) You will notice a dotted vertical and horizontal line, which indicate print area per page. Orientation
This option is used to switch the pages between portrait and landscape layouts.
1) To change the page layout, click the drop lists of Orientation option under Page Layout tab and then Click Portrait (vertical), which is the default, or Landscape (horizontal).
2) You will notice a Landscape layout (horizontal view) as shown below. Size
This option is used to select a paper size for a current section.
1) To select required paper size, click the droplists of Size option under Page Layout tab and then select A4 size page or any other page size as required.
2) Show the view of A4 size page. Print Area
This option is used to mark a specific area of the sheet for the printing.
1) To set a print area, select the range of cells.
2) Click the droplists of Print Area option under Page Layout tab and select set print area.
3) Dotted lines indicate the printable area. See figure below Breaks
This option is used to specify where a new page will begin in the printed copy. Page breaks are inserted above and to the left of the selection.
1) To apply a page break, place your cursor on a particular cell (A15).
2) Then click the droplists of Break option under Page Layout tab and select Insert page break option.
Background
This option is used to select an image to display as the background of the sheet.
1) To set a background image, click Background option under Page Layout tab and then select an image and click Insert button to add image as background. See figure below. Print Title
This option is used to specify rows and columns to repeat on each printed page.
1) To set a print title, click Print Title option under Page Layout tab and then select a row to repeat at top and column to repeat at left as required.
2) Then Click OK. Now if you take a printout, you will notice that the title is getting printed in each page. See figure below.
Previewing Worksheets Before Printing
You can view your Excel 2007 worksheet as it will be printed by clicking the Microsoft Office Button, pointing to Print, and then clicking Print Preview. When you do, Excel 2007 displays the active worksheet in the Print Preview window.
When the Print Preview window opens, it shows the active worksheet as it will be printed with its current settings. In the lower-left corner of the Print Preview window, Excel 2007 indicates how many pages the worksheet will require when printed and the number of the page you are viewing. Lookup & Reference Functions
These functions are useful when user has to extract a single set of data from a long list of data set. VLOOKUP( )
The VLOOKUP function looks up the value in the first column of the lookup table and returns the corresponding value in a specified table column. The lookup table is arranged vertically (which explains the V in the function’s name). The syntax for the VLOOKUP function is:
The VLOOKUP function’s arguments are as follows:
Lookup_Value : The value to be looked up in the first column of the lookup table.
Table_Array : The range that contains the lookup table.
Col_index_num : The column number within the table from which the matching value is returned.
Range_lookup : Optional. If TRUE or omitted, a nearest match is returned. (If an exact
Match is not found, the next largest value that is less than lookup_value is returned.) If FALSE, VLOOKUP will search for an exact match. If VLOOKUP can’t find an exact match, the function returns #N/A.
Tips: If the range_lookup argument is FALSE, the first column of the lookup table need not be in ascending order. If an exact match is not found, the function returns #N/A.
If the lookup_value argument is text, it can include wildcard characters * and ?.
Splitting the Worksheet Window into Panes
Sometime, you may face difficulties in managing large sheets. Problem may arise to view different part of the sheet with in a single window. To solve this problem Excel provides another option for viewing multiple parts of the same worksheet. Choosing View Window Split .It splits the active worksheet into two or four separate panes. The split occurs at the location of the cell pointer. If the cell pointer is in row 1or column A, this command results in a 2-pane split. Otherwise, it gives you four panes. You can use the mouse to drag the individual panes to resize them.
Class Exercise
1. You are having the following database, and you want to find any record in such a way so that when you enter the Salesman Name, his entire details will automatically appear. Configure the blank fields except Salesman name, After configuring the cells enter any salesman name and checks whether respective d-ata for that salesman appears or not:
Example:
Figure above shows a simple worksheet that uses two lookup formulas. This worksheet contains a table of employee data, beginning in row 7. This range is named EmpData. When you enter a last name into cell C2, lookup formulas in D2:E2 retrieve the matching information from the table. The following lookup formulas use the VLOOKUP function:
D2 =VLOOKUP(C2,EmpData,2,FALSE)
E2 =VLOOKUP(C2,EmpData,3,FALSE)
Pivot Table, Database Function and What-if-Analysis
LEARNING OBJECTIVES
Creating Pivot Table
Database Function
Scenario Manager
Goal Seek
Solver Pivot Table
A pivot table is essentially a dynamic summary report generated from a database. The database can reside in a worksheet (in the form of a table) or in an external data file. A pivot table can help transform endless rows and columns of numbers into a meaningful presentation of the data Creating a Pivot Table
If your data is in a worksheet range, select any cell in that range and then choose Insert ➪ Tables ➪ PivotTable, which displays the dialog box shown in
Excel attempts to guess the range, based on the location of the active cell. If you’re creating a pivot table from an external data source, you need to select that option and then click Choose Connection to specify the data source.
Specifying the location for the pivot table
Use the bottom section of the Create PivotTable dialog box to indicate the location for your pivot table. The default location is on a new worksheet, but you can specify any range on any worksheet, including the worksheet that contains the data. Database function DMIN
DMIN function is useful to find out the lowest value of a field based on the criteria provided by you. The syntax of the function is =DMIN (Database,”Field”,”Criteria”). Database is the table from where the result has to be extracted. Field is the field name (Column name) or the position of the column in the table of which minimum value has to be found out and criteria is the range of cells where you specify the condition.
This function is extremely helpful when you have multiple records for a particular item and you need to find the lowest value among them on certain criteria.
DMAX
This function is used to return the largest number of the field or column in a database based on certain criteria. The syntax of the function is: =DMAX(Database,Field,Criteria). Meanings of the arguments are same as DMIN function. Scenario Manager
Scenario Manager allows you to set up several different scenarios. You can use scenarios to predict the outcome of different situations in your spreadsheet. Suppose a company trying to establish a budget for the upcoming year does not know what the sales for the year will be. The sales figures obviously affect the rest of the budget. With the Scenario Manager you can create a “Best Case” and a “Worst Case” scenario. The best-case scenario would show that the budget figures would look like if sales for the year were especially good. The worst-case scenario would show the budget figures would look like if sales were especially poor.
Goal Seek
It determines the value that you need to enter in a single input cell to produce a result that you want in a dependent (formula) cell. The following example shows you how single-cell goal seeking works.
The figure above is showing an investment of Rs.1200 per month @9.5% rate of interest for 5 years can yield a future value of Rs.91706.70. Now the problem with the investor is to get Rs.150000 after the 5 years span. Now he/she wants to know how much should be invested to get that much of amount. This can be solved through Goal Seek option of excel what-if analysis tool. Following are the steps to be followed:
1. Place your cursor on cell B4. Chose Data Data Tools What-If Analysis Goal Seek. Excel displays the Goal Seek dialog box, shown in Figure below:
2. The following information box will appear.
Click OK to accept the result or Click Cancel to restore your worksheet to the form that it had before you chose the Goal Seek command. Solver
The Excel Goal-Seek feature is a useful tool, but it clearly has limitations. It can solve for only one adjustable cell, and it returns only a single solution. Excel’s powerful Solver tool extends this concept by enabling you to do the following:
1) Specify multiple adjustable cells
2) Specify constraints on the values that the adjustable cells can have
3) Generate a solution that maximizes or minimizes a particular worksheet cell
4) Generate multiple solutions to a problem
Set cell B4; the cell you where want to set the value.
Set value 150000 To value field.
By changing cell field should contain the address of the cell you want to change [B1]; i.e, the monthly premium.
Result
Class Exercise - 1
1. Create a new workbook and prepare the following data base with necessary functions.
P_Code
QTY
Rate
Amount
Discount Rate
Discount Amount
Net Amount
P001
42
20
10%
P002
45
15
5%
P003
50
10
5%
P004
100
11
10%
P005
52
21
4%
P006
84
33
3.33%
2. A partnership firm as earned a profit of Rs. 500000 in a particular year. The partners decide to share 45% of book profit amongst themselves in the ratio of their capital. Calculate their share of profit.
Partners
Capital
Share
T.Todi
150000
S.Laha
275000
M.Tewari
650000
3. Complete the following data base on another sheet of the same workbook. Rename the page as Salary.
Code
Basic
Dept
PF
House (Yes/No)
HRA
DA
Gross
PE/001
5000
ACCT
PE/002
4500
ACCT
PE/003
5200
MKT
PE/004
4100
ACCT
PE/005
3200
STCK
PE/006
3550
ADMIN
PE/007
5600
ADMIN
a. PF will be 12% of basic or Rs.600 whichever is higher.
b. Provide House for Accounts dept only
c. Calculate HRA @ 15% for those who do not get accommodation from the company.
d. DA will be 10% of Basic.
e. Calculate the gross salary.
4. Prepare the following layout for salary slip.
This is an auto generated pay slip, means, if any one put the Employee code the entire amount will be generated automatically. Use necessary functions for that.
Class Exercise - 2
5. Complete the following database with necessary functions.
a. If the total amount is more than Rs.1,00,000 then payment date will be 15 days after inward date.
b. Find out the total quantity of all products.
c. Find out the total value of Nokia Mobile
d. Find godown wise total report
e. Create a bar chat for the above data base, based on godown.
f. Find out minimum value product.
g. Highlight the product values which are more than Rs.70,000
h. Which godown is having more quantity of items?
i. Find out maximum value product.
j. Create a filter for the above database.
Class Exercise - 3
Complete the following questions.
1. If you have deposited Rs. 1,00,000 simple interest @ 6.25% p.a, how much you will get after 10 years?
2. If you have deposited Rs. 250000 in simple interest @3.25% p.a, how much you will get after 5 years?
3. Suppose you are going to deposit Rs. 2,646 quaterly in arecurring account to get back Rs. 100000 after certain period with 8% interest p.a. Find out how many years are required to get back the amount?
4. Suppose you have purchased a car for Rs. 5,50,00. You have pad Rs.1,00,000 in cash immediately. Rest of the amount will be paid 10 monthly equal installments with 12.75% interest p.a. Find out the EMI.
5. Present interest rate is 5% p.a. A financial scheme is giving Rs. 50000 at the end of 5 years. How much you have to pay in present to get the said value?
6. Complete the following trading Account for two financial years. Data are given below.
Particulars 2009-10 2010-11 Opening Stock 25000 20000
Purchase
1250000
150000 Purchase Return 36000 40000
Sales
1100000
1350000 Sales return 65000 51000
Wages
50000
52000
Unit 5: MS-EXCEL 2007 Introducing Microsoft Office Excel 2007
LEARNING OBJECTIVES
Opening Ms-Excel
Working with Spreadsheet
Using data in spreadsheet
Active cell
Microsoft Excel is a spreadsheet program that provides with powerful tools and features to analyze share and manage your data with least effort. A Spreadsheet is defined as a large sheet containing information arranged in a matrix of rows and columns. Spreadsheet software is a program or a set of programs for entering, calculating, analyzing and manipulating sets of number. A computer spreadsheet automatically recalculates all the formulae whenever you make a change in the value or number. There are so many other spreadsheet programs such as Lotus 1-2-3, Quattro Pro etc. are available, but Excel is by far the most popular and has become the world standard.
Steps to Open Microsoft Office Excel
1) Click Start MenuAll ProgramsMicrosoft OfficeMicrosoft Office Excel 2007
Alternative Way
1) Right-clickChoose New from the pop-up menu.
2) Click Microsoft Office Excel Worksheet from the sub-menu.
3) Double-click on the Microsoft Office Excel Worksheet icon to open.
When you open Microsoft Office Excel 2007, the first thing you will notice is the new interface. Now that we’ve opened Excel, let’s take a look at what’s on our screen. Refer to figure below.
A brief discussion about the different components of Excel’s opening Screen:
Home: You’ll probably spend most of your time with the Home tab selected. This tab contains the basic Clipboard commands, formatting commands, style commands, commands to insert and delete rows or columns, plus an assortment of worksheet editing commands
Insert: Select this tab when you need to insert something in a worksheet—a table, a diagram, a chart, a symbol, and so on.
Page Layout: This tab contains commands that affect the overall appearance of your worksheet, including settings that deal with printing.
Formulas: Use this tab to insert a formula, name a range, access the formula auditing tools, or control how Excel performs calculations.
Data: Excel’s data-related commands are on this tab.
Review: This tab contains tools to check spelling, translate words, add comments, or protect sheets.
View: The View tab contains commands that control various aspects of how a sheet is viewed. Some commands on this tab are also available in the status bar.
Control Buttons: At the right end of the title bar are the three familiar buttons that have the same functions in all windows programs. You can temporarily hide the word window by clicking the Minimize button, adjust the size of the window with the Restore Down/Maximize Button,
and close the active document or quit excel with the close button.
Name Box: Address bar shows the address of the active cell. In the above figure active cell is A1 that is why we are getting A1 in the name box.
Formula Bar: The formula bar is located beneath the toolbar at the
top of the Excel worksheet. Use the formula bar to enter and edit
worksheet data. The contents of the active cell always appear in the
formula bar.
Rows, Columns and Cells: Every worksheet consists of rows (numbered 1 through 1,048,576) and columns (labeled A through XFD). After column Z comes column AA, which is followed by AB, AC, and so on. After column AZ comes BA, BB, and so on. After column ZZ is AAA, AAB, so on. Each worksheet has 16,384 columns and
10,48,576 rows. So, each worksheet contains 16384 X 1048576 numbers of cells.
Home Ribbon
Ribbon Tabs
A cell is the intersection of a row and a column. For example, the first cell is referred to as A1 (column A, row 1). Data and formulas are entered into the cells. Each cell can hold only one piece of data at a time.
Sheet Tabs: Sheet tabs holds list of worksheets by default it provides three Worksheets named Sheet1, Sheet2, and Sheet3
respectively.
Horizontal and Vertical Scroll Bar: The Scroll Bar is used roll up/down/right and left and see the column/row number changes until you release the mouse button.
Zoom Slider: A slider that magnifies or reduces the content in the worksheet area.
Groups: On each tab, buttons are organized into groups. Depending on the size of the program window, in some groups the button you are likely to use most often is bigger than the rest. Dialog box launcher: Related but less common commands are not represented as buttons in the group. Instead they are available in a dialog box, which you can display by clicking the Dialog Box Launcher at the right end of the group’s title bar
Workbook and Worksheet
When you open Excel, the Excel application window opens with a new Excel workbook. A Workbook is a multi-page Excel document. After saving the Excel file or workbook it takes the default extension .XLSX in its filename. Each page in the workbook is called a Worksheet. The active worksheet is displayed in the document window. By default, a new workbook has 3 sheets. You can add or remove sheets from a workbook to suit your need. At the left end of the horizontal scroll bar are sheet tabs and navigation buttons. Click a sheet tab to move to another worksheet or use the navigation buttons to scroll through the sheet tabs.
Types of Data you can Enter in Excel
An Excel workbook can hold any number of worksheets, and each worksheet is made up of more than 17 billion cells. A cell can hold any of three basic types of data:
Numerical values
Text
Formulas
A worksheet can also hold charts, diagrams, pictures, buttons, and other objects.
About Numerical Values
Numerical values represent a quantity of some type: sales amounts, number of employees, atomic weights, test scores, and so on. Values also can be dates (such as Nov-11-2009) or times (such as 3:24 p.m.).
About text entries
Most worksheets also include text in their cells. You can insert text to serve as labels for values, headings for columns, or instructions about the worksheet. Text is often used to clarify what the values in a worksheet mean.
Text that begins with a number is still considered text. For example, if you type “12 Employees” into a cell, Excel considers the entry to be text rather than a value. Consequently, you can’t use this cell for numeric
Calculations. If you need to indicate that the number “12” refers to employees, enter 12 into a cell and type Employees into the cell to the right.
About formulas
Formulas are what make a spreadsheet a spreadsheet. Excel enables you to enter powerful formulas that use the values (or even text) in cells to calculate a result. When you enter a formula into a cell, the formula’s result appears in the cell. If you change any of the values used by a formula, the formula recalculates and shows the new result.
Entering Data in Cell
Entering data in Excel sheet is very easy. You need not concentrate a lot to learn this thing. There are many types of data that you can enter into your worksheets including text, numbers, dates, times, formulae, and functions. Text is any combination of letters, numbers, and spaces. By default, text is automatically left aligned in a cell. However, Numbers and Dates are right aligned.
Active Cell
In an Excel worksheet, the active cell is the cell with the black border. Data can only be entered into the active cell. Even if more than one cell is selected, there is still only one active cell. Use the mouse pointer or the arrow keys to change the location of the active cell.
Number Entered
Date Entered
Text Entered
Also Known As: Current Cell
Examples: Click on cell B2 with the mouse pointer to make it the active cell before entering the data.
Moving Around a Worksheet
As you probably already know, you can use the standard navigational keys on your keyboard to move around a worksheet. These keys work just as you’d expect: The down arrow moves the active cell down one row, the right arrow moves it one column to the right, and so on. PgUp and PgDn move the active cell up or down one full window. (The actual number of rows moved depends on the number of rows displayed in the window.)
Active Cell has a black border.
Name Box, Copy & Paste, Cell Editing, Allignment and Saving File
LEARNING OBJECTIVES
Using name box
Copy and paste function
Cell editing
Alignment
Saving workbook Navigating using Name box
For larger worksheets (remember there are over 16,000 columns and more than one million rows in a single Excel 2007 worksheet) there has to be a better way to get around then endless scrolling with the mouse - and there is - by using the Name Box. Learn how to jump from one area of your spreadsheet to the next in this book.
Editing Cells in Excel
You can change a data in a cell directly by overwriting the same using mouse and keyboard.
Using Mouse
Suppose you have entered a wrong word “Janury” in Cell A1. You need to edit the text as “January”. Now follow the steps to edit the data using mouse.
Method
1) Double-click on Cell A1 for editing. Edit the part of the cell you want to change using Backspace key.
2) Finally type in the new data and press enter key.
Type the destination cell reference in the Name box.
Copying and Pasting data in Excel
You can save a great deal of time in Excel by copying data rather than repeating the steps needed to recreate or reapply them over and over. Learn how to use Excel's copy and paste options to copy items to different areas of the worksheet.
Using Mouse
You can copy an entry into surrounding cells by using copy and paste command under Home Tab using mouse.
Steps:
1) In the cell A1, type January and press enter key to accept the data.
2) Keep your cursor on the cell A1 and click on Copy command under Home tab.
(Suppose you want to copy the text January in B2, C2 & D2 Cells.)
Position the mouse pointer on the cell B2 and drag till Cell D2 as shown above.
3) Now click Paste command.
Moving data in Excel
Sometimes we don't need to copy data, headings or formulas, but just move them to a new location. Learn how to do this using Excel's cut and paste features.
Using Mouse
You need to move the data in Column-A to Column-C as shown below using mouse.
Steps:
1) Place the cursor on the Cell A1 and drag till Cell A4 by holding Left mouse button.
2) Click Cut (CTRL+X) command under home tab.
3) Place the cursor on target cell (C1) and then Click Paste Command to move the content from Column-A to Column-C.
Paste Command (CTRL+V)
Copy Command (CTRL+C)
Format Data using Font Group
There are many ways of smartening up a worksheet, for example changing font, font size, font style or using white text on a black background. Excel mainly supports two types of data: Text and Number. You can format them using font group. The font group provides the quickest way for controlling various fonts attributes such as the font and the font size, colour etc. To access the font group, just click the Home tab. All of these commands are covered below.
1) In cell A1 type the heading ‘Monthly Sales’.
2) Select the text and click Font drop lists to make it Stencil, 12 point and Bold or Click Increase/Decrease Font command.
3) To make white text on a black background. Choose white font colour and Black fill colour from Font colour drop lists and Fill colour drop lists respectively.
Border
4) Adding lines and borders is a quick way to format important information in Excel. For example, type amount 20000, 75000, 40000 and 60000 in Cell A2:A5. Select cell A3, Click Border drop lists and select ‘Thick Bottom Border’. The effect you can see below.
Alignment Group Options
There are many ways of formatting cell alignment, for example changing the alignment of the cell contents, position the cell contents in the cell, and change the direction of the cell contents. Combines two or more selected cells into a single cell and wraps text into multiple lines in a cell. All of these commands are covered below.
1) Here you can use another type of alignment command called Merge & Center. The Merge and Center button on the Alignment group lets you quickly center text in the left-most cell across the entire range of cells you select.
Select Cell A1:C1 and Click Merge & Center command. This feature is good for centering a title and subtitle at the top of a worksheet as shown below.
2) In the above example, Salesman names are very long and the full name is not visible. To make all content visible without increasing the column width, you need to use Wrap Text command under Alignment group. Wrap text command is use to place a long text on a multiple lines within a single cell. Select Cell A3:A6 and Click Wrap Text button under Alignment group of Home Tab.
Before Merging Cells
After Merging Cells
3) Now, you need to keep the month and Amount in proper alignment like center, Left, Top etc. Select Cell B3:B6 and Click Center and Middle Align button. Similarly the Cell C3:C6 and Click Middle Align button.
4) Now you want to rotate text to a diagonal angle or vertical orientation, which is often used to narrow columns. Place the cursor on Cell B2 and click Orientation drop lists to select the required button. You can also use Increase/decrease Indent command to move the content from any edge of the cell depending on your choice. Each increment in the Indent box is equivalent to the width of one character. To use indent, keep the cursor on a target cell and click Increase/decrease indent button.
Number Group Options
Use the options on the Number tab to apply a specific number format to numbers in cells on the worksheet. To type numbers in worksheet cells, you can use the number keys, or you can press NUM LOCK, and then use the number keys on the numeric keypad. Let’s take an example to explain the use of number group options as shown below.
To learn all the above commands, let take a database as shown below.
1) To change the amount in Indian currency (Rs.). First go to StartControl PanelDouble-Click on Regional and Language Options.
2) Click Customize button to open Customize Regional Options dialog box.
3) Then Click Currency Tab. Change the currency symbol as “Rs” instead of “$” symbol and Click OK.
4) Now, select Cell C3:C6 and choose Indian Currency from Accounting Number Format drop lists.
If you want to display thousands separator instead of Indian currency, then click Comma style button. You can also increase or decrease decimal places by clicking Increase/Decrease decimal place button as shown below.
5) To allow a Percentage (%) sign as suffix with the numeric data and multiply by 100, you need to click percentage style button. Select Cell D3:D6 and click percentage style as shown below.
Adjusting the System Date Settings
If you dates are being interpreted as April 1st instead of January 4th, this has to be altered in the Control Panel. You probably won’t be able to do this on a network but you can do it on your home machine or get the network manager to correct the settings.
1) Go to StartControl Panel.
2) Double-Click on Regional and Language Options.
3) Click Customize button to open Customize Regional Options dialog box.
4) Click Date Tab. Change the Short date format as “dd/mm/yy” and Click OK.
5) Save and reopen an existing workbook for new setting to take effect.
Saving a Workbook using Keyboard
Press key to display Save As dialog box as shown above.
Type a new file name and click Save button.
Opening an Existing Workbook
To open an existing file of Excel from the permanent disk:
1) Select Office Button Open or press.
2) The Open dialog box will appear, choose your file from relevant path and click Open button.
Closing an Excel Workbook.
To exit the active workbook window:
Go to Office Button Click Exit Excel.
To Close Excel Workbook using Keyboard, press key
Class Exercise
1) Open a new worksheet and put the following information in Sheet-1.
2) Format field names (second row) with Font face: Cambria; Font Size: 12 points; Font Style: Bold; Colour: Blue; Border: All border; Fill Color: Olive Green and records (third to sixteen rows) with Font face: Arial; Font Size: 10 points.
3) Merge and center first seven columns fo the first row for the title ‘Student Details’.
4) Format the heading of the database “Student Details” with Font face: Bernard MT Condensed; Font Size: 18 points; Border: Thick Box border; Fill Color: Black and Font Colour: White.
5) Apply Indian Currency (Rs.) format on Fees column with two decimal places.
6) Apply long date format on Date of Admission column.
7) Create a new folder with your name followed by ‘ExcelWork’ under C:\Candidates folder (e.g. Arjun_ExcelWork) and Save the current workbook with a name ‘Excel Assignment’ under your folder.
Place the mouse pointer on the separator of two column letters or row numbers when mouse pointer converts a symbol like below double click. Column Width, Math Function, Workspace and Saving File
LEARNING OBJECTIVES
Column width
Mathematical function
Workspace creation Column width and default size
If the cell width is not adequate for the contents of a column, you can change it to its exact required size using either Best Fit feature or AutoFit option. Best Fit is used not only to change the cell width as well as to change the row height also.
Place the mouse pointer on the separator of the column letters or row numbers, then double-click on it, immediately the Column width or Row height will be changed to display the widest entry within that particular column or row. Always you have to consider the separator between the current column or row and next column or row. This is known as Best Fit feature.
How to select a cell, a range, or text in a cell
Simple Mathematical Calculation
Formulas are used to obtain answers based on mathematical equations that you design. Formulas can be as simple as "2+2" or as complex as calculating the depreciation of fixed assets. When creating formulas, you may use actual values, cell addresses or a combination of the two.
The equal sign (=) is used to indicate to Excel that you are about to enter a formula. This also ensures that formulas beginning with a cell address are not mistaken for text.
Next, you must enter the actual formula for Excel to calculate. When entering these formulas, the following basic mathematical operators are used:
+ (Addition)
- (Subtraction)
* (Multiplication)
/ (Division)
When a formula is complete and the key has been pressed, the result will be displayed in the current cell. The formula itself is displayed in the formula bar (located in the upper-left of the screen next to the cell address).
Let’s take a database to explain the above mathematical operator’s.
1) In the above example, to calculate amount, place the cursor on Cell E3 and type “=” sign, then click Cell C3, type asterisk sign (*) and then click Cell D3 and press enter to accept the formula. Now drag the fill handle till Cell E5 to copy the formula in the respective cells.
2) Calculation of Total Amount, Vat, Discount and Net Total is discussed below. Operators Used in Formulas Operator Name Formula What It Does
+
Addition
=5+6 will return 11
–
Subtraction
=6-5 will return 1
*
Multiplication
=6*5 will return 30
/
Division
= 6/2 will return 3
^
Exponentiation
=6^3 will return 216
Drag the fill handle
Let’s take another database to explain the “/” Division operator as shown below.
Working with Worksheet
Changing Sheet Name
You can change the name of the sheet tabs:
1) Double-click on the sheet tab of the worksheet you want to rename or right-click on the tab, type the name and press enter.
2) Select Rename option from the context sensitive popup menu , type the name and press enter
3) The current name will be highlighted. Type a new name and press Enter key.
Adding a New Worksheet
1) Right-click on the sheet tab
2) Choose and click Insert option from the popup menu.
To quickly insert a new worksheet at the end of the existing worksheets, click the Insert Worksheet tab at the bottom of the screen. Click the drop lists of Insert from the Cell group under home tab.
Removing a Worksheet
1) Right-click on the sheet tab
2) Choose and click Delete option from the popup menu.
Caution: Be careful when deleting worksheets as you cannot undo the deletion.
OR
1) Click the drop lists of Delete from the Cell group under home tab.
2) Choose and click Delete sheet.
Working with Rows and Columns
Rows and columns make up an Excel worksheet. Every worksheet has exactly 1,048,576 rows and 16,384 columns, and these values can’t be changed. You can insert/delete Row(s) and Column(s) but here rows and columns will be deleted automatically from the last according to the number of rows or columns inserted.
Inserting rows in excel is very simple. Right click on row number before which you want to insert the row and Select insert option from the popup menu that appears. Accordingly you can insert column(s) right clicking on the selected columns and clicking on the insert option. Columns always inserts just before your selected column(s).
Alternatively,
You can also insert Row(s) or columns from the home Tab ribbon clicking on the Insert button and clicking on the Insert sheet rows or columns option.
Deleting Rows or Columns
Row(s) or Column(s) can be deleted by right clicking on the selected Row(s) or Column(s) and clicking on the Delete option.
Right click on the selected rows and click on Delete option from the Dropdown list, selected row(s) or column(s) will be deleted. Here one row is selected naturally one row will be deleted.
Accordingly you can delete column(s) right clicking on the selected columns and clicking on Delete option from the Drop down list.
Alternatively,
You can also delete selected row(s) or column(s) clicking on Delete sheet Rows or Columns from the insert button under the home tab ribbon’s Cells group.
Right click on the selected rows and click on Delete option from the Dropdown list, selected row(s) or column(s) will be deleted. Here one row is selected naturally one row will be deleted.
Accordingly you can delete column(s) right clicking on the selected columns and clicking on Delete option from the Drop down list.
Hide/Unhide rows or columns
To get the options of hide and unhide rows and columns, go to format button of Home tab
To hide the selected rows Click on Hide Rows and for hiding columns click on Hide Columns. Contents of the hidden rows/columns will not be printed. To get back the hidden row(s)/column(s), select the previous and next rows of the hidden row(s)/column(s) or select the entire worksheet, and then choose any of these options accordingly. All the hidden row(s)/column(s) will come back immediately.
Adjusting Row Height
Row Height – This option is used to change the row height of the selected row or rows to the defined value. The following dialog box appears, when you click this option, type the value and click on OK button.
AutoFit Row Height – This feature is used to change the height of the selected cell(s) to fit the contents accordingly. This can be done by Best Fit option also.
Column Width – This option changes the width of the selected column or columns. The following dialog box will appear, define the value and then click on OK button.
Class Exercise
1) Open your last saved workbook and go to Sheet-1.
2) Best fit the columns and rows if required.
3) Rename the Sheet-1 as ‘Student List’.
4) Add two more field name after Fees field as ‘Service Tax @10.3%’ and ‘Total Fees’. Apply similar cell formatting in this two field using format painter.
5) Adjust column width in such a way, so that the text ‘Service Tax’ only appears in a single line and the rest text in the second line of the same cell.
6) Adjust row height as required.
7) Calculate Service tax @ 10.3% on Fees.
8) Calculate Total Fees as Fees+Service Tax.
9) Merge and center first cell containing text ‘Student Details’ till Cell I1.
10) Go to a new worksheet and prepare the table (electricity) as follows:
Meter no
Opening reading
Closing reading
Metered units
Rate/unit
Total amt
Rental charge
Service tax 10.3%
Bill Amount
DE/023
4568
5528
2.25
84.40
AP/153
3210
3285
2.25
84.40
FE/092
1102
1125
2.25
84.40
JU/120
6521
8543
2.25
84.40
11) Save and Close the workbook.
Style, Table & Conditional Formatting and Format Painter
LEARNING OBJECTIVES
Style formatting
Table formatting
Conditional formatting
Using format painter Style Group
A style in Excel 2007 is a combination of formatting options that is named and saved as part of your current spreadsheet file. The new style can then quickly be applied to data and cells in the spreadsheet. Let’s take an example to explain the use of Style group options as shown below.
Cell Styles
Cell styles make it very easy to apply a set of predefined formatting options to a cell or range. In addition to saving time, using named styles also helps to ensure a consistent look. You can also defined you own style.
1) Select the desired cells (D3:D6).
2) Click on the Cell Styles option on the Style Group under Home Tab to open the Cell Styles gallery.
3) Click on any style name at the top of the gallery as required.
4) The style's formatting is immediately applied to the selected cells.
Format as Table
Pre-defined table style offers a quick way to apply a standard format to all or part of a worksheet. Before you use Pre-defined table style, select the cells to be formatted. Choose Home Tab Styles group Format as Table and then select any desired style from the display list.
After applying Cell Styles Before Applying Style
After Applying Style
Simple Conditional Formatting
Conditional formatting has improved significantly in Excel 2007, and it’s now a useful tool for visualizing numeric data. Conditional formatting enables you to apply cell formatting selectively and automatically, based on the contents of the cells.
Specifying Conditional Formatting
To apply a conditional formatting rule to a cell or range, select the cells and then use one of the commands on the Home Tab Style group Conditional Formatting drop-down to specify a rule. The choices are:
Highlight Cell Rules: Examples rules includes highlighting cells that are greater than a particular value, between two values, contain specific text string, or are duplicated.
Top Bottom Rules: Examples include highlighting the top 10 items, the items in the bottom 20 percent, and items that are above average.
Data Bars: Applies graphic bar directly in the cell, proportional to the cell’s value.
Color Scales: Applies background colour, proportional to the cell’s value
Icon Sets: Displays icons directly in the cells. The icons depend on the cell’s value.
When you apply conditional formatting rules, Excel displays a dialog box that’s specific to that rule. These dialog boxed have one thing in a common: a drop-down list with common formatting suggestions.
Above Figure shows the dialog box that appears when you choose Home TabStylesConditional FormattingHighlight Cells Rules Between. This particular rule applies the formatting if the value in the cell falls between two specific values. In this case, you enter the two values, and then use the drop-down control to choose the type of formatting to display if the condition is met.
Figure below shows a worksheet with nine ranges, each with different types of conditional formatting rule applied. Here‘s a brief explanation of each.
Type a Value
Select any format
Greater than 10: Values greater than 10
are highlighted with a different
background colour. This rule is just one
of the many numeric value related rules
that you can apply.
Above Average: Values that are higher
than the average value are highlighted.
Duplicate Values: Values that appear
more than one time are highlighted.
Words that contain X: If the cell contains
X (upper or lower case), the cell is
highlighted.
Data Bars: Each cell displays a horizontal
bar, proportional to its value.
Color Scale: The background colour varies, depending on the value of the cells. You can choose from several different colour scales or create your own.
Icon Set: One of many icon sets. It displays a small graphic in the cell. The graphic varies, depending on the cell value.
Removing Conditional Formats
If you no longer want to conditionally format your data, you can remove any rules applied to it and return it to its original format. To remove conditional formats from the selected cells, sheet, or table, click Conditional Formatting in the Styles group of the Home tab. Point to the Clear Rules option and select the appropriate option from the fly-out menu.
Format Painter
Format Painter option is use to copy the formats from one part of a sheet to another, or to another sheet in the same workbook. This option is available under Home TabClipboard Group. Example: 1
To use Format Painter to copy formatting options to a second cell as shown below.
1) Add all the formatting options you want to use to the first cell range.
2) Click on that cell with the mouse pointer to make it the active cell.
3) Click on the Home tabFormat
Painter icon under Clipboard
group.
4) Clicking on a second cell range will
copy the formats from the active
cell to this second cell.
Class Exercise
1) Open your last saved workbook and go to Sheet-1.
2) Highlight course B.COM using conditional formatting.
3) Highlight Fees between Rs.50,000 to 100,000 using conditional formatting.
4) Apply Cell Style named ‘Assent 4’ on all the centre names.
5) Select the table leaving the first row and apply Table Style Light 20 format.
6) Delete all un-used worksheets.
7) Save and close the workbook.
Cell Reference, Fill Series, Custom List and Formatting Numbers
LEARNING OBJECTIVES
Cell reference
Filling series
Custom list
Formatting numbers Cell Reference
Most formulas you create include references to cells or ranges. These references enable your formulas to work dynamically with the data contained in those cells or ranges rather than being restricted to fixed values. For example, if your formula refers to cell A1 and you change the value contained in A1, the formula result changes to reflect the new value. If you didn’t use references in your formulas, you would need to edit the formulas themselves in order to change the values used in the formulas.
When you use a cell (or range) reference in a formula, you can use three (3) types of references:
1) Relative Reference: It refers to the cell by their position in relation to the cell that contains the formula. The row and column references can change when you copy the formula to another cell because the references are actually offsets from the current row and column.
2) Absolute Reference: It refers to the cell by their position in relation to the cell that contains the formula. The row and column references do not change when you copy the formula because the reference is to an actual cell address.
3) Mixed Reference: Any of the row or column reference is relative, and the other is absolute.
By default, Excel creates relative cell references in formulas. The distinction becomes apparent when you copy a formula to another cell. The formula in cell D2, which multiplies the quantity by the price, is =B2*C2. This formula uses relative cell references. Therefore, when the formula is copied to the cells below it, the references adjust in a relative manner. For example, the formula in cell D3 is =B3*C3
Filling Series
Excel supports a wide variety of fill options. Go to Home Tab Editing Group Fill Series to display the series dialog box.
Creation of Linear Series
Linear series is for the series that increases by adding the same value to each successive cell.
Creation of Growth Series
Growth series is for the series that increases geometrically multiplying by a value.
Custom List
Entering a Series is similar to fill the content of cell for a group of cells. Suppose you want to create a series of days, such as Sunday, Monday, and so on. Type Sunday in a cell and drag the fill handle of the cell in any direction, and check a series of days will be generated. Similarly you can create a series of months, i.e.,
Figure demonstrates the use of mixed references. The formulas in the C3:E7 ranges calculate the phone bill for various call made and phone rate. The formula in cell C3 is =$A3*C$2
Select any one of the options from the series in options according to your requirement. Here we are selecting columns.
Select the option Growth from the type list.
Input step value and stop value as your requirement. Here we are using 5 as step value and 400 as stop value. It will create a series like 3, 15, 75, 375
Click on OK to finish.
Select the cell based on which you want to generate the series.
Show Multiplication of 5
January, February, and so on using same way. These series are generated because they are already defined in Excel. You can create your own series with text or number etc.
1) To define your own list Go to Office Button Excel Options Popular Tab Edit Custom Lists
2) Click on NEW LISTType the List itemsClick on Add.
Formatting numbers using the Format Cells dialog box
In most cases, the number formats that are accessible from the Number group on the Home tab are just fine. Sometimes, however, you want more control over how your values appear. Excel offers a great deal of control over number formats through the use of the Format Cells dialog box, shown in Figure below. For formatting numbers, you need to use the Number tab.
Number Formats
Select Number control tab, to apply any number formatting.
General – The default format of number will be applied. Mainly it is required to remove or cancel any number format from the selection.
Number – This type of formatting allows you to set number of decimal places, with a thousand separators, and formatting of negative numbers.
Currency – Numbers are preceded with a default Currency sign immediately. You can also set here number of decimal places and the formatting of the negative numbers. Zero values are displayed.
Accounting – Currency sign and decimal places can set with the numbers. But the setting of Currency symbol with number is different from Currency format. Currency symbols are coming with left alignment whereas numbers or values are right aligned in a cell.
Date – This formatting gives a variety of Date formats.
Time – Different time formats provided by this option.
Percentage – This formatting attached a percentage sign with the number as suffix and multiply the number by 100 (hundred).
Fraction – Includes formats based on either the number of digits to display in the divisor (1, 2, or 3) or the fractional unit (halves, quarters, tenths, and so on).
Scientific – Numbers are displayed in scientific notation. For example: 1.01E+03.
Text – Changes a number to text without adding other formatting. This is useful for numeric labels that may include leading zeros. All the regular numeric formats strip off leading zeros. You must apply the Text format before entering the cell’s contents.
Special – This is mainly used for Telephone numbers, Zip Codes etc. those are mainly having some special formats.
Example of cell formatting:
In the following sections, you see how to use many of Excel’s formatting options to quickly improve the appearance of your worksheets.
Text Alignment
Horizontal: Select an option in the Horizontal list box to change the horizontal alignment of cell contents. By default, Microsoft Office Excel aligns text to the left, numbers to the right and logical and error values are centered. The default horizontal alignment is General. Changing the alignment of data does not change the data type.
Vertical: Select an option in the Vertical box to change the vertical alignment of cell contents. By default, Excel aligns text vertically on the bottom of a cell. The default horizontal alignment is General.
Indent: Indents cell contents from any edge of the cell, depending on your choice under Horizontal and Vertical. Each increment in the Indent box is equivalent to the width of one character.
Orientation: Select an option under Orientation to change the orientation of text in selected cells. Rotation options may not be available if other alignment options are selected. Degrees: Sets the amount of text rotation in the selected cell. Use a positive number in the Degree box to rotate the selected text from lower left to upper right in the cell. Use negative degrees to rotate text from upper left to lower right in the selected cell.
Text control
Wrap text: Wraps text into multiple lines in a cell. The number of wrapped lines is dependent on the width of the column and the length of the cell contents.
Shrink to fit: Reduces the apparent size of font characters so that all data in a selected cell fits within the column. The character size is adjusted automatically if you change the column width. The applied font size is not changed.
Merge cells: Combines two or more selected cells into a single cell. The cell reference for a merged cell is the upper-left cell in the original selected range.
Text direction: Select an option in the Text direction box to specify reading order and alignment. The default setting is Context, but you can change it to Left-to-Right or Right-to-Left.
In this table there are some examples of formatted and unformatted text. There it is also mentioned type of format we have used for that particular cell.
Font Control Tab
1) Select the font from the font list.
2) Select font style as required.
3) Select font size to increase/decrease the font size.
4) Select underline style.
5) Choose font colour.
6) Apply text Effects.
7) See the preview of your applied formats.
Border and Fill Control Tab
Class Exercise
1) Open the last saved workbook.
2) Insert a new worksheet . Name the worksheet as ‘Reference’ and prepare the following table in it with necessary formatting.
3) Complete the above table using Mixed Cell Referencing concept with necessary formatting.
4) Prepare another table just beside the City-Wise Conveyance chart as shown below. Fill Tab
Background Color - Select a background color for selected cells by using the color palette.
Fill Effects - Select this button to apply gradient, texture, and picture fills to selected cells.
More Colors - Select this button to add colors that are not available on the color palette.
Pattern Color - Select a foreground color in the Pattern Color box to create a pattern that uses two colors.
Pattern Style - Select a pattern in the Pattern Style box to format selected cells in a pattern that uses the colors that you select in the Background Color and Pattern Color boxes.
Sample - See a sample of the color; fill effects, and pattern options that you select.
5) Calculate Bill Amount using Absolute Cell Referencing concept.
6) A Partnership firms earned a profit of Rs.800,000 in the year 2009. The partners decide to share 40% of the profit amongst themselves in the ratio of their capital. Calculate their individual shares of profit in the same sheet.
7) Change the value of Akbar’s capital to Rs.600,000 and check whether the table of Partner’s profit has been reflected or not.
Mathematical Functions
LEARNING OBJECTIVES
Mathematical Function
Sum
Average
Max
Min
Round
Sqrt
Power
Some Popular Functions of Excel
Excel includes many math, trigonometry and text functions. These functions perform a wide variety of Calculations such follows:
1) SUM
2) AVERAGE
3) MAX
4) MIN
5) ROUND
6) SQRT
7) POWER
SUM Function
General mathematical built-in functions are provided with Excel to carry out calculations on data within the spreadsheet and can take the place of certain types of formulas.
Functions begin with the = sign just as formulas do. For example, if you had a large column of numbers to be added (A1:A100), you might think you need a long formula to include all of the addresses (=A1+A2+A3....+A99+A100). However, Excel provides a mathematical function which is used primarily to add blocks of numbers. The formula could be re-written as =SUM (A1:A100) which is much shorter.
AVERAGE Function
This function returns the average value for a block of cells. The syntax is: =AVERAGE (FIRST CELL : LAST CELL)
Example:
=AVERAGE (23,89,34,20) The return value will be 41.5.
=AVERAGE (D2:D10) It will return the average value of the range D2 to D10.
Note: You can use non-contiguous cell references also using comma (,) separator.
MAX Function
This function returns the largest value in a block of cells. The syntax is: =MAX (FIRST CELL : LAST CELL)
Numbers are asking for the values or numbers from which you want to find out the maximum value.
Example:
Cell Range from A1 to A10 (A1:A10)
SUM is one of the most commonly used functions. It adds all the numbers in the range of cells. The syntax of the SUM function is: =SUM (FIRST CELL : LAST CELL)
Range is a group of cells to be calculated by the function. You can also provide values as arguments.
Example:
=SUM (15,20, 45,83) You can use values in the function
=SUM (A1:A10) For contiguous cells, give the reference of the first cell, then a colon sign (:) and then cell reference of the last cell of the range. (See figure).
=SUM(B5:B10,B15,B20:B25) For non-contiguous references, use comma as a separator.
Using the Average function to find the Average value in the range of cells
=MAX (45, 12, 78) This function will returns the value 78 which is the highest value out of the given
number.
=MAX (D2:D10) It will return the largest value from the defined range.
MIN Function
This function returns the smallest value in a block of cells. The syntax of the function is: =MIN (FIRST CELL : LAST CELL)
Numbers are the arguments from which the minimum value will be finding out.
Example:
=MIN (45,12,56) The return value will be 12.
=MIN (D2:D10) It will return the minimum value from the defined range.
ROUND Function
This function Rounds a number to a specified number of digits. The syntax of the function is: =ROUND (<Number>, <Num_digits>)
Number is the number you want to round.
Num_digits specifies the number of digits to which you want to round number
Example:
=ROUND (235.75,1) The return value will be 235.8, because you want to round off the number to one decimal place. Since the second decimal value is 5, Round function will add 1 with the first decimal value.
SQRTFunction
This function returns the square root value of a given numbers. The syntax of this function is: = SQRT (NUMBER)
Example:
=SQRT(25) Square root of 25 is 5.
POWER Function
This function returns the result of a number raised to a power. The syntax of this function is: =POWER (Number,Power)
The "^" operator can be used instead of POWER to indicate to what power the base number is to be raised, such as in 5^2.
Example:
=POWER(5,2) The return value will be 25, due to 5 squared.
=POWER(3,1/3) The return value will be 1.44225 , due to 3 raised to the power of 1/3.
Concept of Range
A group of cells is called a range cells. The cells in a range may all be in one column, or one row, or any combination of columns and rows, as long as the range forms a rectangle. A range also can be a single cell. Ranges are referred to by their anchor points, means the upper-left cell address and its lower-right cell address, separated by colon. Some examples of range addresses C24
A range that consists of a single cell. A1:B1
Two cells that occupy one row and two columns. A1:A100
100 cells in column A. A1:D4
16 cells (four rows by four columns). C1:C1048576
An entire column of cells; this range also can be expressed as C:C. A6:XFD6
An entire row of cells; this range also can be expressed as 6:6. A1:XFD1048576
All cells in a worksheet.
Adding Comments to Cells
In excel a note can be added on a certain cell through inserting a comment. You can edit the comment and delete those comments which are no longer useful.
Select the cell and then go to Review tab Comments group New Comment. ~Or~
Right-click on the cell and choose Insert Comment from the popup menu.
After you finished writing, click anywhere outside the comment area to hide the comment box.
A small red arrow at the top right corner of the cell indicates that a comment is inserted on that cell.
Class Exercise
1) Open your last saved workbook and go to the Student List sheet.
2) Delete all the Student Code list and set the Student Code range (A3:A16) in such a way that entering ‘1’ will automatically display ‘ST/0001’. [Hint: Use Custom number format]
3) Feed student code as 1, 2, 3, ……., 14 in the respective cell.
4) Insert a new worksheet after Reference sheet. Name the worksheet as ‘Result ’ and prepare the following dataset in it.
5) Calculate Total, Avearage, Maximum and Minimum marks in the above table.
6) Insert a note ‘Fundamental of Accounts’ on cell F1.
7) Prepare the following table with in ‘Result’ worksheet and do the necessary operations to give resulted figures under appropriate column head as per given instructions.
a. Find out the Square root of the original numbers under the head Square Root.
b. Round off the square root figures upto 2 (two) decimal places and keep under Rounded Figure head.
c. Find out Power of 2 of the Rounded figures under the Power head.
8) Save and close the workbook.
Date Value Function, Workspace Creation and Paste Special
LEARNING OBJECTIVES
Date value function
Workspace creation
Paste special Date-Related Functions
Excel has quite a few functions that work with dates. These functions are accessible by choosing Path: FormulasFunction Library Date & Time.
DATE Function
Returns the sequential Excel date / time serial number that represents a particular date. Syntax: =DATE (Year, Month, Date)
The result will normally be displayed in the format set in regional setting of your computer. By using the number format this format can be changed.
TODAY Function
This function is used to show the current date in a cell. Syntax: =TODAY()
Creation of Workspace
If you frequently work with the same group of workbooks you may need to create a customized workspace. A Workspace allows you to open a group of workbook in one step. Information about the open files, such as their locations, window size, and screen positions, are stored in a workspace file. Therefore, instead of opening individual workbook you can open the workspace file, all the individual workbooks forming a part of the file are opened.
1) To create a Workspace, first open the workbooks you want to include in the workspace
2) Go to View Tab Window Group Save Workspace, the saving dialog box will appear. Enter a name for the Workspace file in the File name box. The default extension for a workspace file is .XLW.
Using the Paste Special Dialog box
For maximum flexibility in what gets pasted, choose Home Clipboard Paste Paste Special to display the Paste Special dialog box (see Figure below). You can also right-click and select Paste Special to display this dialog box. This dialog box has several options, which is explained in the following list.
Data Validation
Excel’s data validation feature enables you to set up certain rules that restrict an user to enter invalid data. For example, you may want to limit data entry in a particular cell to whole numbers between 3000 and 20000. If the user makes an invalid entry, you can display an error message. Excel makes it easy to specify the validation criteria, and you can also use a formula for more complex criteria. Types of Validation Criteria You Can Apply
The Settings tab of the Data Validation dialog box enables you to specify a wide variety of data validation criteria. The following options are available in the Allow drop-down box. Keep in mind that the other controls in the Settings tab vary, depending on your choice in the Allow drop-down box.
Any Value: Selecting this option removes any existing data validation. Note, however, that the input message, if any, still displays if the check box is checked in the Input Message tab.
Whole Number: The user must enter a whole number. You specify a valid range of whole numbers by using the Data drop-down list. For example, you can specify that the entry must be a whole number greater than or equal to 100.
Decimal: The user must enter a number. You specify a valid range of numbers by using the Data drop-down list. For example, you can specify that the entry must be greater than or equal to 0 and less than or equal to 1.
List: The user must choose from a list of entries you provide. In situations where the some few items are to be entered into a column, you can create a drop-down list containing the options from which the user must choose. You can define the values within Source text box using comma separator or a range of data can be defined using an equal sign (=) as prefix.
Date: The user must enter a date. You specify a valid date range by using the Data drop-down list. For example, you can specify that the entered data must be greater than or equal to January 1, 2007, and less than or equal to December 31, 2007.
Time: The user must enter a time. You specify a valid time range by using the Data drop-down list. For example, you can specify that the entered data must be greater than 12:00 p.m.
Text Length: The length of the data (number of characters) is limited. You specify a valid length by using the Data drop-down list. For example, you can specify that the length of the entered data be 1 (a single alphanumeric character).
All: Pastes the cell’s contents, formats, and data validation from the Windows Clipboard.
Formulas: Pastes values and formulas, with no formatting.
Values: Pastes values and the results of formulas (no formatting). The destination for the copy can be a new range or the original range. In the latter case, Excel replaces the original formulas with their current values.
Formats: Copies only the formatting.
Comments: Copies only the cell comments from a cell or range. This option doesn’t copy cell contents or formatting.
Validation: Copies the validation criteria so the same data validation will apply. Data validation is applied by choosing Data ➪ Data Tools ➪ Data Validation.
Custom: To use this option, you must supply a logical formula that determines the validity of the user’s entry (a logical formula returns either True or False). You can enter the formula directly into the Formula control (which appears when you select the Custom option), or you can specify a cell reference that contains a formula. This session contains examples of useful formulas.
The Settings tab of the Data Validation dialog box contains two other check boxes:
Ignore Blank: If checked, blank entries are allowed.
Apply These Changes to All Other Cells with the Same Setting: If checked, the changes you make apply to all other cells that contain the original data validation criteria. Specifying Validation Criteria
To specify the type of data allowable in a cell or range, follow these steps:
1) Select the cell or range.
2) Choose Data tab Data Tools group Data Validation. Excel displays its Data Validation dialog box.
3) Click the Settings tab (see Figure).
4) Choose an option from the drop-down box labeled Allow. The contents of the Data Validation dialog box will change, displaying controls based on your choice. To specify a formula, select Custom.
5) Specify the conditions by using the displayed controls.
6) (Optional) Click the Input Message tab and specify which message to display when a user selects the cell.
7) (Optional) Click the Error Alert tab and specify which error message to display when a user makes an invalid entry.
8) Click Ok.
Class Exercise
1) Open the last saved workbook.
2) Insert a new worksheet after Result sheet. Name the worksheet as ‘Drugs ’ and prepare the following dataset in it.
Allowing only whole no. to selected cells
Setting conditions
Message box Title
Error Message
3) Find out Manufacture date using date function.
4) Change Expiry Date range as text and feed the following dates in the respective cell.
12-Jun-2010; 25-May-2010; 01-Sep-2012; 14-Mar-2009; 11-Nov-2011; 11-Apr-2012; 03-Dec-2009; 01-Sep-2008; 31-Jul-2009; 18-Jan-2012.
5) Days of today column should display the current date.
6) Calculate the number of expiry days as of today in ‘Days Until Expiry’ column.
7) Save and close the workbook.
In this example, we have to check two conditions to offer discount to the customer, one is whether the product is on Special Offer and another one is whether the Order Value is Rs.1000 or above. That can be checked through AND() function [ =AND(B3=”Yes”,C3>=1000)]. Now based on that conditions we have to take decision; whether to offer discount or not. So this above function has to be incorporated with if() function. Thus the final formula is: =IF(AND(B3=”Yes”,C3>=1000),C3*10%,0) Logical Functions
LEARNING OBJECTIVES
Logical Function
IF function
OR Function
Logical Function
Excel’s logical functions are used to perform logical tests, as to reveal whether a statement is true or false. Depending on the outcome of the logical test, a specific result is returned. If Function
This function returns a value depending on the logical test given as an argument of the function. The syntax of the function is: =IF( Logical_test, Result_if_true, Result_if_false).
Logical_test is any value or expression that can be evaluated to true or false. Result_if_true is the value that will return if the Logical_test is true. Result_if_false is the value that will return if the Logical_test is false. Example-1: Simple IF
OR Function
The OR functions checks whether any of the arguments are TRUE. It returns a FALSE only if all the arguments are FALSE. If any argument is TRUE, the function returns TRUE. Syntax: =OR(Logical_test 1,Logical_test 2,……) Example-1:
A handling charge of Rs.50/- is made on all orders paid by Visa or Delta cards. The =OR() function has been used to determine whether the charge needs to be applied.
The figure shows a worksheet with student grades in column B. Formulas in column C use the IF function to return text: either Pass (a score of 65 or higher) or Fail (a score below 65). The formula in cell C2 is: =IF (B2>=65,”Pass”,”Fail”)
NOT Function
NOT function is used to reverse the value of its argument. NOT function accepts a logical value as its argument.
Class Exercise
1) Open the last saved workbook.
2) Create a new worksheet. Rename the sheet as ‘Payroll’ and prepare the following dataset in it. Provide all required formatting and use series concept at employee code.
3) Calculate DA @ 40% of Basic pay.
4) Calculate TA @ 10% of Basic or Rs.1000/- which ever is higher.
5) Provide House for Audit department only and “No” for others.
6) Calculate HRA @ 15% of Basic pay for those who do not get accommodation from company and for other it will be 0.
7) Calculate Gross salary (Basic+DA+TA+HRA).
8) Calculate P.F @ 12.5% of Basic pay.
9) Calculate P.Tax as per slab:
Upto Rs.5000 --- Nil
Rs.5001 – Rs.6000 --- Rs.60/-
Rs.6001 – Rs.10,000 --- Rs.80/-
Rs.10,001 – Rs. 15,000 --- Rs.100/-
Rs.15,001 – Rs.20,000 --- Rs.150/-
Rs.20,001 and above --- Rs.200/-
10) Calculate Net Salary (Gross-P.F-P.Tax)
11) Now replace HRA amount with 20% of Basic whose Basic is more than 15,000 and also who do not posses a house. For other who do not posses house and who’s Basic is below 15,000, don’t replace their HRA.
12) Save and close the workbook.
In this example, any of the two conditions (whether the payment type is Visa or Delta) has to be satisfied to charge Handling Charge of Rs. 50. So, Or() function has to be used within If() function. The final formula is: IF(OR(C2=”Visa”,C2=”Delta”),50,0).
Other Logical Functions, Data Form and Freeze Panes
LEARNING OBJECTIVES
Other Logical Functions
o Sumif
o Countif
o Count
o Isblank
Data Form
Freeze panes Use of Sumif Function
The SUMIF function calculates the total of only those cells that meet a given condition or criteria. The syntax for the SUMIF function is: =SUMIF (Range, Criteria, SumRange)
Range: The range containing the values that determine whether to include a particular cell in the sum.
Criteria: An expression that determines whether to include a particular cell in the sum.
Sum_range: The range that contains the cells you want to sum
Based on the above table, to find out total cost of all Mouse bought:
=SUMIF(B2:C9,"Mouse”,”C2:C9)
COUNT Function
This function counts the number of numeric entries in a list. It will ignore blanks, text and errors. The syntax of this function is: =COUNT(Range1,Range2,Range3... through to Range30)
The following figure evaluates the use of Count Function:
Range
Criteria
Sum Range
COUNTIF Function
The COUNTIF function counts the number of cells in a given range that meet a specific condition. The syntax for the function is:=COUNTIF (Range, Criteria)
Range is used to declare a group of cells from which you want to count those cells, which meet the criteria. Criteria is used to declare the searching matter, it can be a number as well as a value. ISBLANK FUNCTION
This function will determine if there is an entry in a particular cell. Syntax: =ISBLANK(CellToTest)
It can be used when a spreadsheet has blank cells which may cause errors, but which will be filled later as the data is received by the user will be filled later as the data is received by the user. DATA FORM
Many users use Excel to manage lists in which the information is arranged in rows. Excel offers a simple way to work with this type of data through the use of a data entry form that Excel can create automatically.
Data Form command is not available in the Ribbon, to use the data form, you must add it to your Quick Access Toolbar (QAT): Freeze Panes
If you set up a worksheet with row or column headings, these headings will not be visible when you scroll down or to the right. Excel provides a handy solution to this problem: freezing panes. Freezing panes keeps the headings visible while you’re scrolling through the worksheet.
1) To freeze panes, start by moving the cell pointer to the cell below the row that you want to remain visible as you scroll vertically, and to the right of the column that you want to remain visible as you scroll horizontally.
2) Then, Choose View Window Freeze Panes and select the Freeze Panes option from the dropdown list.
3) Excel inserts dark lines to indicate the frozen rows and columns. The frozen row and column remain visible as you scroll throughout the worksheet.
To remove the frozen panes, choose ViewWindow Freeze Panes, and select the Unfreeze Panes option from the drop-down list.
Class Exercise
1) Open your last saved workbook.
2) Go to the Payroll sheet and calculate the following at the end of the dataset:
a. Calculate the Total take home money of Audit department
b. Calculate the Total take home money of Sales department
c. Calculate the gross salary of Media department.
d. Total number of employee in Media department
e. Total number of employee in Acct department
3) Create a new worksheet. Rename the sheet as ‘Installment’ and prepare the following dataset by copying St.Code , Name, Course and Fees from ‘Student List’ sheet.
4) Calculate Outstanding Fees (Fees-Installment), if Date of Installment Paid is mentioned otherwise full Fees amount will appears as outstanding fees. [Hint: Use ISBLANK function]
5) Save and close the workbook.
Financial Functions
LEARNING OBJECTIVES
Financial Function
Loan Calculation
o PV
o FV
o PMT
o PPMT FINANCIAL FUNCTION
Excel is often used to perform calculations involving money, from basic home budgets to complex financial decision making. Consequently, Excel’s function library contains over 50 financial functions that can be used to calculate everything from loan repayments to the future value of an investment. LOAN CALCULATIONS
This section describes how to calculate various components of a loan. Think of a loan as consisting of the following components:
The loan amount
The interest rate
The number of payment periods
The periodic payment amount
If you know any three of these components, you can create a formula to calculate the unknown component. Note: The loan calculations in this section all assume a fixed-rate loan with a fixed term.
This section describes six functions: PMT, PPMT, IPMT, RATE, FV and PV. The PMT Function
The PMT function returns the loan payment (principal plus interest) per period, assuming constant payment amounts and a fixed interest rate.
The syntax for the PMT function is PMT(Rate,Nper,Pv,Fv,Type) Function Argument Description
Rate
The interest rate per period. If the rate is expressed as an annual interest rate, you must divide it by the number of periods.
Nper
The total number of payment periods.
PV
This is the present value, or the loan amount; it is also known as the principal.
FV
The future value after the last payment is made. If you omit FV, it is assumed to be 0. (The future value of a loan, for example, is 0.)
Type
Indicates when payments are due—either 0 (due at the end of the period) or 1 (due at the beginning of the period). If you omit type, it is assumed to be 0.
Let’s take an example as shown below:
You need to find out the monthly payment amount for a Loan of Rs.500,000 with a 14% annual interest rate. The loan has a term of four years (48 months).
The procedure to calculate monthly payment amount are as follows:
1) Place your cursor on Cell B6. Type <=PMT> and press <Ctrl+A> to display function argument dialog box.
2) Fill the following function arguments box: Rate
Cell B3/12 (14%/12). It is divided by 12 as the interest is given in annual basis and the payment is in monthly basis. Nper
Cell B4*12 (4*12). As payment is made monthly for the period of four (4) year. PV
-Cell B2 (-500,000). This will be negative for a loan. FV
Omit FV as it is assumed to be 0. Type
‘0’. It indicate the payment is due at the end of the period.
3) Finally click OK button to display monthly payment amount. This formula returns Rs.13,663.24 (approx) as the EMI.
The PPMT function
The PPMT function returns the principal part of a loan payment for a given period, assuming constant payment amounts and a fixed interest rate.
The syntax for the PPMT function is PPMT(rate,per,nper,pv,fv,type). Function Argument Description
Rate
The interest rate per period. If the rate is expressed as an annual interest rate, you must divide it by the number of periods.
Per
This is the period for which you want to find the principal
Nper
The total number of payment periods.
PV
Present value, the amount you have invested or you have taken as loan.
FV
The future value after the last payment is made. If you omit FV, it is assumed to be 0. (The future value of a loan, for example, is 0.)
Type
Indicates when payments are due—either 0 (due at the end of the period) or 1 (due at the beginning of the period). If you omit type, it is assumed to be 0.
Let’s take an example as shown below:
In the above example, you need to find out the principal amount for the first month of the EMI that you have calculated in the earlier example.
The procedure to calculate Principal amount are as follows:
1) Place your cursor on Cell B8. Type <=PPMT> and press <Ctrl+A> to display function argument dialog box.
2) Fill the following function arguments box as shown below.
Per: Particular number of Installment. Suppose you have total 48 installments, among them you need to know the principal amount of 1st installments. Then type “1” in Per argument box. The period must be less than or equal to nper.
3) Click OK button to get the Principal value (PPMT). This formula returns Rs.7,829.90 (approx) as the principal amount.
The PV function
The PV function returns the present value (that is, the original loan amount) for a loan, given the interest rate, the number of periods, and the periodic payment amount.
The syntax for the PV function is: PV(rate,nper,pmt,fv,type) Function Argument Description
Rate
The interest rate per period. If the rate is expressed as an annual interest rate, you must divide it by the number of periods.
Nper
The total number of payment periods.
PMT
The payment made each period (a constant value that does not change).
FV
The future value after the last payment is made. If you omit FV, it is assumed to be 0. (The future value of a loan, for example, is 0.)
Type
Indicates when payments are due—either 0 (due at the end of the period) or 1 (due at the beginning of the period). If you omit type, it is assumed to be 0.
Let’s take an example as shown below:
You need to find out the original loan amount for four (4) years, which has a monthly payment of Rs.13,663.24.
The procedure to calculate the present value of loan is as follows:
1) Place your cursor on Cell B6. Type <=PV> and press <Ctrl+A> to display function argument dialog box.
2) Fill the following function arguments box as shown below. Rate
Cell B3/12 (14%/12). It is divided by 12 as the interest is given in annual basis and the payment is in monthly basis. Nper
Cell B4*12 (4*12). As payment is made monthly for the period of four (4) year. PMT
-Cell B2 (-13,663.24). It is the amount of the periodic payments FV
Omit FV as it is assumed to be 0. Type
Omit type. It indicates the payment is due at the end of the period.
3) Click OK button to get the present value (PV) of loan. This formula returns Rs.500,000 as the initial loan amount.
The FV function
In financial models the future value of an investment is equal to the present value plus interest or the combined value of all the deposits over the term of the investment plus any interest.
The FV function will return the future value of an investment based on periodic, constant payments and a constant interest rate.
The syntax for the FV function is: FV(rate,nper,pmt,pv,type)
Function Argument Description
Rate
Rate is interest rate per period; if payments are monthly this will be divided by 12.
Nper
Nper is the total number of payments.
PMT
Pmt is the fixed amount deposited each period (assumed to be zero).
PV
PV is the initial deposit or present value (assumed to be zero).
Type
Type is 0 if payment is due at the end of a period or 1 if payment is due at the beginning of a period (the default is 0).
Let’s take an example as shown below:
The above example shows that you are depositing Rs.1000 at the beginning of each month for 5 years with 2.13% quarterly interest. Here you need to find out the matured amount. To do this task you need to use FV function.
The procedure to calculate the future value of loan is as follows:
1) Place your cursor on Cell B6. Type <=FV> and press <Ctrl+A> to display function argument dialog box.
2) Fill the following function arguments box as shown below. Rate
Cell B3/4 (2.13%/4). It is divided by 4 as the interest is given in quarterly basis and the payment is in monthly basis. Nper
Cell B4*12 (5*12). As payment is made monthly for the period of five (5) year. PMT
-Cell B2 (-1,000). It is the amount of the monthly payment. PV
0 ( No initial deposit) Type
1 (payment is due at the beginning of a period)
3) Click OK button to get the future value (FV) of investment. This formula returns Rs.70,849.10 as the matured amount.
Class Exercise
1) Open your last saved workbook and insert a new worksheet after ‘installment’, name the sheet as ‘Financial’.
2) You can get Rs.500,000/- in 3 years time by investing certain amount on monthly basis at 7% p.a. Find out the monthly investment amount. [ Rs.12,522 approx ]
3) If I deposit Rs.1000 at the beginning of each month (starting today) in an account earning 1% per month, how much will I have after two years? [ Ans: 27,243.20 ]
4) If you have deposited Rs.100,000 with compounded interest @ 6.25% per annum for 8 years, find out the return value at the end of 8 Years. [ Rs.162,417 approx ]
5) If the previous deposit follows an MIS (Monthly Income Scheme) then how much amount will you receive at the end of each month? [ Rs.1326 approx ]
6) In every MIS, find out how much amount is paid back as Principal and Interest seperately. Summation of Principal parts would be Rs.100,000 and summation fo Interest parts would be Rs.27,329.56.
7) As per the investment scheme, you have to deposit Rs.835 per month to get Rs.150,000/- at the end of 10 years, find out rate of interest per annum. If you are investing sum of amount to get the same return value after 10 years with same rate of interest per annum, then find out the amount to be deposited now. [ 7.56 (approx) ; Rs.72,379 approx ]
8) Suppose you are going to deposit Rs.2.646 quarterly in a recurring account to get back Rs.1,00,000 after a certain period with 8% interest per annum. Find out how many years are required to get back the amount. [ 7 approx ]
9) Save and close the workbook.
Sorting, Subtotal and Filtering Data
LEARNING OBJECTIVES
Sorting a table
Subtotal
Filtering data Sorting a table
Sorting a table rearranges the rows based on the contents of a particular column. You may want to sort a table to put names in alphabetical order. Or, maybe you want to sort your sales staff by the totals sales made. Excel’s Sort feature helps organize the data in a list. Column fields can be quickly sorted in ascending or descending order. To sort the data in a column, select any cell in the column by which you want to sort.
To sort in ascending order, click the Sort Ascending button on the Data Tab (Data Tab Sort & Filter Tab). To sort in descending order, click the Sort Descending button on the Data Tab. If you want to sort a list by more than one field, you can use the Data Tab Sort & Filter Tab Sort.
After applying sort command the data table will look like as shown below.
Note: You can sort on any number of columns. The trick is to sort the most significant column first and then proceed until the least significant column is sorted. For example, in the real estate table, you may want the list to be sorted by agent. And within each agent’s group, the rows should be sorted by area. And within each area, the rows should be sorted by list price. For this type of sort, first sort by the List Agent column, and then sort by the Area column, and then sort by the List Price column. Figure above shows the table sorted in this manner.
Calculating Subtotals
The most convenient way to create an outline for your worksheet data and to insert subtotals is to use the Subtotal command, which is now situated in the Outline group of the Data tab.
To create automatic subtotals, follow the steps:
1) Sort the data by the column that you want to base your calculation on (this will allow excel to group the data correctly).
2) Click the Subtotal command in the Outline group on the Data tab. The Subtotal dialog box will open.
3) Select the column that you want to base the summary calculations on from the ‘At each Change in’ dropdown list.
4) Select ‘SUM’ function that you want to use the summary calculation from the ‘Use function’ dropdown list.
5) From the ‘Add Subtotal to’ list, select the columns for which you want to include summary calculation.
6) Select or deselect the ‘Replace current subtotals’, ‘Page break between groups’, and ‘Summary below data’ options as appropriate. If you deselect the ‘Summary below data’ option, the subtotals will appear above their respective groups, with the grand total at the top of the data.
7) When you have finished making your selections, click OK. Excel will outline your data according to values in the specified column and will insert the appropriate subtotals and grand total into your worksheet.
Filtering of Data
Many a times, perhaps you need to work with a particular group of records in the database only. A Filter is used to select records that meet specific criteria and temporarily hide all other records. Excel supports mainly two ways to find out specific records by defining criteria: AutoFilter and Advanced Filter. AutoFilter
Use to get the necessary data from a huge database.
1) After selecting the database, click on Filter.
2) Dropdown Menu will come automatically. Start filtering from that menu as per as the criteria.
3) To filter product, click on the product drop lists button to appear a dialog box as shown below. Now deselect all products and select the only Mobile and click on Ok button. (See figure below)
Shows appropriate Subtotals and Grand total.
ADVANCED FILTER
Advanced Filter is identified for its role in searching for specific records meeting a certain criteria. With an advanced filter you can denote the exact criteria to be found. This option helps to find records within database as well as to create another database based on criteria to another location (i.e. Subset of the original database).
Class Exercise
1) Open your last saved worksheet. Go to Student List sheet.
2) Find out the records from the Student List who has taken admission in B.COM course. ( Use Filter)
3) Remove filter from the database.
4) Insert a new worksheet after Student List sheet. Form a database in this worksheet only for B.COM and B.B.A student extracting from Student List database. (Use Advanced filter). Rename the sheet as Extract.
5) Insert a new sheet, name the sheet as “ITEM LIST” and prepare the following dataset.
6) Complete the above table using suitable calculation. Now prepare a data form for the Item List and insert the following records. Don’t put or calculate the values for Amount, Discount Amount and Net Amount field.
7) Go to the Student List sheet and sort the records as per student name in A to Z order.
8) Freeze first two rows and columns of Student Details database.
Filtered data based on Product: Mobile
9) Save and close the workbook.
Charts and Protection
LEARNING OBJECTIVES
Type of charts
Creating chart
Worksheet protection
Workbook protection Creating Charts
A chart is a visual representation of numeric values. A chart is also known as graphs. A chart can be created from the data of a database to provide more visual presentation of the data. Excel’s chart feature provides a variety of charts including Bar, Column, Pie, Area and many more.
Types of Charts
You can create different types of charts. The chart type you choose depends on the kind of data you are trying to chart, and on how you want to present that data. The followings are the major chart types.
Column – Similar to the bar chart; use this chart type to emphasize the difference between items.
Pie – Use this chart type to show the relationship among parts of a whole.
Bar – Use this chart type to compare values at a given point in time.
Line – Use this chart type to emphasize trends and the change of values over time.
Scatter – Similar to a line chart; use this chart type to emphasize the difference between two sets of values.
Area – Similar to the line chart; use this chart type to emphasize the amount to change in values over time. Most of these basic chart types also come in three-dimensional varieties.
Creating a Column or Bar Chart from Data in a Worksheet
Once you have entered the data you want to display as a chart into a worksheet and decided which type of chart you require, you can create it with just a few mouse clicks. Let’s take a database to create column or bar chart.
1) Select Cells A1:B7, Click the drop lists of Column.
2) Go to Insert tabCharts groupClick the drop lists of Column or Bar command to display subtypes.
3) Choose and click the appropriate Sub-type charts. (See figure below)
Creating of Pie Chart from Data in a Worksheet
1) To create a pie chart enters the data into the worksheet as shown below. Let’s create a pie for Wipro.
2) Select Cells A1:A6 and Cells C1:C6 holding Ctrl key.
3) Go to Insert tabCharts groupClick the drop lists of Pie and choose and click the appropriate pie chart type. (Refer to figure below)
4) Click Chart Tools Layout TabClick the drop lists of Data LabelsChoose and click Inside End.
Wipro
2008
2007
2006
2005
2004
Sheet Protection
Excel users protect a sheet for a variety of reasons. One reason is to prevent himself or others from accidentally deleting formulas or other unwanted modifications.
1) To protect a sheet, activate the sheet and choose ReviewChanges Protect Sheet.
2) On clicking Protech Sheet, the following screen appears. Enter a password, although a password is optional. If you enter a password, that password will be required to unprotect the sheet.
3) Turn on the required option and click OK button to display Confirm Password dialog box. Retype the same password for confirmation and click OK button. Finally save the worksheet. Protecting a Workbook
To protect a worksheet’s structure:
1) Choose ReviewChangesProtect Workbook to display the Protect Workbook dialog box
2) In the Protect Workbook dialog box, place a check mark next to Structure.
3) Enter a password and Click OK.
4) Reenter the password again for confirmation and Click OK button.
5) Finally save the workbook.
To unprotect the workbook’s structure, choose ReviewChangesUnprotect Workbook. If the workbook’s structure was protected with a password, you are prompted to enter the password. Feed the correct password to unprotect workbook.
When a workbook’s windows are protected, the user can’t change anything related to the window size or position. For example, if the workbook window is maximized when the windows are protected, the user cannot unmaximize the window. The windows can, however, be zoomed.
To unprotect the workbook’s windows, go to Review TabClick the drop lists of Protect Workbook. Choose and click Protect Structure & Windows to open the following dialog box.
If the workbook’s windows were protected with a password, you are prompted to enter the password. Feed the correct password to unprotect the workbook. Protecting an Excel File
Excel lets you save a workbook with a password. After doing so, whoever tries to open the workbook must enter the password.
To add a password to a workbook:
1) Go to Office button PrepareClick Encrypt Document to open the following dialog box as shown below.
2) Type a password and click OK.
3) Type the password again for confirmation and click OK button.
4) Finally Save and close the workbook.
Now, when you reopen the same excel file, it will ask for a password to open. You need to feed the correct password to open the file as shown below.
Class Exercise
1) Open your last saved worksheet. Go to Installment sheet set an image to display as the background of the sheet.
2) Go to payroll sheet and set first row to repeat at top on each printed page.
3) Insert a new worksheet name “Age group” and prepare the following data set:
4) Produce a Column chart with the above data in the same sheet.
5) Insert a new worksheet name “Trading Status” and create the following database.
6) Produce a Pie chart to get at a glance of trading status for the month of march in the same sheet.
7) Go to the Payroll sheet and protect the entire worksheet. Now try to change any basic salary of any employee.
8) Save and close your workbook with password for opening and modification level.
9) Try to open the workbook without supplying the password.
10) Close your workbook.
Page Setup, Lookup Functions and Splitting Worksheet
LEARNING OBJECTIVES
Page setting for printing
Lookup & reference functions
Splitting worksheet Page Setup Group Options
In Excel, you can print your worksheets just the way they look after you enter the data, or you can enhance the printout using several page layout options. When you select Excel's Page Layout Tab, the Page Layout tab contains several options as shown below.
Margin Option
This option is used to select the margin sizes for the entire worksheet.
1) To set a margin, Click the drop lists of Margins under Page Layout tab and then click Normal, Narrow, or Wide as required.
2) You will notice a dotted vertical and horizontal line, which indicate print area per page. Orientation
This option is used to switch the pages between portrait and landscape layouts.
1) To change the page layout, click the drop lists of Orientation option under Page Layout tab and then Click Portrait (vertical), which is the default, or Landscape (horizontal).
2) You will notice a Landscape layout (horizontal view) as shown below. Size
This option is used to select a paper size for a current section.
1) To select required paper size, click the droplists of Size option under Page Layout tab and then select A4 size page or any other page size as required.
2) Show the view of A4 size page. Print Area
This option is used to mark a specific area of the sheet for the printing.
1) To set a print area, select the range of cells.
2) Click the droplists of Print Area option under Page Layout tab and select set print area.
3) Dotted lines indicate the printable area. See figure below Breaks
This option is used to specify where a new page will begin in the printed copy. Page breaks are inserted above and to the left of the selection.
1) To apply a page break, place your cursor on a particular cell (A15).
2) Then click the droplists of Break option under Page Layout tab and select Insert page break option.
Background
This option is used to select an image to display as the background of the sheet.
1) To set a background image, click Background option under Page Layout tab and then select an image and click Insert button to add image as background. See figure below. Print Title
This option is used to specify rows and columns to repeat on each printed page.
1) To set a print title, click Print Title option under Page Layout tab and then select a row to repeat at top and column to repeat at left as required.
2) Then Click OK. Now if you take a printout, you will notice that the title is getting printed in each page. See figure below.
Previewing Worksheets Before Printing
You can view your Excel 2007 worksheet as it will be printed by clicking the Microsoft Office Button, pointing to Print, and then clicking Print Preview. When you do, Excel 2007 displays the active worksheet in the Print Preview window.
When the Print Preview window opens, it shows the active worksheet as it will be printed with its current settings. In the lower-left corner of the Print Preview window, Excel 2007 indicates how many pages the worksheet will require when printed and the number of the page you are viewing. Lookup & Reference Functions
These functions are useful when user has to extract a single set of data from a long list of data set. VLOOKUP( )
The VLOOKUP function looks up the value in the first column of the lookup table and returns the corresponding value in a specified table column. The lookup table is arranged vertically (which explains the V in the function’s name). The syntax for the VLOOKUP function is:
The VLOOKUP function’s arguments are as follows:
Lookup_Value : The value to be looked up in the first column of the lookup table.
Table_Array : The range that contains the lookup table.
Col_index_num : The column number within the table from which the matching value is returned.
Range_lookup : Optional. If TRUE or omitted, a nearest match is returned. (If an exact
Match is not found, the next largest value that is less than lookup_value is returned.) If FALSE, VLOOKUP will search for an exact match. If VLOOKUP can’t find an exact match, the function returns #N/A.
Tips: If the range_lookup argument is FALSE, the first column of the lookup table need not be in ascending order. If an exact match is not found, the function returns #N/A.
If the lookup_value argument is text, it can include wildcard characters * and ?.
Splitting the Worksheet Window into Panes
Sometime, you may face difficulties in managing large sheets. Problem may arise to view different part of the sheet with in a single window. To solve this problem Excel provides another option for viewing multiple parts of the same worksheet. Choosing View Window Split .It splits the active worksheet into two or four separate panes. The split occurs at the location of the cell pointer. If the cell pointer is in row 1or column A, this command results in a 2-pane split. Otherwise, it gives you four panes. You can use the mouse to drag the individual panes to resize them.
Class Exercise
1. You are having the following database, and you want to find any record in such a way so that when you enter the Salesman Name, his entire details will automatically appear. Configure the blank fields except Salesman name, After configuring the cells enter any salesman name and checks whether respective d-ata for that salesman appears or not:
Example:
Figure above shows a simple worksheet that uses two lookup formulas. This worksheet contains a table of employee data, beginning in row 7. This range is named EmpData. When you enter a last name into cell C2, lookup formulas in D2:E2 retrieve the matching information from the table. The following lookup formulas use the VLOOKUP function:
D2 =VLOOKUP(C2,EmpData,2,FALSE)
E2 =VLOOKUP(C2,EmpData,3,FALSE)
Pivot Table, Database Function and What-if-Analysis
LEARNING OBJECTIVES
Creating Pivot Table
Database Function
Scenario Manager
Goal Seek
Solver Pivot Table
A pivot table is essentially a dynamic summary report generated from a database. The database can reside in a worksheet (in the form of a table) or in an external data file. A pivot table can help transform endless rows and columns of numbers into a meaningful presentation of the data Creating a Pivot Table
If your data is in a worksheet range, select any cell in that range and then choose Insert ➪ Tables ➪ PivotTable, which displays the dialog box shown in
Excel attempts to guess the range, based on the location of the active cell. If you’re creating a pivot table from an external data source, you need to select that option and then click Choose Connection to specify the data source.
Specifying the location for the pivot table
Use the bottom section of the Create PivotTable dialog box to indicate the location for your pivot table. The default location is on a new worksheet, but you can specify any range on any worksheet, including the worksheet that contains the data. Database function DMIN
DMIN function is useful to find out the lowest value of a field based on the criteria provided by you. The syntax of the function is =DMIN (Database,”Field”,”Criteria”). Database is the table from where the result has to be extracted. Field is the field name (Column name) or the position of the column in the table of which minimum value has to be found out and criteria is the range of cells where you specify the condition.
This function is extremely helpful when you have multiple records for a particular item and you need to find the lowest value among them on certain criteria.
DMAX
This function is used to return the largest number of the field or column in a database based on certain criteria. The syntax of the function is: =DMAX(Database,Field,Criteria). Meanings of the arguments are same as DMIN function. Scenario Manager
Scenario Manager allows you to set up several different scenarios. You can use scenarios to predict the outcome of different situations in your spreadsheet. Suppose a company trying to establish a budget for the upcoming year does not know what the sales for the year will be. The sales figures obviously affect the rest of the budget. With the Scenario Manager you can create a “Best Case” and a “Worst Case” scenario. The best-case scenario would show that the budget figures would look like if sales for the year were especially good. The worst-case scenario would show the budget figures would look like if sales were especially poor.
Goal Seek
It determines the value that you need to enter in a single input cell to produce a result that you want in a dependent (formula) cell. The following example shows you how single-cell goal seeking works.
The figure above is showing an investment of Rs.1200 per month @9.5% rate of interest for 5 years can yield a future value of Rs.91706.70. Now the problem with the investor is to get Rs.150000 after the 5 years span. Now he/she wants to know how much should be invested to get that much of amount. This can be solved through Goal Seek option of excel what-if analysis tool. Following are the steps to be followed:
1. Place your cursor on cell B4. Chose Data Data Tools What-If Analysis Goal Seek. Excel displays the Goal Seek dialog box, shown in Figure below:
2. The following information box will appear.
Click OK to accept the result or Click Cancel to restore your worksheet to the form that it had before you chose the Goal Seek command. Solver
The Excel Goal-Seek feature is a useful tool, but it clearly has limitations. It can solve for only one adjustable cell, and it returns only a single solution. Excel’s powerful Solver tool extends this concept by enabling you to do the following:
1) Specify multiple adjustable cells
2) Specify constraints on the values that the adjustable cells can have
3) Generate a solution that maximizes or minimizes a particular worksheet cell
4) Generate multiple solutions to a problem
Set cell B4; the cell you where want to set the value.
Set value 150000 To value field.
By changing cell field should contain the address of the cell you want to change [B1]; i.e, the monthly premium.
Result
Class Exercise - 1
1. Create a new workbook and prepare the following data base with necessary functions.
P_Code
QTY
Rate
Amount
Discount Rate
Discount Amount
Net Amount
P001
42
20
10%
P002
45
15
5%
P003
50
10
5%
P004
100
11
10%
P005
52
21
4%
P006
84
33
3.33%
2. A partnership firm as earned a profit of Rs. 500000 in a particular year. The partners decide to share 45% of book profit amongst themselves in the ratio of their capital. Calculate their share of profit.
Partners
Capital
Share
T.Todi
150000
S.Laha
275000
M.Tewari
650000
3. Complete the following data base on another sheet of the same workbook. Rename the page as Salary.
Code
Basic
Dept
PF
House (Yes/No)
HRA
DA
Gross
PE/001
5000
ACCT
PE/002
4500
ACCT
PE/003
5200
MKT
PE/004
4100
ACCT
PE/005
3200
STCK
PE/006
3550
ADMIN
PE/007
5600
ADMIN
a. PF will be 12% of basic or Rs.600 whichever is higher.
b. Provide House for Accounts dept only
c. Calculate HRA @ 15% for those who do not get accommodation from the company.
d. DA will be 10% of Basic.
e. Calculate the gross salary.
4. Prepare the following layout for salary slip.
This is an auto generated pay slip, means, if any one put the Employee code the entire amount will be generated automatically. Use necessary functions for that.
Class Exercise - 2
5. Complete the following database with necessary functions.
a. If the total amount is more than Rs.1,00,000 then payment date will be 15 days after inward date.
b. Find out the total quantity of all products.
c. Find out the total value of Nokia Mobile
d. Find godown wise total report
e. Create a bar chat for the above data base, based on godown.
f. Find out minimum value product.
g. Highlight the product values which are more than Rs.70,000
h. Which godown is having more quantity of items?
i. Find out maximum value product.
j. Create a filter for the above database.
Class Exercise - 3
Complete the following questions.
1. If you have deposited Rs. 1,00,000 simple interest @ 6.25% p.a, how much you will get after 10 years?
2. If you have deposited Rs. 250000 in simple interest @3.25% p.a, how much you will get after 5 years?
3. Suppose you are going to deposit Rs. 2,646 quaterly in arecurring account to get back Rs. 100000 after certain period with 8% interest p.a. Find out how many years are required to get back the amount?
4. Suppose you have purchased a car for Rs. 5,50,00. You have pad Rs.1,00,000 in cash immediately. Rest of the amount will be paid 10 monthly equal installments with 12.75% interest p.a. Find out the EMI.
5. Present interest rate is 5% p.a. A financial scheme is giving Rs. 50000 at the end of 5 years. How much you have to pay in present to get the said value?
6. Complete the following trading Account for two financial years. Data are given below.
Particulars 2009-10 2010-11 Opening Stock 25000 20000
Purchase
1250000
150000 Purchase Return 36000 40000
Sales
1100000
1350000 Sales return 65000 51000
Wages
50000
52000
Subscribe to:
Posts (Atom)