WORKING WITH WORKSHEETS

Freeze panes

Large spreadsheets often have column or row headings that need to be viewed when entering data. However, once you have scrolled down the rows a short way, you won’t be able to view the headings in row 1.

To fix the position of headings, so that your columns and rows roll behind them, freeze them as follows:

• 1.Click the first cell to the right of any column you want to freeze e.g. Bl, or below a header row e.g. A2 and then select Window – Freeze Panes. To be able to view both column and row headings, click B2.

• 2.Lines will appear down the boundary of column A and below row 1 to show where they are frozen.
• 3.Scroll to the right to enter figures in column H or I and you will see that columns B – D have disappeared behind column A. You will now always be able to view e.g. the months when entering your figures. The column headings will also remain in place if you scroll down below row 15 or 16.

• 4.Take off the freeze at any time by selecting Window – Unfreeze Panes.

Naming sheets

You don’t need to retain the names of sheets as Sheet1, Sheet2 etc. Double click the tab at the bottom of the screen and rename them. You can also insert or move sheets. Add new sheets from the Insert menu, and drag them to new positions if you want them re-ordered. To select a number of sheets, click the first and then hold Shift as you click the last.

Formulae across sheets and files

Within one workbook, you are likely to have data on a number of sheets that sometimes needs to be referred to in calculations e.g. monthly figures used to create an overall total for the quarter. Simply click the correct sheet tab and then the cells when creating your formulae and the addresses will be added as normal.

When drawing information from different sheets, each sheet will be referred to by name plus an exclamation mark. For example, if adding the contents of A6 from three sheets, the formula will look like this:

If you had renamed the sheets it might appear as:

=January!A6+February!A6+March!A6

To use data from different Workbook files, you need to open them and then click the cells as above. Here, any external workbook will be referred to by name plus the file extension xls and the whole will be placed in square brackets. You will also note that the actual cells will contain an absolute cell reference and complex file names will have quote marks round them.

e.g. =‘[budget 2004.xls]Sheetl’!$A$10+‘[budget 2003.xls]Sheetl’!$A$10

Although you can type the formula including punctuation yourself, clicking a cell on each sheet when creating your formulae will automatically enter the appropriate identification and will ensure you do not make any mistakes.

Arranging Windows

You can move between open Workbooks quickly by holding Ctrl and pressing the tab key, and between sheets by using Ctrl and Page Up or Page Down. However, when working with data from more than one Workbook, it is much easier if you can see all the cells at the same time. Open the Window menu, click Arrange and then choose an arrangement such as Vertical. Drag out any boundaries if relevant data is hidden. The active Workbook will have a blue title bar and you must click any Workbook to activate it before you can copy the data into your formula.

FINDING INFORMATION

Many organisations set up databases in Excel. If there is no requirement to relate different sets of data, a relational database such as Access may not have been used and you will therefore have to work with the spreadsheet application. Fortunately, it is perfectly adequate for straightforward storing and searching of data.

Here are four basic functions that can make life easy when using Excel as a database application:

Re-ordering entries

When looking for a particular name or date in a jumble of entries, it can help if you sort the block of data and re-order the records e.g. by name, price or date etc. Do this by selecting all the data including column headings and then opening the Data – Sort menu. (Without column headings selected, each column will be labelled 1, 2, 3 etc in the Sort box and so will be less easy to work with.)

Choose the first order of sort, and perhaps a second or third if several items have the same first entry, and check that each one is in ascending (alphabetical) or descending order. Click OK and the records will now be re-ordered.

Updating entries

A common task is to keep the database up to date, so that it contains the latest names and address details. For a database with thousands of records, finding the correct entry manually could take some time, so you need to use the Edit – Replace facility.

Having completed the Find what: and Replace with: boxes, if you know there is only one entry to change, or that all the entries should be changed, you can click Replace All. However, if you wanted to change Paul Drefuss’s name to Paulo, but not Paul Duchamps’, for example, you would need to keep clicking Find Next and check by eye. When the correct entry is selected, change it by clicking Replace.

Using a form

Many people prefer to enter information into a database by using a form. This lets you view a complete record, rather than a table of all the records together.

Create a form by selecting all the records and going to Data – Form. When the form appears, click New to add new records.

Searching

To search for a particular record or set of records, you can use the form. Click Criteria and you will see empty boxes. Enter your search criteria in the correct boxes e.g. Manchester in the Town box will find any records of suppliers from this town. Click the Find Next and Previous buttons to move through the records.

To view more than one record at a time when searching, you need to apply a filter. Go to Data – Filter – AutoFilter and down-facing arrows will appear next to each column heading (known as a Field name). If you click an arrow, you will see all the entries made in that field. Click any entry to find matching records.

For a more complex search, click the Custom ... entry for any field and you will open the dialog box. Here you can select a logical statement e.g. greater than, contains, ends with etc. and type your own entry or select from the available list. You can also repeat searches to filter out more and more records.

For example, you could find which suppliers have first names beginning with M, and then search for any Madrid suppliers amongst them:

You can print the selected records if required and then take off the filter by selecting (All) from the list, or use the menu and click Data – Filter – Show All.

PIVOT TABLES

More complex analysis of data is possible if you create a Pivot Table. This allows you to organise your columns and rows quite differently from the original layout and calculate totals, averages or maximum figures etc.

Design the table

Use the Wizard and click Next each time to work through the steps.

• 1.Select the data and go to Data – Pivot Table. Check that the correct data source is to be analysed.

• 2.If you didn’t select the cells first, click the red button and highlight the records you want to analyse. Click the button again to return to the Wizard.

• 3.Decide whether to place the table on a new sheet or not and then click Finish. To place the table on the same sheet as your data, clicking the cell where you want the table to start adds the cell address to the box provided.
• 4.You can now create an appropriate design for your Pivot Table. On the left, you will be offered all the headings (field names) in your database, and you can drag any field across to a chosen position in the table.

Analyse the data

There are four areas to which you can drag fields from your database: the Row fields, Data items, Page fields and Column fields. Simply drag fields on and off these areas to experiment with changing the presentation of your data.

To find out how much each company’s total orders come to, for example, you could drag Company to the Row Fields area and Orders to the Data Items area. The result is shown below:

To filter out particular sets of data, you can use the Page Fields area. For example, to see data related to a specific Start Date, drag this field to the Page Fields area. Clicking the arrow allows you to select a particular date and then display that information only.

Calculations

The default function is Sum, to show totals. If you want to display the maximum or average prices, however, double-click the cell showing the type of calculation being performed and select an alternative from the list.

To display the data differently, you could drag the Make field off the Row fields area and replace it with the Item field, and then change the function to show the average cost of each item of clothing.

To see average prices of items by manufacturer, you could now drag Make to the Column Fields area and produce a far more detailed display.

Formatting

Format your data using the normal font and number formats, or click the Format Table button on the Pivot Table toolbar and apply an overall design.

CHARTS

To create a chart from data in a spreadsheet, always select the data and column headings (but not total or title rows) and then use the shortcuts and wizards to make the task straightforward.

To create a column chart instantly, select the data and press function key F11. To work through the wizard, make your selection but now click the toolbar button and follow the steps.

Step 1 – select the appropriate chart type
Step 2 – check or change source data
Step 3 – add titles and labels and add or remove the legend (key)
Step 4 – position the chart on the same or a new sheet

Font types and sizes, numerical formats, line styles and background colours can all be changed – either right-click or double-click the element to be offered the formatting options or select any object and use the normal toolbar buttons or options from the Format menu.

Having created a basic chart, there are several aspects that you may want to modify. To return to any steps in the wizard, or add missing items, right-click in the chart area or select an option from the Chart menu.

Change chart type

Select Chart Type as above and you will be offered Step 1 of the wizard. Select a different chart type from the list and click OK.

Add labels

Select Chart Options to return to Step 3 of the wizard. You can now complete any missing titles and also add pie chart labels – e.g. display a percentage or both label and value.

Scale

The wizard will set appropriate maximum and minimum values for your chart but if you want to amend these, select the Y axis, double-click for the Format dialog box and click the Scale tab.

Having changed your top and bottom figures, you may also need to amend the interval i.e. Major Unit value to display the data appropriately.

Change data

Once you have created your chart, you may want to amend the data that is being displayed. To make a change without re-creating the chart, select the Source Data option to return to Step 2 of the wizard.

To view your data in different ways, you can show the data series in rows or columns. The labels will form the legend.

A common problem is when axis labels are numerical e.g. years or measurements, as these are often interpreted as a data series. Click the Series tab, select the axis that is wrongly included in the Series box and click Remove.

If any labels or values are not displayed correctly, or you want to change the actual entries selected, click the button in the correct box to return to your spreadsheet. Select the appropriate cells with your mouse and then click the button again to return to the dialog box and check the display.

To select data from non-adjacent columns, hold down Ctrl as you drag across the second and subsequent range of cells.

Trendlines

On XY scatter graphs, you can add a trendline by right-clicking any point. There are various options available including displaying the equation on the graph.

Formatting lines and markers

Where your line chart is comparing data, it is important that you can differentiate between the lines easily. Printing in colour will help, but in many offices your printout will be black and white. To help read the chart, double-click the line or right-click and select Format Data Series. You can now re-colour a line or marker, change the marker type or size or remove it altogether.

Printing charts

If a chart is on its own sheet, clicking the Print button will print out the chart showing on screen. If, however, you have placed your chart on the same sheet as the original data, you must select the chart to print it alone. Otherwise, both data and chart will print together. There is an option available in the Page Setup box labelled Chart if you want to set the printer to print in black and white: this will help distinguish the different data series.

If you want to add your details to a chart printout, use the header or footer option available from the View menu. It works as in Word except that you need to click the Custom button to open the header or footer box.