Excel 2007- In Cell Dropdown List

When you need to enter data from a small subset of entries into a range in Excel 2007 you can do it more easily using a custom designed dropdown list.

To configure a dropdown list in a cell type the list of items to use in a single column in a spare sheet in the workbook.

Select these cells and choose the Formulas tab Define Name, type DataForList as the name in the dialog, set the scope to Workbook and click Ok.

Switch to the sheet where you want to add your dropdown list to some cells, select the cells that should display a list of data to choose from and choose Data tab > Data Validation > Settings tab.

From the Allow: dropdown list choose List and, in the Source area, type =DataForList and click Ok.

Now, whenever you click a cell in this range you’ll see a dropdown list appear from which you can choose a list entry for that cell.

If you're using Excel 2003, here is a link to an earlier post explaining how to do this in Excel 2003:

Automatic cell entries in Excel 2003
http://www.projectwoman.com/labels/validation.html

Excel: Print a worksheet your way

When you need to print one version of a worksheet for yourself and another for the boss and you like it small and he likes it to be - well just how he likes it, then you need Views. The Excel Views tool lets you configure a worksheet for different printing options and to save these so you can use them again later on.

You can set views up so you do one for your boss and one for you. Or, you can set one up to print only the summary part of a worksheet and another to print the lot. Even if the print areas and the print settings change, Views let you preconfigure them so you don’t have to set them up manually every time. Better still, Views are saved with the worksheet so they're always available.

Step 1
To save a set of printing settings, first set up your worksheet with the print settings you want to use including setting a print area if needed.

Step 2
To save this set up, choose View > Custom Views > Add (in Excel 2007 choose the View tab > Custom Views > Add). Type a name for the view that explains what settings you have selected. Enable the Print Settings checkbox and click Ok. You can now create another view and save it. Do this for as many different settings as you need. Save your worksheet.

Step 3
In future, before you print, choose View > Custom Views > select the View you want to use and click Show. Now go and print the worksheet - your settings were saved so you don't need to configure them.

Excel page headers and footers

When you're printing a 50 page worksheet, you want to hold onto the printed pages very carefully. If you don't the entire project is prone to disaster as it is all too easy for the pages to get out of order and it’s nearly impossible to sort out the mess. So, either staple them very quickly or use the header and footer tool in Excel to add page numbers to all your pages.

Of course, while page numbers are one of the most common things you might put in a header or footer it isn't the only thing. You can add everything from the date to your company’s logo.

To add a header or footer that will print on every page of an Excel workbook, choose View > Header and Footer in Excel 2003 or, in Excel 2007 choose Insert tab > Header & Footer. In Excel 2003 you can select from a range of preset headers and footers which are configured using typical combinations of items usually used in headers or footers – for example, sheet and worksheet names, page numbers, filename and folders.

If you'd prefer to create your own headers and footers, click the Custom Header or Custom Footer button and create your own design - this is the way you create a header or footer in Excel 2007 too.

Click in the Left, Center or Right areas of the dialog to place information at any of these places on the page. In Excel 2003 the buttons you can select from to add preset information aren't labelled but you can usually tell what they are. From left to right, they let you change the font used, insert the page number, number of pages, date, time, filename and folder, filename, sheet name, and an image. In Excel 2007 they are labelled.

When adding an image to a header or a footer, make sure it is small enough to fit in the header area – there's no tool in this dialog to resize the image if it's too big. When you're done, check the header by selecting Print Preview.

Protect an Excel worksheet

When you create a worksheet for others to use the last thing you want is for them to clobber your formulas or mess up your design. To keep them from making changes to the worksheet, either maliciously or inadvertantly, protect the worksheet.

If you haven't protected a workbook before you may find the process of doing so a little confusing. First you hage to unlock the cells that you want your user to have access to. These will be the cells that they can make changes to such as cells they need to add data to. You do this because all cells, by default, are locked against changes.

Select the cells the user should be able to change and choose Format > Cells > Protection and disable the locked checkbox.

Now choose Tools > Protection > Protect Sheet and, if desired, enter a password that will be required to unprotect the sheet so that it cannot be unprotected without permission. Click Ok and the cells that are locked — in other words everything that you didn’t unlock — will now be protected so that the user cannot change them.

The only cells your user will have access to are those that you unlocked for them to use. In this way, you can protect your formulas so that users cannot change them or overwrite them with fixed values which would render the worksheet potentially inaccurate.

Excel - print charts in black and white

Although your Excel chart might look great in color on the screen, if you're printing to black and white or printing in color and planning to reproduce the charts in black and white you might be disappointed with the final result. Light green, light blue and light orange all look very different on the screen but are indistinguishable in black and white.

So, when your chart is destined for reproduction in black and white, set it up so it is guaranteed to be readible. To do this, select each series or data point by clicking on it, right click and choose Format Data Series (or Format Data Point)> Patterns tab > Fill Effects > Pattern and use a grey or a black and white pattern. Repeat for all the series and save before printing. The chart is guaranteed to look good when printed.

Excel - calculating workdays with Networkdays

Excel has lots of very cool functions for doing all sorts of calculations. One of these is the NETWORKDAYS function.

You can use it to calculate the number of days between two dates taking into account holidays.

Start by placing the dates for the holidays in a range of cells across a row or down a column. Select this range and name it holidays using Insert > Name > Define.

The function calculates the number of workdays between two dates so place one, for now, in cell A1 and the other in A2. This function will calculate the days between the dates in cells A1 and A2 taking into account the holidays listed in the range called Holidays:

=NETWORKDAYS(A1,A2,Holidays)

If the NETWORKDAYS function returns an error make sure that you have the Analysis Toolpak installed as this function is stored in this toolpak. To install it in Excel 2003 choose Tools > Add-ins and enable its checkbox. In Excel 2007, click the Microsoft Office Button > Excel Options > Add-Ins and from the Manage list choose Excel Add-ins and click Go. In the Add-Ins Available list enable the Analysis ToolPak checkbox and click OK.

Solving printing problems in Excel

I've seen adults brought almost to tears over printing worksheets. Big worksheets consume lots of paper and when things go wrong they do so in a spectacularly wasteful way. Sometimes the best you can do is hit the printer Off switch to at least achieve a short term solution to the problem. A longer term solution is to understand how you can control what is printed and that's what I'll cover this month. I'll look at the basics of printing a worksheet and then explore some more advanced options which offer better control over your printouts.

Troubleshooting problems
When you choose File > Print or click the Print button in Excel, the program determines what to print and does so. By default it prints everything on the currently active sheet. So, if you have a small set of data in the top corner of the worksheet and have accidentally typed something into a cell way below this (even if it is just a single space), you'll get your data and everything else between this and the one cell with the mistaken entry printed. It could be pages and pages of blank paper – or lined paper if you have gridlines enabled and it's perilously hard to track what went wrong.

You can see ahead of time that you're about to have problems if you use the Print Preview tool. When the Next button is visible there are more pages to print than the one you can see. Of course, you should take care to never place a space in a cell. If you need to remove the cell's contents, click in the cell and press Delete never use the spacebar.

If you can't find the problem cell to delete it, you can try to fix the problem by deleting all the rows below your data and all the columns to the right of it and try again. In the long term this will avoid the problem happening when you print the workbook again next time. If this is a one off worksheet, you can select the area to print before printing it. Drag over the area to print and choose File > Print (don't click the Print button on the toolbar as it prints the entire sheet regardless of what is selected). When the Print dialog appears, click Selection so only the selection will be printed.

Adding Page Breaks
To preview the page breaks on the worksheet to see where the data will be broken up into individual pages, choose View > Page Break View. Lines will appear on the screen indicating where the page breaks are. You can change these by adding your own manual page breaks but you have to do this inside the current page breaks – for example you can add a break inside a page but you can't configure a page to be longer or wider using this method.

To add a manual page break, click to select the entire column or row where the break should appear and choose Insert > Page Break – the page break will be added to the immediate left of this column or immediately above the row. You can also click a cell and choose Insert > Page Break and a page break will be added above and to the left of that cell. When in Page Break View, not only are page breaks visible on the screen, you can also move them by dragging on them with your mouse.



Headings on all worksheet pages
Another issue when printing is that as soon as a sheet prints on more than one sheet of paper, the column headings or row headings appear on the first page but won't appear on the other pages. This makes the data on the second and subsequent pages almost impossible to understand unless they're taped together to form a single large sheet.

To avoid this, configure Excel to print column and row headings on every page of your printout. Choose File > Page Setup > Sheet tab and click in the 'Rows to repeat at top' box – type the row letters in the form $1:$1 (to print only the first row) or $1:$2 for the second etc.. If preferred, you can click the Collapse Dialog button to hide the dialog while you select the rows to use. Likewise you can set the columns that contain the row titles – generally these are in column A and you specify it in the 'Columns to repeat at left' box with an entry like $A:$A to use just the first column or $A:$B for the first two, etc..

More printing controls
When printing a worksheet that is wider than it is tall, you can print onto paper in landscape orientation to take advantage of the dimensions of the paper. To do this, choose File > Page Setup > Page tab and select Landscape. At the same time, make sure you’ve selected Letter or A4 paper depending on what you're using as each has different dimensions.

Shrink to fit
When you have a worksheet that is just too large to print on a single piece of paper you can shrink it to fit on a single sheet by choosing File > Page Setup > Print tab and click the 'Fit to 1 page(s) wide by 1 page tall' option and it will be reduced to fit on a single sheet.

If your data is very long and you want to print it one page wide but on many pages long you can use the same option – in this case set it so it reads 'Fit to 1 page(s) wide' and delete the entry in the second box – Excel will constrain the width to a single page but print on as many sheets as are needed length-wise.

The same can be done for a worksheet that is wider than it is tall – remove the entry from the first box so it reads 'Fit to page(s) wide by 1 page tall'. Of course, you can also set the value to 2 pages wide or tall or more as required.

When a worksheet will print over multiple sheets in both directions the order in which the sheets are printed may be important. You have two choices – you can have Excel print down the left side of the worksheet first and then across to the next series of pages to the right or you can have it print the width of the worksheet first then the pages below this. This order can be controlled using File > Page Setup > Sheet tab – and select either 'Down, then over' (the default) or 'Over, then down'.

Excel: Open multiple workbooks

If you're like me, you will open Excel in the morning and then open a series of workbooks that you work on each day. You can save time in finding and loading these files by creating an Excel Workspace.

To do this, open all the workbooks you want to have opened each time you launch Excel and then save them as a Workspace file by choosing File > Save Workspace and type a name for the file. Click Save and you can then open all the workbooks at one time by opening the Workspace file. Of course, if you just want to open a single file you can open it as normal.

In Excel 2007 - find the Workspace feature by choosing View > Window > Save Workspace.

Another alternative for opening files automatically when Excel opens is to save the file to the XLStart folder - when you do this, the file is opened every time Excel launches.

Excel Change the Default font

If the default font that Excel 2003 uses for all new worksheets doesn't suit your needs - change it by selecting Tools > Options > General tab and set the Standard font and Size to your preferred choice and choose Ok.

In future, all new workbooks you create will be set by default to this font although those you have previously created will remain unchanged.

If you're using Excel 2007 and you don't fancy the new Calibri font, click the Office button, choose Excel Options and click the Popular group. From the Use this font dropdown list choose the font to use for your new worksheets and click Ok. You'll need to close Excel and restart it for the new font change to be in force.

Excel - reuse chart formats

You've gone to all the trouble to format a chart nicely and you'd like to reuse the format again some time in the future. Instead of recreating the format each time, save it so you can apply it with a single click.

In Excel 2003, right click your chart and choose Chart Type > Custom Types tab and click the User-Defined button. When you do this an Add button appears - click it and type a name and description for your chart when prompted to do so. Click Ok twice when you are done.

Now, in future, when you create a chart you can select this format from the Chart Wizard options or apply it to an existing chart by selecting the chart, right click and choose Chart Type > Custom Types and click User-defined. Select your format and click OK to apply it to the chart.

One word of warning, for some reason, Excel includes chart titles as a format so you'll lose your existing chart title if you have one when you apply the new format to it. It's not a big deal but it helps to know that it's going to happen.

Excel 2007 makes Lovely Lists

Lists were a big addition to Excel 2003 as they allowed you to work with list data in Excel more easily than ever before. One key plus was that they let you create charts that expanded automatically as the data in the list grew. This was something you simply couldn't do before very easily.

Now in Excel 2007 lists are called tables and they are simple to create using the Format As Table option on the Home tab on the Ribbon. One gotcha is that you shouldn't use a table format if you don't want to create a list, instead use the much more cumbersome and much less pretty Cell Styles options.

When you create a list you automatically get Filter buttons for the list. If you don't like or want them, disable them by clicking to disable the Filter button on the Data tab - just make sure your cell pointer is somewhere in the list when you do this. Like in Excel 2003, if you create a chart based on your table, it expands when you add new data to it.

Multiple Paragraphs of text in an Excel cell

Multiple paragraphs of text in an Excel cell sound good, they look good but how the heck do you create them? If you press the Enter key you enter the current text into the cell and move away from it - obviously, pressing the Enter key isn't the answer.

The solution is to press Alt + Enter to create a new line of text in the current cell. Do this as often as you need to. You might have to make the row taller to fit the text if Excel doesn't make the adjustment for you.

Freeze your titles

When a worksheet exceeds one screen it can be difficult to work as the title row disappears off the screen. Solve this by freezing the titles in place so they don't move but you can still move around your worksheet - it's the best of both worlds.

To do this, place your cell pointer below and to the right of the row and column containing your column and row titles. Not choose Windows > Freeze Panes to fix these rows. These titles are saved with your worksheet.

If you need to undo them at a later date, choose Window > Unfreeze Panes to undo the effect.

Selecting chart elements in Excel 2007

It used to be easy to know what part of a chart you had selected in Excel 2003 - you just read the name off the left hand side of the Formula Bar.

Look in vain for this same feature in Excel 2007. Click anything on the chart and the formula bar just says Chart 1 - like duh! I know I have the chart selected it's the element on it that I'm interested in.

The solution is the new Chart Element tool. Click the chart to select it, choose Chart Tools > Format on the ribbon and in the top left corner is the Chart Element list. Not only will it tell you what you have selected on the chart but it's a dropdown list of names of various chart elements. Click one and that portion of the chart is selected automatically.

It's a handy new tool, I'd just like the benefits of the features from Excel 2003 and 2007 blended into one.. call me fussy.

Do You Undo?

This post is subtitled Undos that Do and Those that Don't

If you're using Excel 2003 or earlier, you have a big problem with the Undo command, you see much of the time, it plain doesn't work.

Curious? Try this: open an Excel file, make some changes to it (minor however, you won't be able to undo these however much you think you can). Check the Undo button - it is enabled. Save the file. Now check the Undo button again. Yikes, it's now disabled. You see, after you save a file in Excel 2003, all the Undo steps are removed - no more Undo. It pays to know this is how it works.

In Excel 2007, things are much better, and the Undo retains the changes even after you have saved the file. Much nicer behavior.

Take a snap - Excel 2003 and earlier.

Need a copy of part of an Excel worksheet? Too easy!

You can take a picture of a range in Excel and, for example, insert into Word as a picture or place it an image in another area on a workbook. To do this, first select the area to snap and hold Shift as you open the Edit menu. Choose Copy Picture, select As shown on screen or As shown when printed and click Ok.

Now go ahead and paste the image wherever you desire. This Shift + Edit menu option also works for copying a clip art or other type of image inserted into an Excel workbook.

Error Checking in Excel

Chasing problems in Excel worksheets is a major pain. It helps to create them accurately in the first place but when you're trying after the fact, to find problems, Excel has some tools that can help. One of these is the often overlooked Go To option.

Go To can find formulas that vary from those in the cells that surround them. This can help you find formula errors which would otherwise be difficult to locate.

So, for example, if you have a column of cells which should all contain the same formula you can check to make sure they are written the same way by selecting the cells and choose Edit, Go To, Special, Column Differences (in Excel 2007, from the Home tab select Find & Select, Go To Special and then click Column Differences). Any cells which contain a formula that relates to a different series of cells to those in the active cell will be selected so you can check them. The Row Differences option does the same thing for rows of cells.

View formulas in Excel

If you've ever wanted to view your formulas in an Excel worksheet, perhaps because you suspect one has been overwritten by data or you need to troubleshoot something press CONTROL + ~ to display formulas so you can troubleshoot or debug them. Press the same keystroke again to return to your regular view of your worksheet.

If you select a cell with a formula in it before you press CONTROL + ~ you will see not only the worksheet formulas but also all the precedents to the formula in the current cell.

How old are you?

I know.. it's none of my business, but sometimes you wonder, don't you, just how old you are in days? If this question consumes your waking hours, put the calculator away and crank up Excel.

Excel's Datedif function, while not documented, calculates the difference between two dates in a number of formats; days, months or years. The syntax of the function is: =datedif(start date,end date,units to return). The units must be provided by a quoted string in the format: "y" - full years, "m" - full months, "d" - full days, "md" - full days in excess of the last full month, "ym" - full months in excess of the last full year and "yd" - full days in excess of the last full year.

So, for example, this formula determines the number of days between the dates in cells B6 and C6: =DATEDIF(B6,C6,"d"). Type your birthday and today's day in the cells and you'll know immediately how old you are in days..

Excel Fill Options

You probably already know that you can fill a series of Excel cells by entering the first two numbers in a series and then select the two cells and drag on the marker in the bottom right corner of the selection. Excel fills the selected cells with the next numbers in the series. to find more fill options, including the ability to copy the series rather than filling it, select the cells but use the right mouse button to do the dragging. If you're filling dates you'll get options like Fill Weekdays and Fill Months - that let you control the fill series that Excel creates for you.

Align data in Excel

When you make an Excel row much deeper by dragging on the marker below the cell number you'll often find the cell entry hugs the bottom of the cell. If you'd prefer to have it centered in the cell you won't find the Center button works to do anything more than center the cell entry horizontally.

To center it vertically use the Format, Cell dialog and choose the Alignment tab. From the Vertical list choose Center and the entry will be aligned evenly between the top and bottom cell margins.

Excel - highlight cells containing formulas

There's no shortcut way to color code cells in Excel which contain formulas but this workaround is simple and fast.

Choose Edit, GoTo, Special button and click Formulas and then Ok. Now fill the cells with a color using the Fill Color tool.

It's now clear which cells contain formulas and those that do not.

No new file when you launch Excel

When you open Excel you will, by default see a new blank workbook. If you'd prefer to see nothing at all you can do so by altering the Excel startup icon.

Right click it and choose Properties and then in the Command area add the text /e at the end after the double quotes. Now Excel will launch without the splash screen and with no new workbook.

Gridlines - Yes or No?

In Excel there are gridlines and gridlines. You can display them on the screen as you work or on the printouts or both or neither.

Confusing?

To display or hide gridlines as you work, choose Tools, Options, View tab and enable or disable Gridlines.

For printing, choose File, Page Setup, Sheet tab and enable or disable Gridlines for printing...

Now you know.

Millions in Excel

Excel has some cool formatting tricks up its sleeve and one of these is its ability to shrink really big numbers down to size.

So, if you have values in the millions - like your salary - Ha!, you can size them down to size using a custom format. Select the cells, choose Format, Cells, Number tab and click the Custom group and type #,,"M" and Excel will format 200,000,000 to read 200M! The numbers aren't altered it's just a simpler way of displaying them.

Since the Y axis of a chart inherits its formatting from the top left cell in the chart data range this lets you format a chart's Y axis to show the smaller values too.

Self Portrait in Excel

Excel can take photos of itself, it's a fun technique for applying a portion of a worksheet back into the worksheet as an image or into some other application.

To do this, make a selection around the area you're interested in and hold Shift as you click to open the Edit menu. There's a new option called Copy Picture which, if you click it, you can then select how to copy the picture - as shown on the screen or when printed etc.. Make your choice and then you can paste the image into any application.

To paste it back into an Excel workbook, Shift + click on the Edit menu and you can paste it back in by choosing Paste Picture.

You can do some funky things with this. Take a copy of a portion of a worksheet using this technique and then select a bar in a bar chart. Choose Edit, Paste and you'll paste the image in as your new bar chart fill.

Quick moves in Excel

My friend Theda and I were talking the other day about a project she’s working on where she has to reorder a lot of items into alphabetical order and, in some cases, an order which is not alphabetical.

While she is doing the project in Microsoft Excel there are, of course, difficulties in Excel in moving items around as Excel does not, by default, open up a row when you choose to move an item from one part of the worksheet to another - instead, Excel thinks you want to overwrite the target cells - as if!

There is, however, a way to do this which opens up the space for the new row and which closes up the space that you have just made available. To do this select an entire row or range of rows in Excel by clicking on the row number or numbers. Hold your mouse over the dark outline around the rows and hold the Shift key as you drag the rows to their new position. The empty space that you have created will be closed up and a new space will be created between the other two rows for your item.

Problem solved!

Put a button on an Excel worksheet

I love the form tools in Excel they let you do so many cool things. One thing I love to do is to make buttons run my macros. To do this, use the Forms toolbar, click the Button tool and drag to create a button on the worksheet. When you do, a dialog appears with all your macros listed so you can select the one to use. You can also edit the text on the button and when you're done simply click it to run the macro - sweet huh?

If you're using Excel 2007 you have to add the button tool to your Quick Access toolbar (it's in the Commands Not In The Ribbon group and it's called Button (Form Control).

Text on a chart, Excel

It's easy enought to place a title or Y and X axis titles on a chart but what about a note or comment?

The secret is in the Drawing tools. Display the Drawing toolbar and click on the Text Box button. Now you can drag a text box on your chart and add text inside it. Size it, format it and you're off.

Hide or color Excel gridlines

Ok, grey is my favourite colour - it's the colour of my old school uniform. I'm an Aussie and we still wear uniforms to school! Mine was grey serge in winter and grey cotton in summer, complete with hats and gloves. I kid you not and this is seriously OT and it uses Australian spelling so I'll get back to what I was saying.

Ok, so gray might be my favorite color but it's probably not yours. If Excel's gray gridlines offend your color sense, you can change them or remove them entirely. To remove them choose Tool, Options, View tab and disable the Gridlines checkbox.

To change the color of the lines, choose Tool, Options, View tab and choose an alternate color from the Gridlines color dropdown list. If you didn't realise gridlines were little dots and not solid lines, you're about to see that that's exactly what they are.

Excel’s personal.xls

There’s a special file in Excel called personal.xls which is opened automatically every time Excel opens. This makes it a great place to put your Excel macros as they're then accessible to any open workbook.

Unfortunately, a personal.xls workbook is not created until you actually do it yourself so you may not have one. The simplest way to create one is to record a simple macro because then Excel does it for you.

To do this, open Excel and choose Tools > Macro > Record New Macro and type a macro name. From the Store Macro In dropdown list choose Personal macro workbook and click Ok. Record a step or two—it doesn’t have to be an actual macro but it just has to be something and click the Stop Recording button or choose Tool > Macro > Stop Recording. Once you do this your personal.xls macro workbook will be created - ridiculously simple in fact.

If you're prompted to save the file when you close Excel answer Yes to do so. Excel will save it in a location that ensures it will be opened automatically every time you open Excel. In future, store all macros that you want to be accessible to all workbooks in this file and you won't ever have to load them specially.

Make "My Places" your place...

Until I discovered how to add folders in the list down the left of the Word and other Office program's Save As and File Open dialogs I spent hours navigating to get to the right folder to save a file. Some days it felt like it would be simply easier to dump everthing in the one folder and worry about finding it later on. Ok, I know - bad idea - but it was tempting.

Now I fill my My Places list with all the folders I need long term and short term so saving files in the right folders is simplicity itself. All I do is click the folder in the list on the left and I'm there - just where I want to be.

To do this yourself, from inside Excel or Word, for example, choose File, Save As and notice the My Places bar down the left of the Save As dialog. Navigate to the folder that you want to add to your My Places toolbar and select the folder. Click the Tools menu option in the top right of the dialog and choose Add to My Places. The folder will be automatically added to the bottom of your My Places bar. You can now click it to open the folder anytime you need it and it stays there from one Office session to the next.

Once you no longer need it, you can remove the folder from the list by right clicking it and choose Remove. You can also rename the folder and reorder items in the list by right clicking and choose Delete or Move Up/Move Down as required. You can also switch to small icons if there are too many folders in your My Places bar to see them clearly. The same folders turn up when you choose to open or save a file. Organization is just a click away.

Talking in Excel

Sometimes it’s hard to get your point across in a text message because the nuances of your voice do not display.

When you've got something to say and you need it to be understood by someone viewing your worksheet, why not add a voice message rather than a text comment? It's easy to do.

Choose Insert, Object and click the Create New tab. Click the Wave Sound option and the Window Sound object dialog opens. Click the Record button to record your message and when you are done click the Stop button.

A small sound object icon (it looks like a speaker) appears in your worksheet at the place that you were when you recorded the message - just click and drag it to where you want it to appear. It is saved with the file and can be played by double clicking on it.

Now you’ve got a better chance of people understanding exactly what it is that you’re trying to say to them when they hear you say it - well that's the theory anyway!

Fixing formulas in Excel

Sometimes you need to convert formulas into fixed figures - not often I admit, but often enough that there is an Excel tool for doing this.

To convert any formula into a fixed value, select the cell or cells to fix and choose Edit > Copy and then immediately choose Edit > Paste Special > Values. Instantly your formulas are converted to fixed figures.

Don’t you hate it when you know there’s something wrong but you can’t exactly put your finger on what is happening?

Try this, filter a list in Excel and write an =sum function at the foot of the list to sum the visible data. So far so good? Well, try checking that sum manually – do you still feel confident? Worse still, if you’re using Excel, try to filter the numbers in the column containing the Sum formula and watch as Excel chews up your formula – yikes!

You see, =SUM just doesn’t work on filtered lists. Instead, you have to use SUBTOTAL. Of course, there’s a simpler way. Use the AutoSum button on the Excel toolbar to create your formula and it does the sensible thing and writes a SUBTOTAL function for you. Now, when you filter the data it sums only visible values and it never gets swallowed up.

Snap an Excel range

Did you know you can take a photo of an Excel range? Well you can and it's one cool way to get around the problem of needing to print bits of two worksheets on the one piece of paper, something as smart as Excel is, it just can't do.

To do this, right click a toolbar and choose Customize, Commands tab. From the Categories list choose Tools and from the Commands list click and drag the Camera icon up onto a toolbar. Now select a range on a worksheet and click the camera. Then click where the 'photo' should go.

Repeat this to assemble bits of lots of worksheets onto one page for printing. And the best bit? the photos are 'live' if the data in the worksheets changes, the photo does too!

Data labels on Excel charts

Data labels on your charts show your viewer the values they're looking at and after all - isn't that the purpose of the chart?

To add Data Labels to a chart, click the chart and choose Chart, Chart Options. Click the Data Labels tab and choose a style that will look good on your chart. Typically values is a good choice but, for pie charts, for example, a different type might work better.

Excel and the taskbar

I like to see each individual worksheet I have open named on the taskbar - well, that is unless I don't. When I have one of those "redecorating the desktop" days, I opt to have one indicator for Excel and then use the Windows menu or Control + F6 to switch between them.

Changing how I view my Excel interface is easy. Choose Tools, Options and click the View tab in the Options dialog. Disable the Windows in Taskbar checkbox to view one Excel indicator on the taskbar. Click Ok. Reverse the process to switch back.

Excel Freeze Panes

When you’re working on a very big spreadsheet it can get confusing as to what the headings are for the various rows and columns when you move away from the top most cells.

A simple way to solve this problem is to freeze panes - it's a funny term for something that actually is very handy. Move so that cell A1 is located in the top left corner of your worksheet area and then position your cell pointer just below the set of headings that you want to see and just to the right of the column headings if they’re important too.

Choose Window, Freeze Panes and Excel will freeze the area above and to the left of where you are working. Now if you move around the worksheet wherever you happen to go the cells on the left and top of the worksheet will always be there.

If you need to undo the effect choose Window, Unfreeze Panes and it will all be back to rights. My guess is that you’ll like it so much that you won’t want to change it anyway.

Pictures inside Excel comments

In a previous tip of the day, I showed you how to create shaped comments in Excel but today I’m going to go one step further and create pictures inside the comment.

As you might expect, start off in Excel and add a comment to a cell. Right-click the cell and choose Show comment and then click the border of the comment to select it. Choose Format, Comment and, from the Colors & Lines tab's Color dropdown list choose Fill Effects and then the Picture tab and click Select Picture.

Find a picture to add to your comment from those in your My Pictures folder, enable the Lock Picture Aspect Ratio checkbox and click OK twice. You’ll now have the image inside your comment.

Depending on the image that you have used you may want to change the format of the text, for example coloring it a different color and sizing it large enough so that it can be easily seen.

Naming Ranges in Excel

When you’re working with different areas on an Excel worksheet it sometimes helps to name the area or range as Excel calls it.

You might do this so that you can easily select a print area from a number of different printing areas on the worksheet or where you want to move very quickly to a named area which is in an out of the way place on the worksheet.

To name a range, select the cell or range of cells to name and choose Insert, Name, Define and give the cell or range a name. You can use whatever name you like, it just must be a single word name with no spaces and it can't start with a number. When you’re done, click OK.

Now look up to the top left corner of the screen to the left of the formula bar you will see a small Name dropdown list. You can dropdown the list and select the named cell from the list and you will automatically go to it and, if it is a range, it will be automatically selected ready, for example, for printing.

Shrink to size Excel Printouts

When you're working on a very big worksheet in Excel sometimes it happens that when you print it out you get a stray row or column on a single sheet of paper. It’s not only a nuisance but it’s also a waste of paper.

To ensure that this doesn’t happen choose File, Page Setup and click the Page tab. The Fit To option lets you specify the number of pages wide or tall that a worksheet is. Select the measurements that you want to fit, for example fitting to one page wide by one page tall will print everything on a single page.

What if you don't know both measurements? Don't worry, if you know you want it one page wide but don’t know how tall that will be, select Fit to 1 Page Wide and leave the Tall box empty - that means delete anything that’s in it.

This works the same if you know how tall you want it to be but not how wide. It’s a smart way of saving trees and frustration and it looks much neater than handing someone something with a skerrick of info on the last page.

Any shape Excel Comments

I like to use comments in my Excel worksheets but sometimes the square yellow boxes just don’t do it for me.

Luckily with a little bit of imagination and skill, I can change the shape of my comment boxes.

Here's how to do it yourself:

Add a comment to your worksheet and click the cell that contains the comment, right-click and choose Edit Comment and then click on the border of the comment so you have it selected.

Now, from the Drawing toolbar click the Draw button and choose the Change AutoShape option in the menu. You can select from all sorts of shapes including stars and banners and flowchart symbols.

When you’re done just click outside the comment and the change will be created for you. Neat stuff? I think so.

Keeping watch in Excel

Big worksheets are a bit of a nuisance to work with. I typically find myself working in one area but wanting to see what is happening in another.

To do this, I set up a watch value that tracks the figure I'm most interested in and which is off the screen. To do this, right click the cell you're interested in seeing and choose Watch to set the cell as one to watch. Now, regardless of where you are in your worksheet, you can see the value in the 'watched' cell in the Watch window - double clicking its title bar anchors it on the screen.

In earlier versions of Excel you could create your own watch window using a text box. Draw it using the Text Box button on the Drawing toolbar, click inside it, press F2 and type the cell to watch, eg =Z100 - simple and very effective.

Excel - trapping invalid data

There's a term that I love called GIGO - garbage in garbage out. When you're designing an Excle worksheet to solve a business problem you need to make sure that your data is correct or your worksheets won't give valid results.

Luckily, Excel's Data Validation tool helps you prevent incorrect data being added into your worksheet.

To set up a validation rule and to configure what will happen if incorrect data is entered, start by selecting the cells to which the rule should apply. Choose Data, Validation, Settings tab. Now, for example, to limit the data being entered to a whole number less than 200, select whole number from the Allow drop down list. When the Data area appears, choose Less Than and then in the Maximum area type 200.

To configure a user message to tell the user what is required, click the Input Message tab and type a title and a piece of explanatory text telling the user the limits to the type of Data you're expecting them to enter. This appears as a tooltip entry when any of the cells configured with the data validation rule is selected.

You can also specify what should happen if a user enters an incorrect value. To do this, click the Error Alert tab and select the type of indicator from the Style list. If the user enters incorrect data and if you have chosen the Stop style they will have two choices - Retry and Cancel and the invalid data cannot be entered into the cell. The Warning and Information styles both warn about the incorrect data but still allow it to be entered.

Once you've chosen the style, enter the Title and the Error Message which the user will see if the data in not correct. Click Ok and you can now test the rule.

Excel - finding duplicates

Excel's conditional formatting option has lots of handy uses including highlighting cells that contain duplicate data.

To do this, highlight the range to investigate, for example, a series of entries in column C starting with cell C1. Now choose Format, Conditional Formatting, select Formula Is and enter this in the formula area:

=COUNTIF($C:$C,$C1)>1

Click Format and then select a pattern and color to use and click Ok.

Now duplicate entries in the column will be indicated with the shading you've provided. You can copy and paste this formatting down to other cells in column C to include them.

Excel 2003 lists

Excel 2003 offered a cool new tool for managing data that was in a list format. It made Excel the place of choice for small lists and it simplified the process of charting list data - Excel lists expanded automatically to allow for more data to be entered and charts based on the list data automatically expanded to include the new data - wonderful!

Here's how to work with lists in Excel:

1. Turn an existing table of data into a list by clicking on a cell in the range and choose Data, List, Create List. If your list has a heading row, enable the My list has headers checkbox and click Ok. Notice the border around the list.


2. To add a row, click in the list area and click in the last (Insert) row which has an asterisk in its first cell. It is also possible to add a row in the middle of the list by clicking where the row should appear and choose List, Insert, Row.


3. When data is created as a list, the AutoFilter feature is enabled. To sort data in the list, click the dropdown arrow to the right of the column (field) to sort on and choose Sort Ascending or Sort Descending as required. To sort on multiple columns, use the Data, Sort dialog.


4. To create a complex filter for your list, click the Custom option from the dropdown list for the field that you want to create the query on. Set the tests to use and select And or Or depending on what information you need to extract. Click Ok to view the results. To display all records again, choose Data, Filter, Show All.


5. When you create a chart based on list data it will be automatically updated when you add a new item to the list. To create your chart, click in the list and click the Chart Wizard button on the new List toolbar and proceed through the Wizard as you would for any other chart.


6. To perform calculations on list data use the Toggle Total Row button. This adds a total row to the list and totals the right most column. To disable this total or create another one, click the down pointing arrow to the right of the total and choose None or a different calculation. Each column has its own down pointing arrow from which you can select the calculation to be made on that column's data.