Saturday, January 26th, 2008

Excel 2007 makes Lovely Lists

lists 737621 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.

Wednesday, January 16th, 2008

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.

Monday, January 14th, 2008

Setting tabs in Word

If you only ever use the toolbars in Word you are missing out on a lot of functionality. One thing you can’t do using the toolbar that you can do with menus is to set a dot leader tab.

Choose Format > Tabs and not only can you select the type of tab and its exact position on the ruler but you can select from one of four leader styles. These leader characters appear before the tab stop, so they extend from the last character on the line to the tab position and they are the tool to use when creating lines for users to handwrite text into, for example.

Saturday, January 12th, 2008

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.

Thursday, January 10th, 2008

Format alternate rows in a worksheet

condformat 799185 Format alternate rows in a worksheet

When you’re working with a large worksheet where the data appears in rows across the sheet, you may find it difficult to keep track where you are as your eye moves across a row. You can solve this problem by formatting each alternate row in the worksheet a different colour.

Select the entire worksheet, or just the area containing the data, and choose Format, Conditional Formatting. From the first dialog choose Formula Is and, in the text area to its right, type =mod(row(),2)=1. Click the Format button and set the format to use for each alternate row in your worksheet (a light pastel colour is a good choice). Click Ok twice and each alternate row in your worksheet will be formatted accordingly.

You can apply the same concept to formatting alternate columns if this is the way you view the worksheet. In this case use -=mod(column(),2)=1.

This formula uses the MOD function which calculates the remainder when the current row number is divided by 2 and then tests to see if it is equal to 1. If it is, then the row is formatted, if not, it isn’t. For the first row, the remainder when the row number (1) is divided by 2 is 1 and that is equal to 1 so the answer is true and the format is applied to the first row. The same result happens for each odd numbered row (any odd number divided by 2 gives a remainder of 1). For even numbered rows, there is no remainder so 0=1 is a false statement and the format is not applied.

Saturday, December 22nd, 2007

Word 2007 – Building Blocks

Whenever you type the same thing in Word more than once, ask yourself if there isn’t a smarter way to work. The new Building Blocks feature in Word 2007 lets you create and save frequently used content so that it can be easily inserted into your document.

So, for example if you repeatedly add a disclaimer or a bio to your documents, create it as a building block and insert it into the document when required. To do this, type the text to create as a building block and select it. Choose Insert, Quick Parts and choose Save Selection To Quick Part Gallery. Type a name for the block, select the gallery to attach it to and the category and description of the Building Block. It’s best to save the building block to buildingblocks.dotx as building blocks saved to this file are available regardless of which template is currently in use. Choose the options for the content and click Ok.

In future, to add a Building Block to a document, click the Insert tab and then Quick Parts and select the block to add.

Tuesday, December 11th, 2007

Selecting chart elements in Excel 2007

elements 757374 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.

Wednesday, December 5th, 2007

Images in Word tables

It is possible to place an image inside a table cell in Word. To do this, first create a table, click inside the cell and then choose Insert Picture > From File and select the image to insert. You can size the image inside the table cell as requiredby dragging on its sizing handles.

If you set the image’s Text Wrapping to Behind Text or In Front of Text the image will no longer be constrained to the table and will jump out of it and operate independently of it. To wrap text around the image inside the table cell use the Square or Tight options for best results. In Line With Text only wraps one line and it’s pretty awful.

Thursday, November 29th, 2007

Help!? WTF?

macrosecurity 703383 Help!? WTF?

First up today, a pet peeve of mine. Why do folks head up emails with Help! or Information wanted? It’s so damn inconventient to have to open an email just to know you can’t help. Is it so difficult to say, Chart formatting issue or Contact details for XYZ needed? Obviously it is for some people. Sheesh!

Ok, rant over.

Problem: What do you do if a macro won’t run. You’ve opened a file with a macro in it and you go to run it and nothing happens? Problem is, most likely, that your copy of Word is configured so you can’t run macros. It’s a security thing but it’s no good if you need to run the macro is it?

Solution: Choose Tools > Options > Security, Macro Security and select an option that will let you run macros – go for the most secure option which still lets you do your work. Close and reopen the document and try again. Interestingly enough you might encounter this problem as you’re developing your macros. Word lets you create macros by may not let you actually run them.

Wednesday, November 28th, 2007

Do You Undo?

undo 774290 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.

Tuesday, November 27th, 2007

Preview image in Word

You’ll have seen preview images when you open a file in Word. If you choose File > Open and, from the Views option list you choose Preview, you will see either a small image of the full page or some of the text on the page. All this begs the question of what determines what you see?

The full page preview is an option when you save a file in Word. To configure it, choose File > Properties > Summary tab and enable the Save Preview Picture checkbox. Now, when you save the file it will have a preview image saved with it which will show in the preview area.

To ensure the Properties dialog appears everytime you save a file the first time so you can configure the Save Preview Picture option, choose Tools > Options > Save tab and enable the checkbox for Prompt for Document Properties.

Tuesday, November 27th, 2007

Remove spaces – Microsoft Word

I’m sure it’s happened to you just as it has to me. You copy and paste some text in from the web or an email message and it comes in with leading spaces – on evey line. There are lots of ways to remove the problem starting with hitting the delete key way too many times. Stop already!

There is, however one very smart way to do it without getting a repetitive strain injury. Select the lines of text and press Control + E to center the text. With the text still selected press Control + L to left align it and voila! the spaces are gone.

Wednesday, November 21st, 2007

Sticky spaces in Word

Picture this, you have a line of type in Word with a phone number in it. But… when ever you type it the first part of the phone number goes on one line and the next part scrolls around to the next line. It just won’t ‘stick’ all together.

What you need is a hard space. This is some thing that looks like a space, prints like a space but sticks things together. To use it, remove the space that is between the two pieces you want to stick together then press Control + Shift + Space Bar and you have your hard space.

Word also has a sticky/hard hyphen. It shows between two words but never splits words across the end of a line. Same thing – Control + Shift + Hyphen.

Monday, November 19th, 2007

Excel charts – create an overlapping series

overlap 743194 Excel charts   create an overlapping series

Sometimes an Excel chart will look better if your series overlap – this might be the case when you are comparing data from two years and where you want to show how the values have increased from one year to the next.

To make your series overlap in Excel 2007, select one series, right click and choose Format Data Series. Click the Series Options and decrease the Gap Width (it closes the chart up nicely) and incease the Series Overlap. Set the Series Overlap to around 60% and the Gap Width to around 30% for a good result. This is particularly useful when you are using images in place of colors for the bars of your chart but works in almost any situation.

Saturday, November 17th, 2007

Paste to a layer mask

Ok, here’s the dilemna. You have two images open in Photoshop and you want to add one image as a layer mask into the other.

One solution is to copy the first image, then switch to the second. Click the layer mask and switch to the Channels palette. The layer mask appears as a channel. Select the channel’s visibility icon to make it visible, select the channel to make it active, and click Edit, Paste. Deselect its visiblity, reselect the RGB channel to make that one active, switch back to your Layers palette and the pasted selection is in your layer mask. This solution has the advantage that the copied/pasted piece doesn’t have to be the same size as the layer mask.

The alternate solution if the two images are the same size, is to use Apply Image. Select the target layer mask, choose Image, Apply Image and, as the Source, select the image to copy from, the layer to copy and click Ok. Now the selected layer (or the merged source) is pasted into the Layer Mask.

Two alternatives, the second is easier to use but it does require two same size images.