Excel – Display an Alert If a Sorted List Becomes Unsorted

Excel has the ability to sort data in a variety of ways: Alphabetically Numerically Chronologically In addition, the data can be sorted in either ascending (smallest to largest) or descending (largest to smallest) order. Sorted lists are useful when printing or for scanning when looking for information.  It is far easier to locate an item […]

Excel – Adding Ordinal Suffixes to a List of Numbers

An ordinal indicator is a character, or group of characters, that follows a number denoting that it is an ordinal number, as opposed to a cardinal number. For English speaking countries, this corresponds to the suffixes -st, -nd, -rd, -th in written ordinals (represented either on the line 1st, 2nd, 3rd, 4th or as superscript, […]

Excel Color – Order of Application (Hierarchy)

Have you ever wondered why Excel, on occasion, will or will not color a cell according to your instructions?  The reason may lie in the order by which Excel applies colors. Excel applies colors in layers.  These layers are comprised of three criteria: Colors automatically applied via the implementation of a Data Table Colors manually […]

Two-Way Lookup with the Intersection Operator

Locating a specific item in a table based on two separate criteria can be a challenging task, even for the most ardent Excel user.  Take the following table, for example: Suppose you wish to locate the sales for the Southern region in the month of March.  You could try to write a sophisticated VLOOKUP with […]

VLOOKUP–Looking to the LEFT!

Anyone who has ever used Excel and needed to query a table for information has no doubt used a VLOOKUP function.  VLOOKUP is one of the most widely used functions in all of Excel.  But even the most popular things in life are not without their shortcomings. The single most vexing problem for VLOOKUP users […]

Have you ever wondered just how large an Excel spreadsheet really is? When it comes to entering data, Excel spreadsheets have grown larger and larger over the years.  In Excel’s infancy, spreadsheets were only 256 columns wide and 16,384 rows tall.  Over the years the number of columns remained at 256 but the height grew […]

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 […]

We’ve come a long way over the years when it comes to where we store our files.  In the 80’s and 90’s it was all about local storage; the venerable C:, or “C-drive”.  Then in the 2000’s, when connecting all of the office computers together on a network became the norm, companies told us not […]

Conditional Formatting with Subtotals in Excel

Just as there should be a Nobel Prize for the person who put a screwdriver in the end of a power drill, thus inventing the Power Screwdriver, there should be a Nobel Prize for merging the abilities of Conditional Formatting with the Subtotals tool in Excel. If you have ever used the Subtotals tool to […]

Supercharge Your VLOOKUP Functions in Excel

Any Excel user who considers themselves a “power user” cannot make such a statement with a straight face without knowing how to use VLOOKUP functions. Of all of the ways to retrieve information, the VLOOKUP function is without debate the most popular means of looking up and returning information from an Excel list.  It’s not […]