Flash Fill

Flash Fill is a new tool introduced in Excel 2013. Its a simple tool to handle a frequent problem. You have a small set of data and you need to break it apart into separate columns or join separate columns of data together.

Previously, I would have handled it with a series of text functions in Excel (LEFT, RIGHT, MID) combined with FIND and LEN if the data was complex enough. But if the data set is small, writing a formula sometimes seems like an overly complicated answer to a simple problem (why not just retype?).

Now Flash Fill is stepping in to help you handle this problem. If you give it a series of data (column orientation only) and an example of the pattern you want to extract, it will extract the data for you.

Flash Fill in action
Flash Fill captures the first names only from the adjacent column

You can see once the second name is typed in the column adjacent to the list of full names, Flash Fill is able to see the pattern and offer all the first names in the list. Pressing enter autocompletes the action and the names are filled in. To do this, there can not be more than two blank columns between the source data and the resulting column. You can use the Ctrl + e shortcut to start flash fill.

Flash Fill Icon
Note the Flash Fill Icon displaying after the Ctrl + e shortcut was used.

You can click the Flash Fill icon to display the menu, accepting the suggestions will have all the names autocomplete.

The Flash Fill menu will display if the icon is clicked
The Flash Fill Menu

Here is an even trickier scenario, in the list above some names have two middle initials. Using the “default” flash fill means only the second initial will display in those names. However if I return to any of the names on the list (with two initials) and correct the example to two initials, all of the two initials examples will be extracted.

Two initials with Flash fill
Flash Fill double initial example

I find that seriously impressive.

I can split data in a cell into multiple columns and I can also use Flash Fill to join multiple columns of data together.

Flash Fill concatenation
Joining separate columns using Flash Fill

The same technique used above. Note that I’ve been able to add commas and periods to the text as well.

Formatting via Flash Fill
Using Flash Fill to apply formatting

In the same way, I can use Flash Fill to apply formatting, in this case putting a space between the first and second part of a postal code. You can also use it to format telephone numbers and date information.

PowerPoint – Organizing your colours

I find it useful when creating a presentation that has a custom colour palette to create a custom layout like the one below:

Slide Master containing information about the Colour Palette used in presentation
Colour Palette Assignment Slide Layout – accessible via Master view, or by selecting the layout

You’ll note that the RGB values for the colours are listed, and this is because prior to PowerPoint 2013, the eyedropper tool was not available. I also find it tremendously helpful to note what I use each colour for, so that when I open this file in a couple of years from now there will be a little less detective work.

My Tech Travel Kit

I was listening to the “Gadgets” section of the 20 Minute Delay podcast and it inspired me to share my tech travel kit.

First up is the bag I carry it all in, the Maxpedition Fatty Pocket Organizer

Photo of Maxpedition Fatty Pocket Organizer
Maxpedition Fatty Pocket Organizer

Other sizes are available, but this works for me. It allows me to organize the myriad pieces of cords and kit that are a part of modern life.  Here is an interior view –

Inside the Maxpedition Fatty Organizer
Maxpedition Fatty – Interior view

Next up is a Powerseed portable charger that will charge phone and tablet. One of the nice features of this model is the light. In a pinch it acts as a flashlight, and it certainly has saved me from fumbling around in an overly darkened hotel room a time or two.

Powerseed Charger photo
Powerseed Charger

This model doesn’t appear to be available anymore, but I like being able to charge multiple devices and as I said the light has been unexpectedly helpful.

Powerseed - view of ports and light photo
Powerseed – view of ports and light

Next is a nifty Bluetooth wireless speaker. The Anker SoundCore nano.  Having a speaker on hand allows me to leave the TV off, and listen to my music of choice.

Photo - Anker SoundCore Nano
Anker SoundCore Nano

This has an amazing sound to size ratio. Its volume will fill a hotel room and is very light.
Also from Anker is this 4 port USB charger.

Photo Anker Charging Block
Anker Charging Block

It saves time and weight and made a noticeable difference when I started packing only this instead of all the different phone/tablet charging blocks. The newer version has foldaway prongs.

Lastly is this small extension cord.

Photo Travel extension cord
Travel extension cord

I don’t know how many hotel rooms I’ve been in with inadequate or inaccessible wall plugs. This one came from our local Princess Auto and in addition to having yet more USB ports, I like how it’s designed to plug into itself. This makes it much easier to pack, since it’s the one item that doesn’t fit into the tech bag. BTW,  Princess Auto is often a great source for discount tech type gadgets.

 

Pictures and Transparency

In my last post, I mentioned I was working on a Jeopardy game in PowerPoint. In this game I want to present a series of visual clues before the answer is revealed. The audience is presented with the foreign cover for a popular book and has to guess the name of the book.

Book cover transition from greek to english cover version
Can you guess the book, by seeing its foreign (Greek) version cover?

I want to slowly reveal the English book cover, by gradually making the foreign cover more transparent. With this particular cover, I also wanted to crop the foreign cover image to reveal additional clues. Each clue will be revealed by a click of the mouse.

Hmm is this a problem? I can not control image transparency in PowerPoint, there is no option for this in the Picture Tools menu.

Nope, no problem at all. You can control image transparency by:

  1. Create a shape the same dimensions as your picture.
  2. Remove the outline for the shape.
  3. Change the fill option to Picture or Texture Fill and insert the picture file.
  4. Transparency will now be available

Its’ interesting that placing a picture inside a shape allows you to manipulate that picture as if it was a shape. This concept allows me to play with things like irregularly shaped (non-rectangular) images as well.

 

Thursday – PowerPoint Links

I’ve just been working on a PowerPoint template for a Jeopardy style game. I inherited this template, and as frequently happens a little cleanup is necessary to ensure the PowerPoint template works as desired.

To help you visualize the problem – a picture of the game board

Jeopardy Game Board 1st slide
The Game Board

Each square hyperlinks to a separate slide with the question (and answer).

I felt there were a number of improvements I could do to make the presentation easier to use and maintain. I won’t go into every change today, but a couple of changes involved hyperlinks
(shortcut key Ctrl + K, if you are editing 25 hyperlinks, then the reason for using a shortcut key becomes obvious).

The first maintenance problem I ran into was that the previous designer had applied the hyperlink to both the shape AND the text on the shape (now there are 50 hyperlinks – if you are counting).

Button shape with text selected
Shape with text on top

They did this for a very good reason; that the text on a hyperlinked shape does not change state like normal hyperlink does (the state change shows if the link has been visited or not).

So if the slides the shapes are linked to are reordered or edited, the links have to be painstakingly tracked down and edited and since essentially the links are layered one on top of each other it is a real pain.

I had a better plan. Move the button shapes to the Slide Master (after creating a layout designed for the Game Board slide). Then insert text placeholders (yes, 25 of them) for the dollar values. Position the placeholders over each button. No hyperlinks here.

Now moving back to the Game Board slide in Normal View, I can hyperlink the text box. Text boxes behave differently from shapes, and do change state to show the link has been visited.

Another advantage of the text placeholder is that if the user inadvertently moves the text boxes, the Reset command will snap them back into position. (A definitely plus when editing 25 text boxes).

The other visual difficulty I had, was with the colours of the hyperlinks themselves. They didn’t have a strong contrast with my (new) button colour, and the visited colour was still (kinda) visible. I wanted a strong link colour and once visited I wanted the link to disappear. I could add animations, but why bother when I could solve both problems easily by changing the link colours in the Color Theme.

Theme Colour Panel PowerPoint 2016
Theme Colour Panel PowerPoint 2016

Here is the theme colour panel after I adjusted the Hyperlink and Followed Hyperlink Colours.

The colours in the theme were picked after playing with the free https://coolors.co/ app I also got some good advice from this article. The image at the top of the article is the colour palette created by the Coolors.co app – translated into RGB. I usually add this information as a layout in the slide master.

 

Social Media tips – free photo resources (2018)

Its’ time for an update on free photo resources for your social media work. I like to have them all together in one place since this is a list I use myself 😉

These sites are often offering more than photos, including clip art or vector images. Many fund themselves via premium or paid options, so search carefully to ensure that the picture you love is free.

  • Library and Archives Canada –Image Search
  • New York Public Library – Search Page
  • The Rijkmuseum in Amerstdam has digitized its collection. All of its works are free to use. Its’ policy “If you use our images for publication, then we request that you acknowledge the source (Rijksmuseum, Amsterdam). We would also like to receive a copy of the publication for our library.”
  • Try the Creative Commons search tool.
  • One of my favourites, Pexels.
  • University of Alberta Peel Image search.
  • Good Free Photos offers Public Domain Photos, Clipart, images, and Vectors
  • I’ve mentioned Unsplash before.
  • Not a photo resource site, but very useful is The Noun Project a great site when looking for a graphic to illustrate a concept or idea. You can pay OR give credit to the artist.
  • Rawpixel has a search specifically for public domain images.
  • Gratisography is the work of photographer Ryan McGuire. You’ll note his singular style at work.
  • IM Free  offers photos, icons and more.
  • Any internet search will probably turn up photos from Pixabay, so its probably quicker to go there directly.
  • Shopify runs a free graphics site called Burst.
  • Picjumbo offers free and a paid subscription model.
  • All photos found in the Morguefile archive are free for you to download and re-use in your work, be it commercial or not.
  • Stock Vault offers free stock photos and the opportunity to purchase via their Premium option.
  • Negative space
  • Kaboom pics claim to fame, is that the colour palette of each photo is extracted for you, useful if you are planning coordinating backgrounds or print materials.
  • Fancy Crave  All photos published on Fancycrave are licensed under the Creative Commons Zero (CCO) license which grants you an irrevocable, nonexclusive copyright license to download, copy, modify, distribute, perform, and use photos from Fancycrave for free, including for commercial purposes, without permission from or attributing the photographer or Fancycrave.
  • Stock Snap
  • Startup Stock Photos
  • Splitshire
  • Life of Pix
  • The New Old Stock focused on older pictures curated from  institutions participating in the Flickr Commons.

Don’t get fooled by Fake News! A useful tip

I really didn’t want to put Fake News in the headline of this post, but really that is what this post is all about. How to use a reverse image search when you are on a mobile device, so that you can spot a Fake News story.

Recently a friend shared a Facebook story that just didn’t add up. The photo was of a man in hazmat style suit purportedly cleaning up an oil spill in North Dakota. You can see a version of the photo at the top of the article. The story implied some type of conspiracy that was preventing news of the oil spill from being covered.

A Reverse Image Search told a different story

This didn’t make sense to me, especially as I had been following news of an oil spill that was not on the scale shown by the photo. However a few minutes with a reverse image search showed the picture had been taken in China, and had nothing to do with North Dakota. This made the rest of the story even more unlikely.

Performing your search

A reverse image search is easy to do on a mobile device although iOS users need to download the Chrome browser app first.  Sorry, but Safari doesn’t support Googles image search file upload.

With that in mind here are the steps to performing your reverse image search:

  1. Save a copy of the photo locally, this might mean taking a screenshot depending on the posts’ privacy settings. Here you can see the photo I used. Note that the poster changed the photo to Black and White in order to make it harder to search. Didn’t work though!

    Altered version of original oil spill clean up photo
    Black and White photo of oil cleanup effort – altered from original
  2. Open Chrome and go to www.google.ca

    Either google.ca or google.com will work
    Either google.ca or google.com will work
  3. Click the Images link on the page to go to Image search. Currently that link is in the top right corner of the screen.

    Google Image Search, showing Camera
    You are in Image Search, when the camera icon is in the search bar.
  4. Click the Camera icon to see your options for searching using an image file.
    Google image search options
    Google Image search options. Search by pasting the URL of the image or by uploading the file.

    Choose the Upload an Image tab.

  5. Uploading an Image
    Uploading an Image

    Click the Choose File button.

  6. You will be offered a choice of taking a new photo or going to your Photo Library. Select your photo library, and locate the image you saved previously.
  7. The file is uploaded automatically and the search is performed.
    Reverse Image Search Results
     Reverse Image Search Results initial screen

    Make sure you scroll down the page looking for the Pages that include matching images.

    finding pages that include matching images
    Reverse Image Search – Pages that include matching images.

    Reverse Image searches are amazing

Frankly, I was astounded that the image was located even after it had been changed. Reverse image searches are a powerful tool! Whether from malicious intent or the desire to give a story emotional impact, there are all too many posts on the net where images are misused. Don’t be fooled.

WebGenii Supports Redcliff Youth Soccer

Looks like Christmas has come early this year! Here’s a shot of the bags that WebGenii Consulting will be donating to the Redcliff Youth Soccer Association this year.

Pretty fancy, the zippered front pouch is sized to hold a soccer ball.

drawstring soccer bag
Backpack for youth playing soccer in Redcliff

Let me challenge other Redcliff business to step up and support their community organizations!

A little bit of history – lost … and found

Excel has been around for decades, so it isn’t surprising that there are many features tucked away under the hood. What is surprising is when a useful feature is lost and only careful archeology can bring it back to life.

Excel 4.0 had a rudimentary macro language, mostly using excel formula approaches to building functionality. This was replaced by the VBA programming language. But there are still useful little items tucked away in this early language that haven’t been replaced.

One of these is Get.Cell.

Get.Cell had a boatload of switches that allowed the user to pull information about the cell formatting and contents and most of these have been replaced by the Cell and Type functions in Excel.

But one piece of information that Cell and Type can’t tell you is whether your cell or cells contain formulas vs values and sometimes this is a very handy thing to know at a glance. For example, if you build a spreadsheet using formulas to estimate amounts; but then start to drop in values as more concrete information becomes available.

In this situation I like to format cells containing formulas differently from the cells containing values, so that I can see at a glance where my estimates are. Its’ handy to have the formatting change automatically, so I don’t have to remember what my rules are weeks or months later.

This is where Get.Cell shines. The syntax I’m going to use is

=GET.CELL(48,A1) – where A1 is the cell I’m going to reference.

The trick here is that Get.Cell is NOT entered in a cell, but instead as a named formula. After creating the named formula, I can reference it while applying conditional formatting. In this way, when the type of content in the cell changes the conditional formatting automatically updates.

Using a named formula in Conditional Formatting - dialog box
Using a named formula in Conditional Formatting

Where does one find information about the Get.Cell function? Not from Microsoft or at least not easily from Microsoft.

Try this post https://www.mrexcel.com/forum/excel-questions/20611-info-only-get-cell-arguments.html   to see the possible switches for Get.Cell

 

 

PowerPoint 2016 – a change in Guides

I noticed this improvement to guides in PowerPoint 2016 the other day.

If you apply your guides while in Master View, you can’t inadvertently move them while in Normal View. This is great! And consistent with the way objects behave between normal and master views.

By the way – guides applied in Normal View, can still be moved around in normal view. Like I said – consistent!