Microsoft Office
Microsoft Access
Microsoft Excel
Microsoft PowerPoint
Microsoft Word


Learn Excel


It's Easy to Learn

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

1. What is Excel

2) File Extensions Explained

3) Basics of Using Excel

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.

4) Basic Functions

Sum, max, min, average, Count, CountA,Countif, Sumif, Vlookup, Match, Index, And, IF, or, iserror, isblank, isna, isnumber.

5) Ranges in Excel

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 .... )

exceltutorial1

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.

3) Basics of Using Excel

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.

4 ) Basic Excel Functions

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.

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...

 

 

All rights reserved

Are you using MS Office Tools (MS Excel, MS Access, MS Word, MS Outlook),and want to Confront manual work?, then automation is the answer for you,We are Experts in providing Microsoft Office ToolsAutomations Contact us to know more about Automation Automation. Here is Our web page: We also have list of Macros already developed, evaluate if they meet your requirement and use them instantly Tools list at Excel VBA .net (xlvba.net).