Content for Reprint Blog

Just about everyone who uses a PC must know Microsoft Excel to some degree and most are aware of macros and VBA. However, the vast majority shy away from VBA assuming it to be the preserve of Excel gurus and professional programmers. Nothing could be further from the truth: having Excel VBA training is a natural progression for anyone who knows Excel well and want to take their knowledge to the next level.

Naturally, however, it’s no good attending Excel VBA training classes if your knowledge of Excel is poor. You need to know Excel itself very well before you undertake any form of Visual Basic excel training; otherwise, the applications you create are bound to be flawed.

The art of developing effective Excel applications is to create functionality which complements the features built in to the program. Excel applications created by people who are not Excel specialists or simply do not know the program very well tend to perform actions which could be accomplished by using Excel’s own set of features.

So, if you know Excel very well and you can spare the time, attending Excel VBA courses may be a very rewarding experience. You will discover that Excel VBA is not terribly difficult to learn and that there is a great variety of learning materials available. After all, Excel has been around for a couple of decades and it is installed on most business PCs.

One of the reasons why Excel VBA is worth learning is that the applications you write never have to be built from the ground up. All you are doing is leveraging and automating the powerful capabilities inherent in the program. It’s a bit like walking on those long moving walkways that you get at airports. Any effort you put into walking is immediately amplified and you move much faster than you do when walking on static ground.

If you end up becoming serious about developing applications, you will have a guaranteed audience; since there are so many people using Excel. Finding a decent Excel VBA training course can help your career or even help you get a pay rise in your current job. The skills you will learn will stand you in good stead for years to come.

The author is a trainer and developer with Macresource Computer Solutions, an independent computer training company offering Microsoft Excel VBA Classes at their central London training centre.

Charts offer 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 elements 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 standalone or embedded. A stand-alone chart has an Excel sheet dedicated simply to the chart. Embedded charts are placed directly on the worksheet, often alongside the data being plotted. 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 cited above, where we select a column of labels and one column of values, there would be only one series of data. Whenever a chart contains more than one series, it is necessary to clarify what each column represents. This is done by using a legend. 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.

So, there we have 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.

Headers and footers are especially useful for documents that require several pages to print. The header is information displayed at the top of each page; the footer at the bottom of each page. There are two ways of working with headers and footers in Excel. Both are found in the Page Layout Tab Of the Excel Ribbon. When working in Normal mode, you can access the headers and footers by clicking on the Launch button in the Page Setup section. Here, in the Header/Footer tab, you have access to all of Excel features relating to headers and footers.

As you work with these options, you can click on the Print Preview button then to return to Headers/Footers, click on Page Setup. This method of editing headers and footers is particularly useful if you’re using a fairly small screen.

The second method requires that you are in Page Layout mode. To switch to Page Layout mode, simply click on the Page Layout icon on the right of the Status Bar. You now have direct access to the header and footer areas of the page. Simply follow the prompt “Click to Add Header”. As soon as you activate the header or footer area, “Header & Footer Tools” appears. This consists of the context sensitive “Design” Tab. (A context sensitive Tab is one which only displays in certain contexts.)

Apart from their position on the page, Excel treats headers and footers as identical. Each consists of three sections: left, centre and right. In each of these sections, you can insert a wide range of information. This is done by clicking on the buttons in the “Header And Footer Elements” section of the “Design” Tab. For example, at the bottom of the page, you might want to insert the date, the page number, “Page 1 of 4″, etc. To do this, you would simply activate the footer area, type the word “Page” followed by a space, then click on the page number icon which will generate the page number. Then type ” of ” and finally click on the “Number Of Pages” button.

Other items which can be automatically inserted include “File Path”, as in “c:\Excel Work\myfile.xlsx”; “File Name”, as in “myfile.xlsx”; and pictures, for example, your organisation logo. To insert a picture, simply click on the picture button and then double click on the picture to insert it, just as you would in any other region of the page. You can then resize the picture as necessary or you can increase/decrease the space available for the header/footer.

Excel allows you to change the order of worksheets within a workbook at any time. There are two ways of achieving this, the first of which is simply to drag the tabs representing each worksheet left or right. As well dragging individual tabs, it is also possible to highlight several tabs and drag them all at the same time.

Not only can we move worksheets around within the same workbook, it is also possible to move worksheets from one workbook to another. For example, suppose you have a workbook containing a worksheet for each month of the year (“Jan”, “Feb”, etc.) and that we now want to split this into four smaller workbooks, one for each quarter: the first containing “Jan”, “Feb” and “Mar”; the second containing “Apr”, “May” and “Jun”; and so forth.

To minimise the number of sheets we will end up with in each workbook, we could begin by changing the default number of worksheets Excel will give us in each new workbook. To do this, we click on the Office Button and choose Excel Options. In the section that reads “When creating new workbooks Include This Many Sheets”, we change the number to one. We can then create four sheets by clicking four times on the new sheet icon on the Quick Access Toolbar.

Each of our new workbooks has one sheet, which is the minimum that Excel will allow. We can access these new workbooks by clicking on the View Tab and accessing the Switch Windows drop-down menu. The first method of moving worksheets from one workbook to another is to drag and drop. To do this, we will need to see all the workbooks simultaneously. Excel has a special command for doing this. In the View Tab, click on the Arrange All button and choose “Tiled”. Excel will then present each of the workbooks in a miniature window, allowing us to see all of the open workbooks simultaneously.

The next step would be to select the three sheets pertaining to the first quarter: we click on “Jan”, hold down the Shift key and click on “Mar”. We can then drag the selected sheets across to the window of any of the new workbooks. We can then repeat this exact same procedure for the other quarters.

As we saw earlier, one is the minimum number of sheets which you can have in a workbook. Therefore, when we have moved the last three sheets from the original workbook, it will have no worksheets left and will therefore simply disappear. Naturally, however, the last saved version of the Excel document will still exist on disk.

The final step would be to delete the unwanted sheet from each of the four new workbooks. Having done this, to leave the split screen view and return to normal mode, we simply maximise any of the windows.

Just for reference, the second way of copying sheets from one workbook to another is to use the Move or Copy Sheets command. This can be found in the Format drop-down menu in the Cells section of the Home Tab or by right-clicking on the selected sheet tabs. As well as moving sheets, this method also allows you to create a copy at another location.

A Microsoft Excel workbook is essentially a container, a bit like a folder. Each Excel workbook can hold one or more worksheets and it is the worksheet that is the actual container of one’s information. Worksheets are identified by a tab which shows the name of each sheet. Clicking the appropriate tab activates a particular sheet.

In exactly the same way that Microsoft Excel allows users to hide columns, it is also possible to hide an entire worksheet. Hiding a worksheet is especially useful where you have a workbook that contains a lot of sheets. Naturally, hidden worksheets can be made visible again by simply using the Unhide command. Excel allows you to hide either an individual sheet or to hide a group of sheets. However, for some reason, sheets can only be unhidden one sheet at a time.

To hide a single sheet, simply right-click on the sheet tab and choose Hide. The corresponding worksheet will then disappear. There is also a ribbon command which will do the same thing. First, highlight the sheet by clicking on its tab and then, in the Cell section of the Home Tab of Excel Ribbon, choose Format-Visibility-Hide and Unhide-Hide.

To hide more than one sheet at a time, simply select the sheets by clicking on the first, holding down the Control key on your keyboard and clicking on each of the others. Next, right-click on any of the highlighted sheet tabs and choose Hide.

To make a hidden worksheet visible again, you can right-click on any sheet tab and choose Unhide. The Unhide dialog then appears. Unfortunately, it isn’t possible to select more than one sheet to unhide; if you try Control-click or Shift-click, you’ll see that only one sheet can be highlighted. Highlight the name of the sheet that you want to make visible and click OK.

If you prefer, you can also use the Excel Ribbon command Format-Visibility-Hide and Unhide-UnHide Sheet. When the Unhide dialog box appears, highlight the sheet you would like to unhide and click OK. You will notice that when sheets are unhidden they very conveniently return to the position that they originally occupied.

In this article, we will look at Excel selection techniques which involve using the mouse in conjunction with the keyboard. One of the easiest and most useful is click followed by Shift-click. This is particularly handy for highlighting large ranges of data since it allows you to simply click on two opposite corners of the rectangular range of cells which you want to select. To perform this technique, first click in the top left cell of the range you want to select. Next, make sure that the bottom right cell of the proposed range is visible. Finally, holding down the Shift key, click in the bottom right cell. All the cells between the two clicks will then be highlighted.

The other modifier key on your keyboard which can be used in conjunction with the mouse to make selections is the Control key. Whereas the mouse and Shift key combination only allows us to make rectangular selections using the mouse in conjunction with the Control key we can make non-contiguous selections; in other words, we can select cells which aren’t adjacent to each other. Once highlighted, the next operation you perform will only apply to the highlighted cells. For example, we might copy the cells and paste them into a new worksheet.

A less well-known way of selecting cells is to double-click one of the edges of the bold rectangle which surrounds the currently selected area while holding down the Shift key. Excel always displays a bold border around the currently highlighted cells. If you hold down the Shift key and double-click on the right-hand border, you will select all the cells up to the right-hand edge of the current range. The first column that contains blank cells will act as a boundary and Excel selects up to that boundary. Naturally, you can also extend the current selection by holding down the Shift key and double-clicking on any of the other borders as well.

It is also possible to highlight cells using the keyboard alone. However, only a contiguous range of cells can be selected in this way. To perform this technique, hold down the Shift key and press one of the cursor keys. For example, select to the right, press the right cursor key; to select adjacent rows, press the down cursor key; and so forth. You can also press the left and up cursor keys to remove cells from the selection.

To select all the cells up to the edge of a range, hold down the Control and Shift keys while pressing one of the cursor keys. For example, the right cursor key will select all the cells up to the right edge; the down arrow will select all the cells to the bottom; and so forth.

As well as the cursor keys, you can use the End (and Home) key(s). Holding down the Control and Shift keys while pressing the End key will select all the cells to the bottom right of the current range.

Whenever new cells are inserted into a worksheet, existing data has to shift in order to make room to the newly inserted cells. When we are inserting entire rows or columns the direction in which data will be moved is predetermined. If we insert an entire column, existing data is shifted to the right. If we insert an entire row, any existing data is shifted down. However when we insert cells rather than entire rows or columns excel needs to ask us in which direction we want the existing data to be shifted: to the right or down.

To insert a row of cells, highlight the cells above which you would like the new cells inserted then choose Home – Cells – Insert – Insert Cells. Excel then displays a dialogue box asking you to specify what to do with the existing data and, naturally, you would choose “Down”. When you click OK, existing data moves down and a range of cells is inserted of the same size as the range that was highlighted.

These exact same rules apply when we delete cells. To delete an unwanted range of cells, select the appropriate cells and choose Home – Cells – Delete – Delete Cells. Alternatively, you can right-click on the selected range of cells and choose Delete from the context menu. As before, Excel displays a dialogue box asking you to specify the direction in which you want the existing data to move. This time, however, data is being moved in order to fill the gap created by the Delete command, the directions offered to us are “Up” and “Left”. To delete a row of cells, we would choose “Up” and to delete a column “Left”.

When inserting cells, the format of the newly inserted cells will always match that of the cells which are selected when the Insert command is used.

Inserted cells are also normally blank. However, you can also cut or copy a range of cells and then, instead of pasting them, to insert them above or to the left of a given range. Simply copy or cut the desired range and then highlight the range of cells above or to the left of which you want the cells inserted. Upon right-clicking, you will notice that the context menu displays the command “Insert Copied Cells” (or “Insert Cut Cells” if the cut command was used) in place of the normal “Insert” command. In a similar fashion, in the Insert drop-down menu in the Cells group of the Home Tab of the Excel Ribbon, “Insert Cells” has also been replaced by “Insert Copied (or Cut) Cells”. When you use the copy command, having inserted cells, to exit copy mode and get rid of that flashing border around the copied cells, simply press the Escape key on your keyboard.

There are essentially two ways of inserting cells into a worksheet: you can either insert entire rows or columns or you can insert individual cells. Whenever you insert entire rows or columns, the number of rows or columns that you highlight will correspond to the number inserted.

For example, let’s say that we want to insert a header at the top of a worksheet we might want to insert two blank rows. To do this, we would highlight the first two rows of the worksheet by clicking and dragging across the appropriate numbers of the rows. Having selected the rows above which you want the new cells to be inserted, you can then do one of two things. Firstly, in the Home Tab of the Excel ribbon, you can move across to the Cells section and choose Insert and then Insert Sheet Rows. Alternatively, you can right-click on the row number of one of the highlighted rows and choose Insert from the context menu.

When you insert new rows or columns, it may seem logical that the format of the inserted cells will correspond to the format of the cells that were highlighted when the new row or column was inserted. In fact, the format is copied from the cells above, in the case of rows, and the cells to the left, in the case of columns.

The right-click method of inserting rows or columns is probably faster. However, the other method offers one benefit: you don’t have to highlight the number of rows or columns you wish to insert. Even if you select just a single cell, you can still use the insert button and choose Insert Sheet Columns or Insert Sheet Rows.

Yet another way of inserting cells is to right-click on a cell and to choose Insert. To insert an entire column or row, simply select the appropriate option from the dialog box which appears then click OK.

Naturally, all of the above techniques apply equally well to deleting rows or columns. For example, let’s say we have three cells selected going across three columns; we can move across to the Cells section of the Home Tab of the Excel ribbon, click on the Delete button and choose Delete Sheet Columns. This command deletes not just the selected cells but the entire columns that they form part of. Similarly, to use the right-click method, we would right-click on the row or column label and choose Delete from the context menu.

The alignment section of the Home Tab of the Excel ribbon contains a number of options relating to the way in which your data is position within the cell. Perhaps the most familiar and the most frequently used buttons in this section are the three relating to the horizontal position your data: left, centre and right. However you’ll notice that when you click in the cells of an unformatted worksheet, none of these three icons is highlighted, which indicates that none of them is the default. The reason for this is that Excel treats data differently depending on the data type.

If you type text in a cell, your text is aligned on the left; if you type a number, the number is aligned on the right; if you type a date, it is also aligned on the right. To change the horizontal alignment, either select a range of cells or click on a column letter to highlight the entire column then click on one of the alignment icons.

Haven chosen one type of horizontal alignment, you can change it in two ways. You can either choose a different alignment or you can click again on the already selected alignment to deactivate it. For example, if your text is right-aligned and you click on the Right-align button a second time, this will deactivate right alignment and return you to the default alignment which, for text, is left-aligned. So, in fact, Excel has four types of horizontal alignment: left, centre, right and unspecified (or default), which is the alignment that applies when none of the alignment buttons is highlighted.

Excel also allows you to specify vertical alignment. This setting normally only becomes apparent when you increase the height of the cell and this time there is a definite default which is that text is aligned at the bottom of the cell. This setting applies to text, dates and numbers alike.

To change vertical alignment, either make a selection or click on the row number to select the entire row then click on one of the buttons to make the change: align middle, align top and so forth.

Excel also features the ability to change the orientation of text within the cell. This is particularly useful in those situations where your column headings are wider than the data in the cells. To change the vertical orientation of your text, just select the cells in question and then choose the appropriate angle in the Alignment dialogue.

Having changed the orientation of the headings, you can probably make the columns much narrower. Excel offers a very useful way of doing this: simply select all the columns that contain data then in the Cell group of the Home Tab of the Excel Ribbon, choose Format and then AutoFit Columns. This option makes each of the highlighted columns no wider than it needs to be to display all the data it contains.

When changing the appearance of text within an Excel worksheet, you have the choice of working at the cell level or at the text level. To work at the cell level you simply highlight one or more cells and choose your format settings. The formatting that you choose will then apply to all text within the cell or cells.

For example, to change the font size of your data in Microsoft Excel, you can of course use the drop-down menu next to the font name and choose one of the preset sizes. Another way of changing the size is to use the Grow Fond and Shrink Font buttons located on the right of the font size. These two buttons simply take you up and down the same preset sizes available on the preset size drop-down. You can also use the cursor keys on your keyboard to move through different sizes. Yet another option is to enter a particular size then press the enter key. When doing so, you are permitted to enter decimals, such as 10.5.

In a similar way, when you wish to choose a font, you can highlight the current name and begin to type the name of font you have in mind, for example Times new Roman. Once you have typed enough of the name to avoid ambiguity, the font name will be displayed and you simply press the Enter key.

Colour can be applied in the same two ways in your worksheets, either as a background to the cell or to the text inside the cell. Since background colour applies to the cell itself, it can’t be applied at the text level. With the introduction of Themes in Excel 2007, Microsoft have made it very easy to come up with colour combinations which look good together.

To work at the text level, you need to be in Edit mode. You can enter Edit mode in two ways: the first is to double-click on any cell; the second is to click once on the cell and then click anywhere in the formula bar. You can tell when you’re in Edit mode by the presence of the Cancel and Enter buttons on the left of the formula bar.

When working in Edit mode, you are able to highlight individual characters and change their attributes. For example, if a cell were to contain the text “Google”, you could highlight each letter individually and change it to the colour used in the Google logo.

The facility of using different formatting within the same cell is very useful for headings. It is not quite so useful for the rest of the data in your worksheets since it slows down data entry and makes editing difficult.