Playing the numbers game

Often dismissed as a fancy calculator, a spreadsheet is a useful tool for both the home and business user. Let Windows Middle East guide you through the basics.

  • E-Mail
By  Andrew Picken Published  November 2, 2003

Introduction|~||~||~|In the grand scheme of things, numbers have always been the poorer relation of words. From an early age we are taught how to organise information in terms of words on a page. But when that information happens to be represented in numbers, many of us break out in a cold sweat, cursing that infernal maths teacher from our school days.

The truth is that most us are not comfortable structuring numbers in the same way you would manipulate words on a page to make sense. Luckily this void has been filled by electronic spreadsheets in the computer age, which allow us to organise and group numerical information, while, perhaps crucially, helping us to understand the relationships between sets of information.

In the same way that an author weaves words together, which relies on the sentences and punctuation around it, a spreadsheet is equally dependent on relationships and dependencies between numbers. Confusion often reigns over exactly what a electronic spreadsheet can do but essentially it is a decision making tool that organises information into software-defined columns and rows. The information can then be manipulated by a formula to give a total or sum and then outputted in either a graphical or numerical format.

Spreadsheets are tools that help us understand, model, and manipulate relationships between sets of numerical data. One piece of data is dependent on another if it changes when the other number changes: it is these dependencies that make spreadsheets earn their keep. Investing some time in the creation of a spreadsheet will give you the opportunity to change aspects of the information so you can see what, often non-obvious, effects the changes have on the results we are concerned with.

Spreadsheets allow you to answer all those 'what if' questions that are racing through your head with just a couple of taps on a keyboard. For example, if you maintain the family finances in a spreadsheet and want to know how much extra cash you would save if the interest rates on your loan repayments were lowered then all you need to do is change the numbers in one cell, to see the potential outcome.

It is of course conceivable that you can just write this all out on a piece of paper and still understand the relationships between the information and use a calculator to work out the sums. But where spreadsheets have a clear advantage is in the speed of calculations and capacity to store large volumes of data. You should be warned, however, that spreadsheets are not a panacea for your mathematical shortcomings and you need to understand the relationships between the data in your worksheet before you can get the most out of your electronic spreadsheet.
||**||Introduction Continued|~||~||~|
Although spreadsheets have been around for hundreds of years, the very first electronic spreadsheet program, VisiCalc, emerged in the late seventies, co-written by two programmers: Bob Franklin and Dan Bricklin. On his own web site, Bricklin highlights the enormity of the impact that the software made when it first arrived: "VisiCalc took 20 hours of work per week for some people and turned it out in 15 minutes and it also let them become much more creative." Saving time on laborious data entry tasks is probably one of the biggest attractions of using any electronic spreadsheet package.

It was Lotus 1-2-3 that established spreadsheet software as a major data presentation package as well as a complex calculation tool. Lotus eventually killed off VisiCalc by buying it up but the spreadsheet's popularity finally reached its critical mass with the introduction of Microsoft's Excel package, which was actually originally written for Apple.

Ease of use was the catalyst for this shift towards Excel and although not without its faults, simplicity is at the heart of the Excel software. A big reason for Excel's popularity is because it was one of the first spreadsheets to use a graphical interface with pull down menus and have a point and click capability using a mouse. When users were faced with a clean, intuitive graphical user interface over the command line interface of PC-DOS spreadsheet products at the time, it was clear that Microsoft's Excel software was going to triumph.

For the purposes of our workshop we have used Microsoft Excel, primarily because it is the most popular spreadsheet package, but there are a lot of other options available on the market. For example, there are several free or Open Source software packages such as OpenOffice, Calc and Gnumeric as well as other commercial packages such as Lotus Notes.

So now you have the background to spreadsheets, let us guide you through the basics of this popular calculation and data storage software and give you some tips on how to make the most of your Excel package.
||**||Steps 1 and 2|~||~||~|
In many respects Excel's user interface is similar to Microsoft's other Office software but one of the first things you notice when you open Excel is a sea of hundreds of different rectangles, known as cells, which are divided by columns and rows in the worksheet area. At the bottom of the screen you will notice different tabs, these are for different work sheets, which are essentially different pages of your Excel workbook so you don't have to put all your information on one page.

Along the toolbar at the top is a number of short cut buttons that, once you get to grips with them, will make your Excel life so much easier. This bar comes into its element when you are typing in complex formulas. One of the most crucial parts is the formula bar that displays the constant value or formula used in the active cell. To enter or edit values or formulas, select a cell, type the data, and then press enter. You can also double-click on a cell to edit data directly in the cell. Other equally important buttons are the chart wizard, paste and auto sum functions.

If the prospect of maths classes filled you with dread when you were at school then this section might make you a tad uncomfortable, but it doesn't have to be like that. Try to get into the habit of using the formula shortcuts that are available like the auto sum function on the tool bar. This, unsurprisingly, automatically adds up all the cells you have selected. Another useful shortcut is the paste function, which lists all of the possible combinations of formulas under different categories.

To enter a formula, click on the cell in which you want to enter the formula and type the equal sign. If you click Edit Formula or the Paste Function, enter the formula and then press Enter. You can enter the same formula into a range of cells by selecting the range first, typing the formula, and then pressing Ctrl+Enter. It is also possible to enter a formula into a range of cells by copying a formula from another cell. For more information about copying a formula, click Edit a formula and then click the cell that contains the formula you want to edit.
||**||Steps 3 and 4|~||~||~|
It is quite easy to get lost in a nebulous world of numbers when working on a complex spreadsheet, but altering the layout and format of the spreadsheet can facilitate better information flow and spare you from any unnecessary headaches.

The quickest way to take care of all your formatting needs is to select the data you wish to alter and then right click. Select the Format Cells option and you are presented with the following tabs: number, alignment, font, border, patterns and protection. The number option changes the category of your numbers, e.g it changes the normal numbers into percentages or fractions. On an aesthetic level, Border and Patterns are both interesting formatting options, particularly if you have a complex spreadsheet where colour coding will improve the flow of information. Excel is primarily used for the management of data and it is important to protect this, often sensitive, data from prying eyes. To protect a worksheet, choose Protection from the Tools menu, and then choose Protect Sheet. A password is optional.

There are over 20,000 rows in your average Excel sheet, so keeping track of your spreadsheet has the potential to become an absolute nightmare. This is where functions like filtering allow you to quickly display only the items you want to see from a list.

Go to the Data menu and then select Filter. You are then presented with the Auto Filter and Advanced Filter options. You need to ensure your cursor is over the selection of data that you want filtered so that the filter function knows which data to filter. Auto Filter does exactly what you think it would. Say you need to find all the occurrences of one particular name, but you have a list of over 20,000 names. It takes the data in a column and allows you to filter it either by the occurrence of a particular value or by the top ten occurrences. It is also possible to customise the filter to look for specific occurrences within your data. The Advanced Filtering option involves filtering the data in a list where only the rows that meet a condition, one that you specify using a criteria range, will be listed.
||**||Steps 5 and 6|~||~||~|
Creating charts from the data in your spreadsheet is made fairly straight forward with the use of the Chart Wizard tool located on the toolbar. Simply select all the data you wish to be included in the chart and then click on the chart wizard icon. You are presented with a wide array of chart types, from the humble bar chart right through to the bubble chart. Ensure you click on the preview button before selecting a chart type, as not every type will be suitable. In the next option you have the data range and series tabs. The data range refers to the data for your chart and it is possible to change the data selected from here.

The series tab refers to names, values and category names used in your chart. Clicking on next takes you to the penultimate step where you have the title, legend and data labels options. The legend is a crucial part of understanding any graph and you can alter the position of it around your graph. The data labels tab allows you to add labels onto your data that show the values of each part. The percentage option is particularly useful.

Now it is time to show off your Excel efforts to the world. The most obvious outputting option is to print your sheets out. You can either select the section of data you want printed, print the entire workbook or just the active sheet you are working on. Another, slightly more sophisticated, outputting method is to create links from your spreadsheet to other documents. These are known as hyperlinks and can be created in workbooks by using either text or graphics that link to charts, workbooks or web pages. Right-click the text or graphic you want to represent the hyperlink and then click Hyperlink on the shortcut menu.

Under Link to, click Create new document. Type a name for the new file in the Name of new document box. To specify a location other than the one shown under Full Path, click Change and then select the location you want. Under When To Edit, click an option to specify whether to open the new file for editing now or later. To assign a tip to be displayed when you rest the pointer on the hyperlink, click ScreenTip and then type the text you want in it

Add a Comment

Your display name This field is mandatory

Your e-mail address This field is mandatory (Your e-mail address won't be published)

Security code