Archive for the ‘Excel’ Category
No this isn’t a variation on the classic Abbott and Costello comedy routine. It’s a discussion of how to use an excel formula to arrange members of a group according to some sort of performance criteria.
One way of finding out who the top performers in a list are is to sort the list in descending order by the value that reflects the performance. Of course, that would mean having to re-sort the list every time you want to see the new ranking.
This article will demonstrate how to perform ranking without resorting the underlying list using formulas. These formulas use three excel functions, LARGE(), INDEX(), and MATCH() to rank individuals according to their total sales.
The companion workbook for this article, RANKER.xlsx can be downloaded from here. All data in the workbook is fictitious.
We have a large list of sales representatives. We want to know, without sorting the list, the names of the top 10 sales people. The leaders list should always display the current top 10 sellers based on current data in the sales representatives list.
At first glance, this problem seems to call for some sort of lookup functionality. Unfortunately VLookup() is not up to the challenge because that function can only use the first column of a table or list as an index to the list. Or list of sales representatives has the person’s id or employee number in in its first column.
The more general Lookup() function is also unsuitable because the index list it refers to must be sorted in ascending order.
LARGE The Large() function searches a list of values and returns the value that is in the relative position in the list that you specify. For example, you can find the largest value in a list without having to sort the list. The function is very flexible because it has an argument that lets you specify any specific relative position in the list from 1 (largest value) to the very last position (by specifying a number equal to the total number of items in the list. All this can be done without ever having to sort the list.
INDEX The Index() function returns the value found at a specific position in a list.
MATCH The Match() function searches a list and returns the number of the list row in which the value you have said to search for is found. The example used in this article specifically uses a one-column list. If the list contains duplicate values, Match will find one of the duplicates over and over for as many times as the value is duplicated.
Solving the Duplicates Issue
While the probability of two representatives having exactly the same Sales value may be low, the very possibility that a duplicate might exist necessitates a strategy to deal with duplicates should they occur.
In this example, we have used a bit of formula trickery to create a unique way of identifying duplicate values.
This formula when copied down a column will count the instance of a value in the specified range.
Notice the use of mixed references in the first argument. For this technique to work, the list may not be formatted as an Excel 2007/2010 Table. If the data is formatted as a Table, convert it to a range before entering the formula.
This expression is part of the more complex formula contained in cells F2 to F224 on the Demo_Notable worksheet. When copied down a column the reference to the start of the range remains fixed on E2 while the end of the range and the criteria cell reference are incremented for each successive reference.
However, that’s not the formula trickery I mentioned. In order to use the Large() function, we need numeric values. At the same time, in order to create a useful identifier that will not distort results, we need to work with numbers formatted as text. The trick is to convert intermediate calculations to text and then reconvert the final result back to a number, not just the textual representation of the number.
This is what the full formula looks like
When you copy this formula down the column, say to E3, Excel modifies the relative cell references. So in Row 3 the formula would become:
The idea is to convert the Sales value to text with a fixed number of digits and then concatenate the text value with the text representation of the count of duplicates. Simple adding the two values together arithmetically would distort the value and not bring us any closer to having unique value that will distinguish between duplicate instances. COUNTIF() is counts how many times the value of interest occurs in the specified range, which grows row by row as copy the formula down the column.
Here’s what the formula looks like when you copy it down several rows. Notice that the second argument of the CountIf() function changes the end of the range being counted for each row the formula is copied to. However, the range beginning is anchored to cell E2 because that part of the reference is absolute.
Caution: Using this technique with a very large list may be very slow. For lists of a few hundred items or less should not be affected.
The Final Solution
Six formulas drive the solution. Here are the formulas from the second row of the try it worksheet. Once the individual formulas have been created they can be copied down their respective columns (using autofill.)
The formula in F2 converts the sales value in E2 to Text and concatenates that value with a four character representation of the result of counting the frequency with which the value in E2 appears. This gives us a unique index value that ensures we will be able to find each instance of the duplicate value.
In I2 the formula finds the value in column E that is x positions from the largest value. H2 contains the value 1, so the formula in I1 returns the largest value from the list. This result is not quite adequate because it can’t distinguish duplicate values.
J2 finds the row number of the value in I2. When there are duplicate values in column E, the result in this column will always indicate the first row in which the value occurs.
The formulas in K2 and L2 are very similar to those in I2 and J2 EXCEPT the formula in K2 uses values in column F to determine rank. The forumlas in column F ensure that there are no duplicates to be ranked. The method uses has a significant side effect because it yields an higher rank for the first occurrence of a duplicate and progressively lower ranks for each subsequent occurrence.
The video accompanying this article includes a demonstration of the effect of duplicates on ranking.
The final formula in cell M2 uses the row number calcluation in L2 to find the name of the rep having a particular rank.
Introduced in some Office 2007 applications, the Fluent User Interface is Microsoft’s attempt to ‘expose’ (make more readily available) the commands used to work with an Office application. With Office 2010, all Office applications use this style of user interface. For the average user, the most visible aspect of the Fluent User Interface is the Ribbon, which replaced the menus and toolbar interface style of Office applications from ‘97 through to 2003.
On the positive side the Fluent User Interface does a creditable job of displaying, and making more easily available, the commands that serve the needs of the vast majority of users. In the menu/toolbar style that preceded the Fluent User Interface, some commands necessarily had to be buried in the menu structure. Only the most adventurous user stood any chance of accidentally discovering some of these commands.
The Ribbon, on the other hand makes it possible for the user to easily discover many more commands simply by exploring the contents of each tab. What users will encounter however, are differences in the appearance of the Ribbon depending on the current width of the Application window and the monitor’s resolution.
Notice the detail on the Home tab of the Excel 2010 Ribbon for example:
This is a screenshot of the Ribbon as it is displayed in a very wide window. For display purposes here, the image had to be somewhat resized. Compare that image with this one, using a narrower window:
In the first image, the Styles group has a rich assortment of buttons. In the second, the styles group has only three buttons. In to see the cell styles gallery, you have to click the Cell Styles button dropdown.
With progressively narrower windows more and more groups are collapsed to a few essential buttons which you must click in order to see all the possibilities a group offers:
Here the Styles group has been reduced to a single button:
Here, the Number and Cells groups are also reduced to single buttons:
When the window is extremely narrow, most groups are barely recognizable. Notice that none of the tabs can display their full name:
It is even possible to reach a point where there is no longer enough screen with to display the full Ribbon:
When that happens, ‘expander’ buttons appear, allowing you to navigate to hidden portions of the Ribbon.
Practically speaking, it is unlikely that you would ever use such an extremely narrow window as in the last graphic but keep in mind that screen resolution also affects how the Ribbon will display. The lower the resolution setting, the more likely it is that you will see a truncated view of the Ribbon.
I am among the first to point out that Excel is not a data manager. Yet, at the same time, Excel does have some useful features for working with data, especially small data collections. Such lists can be used directly for data analysis, for example, or indirectly as the data source for mail merges in Word.
Regardless of how the list will be used there are four fundamental rules for setting up an Excel data list:
- The list should have a header row of unique names for each column in the list
- There should be no blank columns in the list
- There should be no blank rows in the list
- Similar data should appear in one column
The last rule may be misunderstood because there is one other guideline for setting up any list. Data in any column should not be grouped. For example, in a name and address list, newcomers are tempted to list a person’s full name in a single cell. This can become problematic very soon.
For example, if you use a single cell for people’s full names, you can then sort the list only into ascending or descending order by the full name. Depending on how you actually enter the name information, a meaningful sort may prove to be impossible.
So, while first name, middle name and last name are similar types of information –they are all names, after all – they are really different and deserve their own cells and therefor their own columns. Separating names into Full Name, Middle Name and Last Name, allows you to then sort the list by Last Name; where several people have the same last name, those groups can be further sorted by First and Middle Names.
Separate columns for First, Last, and Middle names, makes your data far more flexible to work with in other ways. For example, having the first name separate from the last name makes it possible to address a letter to John Smith, but have a salutation in the letter like, “Dear John.”
The video illustrates just how easy it is to create a simple data list. In coming articles, I will discuss how to enhance the list to provide additional columns (fields) for mail merging and how to set up the list as mail merge data source.
Recently, a colleague distributed some rather important health and well-being statistics, illustrating the data with an exploded 3D Graph. Unfortunately, while 3D graphs are more pleasing to the eye than their flat cousins; the perspective necessary to create the 3D illusion, distorts the values being plotted.
Here’s an example using simple arbitrary data. First the exploded version:
Notice how, in the 3D version, the Cons wedge appears smaller than the Pros wedge, even though the two wedges represent exactly the same value (46%). In the flat version, on the other hand, the Pros and Cons wedges appear to have exactly the same size.
Is it the Exploded view that creates this illusion? Consider the following unexploded views. The illusion persists.
An unscrupulous presenter could easily use this illusion to distort the facts and unfairly influence his/her audience. (Remember the adage: “Figures don’t lie; liars figure”?) Think about a political race, for example. Depending on which position the presenter wants to improve the apparent advantage of, all he or she has to do is rotate the 3D chart accordingly to immediately improve the apparent advantage of the favoured position:
The Pros Have It!
The Cons Have It!
Of course, these examples include data labels to help the viewer’s interpretation. Omitting the labels can only make the dishonest presenter’s self-appointed task of deception easier.
So the next time you have to create a graph, think carefully about purpose of graphs and avoid the inevitable optical illusions inherent in 3D charts.
Here are some useful links that I have recently come across on the Web. The list is weighted in favour of Excel only because, at the moment, I am doing mostly Excel training.
A help guide to working with the Fluent User Interface (Ribbon) first introduced in Office 2007 and enhanced in Office 2010, replacing traditional menus and toolbars
BLOGS AND PEER SUPPORT SITES
A blog focusing on tips and how to techniques for getting the most out of Microsoft Office Applications
User peer support forum primarily focused on Microsoft Access but with an active Excel forum as well
Bill Jelen’s (Mr. Excel) site includes peer support forum and many pages of Excel help and tips from Mr. Excel himself
YouTube gateway to the ExcelisFun channel –hundreds of well documented videos illustrating how to do just about anything in Excel.
An excellent collection of Excel related links
Clever data validation example
Color Palette and the 56 Excel ColorIndex Colors:
Create an Excel UserForm
Excel Add-ins Directory:
Analytics and Visualization
Excel for Developers
Excel VBA – Reference Guide
Index of /Excel:
The Spreadsheet Page:
Excel Add-ins Directory
Use Excel spin boxes to help with financial modeling:
VBA for Microsoft Office Excel 2007
Yahoo Groups MS_Excel
Yahoo Groups Excel VBA:training.
I came across this video today which is an excellent tutorial of advanced Excel techniques for data validation. The example uses the Indirect() function as the source for the lists that are used as choices in a ‘drop-down’. I will let the video speak for itself.
One thing that the video doesn’t mention, is that because the lists validation works with are named ranges, they can actually be on a different sheet than the one that the data validation is used on. Ordinarily, data validation lists must be on the same sheet as the cells with the validation rules.
Linked In member Victor Chan publishes an excellent blog at Launch Excel. In two of his recent articles,
Victor discusses very useful Excel Data Entry techniques. Check out Victor’s articles to learn how to improve your Excel efficiency. He also offers a nicely organized collection of Excel shortcuts free if you subscribe to his blog.
Here’s another nice trick I recently learned about. Create and use a custom list to manage lists of names that you use repeatedly in a worksheet.
Create and Use Custom Lists
You can create and use custom list for sorting and for speeding data entry
For example, you are using an Excel Worksheet to manage shift assignments for the same group of people every week. Occasionally you need to sort the list by the names but you do not want the sorted list to be in alphabetical order. A custom list will give you that flexibility.
Start by typing the list of names, in the order you would like them, in an out of the way place. This list can be located on an otherwise unused worksheet for example. Just type one name in a cell and press enter and type the next name. When you are finished you should have a vertical list of names with no blank cells. I have created a list of six names but your list could be much longer.
If you select this list before starting the next step you will be able to import it into your custom lists with just the click of a button.
Now, in Excel 2010, click the File button (in 2007, click the Office button) and click Options (in 2007, Excel Options).
Select Advanced. You will need to scroll down a bit. Look for the Edit Custom Lists button and click it.
That will bring up the Custom lists dialogue. There will be at least four custom lists already there if Excel was properly installed on your system.
You should see the range you selected already filled in beside the caption, “Import list from cells.” Click the Import button and your newly created list will appear at the bottom or your custom lists.
Sort Using a Custom List
Whenever you want to sort a list that contains these names into the order of your custom list, all you need to do is select the Sort button on the Ribbon and specify custom for the sort level that refers to the column containing these names.
When the custom list dialogue appears, select your custom list.
Use a Custom List for Data Entry
If you have ever used autofill to create a list of months or days, you will love this added benefit of having a custom list. Once you have create a custom list, you can type one of the names form the list and then use autofill to create a list of all the names in the order of the custom list.
So, for the shift assignment example, when it comes time to create new shift assignment list, all you need to do is type one of the names from you custom list and use autofill to type the rest for you either vertically or horizontally.
Don’t Discard Your Original List
If you must occasionally add or remove names from your roster, do it in the list you built to import into custom lists. When you have that list up to date, select it and go to the Custom Lists dialogue. Click on your original custom list and delete it, then Import the revised list.
Using this technique will save you a few minutes every time you have to create a new roster for you shift assignments. The time it took you to build and import the list as a custom list will quickly be paid back ten-fold.
At one time one of my job responsibilities included budget management and financial reporting for the charity I worked for. Among other things, I had to identify, analyze, and report on variances on each account that was $1,000 or more over or under budget. At the time, my employer operated offices in four locations, with three or four departments in each location. So I had a fair number of accounts (as I recall about 600) to review every month just to identify those that needed further attention.
Keep in mind that personal computers spreadsheet software were, if not in their infancy, still in their very early youth. These were the days of DOS and non-graphic spreadsheets. Colour monitors, were as yet virtually unheard of.
So, at the time, the best solution I could come up with was a conditional formula in cells adjacent to the variance column that displayed an asterisk when the actual value was $1000 or more over or under budget.
The formulas in column G use the ABS() function to calculate the variance. Printed reports had to show this value for all accounts. The formula first calculates the difference between the actual and budget for the account. ABS calculates the absolute value of the result. So whether the variance is positive or negative, it will be displayed as a positive number.
Column H will display asterisks only if the variance is $1,000 or greater. The formulas use the IF() function to test the variance for being equal to or greater than $1,000. If the test is true, the cell displays an asterisk “*”. The third argument “” cause the cell to display a blank. “” is known as the empty string. You can use it anywhere that you need to have text with no actual characters.
Welcome to the recent past
By today’s standards my solution was rather primitive. With the advent of Graphical interfaces in general and Excel for Windows in particular, conditional formatting became available. With conditional formatting, you can control the format of a cell depending on a rule you create. The format of the affected cells changes automatically depending on whether the rule is true or false whenever the worksheet is recalculated.
So now, something like this became available:
You could set up to three rules so you could highlight cells differently according to the rules you set. So you could visually rank cells according to their values.
Here I have three rules. If the cell value is less than $500.00 the fill colour should be green. If the cell value is $500.00 – $999, the fill colour is yellow. For variances of $1,000 and more, the fill colour is red.
It is also possible to control the formatting of a cell depending on the value of a different cell:
Conditional formatting not only makes it easy to flag serious error conditions (reportable variances in this case) but also enables more pro-active flagging. So, in this example a red cell indicates that variance reporting is required; yellow indicates an account that should be reviewed and monitored; green indicates that no action is needed for the line item at this time. By yellow flagging accounts that may be on their way to a serious variance you can be pro-active and start looking for ways and means to prevent the item from sliding out of control.
Welcome to the 21st Century
Excel 2007 introduced some major enhancements to Conditional Formatting. Now we don’t just have a choice of graphics to convey information about our data even more effectively.
Here the conditional formatting in column G indicates the relative value of each variance by the size of the bar. In column H the traffic light icons indicate whether a report is required for the variance, whether the account should be watched more closely, or whether current results for the account are within satisfactory limits.
The possibilities of Conditional Formatting, while not endless, are extensive. Whether your worksheet is strictly a working document designed to help you manage you job responsibilities more effectively, or whether your worksheet is part of a presentation to your boss or others, Conditional Formatting is well worth exploring.
You may recall this article that talked about how to recover Row A or Column 1 if you have hidden them. Yesteray, Deborah Dalgleish in her Contextures Blog shows an even easier way to do select a hidden first row or column. Have a look at her short video in this article.
Excel on TechRepublic
Along with this article you will find some excellent links for productivity boosting Excel shortcut key. Well worth a visit. You may need to take out a free membership to view or download some of the articles.
Quote of the Day
The future is an opaque mirror. Anyone who tries to look into it sees nothing but the dim outlines of an old and worried face.
– Jim Bishop
Excel, PowerPoint, and Word 2007 all have Recent Documents lists on the Office Menu. You can set an option for the number (up to 50) of document titles to be displayed on this list. It’s a handy way of getting back to documents that you have recently worked on. You don’t need to remember where the document is. Just click the name in the Recent Documents list.
Quote of the Day
Ultimately, the only power to which man should aspire is that which he exercises over himself.
– Elie Wiesel
Even with the option set to retain 50 documents on the list, however, a document that you use only occasionally may be forced off the list if you work with a large number of different documents. Tracking down files that you use only once in a while can be challenging, to say the least. Did you know that you can mark a document name so that it always stays on the list?
To the right of each document name, there is a grey pushpin icon. If you click that icon, the icon will change so that it looks like it is pushed into a corkboard and the colour will change to green. A document name with the green pushpin will always stay on the Recent Documents list, regardless of how many other documents you open and close or how long since you have opened the document.
The name will not always be on the top but it will be in the Recent Documents list. So, if you only open that Greeting Card list once a year and want to find it easily next year, click its pushpin icon on the Office Menu. The next time you go to open it, that once a year document will probably be at the bottom of the list but it will still be there waiting for you to click its name so you can get ready to send next years cards.