Mike Schaeffer's Weblog
Tue, 11 Oct 2005
Excel 12's Conditional Formatting Rules
David Gainer has Summarized a a number of new conditional formatting rules in Excel 12, over on the Excel 12 blog. These rules were designed to "make a greater number of scenarios possible without needing to write formulas." In other words, all these scenarios have simple solutions directly visible in the Excel 12 UI.

Well, if you can't wait for Excel 12, Excel is pretty darned powerful as it is, and as Mr. Gainer states: most of these scenarios have formula-based approaches that work right now. Here are some of the approaches for current versions of Excel:
  • With data bars, color scales, or icons based on the numeric value in the cell, percentages, percentiles, or a formula. See the posts on data bars, color scales, and icon sets for more information on each of these. - This approach to 'databars' generalizes to formula-based scaling, although it's not as pretty, not a color scale, and not an icon set.
  • Containing, not containing, beginning with, or ending with specific text. For example, highlighting parts containing certain characters in a parts catalog. - Use a formula: a lot of these conditions can be tested using FIND: =FIND(string, A1)=1, checks for parts that begin with string, for example.
  • Containing dates that match dynamic conditions like yesterday, today, tomorrow, in the last 7 days, last week, this week, next week, last month, this month, next month. For example, highlight all items dated yesterday. The great part about these conditions is that Excel handles calculating the date based on the system clock, so the user doesn.t need to worry about updating the condition. - Use a formula: the system date is available via NOW(), and Excel offers plenty of date arithmetic functions to check for specific conditions.
  • That are blank or that are non-blank. - Use a formula: =ISBLANK(A1) or =NOT(ISBLANK(A1))
  • That have errors or that do not have errors. - Use a formula: =ISERROR(A1) or =NOT(ISERROR(A1))
  • That are in the top n of a selected range (where n is whatever number you want) OR that are in the top n percent of a selected range (again, where n is adjustable). For example, highlighting the top 10 investment returns in a table of 1,000 investments. - Use a formula: =RANK(A1, range)>n.
  • Cells that have the bottom n values OR cells that are the bottom n percent of a selected range. - Use a formula: =RANK(A1, range)<ROWS(range)-n.
  • Cells that are above average, below average, equal to or above average, equal to or below average, 1 standard deviation above, 1 standard deviation below, 2 standard deviations above, 2 standard deviations below, 3 standard deviations above, 3 standard deviations below a selected range. - This type of thing can be solved using a particular form of formula: =A1<(AVERAGE(range)-n*STDEV(range)) or =A1>(AVERAGE(range)+n*STDEV(range)). For large ranges, it probably makes sense to move the computation of AVERAGE and STDEV into a cell, and have the conditional format reference (with an absolute reference) that cell.
  • Cells that are duplicate values or, conversely, cells that are unique values. - Use a formula: =COUNTIF(range, A1)=1 or =COUNTIF(range, A1)>1. Ensure that the range you use in the formula has an absolute address. If your range is sorted on the 'key' field, you can use this style of formula: =A1<>A2. This can be much, much faster, particularly for large tables. (For the Comp. Sci. types it's O(N), rather than O(N^2), once you have sorted data.)
  • Based on comparisons between two columns in tables. For example, highlight values where values in the .Actual Sales. column are less than in the .Sales Target. column. - Use a conditional format formula: =A1<B1. Apply it to the entire column you want shaded, and Excel will evaluate the seperately for each cell. The cell references in the format formula are relative to the current cell in the selected range. The current cell is the cell in the range that is not highlighted (but is surrounded by a selection border), and can be moved around the four corners of the range with Control+. (period).
  • When working with tables, we have also made it easy to format the entire row based on the results of a condition. - Relative formulas can be made to do this: select an entire range, and define a conditional formula using absolute column addresses (ie: =$a1). Excel evaluates the format formula for each cell in the range, and since the column addresses are absolute, each cell in a row will pull from the came columns. Therefore, each cell in a row will share the same conditional format, which is what we want.
Based on this, you don't have to wait for Excel 12 to get a lot of these features, you just have to wait for Excel 12 if you want Excel to do it for you automatically. My suggestion would be to learn how to use conditional formatting formulas, but I tend to be "here's how to fish" kind of guy more than a "here's a fish" kind of guy.

reddit this! Digg Me!

[/tech/excel] permanent link

Fri, 07 Oct 2005
Excel 12 Databars, Without VBA.
I suspected as much, but Excel has a way to duplicate my UDF using Excel formulas.


That formula evaluates to a bar of length A1 units, rounded to the nearest 0.5. Rescaling can be done in another cell. If you're interested in a bar that can be right-justified, you can use this:


The trickiest part about this is getting the block characters into the formula. For that, I reccomend using the Windows Character Map.

Qualitatively compared to VBA, this method requires more logic to be represented in the spreadsheet: that adds compelxity for readers and makes it tricker to set up than the VBA. On the other hand, it avoids the performance hit of calling UDF and the requirement that the spreadsheet contain a macro. I honestly don't know which is better style, but can say that this would be a perfect time to use a paramaterized range name (if Excel had such a thing).

reddit this! Digg Me!

[/tech/excel] permanent link

Excel 12 Databars, Now. (Sort of)
Microsoft has just announced a cool new feature on the Excel 12 blog: the databar. I think a picture (linked from Microsoft's Excel 12 Blog) can explain it better than I can:

This will be a nice way to look for trends/outliers, but I can also see it being useful for tracking parallel completion percentages in status reports, etc. Of the Excel 12 features announced so far, this is the one that I'm the most excited about. Of course, it's also the one that's easiest to approximate in Excel <12. Andrew has an approach using Autoshapes on his blog, and I'm going to present a slightly different approach.

IMO, his approach looks a lot better, this approach has the benefit of updating automatically. Pick your poison.

It all centers around this little UDF:
Option Explicit

Function GraphBar(x As Double, _
                  Low As Double, _
                  High As Double, _
                  ScaleTo As Double) As String

    x = ((x - Low) / (High - Low)) * ScaleTo
    Dim i As Integer
    Dim blockFull As String
    Dim blockHalf As String
    blockFull = ChrW(9608)
    blockHalf = ChrW(9612)
    GraphBar = ""
    For i = 1 To Fix(x)
        GraphBar = GraphBar + blockFull
    If x - Fix(x) > 0.5 Then
        GraphBar = GraphBar + blockHalf
    End If
End Function
This isn't rocket science: all it does is rescale x from the range [Low, High] to the range [0.0, ScaleTo]. Then, it strings together that many Chrw(9608)'s, followed by a Chrw(9612), if the scaled value's fractional part is >0.5. The trick in this is that Chrw(9608) and Chrw(9612) are VBA expressions that produce the the Unicode equivalent of the old line drawing characters IBM put in the original PC [1]. 9608 is a full box ("█"), 9612 is a half box on the left ("▌"). The result of this function ends up being a string that (when displayed as Arial) looks like a horizontal bar. ("████▌"). Put a few of those in adjacent cells, and you get this:

The formulat in C2 (and filled down) is =GraphBar(B2,MIN(B$2:B$8),MAX(B$2:B$8),5). The MIN and MAX set the scale, the 5 sets the maximum length of a bar. The maximum length, font size, column width can be tweaked to produce a reasonably attractive result, although I do reccomend using vertical centering.

If you want to get a little fancier, conditional formatting works on plot cells...

...whitespace can possibly improve the appearance...

...and this technique can scale.

1] (The original PC didn't have stanard graphics, it was an option. If you bought the monochrome, non-graphics, video board, characters like this were as close as you could get to a bar chart.)

reddit this! Digg Me!

[/tech/excel] permanent link

Thu, 06 Oct 2005
Excel: 'Repeat', the Simplest Macro
This is a simple little two-bit Excel trick that I find myself using all the time, particularly when formatting worksheets.

In Excel, Control+Y is the 'other half' of the Undo/Redo pair. If you undo an action and want to redo what you just undid, Control+Y undoes the undo, so to speak. However, if you haven't undone anything, and there's nothing on the redo queue, Control+Y repeats the last single action you took.

Repeatable actions can actually be quite complex. For example, opening the Format Cell dialog box and applying a format counts as one repeatable action, regardless of how many format attributes you change. Once you make that format change to one cell and before you do anything else Control+Y has become a key that applies that specific format change to as many other cells as you like.

In a sense, Control+Y is a command that's eternally bound to a simple macro that Excel keeps updating with your last action. If you plan your work to group actions together, this 'automatic' macro can save a lots of time.

reddit this! Digg Me!

[/tech/excel] permanent link

Mon, 03 Oct 2005
Formulas Driven from AutoFilters
I had this written out and then discovered a better way. SUBTOTAL is "sensitive to AutoFilter settings", right? Assuming A1 isn't empty, this formula =subtotal(a1, 2)=1 returns TRUE if row 1 is visible and FALSE otherwise. No VBA necessary.

Not too long ago, I made a post that describes how to replicate some of the behavior of Excel Autofilters using a purely formula based approach. One of the arguments I put forward in support of that technique is that it makes it possible to use filtered result sets to drive other calculations. However, the approach also has two disadvantages: it's slow to compute and can be a little tricky to setup and understand. As a sort of intermediate ground between using the AutoFilter and re-implementing it, this post describes how an Excel formula can determine if a row is a member of an AutoFilter result set. The magic bit is this little user defined function:
Function IsVisible(rng As Range) As Boolean
    IsVisible = True
    Dim row As Range
    Dim col As Range
    For Each row In rng.Rows
        If row.RowHeight = 0 Then
            IsVisible = False
            Exit Function
        End If
    For Each col In rng.Columns
        If col.ColumnWidth = 0 Then
            IsVisible = False
            Exit Function
        End If
End Function
Given a range, this function returns true if every cell in the range is visible (non-zero row height and column width). The way Excel works, the Row Height of a row hidden by the Autofilter is reported as zero. Therefore, IsVisible returns false when given a reference to a cell in a hidden AutoFiltered row. Of course, it also returns False for cells in manually hidden rows and columns, but if you're careful, you can avoid that.

For a simple use case, this function can be used to generate alternating color bars that always alternate regardless of the AutoFilter settings. To set it up, Put TRUE in the topmost cell of a free column next to the AutoFilter to be colored. Below the TRUE, fill down with a formula like this: =IF(isvisible(D2),NOT(D1),D1). This formula inverts the value in the column, but only for cells that are visible. This guarantees that regardless of the AutoFilter settings, this column will always alternate TRUE/FALSE in the set of visible rows. This column can then be used to drive a conditional format that highlights alternating visible rows.

A couple sidenotes:
  • This function works because adjusting an AutoFilter triggers recalculation, and Excel notices that this function depends on row heights. For hiding columns, it's a lot less reliable. All the calls to IsVisible have to be forced to recompute after the column is hidden or displayed. To do this, IsVisible can be marked as volatile and recalculation forced by pressing F9. This is a lousy solution.
  • To optimize performance, the function short-circuits its search. The Exit Function's bail out of the calculation as soon as the first hidden row or column is discovered.
  • Excel's SUBTOTAL intrinsic function is also sensitive to AutoFilter settings.

reddit this! Digg Me!

[/tech/excel] permanent link