Monday, April 6th, 2009

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.

Helen Bradley

Sunday, April 5th, 2009

Stupid Photoshop Tricks #3: Faux Reflections – Pt 1


There’s something very compelling about photos that capture reflections. These reflections can come from puddles on the ground but they’re not limited to wet environments. In fact any shiny surface can reflect its surroundings.

If you’re not lucky enough to encounter a reflection worthy of capturing, you can create a faux version with Photoshop. The process is relatively simple and you can create results that vary from looking realistic to those that are playfully faux. In this article I’ll show you three variations on the reflection theme including how to make a reflection in a mirror and how to create a puddle of water where there wasn’t one before.

Before you start working with reflections, there are some things to be aware of. One of these is to avoid attempting the impossible, such as placing a person in front of a surface and creating mirror reflection of them in it. The problem here is that you typically need two images, one shot from each side of the person to create the mirror image because what is reflected in the mirror won’t be what you’re seeing in front of it, as shown in Fig 1.

The easiest reflections to create are where the reflective surface is below the object being reflected in it. Examples of this are reflecting a building or scene in a lake or pond. This type of reflection requires only one image to achieve a semblance of reality. Another type of reflection that it simple to achieve is where you show the reflection but not the original object – in this case, almost anything can be reflected.


Fig 1 This is a photo of a person reflected in a mirror, to recreate this digitally you’d need two images, one shot from either side of the person.

Water reflection
When you think of reflections, you’ll probably think first of a scene or building reflected in a lake or large body of water. These reflections are fairly simple to create. Start with your image open in Photoshop and double click the background layer to make it into a regular layer.

Enlarge the canvas so you have room for the reflection by choosing Image > Canvas Size and read the current size from the top of the dialog. You’ll need to double the height so, to do this, click the Relative checkbox and the box in the top center of the 9 boxes in the Anchor preview. Type the value of the current height of the image into the dialog and click Ok, (see Fig 2). You should have lots of extra room below the image.


Fig. 2. Add more canvas to the foot of the image so you have room for the reflection.

Duplicate the image layer by right clicking it in the Layer palette, choose Duplicate Layer and type a name for this layer – Reflection – and click Ok. Select and delete any portion of the bottom of the image you don’t want to use. Click the layer name to select the layer and flip it by choosing Edit>Transform>Flip Vertical. Use the Move tool to drag the flipped version of the image down below the original image and align them carefully.

The reflection is in place but it’s probably won’t be particularly realistic. To add realism, add a ripple to the reflection layer by choosing Filter>Distort>Ripple and select a ripple amount of either Small or Medium. This adds a subtle distortion to the reflection and makes it look more like water, see Fig 3.


Fig 3 Add a ripple filter effect to make the reflected area look like water.

Because not all reflections will be perfect you can make the reflection disappear as it moves father away from the reflection line. To do this, add a new layer using Layer>New>Layer and fill the layer with black, muddy brown or blue/grey and drag it under all the layers. Return to the reflection layer and add a layer mask by clicking the Add Layer Mask icon at the foot of the Layer palette.

Click in the layer mask and click the Gradient tool on the toolbar. Select a White to Black gradient and set the style to Linear. Drag vertically on the image with your mouse to fill the mask with the gradient. It should be filled with white at the top and black at the bottom. If not, click the Reverse checkbox on the toolbar and repeat the gradient fill until you get a transition from fully opaque to partially transparent for the reflection layer. As you do this, the underlying black or brown layer will show through, see Fig 4.


Fig 4 The reflection has been made partially transparent so it’s not as perfect and it’s more realistic.

Other fine tuning options include duplicating the reflection layer and blur this duplicate by choosing Filter>Blur>Gaussian Blur. Blend this layer into the one below by decreasing its opacity.

If having a reflection appear as a straight line along the image doesn’t look good, you can clone elements from around the reflection to make it less obvious. To do this, add a new layer to the image and drag it on the top of all the layers. Select the Clone Stamp tool and ensure the Sample all Layers checkbox is selected. Now Alt + Click on an area to sample it and paint over the horizontal line to soften it. Cloning onto a new layer allows you to edit the cloned area separately from the original image. In fact, you may notice that nothing we’ve done in these steps has affected the original image.

Crop the image to create a more interesting result. For example, crop so the line between the original and the reflection is along the top third of the image so you’re showing more of the reflection and less of the original or crop so it’s along the bottom third so you see just a little of the reflection, see Fig 5.


Fig 5 This image has the reflection line softened by cloning and it’s cropped so only a little bit of the reflection shows.

Helen Bradley

Friday, April 3rd, 2009

PowerPoint – Create a Custom Show


Did you know that one PowerPoint presentation can be reused over and over again, in different configurations for all sorts of purposes. A large presentation can be broken up into shorter presentations and a regular presesntation can be reworked to be used as a Kiosk presentation at a tradeshow. You can do all this without creating additional files or duplicating slides which means that you can make changes to all the shows in one step.

Create the new show
1 To create a new custom show from an existing presentation, open the presentation and, in PowerPoint 2003, choose Slideshow > Custom Shows and in PowerPowerPoint 2007 click the Slide Show tab > Custom Slide Show. Click the New button and type a name for your custom show – use a name that identifies what it contains or the audience you will present it to.

Add the slides
2 From the Slides In Presentation list click each slide that you want to include in the shortened or custom slideshow and click Add to add it to the slides in custom show dialog.

Organise the slides
3 Use the arrow buttons by the Slides In Custom Show pane to rearrange the slides to a different arrangement in the custom show if desired. When you are done, click Ok to create your custom show.

Selecting a custom show
4 If you click Close you’ll be returned to your original presentation but you can select the custom show at any time by choosing Slideshow > Custom Shows, select the custom show by name and click Show. Now if you run the slide show you’ll see the slides in the selected custom show only and in the desired order.

Customising the show
5 To customize the setup for your custom show, choose Slide Show > Set Up Show. Select the Custom Show option in the Show Slides area and click your show name in the dropdown list. Select whether the show will be given by a speaker, at a kiosk, or browsed by an individual and then set the show options. Click Ok to confirm your choice.

Things to watch out for
There are a couple things to be aware of when you are using custom shows. If you have the custom show selected, while you can still work on other slides – even those not in the custom show – if you choose to view the show you’ll see only slides in the custom show and not necessarily those you’ve been working on.

To view the entire show, choose Slide Show > Set Up Show (in PowerPoint 2007 choose Slide Show > Set Up Slide Show), and select All from the Show Slides list. Now when you run your presentation you’ll see all the slides in your presentation.

Helen Bradley

Wednesday, April 1st, 2009

Stupid Photoshop tricks #2 – Fold a Photo


I love the texture of old heritage photos, particularly those that have been kicking around for some years and which have managed to accumulate folds and crinkles. While it can take many years for a printed photo to develop this sort of quality, luckily Photoshop allows us to create a similar effect for our digital photos in far less time. With the help of the Liquify filter to distort the edge of the photo and some gradient fills to add the highlights and shadows you can create the effect of a folded photo in minutes.

The Liquify filter is a tool which is often used to create funny effects like making a person’s eyes bulge or pushing their nose out of shape. It also has more serious applications, not only in photo retouching but also, as you are about to see, in creating interesting effects with your photos. We will use the Liquify filter to bend the edges of the photo to give the impression that it’s been folded and then unfolded.

Here’s how to get started on the effect:

Step 1
Open the photo that you want to use with this effect. I have used a landscape orientation photograph which will be folded into six panels. Duplicate the background layer by choosing Layer, Duplicate Layer and click Ok. Turn off the layer visibility icon for the Background layer.

Step 2
Add some extra canvas around the image by choosing Image > Canvas Size and click the middle of the nine Anchor points. Select the Relative checkbox and add a small amount of space around the image. For example, for an 7 x 5 inch image add 2 inches to the Width and Height. Click Ok to add the extra canvas, (see Fig 1).


Fig. 1. To make room around the photo so you can warp its edges, add some extra canvas.

Step 3
To alter the edges of the photo so they’re not square, you will use the Liquify filter. Choose Filter > Liquify and select the Forward Warp Tool in the top left of the screen. Select a large brush size and, working across the top edge of the image, start at a position one third of the way across and pull the edge gently up using the Forward Warp Tool. At the two-thirds mark pull down slightly.

Along the right edge, pull the image out at the half way position and do this again half way down the left edge, pulling outwards here too.

Across the bottom, push the edge up at a position one-third in from the left hand edge and push it down at a position two thirds in from the left edge.

Step 4
Reduce the brush size and push again on all these points to create a sharper distortion in the edge. Reduce the brush size again and continue to refine these points until they are smooth but obvious.

Step 5
Switch to a large brush size and push or pull on the corners of the image to get a very subtle change so the corners aren’t perfectly square, (see Fig 2). When you are done you can save the mesh so you can use it later on by clicking the Save Mesh button. Click Ok to return to editing in Photoshop.


Fig. 2. The Liquify Filter lets you warp the edges of your image so it will look like it has been folded.

Add the shadows
Shading will help you achieve the effect of a photo that has been folded and then opened flat:

Step 6
Use the Rectangular Marquee tool to select the top half of the image. Apply a feather to this selection by choosing Select > Refine Edge and adjust the Feather value to achieve a soft edge, (see Fig 3). Add a new layer by choosing Layer > New > Layer and click Ok.


Fig. 3. Use the Refine Edge tool in Photoshop CS3 to add a feather to the selection to soften it.

Set the foreground color to white and the background color to a mid-grey. Click the Gradient tool – it shares a toolbar position with the Paint Bucket Tool – and click in the gradient display on the tool options bar to open the Gradient Editor. Select the Foreground to Transparent gradient and click Ok, (see Fig 4).


Fig. 4. The shadows and highlights are created using a Foreground to Transparent gradient.

Drag your mouse from the top of the image downwards and just over the edge of the selection you made. You will get a gradient which ranges from transparent to white and the white part will be across the middle of the image. The gradient fill is contained inside the selection.

With this layer still selected, choose Layer > Create Clipping Mask so that the gradient fill constrained so it appears over the image and no longer outside it, (see Fig 5).


Fig. 5. To confine the gradient so it covers the image and not the area round it, use Layer > Create Clipping Mask.

Set the Blend Mode for the layer to Screen to lighten the image and adjust the Opacity so the effect is subtle – around 30-40% Opacity is a good value, (see Fig 6).


Fig. 6. Blend the highlights into the image using the Screen layer blend mode and a reduced layer opacity.

Step 7
Invert the selection by choosing Select > Invert. Add a new layer, switch the foreground and background colors by clicking the Switch Foreground and Background Colors icon and apply a light grey gradient fill to the bottom half of the image, this time drag the gradient from the bottom of the image upwards to finish slightly over the top of the selection marquee.

Again, create a clipping mask from this layer by choosing Layer > Create Clipping Mask. Set the blend mode for this layer to Multiply and reduce the opacity to get a slight darkening of the photo along the middle ‘fold’ line, (see Fig 7).


Fig. 7. Add the shadow to the bottom of the image using a grey gradient, this time using the Multiply blend mode.

Step 8
To add the additional highlights you will need six layers, one for each of the six panels that would be created by folding a photo three times. Each panel will have either a white or grey gradient applied to it. When you apply a grey gradient, use the Multiply blend mode and when you apply a white gradient, use the Screen blend mode for the layer.

When creating each panel, create a new layer, make your selection using the Rectangular Marquee tool, feather the selection, apply the gradient, set the layer blend mode, adjust the layer opacity and create the layer as a Clipping mask.

The gradients should be applied as follows:
top left – apply a white gradient to the right hand side of the panel
top middle – apply a grey gradient to left and right sides of the panel
top right – apply a white gradient to the left side of the panel
bottom left – apply a grey gradient to the right hand side of the panel
bottom middle – apply white gradient to the left and right sides of the panel
bottom right – apply a grey gradient to the left side of the panel

You might recognize there is a pattern to how these gradient fills are applied.


Fig. 8. Add shadows to all six of the panels that the folded photo shows.
Step 9
Click the Background layer and choose Layer, New, Layer. Fill this layer with white.

Click the Background Copy layer to select it and click the Layer Style icon at the foot of the Layer palette. Select Drop Shadow and add a drop shadow with the angle of the light at around 130 degrees. Click Ok when you’re done. Right click the Effects layer that contains the Drop Shadow and choose Create layer. This moves the Drop Shadow to its own layer.

Apply a second drop shadow to the Background Copy layer, this time make the shadow smaller, lighter and configure it so the light comes from the opposite direction.


Fig. 9. Add two drop shadows using the Layer Style tool to make the paper look dimensional on the page.

The final image looks like our photo has been folded in three and then opened out. This process can be applied to other creative folds. You can use it to create a photo that has been ‘folded’ more or less times than the three shown here, or one which has a bent corner or which is folded accordion style. A simple combination of lights and darks created using gradient fills will give you a realistic folded look.

Photo credit: Brenda Smith

Helen Bradley

Page 2 of 212