Dot on i

Charts provide us with quick and easy way of graphically illustrating trends within your data. One glance at a chart can make it very plain where there is a dip in sales figures, a surge in visitor numbers and a host of other trends in whatever data is being represented. In this article we will examine the various components of an Excel chart.

The first thing we must have is a set of data which can easily be converted into a readable chart. It is normally best to plot data which is a summary of your information. It is also useful if your data is arranged in columns or rows with headings at the top of columns or on the left of rows.

An example of information which would be easy to convert into a chart is a selection containing two columns with data on the left and the corresponding values on the right. When the chart is created, the labels are placed on what is variously known as the category axis, horizontal axis or x axis; while values are arranged on the y axis. When your data is arranged in this format, the chart that Excel plots will not need much modification.

Charts may either be embedded or standalone. Embedded charts are created directly on the worksheet, often alongside the data being plotted. A stand-alone chart has an Excel sheet dedicated solely to the chart. This is known as a chart sheet; in contrast to a worksheet.

Whether embedded or standalone, the key components of the chart are always the same. First of all, we have a chart area. This is the background to the chart as a whole. Next, we have the plot area. This is the area where the graph or chart is actually plotted. Then, as we have seen, there are two or more axes. In a typical, “no frills” chart, there are two axes: the horizontal, or category, axis and the vertical, or value, axis.

Next, we have one or more series of data. In the example given above, where we select a column of labels and one column of values, there would be only one series of data. In a chart containing more than one series, it is necessary to clarify what each column represents. This is done by adding a legend to the chart. The legend acts as a key which tells us what each colour within the chart actually stands for.

As well as the text labels associated with the axes and with the legend, Excel also allows to create chart titles. As well as the main chart title, we also have the option of placing titles on the axes. Within the plot area, we can also choose to display grid lines. These make it easy to read the value associated with each point on the chart.

These then are the main elements within a chart. However, Excel allows you to customise each of these elements and add other elements which enable you to create charts which convey exactly the message you have in mind.

Author is a developer and trainer with Macresource Computer Solutions, a UK IT training company offering Microsoft Excel 2007 training courses at their central London training centre.

A good many of the worksheets that are created Excel contain headings in the top row of the sheet. Normally, whenever we scroll down the sheet, any headings at the top will disappear. Similarly, if one scrolls to the right, any headings on the left will disappear. Excel’s Freeze Panes command, which is located in the View Tab of the Excel Ribbon, allows you to freeze our headings so that, as we scroll the sheet, headings remain in view.

Excel offers us three options: firstly, we can choose “Freeze Top Row”. A bold horizontal line is then displayed underneath the first row which extends into the row headings. As we scroll down the worksheet, the headings at the top of the sheet remain in view. Similarly, we can choose “Freeze First Column”. This time, the bold line extends to the right of the first column and into the column heading area. Then, as we scroll to the right, the first column remains frozen so that we can see the headings it contains and compare them with the data in the adjacent cells. To return to normal scrolling, we simply choose “Unfreeze Panes” in the “Freeze Panes” drop-down menu.

As well as freezing a single row or column, it is also possible to freeze an arbitrary number of rows and columns. To do this, you simply highlight the cell below the last row you want frozen and to the right of the last column you want frozen. So, for example, if you want to to freeze the first row and the first column, you just select cell “B2″. Once you have highlighted the cell, in the “Freeze Panes” drop-down menu, you would then choose “Freeze Panes”.

This time, there are two bold lines: one indicating the column that is frozen and one indicating the row that is frozen. Then, as we scroll down the first row remains frozen and, similarly, when we scroll to the right the first column remains frozen. Once again, to return to normal behaviour, we simply choose “Unfreeze Panes” in the “Freeze Panes” drop-down menu.

Since this command allows you to freeze any number of rows or columns, if you are ever working on a large worksheet perhaps containing multiple row and column headings, you will probably find it pretty much an essential feature.

The writer of this article is a developer and trainer with TrainingCompany.Com, an independent computer training company offering Microsoft Excel 2007Classes in London and throughout the UK.

Although Excel’s Print Preview feature is fairly useful, it is purely a preview mode. You are not able to work on your data while in Print Preview mode. However, in addition to Print Preview, Excel 2007 has two new modes of working which offer similar benefits to Print Preview while allowing you full access to your data. To switch to these modes, use the buttons on the right of the Excel status bar. There are buttons which can take you from Normal mode to Page Layout or to Page Break Preview.

Normal mode is the default mode in Excel. Here, the focus is on modifying and entering your data without thinking too much about pagination. If you print or preview your data, Excel paginates the worksheet and inserts dotted lines to show you the page breaks. This is normally the only feedback which relates to the printed version of your document.

Page Layout mode, by contrast, gives you a permanent preview of where page breaks will occur and which data will be printed on which pages. When working in Page Layout view, zoom out so that you can see more of the worksheet and you will notice that Excel displays margins on the left, right, top and bottom, as well as headers and footers. However, the really nice thing is that Page Layout view not simply a preview mode. You still have access to all the data within your spreadsheet and you can edit each of the cells it contains. If the printed version of your worksheet is particularly important, for example when working on reports, you may find it convenient to stay in this mode permanently; particularly if you have the benefit of a large monitor.

Excel’s third mode is called Page Break Preview. When you click on the Page Break Preview button that Excel conveniently zooms out so that you can see more of your worksheet. Page Break Preview mode is not dissimilar to Normal mode. Like Normal mode, it is not WYSIWYG (What you see is what you get) and neither headers and footers nor margins are shown. However, the key difference between Normal mode and Page Break Preview mode is that, when you’re working in Page Break Preview mode, the page break margins can be dragged.

This is a very useful facility: if you want to force a given column of data onto a new page, you just drag the blue dotted line representing the page break to the left of that column. It’s a deceptively simple facility. In fact, some experienced Excel 2003 users may even mistake the dotted lines representing page breaks for those which are displayed in Normal mode and may not even realise that they can in fact be dragged.

The The writer of this article is a training consultant with TrainingCompany.Com, a UK IT training company offering Microsoft Excel 2007Classes in London and throughout the UK.

The Split command is to be found in the View Tab of the Excel Ribbon. It allows users to split an Excel spreadsheet window into either two or four separate panes and is particularly useful for comparing data in different parts of a large worksheet.

To use the Split command, you must first activate the cell where you want the split to occur then click on the split button in the Window section of the View Tab of the Excel Ribbon. The horizontal and vertical bars which mark the split can be repositioned simply by dragging them. Separate scrollbars are displayed for each section; so, in the case of a vertical and horizontal split, you will have two horizontal scrollbars and two vertical scrollbars. To remove the split at any time, simply click on the split button once more.

A second and more intuitive way of using the split command is to use the split boxes. These are located above the vertical scroll bar and to the right of the horizontal scroll bar. Let’s say, for example, that we have a spreadsheet containing rows of sales figures. Suppose we want to be able to compare the sales figures of one particular person with the sales figures of other individuals. We can create two vertical windows by dragging the vertical split box. We can then scroll to make the first person’s sales figures visible and in the top pane and then do the same to position the sales figures of any other sales person in the bottom pane.

Excel offers us a really simple way to exit split mode. All you need to do is double-click on the split line.

Let’s now say we want to focus on the total for each individual and that the totals are shown in column J. We could create both a vertical and horizontal split by dragging each of the two split boxes. We can then leave the salesperson’s name displayed in the left pane while, in the right pane, we can scroll across to make the total visible.

As we scroll up and down, we now see the totals for each sales person next to the sales person’s name. If we would also like the heading to remain in place, we simply make the top pane one row deep, displaying only the headings while we scroll the bottom pane up and down.

When we have finished reviewing our totals, we can remove the vertical split and leave the horizontal split in place. To do this, we just double-click on the vertical split line. To return to a completely normal Excel window, we would also double-click on the horizontal split line.

The writer of this article is a developer and trainer with TrainingCompany.Com, an independent computer training company offering Microsoft Excel 2007Classes at their central London training centre.