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!

Making a Zoom Background in PowerPoint

You can make a professional looking Zoom background quickly in PowerPoint. Just be sure to use a 16 x 9 dimension slide.

Oh, and flip your logo or any text on the slide. Yes, it will be mirrored. But when uploaded to Zoom as a virtual background, it will read properly for your audience.

Where to go to reverse images
Don’t forget to reverse images

After creating your slide, save it as a jpeg and then upload it to Zoom.


Looking for help with getting the most from your presentations?
Contact me 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!

Excel: Find the Weekend

In a previous post I showed how I entered a column of repeating dates when building my Social Media spreadsheet. The next thing I like to do, is colour code those dates so that I can see at a glance when the weekend dates are. For this I use the WEEKDAY function in Excel.

Weekday Function Example

Point the WEEKDAY function at a date and it will return a number from 1 thru 7 indicating what day of the week the date is. In this case the formula reads =weekday(A2,2)

The 2 in the above formula is the return type, and here indicates that the week starts on Monday. This means that Saturday and Sunday will return values of 6 & 7.

This is perfect for using with conditional formatting.

If I plug the following formula into the conditional formatting dialog box
=(WEEKDAY(A2,2))>5

I am testing for values above 5, namely the weekend. So I can use this to put a colour fill in those dates so that they stand out.

Weekday function with Conditional Formatting

Obviously, the Results column isn’t needed because the formula is actually residing in the Edit Formatting Rule dialog box.

This is the second post discussing using Conditional formatting with a Social Media spreadsheet. Check out this previous post for another example of using conditional formatting.


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: Sequential Dates in Multiples

The Fill Series Dialog

When I’m setting up my Social Media spreadsheet in Excel, I like to limit the number of scheduled Facebook entries per day. Over time, I’ve come to think that 4 Facebook entries per day is a reasonable maximum. This lets the librarian post “live” when things are happening in the library without clogging up our follower’s feeds.

So I want to create a column of dates that looks like this:

Each date is repeated 4 times
Each date is repeated 4 times

The quickest way to do this with minimal typing is to use the Fill Series dialog box. Since Excel 2007, you can find it under the Fill menu on the  Home tab.

Finding the Fill Series Dialog
Finding the Fill Series Dialog

To use the Fill Series dialog, select the range of cells you want your dates to be entered in. Make sure the first cell in the range has the starting date. Then select the  Fill button and choose Series .

The Fill Series Dialog
The Fill Series Dialog

Enter a  Step value. In this case, because I want 4 repeats of each date I’m using .25 as the Step value. If I wanted 5 repeats, I’d use .20 (and so on).

If you don’t feel like calculating how many cells to select when doing this for a date range that spans a couple of months; try using a  Stop value . With a  Stop Value, the series will stop at the first instance of the date entered into the field. Otherwise, the series will fill the entire selected range. ( In the picture above the full date is not displayed in the field, it was actually 06/01/2016.) Using a <em><strong>Stop Value</strong> </em>allows you to make a rough selection (say 500 cells) and Excel will stop when the series runs its’ course.


This post is originally from 2016, however Filling a series is still as useful in 2020 as it was then.

If you want help with the newest and classic features in Excel drop me a line at catharine@mytechgenie.ca

Home On The Range

Sometimes, when you’re teaching, its not about the complexity of the subject. Sometimes its a very simple piece of information that students get the most “mileage” out of.

When I’m teaching students MS Excel, the simplest thing that I teach them about is Named Ranges. Its the simplest thing to talk about, but the uses for ranges go on and on.

Excel Spreadsheet Example
A plain spreadsheet.

Above you see a standard Excel spreadsheet. Adding a range name or two (or ten) can help make it much easier to work with.

Adding a Range name to the Cell Address box.
Type the range name into the Cell Address box. Press Enter when done.

A range name can refer to a single cell or a group of cells, here I’ve selected the cell containing the total for the six month period (H11).

Click into the Cell address box (circled in red) and type in the desired name. There are some simple rules about naming ranges; the name can’t start with a number, can’t look like a cell reference (imagine how confusing that would be) and can’t use spaces and special characters (notice I’ve used an underscore to separate words). But after that it is up to you, to make your range name meaningful.

Adding a range name to a group of cells
Adding a range name to a group of cells.

If you are going to add a range name to a group of cells, select them and type the name into the cell address box. The most frequent mistake students make at this point, is that they forget to press the Enter key to confirm the range name.

Now, how do you use these range names?

Navigating your spreadsheet using range names.
Navigating your spreadsheet using range names.

First, you can quickly jump to your named ranges by using the drop-down menu. When you click on the drop-down menu in the cell address box, you’ll see a list of all the ranges you’ve added to your spreadsheet. Regardless of what sheet they are on. So you can use this to quickly jump to those cells that you work with again and again.

Range Names can replace cell references in formulas
Range Names can replace cell references in formulas.

Second, you can replace cell references in a formula with range names. Does =SUM(January) seem easier to read and understand than =SUM(B2:B10)? Then a formula that uses range names will make your spreadsheets easier to read.

Third, you can use range names in conjunction with all sorts of other Excel tools. As an example, try using range names with the Data Validation tool.

A Named Range provides the source for this data validation list.
A Named Range provides the source for this data validation list.

In the sample above, a range name provides the source list for a drop-down list.

Data Validation Result
Data Validation Result

Resulting in this drop-down list. The list will update as the list of animals changes on Sheet1.

This is a more elegant solution for using drop-down lists, since it means your source lists can be kept on another sheet, and not clutter up the working area. This is something that is impossible to do, without using a range name.

So faster navigation, easy to read formulas and access to more powerful features in Excel. What’s not to love about range names?


Looking to make a powerful spreadsheet or do you want me to make it for you? Drop me a line at catharine@mytechgenie.ca