Auto highlighting Excel charts

On your dashboards, reports, and charts it’s a good idea to alert the user to certain types of values. In a recent post, we discussed how to alert users to certain records on tables using custom formats. We’ll build upon that here but transition to how we might achieve a similar effect on a chart.

Take a look at the chart in the image below. I’ve built the chart so that no matter what values are being plotted, the greatest value is always highlighted in red. Following similar themes from previous blog posts, I achieved this effect with no VBA. The advantage to not using VBA is that it’s speedy and requires no iteration. On the other hand, for this effect to work, you’ll still need to test if every point is the maximum by using an Excel formula. For small series such as the one shown here, this isn’t really a big deal. But for series with many more points–and more more complex projects–it’s an additional column of data you’ll need to take care of.

image

Let’s do it!

Luckily, the implementation is super easy. So let’s get started. For this example, I needed to create some fake data. So for the values B2:B5, I used RANDBETWEEN. In cell B8, I then find the maximum value that has resulted. (Of course your data won’t be random, but to demonstrate this chart will always pick the greatest value, I use RANDBETWEEN.) The formulas for these items are shown in the following image.

image

Once we have the maximum value, we need to create a parallel series where that maximum value is the only value that appears. Everything else should be #N/A (or something similar–more on that in the discussion section). We could use zero here, but zero is actually a value on our chart. So it’s better we use something that represents a non-number. NA() is perfect for this. The image below shows an IF formula used in the adjacent column to test whether a specific region holds the maximum value.

image

Now, we should have two columns. The first column contains our original data; the second column contains only the maximum number from our original and #N/A’s. At this point, you’re ready to insert a chart. The easiest thing to do is to highlight our data range and insert a new column chart. I’ve done just that below.

image

Remember, the  values on my screen are being generated randomly, so the orange column may be at another range (or even another color) on your screen. At this point, what we’ll want to do is plot the “Max Series” –  the orange column – on the secondary axis. You can do this easily by right-clicking the orange column and going to Format Data Series…..

image

Within the Format Data Series dialog box (Excel 2010 and previous) or context pane (Excel 2013+) go to Series Options and select Secondary Axis. The image below will result.

image

All you have to do now is some clean up and then you’re finished. Below, you can see I’ve removed an axis and adjusted some formatting.

image

Discussion

In this simple example, we just used the maximum value, but you could highlight all sorts of values depending upon what you’re looking for. Perhaps you would like to highlight one or more columns whose values are in a specified range; or, perhaps you just want to direct your viewer to the smallest value. Really, the rules are endless but the dynamic is the same. You just need an additional column that parallels the first.

You may have noticed we introduced #N/A errors where a value did not match the maximum value. Generally, having unhandled errors on the spreadsheet can slow things down. (I’m not sure if that extends to generated errors like these, perhaps a reader could verify for me? I would think they make a contribution.) One way around this is to use a number like negative one instead. If you go that route, just make sure you’re only plotting positive data. You could also use a blank text string, “”. I’ll leave that choice up to you, but remember all options come with tradeoffs. Try each out and see which works the best for you.

Download file: Max Chart Highlight

Advertisements

Better Option Button Controls

So let’s take a look at your standard form control option buttons. In the image below, I have three buttons linked to cell F3. Cell F3 shows a three since the third option button has been selected.

image

But I’m not in love with option buttons mostly because I can’t really do a whole lot of formatting on them. Want a larger font? Tough. Want to make that circle bigger? TOO BAD. Plus, there’s the fact that they remind me of punch card voting ballots. They just look old. (See Chapter 5 of Advanced Excel Essentials for why I also despise ActiveX controls.)

So here’s an alternative. I like to use shapes instead. In the following image, I’ve replaced the option buttons with shapes and have assigned a macro to each of them.

image

I know what you’re thinking. Using form control option buttons is super easy; the method I’m presenting requires macros and named ranges. Why make things more complicated?

Well, it’s all about tradeoffs, isn’t it? This method is actually surprisingly easy to implement. I believe the small amount of code required is a good trade off for having more control over layout and format. And, if nothing else, I present new options to you, the developer. But mostly, it’s just super easy to implement. Once you see how this construction works, you’ll also see there are many other applications for it, most of which cannot be so easily replicated with form controls.

The Basics

The first step is to treat your shapes as a series. That means each shape is part of a range of options. Take a look at what I’ve done below. The name of the button with the caption “Option 1,” is simply Option1, “Option 2” is named Option2, and so forth. For this example, I’ve used captions like “Option 1” to represent the first button, also named Option1. But these buttons can have whatever caption you’d like. What important for this dynamic is that the buttons share a consistent identity. They could just as easily be named Button1, Button2, and Button3.

image

In addition, I’ve named cell E2 LinkedCell for easy access within our code (see below).

image

Once the buttons have been laid out, they  can be assigned to a macro. For this example, I’ll assume you want to assign them to separate macros. But as you’ll see in the code, this dynamic easily lends itself to having each button go to one centralized macro. (We’ll go through a centralized “handler” macro design in the next section.) Assuming you’re working with with three different macros, the could for the three option buttons would be as follows.

Public Sub Option1Selected()
    [LinkedCell].Value = 1
    ' Do some stuff here
    UpdateDisplayOfSelectedOption
End Sub

Public Sub Option2Selected()
    [LinkedCell].Value = 2
    ' Do some stuff here
    UpdateDisplayOfSelectedOption
End Sub

Public Sub Option3Selected()
    [LinkedCell].Value = 3
    ' Do some stuff here
    UpdateDisplayOfSelectedOption
End Sub

Each of these buttons does essentially the same thing: they write the number applied to the suffix of each button to the spreadsheet. Notice this behaves exactly like form controls. In the last line of each procedure, I call UpdateDisplayOfSelectedOption.The code for this procedure is as follows:

Public Sub UpdateDisplayOfSelectedOption()

    Dim CurrentIndex As Integer
    Dim LinkedCellIndex As Integer

    LinkedCellIndex = [LinkedCell].Value

    For CurrentIndex = 1 To 3
        With Me.Shapes("Option" & CurrentIndex)
            If CurrentIndex <> LinkedCellIndex Then
                .Fill.ForeColor.RGB = RGB(242, 242, 242)
            Else
                .Fill.ForeColor.RGB = RGB(248, 203, 173)
            End If
        End With
    Next

End Sub

The code here is pretty straightforward. We set up an iterator (CurrentIndex) to go through all shapes beginning with the name “Option.” If the CurrentIndex equals the LinkedCell, we know we’re dealing with the cell that was selected, so we’ll give it a special color to set it off. Everything else will be the same color.

Leveling Up

The basic structure has been setup, and we could stop here if we wanted. But I think we can go a little further if only to make our code a little less explicit. Right now, it’s filled with numerical constants, generally considered not the best way to go. We could make one procedure that serves as the clearinghouse for all buttons.

We’ll need to take the added if unfortunate step of keeping track of how many buttons exist on our spreadsheet (shown below as TotalButtons). This added step might feel like an annoyance at first, but you’ll see it actually helps in keeping track of your work as it grows. As you add or remove a buttons in your development process you’ll simply type in the updated count into the cell.

image

Now let’s take a look at the code updates.

Option Explicit
Const LIGHT_GREY = 15921906 ' The result of RGB(242, 242, 242)
Const PEACH = 11389944 'The result of RGB(248, 203, 173)

Public Sub ButtonHandler()

    Dim CurrentIndex As Integer
    CurrentIndex = CInt(Replace(Application.Caller, "Option", ""))
    [LinkedCell].Value = CurrentIndex

    Select Case CurrentIndex
    Case 1:
    '...
    Case 2:
    '...
    Case 3:
    '...
    End Select

    UpdateDisplayOfSelectedOption
End Sub

Public Sub UpdateDisplayOfSelectedOption()

    Dim CurrentIndex As Integer
    Dim LinkedCellIndex As Integer

    LinkedCellIndex = [LinkedCell].Value

    For CurrentIndex = 1 To [TotalButtons].Value
        With Me.Shapes("Option" & CurrentIndex)
            If CurrentIndex <> LinkedCellIndex Then
                If Not .Fill.ForeColor.RGB = LIGHT_GREY Then .Fill.ForeColor.RGB = LIGHT_GREY
            Else
                If Not .Fill.ForeColor.RGB = PEACH Then .Fill.ForeColor.RGB = PEACH
            End If
        End With
    Next

End Sub

Notice we’ve replaced the separate procedures with one procedure. Application.Caller returns the name of the button that called the procedure. So if it’s called by clicking on Option1, then Application.Caller will return Option1. Since we know all potential buttons in our series start with the name “Option,” we can use the Replace function to replace the term “option” with nothing. This will just leave us with index of the current option selected. I include CINT() to convert the resulting string to an integer, but I don’t think it’s truly necessary. I think Excel will successfully make the conversion on its own. But it’s not a bad idea to remind yourself what’s happening in the code by including it.

Two small enhancements were made to the UpdateDisplayOfSelectedOption procedure. First, the RGB functions were replaced with named constants. Second, we test if the button is already set to a specific color before setting it to the same color again. This technique was described in my favorite book Professional Excel Development.  Consider how testing properties first might prove useful. In every instance, there’s only one button that needs to be recolored—it’s the prior selected button. So instead of recoloring every single button, we only recolor the last button selected. This means we don’t have to keep track of the previously clicked button even if that’s all that needs to be changed. This speed benefit for checking a property before changing it can be significant in larger applications.

Discussion

This may seem like a trivial example, but consider how the dynamic can be used. I use a similar dynamic in a previous article, A Dynamically Linked Checkbox Matrix with Microsoft Excel. I’ve also used similar mechanisms in my new book, Dashboards for Excel.

What did you think? What ideas do you have for creating better looking form controls?

 

You can download the file used in this article by clicking this link.

“Everyday” Random Shuffling with Formulas

In this article, we’ll talk about how to complete random shuffling with only formulas. Shuffling methods via VBA or a dime a dozen. But it’s much harder to perform a formula-based random shuffle since formulas don’t really allow for iteration. Non-VBA methods therefore require we think about these problems differently (what I describe in Dashboards as “Thinking Outside the Cell”). I call this the “everyday” method because it’s technically imperfect. From  a statistical standpoint, the results are not uniformly distributed. That means the method isn’t perfectly random at all times. We’ll attend to that later in the article.

Step 1. Lay it all out

For now, let’s take a look at a spreadsheet showing our deck of cards (the Excel file is available for download at the end of this article).

image

Though it’s slightly cut off in the picture, there is a complete 52-card deck.

Step 2. Add Some Randomness

Because this is a shuffle, we need to add some randomness to reorganize the cards in the deck. Perhaps the easiest method to do this would be to use RANDBETWEEN. Here, we’ll use =RANDBETWEEN(1,52) since there are 52 cards in total. In the next image, I’ve dragged this formula down so that it’s used 52 times.

image

Anyone who has attempted shuffling in Excel before should immediately realize the problem with using RANDBETWEEN as is. First, there are no guarantees RANDBETWEEN will produce every single number between 1 and 52. Second, there are no guarantees RANDBEETWEEN won’t produce a series of repeated numbers. In the image above, the cells highlighted in peach are duplicates! Clearly, our method needs a few more steps!

Step 3. Add Some Noise

The way we solve both issues presented above is by adding noise. Noise is a small amount of “error” we add to add each randomly generated number. This small amount of error will ensure we also produce a unique list of random numbers. In the image below, I’ve added a small amount to each random number. The small amount is essentially the current card index divided by an arbitrarily large number. As you know, small numbers divided by big numbers produce very small numbers. Using the card index ensures the small amount of noise added is always different, since no cards have the same index. Alternatively, I could have used the volatile function ROW instead of the card index.

If you look at rows 11, 18 and 22 in the picture, you’ll see the RANDBETWEEN resulted in a 50 for each. But because we’ve added some noise, each item has a different resulting value.

image

Step 4. Re-RANK ‘em

What we need to do now is to transform the values produced into numbers between 1 and 52. We can do this using the RANK function. Since each random number is now unique, we know that each number must have a unique rank within the entire range of random numbers produced. For instance if we were just to consider the first three numbers produced in the image above–21.00002, 49.00003 and 28.00004–these numbers, when ranked in descending largest-to-smallest order would yield rank results of 3 (21.0002 is the lowest numbers on the list), 1 (49.00003 is the greatest), and 2 (28.0004 is in the middle). We’ll do this RANKing across all cards (shown below).

image

Step 5. Use INDEX to Retrieve the Results

If our list of random numbers is unique then the their ranks must also be unique. So we’ve now addressed the two issues described above. We now have a resulting list of numbers that fully spans the range of integer numbers from 1 to 52. The last step is to use this resulting list as an index back onto the original card set.

image

And that’s it! You now have a shuffled deck.

Discussion

This setup was likely easier than you though to build. However, it does come with some unfortunate tradeoffs. The biggest trade off is that it’s not perfectly random. Here’s what I mean: let’s say two random numbers have a collision (i.e. the same random number is picked between them). In the image above, rows 7 and 14 collide at random number 42. We’ve seemingly resolved this issue by making each cell unique. However, because we’re using RANK, the item in row 7 will always be closer to the top of the deck than the item in row 14. This is because the noise is not randomly generated. Collisions in this method will always give sorting preference to the numbers that collide first. If we ran this random sorter a few thousand times, the resulting frequency of each card would demonstrate the sort order is not completely random.

The easy away around this is to have the random numbers collide less often. We use RANDBETWEEN(1,52) in this example because it makes thinking about the problem more approachable. But, since we’re checking the rank of unique numbers in a list, the mathematics do not dictate the random numbers be in any specific range. In fact, we could just as easily replace the random number generating part of the formula to something like RAND()*10000. Now that the range of possible numbers is bigger, it’s less likely they’ll be a collision in our shuffle. However, if we’re thinking broadly about shuffling items, the more items you have to shuffle the more likely they’ll be collisions. That’s just because the potential for collisions increases with each new random number generated.

image

Alternatively, you could implement a Fisher-Yates shuffle. This method will always generate random numbers, and it is indeed possible to use only formulas to implement. However, it’s a bit more complicated and perhaps best served as an article for another day (but you’re welcome to try it on your own). Until then, this method will suffice for most demonstrations.

Download the worksheet used for this article: Unique Random Sorting

Custom Formats for Dashboards and Spreadsheet Applications

Conditional formats are volatile. If you have too many, you see the effects of this volatility in serious calculations slowdown. As the function guru Charles Williams puts it, “…conditional formats seem to be super-volatile: they are evaluated each time the cell that contains them is repainted on the screen, even in Manual calculation mode….” But even too many non volatile functions can slow things down.

The good news is you have alternatives. For some specific instances you can get away with an Excel feature called Custom Formats, which is faster than conditional formatting and non volatile. It can also replace the IF and other conditional formulas.

However, you’ll deal with some major trade-offs. Custom formats as you’ll see can be confusing. And, because they’re not commonly used, others may not understand the work you’ve completed — and you yourself might even forget since custom format code is obscured away from the spreadsheet in a dialog box.

We’ll talk about those trade-offs and go through a few examples in this article.

Introduction to Custom Formats

Excel comes with an array of built-in custom formats. For instance, in the image below you can see I’ve placed a simple date on the worksheet. The representation of the date as month/date/year is in fact a custom format that Excel applies by default. You can see the format applied to any cell by selecting the cell and selecting More Number Formats from the format dropdown.

image

The image below shows custom code applied to the date on the spreadsheet. Notice initials like d and m stand in for things like day and month. These are part of the custom format codes that can be used to represent dates.

image

Conditional Expressions with Custom Formats

It’s the combination of these letters, numbers, and codes that allow you to make all sorts of customized formats. The codes in the previous image feature no conditional expressions; however, you can write conditions as formatting codes. The basic prototype for conditions is as follows: [Condition 1] ; [Condition 2]; [Else Condition]

Let’s say you apply the following condition code to a cell: [>10] “high”; [>5] “medium”; “low”.  The image below shows this code applied to the cells in column C. Column B simply equals the values in column C. Because the custom format is applied to column C, the values appear as the text we’ve outline in our formatting code. In C3, the value is 15; our custom format code tells Excel to represent this number as  “high.”

image

Notice cell B5 is simply set to be equal to cell C5 with a formula. In fact, all the cells shown in column B simply refer to the adjacent cells in C. The custom format acts as a mask for the actual underlying values. If you were to write =C3+5, the result value would be 20 (assuming you’ve switched the new cell to a General format) even as what’s being shown in C3 is text and not a value. Dates work in the same way: Excel stores dates as discrete whole numbers but they’re represented in a common date format. Custom formats only change how values are represented on a the spreadsheet; they does not affect underlying numerical values.

Custom Formats for Dashboards

Let’s apply what we’ve learned to a dashboard. Let’s say you want to create an alert where a colored dot character, ●, is shown next to over-budget project. The image below shows this effect. You would like a softer orange alert for projects $10,000 over budget and a harsher orange for projects even more over budget than that. The following image shows this effect achieved through custom formats.

image

To create this on your own, you would first figure out which projects are over budget in Column A. The image below shows we can do this easily by subtracting the amount spent from the budgeted amount.

image

Once this calculation is completed we can apply the following custom format to column A:

[Color46][>20000]●;[Color45][>=10000]●;[Color2]

So let’s break this custom format down. First, you might be wondering how to get the dot character. Well, you can copy and paste it from in this blog post. (That’s probably the easiest way!) Alternatively, you can insert it (or any other character) by going to the Insert tab and selecting Symbol. The dot resides in the Geometric Shapes subset. I usually insert the character to a random cell and then copy it from there, pasting it into the custom format as needed.

The next item to point out is the [ColorXX] code. Excel allows you to change the font colors depending upon the conditions satisfied. The numbers 45, 46, and 2 refer to color codes within Excel’s internal colors. Unfortunately, there are only 56 colors available, which is admittedly very constraining.  Click here to see the entire color list. (You might want to bookmark this link so you have the color table handy at all time.) For reference, Colors 46 and 45 are the dark and light orange respectively; Color2 is white.

Once you have the values in Column A as shown above, it’s a simple matter of selecting the values and applying the custom format. This is shown below with some resizing of column A to reflect how I want to see the dots. I’ve also increased the font size. Notice the custom format dropdown box on the ribbon now says “Custom” to reflect the custom format code we’ve created and applied.

image

Discussion

There’s much more to custom formats. For instance, the [>10] “high”; [>5] “medium”; “low” code from above could replace the following nested IF formula =IF(A1 > 10, “high”, IF(A1 > 5, “medium”, “low”)). The greatest advantage to custom formats however is that you can dynamically change how values are represented on the spreadsheet without the weight of additional calculation and volatility. Excel can instantly change how it represents an underlying value much faster than it can recalculate.

But as you can see custom formats are also pretty limited in formatting options; they’re sometimes hard to understand; and how they work isn’t always obvious (their code is hidden away in a dialog box). Indeed, for ninety-five percent of cases, the difference in calculation speed of an IF formula and the dynamic representation of a custom format is nominal.

Still custom formats present many interesting options for developers. For dashboards and models featuring heavy calculation, they prove to be a useful alternative.

Dashboards for Excel

My book Dashboards for Excel uses examples like this and so much more. Buy it today! (The book is out and shipping. Amazon is fixing the listing.)