Microsoft Excel

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:

Convert01

Read More

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.

Read More

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.

Read More

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.

Read More

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?

Read More

Microsoft Excel – Understanding Excel Functions with Help

Admit it, you’ve used Excel functions without any idea how they work.  Someone said, “Click here for this.  Click here for that.” You’ve dutifully followed orders, blindly clicking on cells with no real clue as to why.

It’s time to lift the veil of mystery and understand why these functions are so demanding of such data.

Here is just one of many options for understanding function logic:

This first part has nothing to do with obtaining function information, but it’s a great time saving trick.  If you like to type your functions directly in the cell; press your EQUAL button [=] and start typing the first few letters of the function name.  As you type, Excel will begin to AutoComplete the function’s name.

Read More

Microsoft Excel – Convert Answers to Values FAST!

If you are new to Excel, you no doubt have already discovered the need to take a series of numbers created by formulas and convert them to fixed values.  In other words, replace the questions with the answers.  With no idea how to accomplish this, beginners usually spend great stretches of time retyping the numbers into the answer cells to make them “permanent”.

Eventually, someone who feels your pain turns you on to the technique of highlighting all of your formula cells, clicking Copy, then in the same cells clicking Paste Special…, Paste Link.

You could now not be more ecstatic.  “This is going to save me sooooo much time”, you say to yourself.

Well, guess what.  There’s an even FASTER way to accomplish this task.  Try this out:

Read More