| General Stuff | Macros, User Forms, etc |
| Working in MS Excel | What is a macro in Microsoft Excel |
| Functions in MS Excel | Recording a macro in Excel |
| Data Validation in spreadsheets | Understanding the recorded macro code |
| Data Validation Using Lists | How to write your first macro |
| Navigate through worksheets while keeping headers visible | Macro to input data into Excel Worksheet |
| Get data into an Excel Worksheet from Text Files | A Formula Macro |
| Get data into an Excel Worksheet from another Excel File | A Macro to Perform Calculations in MS Excel? |
| How to get data into an Excel Worksheet from a Database | How to Create a New Workbook Using a Macro |
| How to get data into an Excel Worksheet from the Web | How to Open the Workbook Using VBA or Macro |
| How to Analyze Data Using Auto-Filter | How to Access Excel Worksheets in Workbook Using Index Number |
| What are Named Ranges in Microsoft Excel | How to Access Worksheets in Workbook Using Names |
| How to Analyze Data Using Advanced Filter | How to Access Excel Worksheet Cells Using A1 Notation or Range Property |
| Analyzing Data in Excel using VLOOKUP | Refer to Excel Worksheet Cells Using Row & Column Index or Cells Property |
| Analyzing Data Using VLOOKUP and Named Ranges | How to refer to Rows and Columns Using VBA or Macro |
| Analyze Data Using VLOOKUP and Named Lists | How to Refer to Excel Worksheet Cells Using Shortcut Notation |
| Analyze Data Using the SORT Function | How to Refer to Named Ranges Using Excel VBA or Macro |
| IF and Nested IF for data analysis | Referring to Cells Relative to Other Worksheet Cells Using Offset Property in VBA |
| Charts in Excel | How to Use a For Next Loop in Excel VBA |
| What-if Analysis Using GOAL-SEEK | How to Use a Command Button |
| What-if Analaysis Using Solver | How to Call a Worksheet Function Using a Macro |
| Data Analysis and Reports Using Pivot Tables | Calculate Equal Monthly Instalments Using a Macro or Excel VBA |
| How to Use Excel Help Effectively | More Excel VBA or Macro Solutions in MS Excel 2003 |
| More MS Excel solutions... | More MS Excel 2007 VBA Solutions... |
| The
goal of the online avatar
of the Family Computer Club is to help you to learn how
to use Microsoft
Office Excel formulas, functions and macros (VBA) through our free and
good quality
training video tutorials. Our videos will demonstrate how you can use
MS-Excel for storing, organizing and manipulating large amounts of
data.You should be able to find solutions to many financial
calculations and
what-if scenarios quickly after studying the presented help articles
and content. Why learn Excel? Why is MS-Excel so important in today's work and business environment? Microsoft Office Suite is used by nearly every employer, school and university in the world and the more extensive your skills with the application suite, particularly Excel spreadsheets, the more attractive a candidate you are when applying for jobs. How we have organized our online Excel learning course: 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 a hard copy or preparing a presentation for our meetings. With this in mind let's see how we can benefit from Microsoft's excellent software. 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 at the top and the rows numbered as 1, 2, 3, 4 on the left. 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 if 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 cell addresses is obvious: If you mistakenly entered 6.5 in cell B1 instead of 5.6, just overwrite the value with 5.6 and your result in C1 automatically corrects to 15.6. 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, if you entered 'Andy Davis' in cell A1 and if you now want to separate the first and the last names, then 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 the library functions. 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 important and useful Excel 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. Of course, you can use the simple 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. Dynamic charts can help to view charts and data as desired. 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 Excel 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. 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. If you have any queries regarding our free Excel training videos or offline training, please mail here. We also recommend that you buy an excellent book on Microsoft Excel |
|