fejezet - Advanced Spread Sheet System Applications

1. Introduction

The term ‘spreadsheet’ comes from traditional accounting practice. It was used to describe the format used in book-keeping ledgers, in which expenditure categories were arranged as columns, and amounts were added in the relevant columns, with each row representing a transaction. This organisation of rows and columns is carried over into today’s software (Figure 4.1).

4.1. ábra - Figure 4.1: Tables and Rows

Every cell in a spreadsheet is like that programmable calculator! Every cell can contain any formula, of almost any complexity, and reference numbers in other cells. This means that once you have defined your formula, merely changing the numbers in the other cells allows you to freely experiment with your data, instantly. A cell is not restricted to numbers. In fact, a cell can contain any of:

• 
  Text;
  
• 
  Numbers;
  
• 
  Logical values (true or false);
  
• 
  Formulae (that is, calculations), which include references to other cells.
  

4.3. ábra - Figure 4.3: Features and descriptions

The cell selector allows you to highlight any cell by name. This is often faster than scrolling around a large worksheet. The Excel Window and menu bar houses most of Excel’s commands (Figure 4.2, 4.3).

• 
  File: Saving and printing;
  
• 
  Edit: Cutting and pasting, filling and clearing cells, deleting cells, columns and rows, searching;
  
• 
  View: Viewing a document in different ways, enabling and disabling toolbars, headers and footers;
  
• 
  Insert: Inserting cells, rows, columns, worksheets, charts, functions, objects, diagrams;
  
• 
  Format: Applying styling and formatting to cell contents, rows and columns, defining styles;
  
• 
  Tools: Checking spelling, mathematical consistency, protecting cell contents and whole sheets, high-level tools, options;
  
• 
  Data: Sorting, filtering, grouping, data tables;
  
• 
  Window: Handling multiple windows;
  
• 
  plus of course a Help menu.
  

• 
  Column titles are alphabetic, starting A, B and so on, through AA, AB to IV, 256 in all.
  
• 
  The toolbars—Excel’s toolbars, the Standard toolbar and the Formatting toolbar, displayed on the same row. You can display the toolbars in full on two separate rows if you want. Excel’s toolbars, of which there are many, are designed to give you quick access to often-user commands. You can create your own toolbars, too, just as you can with Word.
  
• 
  The task pane is displayed here because we’ve just done a File ➪ New command.
  
• 
  The horizontal scroll bar allows you to move forward and backward through the columns in a worksheet.
  
• 
  The vertical scroll bar (here obscured by the task pane) allows you to scroll a worksheet vertically through its rows.
  
• 
  The bottom border of the window contains information about the status of the current cell. It displays Enter if you are typing data into a cell, otherwise it displays Ready. Tips are also displayed here when you are engaged in an editing task such as copying a group of cells.
  
• 
  The worksheet tabs allow you to switch between the different worksheets in a workbook.
  
• 
  The worksheet selector widgets allow you to scroll the worksheet tabs if necessary.
  
• 
  Row titles are numeric, from 1 through to 65,536—that’s 16 million cells to a single worksheet!
  

You have already learned that a cell whose contents start with ‘=’ is interpreted by Excel as a formula. Excel will try to calculate the results of any formula it finds and displays the results in the cell (Figure 4.4). But how can you create formulae? Excel offers you four ways:

• 
  Entering a formulae directly into a cell.
  
• 
  Entering a formula using the formula bar.
  
• 
  Building up formula by clicking on the cells you wish to include.
  
• 
  Pasting Excel functions into a formula.
  

3. Excel functions

Excel functions (Figure 4.5, 4.6) provide the real mathematical power of Excel. A function in Excel is an expression that calls a piece of code dedicated to a specific purpose. For example, the formula: =SUM(A1:A4) calls the function SUM() to return the total of the cells A1, A2, A3 and A4, that is: A1+A2+A3+A4. Excel contains over two hundred functions, which allow you to calculate results that would be far too complex and tedious to program into a worksheet by hand. You can see this if you select Insert Function… in a blank worksheet, then set Or select a category in the Insert Function dialog to All. Many of them you will never use, as they are dedicated to complex mathematical calculations that you are unlikely to encounter—at least, not yet. Some, such as the SUM() function described above, are essential (Figure 4.5). Excel’s functions are grouped by purpose, as the pop-up menu adjacent to the Or select a category field in the Insert Function dialog shows.

4.5. ábra - Figure 4.5: Spreadsheet formulas

4.6. ábra - Figure 4.6: Functions

3.1. Category Includes

• 
  Database: a set of functions for calculating data from an embedded database, or ‘look up’ list. Excel allows tables of data to be embedded in a worksheet.
  
• 
  Date and Time Functions: to convert or display anything to do with dates, hours, minutes and seconds, for example NOW(), which returns the current data and time.
  
• 
  Financial: a set of functions to calculate common financial values, such as the total cost of a loan, the future value of an investment, or the required interest rate for a loan.
  
• 
  Information: a set of functions that are mainly concerned with returning information about the state of other cells. For example ISBLANK(), which returns FALSE if a cell or range of cells has contents, else TRUE.
  
• 
  Logical: a set of functions for combining logical expressions, such as AND(), OR(), IF(), and which return the values TRUE or FALSE.
  
• 
  Lookup and Reference: a set of functions for extracting data from look-up tables within a worksheet, or information about the current cell. Examples of the latter are ROW() and COLUMN(), which return the row and column numbers of the cell containing the current formula (i.e. “What row or column am I in?”).
  
• 
  Maths and Trig: a set of functions to calculate common mathematical and trigonometrical values, such as sine, tangent, cosine, square root, sum of squares.
  
• 
  Statistical: a comprehensive set of functions to calculate values used in statistical analysis, such as average, maximum, minimum or n-th largest of a set of numbers, as well as more complex functions such as the Squared, Poisson distribution and Student’s t-distribution tests.
  
• 
  Text: a set of functions to process text, for example to make one length of text from text in multiple cells, to convert numbers to text, or to convert text to upper or lower case.
  

6. When you have finished experimenting, click on OK to close the Function Arguments dialog. Entering a function like this might seem a bit long-winded for something as simple as SUM(), but it’s really useful for functions with more, or more complex, arguments, or for functions with which you’re not familiar. Keep this workbook open for the moment, as we’ll add to it in the next step.

3.2. Copying and pasting formulas

Soon you will be entering a set of data for 10 students. But first you must complete the Gradebook Template by creating formulas that will produce a TOTAL or SUM of the scores for EACH student, as well as a PERCENTAGE (a score out of 100) for each student in the class. Before you carry out the exercise, here is a description of what is involved. Fig. 4.13 illustrates the process.

3.3. The order of processing of formulae

When you write formulae in Excel, you need to remember that it has a predefined order of priority for processing mathematical expressions. What we mean is that:

=3+4*12

in Excel give the answer 51—that is, Excel gives the multiplication a higher priority than the addition, so does it first. So this expression is equivalent to:

and not:

which would give the answer 84. Excel uses the following order of priority when executing formulae:

3.4. Priority Operator Description

Highest Colon, comma Cell references, for example ‘C3:C6’

- Negation, for example ‘-1’

% Percentage, for example ‘20%’

^ Exponentiation, for example ‘2^3’ (this means ‘2 cubed’, i.e. 2*2*2)

* and / Multiplication and division

+ and - Addition and subtraction

& Join text strings (‘concatenation’)

Lowest = < > <= >= <> Comparison: equal, less than, greater than, less than or equal, greater than or equal, not equal

You can override this order of priority by using brackets. Excel will first evaluate the expression in the innermost pair of brackets, using the priority shown above, then the next pair of brackets, and so on. If it finds two mathematical operators with the same priority, such as multiplication and division, it evaluates the formula from left to right.

3.5. Using relative and absolute cell references

We have mentioned relative references above. Now you can see examples for using relative and abosulte cell references below.

4.7. ábra - Figure 4.7: Absolute reference

4.8. ábra - Figure 4.8: Relative reference

For example:

C3:C5

1. Reopen your shopping list workbook, if it’s not still open from the previous section.

2. First, add the titles ‘Item’, ‘Pounds’ and ‘Euros’ in cells B2 to D2.

3. Drag to select these cells again, then click on the button in the formatting toolbar to set the titles to bold.

4. In cell F2, enter:

Euros per Pound Tab 1.52118 Return

(or substitute the current conversion rate)

5. You can probably only see part of what you typed, as column F will be too narrow to display the entire phrase. Move the mouse cursor over the boundary between the titles for columns F and G, then click and drag to make column F wide enough.

6. Click in cell D3 and enter the following:

=C3*G2 Return

This calculates the price of your eggs in Euros, and the result is displayed, which will be €1.901475 if you used the exchange rate of €1.52118/£1.

7. Now click again in cell D3, and drag to copy its contents to cells D4 to D6. Not quite what you expected, maybe? As you can see if you select cells D4, D5 or D6, Excel has changed the reference to cell G2, which contains your conversion rate, to G3, G4 and G5. However, this is not what you want to happen—you want Excel to use the contents of cell G2 for all the conversions. Here’s how to stop this happening…

8. Select cell D3 again. Using the formula bar, change the cell’s contents to:

=C3*$G$2

9. Repeat step 7. This time you should get correct results in euros for all your items.

10. To complete this exercise, we’ll visit the formatting dialog to set the decimal spaces of the euro figures to 2.

Select cells D3 to D6, then select Format ➪ Cells… In the Format Cells dialog, select

Number. The number of decimal places should default to 2, so just click on OK. We’ll have more to say about cell formatting in Step 4. Before you leave this step, try the following exercises on your own:

• 
  Copy cells C8 to D8 and C10:C11 to D10:D11 to see how Excel handles absolute function references.
  
• 
  Change the euro conversion rate by changing the value in G2 and watch Excel work for you!
  

When you are editing a formula in the formula bar, the F4 key allows you to toggle between all the combinations of absolute, row-absolute, column absolute and relative references. Excel is usually clever enough to work out which reference to change.

We have described how you express a range of cells in Excel. For example, the formula:

=SUM(A1:A4)

is the same as

=SUM(A1,A2,A3,A4)

You might wonder how you express multiple ranges. For example, suppose you wanted to tell Excel to calculate the sum of cells A1 through A4 and D2 through D6? It’s easy—you do it like this:

=SUM(A1:A4, D2:D6)

Try this now for yourself, using a blank worksheet to experiment with. You can select non-adjacent ranges of cells such as this by:

• 
  Clicking and dragging to make the first selection
  
• 
  Holding down the Ctrl key
  
• 
  Clicking and dragging to make the second selection.
  

example:

means the same as:

=SUM(A1:A12)+SUM(B1:B12)

The system is copying the formula in cell J10 to cell J14 in relation to ("relative to") cell J10. In other words, just as the formula in J10 sums the values stored in cells D10 through H10, so the formula copied to cell J14 will sum the values relative to cells D14 through H14. Does that make sense? If so, give yourself a pat on the back! If not, don't despair. Read it over a couple of times. The alternative to a Relative Reference, by the way, is an Absolute Reference. You will need to use an Absolute Reference shortly, at which time you will more easily understand what it means in the context of the exercise. Bet you can't wait to check it out!

Press Ctrl-s again to save your work so far (are you getting into the habit of doing this?)

Filling down (copying the formula into the rest of the TOTAL column)

Excel provides a neat tool to duplicate the contents of cells into a set of adjacent cells. For the sake of this exercise we will assume you will have just ten students in your class. You are going to duplicate the formula that is in cell J14 into the other nine cells below it. As before, Excel will automatically adjust the cell addresses so that they are appropriate (relative) to each student's record.

3.6. Advanced Excel Tips

Auto Fill

Auto fill can be accomplished either by issuing the Edit-Fill-Series command and selecting one of the options from the dialog box that appears. The Edit-Fill-Series dialog box is particularly useful when defining a fill series of dates where the stop date is many cells away. It is also useful when you want to create a trend line using a linear or growth series.

The Edit - Fill - Series Dialog Box

Another way to auto-fill a range is to grab the “fill handle” with the mouse pointer and to drag it in the direction that you want to fill. The “fill handle” is the square on the lower right corner of a highlighted range. When you hover over the “fill handle” with the mouse, the cursor changes from a big + to a much thinner -.

Auto Fill with the Fill Handle

You can fill in several types of series by selecting cells and dragging the “fill handle.” By dragging the fill handle of a cell, you can copy the contents of a cell to other cells in the same row or column. If a selection contains a number, date, or time period, you can extend the series. For example, if you want to fill in a series of dates where the dates go from the last date of one month to the last date of the next month, simply enter in the first two month ends and then drag the range down with the fill handle.

AutoFill with the Fill Handle and the Control Key

The AutoFill feature has built-in defaults that can sometimes bother you. To get the AutoFill to increment a number or not increment a text label with a number, simply press the Control Key along with using AutoFill, and you will get the opposite results. In other words, by pressing the Control Key along with the AutoFill, you are temporarily flipping the default settings opposite what they normally are.

4. Working with charts and graphs

Chart is a term that has come to us from America. It tends now to be applied to any graphical representation of data that is not a traditional graph. For example, the terms bar chart and pie chart are very common in business:

• 
  A bar chart represents numerical quantities as vertical or horizontal bars in which the length of the bar represents the size of each value
  
• 
  A pie chart represents proportions of a whole, or percentages, as slices of a flat cylinder, or ‘pie’, in which the size of the slice represents that quantity’s proportion of the whole.
  

4.9. ábra - Figure 4.9: Common charts elements

4.10. ábra - Figure 4.10: Chart styles

Creating a simple chart

Let’s see just how easy it is to create a chart. We’re going to produce the pie chart shown on the preceding page, which is based on your shopping list workbook. Excel makes it really easy to do this by providing a Chart Wizard.

Embedded versus floating charts

Whether you choose to embed a graph or chart in a worksheet, or to give it its own worksheet, depends largely on the design of your application and how you intend it to be used:

• 
  Embedding a chart in a worksheet allows you to see the chart change as the data changes. This is useful if you are using the worksheet to analyse data. However, it can obscure your working area unless you are running Excel on a monitor with a large screen. It also means that you cannot easily print just the chart.
  
• 
  Giving a chart its own worksheet leaves you with more working space on your data worksheet, but means that you cannot see both the data and the chart at the same time. It does make it easy to print just the chart, however.
  

You saw in the first dialog of the Chart Wizard that Excel can create many different types of chart and graph. Some are purely decorative, others place specific requirements on your data, and do not make sense if the data does not meet those requirements. For example, X/Y scatter plots are not meaningful unless each data point has at least two numerical values. More exotically, ‘bubble’ plots, shown on the right, relate three sets of numerical values, in which the size of the bubble is used to represent the third variable. In the next exercise, we’ll use a bar graph format to display both the pounds and euros data from your shopping list. This is thus a 2-series chart, as it displays two sets of numerical data:

4.1. Adding legends to your chart or graph

You have already seen how to use the options in the Chart Wizard to add legends to a chart, and how to edit the formats of axes legends. Excel has many options for adding legends to charts. These tend to be specific to the type of chart or graph you are using. These are available from the Chart Options dialog, which differs between different chart and graph types.

4.11. ábra - Figure 4.11: Formatting columns

4.2. Editing charts and graphs

Editing items in charts and graphs is very simple. All you have to do is: Select the item to be edited, right-click and:

• 
  Select the relevant Format option, or
  
• 
  Select Clear to delete the item, or
  

If none of the options for a particular chart type seem to be right, Excel allows you to change the type of a chart even after you have placed and edited it. Try this now:

1. Reopen the shopping list example workbook if it is not already open.

2. Right-click on the embedded pie chart in Sheet1, and select Chart Type…

3. Select a completely different chart type, such as the fifth option under Cylinder.

Click on OK.

4. Use the techniques you learned in the previous section to change the font to Arial Bold.

Click on the chart object to select it, then drag the top border up to make the chart taller.

4.3. Editing a chart’s data range

You may find, once you’ve created a chart or graph that you need to change the source data for the chart. For example, if the data is contained in a table within a worksheet, and you add to it, you will want to include the new data in the chart. Excel makes this easy to do. You have two options, both controlled from the Source Data dialog:

• 
  Redefine the data range by clicking and dragging
  
• 
  Redefine the data range by directly editing the data series specification. To see how this works, we’ll change the data range on the pie chart in our shopping list workbook:
  

5.1. Basics Reviewed

If you are going to copy formula, you need to understand the difference between absolute cell references and relative cell references. For example, if you enter the formula =C5 in cell C6, the reference is read by Excel as “Add what is one cell above the current cell.” Copying this formula to any other cell will result in a formula that adds what is in the cell above the cell that is being copied to. For example, if you copy the formula to cell M90 the formula will read =M89. You can make a cell reference an absolute reference by placing $ symbols in front of the column reference and the row reference. For example, if you enter the formula =$C$5 in cell C6 the formula will remain =$C$5 regardless of where you copy it. As new users quickly learn, understanding how to use absolute references is essential to most accounting and other financial spreadsheets.

5.2. Beyond the Basics

Using the F4 key to Create Absolute References

One approach to making a cell reference absolute is to simply type in the $ symbols. This is time consuming and usually requires the user to type the $ symbol twice. An easier approach is to simply press the F4 key while building the formula. This can be done both when you are typing in a cell reference and when you are using the mouse to build a formula by pointing to the cell reference.

5.3. Partially Absolute Cell References

In some worksheets, it is useful to have the cell reference be “partially absolute.” This means that the cell reference is either absolute with respect to column or relative with respect to row or vice versa.

In the previous screen capture, the formula has been constructed with partially absolute references so that it can be copied both down and across. Unfortunately, Excel does not allow you to use the drag function to copy something both down and across at the same time, so you will have to copy down and then copy across.

Note: When you are building partially absolute references, you can still use the F4 key to insert the $ symbol. You simply press the key more than one time to alter the nature of the reference. For example, if you press the F4 key twice when building a cell reference in a formula, you get a cell reference that is absolute with respect to row but relative with respect to column. If you press the F4 key three times, you get a cell reference that is absolute with respect to column but relative with respect to row.

5.4. Range Name and Sheet Name References

Range names (formally referred to as defined names in Excel) and sheet names are always absolute references and do not need the $ symbol. IMHO was an unwise choice by the designers of Excel because it takes away the option of having these references be either absolute or relative. Having sheet name references always be absolute, for example, prevents you from building a formula that points to a certain cell in the sheet immediately above the current sheet. This technique is useful in building spreadsheet models that have totals that continue from one sheet to the next.

5.5. AutoSum Drop Down Options

In Excel 2002 and Excel 2003, the AutoSum icon (look on the standard toolbar) has a drop down box that allows you to choose other commonly used mathematical functions.

6. Working with Styles

If you have not worked with styles before when using MS Word, you are really in for a treat. In Word, styles are drop down menu selections that can include formatting for font, spacing, borders and underlines, italics, color, and a whole lot more – all by just making one selection. Once you have defined a style (for example, Heading 1), you can later change the attributes of that style and all of the text that is formatted using that particular style will change to the new format. This is actually just a small glimpse into the benefits that styles bring to MS Word, but it’s a good start. In Excel, styles work a bit differently. One style allows you to set the appearance attributes for anything that is entered into a cell, both text and numbers, and also allows you to set the protection attribute of a cell. To bring up the Excel style dialog box, use the Alt – ‘ key sequence or issue the Format – Style command from the main menu.

There are a number of pre-programmed styles in Excel, and you can create your own styles by typing a new name in after Style name: in the top of the dialog box. Then, when you want to use that style, you can simply choose the new style from the list. Rather than having to open the style dialog box every time you want to use a style, you can add the style icon to your formatting toolbar.

This is all very well and good until you start a new spreadsheet and find that the new style you liked so much is missing from the new blank spreadsheet. It is also missing from all your old spreadsheets that were created prior to learning about and creating custom styles. Custom style settings are not global settings but, rather, are embedded in the particular workbook in which you create them. However, there are solutions to both issues of getting your new styles into existing spreadsheets and of wanting to have your styles appear automatically in new blank spreadsheet files.

Let’s tackle the task of getting the custom styles into existing spreadsheet files that you are going to continue to use for some time. Let’s assume that you have created several styles you really like and would like to transfer these styles to an existing file. First, open the file that has the custom styles. Next, open the older file to which you want to transfer the custom files. While you are in the older file, issue the Format – Style… command from the main menu.

Notice that the Style dialog box has a Merge button on the following graphic. You can use this button to open the Merge Styles dialog box. Unlike MS Word, which allows you to browse for files that have your styles, you have to have the other file that has your custom styles in it already open. It is a little cumbersome, but it is a lot easier to merge styles from one file to another than it is to create them all over again with every old file where you want your newly created styles.

What about the problem of wanting to have your styles automatically appear in new blank MS Excel Workbooks? The solution to that issue is to create a new default template that contains your custom styles. Then, every time you start with a new blank file, your custom files will be there waiting for you.

6.1. Changing Defaults for Workbooks and Worksheets

For many of the settings you use frequently, such as formats, macros, sheet names, and ranges, you may want to create a Master Template. The simple way to do this is to start with a blank worksheet and fix up all the items you want to have present in all future worksheets and then save the file to be used as the starting point of all new worksheets.

6.2. Create a File You Always Start With

A simple approach is to create a blank file that has all the settings you would like and to save it to use each time you create a new file. If you save the file with a name like _blank.xls and place it in the default directory, it will be relatively easy to find. Whenever you want to start a new worksheet, you simply open this file, which will be the first file in the default directory. It is probably a good idea to change the file attribute of this file to “read only” so you won’t forget to rename the file before you save it.

6.3. Create a Template to Use for New Workbooks

Excel templates are just like regular Excel files except they have the .xlt file extension rather than the .xls file extension. When you open a template file using the File – New command, Excel assumes that you don’t want to save the new file over the original template file. Excel also assumes that you want the file to have an .xls file extension when it is saved rather than the .xlt extension. The template scenario solves two problems with the simpler approach described previously. First, you don’t have to remember in what directory it is stored or waste time searching around for your template file. It is always available from the File – New menu. Second, you don’t ever make the mistake of saving the file and writing over your startup file because the template setup assumes you don’t want to write over the template.

To setup a template, start by making all the changes you normally have to make each time you create a new file. These alterations might include changing the margins, column widths, or footers to meet your preferences and your businesses standards. Then, save the file as a template in the templates directory.

6.4. Create a Template that is Opened Automatically

To permanently change the default worksheet you get when you start Excel or to start a new file, save your template as a file named book.xlt in the Excel XLStart folder. Excel will use this template as the default model from then on. You can create a similar default file for individual worksheets using the filename sheet.xlt in the same directory.

7. Questions

1. 
   What are the main features of a speadsheet system?
   
2. 
   What is the structure of a spreadsheet?
   
3. 
   Describe the formulas!
   
4. 
   What are spreadsheet analyses for?
   
5. 
   What are the spreadsheet functions?
   
6. 
   What are the relative and absolute references?
   
7. 
   How can we create charts?