A good many of the worksheets that are created Excel contain headings in the top row of the sheet. Normally, whenever we scroll down the sheet, any headings at the top will disappear. Similarly, if one scrolls to the right, any headings on the left will disappear. Excel’s Freeze Panes command, which is located in the View Tab of the Excel Ribbon, allows you to freeze our headings so that, as we scroll the sheet, headings remain in view.
Excel offers us three options: firstly, we can choose “Freeze Top Row”. A bold horizontal line is then displayed underneath the first row which extends into the row headings. As we scroll down the worksheet, the headings at the top of the sheet remain in view. Similarly, we can choose “Freeze First Column”. This time, the bold line extends to the right of the first column and into the column heading area. Then, as we scroll to the right, the first column remains frozen so that we can see the headings it contains and compare them with the data in the adjacent cells. To return to normal scrolling, we simply choose “Unfreeze Panes” 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, there are two bold lines: one indicating the column that is frozen and one indicating the row that is frozen. Then, as we scroll down the first row remains frozen and, similarly, when we scroll to the right the first column remains frozen. Once again, to return to normal behaviour, we simply choose “Unfreeze Panes” in the “Freeze Panes” drop-down menu.
Since this command allows you to freeze any number of rows or columns, if you are ever working on a large worksheet perhaps containing multiple row and column headings, you will probably find it pretty much an essential feature.
The writer of this article is a developer and trainer with TrainingCompany.Com, an independent computer training company offering Microsoft Excel 2007Classes in London and throughout the UK.
Microsoft Excel is an excellent spreadsheet environment and a very powerful tool for business analysis. 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.
Part of tailoring the training will be the identification of the 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.
If a training company offer customised Excel courses, they should also 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.
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 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 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.
Having 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 scroll bars to make different areas of the worksheet visible. You can also move around the worksheet by pressing 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 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”, 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.
In order to create a chart in Microsoft Excel, you must highlight the data that you want to plot. Your selection should also include any relevant headings. Excel allows you to create charts on chart sheets independent of the worksheet containing the data or to embed the chart on the worksheet alongside the data.
To create a chart which is embedded in the worksheet, having selected the range of data that you wish to plot, click on the Insert tab of the Excel ribbon and, in the chart section, you will find a series of drop-down menus offering each of the main chart types. Each drop-down reveals a gallery of customised chart types.
In addition to these options, we can click on the launch button in the bottom right of the Charts group to bring up the Insert Chart dialog box. Here, we are given access to the full range of chart types that Excel has to offer. You will also notice that there is a button which says Set as Default Chart. If you use a particular type more than any other, you can choose that type and click this button so that the next time you create a standalone chart, by right-clicking on a worksheet tab and choosing Insert, the new default chart will be used as the chart type.
When you choose an option from one of the drop-down menus in the Charts group of the Insert tab, Excel creates the embedded chart. The chart is placed on the draw layer of Excel which is different from the worksheet layer. When you click on a worksheet cell, you are working on the worksheet layer and the chart becomes inactive. When you click on the chart, it is highlighted and you will then be working on the draw layer.
You will notice that whenever the chart is selected, Excel shows three contextual tabs called Design, Layout and Format. They contain a series of options specifically related to charts.
In the Design tab, we have the Location Group which contains The Move Chart button. Clicking on this button gives us the option to move the chart to a separate sheet. If you choose the option, Excel creates a new chart sheet and places the chart on it. We can also do the reverse. If you click the Move Chart button again and click on “Object In…”, we can now choose the worksheet that we want to transfer the chart to. When we click OK, Excel embeds the chart in that worksheet and deletes the empty chart sheet.
The The writer of this article is a trainer and developer with TrainingCompany.Com, an independent computer training company offering Microsoft Excel 2007Classes at their central London training centre.
This article describes two features which are very useful when printing worksheets in Microsoft Excel: Set Print Area and Print Titles.
Set Print Area
As well as printing headers and footers on every page, Excel also allows you to specify Print Titles. Whereas headers and footers give an overall description of the report, print titles are taken from the worksheet itself. They are normally row and column headings and they are very often an essential part of the printed report.
For example, let’s say you have a worksheet with only five average-sized columns but several hundred rows, although the header and footer appear on every page, only page 1 will have column headings. This means that it becomes difficult to know what the figures on the other pages relate to. We can make use of Excel’s Print Titles feature to print the column headings on every page. Print Titles are in the Page Setup section of the Page Layout tab; simply click on the button and Excel displays the Page Setup dialog box with the Sheet tab activated. In our five column worksheet example, we would only need to specify the number of rows that we want to repeat at the top of every page. In our example, it would be only the first row. Having clicked to specify row one, we can click Print Preview to see the result and we would see that the headings are repeated at the top of each page.
Set Print Area
When you print a worksheet, Excel normally prints all the data that it contains. However, there are times when you only want to print a selection of your data. Although you can achieve this by hiding rows and columns, Excel offers a simpler solution.
As well as printing headers and footers on every page, Excel also allows you to specify Print Titles. Whereas headers and footers give an overall description of the report, print titles are taken from the worksheet itself. They are normally row and column headings and they are very often an essential part of the printed report.
For example, let’s say you have a worksheet with only five average-sized columns but several hundred rows, although the header and footer appear on every page, only page 1 will have column headings. This means that it becomes difficult to know what the figures on the other pages relate to. We can make use of Excel’s Print Titles feature to print the column headings on every page. Print Titles are in the Page Setup section of the Page Layout tab; simply click on the button and Excel displays the Page Setup dialog box with the Sheet tab activated. In our five column worksheet example, we would only need to specify the number of rows that we want to repeat at the top of every page. In our example, it would be only the first row. Having clicked to specify row one, we can click Print Preview to see the result and we would see that the headings are repeated at the top of each page.
Although Excel’s Print Preview feature is fairly useful, it is purely a preview mode. You are not able to work on your data while in Print Preview mode. However, in addition to Print Preview, Excel 2007 has two new modes of working which offer similar benefits to Print Preview while allowing you full access to your data. To switch to these modes, use the buttons on the right of the Excel status bar. There are buttons which can take you from Normal mode to Page Layout or to Page Break Preview.
Normal mode is the default mode in Excel. Here, the focus is on modifying and entering your data without thinking too much about pagination. If you print or preview your data, Excel paginates the worksheet and inserts dotted lines to show you the page breaks. This is normally the only feedback which relates to the printed version of your document.
Page Layout mode, by contrast, gives you a permanent preview of where page breaks will occur and which data will be printed on which pages. When working in Page Layout view, zoom out so that you can see more of the worksheet and you will notice that Excel displays margins on the left, right, top and bottom, as well as headers and footers. However, the really nice thing is that Page Layout view not simply a preview mode. You still have access to all the data within your spreadsheet and you can edit each of the cells it contains. If the printed version of your worksheet is particularly important, for example when working on reports, you may find it convenient to stay in this mode permanently; particularly if you have the benefit of a large monitor.
Excel’s third mode is called Page Break Preview. When you click on the Page Break Preview button that Excel conveniently zooms out so that you can see more of your worksheet. Page Break Preview mode is not dissimilar to Normal mode. Like Normal mode, it is not WYSIWYG (What you see is what you get) and neither headers and footers nor margins are shown. However, the key difference between Normal mode and Page Break Preview mode is that, when you’re working in Page Break Preview mode, the page break margins can be dragged.
This is a very useful facility: if you want to force a given column of data onto a new page, you just drag the blue dotted line representing the page break to the left of that column. It’s a deceptively simple facility. In fact, some experienced Excel 2003 users may even mistake the dotted lines representing page breaks for those which are displayed in Normal mode and may not even realise that they can in fact be dragged.
The Split command is to be found in the View Tab of the Excel Ribbon. It allows users to split an Excel spreadsheet window into either two or four separate panes and is particularly useful for comparing data in different parts of a large worksheet.
To use the Split command, you must first activate the cell where you want the split to occur then click on the split button in the Window section of the View Tab of the Excel Ribbon. The horizontal and vertical bars which mark the split can be repositioned simply by dragging them. Separate scrollbars are displayed for each section; so, in the case of a vertical and horizontal split, you will have two horizontal scrollbars and two vertical scrollbars. To remove the split at any time, simply click on the split button once more.
A second and more intuitive way of using the split command is to use the split boxes. These are located above the vertical scroll bar and to the right of the horizontal scroll bar. Let’s say, for example, that we have a spreadsheet containing rows of sales figures. Suppose we want to be able to compare the sales figures of one particular person with the sales figures of other individuals. We can create two vertical windows by dragging the vertical split box. We can then scroll to make the first person’s sales figures visible and in the top pane and then do the same to position the sales figures of any other sales person in the bottom pane.
Excel offers us a really simple way to exit split mode. All you need to do is double-click on the split line.
Let’s now say we want to focus on the total for each individual and that the totals are shown in column J. We could create both a vertical and horizontal split by dragging each of the two split boxes. We can then leave the salesperson’s name displayed in the left pane while, in the right pane, we can scroll across to make the total visible.
As we scroll up and down, we now see the totals for each sales person next to the sales person’s name. If we would also like the heading to remain in place, we simply make the top pane one row deep, displaying only the headings while we scroll the bottom pane up and down.
When we have finished reviewing our totals, we can remove the vertical split and leave the horizontal split in place. To do this, we just double-click on the vertical split line. To return to a completely normal Excel window, we would also double-click on the horizontal split line.
The writer of this article is a developer and trainer with TrainingCompany.Com, an independent computer training company offering Microsoft Excel 2007Classes at their central London training centre.
Excel 2007’s Quick Print facility allows you to send a document to the default printer without the need of entering values in a dialogue box. If the Quick Print button is not already showing on your Quick Access Toolbar, simply choose Quick Print from the Customise Quick Access Toolbar drop-down menu. You will notice that the tooltip which appears when you position the cursor over the Quick Print button displays the name of the default printer in brackets. If the printer that is shown is not the one you wish to use, you can simply choose the regular Print command from the Office button.
If, like a growing number of Microsoft Excel users, most of your documents are transmitted electronically, you may have Adobe PDF set up as the default printer. In this case, when you click the Quick Print button, you will be prompted to save the file since printing to Adobe PDF means creating a disc file.
Whatever your default printer, you will find that Excel prints the document using its default settings: moderate margins, no header or footer, no column or row headings and no gridlines. If the document cannot fit on a single page, Excel will produce multiple pages moving down first and then across. Having printed the document, Excel paginates your worksheet and subsequently displays dotted lines representing the page boundaries.
Whereas Quick Print sends the document to the printer straight away, Print Preview offers a method of previewing the document prior to sending it to the printer and is often a useful precaution. To access Print Preview, click on the Office button in the top left of your screen, choose Print and then Print Preview.
If the preview of your document is acceptable, simply click on the print button to send the document of the printer. If the document needs to be changed in some way, one option is to click on Page Setup. This gives you access to settings such as the margins, page orientation, header and footer, as well as other advanced features.
You also have the option of zooming in on your data by clicking on the zoom button. When you click the zoom button a second time, the whole page is displayed once more. Excel allows you to preview all the pages by clicking on the Next and Previous buttons.
You can also show or hide margins. Margins consist of dotted lines with drag handles at the end of each line. The margins displayed in Print Preview are fairly comprehensive. Firstly, we have the page margins: top, bottom, left and right. Next, we have margins to control the area available to headers and footers. Finally, we have drag handles allowing us to change the column widths. You will often find that you can reduce the number of pages required to print a document simply by changing the various margins.
Before you can manipulate a chart any way, you need to highlight or select the chart. To select an embedded chart simply click once on its border. When a chart is highlighted, Excel does two things: it displays the data on which the chart is based and it displays the Chart Tools. Chart Tools consist of the Design, Layout and Format context sensitive tabs.
To select a stand-alone chart, simply click on the sheet tab. In a standalone chart, the chart occupies the entire sheet and normally the chart remains highlighted. However, it can be deselected if you click on the blank space outside the chart area. If the chart has become deselected, simply click on its border to reselect it; just as you would with an embedded chart.
The most basic kind of manipulation one can do to an embedded chart is to reposition and resize the chart. To resize the chart, you must use one of the resize handles. These are positioned on the corners of the chart and in the middle of each side. Each resize handle consists of three dots. When using the middle handles, you can only change one dimension; either the width or the height. When you use the corner handles, you can change both dimensions. To resize proportionally, in other words to retain the aspect ratio, hold down the Shift key as you drag.
It is also possible to permanently lock the aspect ratio of a chart, so that it becomes impossible to stretch it disproportionally. Click on the Format tab and then click on the launch button. In the size section this displays the Size and Properties dialog box. Here you’ll find the very useful option Lock Aspect Ratio. If this option is activated, whenever you use the corner handles to resize a chart, the aspect ratio is automatically preserved without having to use the Shift key.
To move a chart within the worksheet, position the cursor on the border of the chart but away from the resize handles. You will notice that when the cursor is positioned over a resize handle it changes to an icon with two arrows. When you have the cursor over the border but away from the resize handles, the cursor changes to an icon with four arrows. At this point, simply click and hold and drag the chart to a new location.
Although Excel places an embedded chart on a layer which is separate to the worksheet itself, it still associates it with the columns and rows over which it is place. This means that if you make adjustments to these rows and columns, the chart can move or be resized. For example, if we insert a column to the left of one of the columns over which the chart is placed, the chart becomes wider to accommodate the newly inserted column.
If this behaviour becomes a nuisance at any time, Excel allows you to deactivate it. Simply highlight the chart, click on the Format tab and then click on the launch button in the Size section. This time, click on the Properties tab and here you’ll find options for Object Positioning. If you choose the option Don’t Move or Size With Cells, this means that chart objects will become completely independent of the columns and rows on which they superimposed.
The author is a trainer and developer with TrainingCompany.Com, an independent computer training company offering Microsoft Excel Classes in London and throughout the UK.
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 the new workbooks will have one sheet, which is the minimum that Excel will allow. We can access the new workbooks by clicking on the View Tab of the Ribbon and using the Switch Windows drop-down menu. To move worksheets between workbooks using drag and drop, we will need to see all the workbooks simultaneously. Excel has a special command for achieving this. In the View Tab, we click on the Arrange All button and choose “Tiled”. Excel will then display each of the workbooks in a smaller window, allowing us to see all of the open workbooks simultaneously.
The next step would be to select the three sheets pertaining to the first quarter: we click on “Jan”, hold down the Shift key and click on “Mar”. We can then drag the selected sheets across to the window of any of the new workbooks. We can then repeat this exact same procedure for the other quarters.
As we saw earlier, the minimum number of sheets which you can have in a workbook is one. Therefore, when we have moved the last three sheets from the original workbook, the window will simply disappear. Naturally, however, the last saved version of the file will still exist.
The final step would be to delete the unwanted (original) sheet from each of our four new workbooks. Having done this, to exit the split screen view and return to normal mode, we simply click on the maximise button in any of the tiled windows.
As a matter of interest, the second way of copying sheets from one workbook to another is to use the Move or Copy Sheets command. This is available in the Format drop-down menu in the Cells section of the Home Tab or by right-clicking on the selected sheet tabs. As the name implies, as well is moving sheets, this method allows you to create a copy at another location.