Content for Reprint Blog

Microsoft Excel VBA training is used to teach that Excel is more than just a glorified calculator, which is what many people believe it to be. It is sad but true that in the majority, people do not know or care to understand just how powerful this program can be. By undertaking this training we are able to delve more deeply into the advanced features of Excel.

Within the Excel essentials training programme, you will find material prepared for beginner and advanced users. The result is that you will learn to use VBA from your own present level of expertise or non-expertise. Using a Visual Basic Application (VBA), and even without knowing anything about Excel macros, you will be able to create your own charts that will automatically calculate unfathomable amounts of numbers, and contain massive set of data.

The macro code language of most Microsoft Office programs is Visual Basic. The use of macros allows you to manipulate and automate programs with VB tools. This is ideal for saving regularly used functions or calculations. Visual Basic tools are standard tools for Office programs and are an interface situated in the programming environment. Writing and running our own code for enhanced functions means that every possible task need is met.

Some training assumes that you are already familiar with the powerful functions of Exel, and once you start writing your own code you won’t want to stop. If you are not familiar with the functions of Excel and the idea of having to write your own code puts you off, don’t let it. Training is designed to teach you to navigate the program and use code without having to write it.

The functions of VBA and the value that it adds to almost all Office programs, is therefore attainable by those who do not at all necessarily specialize in programming. In other words you don’t have to be a guru.

The training teaches you why you should used code and where. Screen shots and icons are used in the training modules to keep things simple, and as you are using Excel while you learn, it is a hands-on experience.

The author is a trainer and developer with OnSiteTrainingCourses.Com, an independent computer training company offering Microsoft Excel Classes in London and throughout the UK.

Learning about Microsoft Excel VBA training classes, courses and seminars will be the first step necessary in order to take advantage of the quality training available when it comes to MS Excel and Visual Basic for Applications. Many people mistakenly assume that Excel is nothing more than a kind of high speed calculator, but that couldn’t be further from the truth.

Other people assume that Excel is some sort of extremely simple word processor that can handle tables or even a type of white board that can help set up images and text. Yes, it can do these things (which are quite basic to its program, to be honest) but that is only scratching the surface of the potential of MS Excel.

Just taking a few minutes to read up or look at a website dedicated to MS Excel can show a person a myriad of capabilities that the software suite can execute with extreme competence. VBA training courses are good way to learn just how expert the program is at taking any amount of data and manipulating it such that it can be presented in any way that its user wants it to be displayed.

Those who are most familiar with MS Excel recommend that people who’d like to really learn to power up Visual Basic for Applications within the Excel software program should take a training course or seminar first of all. You won’t need to be a super programming guru or genius when it comes to MS Excel, though. You’ll just need to understand the basics of the software program.

Go online and conduct a search for a training course that takes you by the hand and shows you how to use VBA in such a way that you can command all the features of Excel, MS Word or PowerPoint from it. Mainly, most people will attend a two-day workshop — which is widely available — and which will show a person fairly rapidly how to begin to manipulate software suite.

Taking full advantage of the Visual Basic for Applications capability within Microsoft Excel can be learned fairly quickly by attending a Microsoft Excel VBA training seminar or course. Such a course can teach a person that there is far more within Excel than just the basics which, sad to say, most users only normally take advantage of.

The writer of this article is a trainer and developer with OnSiteTrainingCourses.Com, a UK IT training company offering Microsoft Excel training courses in London and throughout the UK.

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.