Using Excel MODE Function to Return a Text Response

Excel’s MODE function is a great tool for returning the most frequently occurring number in a set of numbers. But what if you want to return the most frequently occurring word in a list of words?

MODE with Numbers

Using the MODE function in Excel is quite simple; you point to a list of numbers and MODE will tell you which number occurs the most often.

In this list, the number “4” appears more often than any other number.

MODE with Words

As you can see, the MODE function does not work very well when pointing to a list of words.

Mode2

The function returns a “#N/A” error message.

Not to fear; MODE can be made to return words, but it take the combined efforts of SEVERAL functions, none of which are MODE! (How odd does THAT sound?)

Excel – Mixed Pivot Table Layout

Microsoft Excel Pivot Tables is one of the greatest inventions known to man; second only to those buttery mints they bring you in fancy restaurants. Even the greatest of ideas are not without their issues. Take for example the Report Layout feature in Pivot Tables. Excel gives the user three options with which to display hierarchically (wow; that’s a hard word to spell) related data.

Compact Form
Outline Form
Tabular Form

Let’s examine what makes each layout unique.

Compact Form

All row-based data is combined into a single column, one row per line item, and indented to reflect the position within the hierarchy.

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:

What’s New in Microsoft Office 2010 – Part 1 of 6

With every new release of Microsoft Office (or really any software product for that matter) users of a previous version ask themselves the question “should I upgrade?” And, as always, the answer is…it depends. I’ve collected a few of the new features of Office 2010 to hopefully help you make your decision. However, if you’re one of the many folks who didn’t move to Office 2007 because of the new Ribbon interface – you may want to seriously consider Office 2010. It corrects some of the confusion introduced by the Ribbon interface and adds quite a few useful new features as well. In this post, we’ll explore some of the new features that are common to most, if not all, of the Office 2010 applications.

excel

Using Excel MODE Function to Return a Text Response