Introduction to Excel Spreadsheets

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', 'sumif'. '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 analyzing 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 important 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 spreadsheet's 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 features 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.