Upcoming Course Trailer

I’ll be teaching the Formulas and Functions course at Medicine Hat College for Continuing Studies on Friday, January 28, 2022.

Absolute and Relative References are just one of the topics we’ll cover that day.

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

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

Excel: Adding time

Hands on a laptop keyboard
Adding Time
Automatically adding 30 minutes to the Start Time

In my Social Media spreadsheet I want to add 30 minutes to the starting time for the post. Why 30 minutes? That’s the default scheduling time in Google Calendar.

To do this, I use the TIME function. The TIME function has 3 arguments; hour, minute and seconds – all three arguments are required. So my formula would look something like this:

=F2+TIME(0,30,0)

So, I’m adding 30 minutes to the value from cell F2. Easy!


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

A Quick Export View from Outlook

I’m currently advertising for an Assistant TechGenie, so unsurprisingly there are a ton of email responses hitting my email box (what is surprising; how few are from Canadian Citizens).

After I’ve sorted out the responses into a folder of likely candidates, I want to create a list of names and email addresses that I can refer to as I move through the process. The question – is there a quick way to do this from Outlook?

The answer is yes, once you build yourself a custom view. If you haven’t played with custom views in Outlook, you really should take a moment to appreciate the simple way they can add productivity to your email tasks. Today, I’m at a machine using Outlook 2016, so you might find older versions of Outlook a little different.

Create a Table View

Outlook supports numerous types of views but for this task, I’m using Table View as I can then copy and paste the information directly into a spreadsheet.

  • go the the View ribbon
  • Click the Change View button
  • Select Manage Views, the Manage All Views dialog box will appear
Outlook Manage All Views Dialog Box
Manage All Views
  • Click on the New button, and the Create a New View dialog box will appear
Outlook Create a New View dialog
Create a New Outlook View
  • Name your view with a meaningful name, since you will want to reuse it. Make sure Table view is selected, and make it available to all your folders. Click OK.  If you are following along, at this point you will notice that I have already removed the columns I don’t want.
Select only the columns you want in your final spreadsheet
Remove all the columns except the ones you plan on using

But there is one column I do want – the sender’s email address. And you will not find it in the lists of available columns. Instead, try this trick I picked up from espacecode.com

Add a Custom Column

 

  • Use the New Column button, the New Column dialog will appear.
  • Name the column (the name can not be “email”).
  • From the Type drop down list, choose Formula.
  • Paste the following formula into the Formula field:
     IIf(InStr([SearchFromEmail], "@") = 0, "", Right([SearchFromEmail],len([SearchFromEmail])))
  • Reorder the columns in as desired.
  • Click on the OK button to complete this step and the Advanced View Settings: Your View Name will appear
Outlook Advanced View Settings dialog
The Advanced View Settings Dialog

Fine tune your View

  • Click the Other Settings button and the Other Settings dialog will appear
Outlook - Other Settings dialog
The Other Settings dialog box
  • Make sure that the Reading Pane is turned off as well.
  • Click on the OK button twice to return to the folder view. If you have Message Previews turned on for this folder, turn them off. The result should look as follows:
Outlook-SpamViewResults
Here are the email addresses of spammers exposed.

Using Your Custom View

You can now copy this information directly into a spreadsheet and easily make a list of names and email addresses. And since this is a named view, when the task is done and I want to return to my preferred email view, I can do so. But I can reapply the view at any time.


Are you looking for methods to handle your email overload? Drop ME an email catharine@mytechgenie.ca and we can build some simple tools (like this one) to help you manage your email more effectively.

Get More From PowerPoint with the Medicine Hat Chamber of Commerce

Join me in this webinar, hosted by the Medicine Hat Chamber of Commerce

There’s been a lot of talk lately about “Zoom Fatigue”. The trailer above is one way to combat fatigue. Creating trailers for your presentations allow you to shorten the presentation by introducing information ahead of time. Like a movie trailer; your talk trailer tells your audience what to expect and allows you to cut your talk time down.

And why limit yourself to a single trailer? For longer and more complex materials, you might want to create multiple trailers that can prepare your audience properly for your talk.

Creating your trailer in PowerPoint allows you to easily reuse elements in future trailers. This saves time and strengthens your brand presence.  You can bet I’ll be reusing the little animated stars on this slide that act as an attention getter for keywords.

Showing elements to be reused.
These little purple star animations pull the eye to key words

Like the idea of saving time? Drop me a line, and lets’ make something fantastic for your next presentation. A reusable something fantastic!

Animated GIFs and Video for Social Media Using PowerPoint

Exported PowerPoint Image

The latest version of PowerPoint allows you to export your presentation as an animated GIF. Animated GIFs are great for catching the eye on social media.

Redcliff Library Board Member Promotion - as an animated GIF
Redcliff Library Board Member Promotion – as an animated GIF

There are of course, lots of animated GIF sofware packages available, many are free. But none are as useful as PowerPoint when it comes to incorporating imagery that you already have on hand. Remember to keep the size of the file down, as Twitter limits animated GIF size to 5MB.

If you want a little more room or sound, remember that you can export your PowerPoint presentation as a video in mp4 format.

You notice some differences between the video and GIF versions of this little social media piece. This is to optimize file size for the animated GIF.

The beauty of creating this in PowerPoint is that it is easily accessible for updating by the client.


Like what you see? Drop me a line, and lets’ make something fantastic for your next social media promotion. A reusable something fantastic!


Music:

Path Of The Fireflies by AERØHEAD | https://soundcloud.com/aerohead

Music promoted by https://www.free-stock-music.com

Creative Commons Attribution-ShareAlike 3.0 Unported

https://creativecommons.org/licenses/by-sa/3.0/deed.en_US

A Summary Sheet In Excel

Why?

A summary sheet can be used to quickly view key details from multiple sheets in a large workbook. A correctly formatted summary sheet can be used as the basis for a mail merge in Word.

How

This morning’s client help call came from a client using Excel to track employee performance reviews. Once the reviews are completed, they will use Word’s Mail Merge feature to generate individual checklists for each employee.

This process starts with a well-designed Excel workbook.

A view of the Summary Sheet
This summary sheet can be used for many purposes, including as the data file for a mail merge.

In the example above, there is a summary sheet which pulls the critical information from each individual employee’s sheet. Regardless of the scenario, a summary sheet is frequently used to present critical information at a glance.

Breaking out the individual sheets in the workbook
Each employee sheet has a similar layout.

Copying and pasting information or creating individually linked formulas is tedious. Instead; try creating a named formula.

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

The GET.WORKBOOK function is a leftover from the old Excel 4 macro language. This is why the file needs to be saved as a macro enabled file. Even though there is no VBA in it. Finding documentation on GET.WORKBOOK is hard, since Microsoft doesn’t officially support it anymore. But a kind soul has posted here https://answers.microsoft.com/en-us/mac/forum/macoffice2011-macexcel/getworkbook-help-in-macro-language-in-excel-2011/a359708d-b5b7-48ba-8d1e-c4114d4642d2

The GET.WORKBOOK functions cannot be entered directly in a cell, instead you must place them in a named formula. Here is what it looks like, using the Name Manager dialog box. The named formula can be called anything. Here I’ve used ListSheetsFunction as the name.

Adding a Named formula through the Name box
Using GET.WORKBOOK will mean your file will need to be saved as a Macro-Enabled workbook.

Once the named formula has been created, it can be used in conjunction with the INDEX function in the following formula:

=INDEX(ListSheetsFunction,ROW())
Demonstrating how the named formula captures the sheet name
After creating the formula in cell A2, copy it down column A for as many rows as there are sheets in the workbook.

You can see how the formula captures each sheet name as it is copied down the column. Note (this formula doesn’t always recalculate automatically, you may need to close/reopen your workbook or recopy the formula to force recalculation after editing sheet names).

Now that the list of sheet names is in the first column, you can use that to build the formulas that will give you the summary of each sheet in the workbook.

=INDIRECT(ADDRESS(2,COLUMN()-1,1,1,$A2),TRUE)
Adding the INDIRECT/ADDRESS formula to link to the key cells in the sheets
Depending on the layout of the detail sheets, you can copy one formula to the rest of the summary sheet.

In this case I copied the formula into cells B2:E4. Now as the fields are updated in each individual sheet, the summary will update. If new sheets are added, the formulas from the previous row can be copied down.


There are hundreds of formulas available in Excel; the trick is to cut through all the options and find the one that does the job you need done.

If you are interested in becoming a My TechGenie client, give me a call at 403-581-1275 or send an email to catharine@mytechgenie.ca. Practical, everyday technology is our specialty!