Jon’s Toolbox is a simple Excel VBA add-in that provides a handful of useful functions. I use it when I am preparing or teaching classes, writing blog posts, and working on projects.
Jon’s Toolbox is not a replacement for Peltier Tech Charts for Excel, nor does it require Peltier Tech Charts for Excel in order to operate properly. The two utilities run independently, and I have both installed on each of my computers.
Click Below to Get Jon’s Toolbox
Jon’s Toolbox is available for $35. Click this button to make your purchase, or scroll down to read about its features.
Check your shopping cart with this button.
What Jon’s Toolbox Does
I’ll do a quick run-through of the menu controls to give you a sense of what Jon’s Toolbox is all about. Peltier Tech Charts for Excel is a large collection of advanced features, for creating custom chart types which are not native to Excel, for manipulating charts, and for controlling data that charts are linked to. Jon’s Toolbox is a self-contained set of functions that help with quickly inserting data and charts, and with quickly formatting charts and worksheets.
Jon’s Toolbox Ribbon Commands
This is a quick look at the custom ribbon tab of Jon’s Toolbox. Click on the image to see it full size in a new browser tab.
The ribbon includes groups of controls, such as Insert Data – Chart – Pivot, Chart and Font Size, Chart Tools, Range Tools, Range and Chart, plus a teaser at the end, From the Blog.
Insert Data – Chart – Pivot
Insert Data – Chart – Pivot has controls to quickly insert data, charts, or a whole data-pivot table-pivot chart ensemble. This is nice when I’m running a demo of my other software, preparing for a class, writing documentation or a blog post. It’s especially nice if I’m running a class and I want to demo a feature of Excel and I want some data for a chart quickly, and I don’t want to make everyone watch me mistype a bunch of numbers.
Data Range
Insert Data Range inserts a handy dummy data set, ready for me to demonstrate Excel’s features, or the features of some software I’ve written. If the active sheet is blank, it puts the data there, otherwise it inserts the data onto a clean worksheet.
Nothing fancy, but it’s a good simple data range for demonstration purposes.
Column Chart, Bar Chart, etc.
There are chart buttons for all of the chart types I routinely use. Yes, it includes pie charts, get over it.
Each of the buttons is a split button. I’ll illustrate the functionality with the Column Chart button.
If you click on the icon (left), the program creates the chart type shown. If you click on the dropdown (middle), you get a dropdown list with the options for that chart type (right).
When you use one of the options from the dropdown, the split button icon changes to that chart type, which is created next time you click on the icon.
The way a chart is inserted varies, depending on what is selected.
- If no data is selected, the program inserts a new worksheet, it inserts the dummy data range (see Data Range above), and it inserts a chart one row below the data range using this data.
- If a data range is selected, or if a cell in a block of data is selected, a new chart of the selected type is inserted, one row below the data range.
- If a chart is selected, the program inserts a new chart of the selected type, offset slightly from the original chart, using the same data as the previously active chart.
The charts created here are essentially the same as those from Excel’s own Insert tab, the same size and same series formatting, but they follow my own preferences for that chart type.
For example. Excel’s column charts have too much space between clusters, and unneeded space between columns in a cluster. Jon’s Toolbox fixes this.
Excel’s bar charts draw the axis in the opposite order of how the data appears in the worksheet. Jon’s Toolbox fixes this, while also changing to an aspect ratio that better accommodates the chart.
In addition to being spelled with too many silent letters, Excel’s doughnut charts have too fat a white line surrounding the individual points, the default hole size of 75% is ridiculously large, and the legend is placed below the chart, shrinking the doughnut’s height and leaving wide swaths of white space on either side of the doughnut. Jon’s Toolbox creates a donut chart spelled with no excessive letters; with a thin white outline around the points; with a default hole size of 66% for one ring (one series), 50% for two rings, and 33% for three or more rings; and with its legend on the right side of the chart, allowing the donut to fill more of the space occupied by the chart. In addition, Jon’s chart is closer to square, which means the chart takes up less space.
Data, Table, and Chart
Data, Table, and Chart inserts a new worksheet. The worksheet contains a Table of data, a Pivot Table constructed using the data in this Table, and a Pivot Chart based on this Pivot Table. Click on the screenshot to view in a new window.
This is a great way to quickly show someone about Tables and Pivot Tables and Charts.
Chart and Font Size
Are the default charts in Excel the right size for your purposes? In the US, the defaults are 5 inches wide and 3 inches tall, and frankly, that’s way larger than most of my charts need to be. Also, that 14-point chart title is much too large.
The Chart and Font Size group of controls provides the following predefined set of alternative sizes for charts and their fonts.
The different chart and font sizes look something like this. Note that they are independently applied to each chart. For my blog posts, I can easily fit two of the small charts, usually with medium font, side by side within a column of text.
Like many of the features of Jon’s Toolbox, if you select multiple charts before clicking the button, the formatting is applied to all selected charts.
Chart Tools
Jon’s Toolbox has a handful of useful chart tools.
The default legend in an Excel chart is pretty widely spaced out (below left). Condense Legend closes it up (below right).
You can easily Reverse Series (and then unreverse them) in a chart, for example in a clustered column chart…
…or in a stacked column chart.
By default, Excel plots a bar chart with the vertical axis in exactly the opposite order as the data appears in the worksheet. This makes sense on some level, but not from the standpoint of usability. Click Flip Bar Chart and fix your bar charts instantly. (Click it again to get the upside-down version back.)
Note that if you use Jon’s Toolbox to insert a bar chart, it is automatically plotted the right way.
The default Excel color palette is not too bad, but if large areas of your chart are filled with the colors, the chart may seem oversaturated (below left). In Excel’s color pickers, the next lighter shade is 40% brighter, which may make the charts washed out. Apply 20% Brightness applies an intermediate shade to the selected series (below right). Note that you have to select and format each series separately.
Finally, the Copy Bitmap button copies the active chart to the clipboard in bitmap format, saving you several clicks (Home tab > Copy dropdown > Copy as Picture > select Appearance and Format). Simply switch to PowerPoint or your image editing software and Paste.
Range Tools
Jon’s Toolbox contains a set of range formatting tools.
(Format as) Chart Data
When you select a chart or a series in a chart, Excel highlights the associated data in the worksheet.
Chart Data applies this formatting more permanently to the worksheet. The colors are the same, it only lacks the small square handles on the corners of the shaded regions. Great for tutorials and illustrations, though.
Click the Chart Data icon, and different things happen depending on what is selected.
- If a chart is selected, the data range for the whole chart is formatted.
- If a series in a chart is selected, the data range for that series is formatted.
- If a range is selected, the range is formatted as if it were used in a chart. If more columns that rows are selected, the range is formatted as if series are plotted by row, otherwise it is formatted as if series are plotted by column.
If you need more control over the formatting, click the Chart Data dropdown.
You can format a range including X and Y values and series names as if plotted by row or by column. You can format a range as if it contains only series names, X values, or Y values.
In addition, you can format a range so it looks like a selected range (below left), as a copied range (below center), or by clicking the two buttons in succession, as a selected range that’s been copied (below right).
I have found these very useful when writing blog posts, class notes, or documentation for my software.
Borders
Excel has an extensive built-in Borders menu.
And the borders it creates are stark, in-your-face dark black. Sometimes I even want to pluck out my own eyeballs. (My daughter once determined that any statement is funnier if you say “eyeballs” than if you just say “eyes”, and I agree. She’s a scientist working on visual perception using primates.)
Below are Outside Borders, Thick Outside Borders, All Borders, and a combination of All Borders and Thick Outside Borders. Pretty harsh.
Nice Borders applies more visually appealing borders to a selected range. Medium is a medium gray, darker than the default cell boundaries, but not as irritating as black, while light is a light gray similar to the default cell boundary color. “Nice” borders means a medium gray outline with light gray internal boundaries, and the other options format internal borders and outlines, or just outlines, with medium or light gray borders.
These are illustrated below with cell gridlines (Gridlines on the View tab) displayed on the left and turned off on the right.
Medium Borders applies medium gray borders to each cell in the selection.
Medium Outline applies a medium gray border around the outer cell boundaries of the selection.
Light Borders applies light gray borders to each cell in the selection. The light gray is indistinguishable from the default cell gridlines. I use this to put gridlines only on a certain range, while turning off gridlines elsewhere.
Light Outline applies the light gray border around the outer cell boundaries of the selection.
Nice Borders applies a medium gray outline around the selection and light gray borders to cell boundaries within the selection.
If I am making screenshots for documentation, I will often either hide the default gridlines and use Light Borders (first screenshot below), or I will show the gridlines and use Nice Borders (second screenshot below).
When you select one of these Border options, it becomes the new icon and default action for the command.
Clear Formats
Clear All Formats will clear all formats, except for number formatting, from a selected range. This is nice, because sometimes you need to get rid of some ugly border and fill colors, but recreating number formats is a chore.
In the Clear All Formats dropdown, the options are to
- Clear border formatting,
- Clear border and fill formatting,
- Clear all formatting except for number formatting.
Page Breaks
Hide Page Breaks simply removes those unsightly page break dashed lines that make me crazy.
Range and Chart
The Range and Chart group has some controls that help you control how the chart interacts with the worksheet.
Select Data allows you to quickly select a new source data range for the active chart.
Cover with Chart lets you select a range for the active chart to cover. The chart is repositioned and resized to cover the selected range.
Stretch Chart resizes the active chart (or all selected charts) to fill the cells it partially covers.
Center Chart moves the active chart (or all selected charts) so it is centered within the range of cells it covers.
From the Blog
Finally, Jon’s Toolbox includes some nice little programs that were previously presented in the Peltier Tech Blog.
These programs were featured in the following blog posts:
- Add Totals to Stacked Column Chart (but it works on stacked bar, stacked area, and stacked line charts, too)
- Trendline Calculator for Multiple Series
- Built In and Custom Lists in Excel
Get Jon’s Toolbox Here
Jon’s Toolbox is available for $35. Click this button to make your purchase.
Check your shopping cart with this button.
Bulk Discount
If you purchase multiple licenses, you qualify for a volume discount, shown in the table below. The discount is automatically applied in the shopping cart when you enter your quantity.
Guarantee
Like all of Peltier Tech’s software products, Jon’s Toolbox is guaranteed for 90 days from the date of purchase. If you are unsatisfied for any reason, contact Peltier Tech directly to request a refund. Any feedback is appreciated, but none is necessary.
License
Each user of Jon’s Toolbox requires a license. Each license allows the user to install Jon’s Toolbox on up to two computers of which they are the primary user. If a user gets a new computer, the software can be uninstalled from the old machine and reinstalled onto the new one.
Installation
Jon’s Toolbox is a simple Excel VBA add-in, which can be installed in Excel for Windows or Excel for Mac. Follow instructions at Install an Excel Add-In, and you’ll be working in no time.
Questions?
Contact Peltier Tech with any questions about Jon’s Toolbox.
TAG says
What’s the difference between this product and the Peltier Tech Charts product?
Jon Peltier says
Hi TAG –
Peltier Tech Charts for Excel 3.0 has more advanced functionality. For example, it can create a wide range of custom charts that either Excel doesn’t offer natively, or the native versions are not very flexible.
Peltier Tech Charts for Excel is comprised of heavy-duty tools for managing chart data and formats, and for exporting charts en masse to various destinations.
Jon’s Toolbox will insert standard Excel charts, quickly, from either a prefab sample data set, or if the active cell is in an existing data range, from that data range. The overlap and gap widths of column charts are more pleasing (to me), the bar charts come out rightside up, pie and donut charts are formatted to make better use of the chart’s real estate.
Jon’s Toolbox contains simpler tools which are geared not towards detailed data analysis but rather for demos, training, and prepublishing.
Michael Hamilton says
Does this work for Mac or just Windows?
Jon Peltier says
Hi Michael –
I’ve designed the add-in to work on Mac and Windows. I built it in Windows, but haven’t used anything known to fail on a Mac. I haven’t tested it extensively on my old MacBook Pro.
Bob says
Hi Jon,
Sorry for the question, but it is early.
Does Jon’s Toolbox require the PT Charts for Excel 3.0 to run, or is it stand-alone?
I don’t require the sophisticated charts the the PT Charts provides, but see the value in the Toolbox.
Thanks
Bob
Jon Peltier says
Hi Bob –
Jon’s Toolbox works side by side with Peltier Tech Charts for Excel, but neither program is required by the other.
Dolf says
Hi Jon,
Just keep em coming your tools and I buy them all as I know it is a big time saver.
Regarding the charts for excel a tutorial how to add in stuff eg a total column in front of a stacked waterfall in the same graph without distorting functioning of the original graph would be helpful. You are top in quite speedy bilateral after sales service, but it would be good to also include it in the tutorial as growing document.
Best regards!
Dolf
Jon Peltier says
Hi Dolf –
I’m not sure what you mean about the total columns in a stacked waterfall, though it sounds familiar. Maybe a few more details would remind me?
Dave says
Pretty nice work, Jon. I built an analytical scatterplot tool that allows the user to specify z-axis discriminators to investigate relationships to other data besides just the x- and y- data. You can then just specify the number of bins of the z-axis you want to view and just draw the regression lines for each bin. I was doing this using macros, so hopefully I can utilize your tool to come up with a better solution.
Jon Peltier says
Dave –
I don’t think anything in the new utility does what you mention, that is, split XY data into separate series based on a third variable. But I’ve used various approaches to do this, and I think I could write some code to do it easily. Stay tuned.
Dave says
Will do John. What I was referring to was moving from macros to something better to do the regression lines for the series separated based on the z-axis bins I specified. It might be okay right now, since I’m trying to build a better scatterplot for a cooler data visualization.
Erdem says
Hey Jon,
How to use “Reverse Series” function exactly? I’m selecting the sample chart in the addin from its borders and clicking on “Reverse Series” button from toolbar. Then getting a “Microsoft Visual Basic Run-time error ‘1004’: Application-defined or object-defined error” msg. box. What am I doing wrong? Thx.
Jon Peltier says
Erdem –
Let me check that out. What kind of chart?
Erdem says
Jon, it’s the sample chart in your add-in when you clicked on “Data, Table and Chart” button.
I just wanted to try the functionality of the “Reverse Series” but got that error msg.
Just selected the chart from its borders and clicked on the function button, if that is the right way..
Jon Peltier says
Erdem –
In general, that’s how the Reverse Series function works. But the sample chart you’ve created with Data, Table, and Chart is a Pivot Chart, and apparently the code I used here does not work in a pivot chart.
In a blank worksheet, click one of the other chart buttons in the Insert Data – Chart – Pivot group, which inserts simple data and a regular chart. Then try Reverse Series again.
It turned out to be easy to make it work for a pivot chart, so it will be in the next release, along with a couple other enhancements.
Erdem says
Well, I didn’t know that as there was no specific exemption for the Pivot Charts in above specs.
Regarding the question if it is worth to implement this feature for the Pivot Charts; well, as the add-in is a commercial product now and not a personal helper anymore, I would say yes to cover all chart types for not to make any exemption in the list (and not to cause VBA errors for the irregular charts) if it will not take too much time of you.
But of course it’s just my two cents.
Thanks anyway for the clarification.
Jon Peltier says
Erdem –
As I said in my edited reply, making it work for a pivot chart is pretty easy, so I’ll put that in the next release.