HJDS Computer Services Computer Systems Support and Installation.

One of the pivotal components of the Microsoft Office 2007, Excel is a uniquely powerful spreadsheet. If you bought this sophisticated piece of software, it makes sense to ensure that your staff members know how to use it effectively. Having allowed them a week or two to get used to the new environment and go through some online tutorials, you will probably want to get them properly trained. Tutor-led software training has the benefit that delegates are able to ask questions as they learn and have complex concepts explained and demonstrated to them until they fully understand them.

Sending your people on a public Excel course is one possibility. However, increasingly companies are demanding to have this training customised to meet their specific demands. Microsoft Excel can be used for a variety of data analysis and storage tasks: not everyone uses it in the same way. Perhaps you will be using it for complex business modelling. Or, you may be using it to create interactive forms and reports complete with complex calculations. Maybe your staff will be using the program in a database role recording information under column headings. Booking a customised course will ensure that you only pay for instruction which is relevant to your requirements and reflects the way in which you will be using Microsoft Excel.

Before you start contacting Excel training companies, it would be a good idea to ensure that you have a clear idea of what you want to achieve by using Excel and that your expectations are realistic. When you approach training companies, you should make it clear that you do not simply want them to deliver their standard Excel courses but that you require a customised programme of training. Between you, a schedule of topics to be covered should then be drawn up and the duration of the program decided.

The customisation process may also involve identifying different requirements within your own organisation. Different people may need to do different tasks with the program and therefore need different skills. For example, some of your users will be primarily interested in using Excel for business analysis and projection. Their primary areas of interest will be the “What if” analysis tool like goal seek, scenarios and pivot tables. On the other hand, you may have people who are interested in create charts and reports either for printing or for use in PowerPoint presentations.

Most training companies offering customised Excel courses should be willing to accommodate the specific needs of your organisation and the different profiles of the staff members: accounts, sales and marketing, etc. Between you, you can then create a program of study which satisfies the needs of all users. Perhaps this may mean, having different courses for users with different profiles or perhaps the best approach will be a modular one whereby some modules are taken by everyone while others are only attended by certain user groups.

Posted under Software

Each Excel document is called a workbook and each workbook can contain up to 255 worksheets. To navigate to a particular worksheet, click on one of the tabs displayed at the bottom of your screen.

To the left of the tabs will find four navigation icons. These are very useful where you have a workbook that either contains lots of worksheets or has worksheets with very long names. The very first button makes the name of the first worksheet visible; the very last button makes the name of the last worksheet visible. The left pointing arrow button makes the name of the previous worksheet visible and of course the right pointing arrow button makes the name of the next worksheet visible. These four buttons don’t actually activate a worksheet; they simply make its tab visible. To activate a worksheet, you still have to click on that particular name tab.

Worksheets can also be activated from the keyboard. To activate the next worksheet to the right, hold down the Control key and press Page Down. This moves you forward through your worksheets are naturally holding the Control key and pressing Page Up moves you back to the left.

Once you have navigated to a particular worksheet, you will need to go to a particular cell or a particular section of that worksheet. Firstly, you can use the scrollbars to make different parts of the worksheet visible. Secondly, you can move around the worksheet using the arrows on your keyboard: down, right, up and left.

Excel also contains useful keyboard shortcuts for moving to the edges of a given body of data. To get to the right-most cell of the current range, hold down Control and press the right arrow and of course to get to the bottom cell, hold down Control and press the down arrow.

It is also possible to do exactly the same thing using the mouse. Position the cursor on one of the edges of the bold selection rectangle surrounding the active cell and then simply double-click. Double-clicking on the right hand edge of the selection rectangle activates the extreme right of the current range. Double-clicking on the bottom edge moves the cursor to the bottom edge of the range, and so on.

There are two final navigation key combinations which should be mentioned: Control-Home and Control-End. Hold down the Control key and press the End key to move to the bottom right of the current range. Hold down Control and press Home to move to the top left of the current range.

As well as navigating through worksheets, all users of Excel make frequent use of the Ribbon. Excel offers a series of useful keyboard shortcuts when working with the Ribbon.

To access the ribbon keyboard shortcuts simply press the Alt key once on your keyboard. A series of badges are then displayed which represent the letters or numbers that you should type to activate that part of the Ribbon. For example, “W” is the shortcut for accessing the View Tab.

When you press “W” and the View Tab becomes active, another series of badges is displayed on each of the commands within the View Tab. For example, the “Arrange All” command has “A” as its keyboard shortcut, so simply typing “A” is equivalent to clicking the Arrange All button.

Once you’ve typed a letter to execute a command, the Ribbon loses focus and the shortcut badges disappear. To access Ribbon commands via the keyboard once more, simply press the Alt Key and the badges will reappear. This means that you never have to worry about learning keyboard shortcuts. All you have to remember is to press the Alt key on your keyboard and Excel will prompt you from there.

Posted under Software

When working in Microsoft Excel, it is very likely that you will sometimes need to open more than one workbook at a time. Excel allows you to do this and to navigate between the open workbooks in a number of ways.

To open several Excel documents, click on the Office button and choose “Open. Naturally, you can only open several workbooks at once if they are in the same folder. To highlight a range of workbooks, click on the name of the first, hold down the Shift key on the keyboard and click on the name of the last.

To select individual files in an arbitrary fashion, click on the first file, hold down the Control key, click on the second, third, and so forth. You can also drag a selection rectangle around a series of files to highlight them. When you do so, make sure you start in blank space rather than starting on an item. Having highlighted the files that you want to open, click on the Open button.

Excel will then open each of the selected files in a maximised window. This means that you can only see one workbook at a time. To switch between workbooks, you can use the Windows taskbar and choose a particular name. You can also click on the View tab of the Excel Ribbon and here you’ll find the Switch Windows button. This contains a drop-down list of all the windows you currently have open. You can simply select a name to activate it.

The Window group of the View tab also contains an option for tiling your Windows. Just click on the button marked Arrange All and choose Tiled. When you click OK, Excel arranges all the open documents into separate small windows so that you can see the contents of all files simultaneously. To activate a file, simply click on any part of its window.

To exit Excel’s tiled mode, click on the maximise button of any of the open documents. This action maximises all the open windows so when you switch windows, you will see that all of them have been maximised.

Regardless of which display mode is currently active, you can always use the keyboard to switch between the various files that you have open in Excel at any given time. To do this, hold down Control and press the Tab key.

A particularly nice feature of Excel is the ability to switch workbooks when you are in the middle of creating a formula. This allows you to create formulas with external references. For example, if you are creating a formula which uses the VLOOKUP function but the lookup table resides in a separate workbook, just make sure that both workbooks are open before you start creating the formula. At the point where you need to enter the location of the lookup table, use any of the techniques discussed above to switch workbooks and drag across the cells containing the lookup table.

Posted under Software

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 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 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, 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 components within a chart. However, Excel allows you to customise each of these components and add other elements which enable you to create charts which convey exactly the message you have in mind.

Posted under Software

Excel’s page formatting features are accessed by clicking on the page layout tab of the Excel ribbon. When working with page formatting, you may also find it useful to enter page layout mode by clicking on the page layout button in the status bar. Adjust the zoom as required and you now have a constantly updated preview of how your document will look when it prints out.

Excel also shows you the number of pages required to print a document on the status bar. Some documents are easier to print by changing the orientation to landscape. This often enables you to fit all the columns in your worksheet onto a single page. To change the page orientation, choose Orientation and then Landscape.

Excel offers three methods of changing the margins. The first is to click on the Margins drop-down and choose one of the presets. There are four options: the last custom setting used, normal, wide and narrow. If none of these settings is ideal for your data, the second method of modifying margins is to enter custom settings. This is done by choosing Custom Margins: the last option in the Margins drop-down menu.

When entering margin settings in this window, it is important to realise that there’s a difference between left and right margins and also top and bottom margins. The figure you enter in the left and top boxes will be faithfully reproduced by Excel. So, for example, if we set the left margin to 3 cm, you will have precisely 3 cm on the left-hand margin. However, because Excel never prints a fragment of a row or a fragment of a column and only prints complete rows and columns, the figure you enter on the right will be the minimum margin rather than a figure which Excel can faithfully reproduce each time. And the same applies to the bottom margin setting.

The third method of modifying margins is perhaps the best of all. It’s also the most interactive. Simply position the cursor on the left of the ruler and drag to the left or right to change the margins. Excel immediately updates the preview of your page and shows you the actual margin setting. You can continue dragging until you are happy with the margins.

Another simple way of changing the way in which your data prints is to change the paper size. In many cases, you can reduce the number of pages required simply by using A3 paper instead of A4. Naturally, it’s only possible to change the paper size in this way if you have a printer capable of printing that paper size. If you output most of your documents to PDF, paper size will never be a problem and altering the paper size in this way is often a good solution.

Posted under Software

Most of the worksheets that are created Excel contain headings in the top row of the sheet. Usually, when we scroll down the sheet, any headings at the top will simply disappear. In the same way, if we scroll to the right, any headings on the left will disappear. The Freeze Panes command, which is located in the View Tab of the Excel Ribbon, allows us to freeze our headings so that, as we scroll the worksheet, our headings remain in view.

Excel gives us three options: firstly, we can choose “Freeze Top Row”. A bold horizontal line is then visible underneath the first row which extends into the row headings. As we scroll down the worksheet, the headings at the top of the sheet will now remain in view. Similarly, we can use the “Freeze First Column” command. This time, the bold line will extend to the right of the first column and into the column heading area. Then, as we scroll to the right of the worksheet, the first column remains frozen so that we can see the headings it contains and compare them with data in the adjacent cells. To return to normal scrolling, we simply use the “Unfreeze Panes” command 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, you should see two bold lines: one indicating the column that is frozen and one indicating the row that is frozen. Then, when scrolling down, the first row remains frozen and, similarly, when scrolling to the right, the first column remains frozen. Once again, to normal scrolling, simply choose “Unfreeze Panes” in the “Freeze Panes” drop-down menu.

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

Posted under Software

Microsoft Excel allows you to change the order of worksheets within a workbook at any time. There are two ways to do this, the first of which is simply to drag the tabs representing each worksheet to the left or right. Not only can you drag individual tabs, it is also possible to select several tabs and drag them all at the same time.

As well as moving worksheets around within the same workbook, it is also possible to move sheets from one workbook to another. For example, let’s say we 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 for “Jan”, “Feb” and “Mar”; the second for “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 is to highlight the three worksheets relating to the first quarter: we click on “Jan” (the first), hold down the Shift key and click on “Mar” (the last). We can then drag the selected sheets across to the window of one of our new workbooks. We can the simply repeat this procedure for the three remaining 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 in our procedure would be to delete the unwanted sheet from each of the four new workbooks. Once we have done this, to leave the split screen view and return to normal mode, we simply maximise any of the open 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.

Posted under Software

A Microsoft Excel workbook is really a container, a bit like a folder. Each Excel workbook contains one or more worksheets and it is the worksheet that is the actual container of your information. Worksheets are identified by a tab which carries the name of the sheet. Clicking a tab will activate that 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, just right-click on the sheet tab and choose Hide. The corresponding worksheet will then vanish. There is also a ribbon command which achieves the same thing. First, select the sheet by clicking on its tab and then, in the Cells section of the Home Tab of Excel Ribbon, choose Format-Visibility-Hide and Unhide-Hide.

To hide more than one sheet, simply highlight the sheets by clicking on the first holding down the Control key and clicking on each of the others. Next, right-click on one 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 box will then appear. Unfortunately, it is not possible to select multiple sheets to unhide; if you try Control-click or Shift-click, you’ll soon see that only one sheet can be highlighted. Highlight the name of the sheet that you would like to make visible and click the OK button.

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.

Posted under Software

As well as using and modifying Excel’s own built-in cell styles, you also have the option of creating your own. One convenient method of doing this is to select a range of cells and apply all the formatting attributes that you want to add to your style and then convert those attributes into a cell style. When you create a cell style, Excel will pick up all the attributes from the selected cells and include them in the style. Let’s say, by way of illustration, we want to create a cell style for headings with text angled at 45 degrees.

We set the text angle to 45 degrees by choosing “Angle Counter Clockwise” from the Alignment section of the Home Tab of the Excel Ribbon. Let’s say that we also want to centre the text horizontally and vertically, change the text colour and make the text bold and slightly larger. Having chosen the relevant options, to create a style which has all these attributes, simply highlight a cell that contains these formats and choose “New Cell Style” from the Cell Styles drop-down menu in the Styles Tab of the Excel Ribbon.

The Style dialogue box will then appear. Here, we can specify a name for our new style: let’s say we call it “Angled Heading”. The Style dialogue box contains six categories of formatting information which can be activated or deactivated via a series of checkboxes. We can switch off any categories of formatting that don’t apply. Thus, for our heading style, we can deactivate the categories “Number”, “Border”, “Fill”, or “Protection”; so we can uncheck all of them. Next to each of the categories, Excel lists the relevant attributes of the highlighted cell(s); so it’s not necessary to click on each category to set any options; all we need to do is click OK.

When creating a style in this way, it’s important to realise that Excel does not apply the style to the cell or cells on which the style was based. If you want to apply the style to the cell, you need to do so explicitly by highlighting the cell and choosing the name of your new cell style from the Cell Styles drop-down menu in the Styles section of the Home Tab of the Excel Ribbon. Having created a style you’ll notice that Excel has an extra category called custom in the Cell Styles drop-down menu and it is here that you can choose the style that you just created. Although the highlighted cell may look the same, it will now be associated with the new style.

We would then proceed to apply the style wherever it was needed throughout our workbook. This, in itself, is a big time-saver. In addition, however, if we decide to change the format of our heading, all we need to do is to modify the attributes associated with our cell style and all of our headings will be automatically updated.

Posted under Software

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 main modifier key which can be used in conjunction with the mouse to make selections is Control. Whereas the mouse and Shift key combination only permits us to make rectangular selections, if we use the mouse in conjunction with the Control key, we can make non-contiguous selections; in other words, we can highlight cells which are not adjacent to each other. Once you have highlighted a region, the next operation you perform will only apply to the highlighted cells. For example, you might want to copy the cells and paste them into a new worksheet.

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

It is also possible to make selections using just the keyboard and no mouse. Only contiguous ranges of cells can be selected in this way. All you need to do is hold down the Shift key while pressing one of the cursor keys. For example, if you press the right cursor key, you will select to the right; pressing the down cursor key will select adjacent rows; and so on. You can also move back in the opposite direction to remove cells from the selection.

To highlight 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, to select all the cells up to the right edge, press the right cursor key; to select all the cells to the bottom, press the down arrow; and so forth.

As well as pressing the cursor keys, you can use the End key. Hold down Control and Shift and press the End key to select all the cells to the bottom right of the current range.

Posted under Software