| The aim of our website is to help you to learn how to use Microsoft Office Excel including Macros (VBA) free of cost through our high quality training video tutorials. You should be able to find solutions to many financial calculations and what-if scenarios quickly after studying the presented material. Why is MS-Excel so important in today's work and business environment? Microsoft Office Suite is used by nearly every employer in the world and the more extensive your skills with the application suite, particularly Microsoft Excel, the more attractive a candidate you are when applying for jobs. |
SAMPLES OF YOUTUBE TRAINING
VIDEOS
| |
|
|
|
|
Our program is based on the fundamental understanding of the working of the computer: Input-Process-Output. Therefore, every work that we do must take this into account. Our data entry should be done after finalizing a plan on paper even for relatively uncomplicated analysis. The process should use the relevant formulas, functions and macros and Excel then takes care of optimum CPU and memory usage. Finally we need to get our output for various purposes like printing or presentation.
With this in mind let's see how we can benefit from Microsoft's excellent software. It is a user friendly electronic spreadsheet program and part of the Microsoft Office Suite that can be used for storing, organizing and manipulating data.
|
|
When you start the
application
you see a worksheet or spreadsheet with many
rectangular entities called cells. Each cell can be identified by a
column at the top and a row number on the left. The normal view is
where you see the columns with A, B, C, etc as headers and the rows are
numbered as 1, 2, 3, 4. You can also have the view where you see the
columns and the headers with numbers starting from 1. In this case
known as the 'R1C1' reference
style view each cell can be identified by
numbers on the top and left. The columns are numbered from 1 to 256 in
the version 2003 and the rows are numbered from 1 to 65536. In the 2007
application version the rows are numbered from 1 to 1048576 and columns
from 1 to 16384. MS-Excel 2010 has the same number of rows and columns
as Excel-2007. For example, the 4th row and the third column cell
would be identified as 'R4C3'. We use the 'R1C1' reference style when
recording macros. Most people, however, work with the default
alpha-numeric style, for example, A1. Data entry of numbers and text
should be done using the excellent data
validation feature which helps the user to make lesser mistakes
and
improves the speed of data entry. An example of data
validation can be found here. You can now enter text, numbers, currencies, dates, formulas and functions in the cells. It is a good idea to apply the proper formatting on the entered data. For example, we may want to enter the salaries of employees as whole numbers and the prices of items in decimal format. |
| Calculations
in the spreadsheet are performed by using the 'cell addresses', i. e.
we do not use the values in the cells. For example, let's say, we have
the whole number '10' in cell A1 and the decimal number '5.6' in cell
B1. Now we wanted to display the sum of these two numbers in cell C1.
We would use the formula '=A1+B1' in cell C1. So you see, we have not
used the formula '=10+5.6' in the cell C1. The advantage of performing
calculations using addresses is obvious: If you mistakenly entered 5.6
in cell B1 instead of 6.5, just overwrite the value with 6.5 and your
result in C1 automatically corrects to 16.5. Protecting
your formulas using cell protection and getting them checked
by another person to avoid errors is important. Text can not only be formatted in this software but it can also be manipulated in interesting ways. For example, you entered 'Andy Davis' in cell A1. Now you want to separate the first and the last names. That can be done easily. Date and times are entered into the worksheet in special formats and they can also be manipulated using formulas and functions. This can be very useful in project management or time management. We have also shown how to find birthdays and anniversaries from Excel data using date and related functions. Formulas and functions form the heart of a spreadsheet application. You can use simple in-built functions like 'sum', 'average', 'maximum', 'minimum', etc or you can create your own complicated custom functions and formulas to perform interesting and difficult calculations. Also if you have never used formulas and functions but you know what you want to do with your data you can work with library functions which include good examples. |
|
| |
Database functions like DSUM,
DAVERAGE, DMAX, etc can be helpful in analysing and summarizing
your spreadsheet data quickly based on specific criteria. A more powerful and flexible method to summarize your worksheet data is to use array formulas. Vlookup and Hlookup are functions that many people use to extract information from large worksheets. Because there appears to be a confusion on using the Hlookup and Vlookup functions, we have created a video demonstrating the use of both the lookup functions on the same spreadsheet to extract the same information. Of course, you can use the simple or multiple or nested 'IF' functions to perform calculations based on definite criteria. Next you have to decide where and how you wish to place your data. A good design helps in the ease of calculations later. Empty rows and columns between data should be avoided. If the data we enter into the spreadsheet spans a very big range and we need to keep the headers in place while we scroll up and down or left and right, we can freeze the column and/or the row headers. This 'freezing' helps us to see which data belongs to which item without having to scroll up and down or left and right. Once the data is in place, we need to get information out of our data. The Microsoft spreadsheet application provides excellent tools to sort, filter, format and analyze data. Using these tools you could, for example, easily find out which salesperson is performing well or which product is selling well or how much money people owe your business or the other way round. Sometimes we may need to know quickly whether we have an item in stock or not. The 'scenarios' feature helps us understand worst, normal and good situations for our business or profession. |
Another interesting way to present your data in a graphical form is to use 'in-cell' graphs. Here you use the standard repeat function and conditional formatting to create interesting graphs inside the worksheet cells which help the user immediately understand the importance of the highlighted data. Conditional formatting can also be used to highlight duplicates. Dynamic formatting of data based on conditions and use of a control like a checkbox can make data presentations more interesting. You can also create a Gantt chart quickly using the in-cell graphing method. A picture is indeed worth a thousand words!
Templates are very useful elements if you wish to perform repetitive tasks. A case in point is an expense report. Creating templates is an art. In fact, people who can create spreadsheet templates make good money. Template creation has become an industry. You can create good templates only if you have a good understanding of formulas, functions and the domain for which you wish to create templates. Thousands of free templates are available on the internet.
What-if tools like Goal Seek, Solver, Data Tables and Scenario Manager help the user find out how the a result will change depending on the change in a single value or multiple values in other cells that produce the result.
After familiarizing yourself with many of the lovely possibilities of using the application for your business, professional or personal needs, you may try your hand at 'macros'. Macros help you to automate many calculations and data entries. You will need to learn Visual Basic for Applications (VBA). VBA is easy to learn and the Visual Basic Editor comes free with the Microsoft Office Suite. What should you learn in VBA? The first step should be how to access the cells in the worksheet. Then you should learn how to move from one cell to another cell of your choice. Next you should be able to place values in the appropriate worksheet cells. You should then be able to perform calculations using VBA. Once you have mastered these basic VBA steps, you should then learn how to loop through the cells so that you can automate your calculations over a large range of the worksheet. The two important loops are 'do while' and 'for... next'. With a little practice you'll find that VBA opens a door of opportunities. Our training videos provide examples of practical VBA code or macros. Here's an example of automating the creation of charts that look like dashboards from multiple data sources using the 'record a macro' feature and tweaking it a little bit with your knowledge of VBA!
The other mportant part of macros is the userform. With the help of user-forms you can create a great user interface that will reduce errors in data entry into the Excel worksheet via such forms. Since persons with good experience in programming can create such interfaces the formula checking can be more rigorous and less error prone. Remember this application is used for financial data and simple errors can cause errors running into billions of dollars! Also such interfaces can help automate our work.
The Microsoft Excel Application has a detailed help feature. Of course, to use this feature you need a little patience and study. In fact the library functions are very exhaustive. If you work meticulously with the library feature, you can learn Excel on your own to a great extent. Therefore, if you don't know how to use a function in this spreadsheets application, you can refer to this feature and discover how the different formulas and functions are to be used.
Last not least, many persons have asked us questions like these: Can I perform statistical calculations with this software? Can you show us how to create financial models? The answer to this is quite straightforward: If you learn to use the program thoroughly and if you have a good domain knowledge of, say, statistics or finance, you should be able to marry both and find a solution. Here is a training video to conduct a sensitivity analysis with data tables.
Descriptive statistics is a great feature in Excel to quickly find out important statistics like standard deviation of your data.
Last not least, we have created a learning video on 'Excel tips' for beginners as well as advanced users which includes ideas to make your work in spreadsheets quicker and more intuitive. Tip of the day also has more than 20 latest videos to help you learn some of the interesting fetaoures of MS-Excel that you might have overlooked like the speak cell features which shows how to have your own assistant who reads out to you!
In summary: Excel is a computer program that simulates a graph paper sheet consisting of cells which are organized into rows and columns to make data entry, calculations and analysis of data easy and quick using formulas, functions, what-if tools and custom programs called macros.
We have created many free training videos to demonstrate the above concepts. We will regularly add more videos for our visitors.
It is our endeavor to give you the best training material. If you have any queries regarding our free Excel training videos or offline training, please mail here. Also note that some of the training videos are large files and may take time to download even with a broadband internet connection. So be patient.
