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

Excel contains a very wide variety of formats that you can apply to your cells. Naturally, when you create your worksheets you want your formatting to be consistent. Cell Styles offer an excellent way of achieving this.

Cell Styles are located in the Style section of the Home Tab of Excel’s Ribbon. When you click the Cell Styles button, the categorised Cell Styles gallery is revealed. The gallery provides a live preview; just hover over any of the styles to temporarily change the current selection within your worksheets, giving you a preview of what your data will look like if you choose that particular style.

The first benefit of using styles is that you apply several formats in one operation. For example, say we choose the style called “Heading One”; we automatically change the size, alignment and colour of our text. We can speed up the formatting process even further by using the Format Painter to copy styles into the other cells or even other worksheets. Simply highlight an area that already has a cell style applied, double-click on the Format Painter to make sure it stays highlighted then drag across the appropriate range(s) of cells. When you have finished, click once on the Format Painter to deactivate it.

Having applied Cell Styles in various areas of a workbook, you can take advantage of the most important benefit offered by styles; if you modify the attributes associated with any of the styles used in a workbook, the formatting of all cells to which that style has been applied will automatically be updated.

As for modifying the defintion of a style, since we’re not directly applying formats to any of the cells, it doesn’t matter which cells are highlighted. To modify the a style’s attributes, click on the Cell Styles button, right click on the name of the style and then choose “Modify”. You will then be offered the six categories of formats which can be included in a style: Number, Alignment, Font, Border, Fill and Protection. The list is pretty comprehensive and includes just about everything that Excel has to offer in the way of formatting.

You now have the facility of activating and deactivating categories as necessary. Any categories that do not apply to a particular style can simply remain inactive (i.e., checkbox not ticked). Next, click on each relevant category and make your selections. When you click OK to confirm these changes, all cells to which the style has been applied will be automatically updated.

Posted under Software

Most Excel users have encountered the SUM function. It is one of the most widely used functions within the program. And most Excel users will also have used the IF function at some stage. The SUMIF function is really just a combination of SUM and If which allows us to calculate the total of all cells within a given range that match a certain condition.

For example, let’s say that, in a “Sales” worksheet, we have a breakdown of the sales of each individual salesperson. We then want to create a summary of these figures in a “Branches” worksheet in which we want to create a total sales figure for each branch; so our “Branches” worksheet would have two columns: “Branch” and “Total Sales”. We can use the SUMIF function to calculate the figures in the “Total Sales” column.

A good first step would be to create named cells so that we can refer to these names in our formula. To name a range of cells begin by selecting the range then click on the name box in the top left of the worksheet, enter a name then press the Enter key.

Once we have inserted the names of all the branches in the first column of our “Branches” worksheet, we would highlight the first cell in the “Total Sales” column, adjacent to the cell containing the name of our first branch; let’s say our first branch is “Birmingham”. When using functions for the first time, it’s always useful to use Excel’s Insert Function facility. To access this, click the Insert Function button on left of the formula bar. The SUMIF function is to be found in the “Math and Trig” category. Scroll down the list, highlight SUMIF and then click OK. Excel will then prompt us for the three arguments required by the SUMIF function.

The first argument is the range of cells that we want to evaluate. In our branch sales example it would be the column containing the names of the branches. If we have named this column, we can insert this name by clicking on “Use In Formula” in the Formulas Tab at the Excel Ribbon. This is a drop-down menu containing all the names in the workbook.

The second argument is the criteria we want to match. In our example, is simply the contents of the cell in the adjacent “Branch” column, which in this case contains “Birmingham”. We can click in the cell to pick up the reference.

The final argument is the SUM range and, in this case, it will be the column that contains the cells that we want to actually total; namely, the sales figures. Again, if we have named this column, we would click on “Use In Formula” and choose the name that we created earlier. Having specified the three arguments we click OK and Excel creates the formula.

It’s now safe to copy the formula down. The cell reference of the adjacent column containing the branch name will change but the two named ranges will remain the same. To copy the formula down, simply position the cursor on the AutoFill handle in the bottom right of the cell and then either drag or simply double-click.

Posted under Software

Excel contains a wide range of functions which are conveniently arranged into categories. The COUNT functions are found in the statistical category. There are five of them; COUNT, COUNTA, COUNTBLANK, COUNTIF and COUNTIFS. We will examine the COUNTIF and COUNTIFS functions in another article. In this article, we will be looking at COUNT, COUNTA and COUNTBLANK.

The COUNT function is used to return the number of cells within a given range or series of ranges which contain numbers. The COUNTA function simply counts the cells which aren’t blank; while the COUNTBLANK function does the reverse; it counts the cells in a range which are black.

Suppose we have a spreadsheet containing the scores achieved by students in a variety of subjects. Let’s say that, for each student, we can input one of three entries under each subject: firstly, a figure representing the score achieved; secondly, the letter “X” indicating a subject they were scheduled to take but missed; and, thirdly, the cell can be left blank, indicating a subject that they were never scheduled to take.

In order to count the number of exams each student took, we would use the COUNT function. To count the number of exams each student was scheduled to take, we would use COUNTA. Finally, to calculate the number of exams they were not scheduled to take, we would use COUNTBLANK.

To build the formula for calculating the number of exams taken, we can click in the appropriate call and then click the Insert Function button on the left of the formula bar. Next, we would highlight the statistical category and scroll down to “COUNT”. As always Excel reminds us of what this function does: it “counts the number of cells in a range that contain numbers”.

When we click OK, the Function Wizard is launched and we are prompted to specify the parameters required by the function. Almost always, these parameters will consist of cell references. We can simply drag across the cells we want to count and Excel will create the reference for us. We can then click OK and Excel Will work out the correct value.

Next, we would repeat the same procedure to create a formula containing the COUNTA function and another for containing COUNTBLANK. Finally, we would copy the formula down by highlighting the cells containing the original formulas and dragging the AutoFill handle down.

Posted under Software

When formatting text within an Excel worksheet, you have the choice of working at the cell level or at the text level. If you wish to work at the cell level you simply highlight one or more cells and choose your format settings. Any formatting that you choose will then apply to all text within the cell or cells.

For example, if you wish to change the font size of your data in Microsoft Excel, you can use the drop-down menu next to the font name and choose one of the preset sizes. You can also use the Grow Fond and Shrink Font buttons located on the right of the font size. These two buttons simply cycle through the same preset sizes available on the preset size drop-down. You can also use the up and down arrows on your keyboard to move through different sizes. Yet another option is to enter a specific size then press the enter key. When doing so, you are permitted to enter decimals, such as 10.5.

In a similar way, when you wish to choose a font, you can highlight the current name and begin to type the name of font you have in mind, for example Times new Roman. Once you have typed enough of the name to avoid ambiguity, the font name will be displayed and you simply press the Enter key.

Colour can be applied in two ways in your worksheets either as a background to the cell or to the text inside the cell. Because background colour applies to the cell it can’t be applied at the text level. With the advent of Themes in Excel 2007, Microsoft have made it very easy to come up with colour combinations which look good together.

To work at the text level, you need to go into Edit mode. You can enter Edit mode in a couple of ways: the first is to double-click on any cell; the second is to click once on the cell and then click anywhere in the formula bar. You will know when you’re in Edit mode by the appearance of the Cancel and Enter buttons on the left of the formula bar.

When working in Edit mode, you are able to highlight individual characters and change their attributes. For example, if a cell were to contain the text “Google”, you could highlight each letter individually and change it to the colour used in the Google logo.

The facility of having different formats within the same cell is very useful for headings. It is less useful for the rest of the data in your worksheets since it slows down data entry and makes editing difficult.

Posted under Software

The alignment section of the Home Tab of the Excel ribbon contains a number of options relating to the way in which your data is position within the cell. Perhaps the most familiar and the most frequently used buttons in this section are the three relating to the horizontal position your data: left, centre and right. However you’ll notice that when you click in the cells of an unformatted worksheet, none of these three icons is highlighted, which indicates that none of them is the default. The reason for this is that Excel treats data differently depending on the data type.

If you type text in a cell, your text is aligned on the left; if you type a number, the number is aligned on the right; if you type a date, it is also aligned on the right. To change the horizontal alignment, either select a range of cells or click on a column letter to highlight the entire column then click on one of the alignment icons.

Haven chosen one type of horizontal alignment, you can change it in two ways. You can either choose a different alignment or you can click again on the already selected alignment to deactivate it. For example, if your text is right-aligned and you click on the Right-align button a second time, this will deactivate right alignment and return you to the default alignment which, for text, is left-aligned. So, in fact, Excel has four types of horizontal alignment: left, centre, right and unspecified (or default), which is the alignment that applies when none of the alignment buttons is highlighted.

Excel also allows you to specify vertical alignment. This setting normally only becomes apparent when you increase the height of the cell and this time there is a definite default which is that text is aligned at the bottom of the cell. This setting applies to text, dates and numbers alike.

To change vertical alignment, either make a selection or click on the row number to select the entire row then click on one of the buttons to make the change: align middle, align top and so forth.

The alignment option also includes the ability to change the orientation of text within the cell. This is particularly useful in those situations where the headings are wider than the data within the cells. To change the vertical orientation of your text, you simply select the cells in question and then choose the appropriate orientation in the Alignment dialogue.

Having changed the orientation of the headings, you can probably make the columns much narrower. Excel offers a very useful way of doing this: simply select all the columns that contain data then in the Cell group of the Home Tab of the Excel Ribbon, choose Format and then AutoFit Columns. This option makes each of the highlighted columns no wider than it needs to be to display all the data it contains.

Posted under Software

Almost everyone has encountered the SUM function. It is one of the most widely used functions within Excel. And most Excel users will also have encountered the IF function. The SUMIF function is a combination of SUM and If which allows us to calculate the total of all cells within a given range that match a certain condition.

For example, let’s say that, we have a “Sales” worksheet containing a breakdown of the sales of each salesperson. We now want to create a summary of these figures in a “Branches” worksheet containing two columns: “Branch” and “Total Sales”. We can use the SUMIF function to generate the figures in the “Total Sales” column.

A good place to start would be to create named cells so that we can refer to these names in our formula. To name a range of cells we first select the range then we click on the name box in the top left of the worksheet, enter the name then press the Enter key.

Having inserted the names of all the branches in the first column of our “Branches” worksheet, we would highlight the first cell in the “Total Sales” column, adjacent to the cell containing the name of our first branch; let’s say, for example, our first branch is “Birmingham”. When using functions for the first time, it’s useful to use Excel’s Insert Function facility. To access this, click on the Insert Function button on left of the formula bar. The SUMIF function is in the “Math and Trig” category. Scroll down the list, highlight SUMIF and then click OK. Excel now prompts us for the three arguments required by the SUMIF function.

The first argument is the range of cells to be evaluated. In our branch sales example, this would be the column containing the names of the branches. If we have created a name for this column, we can insert this name by clicking on “Use In Formula” in the Formulas Tab at the Excel Ribbon. This is a drop-down menu containing every name in the workbook.

The second argument is the criteria we want to match. In our example, is simply the contents of the cell in the adjacent “Branch” column, which in this case contains “Birmingham”. We can click in the cell to pick up the reference.

The final argument is the SUM range; the column that contains the cells that we want to actually total; namely, the sales figures. Again, if we have named this column, we would click on “Use In Formula” and choose the name. Once we have specified the three arguments, we click OK and Excel creates the formula.

It is now safe to copy the formula down. The cell reference of the adjacent column containing the branch name will alter but the two named ranges will remain the same. To copy the formula down, simply position the cursor over the AutoFill handle in the bottom right of the cell and then either drag or simply double-click.

Posted under Software

Each Excel document is referred to as a workbook and each workbook can contain up to 255 worksheets. To activate 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 using the keyboard. To activate the next worksheet to the right, hold down Control and press the Page Down key. This moves you forward through the worksheets are naturally holding Control 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 has some useful keyboard shortcuts for moving to the edges of a given body of data. To get to the right-most cell of your current range, hold down the Control key and press the right arrow key 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 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, most Excel users make frequent use of the Ribbon. Excel offers a series of handy keyboard shortcuts when working with the Ribbon.

To access Ribbon keyboard shortcuts simply press the Alt key once. A series of numbers and letters is then displayed. These represent the shortcuts that you should type to activate that part of the Ribbon. For example, “W” is the shortcut key for activating 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’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