HJDS Computer Services Computer Systems Support and Installation.

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

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