Join OfficeTipsAndMethods and share your insight in a Comment. You must be a member to comment and all comments are moderated before being published. Membership is free and we would never share your personal information with anyone.

Archive for the ‘Excel’ Category

Yesterday Microsoft announced Office 2016 availability for public preview. Details and a link to the Office 2016 Preview site are in this blog article.

Over the last 12 months, we’ve transformed Office from a suite of desktop applications to a complete, cross-platform, cross-device solution for getting work done. We’ve expanded the Office footprint to iPad and Android tablets. We’ve upgraded Office experiences on the Mac, the iPhone and on the web. We’ve even added new apps to the Office family with Sway and Office Lens. All designed to keep your work moving, everywhere. But that doesn’t mean we’ve forgotten where we came from. While you’ve seen us focus on tuning Office for different platforms over the last year, make no mistake, Office on Windows desktop is central to our strategy.

A crash can impact your computing life in ways that are mere inconveniences to major disasters. In this article I am going to discuss ways of limiting inconvenient data loss.

First a definition: an inconvenient data loss (as opposed to a catastrophic loss) is the loss of data which can be rebuilt relatively easily, requiring only a moderate amount of time and inconvenience to accomplish the re-build.

Catastrophic loss, on the other hand, refers to the loss of critical data that would be very difficult, perhaps even impossible, to rebuild. Some examples of critical data are business or personal financial records, family photos where a print doesn’t exist, original artistic creations, any non-trivial original work that you have created on the computer.

Data loss is a question of when, not if it will happen; it will happen.

Catastrophic Data Loss

The only way to prevent catastrophic data loss is to regularly and frequently back the data up to a second location. How often should you back up? The more important the data, the more frequently you should back it up. A web search will turn up links to many backup applications that will assist you in backing up. Once you have decided on an application, you then need to decide what (individual files and folders) to backup, where to place the backup, and when the backup should happen.

Choose what to backup carefully

Choose a location that is not on the same disk or media as the actual data

Choose a backup frequency that will minimize the amount of critical data that will be lost when your computer crashes

Inconvenient Data Loss

The specific inconvenient data loss that prompted this article was a recent computer failure. In short, Windows crashed, making my computer unusable until I re-installed Windows. At the time, Office 2010 applications were my main tools. Roughly 75% of everything I do using my computer involves one or more Office applications (Word, Access, Excel, Publisher, PowerPoint.) I thought I had a reliable backup strategy in places with backup software copying my documents, spreadsheets, etc. to a backup folder that was in turned synced to a cloud location. That way, even if my computer experienced a total failure, I would still have all my files, with little or no loss.

Over the years, custom templates have become an essential part of my electronic tool box. For example I have Word templates that help me quickly create several different documents that are an essential part of my training practice. These include class lists, class evaluations, training quotations, and course outline formats. In short, whenever I recognized that I was creating documents that repeated standard information, I created a template that would include the information common to each of these types of documents.

Of course it takes time to create good templates but the invested time is quickly repaid because having a template eliminated re-inventing the wheel to create routine documents.

And so it seems, templates were the Achilles’ heel of my backup strategy. I had overlooked that fact that custom templates and page parts in Office 2010 and earlier, were not stored in a location that is readily accessible to backup software. In other words, backing up document does not back up templates.

When windows went down for the count, the crash took out of circulation the folders where my custom templates were stored. Recovery, while not difficult, has been time consuming. Because I didn’t lose my data, I have been able to open files that I had originally created from each template and delete any of the content that was not part of the generic template. The difficult part has been remembering exactly what templates I had been using.

Avoiding the Problem in Office 2010 and Prior

Obviously this is an experience I would like to avoid in the future so I have modified my backup strategy. Ironically, the solution I came up with resembles the Office 2013 approach to custom template storage, something I became aware of only after I had worked out my new strategy.

First, I created a folder, MyTemplates, in my Documents folder. Each time I create a new template, I save it to the the default templates folder and then save a new copy to MyTemplates. My backup strategy already included the Documents folder so MyTemplates is automatically backed up with every scheduled backup.

This approach isn’t ideal because it does require manual intervention whenever I create or modify a template but this minor inconvenience is well worth the bit of time it takes because it minimizes the risk of having to recreate templates in the event of a catastrophe.

How Office 2013 Handles Custom Templates

Office 2010 and prior Office versions buried custom templates in subfolders managed by Windows. The exact folder location depended on the particular Office version. In Office 2013, custom templates can become more accessible to the user. The default is a folder, Custom Office Template in the user’s Documents folders.

Given the experience I outlined above, I strongly recommend going with the default and then ensuring that the Custom Office Templates folder is included in the list of locations that you regularly backup.

Introduction

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.

The Challenge

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.

The Functions

For complete descriptions of the functions used in this example refer to linked help pages, LARGE(), INDEX(), and MATCH().

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.

COUNTIF($E$2:E2,E2)

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

=VALUE(TEXT(E2,"00000000")&TEXT(COUNTIF($E$2:E2,E2),"0000"))

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:

=VALUE(TEXT(E3,"00000000")&TEXT(COUNTIF($E$2:E3,E3),"0000"))

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.

Share this:

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.

Share this:

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.

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.

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.

Formulas

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.