Excel Training

The Family Computer Club's online training programm in Excel is based on the fundamental understanding of the working of the computer: Input-Process-Output. Therefeore, every work that we do in Excel 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 of Excel with optimum CPU and memory usage. Finally we need to get our output for various purposes like printing or presentation.
A few words on why you should learn Excel: Microsoft Office Suite is used by nearly every employer in the world and the more extensive your skills with the application suite, particularly Excel, the more attractive a candidate you are when applying for jobs. With this in mind let's see how we can benefit from Microsoft's excellent software.

Excel 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 Excel 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 Excel 2003 and the rows are numbered from 1 to 65536. In Excel 2007 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'. Microsoft Excel uses the 'R1C1' reference style when recording macros. Most people, however, work with the default alpha-numeric style, for example, A1.

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 Microsoft Excel 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 in Excel can not only be formatted in Excel 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 in Excel.
Date and times are entered into the Excel 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.
Formulas and functions form the heart of Excel. 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.
Next you have to decide where and how you wish to place your data in Excel. 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 Excel 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. Microsoft Excel 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 in Excel 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. Microsoft Excel'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 Excel worksheet cells which help the user immediately understand the importance of the highlighted data. 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 in Excel. A case in point is an expense report. Creating templates is an art. In fact, people who can create templates in Excel make good money. Template creation has become an industry. You can create good templates in Excel only if you have a good understanding of formulas, functions and the domain for which you wish to create Excel templates. Thousands of free templates are available on the internet.
After familiarizing yourself with many of the lovely possibilities of using Excel 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 Application (VBA). VBA is easy to learn and the Visual Basic Editor comes free with Excel. What should you learn in VBA for Excel? The first step should be how to access the Excel cells in the worksheet. Then you should learn how to move from one Excel cell to an Excel 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 for Excel 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 in Excel? Can you show us how to create financial models in Excel? The answer to this is quite straightforward: If you learn to use Excel 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.
We have created many free Excel 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 videos or offline Excel training, please mail here. Also note that some of the Excel training videos are large files and may take time to download even with a broadband internet connection. So be patient.

You can also visit our blog and view most of the videos in a 'condensed' format.


SocialTwist Tell-a-Friend

What's new? Excel 2007 training including macros

Training in Excel 2003
Macros training (VBA)
  1. Introduction
  2. Text Formatting
  3. Curreny or Number Formatting
  4. Formatting dates
  5. Basic Calculations
  6. Guidelines for entering data on a worksheet
  7. AutoFill
  8. Paste Special
  9. Custom Lists
  10. Fill series
  11. Password protection and Encryption
  12. AutoFilter
  13. Advanced Filter
  14. Pie chart
  15. Conditional Formatting
  16. 'IF' Function
  17. Multiple 'IF'
  18. PMT function
  19. Compound Interest calculation
  20. Solver & Scenarios
  21. Pivot Tables
  22. Array Formulas
  23. Proper data entry
  24. Validation of text and numbers
  25. Data validation of dates
  26. Custom Data validation
  27. Using Named Lists in data validation
  28. Importing Text files into Microsoft Excel
  29. Importing Data from MS-Access into Excel
  30. Named ranges
  31. DSUM function
  32. Vlookup
  33. Hlookup
  34. Text Editing in Excel
  35. Forecast function in Excel
  36. Depreciation Function
  37. Growth function
  38. Exporting Data from Tally 9 (Accounts) into Excel
  39. Exporting Excel charts to PowerPoint
  40. Time Functions
  41. Date Functions
  42. Calculating a running balance
  43. Smart Tags
  44. Text Function EXACT
  45. Frequency Function
  46. Goal Seek Function
  47. AND function
  48. Sumif Function
  49. Countif Function
  50. Sumproduct Function
  51. Lists
  52. A macro vs function for text processing
  53. Analyzing Access data in Excel
  54. Printing in Excel - Part 1
  55. Printing in Excel - Part 2
  56. Statistics in Excel
  57. PowerPoint Presentation on Statistics in Excel by Microsoft
  58. Exporting data from MySQL to Excel
  59. Importing data from Excel into MySQL
  60. Adding a combo box control
  61. Adding a spinner button control
  62. Adding a scroll bar control
  63. Loan project using the above controls
  64. Performing a web query
  65. Freeze property in large Excel worksheets
  66. Hide and Unhide data
  67. Importance of templates
  68. Invoices in Excel
  69. Networth calculator
  70. Expense statements in Excel
  71. Breakeven analysis
  72. Protecting and hiding formulas
  73. Gantt Charts
  74. Internal Rate of Return (IRR)
  75. Collaborating using Shared workbook(s)












  1. What is a macro in Excel?
  2. Recording a macro
  3. Recorded macro code
  4. Simple Macro
  5. Entering data using a macro
  6. Formula macro
  7. Writing a Macro using VBA
  8. Creating a new workbook
  9. Opening a workbook
  10. Referring to sheets by Index Number
  11. Referring to sheets by name
  12. Referring to Excel ranges using the A1 notation
  13. Referring to cells using index numbers (r1c1 reference)
  14. Refer to rows and columns in macros
  15. Referring to cells by using shortcut notation
  16. Referring to named ranges
  17. Referring to Excel cells relative to other cells
  18. Referring to cells by using a range object
  19. Referring to all cells on the worksheet
  20. Referring multiple ranges
  21. Looping through a range using a FOR NEXT LOOP
  22. Selecting and activating Excel cells
  23. Working with 3-D Ranges in Microsoft Excel
  24. Working with active Excel cells
  25. Using a command button
  26. Calling a Worksheet Function
  27. A macro using the PMT function
  28. Creating a custom dialog box - Step 1
  29. Adding controls to the user form - Step 2
  30. Adding code to controls - Step 3
  31. Calendar control tool
  32. Date and time picker tool
  33. An interactive web page
  34. 'FOR LOOP' macro
  35. For Each... Next loop
  36. CurrentRegion property
  37. Publishing an Excel document to a web server
  38. Data Forms
  39. Troubleshoot a macro
  40. A macro for formatting & calculations
  41. Pay-Packet calculation with a macro
  42. Creating custom Excel function
  43. Macro for text handling in Excel
  44. Nested loops macro to delete duplicates
  45. A sample project
  46. Running multiple macros in a worksheet
  47. Attaching the above macro to a command button
  48. Offset Excel cell property - an interesting macro


Free Ebook on Microsoft Excel training
Attend a live workshop on Excel 2007

Quickly find the Excel training topic that interests you.
Example: Just type VLOOKUP or IF in the search box above.

About us
Tags: Microsoft Office, Microsoft Excel, Excel training, free excel training, excell, vba, computer training, excel software, how to use excel, excel 2003, excel 2007, excel vba, excel spreadsheet, learn excel, excel tutorial, ms excel