Channels Networks Blog

Information is entered into an Excel worksheet by modifying the cells within that sheet. The process is pretty much the same regardless of whether you are entering information into empty cells or cells that already contain data. To enter information into an empty cell, simply activate the cell and then begin typing. As soon as you do, the formula bar becomes activated and two icons appeared to the left of the formula bar.

Once you finish typing you can do one of two things: you can either cancel, to abandon the changes you’ve made to the cell; or enter, to confirm the changes you’ve made. To cancel, either click on the cancel button on the left of the formula bar or press the escape key on your keyboard. Alternatively, you can confirm your changes: to do this you can either click on the Enter button to the left of the formula bar or press the Enter key on your keyboard.

Pressing the Enter key does two things: it confirms your data entry and, normally, moves you down one cell. This is usually fairly convenient. However Excel allows you to choose what happens when you press the Enter key. To do this, click on the Office button and choose Excel options. On the left of the dialog box, click on the advanced Tab. This displays “Editing Options”. The first Editing Option “After pressing enter move selection” allows you to choose the direction in which you move when you press the Enter key.

As well as choosing “down”, “right”, “up” or “left”, you also have the option of completely deactivating the movement. This means that, whenever you make an entry and press the Enter key, the information is entered into the cell but the cell remains active: you don’t move down.

If we need to enter data into several cells, Excel allows us to make a selection and then navigate between the selected cells, entering information as you go. For example say we wish to fill out an invoice: we can select a matrix of cells under the “quantity”, “description” and “unit price” columns. Once we’ve entered the quantity, we can then press the Tab key on the keyboard to move to the “description” column. We can then press Tab again to move the “price” column, and so on.

When we reach the last column of our selection, pressing Tab again moves us to the second selected row. When we reach the bottom right of the selection, if we continue to press Tab, we are taken back to the top left once more. Excel even allows us to move in reverse by pressing Shift and Tab keys together.

There is an alternative to moving right and then down. We can move down first and then to the right by pressing the Enter key instead of the Tab key. Also, pressing Shift and Enter together allows us to move in reverse, i.e., up and then to the left.

There are two ways of entering information into a cell that already contains a value. The first is simply to type a new value: activate the cell; type the value and then press the Enter key. The second is to edit the value. To do this, you can either double-click on the cell and make your changes directly within the cell; or you can activate the cell and make your changes in the formula bar.

You can find out more about Excel VBA training courses, visit Macresource Computer Training, an independent computer training company offering Excel VBA training courses at their central London training centre.

In this tutorial will look at creating and customising a column chart. The first step is to select the data that we want to plot, taking care to include any column and row headings. Row headings will be used as the names of the chart series and will be displayed in the legend. Column headings will be used as category labels. If the selection includes two sets of column headings, Excel will automatically recognize this and create two sets of headings on the category axis for us.

The next step is to click on the Insert ribbon tab and from the Column drop-down menu choose the option that we require. The very first option is the omnipresent scattered column chart. Excel creates our chart and places it in the worksheet as an embedded chart. If we want to change it to a standalone chart, click on Move Chart in the Location section and then choose New Sheet and enter a name for the new chart sheet.

Having specified which chart you wish to create, you can of course customise it to suit your requirements. To change the colour of the columns, simply click once on any member of a series to highlight the whole series and then choose a colour from the Shape Fill drop-down menu in the Format contextual tab. As well as Shape Fill, the drop-down also offers Shape Outline and Fill Effects. The Fill Effects includes preset effects such as shadow, glow and bevel.

There are a number of subtypes available within the column chart type. The most basic and probably the most widely used is the clustered column chart type. To change the chart type, go to the Design contextual tab and click on Change Chart Type.

In the Stacked Column type, the overall total of all series within each category takes precedence over the individual value associated with each series. The second type of stacked column is 100% Stacked Column. Here, the height of each column becomes 100% and so all columns have exactly the same height. This type of chart shifts the emphasis away from the number or quantity represented by each series to the percentage split between series.

Excel also offers us 3-D versions of these three different column chart types. However, these are not strictly 3-D charts; they simply have a 3-D effect on the columns. It is the 3-D Column option which gives us an actual three-dimensional chart. Here, Excel adds depth to the chart and places the series along the z axis (the third dimension). The remaining column options are simply variations on these basic themes. For example, if we want to go for a 3-D chart, we might choose to have pyramids instead of rectangular blocks.

The writer of this article is a developer and trainer with Macresource Computer Training, a UK IT training company offering Microsoft Excel 2007 Classes in London and throughout the UK.

Charts are a 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 need 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 placed directly on the worksheet, often alongside the data being plotted. A stand-alone chart has an Excel sheet dedicated simply 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, we can also include chart titles. In addition to 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.

The writer of this article is a developer and trainer with Macresource Computer Solutions, an independent computer training company offering Microsoft Excel 2007 training courses at their central London training centre.

Although Excel’s Print Preview facility is very handy, it is purely a preview mode. You can’t work on your data while in Print Preview mode. In addition to Print Preview, Excel 2007 offers two new modes of working which offer similar benefits to Print Preview while allowing you full access to your data. To access these modes, click on one of the buttons on the right of the Excel status bar. Here you will find buttons that can take you from Normal mode to Page Layout or to Page Break Preview.

Normal mode is the Excel’s default mode. In Normal mode, the focus is on modifying and entering your data without thinking too much about pagination. If you print or preview your data, Excel will paginate the worksheet and insert dotted lines to show you the page breaks. This is normally the only feedback you will get which relates to the printed version of your document.

By contrast, Page Layout view gives you a permanent preview of where page breaks will occur and which data will be printed on which pages. When in Page Layout view, zoom out so that you can see more of the worksheet and you’ll notice that Excel displays margins on the left, right, top and bottom, as well as headers and footers. However, the great thing is that Page Layout view not simply a preview mode. You still have access to all the data within your worksheet and you can edit each of the cells it contains. If the printed version of your worksheet is particularly important, 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 the next page, you just drag the blue dotted line representing the page break to the left of that column. It’s a deceptively simple feature. In fact, many 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 these lines can in fact be dragged.

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

Concatenation is the process of stringing together different pieces of information to form a new piece of data. For example, if we have columns containing people’s title and first name and another containing their last name, we could use concatenation to create their full name. All we need to do is to string together the title, first name and last name.

Excel offers two methods of concatenating data. The first is the use of the ampersand (’&’) which is the concatenation operator: the second is the use of the CONCATENATE function. To use the ampersand in our ‘Title, First Name, Last Name’ example, we would first type in “=” to indicate that we want to create a function. Secondly, we would click on the cell that contains the title to have Excel add a reference to that cell to our formula.

The next step would be to add a space to separate the title from the first name. To do this, we simply type the concatenation operator followed by a space and since a space is a text character, it has to be enclosed in quotation marks. Thus, we would actually type ‘& ” ” &’. Next, we click on the cell that contains the first name, then type ‘& ” ” &’ once more to add a space separating the first name from the last name.

Finally, we click the cell that contains the last name and, to confirm the formula, either press the Enter key or click on the Enter button on the left of the formula bar.

Let’s now look at doing the same thing using the CONCATENATE function. When using the Insert Function command, the CONCATENATE function is found in the Text category. It allows you to string together up to 255 pieces of information. Let’s say, for example, that our title was in cell C2, our first name in D2 and our last name in E2; our formula would be ‘=CONCATENATE(C2,” “,D2,” “,E2)’.

As to which is better; there is not much to choose between them. Simply use the one which you feel makes more sense to you. One argument for using the first method is that the use of the concatenation operator is found in most scripting and programming languages. Some environments use the ampersand; others use the plus sign.

Visit Macresource Computer Training for Microsoft Excel VBA classes