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.

Microsoft Office Excel 2007 contains no menus or toolbars. However, it does include a feature called the Office Button which bears a remarkable similarity to a menu as well as a Quick Access Toolbar which functions a lot like the toolbars found in previous releases of Microsoft Office. In this article, we’ll take a look at these two features and see how they work.

Excel’s Office Button

The Office Button is found in the top left of the Excel screen. When you click it, a menu appears which resembles menus found in most software programs and also in previous versions of Excel. The Office Button menu allows you to create, open, save and print documents. It also contains options for preparing documents for distribution, closing the currently active document and exiting Excel altogether.

The Office Button also contains a list of the recently opened files. Next to the name of each file is a pin icon. This icon is used to pin the associated document. Pinning a document means that it won’t disappear from the list of recently opened files. By contrast, unpinned documents will eventually disappear from the list as files are created and opened.

It is also here that you will find “Excel Options”. (This is equivalent to Tools-Options found in previous versions of Excel.) Excel Options are displayed as a column of categorised tabs on the left of the dialog. Just click on a tab to reveal options in that category. The Excel Options customise the way in which Excel works. Let’s look at a few examples…

Excel’s Developer Tab (which contains commands for working with macros) is not visible when Excel is first installed. To make Excel display it, choose “Excel Options” from the Office Button and, in the “Popular” category, switch on the option “Show Developer Tab in the Ribbon”. A second example… By default, each new document you create has three sheets. If you find this too many or too few, you can change the number of sheets which each new work will contain. Choose “Excel Options” from the Office Button and, in the “Popular” category, under the section headed “When Creating New Workbooks Include this Many Sheets”, simply enter your preferred number of sheets for each new workbook you create.

The Excel Quick Access Toolbar

The Excel Quick Access Toolbar is usually displayed in the top left of the screen, next to the Office Button. It functions in a similar way to the toolbars found in old versions of Microsoft Excel.

By default, the Quick Access Toolbar contains only three commands: saving the current workbook, undoing commands and redoing commands.

To the right of the three default commands is a menu which enables you to personalise the Quick Access Toolbar and determine which buttons are displayed and which commands remain hidden.

Each of the commands in the Quick Access Toolbar menu is a switch: if a command is active, when you select it, it will be removed: if a command is hidden, choosing it will make it visible.

Another method of customising the Quick Access Toolbar is to add your favourite options from the Excel Ribbon. Just right click on an option in any of the Tabs of the Ribbon. In the context menu which pops up, simply choose “Add To Quick Access Toolbar”.

If you find that you are using the Quick Access Toolbar a lot, you may find it convenient to move it below the Ribbon. Simply choose “Show Below The Ribbon” from the Quick Access Toolbar drop-down menu. You can also make the Quick Access Toolbar more accessible by minimising the ribbon. To do this, double-click on the name of any ribbon Tab or choose “Minimise the Ribbon” from the Quick Access Toolbar menu.

When you’ve taken the plunge and upgraded to Excel 2007, you will almost certainly benefit from some training. Which type of training you choose depends on your existing experience in using the program. However, in general, the ideal course will first of all rid you of the disorientation most Excel users feel when faced with the Excel 2007 interface. In addition, your Excel 2007 training course should show you how to make the most of the new features version 2007 has to offer.

At the end of any training on Excel 2007, you should feel that you have grasped the logic of the new interface and be able to navigate the ribbon and find all the basic commands. You should be getting used to doing things the Office 2007 way rather than the Office 2003 way.

However, after being trained on 2007, you will want to do more than just get back to where you were with 2003. You will want the training to show you what new features have been added to Excel 2007 and how these new features can benefit you.

One fundamental new feature in Excel 2007 is the dimension of a worksheet which is now about 1000 times bigger (in terms of the number of cells) than previous versions. A good Excel 2007 training course should show you how to fully exploit the space available and how to quickly navigate and manage the larger worksheets that will result.

Your training should also include demonstrations and explanations of pivot tables, a feature which has been revamped and improved in Excel 2007. If you or your colleagues have not used pivot tables to any great extent before, the training should begin by reviewing basic pivot table concepts before moving on to look at what Excel 2007 has to offer.

Charts and graphics are a great way to add impact to your Excel reports. Does your organisation use them? If so, make sure that your Excel 2007 training course incorporates gives you plenty of practice examples in using Excel 2007’s new features to create and manipulate charts and graphics. You should become a dab hand at using the new charting ribbons: the format ribbon, the design ribbon and the layout ribbon. Do you need advanced features too? If so, you should also be looking to learn about pivot charts, scatter charts and adding trendlines to your charts.

Conditional formatting has been around in Excel for some time. However, this feature has now been considerably improved. If you are getting trained on Excel 2007, you should definitely make sure that you will learn about these improvements and how to use the Color Scale and Data Bars features.

Formulas are the lifeblood of Excel. They analyse the raw data entered in other cells and provide most of Excel’s awesome power. You should definitely request that your Excel 2007 training shows you the improvements that have been made to the entry of formulas in the formula bar and the key new functions that have been introduced, especially AverageIf, SumIfs, and IfError

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.