Excel training | |
|
|
We created this Microsoft Excel* online training program covering versions 2003 and 2007 because we ourselves faced lots of problems in Excel spreadsheets with things like Advanced Filter, Vlookup, Macros, etc. In the process we learnt how to create good videos and also convert them to Windows Media files. One thing led to another and since we did not want many of our friends to go through the same pain we thought we should share these 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. 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. 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 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 data from large worksheets. 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. You may also need to present your data to your boss or another company. The program's charting and Pivot tables and reports provide an excellent way to present your data quickly and easily. 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.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. 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. 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. 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 and custom programs also known as 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. Tip: If you find the text or video difficult to view, just press 'Ctrl' and '+' simultaneously on your keyboard to increase the font or view size! *Microsoft Excel and Microsoft Office Suite are trademarks of Microsot Corporation. Some of the windows movie files are best viewed with InternetExplorer. Category:Training |
| Quickly find the topic of your interest from hundreds of videos: | |