Passing a spreadsheet around between organizations has a hidden problem; one that can easily make trouble. And the trouble comes, not from the spreadsheet, but from the default date setting on the computer.

Excel uses the default date setting to interpret the order of date information. Whether its’ Month-Day-Year or Day-Month-Year, or even Year-Month-Day, that information comes from the OS date settings. These are settings that we don’t often think about once we’ve set them. And typically, they are the same throughout an organization.

But take the spreadsheet you’ve designed, that uses Month-Day-Year into a Day-Month-Year organization, and all sorts of problems crop up.

The first problem is that you might not notice immediately; if July 6, turns into June 7 – that might not jump out at you as a problem. If you are lucky, you’ll spot something weird about the 12th of Month 21 …

So how do you nail down those dates so they can’t shift? One strategy is to break up your date entry into your preferred format, and then rebuild the date using the DATE function.

The syntax for the DATE function is =DATE(year, month, day)

Using the Date function to reassemble a date from separate cells

Here you can see the DATE function is building a date from the values in three separate cells: A3, B3 and C3 and the formula looks like this =DATE($C3,$B3,$A3)

Another advantage of this strategy is that Data Validation can be applied to these cells; ie the day column can be restricted to whole numbers between 1 and 31, the month column to whole numbers between 1 and 12 and the year column as well. In the sample file I’m using, the column holding the complete date (D) is hidden from the user. They will only see columns A thru C. The complete (and correct) date is referenced in formulas.

An alternate strategy would be to use the DATE function to extract the correct order from a whole date typed into a cell. In this case you would need to rely on the users to enter the date consistently regardless of their date system. I would recommend a custom date format be applied and a comment to tell the user what the required date format is. Breaking the date up avoids this reliance on the user’s compliance.

Duotone Photos

I’ve been showing you how to use PowerPoint to quickly create stencil and lace effects. Now, let’s look at creating duotone photos. In addition to making a photo look very modern, duotone is a useful technique for using less than stellar photos.

Cat in the Kitchen

While the cat might be photogenic, the background is not. I want to move from the photo above to the duotone below, which is suitable for adding a quote.

So true

The first step is to crop the picture as closely as possible.

Just the handsome face here – no clutter

But unfortunately, once enlarged you see the photo is a little blurry. This won’t be a problem going forward and it shows how this technique can cope with less than perfect photos.

Going to Picture Corrections:Brightnesswas set to 65%Contrastto 100%

Picture Color: Saturationwas set to zero.

The Adjusted Photo

There is a bit of guesswork here, as I had to bring up Brightness enough to wash out the dark corner of the chair the cat is on, yet leave as much detail as possible. You’ll note that this brings out a lot of light spots on the pupils as well.

Why not just Recolorthe picture to Black and White? In this case, I felt that recoloring removed too much detail from the photo. In the case of a different photo, recoloring might be the quickest and easiest method. I’d definitely try it first and see if I liked the results.

I’ve drawn a rectangle and filled it with a bright colour for contrast, this has been placed under the photo.

Now I can make the white portion of the photo transparent, by selecting Picture Tools>Format>Color>Set Transparent Color and clicking on a white portion of the picture.

The black portion of the photo remains.

What’s also hard to see in the above picture is that the photo has a lot of small grey artifacts in the borders of the fur. This is exactly what we added in when making the lace picture earlier, but here it is unwanted. An additional step is required for this photo (again for some photos it might be unnecessary).

But before I do that – I’m going to use the Ink command and touch up the pupils to remove some of the glints. Ink is only available in Office 365.

Showing Glint repair using ink tool.

After filling in the glints on the pupils, I grouped the ink layer with the photo. Then I copied and pasted the photo (and ink layer) as a picture. PowerPoint remembers all the photo editing done to a picture (which is why the Reset command works) and applies those steps cumulatively. I want to start fresh and apply the Recolor command to strip out the grey artifacts without losing a lot of detail. After recoloring the photo to 25% Black and White I set the White color to transparent

The same photo, but now with a crisper look

Again, I grouped the photo with bright background rectangle, pasted it as a picture and this time set the black portion as transparent. This is similar to the photo stencil.

Photo with transparent cat

In the final step, set a gradient fill in your chosen colour scheme to colour the duotone.

Setting the background of the slide to colour the duotone

The main elements of this technique are applicable to a number of photo effects. Try them out and see what you get!

Duotone photos are the current thing online and you can buy software or use a service to convert your photos into duotone photos. But did you know that you can easily create duotone photos in PowerPoint?

Along the way to this technique, I’ll show you how to make stencils and lace out of your photos as well.

Stencils

Windows 10 default penguin photo

I’m going to use this photo of penguins to make a stencil type image. I’m using Office 365, but this can be done in PowerPoint 2010 as well. I’ve also changed the background colour of my slides. This isn’t necessary, but will make the images easier to understand.

After inserting the photo and changing the background colour, go to Picture Tools>Format>Artistic Effects. You can use either the Photocopy effect or the Cutout effect. The main difference will be the amount of small detail retained by the photo. I like the Cutout option with this photo.

The first step in creating a stencil, applying the Cutout Effect

Reduce the number of shades to 1

The Cutout now has a the number of shades reduced to zero

Select Picture Corrections and adjust Contrastto 100%

The contrast on the picture is now 100%

Select Picture Color and adjust Saturationto 0%

Saturation on the picture is now set to zero, removing the small blue highlights that were visible before.

Even if you are on Office 365, you’ll need to use the Ribbon. The Set Transparent Color command is not on the Picture Color Tab. Select Picture Tools>Format>Color>Set Transparent Color. Click on a black portion of the picture. Voila! A stencil of Penguins that takes on the colour of the slide background.

After the black portions of the picture have been removed, the slide background is visible.

Next, I’ll show a variation on this technique to make a “lace” overlay.

For those of you who haven’t had one of my seminars on using PowerPoint to create powerful image quotes for your social media feed; now’s the time to get out into the garden with your camera phone and take a few photos.

Chrysanthemum

You need to create a stockpile of good background photos that you can use for fresh quotes. And summertime in your garden is a great time and place to do this.

Closeups of plants and flowers make a great background for a variety of quotes – like this one I found on the Olds Municipal Library Facebook feed.

A wonderful quote from Jo Walton.

You can see how they use a transparent overlay over part of the picture to help the text stand out.

You may not have an immediate need for those pictures, but you can set them aside for later use, like this image of purple pink chrysanthemums (my chrysanthemums are looking particularly lovely this year, due to the fact I’ve just bought them).

The colour of these flowers, will do nicely for a different Valentines’ Day image quote.

You don’t need a fancy camera to get these pictures, the camera on your phone will do just fine. But do make sure you take your pictures in both horizontal and vertical orientations to make sure you have more layout options later on.

When you don’t know how you’ll be using those photos, options are very good.

Don’t just focus on flowers (hehe, see what I did there), leaves and foliage are useful too.

Hey! I think I see a face in there!

Don’t forget that the same picture can be used multiple ways, once you start throwing colour filters and special effects at it.

Left is original photo – the right has the saturation cranked up.

Oh, and that image has been flipped, since I like the leaves appearing on the right side of the photo better.

A final tip, when saving your image quotes, use the PNG format, it creates fewer artifacts (small jiggly lines that make text harder to read) than JPEG.

Finally, be sure to create your image quotes in the right dimensions Facebook, Twitter etc. I have have some pre-sized templates that you can use.

The formula checks the position number of the cell generated by the base formula and sees if it is less than or equal to the number of values in each category in column A. It then returns the value of the category in each cell.

Because I wanted to put symbols in the cell like these examples.

Talking Heads waffle chartBombs waffle chart

I took that monster formula and made it into a named formula.

This made building the conditional formatting rules much easier to do(simply because the conditional formatting dialog is so cramped).

Lastly, I built a series of conditional formatting rules to change the background colour of the cell based on the value returned by the formula. For the waffles using symbols, the rule formats the colour of the font, instead of the background.

A couple of additional pointers

To create a perfect grid, switch the view in Excel to Page Layout View. Page Layout View uses the same measurement scale for both row height and column width. Set your measurements here.

For the symbol waffles, use the File> Options>Advanced> Display Options for this worksheetand turn off the display of gridlines. That way when you copy the waffle, the gridlines will be invisible.

I’m often asked my opinion about Prezi as an alternative to PowerPoint. My answer – meh.

I can’t even get worked up enough about it to dislike it. Except now, after having seen a number of Prezi presentations I do have a Pavlovian nausea response to Prezi’s default swooping motion animations.

From my point of view; watching a naive user (my son) using Prezi for the first time really exposes all its’ faults. The intense focus on the visual. There is apparently no way of storyboarding a presentation in a text only view. This makes presentations that tend to not have logical flow. It is also difficult repurpose content from Prezi. At least without moving to the paid subscription model.

And while Prezi does have attractive templates, it squanders its’ most obvious asset – motion transitions; by not guiding users on how to use them effectively. Its’ other obvious positive features, shared collaboration and its online workspace/storage are also available in other products like Google Slides.

I might look to Prezi for visual inspiration, but if I had real work to do I’d chose a different presentation package.