Excel – Convert Names to Email Addresses

Converting Names into Email Addresses

Suppose you have a list of names, perhaps a roster of employee names, and you wish to generate email addresses for these individuals. If you work at a company that has an established standard for email addresses (i.e. first initial of first name with last name) then you have a few options. The preferred strategy depends largely on the version of Excel you are using as well as the naming pattern used in the emails addresses.

Flash Fill (Excel 2013 / Excel 2016)

If you are not familiar with Flash Fill, this tool allows you to type a pattern next to existing data and Flash Fill will repeat the pattern for the remaining data but on a per-record/per-line basis.

Let us take a look at the following example:

You have a list of first and last names and you wish to convert those names to an email format that takes the first letter of the first name, adds a “dot”, then adds the last name with an “@” sign and the company domain name. If we had an employee named “Fred Smith” who worked at “widget.com”, we would need to assign the email address “f.smith@widget.com” to the user.

Imagine a list like the following:

How to use Pivot Tables in Microsoft Excel

In our recent free webinar, SkillForge instructor Bryon Smedley shows all of the cool things you can do with Pivot Tables in Microsoft Excel. If you’re not using them already, you can learn all about them in our Microsoft Excel Pivot Table Training Class! How …

How to Round Task Durations in Microsoft Project

How To Round Task Durations in Microsoft ProjectWatch this video on YouTube In this tutorial, you’ll see how to round the duration of a task in Microsoft Project by using a custom field. You’ll also see how to use this custom field in all of …

Dependency Types in Microsoft Project

When creating schedules in Microsoft Project the first thing that a Project Manager would typically do is to input the tasks involved in a project. These tasks then need to be linked to show the relationship between them. These links create task dependencies.

There are 4 different types of task dependency:

Finish-to-Start (FS): The finish date of one task drives the start date of another.
Start-to-Start (SS): The start date of one task drives the start date of another.
Finish-to-Finish (FF): The finish date of one task drives the finish date of another.
Start-to-Finish (SF): The start date of one task drives the finish date of another.

Excel Pivot Tables Filter by Values

If you’ve ever used Pivot Tables in Excel, you no doubt have discovered the wonders of filtering. The ability to filter row or column items can be extremely helpful when you don’t wish to analyze all of the items in the driving data set.

filter_pivottable_values_1

But what do you do if you wish to filter by the Value-based items? In other words, the numbers in the “connect the dots” area where row and column choices intersect. These, on first glance, don’t appear to have sorting and filtering controls available.

filter_pivottable_values_2

Rest assured, they do exist; you just have to dig a bit to find them.

Excel Lookup with Dynamic Input

VLOOKUP is great for returning information from a database, but one of the limitations is that the return information is static.

What if the user wishes to look for certain data one day but different data another day? This would require either two different sets of VLOOKUP functions or the functions would need to be reprogrammed.

In the database below, the user would wish to return address information in one scenario, but return financial information in another scenario.

Suppose there are times when the user requires a mixture of the two; that would require a third set of VLOOKUP functions. This could become an ever evolving set of work.

Automatically Refresh Excel Pivot Tables

Excel PivotTables are one of the greatest tools in the spreadsheet user’s toolkit.

However, there is one tiny bit of functionality that appears to be missing: the ability of pivot tables to automatically update when information in the source data changes.

Most user’s see this as a glaring lack of functionality. There is, however a very good reason why pivot tables do not automatically update.

Office 2013 – Recover Unsaved Documents

Admit it; you’ve done this more than once. You open an Office application like Word or Excel and type out your next great novel or number-crunching masterpiece. Then, in a state of haste, you start closing windows and accidentally close the one window you intended to leave open. One second later you realize, this was the one windows you shouldn’t have closed. All is lost… or is it?

Power Pivot KPI Icons Not Changing

A common complaint about Power Pivot KPI icons is that it seems no matter which set of icons you select, it only shows the standard Red-Yellow-Green circle icons.

PP1

Rename Files Fast with the Tab Key

If you scan documents from a network scanner (like the giant printer down the hall) and have the scan sent to your email, you are probably less than thrilled with the name that the scanner gives the file. The name is usually something generic and uninformative like “Scan0001.pdf” or a combination of the date and the time like “07182015-100855.pdf”.

Tab1

Microsoft Office