Author Archives: Jordan G

Happy Holidays 2015

OK, so I know a few days ago I said I had written my last blog posted before the end of the holiday. But then I’ve released holiday cards in both 2013 and 2014, so this year should be no different. So I went with a topical Star Wars theme. The text being shown is  an abridged version of the introduction chapter in Dashboards for Excel.

Recording #3

Speaking of which, if you haven’t gotten your copy yet, you should. The book currently has a 4 out-of 5 on Amazon, with the most recent reviews being the most complimentary. (And I swear, I don’t know the people writing these reviews.) Aside from some complaints about the book not being in color, the book is doing well. For what it’s worth, I inquired about color printing with my publisher long before it was printed, but most tech-trade books are not published in color and the few that are already have a strong history of sales and success. (But also: don’t stop asking! If enough people ask for a color imprint, I can make a stronger case to my publisher for the second edition!) Remember the eBook versions are in color too, if that’s your thing. And, of course, I have all the print materials at the ready, so you can always ask me directly if you have trouble understanding anything.

Download File: Happy Holidays 2015.xlsm

image

Remember to hit the download icon from within Google Drive to get the file.

If you want to change the Star-Wars-text for your own application (aren’t there like a million business uses for this?!?!), simply click the worksheet tab called “Backend” and type new text into the textbox.

Thanks to all of you for a wonderful year. Next year will be even better!

Jordan

Advertisements

Advanced Excel Essentials for $10

EDIT: Unfortunately, the deal has ended.

I’m writing this from my car on my way to visit family so forgive the brevity and potential spelling errors. Thanks to fellow Apress author, Debra of Contextures.com, for letting me know! You can get my first book in ebook/pdf format for $10.00. Follow this link to get your copy: http://www.apress.com/9781484207352

 

Enlarging Form Control Option Buttons

In a previous tip, I had complained about form control option buttons. I had argued they suffer because you can’t change the font size nor the “punch circle.” But then an idea dawned on me that builds off of that previous tip. We could use the same dynamic described in that earlier article and combine it with the camera tool.

Click here to learn more about the Camera tool.

Say Cheese!

Let’s take a look. On the left side, I’ve create a button list. This is just a place to store my option buttons. Notice I’ve placed them within a single cell each—this makes it easy to take a snapshot with the camera tool (since the camera will always only reference one cell).  The image below shows how  this works. Notice option buttons are all linked to cell B8, which I’ve named “SelectedIndex.”

image

Again, following my previous tip, we can assign each image created by the camera tool to a macro. That macro would change the Selected Index depending upon which button was selected

Click here to read that previous tip to understand it in full.

Let’s take a look at that macro:

Public Sub OptionButtonHandler()
    Dim Index As Integer
    Index = CInt(Replace(Application.Caller, "Button", ""))
    [SelectedIndex].Value = Index
    [SelectedIndex].Calculate
End Sub

You maybe wondering about that last line in the subroutine. Camera tool images can be a little funky. In the previous screenshot, you can see that these option buttons images are on the same tab as their form control counterparts. Presumably, you’ll place the option button images on a different worksheet tab and the original button list will live on a hidden tab. For whatever reason, whenever you place these form control images on another tab, the image won’t always sync with the right value. So making a call to recalculate will ensure that it does. The download file provided at the end of this post demonstrates a setup where the images are on a different tab.

Note: I’ve only noticed this problem in Excel 2010. Excel 2013+ does not seem to have this issue requiring a call to calculate. I’ve included it here so it’s backward compatible with previous versions of Excel. It’s also a bit slower, so feel free to remove it completely if it’s not an issue on your end. 

Discussion

Look, I’m probably not going to use these buttons anytime soon. I’m not saying you shouldn’t, but I don’t have a lot of use for them. I’ll still prefer my method of using shapes. However, the camera tool when combined with my earlier tip does allow you to create functionality not native to Excel. For instance, if you type a new font in one of the cells on the button list, you can even change the format of the radio button in the way it’s presented. In the image below, I’ve deleted the original caption to the option button. In its place I wrote another caption into the cell and picked a cheesy font. Yes, it doesn’t look great, but you can play with the formats and make it look better. This should give you an idea of what’s available.

image

My hope with this article is that we keep the creative juices flowing and see what other novel ways it can be applied.  Again, I probably won’t use this, but hey, maybe you will, and that’s enough for me.

I’m going to Dublin, Ireland

download

From December 23-30, I’ll be in Dublin, Ireland on holiday with my wife. If you are in the Dublin area and would like to say hi, let me know! Otherwise, this is likely my last blog post before the end of the year, so have happy holiday and terrific new year!

Download file: Large Option Buttons.xlsm

Also, buy my new book! Makes a great stocking stuffer!

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