• LOGIN
    • No products in the basket.
52 STUDENTS ENROLLED
  • Profile photo of Pankaj
  • Profile photo of PRAKASH
  • Profile photo of MOHIT
  • Profile photo of Prakash
  • Profile photo of Shivani Gawade
  • Profile photo of Bipul

Course Curriculum

Section 01: An Introduction
Introduce yourself to Excel 2007/2010/2013 00:03:15
An introduction to Excel environment.
Downloads | Exercise Files 00:00:00
Section 02: File Conversion
Converting Older version file to a New versionFREE 00:04:04
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 Newer Version file to an Older Version 00:04:22
To convert a Newer Version file into an older one, "Save As" option is used for the same.
Section 03: Using Sum Functions
How to write a Formula in Excel 00:06:29
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.
How to Use Sumif 00:03:44
What Does It Do ?This function adds the value of items which match criteria set by the user.Syntax=SUMIF(RangeOfThingsToBeExamined,CriteriaToBeMatched,RangeOfValuesToTotal)
Using Sumifs for Multiple ConditionsFREE 00:05:54
SUMIFS is the plural version of SUMIF. Using SUMIFS you can find the sum of values in your data that meet multiple conditions.SyntaxSUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Section 04: Using Count Functions
Count text entries using COUNTA 00:05:00
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)
Count number of blank entries using CountBlankFREE 00:02:35
What Does It Do ? This function counts the number of blank cells in a range. Syntax =COUNTBLANK(RangeToTest)
Count with a condition using COUNTIF 00:04:24
What Does It Do ? This function counts the number of items which match criteria set by the user. Syntax =COUNTIF(RangeOfThingsToBeCounted,CriteriaToBeMatched)
Count with multiple conditions using COUNTIFS 00:05:47
What Does It Do ? Applies criteria to cells across multiple ranges and counts the number of times all criteria are met.SyntaxCOUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
Section 05: Using Text functions
An Introduction to Basic Text Functions 00:07:57
In this Video we will talk about some basic text functions like:UpperLowerProperRept
Some more Text Functions 00:07:52
In this Video we will talk about some basic text functions like:LeftRightMidconcatenate
Text to Column – A Complex Function 00:07:15
Split content based on a delimiterUse 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).
Section 06: Using Logical functions
If Function 00:04:55
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 with AndFREE 00:05:59
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 Or 00:06:31
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.
Nested If 00:11:01
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.
Section 07: Understanding Lookup functionality
4 Types of References 00:07:22
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.
Vlookup with FalseFREE 00:10:40
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 True 00:05:20
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 Mixed References 00:05:19
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 IF Function – a nested function 00:05:18
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.
Hlookup – lookup in horizontal way 00:04:03
e 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_numberThe 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.
Lookup – no “V” no “H” only lookup 00:04:11
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.
Index & Match – the power of two 00:09:27
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 together.
Section 08: Date & Time Functions
The Basics of Date & Time Functions 00:11:25
In this Video we will try to find out that how excel store time and Date.
Understanding how Excel records and stores dates 00: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 timeFREE 00: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 00: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 00:02:04
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 TODAY and NOW functions for dynamic date time entry 00:03:24
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 NumberIn 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 Auto Fill to enter date and time series 00:04:02
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.
Exploring keystroke shortcuts 00:01:40
Using Keyboard shortcuts will help us in dealing with Date & Time functions more gracefully.
Formatting time for hours over 24 00:02:13
Formatting Time for Hours will really help in entering Time in excel cells.
Creating custom date formatting 00:02:45
One can easily customize the Date formatting as per our individual requirement very easily.
Section 09: Using Financial Functions
Calculate an Installment of a loan using PMT Function 00:03:40
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 Interest per period using IPMT 00:03:08
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 Principal per period using PPMT 00:04: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 Rate of Interest if not known 00:02:26
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.SyntaxRATE(nper, pmt, pv, [fv], [type], [guess])
Calculate the term of loan using NPER Function 00:03:05
Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.SyntaxNPER(rate,pmt,pv,[fv],[type])
Calculate the Present value using PV 00:02:24
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.SyntaxPV(rate, nper, pmt, [fv], [type])
Create a Loan Table 00:12:34
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.
Calculate Depreciation in Excel 00:05:23
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 specificperiod 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 ownershipas 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 lessthe first years depreciation.Any subsequent years use the same percentage, using the original value of the item lessthe depreciation of the previous years.The percentage used in the depreciation is not set by the user, the function calculatesthe 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 wasoriginally purchased.If the item was purchased part way through the financial year, the first years depreciationwill 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 thefirst financial year. This is an optional value, if it not used the function will assume 12 as the value.
Misc. Functions 00:04:03
These miscellaneous functions includes :MINMAXSMALLLARGE
Section 10: Sort & Filter Functions
Sort Data as per your requirement (Custom Sort) 00:09:07
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 by Column or Row (with different levels) 00:08:03
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.
Filter Data 00:05:07
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 with Conditions 00:06:11
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 Complex conditions (ADVANCED FILTER) 00:11:15
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.
Section 11: Excel Charts - In depth
First thing First – How to Create a Chart 00:02:46
Charts are used to display series of numeric data in a graphical format to make it easier to understand large quantities of data and the relationship between different series of data.To create a chart in Excel, you start by entering the numeric data for the chart on worksheet. Then you can plot that data into a chart by selecting the chart type that you want to use on the Office Fluent Ribbon (Insert tab,Charts group).There are two types of charts in Excel.1. Embedded chart2. Chart sheet
Play with Design & Layout of Chart 00:02:45
Design & layout is all about the cosmetic changes you make to the chart to make it more presentable in front of the spectators.After you create a chart, you can instantly change its look. Instead of manually adding or changing chart elements or formatting the chart, you can quickly apply a predefined layout and style to your chart. Excel provides a variety of useful predefined layouts and styles (or quick layouts and quick styles) that you can select from, but you can customize a layout or style as needed by manually changing the layout and format of individual chart elements.
Link a Chart Title 00:02:52
You can also link chart and axis titles to corresponding text in worksheet cells by creating a reference to those cells. Linked titles are automatically updated in the chart when you change the corresponding text on the worksheet.This feature is really helpful when we create Excel dashboards in excel to make dynamic we have to link the tile and axis title to make is work properly.
How to Show Axis Titles 00:02:33
To make a chart easier to understand, you can add title, such as a chart title and axis titles, to any type of chart. Axis titles are typically available for all axes that can be displayed in a chart, including depth (series) axes in 3-D charts. Some chart types (such as radar charts) have axes, but they cannot display axis titles. Chart types that do not have axes (such as pie and doughnut charts) cannot display axis titles either.
How to show Data lables 00:02:11
To quickly identify a data series in a chart, you can add data lables to the data points of the chart. By default, the data labels are linked to values on the worksheet, and they update automatically when changes are made to these values.
Show data with chart in Data Table 00:02:02
You can also show a data table for a line chart, area chart, column chart, or bar chart. A data table displays the values that are presented in the chart in a grid at the bottom of the chart. A data table can also include the legend keys.
Column Chart 00:02:03
A column chart shows data changes over a period of time or illustrates comparisons among items. Column charts have the following chart sub-types:Clustered Column This type of chart compares values across categories. It is also available with a 3-D visual effect. As shown in the following chart, categories are organized horizontally, and values vertically, to emphasize variation over time. Stacked Column This type of chart shows the relationship of individual items to the whole, comparing the contribution of each value to a total across categories. It is also available with a 3-D visual effect. 100% Stacked Column This type of chart compares the percentage each value contributes to a total across categories. It is also available with a 3-D visual effect. 3-D Column This type of chart compares data points along two axes. For example, in the following 3-D chart, you can compare four quarters of sales performance in Europe with the performance of two other divisions.
Bar Chart 00:02:33
A bar chart illustrates comparisons among individual items. Bar charts have the following chart sub-types:Clustered Bar This type of chart compares values across categories. It is also available with a 3-D visual effect. In the following chart, categories are organized vertically, and values horizontally, to place focus on comparing the values. Stacked Bar This type of chart show the relationship of individual items to the whole. It is also available with a 3-D visual effect. 100 % Stacked Bar This type of chart compares the percentage each value contributes to a total across categories. It is also available with a 3-D visual effect.
Line Chart 1 00:03:04
A line chart shows trends in data at equal intervals. Line charts have the following chart sub-types:Line This type of chart displays trends over time or categories. It is also available with markers displayed at each data value.
Line Chart 2 00:02:42
We can reduce the unwanted axis gap by formatting the axis through format axis title.
Line Chart with Log Scale 00:02:07
When you create a graph in Excel, by default any numerical axis will use the linear scale. This means that each tick on the axis is the same number of units away from the adjacent ticks. When you have a set of numbers that has both very small numbers and very large numbers, a linear scale may not display the information usefully. To fix this issue, you can change an axis on an Excel graph to use a logarithmic scale, which has each tick on the axis represents an order of magnitude over the initial tick mark. For example, if the first tick represents 10, the second tick would be 10 to the second power, or 100, and the third tick would be 10 to the third power, or 1,000.
Area Chart 00:01:22
An area chart emphasizes the magnitude of change over time. Area charts have the following chart sub-types:Area This type of chart displays the trend of values over time or categories. It is also available with a 3-D visual effect. By displaying the sum of the plotted values, an area chart also shows the relationship of parts to a whole. For example, the following area chart emphasizes increased sales in Washington and illustrates the contribution of each state to total sales. Stacked Area This type of chart displays the trend of the contribution of each value over time or categories. It is also available with a 3-D visual effect. 100% Stacked Area This chart type displays the trend of the percentage each value contributes over time or categories. It is also available with a 3-D visual effect.
Pie Chart 00:02:34
A pie chart shows the size of items that make up a data series, proportional to the sum of the items. It always shows only one data series and is useful when you want to emphasize a significant element in the data.
Pie of Pie or Bar of Pie 00:02:33
Pie of Pie This is a pie chart with user-defined values extracted and combined into a second pie. For example, to make small slices easier to see, you can group them together as one item in a pie chart and then break down that item in a smaller pie or bar chart next to the main chart.Bar of Pie This is a pie chart with user-defined values extracted and combined into a stacked bar.
Bubble Chart 00:01:20
A bubble chart is a type of xy (scatter) chart. It compares sets of three values and can be displayed with a 3-D visual effect. The size of the bubble, or data Marker, indicates the value of a third variable. To arrange your data for a bubble chart, place the x values in one row or column, and enter corresponding y values and bubble sizes in the adjacent rows or columns.
Selecting Chart elements 00:05:16
You can use the mouse to select chart elements on a chart sheet or in an embedded chart. However, if you are not sure where a specific element is located in the chart, you can select it in the Chart Objects box on the Chart toolbar. You can also select chart elements by using the keyboard.
Use of Format Dialog box 00:03:06
Although the Chart Tools ribbon is full of cool things you can do to your chart, sometimes you might want more control. In earlier versions of Office, advanced formatting options were buried deeply in hard to find, complex dialog boxes. Now these options are available in clean, shiny, new task panes. Select the chart element (for example, data series, axes, or titles), right-click it, and click Format .
Modifying Chart & Plot area 00:04:20
The plot area in a chart or graph in spreadsheet programs such as Excel refers to that area of the chart that graphically displays the data being charted.
Modifying Data Series 00:02:04
After you create a chart, you might have to change its source data on the worksheet. To incorporate these changes in the chart, Microsoft Office Excel provides various ways to update a chart. You can instantly update a chart with changed values, or you can dynamically change the underlying source data. You can also update a chart by adding, changing, or removing data.
How to handle missing Data 00:03:57
When you create a line chart in Excel, missing data points (blank cells) aren’t plotted, which leaves gaps in the chart. Excel provides two other ways of handling missing data:* Treat the missing data as zero.* Interpolate the data by connecting the line between the nonmissing data points.
Modifying 3-D Charts 00:02:57
To make a 3-D chart (a chart type that displays data on a third, depth axis, such as a 3-D column, 3-D line, or 3-D surface chart) easier to read, you can change the 3-D format, rotation, and scaling of the chart.You can change the plotting order of data series so that large 3-D data markers do not block smaller ones.
Creating Combination of Charts 00:03:01
A combination chart uses two or more chart types to emphasize that the chart contains different kinds of information.
Creating Chart Templates 00:02:55
If you want to create another chart like the one that you just created, you can save the chart as a template that you can use as the basis for other similar charts.
Creating Picture Charts 00:02:11
You can enhance a chart by inserting a picture (such as a business logo) in the chart area. Or you can use a picture fill to draw attention to specific chart elements such as the chart area, the plot area, the data marker (in column, bar, area, bubble, 3-D line, and filled radar charts), the legend (in 2-D and 3-D charts), or the 3-D walls and floor of a chart.You can also copy a picture to a chart or to chart elements such as data markers in 2-D line charts, scatter charts, or an unfilled radar chart. If a picture is no longer needed, you can remove it.
Section 12: Consolidate Data from different Workbooks/Worksheets
Data Consolidation Function 00:08:37
To summarize and report results from separate worksheets, you can consolidate data from each separate worksheet into a master worksheet. The worksheets can be in the same workbook as the master worksheet or in other workbooks. When you consolidate data, you are assembling data so that you can more easily update and aggregate it on a regular or ad hoc basis.
Section 13: Data Validation & Protection
Data Validation 00:20:07
Data validation is an Excel feature that you can use to define restrictions on what data can or should be entered in a cell. You can configure data validation to prevent users from entering data that is not valid. If you prefer, you can allow users to enter invalid data but warn them when they try to type it in the cell. You can also provide messages to define what input you expect for the cell, and instructions to help users correct any errors.
Protect Worksheet 00:03:10
To prevent a user from accidentally or deliberately changing, moving, or deleting important data from a worksheet or workbook, you can protect certain worksheet or workbook elements, with or without a password. You can remove the protection from a worksheet as needed.By default, when you protect a worksheet, all the cells on the worksheet are locked, and users cannot make any changes to a locked cell. For example, they cannot insert, modify, delete, or format data in a locked cell. However, you can specify which elements users will be able to change when you protect the worksheet.Hiding, locking, and protecting workbook and worksheet elements is not intended to help secure or protect any confidential information that you keep in a workbook. It only helps obscure data or formulas that might confuse other users and prevents them from viewing or making changes to that data.Excel does not encrypt data that is hidden or locked in a workbook. To help keep confidential data confidential, you may want to limit access to workbooks that contain such information by storing them in a location that is available only to authorized users.Before you protect a worksheet, you can unlock the ranges that you want users to be able to change or enter data in. You can unlock cells for all users or for specific users.
Section 14: Pivot Table
Understanding Pivot Table 00:10:42
Being able to analyze all the data in your worksheet can help you make better business decisions. But sometimes it’s hard to know where to start, especially when you have a lot of data. Excel can help you by recommending and then automatically creating PivotTables, which are a great way to summarize, analyze, explore, and present your data.
Pivot Table Query 1 00:03:04
Please follow the video as we will solve a query through Pivot Table.
Pivot Table Query 2 00:03:24
Please follow the video as we will solve a query through Pivot Table.
Pivot Table Query 3 00:02:44
Please follow the video as we will solve a query through Pivot Table.
Pivot Table Query 4 00:04:18
Please follow the video as we will solve a query through Pivot Table.
Section 15: What If Analysis & Solver Add-In
Goal Seek 00:07:35
Goal Seek is part of a suite of commands sometimes called what-if-analysis tools. When you know the desired result of a single formula but not the input value the formula needs to determine the result, you can use the Goal Seek feature available by clicking Goal Seek on the Tools menu. When goal seeking, Microsoft Excel varies the value in one specific cell until a formula that's dependent on that cell returns the result you want.
One Way Data Table 00:05:12
A data table is a range of cells that shows how changing one or two variables in your formulas will affect the results of those formulas. Data tables provide a shortcut for calculating multiple results in one operation and a way to view and compare the results of all the different variations together on your worksheet.A one-variable data table has input values that are listed either down a column (column-oriented) or across a row (row-oriented). Formulas that are used in a one-variable data table must refer to only one input cell.
Two way Data Table 00:06:12
A two-variable data table uses a formula that contains two lists of input values. The formula must refer to two different input cells.
Scenario Manager 00:09:06
Although the Goal Seek and Solver commands are extremely useful, if you run several forecasts you can quickly forget the results for each forecast. More important, you have no real way to compare the results of the Goal Seek and Solver commands. Each time you change the data, the previous solution is lost. To address this limitation, the Scenario Manager helps you keep track of multiple what-if models. Using the Scenarios command on the Tools menu, you can create new forecasting scenarios, view existing scenarios, run scenario management commands, and display consolidated scenario reports. We’ll show you each technique in this section.
Solver Add-in 00:11:01
Excel includes a tool called solver that uses techniques from the operations research to find optimal solutions for all kind of decision problems.
Section 16: Array Formula
How to apply an Array Formula 00:07:30
An array formula is a formula that can perform multiple calculations on one or more of the items in an array. Array formulas can return either multiple results or a single result. For example, you can place an array formula in a range of cells and calculate a column or row of subtotals. You can also place a formula in a single cell and calculate a single amount. An array formula that resides in multiple cells is called (logically enough) a multi-cell formula, and an array formula that resides in a single cell is called a single-cell formula.
Array with Numbers 00:04:55
Using array formula with numbers
Array with Text 00:03:02
Using array formula with Text.
Average with Array 00:04:26
Calculating average with Array
Section 17: Macro & VBA Intro
What is a Macro 00:04:26
The literal meaning of Macro is - on large scale, but do not confuse this with that meaning, in Excel, Macro means recording your actions and to play those recorded action in future by hitting the specified keystroke on your keyboard or toolbar button or an icon in a spreadsheet. It means that whenever you have some repetitive work to perform in Excel e.g. a daily, weekly, bi-weekly, monthly, quarterly report etc. then while preparing that report for the first time you can just start the recoding of the macro and start doing your work to prepare the report and after finishing the same you can just stop the recording and you are done….. So whenever in future, if you want to create that report again just hit the keystroke you assigned to that macro and Excel will take from few seconds to few minutes to prepare that report which you have created in hours.One of the most beautiful features of Macro recording is that while recording is turned ON and if you leave your computer for another hour or two or whatever time, Excel will not record anything, because as I already mentioned earlier that Macro will record only your actions.
Show developer Tab 00:01:40
Excel 2007 users –-Click the Microsoft Office Button and then click Excel Options.-In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.Excel 2010/2013 users –-Click the File tab-Click Options, and then click Customize Ribbon-In the Customize Ribbon category, in the Main Tabs list, select the Developer check box, and then click OK
3Ways to record a macro 00:01:44
Record your first Macro 00:10:10
By going through from any of the above ways you will see a “Record Macro” dialog box on your screen. You can see in the dialog box the first thing required to fill in is “Macro Name”. Let me tell you guys that before giving any name to your Macro just keep in mind few things that name can not contain any spaces or any special character. You even can’t give a name which starts with a number but yes after starting a name with an alphabet you can use numbers. Just try to write a name which is crisp and meaningful. So let’s say we want to record a Macro that will write our name in an active cell. So, let’s give this Macro a name as “Name1”.Now we need to provide a shortcut to this Macro. Now again few things to take care of before you choose any shortcut, you can see that in the dialog box it is written as “Ctrl + “i.e. if you write any alphabet in the box, that alphabet with control key will be the shortcut for this Macro. But keep in mind one thing that almost every alphabet is already assigned as a shortcut with control key e.g. if I use “C” than “Ctrl + C” will be the shortcut to run this Macro in future and this in turn means that this will replace your default shortcut i.e. in future if this workbook is opened in which you will store the macro and if you will press “Ctrl + C” to copy any content (which is a usual thing) but instead of copying, Excel will run this Macro. So, now the question comes that how we can assign a shortcut key?..... the answer to this question is, instead of just pressing an alphabet on your keyboard , press “Shift + alphabet “ e.g. in this case we will use “Shift + N”.Where to store the Macro? (As I told you earlier, that whenever we record a Macro, only your actions will be recorded and in the backstage of Excel, these actions will be saved as different statements. So what happens is, whenever we run the Macro, Excel execute these statements one by one so quickly that it perform all the task in few minutes or few seconds) so if excel is asking you that where to store the Macro, it means that where you want excel to store the coding.In this drop down you will see three options - This Workbook, New Workbook & Personal Macro Workbook.For our example, let’s choose “Personal Macro Workbook”.The last part of this dialog box is Description. As we can’t explain everything about the Macro by its “Macro Name” so we can use “Description”. This will help us to know which Macro will do what.Now just press ok and the recording will start. You can also see the color of the Macro recorder (in the left corner of the status Bar is changed to Blue).Now just type your name in the active cell and then press enter. After doing so just stop the recording.
Use Relative Reference 00:06:04
Macros by default works on absolute references i.e. whenever we record a Macro, excel will automatically save the address of the cells in the coding so if we want excel to work on relative references we need to select "Use Relative Referance "
Monthly Report Macro 00:04:20
Let's prepare a Monthly report Macro. After going through with the video just follow the steps in your system and prepare an identical macro. If you encounter a run-time error while playing the Macro then don't panic. You may have done something wrong, so just record the Macro again with another name and run the same again.
Assign a Macro to a Shape 00:02:24
Now let's see how to assign a Macro to a shape. In this lecture we will learn how to assign a Macro to a shape and make a clickable shape.
How to edit or delete a Macro 00:04:01
As we have recorded a Macro in Personal Macro Workbook, we need to first unhide the workbook first and then we can easily edit or delete the same.
Security Settings of Macro 00:04:17
It is really important to understand the security settings of a Macro because on the basis of which security settings you choose on your computer, you will be able to run the Macro enabled files.
Section 18: Macro Controls & Buttons
Text Box Control 00:03:32
Enables you to, in a rectangular box, view, type, or edit text or data that is bound to a cell. A text box can also be a static text field that presents read-only information.
Scroll Bar 00:03:20
Scrolls through a range of values when you click the scroll arrows or drag the scroll box. In addition, you can move through a page (a preset interval) of values by clicking the area between the scroll box and either of the scroll arrows. Typically, a user can also type a text value directly into an associated cell or text box.
Combo Box 00:02:31
Combines a text box with a list box to create a drop-down list box. A combo box is more compact than a list box, but requires the user to click the down arrow to display the list of items. Use to allow a user to either type an entry or choose only one item from the list. The control displays the current value in the text box, regardless of how that value is entered.
Option Button 00:02:03
Allows a single choice within a limited set of mutually exclusive choices usually contained in a group box or frame. An option button can have one of three states: selected (turned on), cleared (turned off), and mixed, meaning a combination of on and off states (as in a multiple selection). An option button is also referred to as a radio button.
Check Box 00:02:01
Turns on or off a value that indicates an opposite and unambiguous choice. You can select more than one check box at a time on a worksheet or in a group box. A check box can have one of three states: selected (turned on), cleared (turned off), and mixed, meaning a combination of on and off states (as in a multiple selection).
Lable control 00:01:12
Identifies the purpose of a cell or text box, displays descriptive text (such as titles, captions, pictures), or provides brief instructions.
Command Button Control 00:01:29
Runs a macro that performs an action when a user clicks it. A command button is also referred to as a push button.
Image Control 00:01:21
Embeds a picture, such as a JPEG, GIF, or bitmap
Calendar Control 00:02:01
Embeds a Calendar.
Section 19: visual basic for applications (VBA) Introduction
Introduction to VBE 00:07:21
VBA is the acronym for Visual Basic for Applications. It is an integration of the Microsoft's event-driven programming language Visual Basic with Microsoft Office applications such as Microsoft Excel. By running VBA within the Microsoft Office applications, you can build customized solutions and programs to enhance the capabilities of those applications.A lot of people might not realize that they can actually learn the fundamentals of Visual Basic programming without having a copy of Visual Basic professional. Why? Because there is a built-in Visual Basic Editor in Microsoft Excel, and you can use it to customize and extend the capabilities of MS Excel. The applications you build with MS Excel is called Visual Basic for Applications, or simply VBA.
How to read coding 00:07:20
In this video, we will try to understand how to read the coding in VBE. It will help us to understand the Macro recording in much easier way.
© 2016. LearnBox Education.

Login

Register

Create an Account
Create an Account Back to login/register