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: Concatenation for the Social Nation

Girl with laptop and manic grin
Concatenation results
Excel Concatenation formula results

As I’ve worked more with scheduling posts, tweets and pins, I’m trying to make the most of the Subject line used by Google Calendar.

Google Calendar Subject line
Google Calendar Subject line

I’ve found that if I combine a meaningful keyword describing the post(or tweet, or pin) plus the phrase that triggers the IFTTT action, then managing the scheduled posts once they are uploaded into Google Calendar is a bit easier. It also makes it easier when I’m filtering and managing the spreadsheet too.

In my spreadsheet I use a separate column each for subject keyword and for subject trigger phrases (actually I’m paring those down to keywords too). But I want them joined together to create the actual subjects. To do this, I use the Excel CONCATENATION function. Which is most simply represented by the & symbol. In the example at the beginning of the post you can see the formula:

=B103& ” ” &C103

In this case I’m using the & symbol to join the values of cells B103 and C103 together with the string ” ” in the middle to create a nice space between words. This allows the subject phrase to be created automatically once I’ve selected the subject and trigger keywords.


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