Basic Excel Tutorial:
Audience:
This tutorial is targetted for beginners, covers basics of Excel, intermediate users can glance over you may find some of content useful.
What we are covering in this Lesson
Selecting cells, selecting all cells, Font Colors, cell back color, resizing cells, Inserting and deleting columns, inserting and deleting cells, Setting Cell Borders, Text Alignment within cells, Merging Cells, Formatting font within cells (Bold, italic etc), Wrap text within cells, fit to cell, Setting data type value format, Text orientation within cell, indentation of text within cells.
Sum, max, min, average, Count, CountA,Countif, Sumif, Vlookup, Match, Index, And, IF, or, iserror, isblank, isna, isnumber.
Why to use Range, how to create Range, delete range, change range reference, using names manager.
6) Data manipulation
Data validation, Sort data, filter data, Import data from external source (text file, web, Access)
7) Creating Charts, pivot tables
8) Paste special
9) Security
Protecting worksheet, protecting selected cells only
1. What is MS Excel, When to use Excel
MS Excel is spread sheet solution, we use MS Excel Extensively to manage and maintain financial data, due to its Rich capability to provide vast financial formulas and functions, Rich formatting features, Rich data organizing capabilities.
Data is organized into rows and columns in two dimensions, we refer each cell with a row and column,
Rows are identified by numbers (Row 1, Row 2, .... ) and columns by Letter/s (A, B, C, D .... )
Excersize: Open Excel, Create a blank Excel file and Save it on your desktop with name "Learning.xlsx"2) File Extensions: It is very importent that we understand file extensions used in MS Excel.
2003 Excel | .xls : Extensively used format, when using this file extension, user was not aware that received file with this extension has any macros or not. |
.xla : Add-in | |
.xll : Linked library extension | |
2007 and later: There are various other Extensions for 2007 and later version click here for more details |
xlsx: This file extension confirms that the file is only Excel file no macros in it. |
xlsm: Excel file with macro, care ful when opening file with xlsm, open only if you know that source is known to be free from threat. |
I created a video to show this part of Learning, it is easy by video for this lesson, watch it carefully and practice along with video.
Before learning about functions, let us learn about how to use function within Excel.
As shown in the picture below, select a cell where you wanted to implement formula, enter = in the cell and excel is smart enough to show you list of formulas.
Image 1
Sum
As the name indicates, this function sums up all the numeric values in the specified range
=SUM( number1, [number2, ... number_n] )
Example:
= SUM(1,2,4,1099,98)
= SUM(D4:D7) ' This function here will sum up all values in Cells from D4 to D7
That is equal to 5000+6500+2566+15000 = 29066
Average
=Average(1,4,45,5) will return the average of(1+4+45+5)/4 = 13.75
= Average(D4:D7) will calculate and return average of values in all the cells from D4 to D7
MAX
=Max(1,4,45,5) will return maximum value in the list = 45
=Max(D4:D7) = 15000 <- Referring to "image 1" above
Min
=Min(1,4,45,5) will return minimum of values in the list = 1
=Min(D4:D7) = 5000 <- Referring to "image 1" above
Count
=Count(Value1, Value2, ...) Returns the count of numbers
=Count(C4:C7) will return value 0 [Referring to image1 above.]
=Count(D4:D7) will return 4 [Referring to image1 above.]
Note: Returns the count of numbers only.
CountA
=CountA(value1..[value2,value3...]) returns the count of nonblank values in cells
=Count(C4:C7) will return 4
similarly =Count(D4:D7) will return 4 [Referring to image1 above.]
Sumif
=Sumif(range,criteria,[sumRange])
SumIf Function has 3 parameters
1. Range to check for criteria - Required to specify
2. Criteria - Required to specify
3. SumRange - Optional, if value for this parameter is not specified then "Range to check criterial" cells will be summed up after meeting criteria range.
[Referring to image1 above.]
if we need to calculate total (sum) of the housing allowance for all the employees having base salary of more than $5000, here is the formula to calculate it
=SumIf(D4:D7,">5000",F4:F7)
Answer you will get is 1625+3750 = 5375
CountIF
CountIF(range, Criteria)
CountIf has two parameters
1. Range to count for
2. Criteria to check criteria to include cells to count numbers
=CountIf(D4:D7,">5000") will return 2, there are only two values with more than 5000 base salary.
Note: Unlike in Count and CountA, this function counts irrespective of numeric or alpha numeric cells, it only checks to meet criteria.
Vlookup
=Vlookup(lookup_Value,TableArray,ColumnIndexNumberInSpecifiedTableArray,MatchType)
lookup_Value: Value to loook in specified table array FIRST COLUMN
TableArray: Table array to look for value and to return value
ColumnIndexNumberInSpecifiedTableArray: Which column value to return if lookup value is found in FIRST COLUMN of specified table.
MatchType: True or False (False = Exact match, True= Closest match)
----
Vlookup is Most used function in Excel, most popular as well
Why Vlookup is used, to explain this, for example
If you want to get Employee base salary in Cell D1 [referrig to image1]
By Entering Employee name in Cell C1
You will have to enter the following formula in Cell D1.
=Vlookup(C1,C4:D7,2,False)
Work in progress...
http://www.xlvba.net All rights reserved