|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||||||||||||||
|
|
|
|||||||||||||||||||||
|
|
|
|||||||||||||||||||||
|
|
|
|||||||||||||||||||||
 |
 |
|
||||
|
||||||
Excel Spreadsheets:A first look
Before the advent of multimedia and the popularity of the Internet, the most commonly used application on desktop computers was a spreadsheet. Spreadsheet programs are the easiest to use despite how daunting they may look. And they can be used for many different purposes—from simple lists to complex financial statements.
The spreadsheet’s ability to provide instant calculations, what-if analysis, and dazzling graphs has proven irresistible to computer users. It’s a natural for analyzing statistical data, producing financial reports, analyzing personal finances and many other kinds of numerical operations.
A spreadsheet’s basic function is to work with numbers, and it does that in a way nearly everyone can understand. The key attraction is this: if you change a number, a spreadsheet will give you the new results instantly, and change the corresponding graph automatically.
The first popular spreadsheet was VisiCalc: Visible Calculator. The newest spreadsheet program is Excel 97, part of the Microsoft Office suite.
What is a spreadsheet?
If you have used Word 97, you already know many Excel operations. A word processor provides a "blank sheet of paper" on which you can type a document. A spreadsheet is a program that provides a columnar sheet with a large number of vertical columns and horizontal rows. The result is a series of boxes, called cells, in which you can fill up numbers, words or formulas. The formulas are for calculations, e.g. add a list of numbers.
What can I use a spreadsheet for?
While a spreadsheet is ideal for working with numbers and doing calculations, you don’t have to use these features—a spreadsheet can be used for keeping simple lists: say, a list of holidays, list of your CD collections, books and even phone numbers. You can obviously use a word processor for such lists, but it is often easier to do these things on a sheet that already has columns and rows drawn out. And supposing today you make a list of audio CDs and some time in the future you want to know what is the total playing time of the music you have. Simply type in the playing time of each CD in the column next to its title, and the spreadsheet will show you the total time instantly. As you add or delete CDs the total changes automatically.
The real power of spreadsheets is seen in numerical applications such as accounts, stocks and shares, budgets, projections etc. Whatever use you may put the program to, the basic features and operations are the same. The best part is that the numerous, powerful features built into spreadsheets stay completely out of the way until you want to use them.
Different strokes
Five different types of data can be entered into spreadsheets:
1. Numbers
2. Strings (characters)
3. Formulas
4. Functions
5. Dates
Formulas and functions are discussed below, the other types are self-evident. Excel recognizes what type of data you are entering simply as you type it in—in most cases it is not necessary to specify this explicitly.
Formulas and functions
Spreadsheets allow users to define, and type in, whatever formula they want to use for their calculations. All spreadsheets use the basic mathematical symbols, that is, a plus (+) for addition, a minus (-) for subtraction, an asterisk (*) for multiplication, and a slash (/) for division.
Another very important symbol is the parenthesis or the bracket. You use parentheses to indicate what part of a formula you want done first. For example, 1+2*3 will give you 7, because programs tend to multiply before they add. If what you really want is to first add 1+2 and then multiply that sum by 3, which equals 9, you must use parentheses. To do that you use the formula (1+2)*3, and the program will calculate what is in parentheses first. As another example, ((1+2)/3)*4 tells the program to add, then divide, then multiply, which is the exact opposite from the way the program would do it without the parentheses.
Usually formulas are based on contents of cells rather than absolute values, i.e. numbers are replaced by cell references in formulas, e.g. A1/A2*100 would calculate the percentage of the contents of cell A1, based on the total in A2.
For totaling a series of numbers we could type in:
First number + Second number + Third number ....
If there were hundreds of numbers to add up, this would not only be cumbersome, but would also be prone to mistakes. Spreadsheets provide a much easier method—functions. For example, the function to add a series of numbers in cells A1 to A100 would be =SUM(A1:A100).
To enter a function select Function... from the Insert menu or click on the fx button on the toolbar. Excel has an excellent functions wizard which helps you through the process.
Functions are categorized as Financial, Text, Dates, Database, Logical, Statistical etc. There are a large number of functions built into any spreadsheet. The average user needs less than a dozen—the others will stay out of the way and never bother you.
Excel Power tools
You buy a coffee machine and have great coffee for months, and then you suddenly realize you can use the same machine to make soup! You can’t make soup with Excel, but it has some superb features that let you do things you hadn’t thought possible. Some of these powerful features let you manipulate data in a variety of ways, making working easier and add a touch of class to your work. The best part about all Excel features is that they remain out of the way and don’t bother you until you need them. The flip side is that you don’t need them because you don’t know about them!
Your order please
When you put money in your wallet, you put notes of same denominations together. If you have a list of names, you are bound to want to them in some order—alphabetical, age-wise, the marks they got in an exam or the amounts of money they owe you. A spreadsheet makes it simple to enter the data in any sequence and then rearrange it any order you please.
Let’s open the spreadsheet we created in the last issue. If you don’t have the file, it will take only a few moments to recreate—just follow the figure. Remember the percentages in Column F are calculated using the formula =D2/E2*100 for Row 2 and corresponding formulas for the other rows.
Now let’s add the Ranks in Column G. We will give a Rank of 1 to the student with the highest marks, 2 to the second highest and so on. We will first sort the data from highest percentage to lowest. If your columns have headings, Excel will call them by those names.
The rows are now rearranged so that Row 2 has the student with the highest marks. Now let’s enter the ranks.
If you want, you can now sort the data again on Sl.No. to revert to the original sequence of names.
Why should you do all the work?
When you got your computer, you were told that one of the good things about a computer is that it does repetitive tasks without complaint. Yet you find, you are the one who’s doing all the repeating! Copying the same set of cells repeatedly, choosing the same print range again and again, sorting and resorting are some of the common examples of tasks many of us do several times a day. You can finally relieve yourself from this tedium—Excel provides a tool to automate repetitive tasks: macros.
You know that Excel automatically recalculates formulas when you change any of the referenced cells. But what would happen to the Rank if you change the marks for any student? It would remain unchanged, because it isn’t a formula. To update the ranks, we would have to go through the sorting routine described above. But we can teach this method to Excel and ask it to repeat it for us whenever required. Macros is an MS Office tool that lets us record a series of actions, assign the set of actions to a keyboard shortcut or even a toolbar button and then use at will.
Now whenever you press Ctrl-R, the ranks will be updated and the sequence will be returned to its original order, i.e. the above 12 steps will be executed with a single command.
When you save this worksheet, the macros will be saved along with it; however when you re-open the file, a dialog box will appear warning you that this file has macros which could be virus infected. All macros can potentially be virus-infected and this dialog box appears for all worksheets with associated macros. You can safely enable macros if the file was created on the same system on which you are using it.
Show Dad what he wants
People prefer to look at charts rather than lists of numbers. And if the charts are done creatively, they are bound to be seen. A simple and effective way to brighten up any chart is to spruce up its background—add a fill effect or a picture. Right-click anywhere on the background and click on Format Walls... Then click on Fill Effects and choose a gradient, texture or picture. You know what kind of pictures your dad likes, so when you get your report card, make a chart of all your marks and let him appreciate the picture, especially if your marks are not worth appreciating.