Practical Exercises:
Spreadsheets, Composite Documents HTML
Conventions:
Bold type is used to refer to Buttons and menus on the screen in Word and other applications.
This Sans-serif font is used for examples.
Italics are used for emphasis.
Table of Contents
Some Preliminaries 2
Excel Spreadsheet 3
Introduction 3
Exercise X1 – Creating a Spreadsheet 3
Exercise X2 – Changing the look and style 4
Exercise X3 – Adding Formulae 4
Exercise X4 – Adding a Row to a Spreadsheet 5
Exercise X5 – Making a Graph of the Data 5
Exercise X6 – Multiple Sheets 5
Exercise X7 – Dynamic Linking 6
More Advanced Excel Functions – see p.12 6
Advanced Word Techniques 7
Exercise W1 – Building a Word Template 7
Exercise W2 – Updating Normal.dot 7
Exercise W3 – Inserting a Table of Contents 8
Exercise W4 – Inserting Cross-References 9
Composite Documents 10
Exercise C1 – Producing a Spreadsheet 10
Exercise C2 – Making a Graph of the Data 10
Exercise C3 – Making a Drawing 10
Exercise C4 – Adding a Picture to the Document 10
Exercise C5 – Adding a Table to the Document 11
Exercise C6 – Adding a Graph to the Document 11
Exercise C7 – Adding an Object 11
More Advanced Excel: Exercise X8 – Currency Converter 12
Databases 13
Definitions 13
Basic database management techniques 13
Access Databases 14
Exercise A1 – Setting up a Database 14
Exercise A2 – Creating Queries 14
Exercise A3 – Database with two tables 14
Exercise A4 – Database + Mail Merge Exercise 1 15
Flat File Database Management Exercises using MS Excel 17
Exercise D1 – Manipulating a simple Excel “database” 17
Exercise D2 – Mail-merging from an Excel table 19
Results for Module BS1904 – Computer Applications 19
Editing Internet Web Pages 20
Exercise – Working with HTML Source 20
References 20
Some Preliminaries
You are advised to work using Windows Explorer, so that you can navigate easily to the piece of work that is of interest, and get Windows to open it with the appropriate application.
At home, it’s easy to start Windows Explorer – just hold down the Windows key and press E.
That won’t work in College, as the function of the Windows key has been inhibited on student accounts to stop people leaving machines locked and thus keeping others off a machine that’s really not in use. To get round this, you should add a shortcut to your desktop:
1. Right-click the Start button, and select “Explore” – this will start Explorer on your Start menu
2. Make sure Explorer is not full-screen, and that you can see a bit of desktop
3. Click on Programs in the Left pane, then on Accessories
4. Find Explorer in the Right pane
5. Using the Right mouse button, drag the object on to the desktop (if you drag with the left button, Explorer will vanish from your Accessories menu)
6. When you release it, you’ll get chance to create a shortcut on the desktop
7. Finally, right-click anywhere on the desktop background, and sort the icons as you wish.
Stop Windows from hiding useful information
It’s very confusing not to be able to see the file extension. For example, you can end up with multiple files that all seem to be called fred but three of them are really fred.docx, fred.doc and fred.dot, and two more are fred.html and fred.htm (worse still, the last two even have the same icon and description shown in a details view).
Unfortunately, this is the default state of Windows, and you need to change it to avoid problems later, particularly with HTML. Please go into Windows Explorer and do the following:
· Pull down Tools
· Select Folder Options.., and then choose the View tab.
· Find the check-box that says “Hide extensions for known file types” and uncheck it.
· Click OK to apply the change and close the dialogue.
This change will give you added information in Word, Excel, and other applications.
You may have to repeat this process if your UC Winchester account has to be reset for any reason.
Getting the best out of Windows Explorer
The remaining settings in Explorer are down to personal preference – there are some people who actually like scrolling through a window full of large icons without dates or sizes! However, I find the following layout best in most situations.
· View the folders in the left-hand pane (press the Folders button if the pane is a blue mess);
· Change the View to Details – that way you can see date, time, and other details;
· Sort into a sequence that suits you – I find descending order of date best, as it puts the stuff you last worked on right at the top;
· Finally, to make sure that you get the same view next time, save these settings:
· Pull down Tools
· Select Folder Options.., and then choose the View tab
· Press the Apply to all folders button
· Say Yes on the dialogue asking you to confirm. This will become your default view.
Excel Spreadsheet
Introduction
A spreadsheet is essentially a computerized calculator that allows many calculations to be made at the same time. These calculations may be linked (or independent), and can be combined in complex ways to carry out sophisticated modelling applications. The spreadsheet started life as a manual tool in Finance, but didn’t enter common use until the first computer version – VisiCalc.
Most of the skills you’ve practised on Word are equally applicable to Excel. Selection, Cutting, Copying, Pasting, and format operations are general to all “well-behaved” Windows applications.
Microsoft® Excel uses the notebook metaphor, which lets you group related subjects, and then select one of them using a tab. “Right-clicking” almost anywhere in Excel will bring up a context-sensitive menu that usually offers most of the facilities you’re likely to want at that point.
Exercise X1 – Creating a Spreadsheet
- Open Excel (from the Start menu).
- Save it – give it a name you can remember (like supermkt.xls) in a suitable folder (you may want to create a new folder using the tool in the “Save As” dialogue).
- Select a cell for data entry. You’ll see a border round it, and anything you type will appear at the top of the spreadsheet.
- Move to another cell, either with the mouse, or by pressing the cursor keys.
- Enter the supermarket spreadsheet as shown below, starting with the Item heading in cell C6 and the Supermarket names in cells D6 through G6 (D6:G6). Leave row 7 blank and enter products and costs in cells C8:G10 It doesn’t matter much if you don’t put entries in exactly these cells, but if you don’t, it will invalidate the cell addresses given in subsequent instructions.
Item Waitrose Tesco Sainsbury Asda
Tea 1.4 1.2 1.25 1.15
Coffee 1.45 1.56 1.5 1.5
Sugar 0.9 1.09 1.21 1.2
- Many of the following operations require you to select a group of cells. Do this by clicking on the cell at one corner of the group, and dragging the mouse cursor to the opposite corner of the group. When you release the mouse button, all the cells in the group will be in highlighted, except for the first, which has a border round it (this is the one that’ll be changed by any typing you do). Clicking any cell outside the group removes the selection. Practise selecting groups of cells.
- Save the spreadsheet as supermkt.xls (you should first create a samples folder in My Documents). You can then recover what you’ve typed, regardless of any later errors or power-failures. As everywhere in computing, it’s important to Save whenever you are ready to commit what you’ve done.
Exercise X2 – Changing the look and style
1. Change the number format by selecting the cells containing the numbers (D8:G10), clicking the right mouse button to bring up a context-sensitive menu, and selecting Format Cells to work on the format of the block of cells you had selected. In the Category column, select Currency, and leave the number of decimal places as two. Click OK to apply the changes.
2. Ensure that the number entries are selected (as above), and align the numbers centrally in the cells using the Formatting toolbar. If you can’t see this toolbar, pull down the View menu and make it visible. The alignment buttons consist of a series of horizontal lines – not surprisingly, central alignment has these lines centered on the button.
3. Select the names heading the columns (C6:G6). Click the B button to make them bold.
4. With these cells still selected, increase the point size of the using the font item on the toobar.
5. If a name is now wider than its cell, widen the cell by placing the mouse cursor in the column identifier row (marked A, B, C… beneath the spreadsheet title bar) and position the cursor across the boundary separating the column from the one on its right. When the cursor changes to a double-headed arrow, click and drag the cursor to the right to widen the cell. Repeat this action for the other columns as necessary.
6. See if you can find another way to do this (hint: select the columns, then pull down the Format menu).
7. Select the names and change their background colour by invoking Format Cells and choosing the Patterns tab
8. Select all cells in the table (C6:G10) and expand the row height by pulling down the Format menu and selecting Row then Height, and changing the height to 20. Click OK to apply the change.
Exercise X3 – Adding Formulae
1. First calculate the total cost of items from the Waitrose Supermarket. Move the cursor to cell C12, two rows below the final item (Sugar) in the first column of the table. Type the word Total.
2. Begin to create the formula for summing the cost of items in the first column. There’s a short-cut for this, using the big Sigma (S) on the tool-bar. Select the cell where you want the total to appear (D12). Next press the Sigma, and Excel will generate a formula containing =SUM(D8:D10). SUM is a function that adds up the cells listed in its argument (the stuff inside parentheses). Note the dotted line that appears round the cells being added up – you can change them by dragging with the mouse. Accept the formula by clicking on the green tick near the top of the sheet. Check that the answer makes sense.
3. Next click on D12 to find how it was done. In the entry box, you’ll see the formula =SUM(D8:D11) You could have typed this in yourself, or just typed the name of the function, =SUM(, and then entered the range by dragging across the relevant cells with the mouse.
4. Try an alternative method of entry to work out the total in the Tesco column.
5. Next select one of the cells containing a total, and press the Copy button.
6. Select the two cells that don’t yet have totals, then press the Paste button. Check that the values make sense, then look at the formulae that have been entered into them. Note that the column letters were modified when you copied the formula. This is a very powerful feature of spreadsheets.
7. Add a column to the table to calculate the average cost of an item across all the supermarkets. For this purpose, use the formula:
=AVERAGE(start_cell_reference:end_cell_reference)
where you determine the actual start and end cell references, e.g. E8:H8 for an average in I8. Do not type the words shown in the script font!
8. Provide averages for each item (row).
Exercise X4 – Adding a Row to a Spreadsheet
1. Click on the row number to the left of the Coffee cell (C9); this selects the entire row. Right-click to bring up a context menu, and click on Insert; a new row will be inserted. Note: if you had just selected a cell, rather than the whole row, Excel would have needed to find out what you wanted to insert. It does this by opening an Insert dialogue box, which allows you to insert columns or rows.
2. Inspect the cells containing formulae, and note how these have been updated to reflect the fact you have inserted within a range.
3. Enter Biscuits in this column and the prices 1.06, 0.99, 1.25 and 1.05 in the cost columns (D9:G9). Note how the column totals have automatically changed to reflect these insertions.
4. Add the average title cell, and adjust the alignment of names and numbers as necessary.
Exercise X5 – Making a Graph of the Data
1. To draw a graph, you must first select the data to be plotted, including the names (in this case, products and store names). Select the required data (block C6:G11), omitting the Totals. Click on the Graph tool in the Toolbar. Work through the Chart Wizard to construct a column or bar chart. It’s probably best to put the graph immediately below your table of figures.
2. Change one or more entries in the table to see how the graph immediately reflects the change.
3. Experiment with different chart types, and try to change colours and fonts.
4. Next create a new chart, making sure that the supermarket names are the categories. Change Chart Type to Stacked Bar, and select the type that makes the height represent absolute value (there’s another type where all data groups are scaled so the total height is constant – that’s how you show relative values without needing a whole lot of pie-charts).