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.

Go Ahead - Make My day!

Ok, so this post is seriously off topic but I just had to write it. I subscribe to a great email newsletter written by horseman Doug Emerson and which you can find here: www.profitablehorseman.com.

He writes great stuff, common sense, smart and very business oriented. I don't know anyone who couldn't benefit from his 'pearls of wisdom' whether or not you know what end of a horse the food goes in.

Ok so what about the Make My Day bit. Well, I took the time to write to Doug today to say hi and thanks for his stuff. It was just a short email, a few lines but very soon a reply came back and in it he said "you made my day!" - that's exactly what I feel when someone who has read an article I wrote writes to say "Hi and Thanks". When the "Hi and Thanks" comes without strings, no questions attached, just an out of the blue pat on the back it really does just that, it makes your day, whoever you are.

So, my challenge to you? Go ahead and make someone's day. Next time you read something and think "that was good/cool/interesting/well written..." email them. Say hi and thanks. Pass the good word feelings around, curiously, I think (in the process), you'll find it makes your day too!

Getting alignment right (or left), in PowerPoint

Let's face it, it's easy to dump pictures, text and charts on a PowerPoint slide but it can end up looking like the dog's breakfast - at least what I imagine a dog might eat for breakfast.

When you want your slide to look good, you need to have everything aligned neatly on it. To do this, click on the first object to align and Control + click on the second. From the Drawing toolbar choose Draw, Align or Distribute and then an option such as Align left to align both objects so they're lined up along the left edge of the object that is further to the left on the screen.

In a few simple clicks you can restore order to an unruly slide.

Seeing double - split windows in Word

Long documents are harder to manage than short ones, and it can be difficult to cut and paste an item on page 1 all the way to page 20. I find it easier to do when I can see the source and target area on the screen all at once. That's why I love the Split window tool.

Here's how to use it, position the document on the screen where you want it to be split in two and choose Window, Split. Now click where you want should be. You'll get two horizontal panes each with its own scroll bars and you can move around the panes independently and show different places in the document in each. You can also drag and drop between panes.

When you're finished editing, choose Window, Remove Split.

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.

Change the color of unread Outlook messages

Outlook highlights all unread messages in your Inbox using bold type. If you'd like something more flashy or subtle, simply change the formatting option. To do this, click your Inbox and choose View, Arrange By, Current View, Customize Current View. Click Automatic Formatting, click Unread Messages and click Font and set the color, font and size to use for unread messages. When you're done, click OK - simple, and from now on, very colorful!

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.

Too cool for school - Office 2007 SmartArt

New to Microsoft Office 2007 PowerPoint, Excel and Word is the SmartArt feature which is one you’re just going to love.

To test it out add a new slide to a PowerPoint presentation, for example, and select the blank layout. From the Insert tab on the Ribbon, select SmartArt and then one of the SmartArt objects.

I like the one called Staggered Process which I've shown here as it makes a great display for a simple step-by-step process. Select your choice of design and then you’ll see text brackets appear on the screen. Click in them or click the double-pointing arrows at the left of the SmartArt object and type your text in the special dialog.

Once you’ve got your bullet points in - and you can add more than the default three if you need more - you have a simple step-by-step graphic. But - this is only the beginning.

There are lots of different looks for your graphic including beveled edges and 3D effects, and you can choose these from the SmartArt styles dropdown list in front of you. You can also change the colors used in the design which, of course, are based on the current document Theme colors. Change the Design Ttheme and the look of the project changes with it.

It’s about as simple as it’s ever going to be to add great looking step-by-step graphics to a PowerPoint slide. They are, seriously, way cool...

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.

Vale BoxOver?

Yesterday morning I was investigating this cool tool, BoxOver. It's a JavaScript routine for making the most wonderful popup boxes that appear over the contents of your web page.

In what was to become a classic "snooze and you lose" scenario I planned to return later that day to get the code.

Yeah! right. Like the planets were all in some sort of weird cosmic alignment on that one. By nightfall, the site was gone. Seems like the owner probably didn't pay their domain name registration and it expired - the very day I was planning to download their utility. So, the site is gone and because you could only download the tool from that site, I now can't have it.

I'm off to check the registration expiry dates on all my sites, this one, http-design, my namesake, helenbradley.com and my ecommerce site backgroundartz.com. It's a salutary lesson on the importance of making sure you pay your registration fees on time or there are squatters out there who will move in within seconds of the time expiring - there's no late fee payable, when you're over time, you're out.

If anyone knows where BoxOut can be downloaded from, I'd love to hear from you!

In the meantime - check your domain name expiry dates. Mark your calendars and pay up well in advance or the next person who loses years of good will could be you.

<rant ends>

UPDATE: BoxOver is back! Well, sort of.. read on..

Actually, on reflection, I think BoxOver never really left. You see, it comes and goes... it's there one time you visit and not the next. So, first off, snaffle a copy while you can... it's the neatest utility around - if it's there when you visit the site, grab it and don't think twice!

I think the site probably has a daily download limit and when it exceeds this it closes down and you get a funky portal view which is useless. The solution is to get up really early (or, my preference, stay up really late!) and snag it when a new day begins and the site is up.

Anyway, short answer is - it's there, you just have to be lucky and if you miss it once, try again later.

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.

PowerPoint 2007 Torn Photo Edges

One of the cool things about PowerPoint 2007 is the way that you can create fab photo edges with no work at all.

To see this at work add a new slide using the Home tab, click New Slide and then from the Layout list choose Picture with Caption. Now click on the icon in the middle of the slide to add your picture.

Go to the Design tab and choose one of the designs to use for your slide. You’ll see that designs like Opulent create a stacked photo effect, Paper creates a rough paper edge effect, Solstice creates an effect where the photo looks adhered with sticky tape (this is soooo cool!), Concourse has an interesting shadowed edge effect, Flow tips the image on an angle, and Foundry rounds the opposing corners.

From here you can click the picture and then click the Picture Tools, Format tab to add other picture effects to your image such as a reflection or soft edges.

There’s really an almost unlimited number of variations available. I dare you to stop at just using one!

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.

Axing Word Smart Quotes

I like Word smart quotes most of the time but sometimes, like when I'm preparing an email or some code, they're not needed and can make a mess of the project.

You can, quite easily, stop this happening if you choose Tools, AutoCorrect Options, AutoFormat as you Type tab and disable the checkbox for "Straight quotes" with "smart quotes" and click Ok. Now you'll have straight quotes in your document.

Reverse the process to get them back.

This simple macro, attached to a toolbar button can do the toggling work for you (just make sure you put everything between the Sub and End statements appears on one line:

Sub changeQuotes()
Options.AutoFormatAsYouTypeReplaceQuotes = Not (Options.AutoFormatAsYouTypeReplaceQuotes)
End Sub

Inserting Images in Word

In recent implementations of Word they broke the image insertion tool. When you insert an image it comes in as "Inline with text" which means it won't move, it breaks up lines and, in general, looks awful.

Luckily it's fixable and permanently so. To do this, choose Tools, Options, Edit tab. From the Insert/Paste pictures as dropdown list choose something like Square or In front of Text (in fact anything other than In line with text). Click Ok and the change will be permanent. Although you can, of course, always change the setting for any inserted image if you really do want to put it inline with text by selecting the option from the Picture toolbar on an image by image basis. My bet is you won't ever do it though!

PowerPoint Guides

If you're a perfectionist like I am (on a good day, anyway), you will want everthing lined up neatly on your PowerPoint slides. To do this, use Drawing Guides. To do this, choose View, Grid and Guides and enable the Display Drawing Guides on Screen checkbox.

You'll see two cross hatch grid lines. To move them simply hold your mouse over them and move one. To create a new guide, hold the Control key as you drag on an existing guide. To remove a guide drag it off the slide.

You can now neatly line everything up that needs to be lined up.

Smaller spaces on PowerPoint Slides

Sometimes there is just too much space between the edge of the slide and the bullet character that PowerPoint uses.

However, luckily, there is a way to fix the problem and to make the space smaller. Start by displaying the ruler by choosign View, Ruler. Then select the text to alter and, on the ruler you'll see the marker that controls the positioning of the bullet. Drag it with your mouse, if you need to do this in small amounts, hold Control as you do it.

Which is a bit strange really, because in Word, for example, holding Alt is how you get to fine tune spacing.. hmm! well, if nothing else, these crazy mixed up keystrokes keep some of us in business.

Joining data in Access queries

When your Access query results need to look neat and tidy, you'll often want to join related pieces of data together that actually appear in separate fields of the file. For example if you have a list of first names and last names in separate fields (as you should), you can still view them in a single column in your query results.

To do this, join the data in the query data sheet, for example this will join the data from the FirstName and LastName fields and display it as a proper name in the query results:

FirstName & " " & LastName

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.

Color code Outlook 2003 appointments

I'm a visual person so I like to see things and I use the Outlook calendar to plan my day. But some appointments are really important and some I can miss if I need to.

So, I like to color code my appointments so I know at a glance what has to be done. I show personal appointments as one colour, online seminars and things that would be nice to attend but which I don't have to attend as another color and stuff I have to do as yet another color.

To do this, open the Calendar and select the Calendar Coloring button on the Standard toolbar. Choose Edit Labels and, opposite each color make sure the descriptions for the types of appointments match those you want to use and, if not, change them (you only need to do this step once).

Then, to color code an appointment click the Label drop-down menu and choose the color to use - simple but very effective!

PowerPoint preview and edit

I'm one of those people who love to do multiple things at one time. I can listen to a movie, work and have the washer and dryer chugging away in the background while I'm planning a weekend art project.

That's why I love today's tip. It lets you preview a PowerPoint presentation at the same time as you edit it.

Simply, choose Slide Show and while clicking View Show, hold the Control key. You'll see your slide show on the screen in the top left corner and PowerPoint visible in the remaining area. You can work on the show and preview it (and your changes) the same time.. neat idea?

Cool moves in Publisher

Here are some of my favorite tips for getting things lined up in Publisher 2003:

To move a number of objects together, select them and choose Arrange, Group from the menus or click the Group button which appears when the objects are selected. You can now move and size the objects all at once. Choose Arrange, Ungroup to ungroup them again.

The Nudge and Measurement menus help you move objects in small increments. Choose Arrange, Nudge and tear off the Nudge menu to have access to the buttons. The measurement toolbar (View, Toolbars, Measurement) helps you position objects with pinpoint accuracy.

To create a ruler guide, position your mouse over the horizontal or vertical ruler bar and, when the mouse changes shape, drag the ruler guide into position. To display rulers, choose View, Rulers. Use these guides as an aid for lining up objects on your page. Right click the guide to access other options.

Click and go in Word

You already know that you can simply double click anywhere in the Word editing window and immediately the cursor appears ready for you to start typing. But clicks elsewhere do practical and timesaving things too:

1. Double click a ruler (vertical or horizontal) to open the Page Setup dialog
   
2. When you have a header or footer set up, double click it to go to Header and Footer view
   
3. Double click the indent marker on the horizontal ruler to open the Paragraph dialog
   
4. Double clicking a bullet or number in a list opens the Bullets and Numbering dialog
   
   
   and, finally for today at least:
   
   
   
5. Double click on the column marker in the ruler when you're inside a table (or double click the table's move handle) to open the Table Properties dialog.

Grouping AutoText entries in Word

Yesterday I explained how to use AutoText entries to speed up entering data. Today, I'll show you how to organize your AutoText entries into groups on the menu using Styles:

You create or add an entry to AutoText groups on the AutoText menu by saving an AutoText entry formatted using a style with the name you want to be added to the menu. For example, to add an AutoText entry to the Closing group, type the text, select it and format it with a style called closing (or create a style with that name). Then save the text as an AutoText entry.

When an AutoText entry is saved that is formatted with a style a new entry with that style name appears on the AutoText menu or the item is added to the submenu if it already exists. So, to enter something on the closing submenu, format it with a style that you have called Closing before creating it.

AutoText in Word

The AutoText tool in Word can help speed up your work by automatically inserting pieces of text and images. Here's how to use it:

Step 1
To create an AutoText entry, type and then select the text to save and choose Insert > AutoText > New, type the name for the entry and click Ok. You can also do the same thing with images inserted into a document and create an AutoText entry from one.

Step 2
To later use a saved AutoText entry, type the name of the entry. When you have typed sufficient letters to uniquely identify the entry (at least four), Word will prompt you with the AutoText name. Press Enter to insert the AutoText entry. You can also type the name and press F3 or select the entry by choosing Insert, AutoText, AutoText and choose it from the dialog.

tomorrow... how to create your own groups in AutoText to organize entries.

Autonumbering documents in Word

Word does not contain any option for automatically numbering a series of documents with a consecutive number. The solution is to create a macro to do the work for you. Start with a template that has a macro that runs when ever the template is used for a new document. The macro should read a number stored in a file on your drive, add it to your document and then, to prepare the number for the next time it's required, the number should be incremented by one and be written back into the file.

To create the solution, create a new document (or open an existing one to use as a template) and click where you want the sequential number to appear, and choose Insert, Bookmark, type docNum in the Bookmark name area and click Add.

Save this file as a template by choosing File, Save As, from the Files as type list choose Document Template (*.dot), give the file a name and click Save. With the file still open, choose Tools, Macro, Macros and type the name of the macro docNum and, from the Macros In list choose the template file name for the file you just saved and choose Create.

Type this macro as shown, the sub and end sub lines should be there already:

Sub docNum()
Dim MyString, docNumber
FileToOpen = "c:\windows\docNumfile.txt"
Open FileToOpen For Input As #1
Input #1, docNumber
Close #1 ' Close file
ActiveDocument.Bookmarks("docNum").Select
Selection.InsertAfter Text:=docNumber
docNumber = docNumber + 1
Open FileToOpen For Output As #1
Write #1, docNumber
Close #1 ' Close file.
End Sub

Now choose File, Close and Return to Microsoft Word. With the template on the screen, choose File, Close and answer Yes when prompted to save your changes.

Now open Notepad and type a number 4 or 5 numbers less than the number of the first quote you want to use. So, if you want to start numbering at 200, type 195 so you have a few numbers to use to test the process. Choose File, Save As and save the file as a text file, calling it docNumfile.txt and save it to this folder: C:\windows. Close Notepad

To test the process, choose File, New, choose the template file and click OK. Now run the macro by choosing Tools, Macro, Macros, docnumb, Run. If you have everything right the document number will be inserted in the document.

When this is working fine, alter the macro so this process of inserting the document number happens automatically whenever you create a new document based on this template. To do this, choose Tools, Macro, Macros, click on docnumb and click Edit. Change this macro's procedure name by altering this line of code:

Sub docnumb()

to read

Sub AutoNew()

Choose File, Close and Return to Microsoft Word. With the template on the screen, choose File, Close. Say No to saving your changes to this file but answer Yes to save the changes to your template file.

Now test again by creating a new file using File, New, choose your template and click OK. The document number should be added automatically to the new document.

If, in the process of testing you find you go past your starting document number, open Notepad and open the file docnumfile.txt, type a new starting number and save it again.

Use the PowerPoint menu to go back

By default, when you are viewing a PowerPoint presentation pressing the right mouse button shows the PowerPoint presentation menu.

If you'd prefer that, instead, it moved back one slide, then you can configure it to do this. Choose Tools, Options and click the View tab. Clear the Show menu on right mouse click checkbox and click Ok.

Now, the left mouse takes you forward and the right takes you back. If you need to see the menu at any time, pressing Shift + F10 will display it.

Add a picture to a Visio Stencil

When you repeatedly use an image in Visio, you can add it to a Stencil so it's ready for use at any time. This is handy for your company logo, for example.

Step 1
To do this, save your logo as a file capable of being imported into Visio such as GIF, BMP or Tif. Open a new blank Drawing and choose Insert, Picture, From File and locate and open your picture. Use the sizing handles to size it to the size you want it to default to when you insert it into a drawing in future.

Step 2
To create the stencil that will contain your logo, choose File, Shapes, New Stencil (metric) or open an existing stencil from your My Shapes collection to use. If the icon to the left of the stencil name does not show a red asterisk in its top left corner, right click the icon and choose Edit Stencil. It will now show a small asterisk indicating it is able to be edited.

Step 3
To add the image to the Stencil, hold the Control key and drag and drop the image onto the stencil. Click in the name area below it and type a name for it descriptive of what the image contains. Right click the Stencil's icon and choose Save As and give it a name (or, if you're adding to an existing stencil, choose Save). Again, right click the icon to the left of the stencil name and disable the Edit Stencil option.

Step 4
In future, to locate and use your image, choose File, Shapes, My Shapes and open the stencil containing the shape to use. Drag the shape from the stencil and add it to your document. Create stencil shapes in this way containing your company logo, address details and other objects you repeatedly use on your drawings.

Background Graphics in OneNote

One of the improvements in OneNote 2003 SP1 is the addition of background graphics which appear on a separate layer of the note page and which are stable and won't move.

To create a graphic as a background, insert it onto the page and then right click and choose Set Picture as Background.

To alter the graphic later on, you must promote it back onto the page by right clicking somewhere over the top of it and disable the Set Picture as Background checkbox.

This feature is useful for creating custom stationery and for adding notes over the top of your graphics – the graphic stays more stable and you're less likely to encounter the situation where your pointers move out of position.