Tag: Excel 2007

Building Formulas With The AutoSum Tool

Excel’s AutoSum tool is used to automatically generate formulas. It is found in two locations on the ribbon: firstly in the Editing section of the Home Tab; and, secondly in the Function Library section of the Formulas Tab.

Excel’s AutoSum tool is used to automatically generate formulas. It is found in two locations on the ribbon: firstly in the Editing section of the Home Tab; and, secondly in the Function Library section of the Formulas Tab.

The AutoSum tool has several uses. Firstly you can use it to generate the total or SUM of a series of numbers. To do this, you can select a row of cells and click once on the AutoSum tool. Excel places the total of the highlighted cells in the first available empty cell to the right of the highlighted range. You can then copy the formula by using the Autofill handle. As you drag down, Excel will copy the formula making the changes necessary to return the correct total for each row.

The AutoSum tool can also be used to return the total of several columns simultaneously. To do this, just select all the data in each of the columns you wish to total and then click once on the AutoSum tool. Excel generates a formula at the bottom of each column in the first empty cell.

Clicking on the AutoSum tool always generates a formula containing the SUM function which returns the total of a given range of cells. However the AutoSum tool can also be used to generate other functions, such as AVERAGE. To access the other functions, click on the drop-down menu to the right of the AutoSum tool, choose a function and then make sure that Excel has correctly guessed the range of cells you wish to apply the function to.

If Excel has failed to guess, you can correct it: either by typing the correct reference; or simply resize the selection rectangle until it encloses the correct range of cells then press the Enter key or click on the Enter button located on the left of the formula bar. The formula can then be copied using the AutoFill handle.

The AVERAGE function is famous for generating too many decimal places. If you want the number of decimals to be consistent, click on the Launch button in the Number section of the Home Tab, choose “Number” as the format and then choose the number of decimal places you like.

The remaining functions available on the AutoSum tool are COUNT, which tells you the number of cells in the highlighted range containing numbers; MAX, which returns the highest value in the range; and MIN which brings up the lowest value. The final option in the AutoSum tool drop-down menu reads “Other Functions”. It gives you full access to Excel’s dazzling range of functions.

To learn more about Excel VBA training courses, visit Macresource Computer Training, an independent computer training company offering Excel VBA training courses at their central London training centre.

Microsoft Excel In-house Training Checklist

Class sizes

Class sizes

As to the size of the class, somewhere between five and ten people can usually be accommodating in a training room with reasonable facilities. You will need to ensure that you have a conference or training room equipped with a computer for each delegate and a projector which can be connected to a computer used by the trainer to demonstrate each technique.

A general rule of thumb is that the bigger the leap you are asking people to make, the smaller the class needs to be. For example, if you decide to get your staff trained on the use of Excel macros and none of them have ever done any programming, then you should restrict the size of the group to between three and six people. By contrast, if you have a group of people who have been using the program for some time, but have never been formally trained and need to be given a better idea of what the program can do, you could probably have a larger group of, say, between seven and ten people.

Class sizes

As to the size of the class, somewhere between five and ten people can usually be accommodating in a training room with reasonable facilities. You will need to ensure that you have a conference or training room equipped with a computer for each delegate and a projector which can be connected to a computer used by the trainer to demonstrate each technique.

Which topics should be covered

Which version of Excel?

One feature that you should almost never omit in an Excel training course is formulas and functions. Functions are the lifeblood of Microsoft Excel. They allow the program to carry out complex calculations and produce useful results in a variety of different areas including statistics, engineering and the financial arena. Be sure to give the training company a good idea of the type of data your people work with and the information they need to obtain. This will enable the trainer to include coverage of functions which can help increase your productivity and get the most out of Excel.

Which version of Excel?

As to the size of the class, somewhere between five and ten people can usually be accommodating in a training room with reasonable facilities. You will need to ensure that you have a conference or training room equipped with a computer for each delegate and a projector which can be connected to a computer used by the trainer to demonstrate each technique.

Ideally, training courses should be attended by users with a similar level of experience and with similar requirements. If you have a mixture of skills levels, it is best to split the training into separate sessions to cater for people’s different needs. It is also important to limit attendance to those people who can attend for the entire duration of the training. Having delegates nip in and out of a training session is disruptive and doesn’t really benefit anyone.

To learn more about Excel VBA training courses, visit Macresource Computer Training, a UK IT training company offering Excel VBA Classes in London and throughout the UK.

Microsoft Excel 2007 Navigation Tips And Techniques

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.

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 worksheet tabs will find four navigation icons. These are useful where you have a workbook that either contains lots of worksheets or has worksheets with very long names. The very first icon makes the name of the first worksheet visible; the very last icon makes the name of the last worksheet visible. The left pointing arrow reveals the name of the previous worksheet and of course the right pointing arrow reveals the name of the next worksheet. These icons do not actually activate a worksheet; they simply make its name tab visible. To activate a worksheet, you still have to click on that particular tab.

Worksheets can also be activated via the keyboard. To activate the next sheet to the right, hold down Control and press Page Down. This moves you forward through your worksheets are naturally holding Control and pressing the Page Up button 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 allows you to use 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 with the mouse. Position the cursor on one of the edges of the selection rectangle (that bold highlight which is displayed around the currently active cell) and then you simply double-click. Double-clicking on the right hand edge of the selection rectangle takes you to the extreme right of the current range. Double-clicking on the bottom edge jumps to the bottom of the range, and so forth.

There are two final navigation keyboard shortcuts which should be mentioned: Control-Home and Control-End. Hold down the Control key and press End 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 letters and numbers is then displayed which represent the shortcuts 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”, the View Tab is activated and another series of badges is displayed on each of the commands within the View Tab. For example, the “Arrange All” command uses “A” as its keyboard shortcut, so simply typing “A” is equivalent to clicking the Arrange All button.

Once you have typed a letter to execute a command, the Ribbon loses focus and the shortcut letters and numbers disappear. To access Ribbon commands via the keyboard again, just 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 then on.

If you would like to learn more about Excel VBA training courses, visit Macresource Computer Training, a UK IT training company offering Excel VBA Classes at their central London training centre.