Excel A 2 Z - An In depth Tutorial with Simplest logics
3.8 (14 ratings)
662 students enrolled

# Excel A 2 Z - An In depth Tutorial with Simplest logics

Learn Excel in just few hours...
3.8 (14 ratings)
662 students enrolled
Last updated 2/2014
English
English [Auto]
Current price: \$16.99 Original price: \$24.99 Discount: 32% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
• 8.5 hours on-demand video
• Access on mobile and TV
• Certificate of Completion
Training 5 or more people?

What you'll learn
• An In-depth excel tutorial
• From basics of writing a formula to complex functions
• Functions explained in most logical and simplest manner
• Suitable for beginners to advanced users. ideal for users who learn faster when shown.
• Learn different Functions from Microsoft Certified trainer from your own desk.
• Get a certificate of completion
Requirements
• Microsoft Excel 2007/2010/2013
Description

In this Excel A 2 Z - An In-depth video course, Nurture Tech Academy introduces you to the most simplest and logical way to understand Excel environment in detail.

In this Excel training course, you will learn by watching the author actually perform the operation he is instructing on, as he explains step by step how to perform these functions. The training starts with Excel Introduction and its interface, how to write a Formula in Excel and starts with basic formulas like Sum and Count then gradually move to the complex one like IF, IF with And, IF with OR, Nested IF. Then it will cover the whole Lookup Functionality. Financial Functions will be covered there after with Date & Time Functions.

It will also covers the basics of Excel Charts, types of charts in Excel (e.g. Column, Bar, Line, Pie, Pie of Pie, Bar of Pie, Area, bubble chart etc.)

Data Validation, What-if Analysis, Solver Add-in covers afterwards.

Then we will see step by step how to record a Macro. Then it will show you how to use show developer tab, ways to record a Macro, record a Macro, use Relative Reference, Create a Monthly Report Macro, assign a Macro to a shape, how to edit or delete a Macro and finally what are the security settings of a Macro.

By the completion of this online training course, you will be fully versed, and capable of using most of the Formulas & Functions in Microsoft Excel 2007/2010/2013 in a commercial Environment.

Who this course is for:
• Working Professionals & Students
Course content
Expand all 112 lectures 08:41:12
+ An Introduction
2 lectures 04:57

An introduction to course.

Preview 01:42

An introduction to Excel environment.

Introduce yourself to Excel 2007/2010/2013
03:15
+ File Conversion
2 lectures 08:26

In this lecture we will learn how to convert an older version file into a newer one. As some of you are using Excel 2007 ( if you have a round button on the top left corner of your excel window) or Excel 2010/2013 (A file tab instead of a round button on the same position). Now if you are converting any older version file into the newer one then procedure is same for all. Just check out the same in detail in the video.

Converting Older version file to a New version
04:04

To convert a Newer Version file into an older one, "Save As" option is used for the same.

Converting Newer Version file to an Older Version
04:22
+ Using Sum Functions
3 lectures 16:07

In this Video i have tried to explain what is the proper way of writing a formula in Excel, because on the basis of this we will able to use any formula in future.

Preview 06:29

What Does It Do ?This function adds the value of items which match criteria set by the user.

Syntax=SUMIF(RangeOfThingsToBeExamined,CriteriaToBeMatched,RangeOfValuesToTotal)

How to Use Sumif
03:44

SUMIFS is the plural version of SUMIF. Using SUMIFS you can find the sum of values in your data that meet multiple conditions.

Syntax

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Using Sumifs for Multiple Conditions
05:54
+ Using Count Functions
4 lectures 17:46
 What Does It Do ? This function counts the number of numeric or text entries in a list. It will ignore blanks. Syntax =COUNTA(Range1,Range2,Range3... through to Range30)
Preview 05:00
 What Does It Do ? This function counts the number of blank cells in a range. Syntax =COUNTBLANK(RangeToTest)
Count number of blank entries using CountBlank
02:35
 What Does It Do ? This function counts the number of items which match criteria set by the user. Syntax =COUNTIF(RangeOfThingsToBeCounted,CriteriaToBeMatched)
Count with a condition using COUNTIF
04:24
 What Does It Do ? Applies criteria to cells across multiple ranges and counts the number of times all criteria are met. Syntax COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
Count with multiple conditions using COUNTIFS
05:47
+ Using Text functions
3 lectures 23:04

In this Video we will talk about some basic text functions like:

Upper

Lower

Proper

Rept

An Introduction to Basic Text Functions
07:57

In this Video we will talk about some basic text functions like:

Left

Right

Mid

concatenate

Some more Text Functions
07:52
##### Use this method if your names have a delimited format, such as "First_name Last_name" (where the space between First_name and Last_name is the delimiter) or "Last_name, First_name" (where the comma is the delimiter).
Text to Column - A Complex Function
07:15
+ Using Logical functions
4 lectures 28:26
 What Does It Do? This function tests a condition. If the condition is met it is considered to be TRUE. If the condition is not met it is considered as FALSE. Depending upon the result, one of two actions will be carried out. Syntax =IF(Condition,ActionIfTrue,ActionIfFalse)
If Function
04:55

In this we will try to merge two functions in excel i.e. IF and AND. An AND function is like if u are sitting in five exams and if you passed in all the exams then only the final result will be Pass and if you got fail in any one of the exams then you will be considered as Fail.

If with And
05:59

In this we will try to merge two functions in excel i.e. IF and OR. An OR function is like if u are sitting in five exams and if you passed in any of the exams then the final result will be Pass and if you got fail in all of the exams then you will be considered as Fail.

If with Or
06:31

A Nested IF function is when a second IF function is placed inside the first in order to test additional conditions. "Nesting" IF functions increases the flexibility of the function by increasing the number of possible outcomes.

Preview 11:01
+ Understanding Lookup functionality
8 lectures 51:40

In this lecture we will cover 4 types of references. It is really important to know how excel reacts on references. The references available in excel are as follows:-

1. Relative Reference - In this case if you refer a cell address it will not follow that address strictly i.e. it will change if you copy and paste the same on another cell.

2. Absolute Reference - In this case it will follow the same cell address everywhere when we copy and paste the same.

3. Mixed Reference - Under Mixed reference, we can either freeze a column of a row whenever we refer a cell.

4. 3D Reference - Whenever we refer a cell on a different sheet or a different workbook it is known as a 3D reference.

4 Types of References
07:22

You can use the VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the same row of the range. In this lecture we will learn how to use Vlookup with False or Exact Match. Whenever we have values exactly available in our lookup range we can use Vlookup with False. This will help us to get the final result.

Vlookup with False
10:40

In the earlier lecture we have learnt how to use Vlookup with False when we have exact lookup values available but what if, we have some approximate values available for lookup. In that case we can use Vlookup with True. We can either choose True from the dropdown list or we can write 1 in that argument. Both will work fine.

Vlookup with True
05:20

In this lecture we will cover how to use vlookup with Mixed Reference. Let me tell you guys this will help a lot in saving time while working because a number of times we face a situation in which we have to lookup the values from a data range but we have to keep either column or row to be freeze for further references. If you are not clear about how to use Mixed Reference then i'll advice you to please go through with the first lecture again and fully verse with references part and then jump into this lecture.

Vlookup with Mixed References
05:19

With this lecture i assume that you know how to use a logical function like "IF". Let me just give you a brief about IF function that, as per its syntax i.e. (logical_test, [value if true], [value of false]) it judges the condition we give it and on the basis of that it apply the result e.g. if i toss a coin, it will be considered as a "logical_test" , now if i demand heads so this is my condition so if this condition will become true than excel ask us that what we want it to show. It means that i demanded heads so i say that if head comes then this formula will show "You Win" otherwise "You loose" so i'll assign "You Win" to [Value if true] and "You loose" to [Value of false].

So we will club this function with Vlookup and tell excel to lookup values on the basis of some conditions.

Vlookup with IF Function - a nested function
05:18

The Microsoft Excel HLOOKUP function searches for value in the top row of table_array and returns the value in the same column based on the index_number

The syntax for the Microsoft Excel HLOOKUP function is:

```HLOOKUP( value, table_array, index_number, [not_exact_match] )
```

value is the value to search for in the first row of the table_array.

table_array is two or more rows of data that is sorted in ascending order.

index_number is the row number in table_array from which the matching value must be returned. The first row is 1.

not_exact_match is optional. It determines if you are looking for an exact match based on value. Enter FALSE to find an exact match. Enter TRUE to find an approximate match, which means that if an exact match if not found, then the HLOOKUP function will look for the next largest value that is less than value. If this parameter is omitted, HLOOKUP will return an approximate match.

Hlookup - lookup in horizontal way
04:03

In this lecture we will see how to use lookup.

The Microsoft Excel LOOKUP function returns a value from a range (one row or one column) or from an array. There are 2 different syntaxes for the LOOKUP function:

In Syntax #1, the LOOKUP function searches for value in the lookup_range and returns the value in the result_range that is in the same position.

The syntax for the Microsoft Excel LOOKUP function is:

```LOOKUP( value, lookup_range, [result_range] )
```

value is the value to search for in the lookup_range.

lookup_range is a single row or single column of data that is sorted in ascending order. The LOOKUP function searches for value in this range.

result_range is optional. It is a single row or single column of data that is the same size as the lookup_range. The LOOKUP function searches for the value in the lookup_range and returns the value from the same position in the result_range. If this parameter is omitted, the LOOKUP function will return the first column of data.

Lookup - no "V" no "H" only lookup
04:11

When deciding between which vertical formula to use in excel, the majority of Excel experts agree that Index & Match is a better formula than Vlookup. However, many people still resort to using VLOOKUP because it’s a simpler formula. One major driver of this problem is that most people still don’t fully understand the benefits of switching from VLOOKUP, and without such an understanding, they are unwilling to invest the time to learn the more complex formula.

In this lecture i'll explain you the advantage of using Index and Match togather.

Index & Match - the power of two
09:27
+ Date & Time Functions
10 lectures 37:00

In this Video we will try to find out that how excel store time and Date.

The Basics of Date & Time Functions
11:25

Excel stores all dates as integers and all times as decimal fractions. With this system, Excel can add, subtract, or compare dates and times just like any other numbers, and all dates are manipulated by using this system.

In this system, the serial number 1 represents 1/1/1900 12:00:00 a.m. Times are stored as decimal numbers between .0 and .99999, where .0 is 00:00:00 and .99999 is 23:59:59. The date integers and time decimal fractions can be combined to create numbers that have a decimal and an integer portion. For example, the number 32331.06 represents the date and time 7/7/1988 1:26:24 a.m.

Understanding how Excel records and stores dates
04:41

Excel stores all dates as integers and all times as decimal fractions. With this system, Excel can add, subtract, or compare dates and times just like any other numbers, and all dates are manipulated by using this system.

In this system, the serial number 1 represents 1/1/1900 12:00:00 a.m. Times are stored as decimal numbers between .0 and .99999, where .0 is 00:00:00 and .99999 is 23:59:59. The date integers and time decimal fractions can be combined to create numbers that have a decimal and an integer portion. For example, the number 32331.06 represents the date and time 7/7/1988 1:26:24 a.m.

Understanding how Excel records and stores time
01:39

Excel stores all dates as integers and all times as decimal fractions. With this system, Excel can add, subtract, or compare dates and times just like any other numbers, and all dates are manipulated by using this system.

In this system, the serial number 1 represents 1/1/1900 12:00:00 a.m. Times are stored as decimal numbers between .0 and .99999, where .0 is 00:00:00 and .99999 is 23:59:59. The date integers and time decimal fractions can be combined to create numbers that have a decimal and an integer portion. For example, the number 32331.06 represents the date and time 7/7/1988 1:26:24 a.m.

Looking at standard date time entry options and acceptable alternatives
03:07

Excel stores all dates as integers and all times as decimal fractions. With this system, Excel can add, subtract, or compare dates and times just like any other numbers, and all dates are manipulated by using this system.

In this system, the serial number 1 represents 1/1/1900 12:00:00 a.m. Times are stored as decimal numbers between .0 and .99999, where .0 is 00:00:00 and .99999 is 23:59:59. The date integers and time decimal fractions can be combined to create numbers that have a decimal and an integer portion. For example, the number 32331.06 represents the date and time 7/7/1988 1:26:24 a.m.

Using instant date time entry
02:04

The TODAY function returns the serial number of today's date based on your system clock and does not include the time. TheNOW function returns the serial number of today's date and includes the time.

##### How Dates Are Sorted Based on Serial Number

In Excel, dates are sorted based on the serial number of the date, instead of on the displayed number. Therefore, when you sort dates in Excel, you may not receive the results you expect.

For example, if you sort a series of dates that are displayed in the mmmm date format (so that only the month is displayed), the months are not sorted alphabetically. Instead, the dates are sorted based on their underlying date serial number.

Using TODAY and NOW functions for dynamic date time entry
03:24

Instead of entering data manually on a worksheet, you can use the Auto Fill feature to fill cells with data that follows a pattern or that is based on data in other cells.

Using Auto Fill to enter date and time series
04:02

Using Keyboard shortcuts will help us in dealing with Date & Time functions more gracefully.

Exploring keystroke shortcuts
01:40

Formatting Time for Hours will really help in entering Time in excel cells.

Formatting time for hours over 24
02:13

One can easily customize the Date formatting as per our individual requirement very easily.

Creating custom date formatting
02:45
+ Using Financial Functions
9 lectures 40:51

The Excel PMT function computes the periodic payments for a loan, assuming constant payments and a constant interest rate. The syntax of the PMT function is:

`PMT(rate,#per,pv,[fv],[type])`

Calculate an Installment of a loan using PMT Function
03:40

If you want, you can use the IPMT or the PPMT function to compute the amount of interest paid each month toward the loan and the amount of the balance paid down each month (called the payment on the principal).

To determine the interest paid each month, use the IPMT function. The syntax of the function is:

`IPMT(rate, per, #per, pv, [fv],[type])`

Except for the per argument, the arguments for the IPMT function are the same as for the PMT function. The perargument indicates the period number for which you're computing the interest.

Calculate Interest per period using IPMT
03:08

Similarly, to determine the amount paid toward the principal each month, use the PPMT function. The syntax of the PPMT function is:

`PPMT(rate, per, #per, pv, fv, type)`

Calculate Principal per period using PPMT
04:08

Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value.

##### Syntax
```RATE(nper, pmt, pv, [fv], [type], [guess])
```
Calculate Rate of Interest if not known
02:26

Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.

##### Syntax
```NPER(rate,pmt,pv,[fv],[type])
```
Calculate the term of loan using NPER Function
03:05

Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender.

##### Syntax
```PV(rate, nper, pmt, [fv], [type])
```
Calculate the Present value using PV
02:24

In this we will prepare a Loan table. In the Loan table we will able to determine that in which period how much Principal and how much interest we have paid and how much principal left for rest of the months.

Create a Loan Table
12:34

SLN Method:

 What Does It Do ? This function calculates the Straight Line Depreciation of an item. (Also known as Fixed Instalment method). The Straight Line Depreciation is how much the value of an item reduced during a specific period of time. The result is a uniform depreciation value. An example would be if you bought a new car for £20,000, then kept it for 6 years. At the end of your ownership you sell the car for £8,000. The difference between the original and the trade in price is £20,000 - £8,000 which is £12,000. Because you owned the car for 6 years, the SLN is calculated as £12,000 / 6 which is £2,000. Syntax =SLN(OriginalCost,SellingPrice,LengthOfOwnership) The LengthOfOwnership can be any time period, days, months or years. However, the SLN which is calculated will, be for that time, specifying 2 years ownership as 24 months will give an SLN per month.

DB Method:

 What Does It Do ? This function calculates deprecation based upon a fixed percentage. The first year is depreciated by the fixed percentage. The second year uses the same percentage, but uses the original value of the item less the first years depreciation. Any subsequent years use the same percentage, using the original value of the item less the depreciation of the previous years. The percentage used in the depreciation is not set by the user, the function calculates the necessary percentage, which will be vary based upon the values inputted by the user. An additional feature of this function is the ability to take into account when the item was originally purchased. If the item was purchased part way through the financial year, the first years depreciation will be based on the remaining part of the year. Syntax =DB(PurchasePrice,SalvageValue,Life,PeriodToCalculate,FirstYearMonth) The FirstYearMonth is the month in which the item was purchased during the first financial year. This is an optional value, if it not used the function will assume 12 as the value.
Calculate Depreciation in Excel
05:23

These miscellaneous functions includes :

MIN

MAX

SMALL

LARGE

Misc. Functions
04:03
+ Sort & Filter Functions
5 lectures 39:43

You can sort data by text (A to Z or Z to A), numbers (smallest to largest or largest to smallest), and dates and times (oldest to newest and newest to oldest) in one or more columns. You can also sort by a custom list (such as Large, Medium, and Small) or by format, including cell color, font color, or icon set. Most sort operations are column sorts, but you can also sort by rows.

Sort Data as per your requirement (Custom Sort)
09:07

You might sort by more than one column or row when you have data that you want to group by the same value in one column or row, and then sort another column or row within that group of equal values. For example, if you have a Department and Employee column, you can first sort by Department (to group all the employees in the same department together), and then sort by name (to put the names in alphabetical order within each department). You can sort by up to 64 columns.

Sort data by Column or Row (with different levels)
08:03

Filtering data is a quick and easy way to find and work with a subset of data in a range of cells or in a table column. Filtered data displays only the rows that meet criteria that you specify and hides rows that you do not want to display. You can also filter by more than one column. Filters are additive, which means that each additional filter is based on the current filter and further reduces the subset of data. You can create two types of filters: by a list of values or by criteria.

Filter Data
05:07

Filtering data by conditions will help us to filter our data in any possible way and come up with a piece of data very quickly.

Filter Data with Conditions
06:11

When you use the Advanced Filter, you need to enter the criteria on the worksheet. Create a Criteria range above your data set. Use the same column headers. Be sure there's at least one blank row between your Criteria range and data set.

Filter Data with Complex conditions (ADVANCED FILTER)
11:15