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

When entering numbers into a spreadsheet, one often needs to ensure that the number format is consistent. For example, if the numbers represent prices, you may want to display the appropriate currency symbol or you may simply want to ensure that the number of decimals displayed is always the same.

Unless you specify otherwise, all numbers in Excel are rendered in the “General” format. This means that numbers are displayed exactly as you enter them: if you enter two decimals, two decimals are displayed; if you went to one decimal, one decimal is displayed; and so on.

When specifying the number format, the best idea is usually to select the whole column. To do this, click on the letter or letters representing the column. (Any text contained in the selection will not be affected by the number format you specify.)

Number formats are specified in the “Numbers” group of the Home Tab of Excel’s Ribbon. There are three important formats which apply to numbers: the first is simply called “Number”, the second “Currency” and the third “Accounting”. To gain access to the complete range of number formats, click on “More Number Formats” in the “Numbers” drop-down menu. Another way of opening the “Numbers” dialog box is to click on the launch button in the “Numbers” group of the Home Tab of the Excel Ribbon.

When you click on each of the number formats, you are presented with a series of choices which enable you to refine the way that the format will work. For example, if our numbers refer to an hourly rate, we would probably click the “Number” category in the left column and then specify two decimal places. The option labelled “Use Thousands Separator” will insert the appropriate separator to demarcate thousands. The separator which Excel uses will depend on your locality: for example, if you are in the UK or USA, a comma will be used; if you are in a European country, a dot will be used.

The final option in the “Number” category controls the display of negative numbers. The default is to display a minus sign in front of the number and leave the colour of the number unchanged. However, you can also dispense with the minus sign and change the colour of negative numbers to red; or you can both change the colour of negative numbers to red and display the minus sign.

When we click the “Currency” category, we have pretty much the same choices with the addition of the currency symbol. We can specify which currency symbol is used or we can dispense with the symbol altogether.

The “Accounting” category is pretty much the same as “Currency”. Once again, you can choose a particular currency symbol. However, you will notice that you do not have any choices relating to negative numbers. The convention in accountancy circles is to always place negative numbers in brackets.

As an alternative to using the number dialog box, you can also click on one of the series of handy buttons which are used to apply each of the number formats with single click. There are also two buttons for decreasing and increasing the number of decimals displayed in the highlighted cells.

Finally, there will be times where you enter a number into a cell but do not want Excel to regard it as a number. For example, if you have a column of data with an ID of some sort, although the ID may be numeric, you may not want Excel to see it as a number or to change it in any way. You will probably want the ID to simply stay exactly as it was entered. In this scenario, it’s best to format the number as “Text”. The easiest way of doing this is to highlight the appropriate column and in the number dialog box select the “Text” category.

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

Upgrading to Excel 2007 may be something of a shock to you and your staff. The initial reaction of most people is: “where is everything?” Bearing this in mind, you may well find that a training course on Excel 2007 is a good investment. The training should first of all get you past the initial state of confusion caused by the fact that 2007 looks so different from previous versions. Then it should give you some guidance on the new features in Excel 2007 such as the enhancements to charting and graphics, functions and conditional formatting.

One of the first things you should look for in having training on Excel 2007 is a full explanation of how the new interface works. You should be shown the new way of working and learn useful tips and shortcuts which will enable you to become at least as productive in Excel 2007 as you were in 2003.

In addition to this, however, you will want to learn the new features that Excel 2007 has to offer: the stuff that either wasn’t available in previous versions or which has undergone considerable enhancement.

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.

Pivot tables have been considerably improved in Excel 2007. However, given that so many users are a bit vague on getting the best out of pivot tables, why not ask that your training on pivot tables begins with a review of fundamental pivot table concepts before moving on to look at how Excel 2007 implements pivot table features.

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.

Your Excel 2007 training course should also cover conditional formatting. This is a feature that has been much enhanced in Excel 2007 and your training should show you how to exploit the new features available. Make sure you will come away from the training knowing all about Data Bars and Color Scale.

An Excel spreadsheet without formulas and functions is not much use to anyone. Functions are what Excel is all about. Microsoft have improved the way in which function are entered and edited and added several new functions. When you book training on Excel 2007, make sure that your course will include coverage of new functions like SumIfs, IfError and AverageIf as well as a demonstration of the improvements to the editing of formulas.

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