Thursday, February 22nd, 2007

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.

Helen Bradley

Wednesday, February 21st, 2007

Getting into PowerPoint Actions

When you create PowerPoint presentations that will be used or viewed by one person at a time, rather than by a group, you can customise movement using Action buttons.

Using an action button you can take a user to another place in the presentation or offer Yes and No as a choice to progress to the next slide and take the user to different places depending on their answer.

To add an action button, click on the slide to contain the action and chose Slide Show, Action Buttons and choose Action Button: Information and drag to place the button on the slide. When the Action Settings dialog appears, choose Hyperlink to and select the slide to link to. When a user clicks the button they will be taken to that slide.

If you want them to be able to return to where they came from you can add an Action: Return button to the slide you’re sending them to and they can click it to return to where they came from. This is handy for providing a slide with more information on the topic, for example.

You can use Actions without the buttons that PowerPoint provides. So, for example, you can use custom icons or AutoShapes and place them on the slide. Then right click the image and choose Action Settings from the menu. From here you can configure the same action options for the shape as you do for a PowerPoint Action button.

Action buttons can also be used to display an Excel worksheet or chart, a Word document, or even a web page, for example. When you’re creating an action button that should appear on all the slides in a presentation, you should create it on the Slide master by choosing View, Masters, Slide Master. Then the action button will be accessible to all slides in the presentation. If you need to block it from one slide, place an object over the top of it – action buttons under other objects are disabled and cannot be clicked.

Helen Bradley

Tuesday, February 20th, 2007

Outlook 2003 – out of office reply

If you’re not around to read your email you can configure Outlook to automatically reply to incoming messages to tell senders that you’re out of the office and when you’ll return.

There are two possible scenarios, either you’re using Microsoft Exchange Server at work or you’re using a standalone version – most likely at home.

If you’re using Outlook 2003 with Microsoft Exchange Server service you’ll find in your profile that you have an Out of Office Assistant you can use to manage replies to your emails when you’re out of the office. To do this, choose Tools, Out of Office Assistant and click the I am currently Out of the Office option. In the AutoReply only once to each sender with the following text area type the message. It is usual to indicate that you are out of the office and the time you anticipate returning.

You can also create a rule so that only certain types of messages receive this reply. To do this, choose Tools, Out of Office Assistant and click the Add Rule option to specify the types of messages to which the reply will be forwarded. For example, you may create one reply for external mail and one for internal mail.

If you don’t have the Out Of Office Assistant available (this will be the case if you’re using a standalone version of Outlook), you can mimic the solution using a template reply email and a rule. Ensure you are using Outlook as your email creating software – this won’t work if you’re using Word. To check this choose Tools, Options, Mail Format tab and ensure Word is not selected here and ensure Plain Text is selected. Now follow the instructions in the previous tip to create a reply template message.

Now choose Tools, Rules and Alerts and click the E-mail Rules tab and click New Rule to start the Rules Wizard. Click Start from a blank rule and select Check messages when they arrive, and click Next. When prompted with Which condition(s) do you want to check? select a box describing the type of message to apply it to – such as emails received through the specified account or Sent Only To Me. Click Next. When prompted with What do you want to do with the message?, click the Reply using a specific template option. In the second panel, click the specific template option and select your reply template and click Open. Click Next, add any exceptions and click Next. Name your rule and set it to run and click Finish.

Outlook tracks who has received the replies and won’t send multiple replies even when it receives multiple messages from the same person.

Helen Bradley

Monday, February 19th, 2007

DPReview postings

There has been some discussion on the forums of DPReview.com about opinions written by someone who calls themselves “Blue Water”. They purport to be me.

Just to clarify the situation, I am not Blue Water. Until today I didn’t know DPReveiw.com even existed and I had never posted there. The opinions and comments of Blue Water are not mine.

Helen Bradley (the real one!)

Helen Bradley

Monday, February 19th, 2007

Picasa: quick and easy photo editing for free

Picasa is a great free photo organiser and editor and you can find and download it from picasa.google.com. Here are some of the key editing features in Picasa. If you already use Picasa, I suggest you update your version now. In past versions, edits you make in Picasa were only visible in Picasa and were not saved with the image unless you exported it. The new version avoids these problems.

  1. To edit an image, from the main screen (called the Library), double click the image to open it in the editor. To straighten a photo, select the Straighten tool and drag the slider to the left or right to rotate the image until it is straight – use the grid as a visual guide to gauge when it is straight. Click Apply to apply the fix and crop the uneven edges from the image.
  2. To lighten an image where the subject is in shadow, choose the Tuning tool and drag the Fill Light slider to bring the detail out of the shadows – this mimics using the fill flash on your camera. The Temperature slider adjusts the colour of the light in the image, drag it to the right to add yellow to the image and to the left to add blue.
  3. To fix an image with poor contrast you have two options. The first is to use the Auto Contrast fix in the Basic tools. If the fix isn’t enough or still needs some tuning, select the Tuning tab and adjust the Highlights and Shadows until you get the desired result. To fix a batch of images, select them in the Library by Control + clicking on them and choose Picture, Batch Edit, Auto Contrast.
  4. To crop an image for printing, select the image and choose the Crop tool. To crop for 4 x 6 printing, choose the 10 x 15 option (the ratios are the same) and drag over the image. Drag on the edges of the shape to size it larger or smaller but still in the correct proportions. Click Apply to apply the change to the image.
  5. When you’ve finished working on an image, sharpen it using the Effects menu and choose the Sharpen option. You can use this tool multiple times if needed to sharpen the image – every time you click it, the image is sharpened a little more. If you sharpen too much, click the Undo button to roll back the changes. An image you will print should be sharpened more than one you’ll simply preview on the screen.
  6. To turn an image into a black and white image, choose the Effects, Filtered Black and White. Click the Pick Color box and test the colours that appear – each will give a slightly different black and white image based on the colours in the original image – pick the version that looks the best – ie where there are best variations between dark and light areas.

Helen Bradley

Sunday, February 18th, 2007

Autonumber a series of Word documents

Word does not contain any option for automatically numbering a series of documents with a consecutive number – the type of thing you might want to do if you are using Word to create invoices or numbered purchase orders. 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.

Helen Bradley

Saturday, February 17th, 2007

Start Smart in Excel

When Excel starts a default new workbook displays. This workbook contains many of the default settings for Excel and it can be customized so it looks the way you want it to look.

You can configure the defaults for your starter Excel worksheet if you place your settings in one of the two Excel templates; Book.xlt and Sheet.xlt. Neither file is required but, if you have one or other, or both, stored in your XLStart folder then Excel opens them whenever it opens and uses the data you have stored in them as its own default settings.

When you’re creating either or both of the files make sure to put the relevant settings in the right file. Book.xlt should contain the defaults for all new workbooks such as styles and toolbars.

Use Sheet.xlt for options appropriate for all new sheets in a workbook which are added when you choose Insert, Worksheet. A word of warning, when you save a workbook as sheet.xlt make sure it contains only one Sheet.

Helen Bradley

Friday, February 16th, 2007

Misunderstood Photoshop – Clipping Paths

My latest article in the Misunderstood Photoshop series is out today.

The Photoshop clipping mask feature is a quick way to create effects such as text filled with a photograph. Helen Bradley points out more handy uses for clipping masks, such as limiting the effect of adjustment layers, or simplifying the process of editing a portion of an image. You can follow along with the accompanying video tutorial to see exactly how it’s done.

Find it here: Misunderstood Photoshop – Clipping Masks

There are more articles to come!

Helen

Helen Bradley

Friday, February 16th, 2007

Shameless Self Promotion

Ok, so it’s not exactly a tip of the day, but I already did today’s so I feel it’s justified.

I have lots of cool Photoshop tutorials around my site, check these out:

Photoshop Brushes tutorial
Learn how to create your own Photoshop Brushes

How to use Adjustment Layers
Make photoediting a simple and undoable process using Adjustment layers

Photoshop Gradients
Fix images and create colorful effects using Photoshop Gradients

Straighten an image in Photoshop, Elements and Paintshop Pro
Straighten your images with this simple to follow tutorial that covers the popular photoediting programs

Photoshop Shapes -New!
Helen Bradley explains how to use Photoshop Shapes to edit your photos and create fun shape overlays.

Create and use Photoshop Masks
Masks aren’t as hard as you think they are and this tutorial makes them simple to use.

Create seamless patterns in Photoshop
Whether you need them for the web or for a background for an Excel worksheet, here’s how to create great seamless patterns and we’ve included a mini tutorial on TV scan lines.

Color match photos in Photoshop
Even if your photos were taken in totally different lighting

Color Swatches in Photoshop
Create your own custom color swatches and use them in your photo editing work.

Ok, so that’s done, now back to the tips..

Helen Bradley

Friday, February 16th, 2007

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.

Helen Bradley