Here is a scenario: A running total of numbers, updated daily. You want to capture the previous day’s total, as you can see in the picture below. 
I’m showing the answer in two steps here, in real life I’d make it into one formula.
The first step is to capture the row number of the previous day’s total. Finding it using the numbers in Column C would be way too complicated. But Column B has the kind of data we can use.
Using the formula =LOOKUP(2,1/(B2:B29<>0),ROW(B:B)) captures the row.
What the lookup formula is doing is starting by evaluating the numbers from B2:B29 looking for values that aren’t equal to zero.
This creates an array like this: {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
The TRUE values will equal 1 and the FALSE values equal 0.
This means when the formula divides 1 by those values, an array looking like this is created:
{1;1;1;1;1;1;1;1;1;1;1;1;1;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}
Lookup can’t find 2 in the array, so it settles for the largest value in the array that is less than or equal to lookup_value.The ROW function tells it to return the row number from the array B2:B29.
The second step is to combine the row number with the column number and show the result using =INDIRECT("C"&D2)
And there you have it. A quick way of always finding the previous day (month, year, whatever).
Three Presentation Mistakes
Last weekend I attended the Southern Alberta Library Conference. I really enjoy this conference, the speakers are great and the topics really relevant to my volunteer work with the Redcliff Public Library. So what does this have to do with presentation mistakes? It was interesting to see the kind of presentation mistakes that speakers who are good at presenting make.
Mistake Number 1
Our old friend – too much text on the slide. Even good speakers do this, even though they shouldn’t. I suspect because they worry about leaving something out of their presentation. 
Once again, I’d like to join my voice to all the presentation experts telling you NOT to put all your text on the slide. But, I know it will happen anyway, so what can we do to improve a slide like this?
Remove Bullets
If you are going to write full sentences with punctuation, then bullets are completely unnecessary. They take the viewer’s eye away from the content of the sentence. Save bullet points for sentence fragments, which is what they are designed for.
One Sentence Per Slide
Help the audience focus its’ attention by restricting yourself to one sentence per slide at a time.
Position Sentence Text
There is no rule in PowerPoint (or any presentation software) that requires you to use the default text wrapping. Add line breaks to force text to wrap for greater readability and easier recall. Notice how the ham jumps out from the rest of the text, when it is forced onto its’ own line. Think about the part of the sentence you wish to emphasize and add line breaks accordingly. Also, if the sentence is on its’ own slide, there will be room to do this.
Mistake Number 2
Smart Art can cause problems of its’ own. In particular, the seductive way it shrinks text to fit into the graphic makes people forget to edit. (See mistake number 1)
Also, the default colour schemes means a lovely rainbow of colours. How is this a bad thing you ask? Well, inevitably you get a colour combination like point three in the graphic above. White text on a yellow background. That’s readable on a computer monitor, but when projected onto a screen it doesn’t have enough contrast.
The rainbow effect above, does something else as well. It wastes the potential usefulness of those colours. Colour is a great way of adding organization and hierarchy to a presentation. In the slide above, perhaps green refers to free-range meat, blue to fish, yellow to poultry, red to spicy foods, and I have no idea what pink would refer to. Because there is no organization being used here, just the random default applied by Smart Art.
Ignoring the organizational impact of colour, is like leaving money on the table.
Mistake Number 3
This last mistake is a little bit of mistake 1 AND mistake 2 combined, and it comes from using Smart Art process graphics like the one below:
Hmmm, yes it IS hard to read, but I can understand the desire to help people understand the flow of a process. So why not introduce your process in a series of slides like this:
In this sample slide I’ve taken the process and reduced to a smaller graphic in the top left corner. Here it will act as a map to show people where we are. I’ve toned down the colours of the steps that are not being talked about on this slide. I’ve left the bright blue alone, because we are talking about the blue step on this slide. I’ve cut out the blue step and enlarged it, so the text will be easier to read. It is easy to imagine each step in turn being featured on a separate slide and highlighted on the map.
Once again, thanks to everyone who spoke at the Southern Alberta Library Conference. I learn a lot about how to be a better library board member every time I attend. And, if you are a resident of Alberta; consider volunteering in your local library. It really is the best volunteer gig around. Such a positive environment that really makes a difference in the community!
I offer presentation design services and coaching. Feel free to send me an email.
iTunes Won’t Stop Asking For Your Password
Let’s start by agreeing that we all hate iTunes. Feel better now? Nope. Me neither.
However, I seem to have found a reliable solution for when iTunes repeatedly asks for your password. A hat tip to ytene over on the Apple forums for this.
The simple solution:
- Sign in
- Go to the Account menu and Sign Out again
- Close iTunes
- Restart iTunes and login when requested
Hopefully, this will fix the problem.
New Windows 10 Feature
Have you ever wanted to magnify just part of your windows screen? I do this frequently and for years relied on a 3rd party app. Unfortunately, it didn’t make the transition to Windows 10. But its’ all good now, because Windows now has a builtin magnification utility.
You’ll find the Magnifier settings in Settings, Ease of Access, Magnifier. I like using the Lens as the default. You can change the size of the lens here too. Once you are set up, you can easily turn the magnifier on/off with the following shortcuts:
Windows Key ⊞ + Plus sign to turn on
Windows Key ⊞ + Esc key to turn off
Shortcut Keys Galore
I often get asked for lists of shortcut keys for various products. I’ve started keeping a little spreadsheet with shortcut keys.
There is some variation in shortcut keys between versions of Office and the Mac/Windows world. These have been tested in Office 2016 on Windows 10.
Nail Down That Date!
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)
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 Photo Technique
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.
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.
The first step is to crop the picture as closely as possible.
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:
Brightness was set to 65%
Contrast to 100%
Picture Color: Saturation was set to zero.
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 Recolor the 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.
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.
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
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.
In the final step, set a gradient fill in your chosen colour scheme 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!
Making Lace Photos
Lace
In the last post, I showed how to turn a photo into a stencil. This time, we’ll be adding a lace effect to this photo.
Using this photo:
After inserting the photo and changing the background colour, go to Picture Tools>Format>Picture Corrections. Set Sharpness to 100%. The picture may look worse at this point, but don’t worry.
Now, set Contrast to 100%. Yep, it definitely looks worse.
Select Picture Color and adjust Saturation to 0%
Things are looking up again. Select Picture Tools>Format>Color>Set Transparent Color. Click on a black portion of the picture.
Now is the time for a little cropping and copying that will make the lace effect stand out.
And finally, layer the lace over the Wedding Kiss photo for the result.
Next week, I show how a variation of these techniques creates a duotone photo.
Photo Stencils
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
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.
Reduce the number of shades to 1
Select Picture Corrections and adjust Contrast to 100%
Select Picture Color and adjust Saturation to 0%
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.
Next, I’ll show a variation on this technique to make a “lace” overlay.
Making Powerful Image Quotes
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.
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.
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).
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.
Don’t just focus on flowers (hehe, see what I did there), leaves and foliage are useful too.
Don’t forget that the same picture can be used multiple ways, once you start throwing colour filters and special effects at it.
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.