The New IFS Function

Once in a while, Microsoft comes up with a new feature in Excel that thoroughly captures my interest. As most Office 365 users know, there are new features and functions being regularly added to this latest version of Excel. Not all of them strike the fancy (as the British may say…) of a large group, but there are exceptions.

One such marvelous feature (IMHO) is the new IFS function. If you are like many Excel users, you have had experience with Nested IF Functions.  These handy functions offer a wonderfully powerful way to extract information from your data subject to multiple conditions. 

Regrettably, however, Nested IF Functions tend to be rather, well, Clunky. Thankfully, the new IFS function simplifies the nested practice, as it allows you to specify a series of conditions in a single Streamlined Function!

Instead of being tasked with stringing multiple IF functions into a mega-function, (which will likely boggle the minds of others who might inherit your work), you can achieve the same results in a much more elegant format.

Let’s use the classic exam grading system of assignments for an illustration. Assume that you have a student’s numerical grade in Cell A1.  Using our cool new function, we can construct a formula in Cell B1 as follows:

=IFS(A1>=90, “A”, A1>=80, “B”, A1>= 70, “C”, A1>=60, “D”, A1>=0, "More studying is recommended…”)

If the student’s score in A1 is 84, then the clear result in B1 will be “B”.  This marvelous enhancement to Excel that can save you a good deal of time, and make your formulas easier understood by others as well as yourself. If you are an Office 365 user, try it sometime. I’m confident that you will find this a very worthy addition.

Concatenation and You

As I have previously mentioned, Concatenation is one of my favorite time savers. Teaching classes as large as 200 students, I have frequent use for sending emails to large groups. 

Not surprisingly, I always make a spreadsheet of my students’ names (this could also work for you company employee, of course). When it comes to sending emails to the group, I have found that Concatenation is the perfect solution!

Assume that you have a list of students with the First Names in Column A and Last Names in Column B. With concatenation, you can easily combine them into an Email-Friendly column of names in a “Last Name, First Name” format. The secret to Concatenation is using Ampersands and cell references combined with quoted text or punctuation. Here’s how to do it:

Assuming your table starts in cell A1, put the following formula in C1 (Note: There must be a space after the comma in quotation marks):

=B1&", "&A1

This elementary formula combines the contents of B1 (last name) with a comma, space, and contents of A1 (first name). Select C1, place your cursor over the “handle” in the lower-right corner of the cell, and give a quick double-click. This will populate your Column C Email List down as far as you have data in Columns A and B.

Then it is a simple matter of copying the contents of Column C, and pasting into Outlook. Bamm! You have just saved you time that you can use doing something useful (like Golf…)!

Red Light - Green Light

Everybody raise their hand who likes typical, boring, drab Excel workbooks. Anybody? No? I didn’t think so. I am sure most of us would agree that Excel worksheets usually lack any kind of Pizzazz to make them engaging. Well today we are going to look at how to take the “Cold” out of Cold Hard Data.

A worksheet can serve as a Decision Tool and showing a Yes, No, or Maybe adjacent to your agenda items can certainly get the job done. but it’s Boring, Boring, Boring, isn’t it? Since the advent of Excel 2007 you have sets of Graphical Icons with your decision lists that can give your reports some of that much-needed Sparkle!

Let’s suppose you have a proposed agenda for an upcoming annual corporate meeting. Possible topics have been submitted from various departments, and you want to use a Semaphore for an icon when make a preliminary worksheet showing a:

• Green Light for a definite inclusion in the program
• Yellow Light for a possible inclusion
• Red Light to exclude the topic from the agenda

Here’s What to Do:

1.  Select the range of cells in which you want a Semaphore to appear
2.  Click Conditional Formatting on the Home tab, choose Icon Sets and pick the set that includes the Semaphore
3.  Go back to Conditional Formatting and choose Manage Rules
4.  Click Edit Rule and put a check in the Show Icon Only box and Apply
5.  By default, a “1” will be a Red Light; “2” will be a Yellow Light, and “3” will be a Green Light

Your Decision Lists will instantly take on a New, Engaging Look and will be sure to gain closer looks by any of the users. And, of course, the cool thing is that it is Easy (and “Easy” is a good thing…). 

Best Chart Titles Ever!

Using Dynamic Chart Titles has been one of the most popular topics of this blog. Since dynamically controlling your chart’s Titles can make your charts even better, it is fitting to look at this fantastic technique one more time. Believe me, if you are looking for an “Oh, Wow!” feature for your next report that includes a chart, a Dynamic Chart Title is for you! By linking the chart title to a value displayed in a specific cell on your worksheet, you can create a very accessible chart that will amaze and delight your users (and maybe garner you the attention you deserve…).

For example, we will presume you have a table of data and a chart that changes dynamically every time you change the value in a dropdown box. It is then remarkably easy to create our eye-catching title functionality:

1. Create your chart linking it to your dynamic table
2. Select your chart by clicking on it
3. Click the Chart menu and choose Chart Options
4. From the Chart Options window, click on the Chart Title box
5. Enter a temporary placeholder value (Such as “Chart1) and click OK
6. Make sure the temporary title is selected and click the Formula Bar above the sheet
7. Type "=" then click on the cell that contains the dropdown box, (simply created using Validation/List), and click Enter

How Totally Cool is That!  Now every time you change the Name or Value in the Drop-Down, the Chart Automatically Updates its Title! This is an advanced, (but effortless) way to make the charts in your report Stand Out from the mundane masses.

Seriously, give this technique a try; you’re going to like it!

A Few More Tricks…

Excel Tricks can be a seemingly magical way to save time and look Cool (and, of course, looking “Cool” is the most important…) in Excel. Here are a few of my current favorites:

1. Select Non-contiguous Cells and Ranges
We all know how to select contiguous cells in a range or database, but how about non-contiguous cells?  Selecting these cells in a worksheet is as simple as holding down the CTRL key and click on the cells you want.  Presto!

2. Enter in Bulk

Let’s say you want to enter the same numbers or same text in a block of cells. There are a number of tedious ways of doing this, but if you want to save your wrist and look Totally Cool in the process, you can do the following:

    a) Select the entire range of cells you want to contain the same content

    b) Type your words or values (be sure to do this while having the entire block of cells selected)

    c) Press CTRL+ENTER and Bamm - That’s it!

3. Align Text Your Way!
Right-Click and access the Alignment tab on Format Cells. It’s a Snap aligning your cell in any orientation you want.  For instance, do you want the text in your cell to be vertically oriented?  Just click the Vertical Orientation Graphic, and Boom, mission accomplished!

4. Jump Between Worksheets

To move from worksheet to another does not mean you need to reach for your mouse.  To switch to the next worksheet to the left, keep your hands on the keyboard and simply enter Ctrl + Page Up. Or change to the worksheet to the right by entering Ctrl + Page Down. Nice!

5. Use Your Chart in Another App
Do you want to use your chart in PowerPoint, Word, or some other application? Select your chart and Copy/Paste as a Picture.  You can then feel assured that it will stay true to the original.  Easy Magic (and “Easy” is always a good thing…).

Tricks, Tricks, Tricks! Every Excel Guru should have a sack of them!