How Styles Work In Microsoft Excel 2007

In addition to using and modifying Excel’s built-in cell styles, you also have the facility of creating your own. A convenient method of doing this is to highlight a range of cells and apply all the formatting attributes that you would like your style to have and then to turn those attributes into a cell style. When you create your cell style, Excel will detect all the attributes from the highlighted cells and include them in the style. Let’s take an simple example; let’s say we want to create a cell style for headings with our text angled at 45 degrees.

To set the text angle to 45 degrees, choose “Angle Counter Clockwise” from the Alignment section of the Home Tab of the Excel Ribbon. We might also want to centre the text horizontally and vertically, change the colour and make the text bold and slightly larger. Having chosen the relevant options from the Home Tab of the Excel Ribbon, 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 go on to apply the style wherever it was needed throughout our workbook. Just doing this is, in itself, a big time-saver. In addition, however, if we were 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.

Author is a developer and trainer with Macresource Computer Solutions, an independent computer training company offering Microsoft Excel 2007 Classes in London and throughout the UK.

Customising Text Alignment Microsoft Excel 2007

The alignment section of the Home Tab of the Excel 2007 ribbon is home to a number of options relating to the way in which your data is position within the cell. The most familiar and most frequently used icons in this section are the three relating to the horizontal position your data: left, centre and right. However you’ll notice that when you activate the cells of an unformatted worksheet, none of the three alignment icons is highlighted. This indicates that none of them is the default. The reason for this is that Excel treats data alignment 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 set the horizontal alignment, you can 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 click on a different form of alignment or click again on the already selected alignment. For example, if your text is centred and you click on the Centre button a second time, this deactivates centre alignment and returns you to the default alignment which, for text, is left. Thus we have, effectively, 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.

Switching Document Windows In Microsoft Excel

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

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 a more arbitrary fashion, hold down the Control key and click on the name of each file. You can also drag a selection rectangle around the names of several files to highlight them all. When you do so, make sure you begin your selection in blank space rather than starting on an item. Having specified the files that you want to open, click on the Open button.

Excel opens each of the selected files in a maximised window. This means that you can only see one file at a time. To switch between files you can use the Windows taskbar and choose a particular name. You can also click on the View tab of the Ribbon and here you’ll find the Switch Windows button. This contains a list of all the windows you currently have open. You can simply choose 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 tiled mode, click on the maximise button of any of the open workbooks. This action maximises all the open workbook windows so when you switch windows, you will find that all of them have been maximised.

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

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.

Excel 2007 Navigation Techniques

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 buttons. These are useful where you have a workbook that either contains lots of worksheets or has worksheets with very long names. The very first one makes the name of the first worksheet visible; the very last one makes the name of the last worksheet visible. The left pointing arrow makes the name of the previous worksheet visible and of course the right pointing arrow makes the name of the next worksheet visible. These icons don’t actually activate a worksheet; they simply make its name tab visible. To activate a sheet, you still have to click on that particular 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’ve navigated to a particular worksheet, you will need to go to a particular cell or a particular section of that worksheet. Naturally, you can use the scrollbars to make different parts of the worksheet visible. You can also 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 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 the 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 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.