Excel: Validate!

DataValidationdropdown

When building my Social Media spreadsheet, I want to enter my subject keywords and trigger keywords consistently. Minor typos can make it difficult to find all the relevant posts and worse; prevent scheduled posts, tweets and pins from being published on time. This is why I find the Data Validation feature in Excel so useful. As you can see in the picture above, once Data Validation is in action, my data entry is restricted to a preset list of options.

Data Validation Ribbon
Find the Data Validation tool on the Data ribbon

Since Excel 2007, the Data Validation tool has been on the Data Ribbon. Simply select the cells you want to apply Data Validation to and press the Data Validation button and select Data Validation. Then the Data Validation Settings dialogue box will appear.

Data Validation Settings
Data Validation Settings

To keep the active sheet “clean”, I use a named range on another sheet as my data source (I’ve talked about that previously). Here you can see it’s called PostTypes. But you can enter short lists directly into the Source box:

Data Validation Settings
Data Validation Settings

However, I find in the long run (especially for long lists) keeping the list source on another sheet makes maintenance easier.


This post is originally from 2016. If you want help with the newest and classic features in Excel drop me a line at catharine@mytechgenie.ca

Excel: Looking Good in Facebook

Chalkboard with hands giving thumbs up gesture and the phrase We Like It! in background
Controlling the layout of a wordy Facebook post
Controlling the layout of a wordy Facebook post

You can see the above is a pretty long Facebook post. To force line breaks when posting to Facebook use the html tag: <BR>

To force line returns inside an Excel cell use the Alt+Enter shortcut. This way your Excel layout looks like your Facebook post.


This post is originally from 2016. If you want help with the newest and classic features in Excel drop me a line at catharine@mytechgenie.ca

Flash Fill

Flash Fill was introduced in 2013, but I have clients who are just upgrading to 2013 this year. This is the kind of feature that makes upgrading worthwhile.


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.


I was (and still am) excited to share Flash Fill with my favourite clients. If you are interested in becoming one of my clients, drop me a line at catharine@mytechgenie.ca

Excel: Multiple Cell entry

Here’s a quick keyboard tip, instead of copying and pasting the same information in multiple cells (too many steps!), try the following.

  1. Pre selecting the cells you want to enter data in.
  2. Enter the data – BUT instead use the Ctrl + Enter keyboard shortcut to confirm the data. This will duplicate your data entry to every cell you have selected.

The toughest part of this shortcut is remembering to use it!