Jon’s Toolbox

Wednesday, November 25, 2020 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2021, All rights reserved.
 

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.

Get Jon’s Toolbox Here

Jon’s Toolbox is available for $35. Click this button to make your purchase, or scroll down to read about its features.

Add to Cart

Check your shopping cart with this button.

View Cart

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.

Jon's Toolbox Ribbon Controls

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.

Groups of Controls on the Jon's Toolbox Ribbon

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.

Insert Data - Chart - Pivot Tab of Jon's Toolbox Ribbon

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.

Data Range inserts this simple data range into the active sheet or 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.

Column Chart SPlit Button on Insert Tab of Jon's Toolbox Ribbon

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.

MRU Column Chart Subtypes

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.

Comparison Between Column Charts Created by Excel and by Jon's Toolbox.

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.

Comparison Between Bar Charts Created by Excel and by Jon's Toolbox.

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.

Comparison Between Do(ugh)nut Charts Created by Excel and by Jon's Toolbox.

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.

Insert Data Table, Pivot Table, and Pivot Chart

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.

Chart and Font Size Group of Controls

The Chart and Font Size group of controls provides the following predefined set of alternative sizes for charts and their fonts.

Tables of Chart and Font Sizes

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.

Chart and Font Size Examples

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.

Chart Tools Group of Controls

The default legend in an Excel chart is pretty widely spaced out (below left). Condense Legend closes it up (below right).

Uncondensed and Condensed Legends

You can easily Reverse Series (and then unreverse them) in a chart, for example in a clustered column chart…

Reversed Series in a Clustered Column Chart

…or in a stacked column chart.

Reversed Series 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.)

Flip Your Bar Chart

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.

20% Brighter Fill Colors

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.

Range Tools Group of Controls

(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.

Format 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).

Format as Selected, as Copied, or as Selected and Copied

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.

Excel's Borders Dropdown 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.

Built-in Borders (ugh)

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 Borders

Medium Outline applies a medium gray border around the outer cell boundaries of the selection.

Medium Outline

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 Borders

Light Outline applies the light gray border around the outer cell boundaries of the selection.

Light Outline

Nice Borders applies a medium gray outline around the selection and light gray borders to cell boundaries within the selection.

Nice Borders

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).

Light Grid Formatting With Chart for Blog Screenshot
"Nice" Border Formatting With Chart for Worksheet

When you select one of these Border options, it becomes the new icon and default action for the command.

Most Recently Used Borders Commands

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.

Rnage and Chart Group of Controls

Select Data allows you to quickly select a new source data range for the active chart.

Select Chart Source Data Dialog

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.

Select Range for Chart to Cover Dialog

Stretch Chart resizes the active chart (or all selected charts) to fill the cells it partially covers.

Chart Before and After Stretching to Fill Range It Covers

Center Chart moves the active chart (or all selected charts) so it is centered within the range of cells it covers.

Chart Before and After Being Centered Within the Range It Covers

From the Blog

Finally, Jon’s Toolbox includes some nice little programs that were previously presented in the Peltier Tech Blog.

Handy Features From 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

Click Below to Get Jon’s Toolbox

Jon’s Toolbox is available for $35. Click this button to make your purchase.

Add to Cart

Check your shopping cart with this button.

View Cart

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.

Volume discounts on Peltier Tech software.

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.

Version History

History by Build Number and Date

  • Build 74 – 3 January 2021
    • Retire support for Excel 2010
    • Functions now work on shapes as well as charts
      • Center in Underlying Grid
      • Fill Underlying Grid
      • Select Range to Cover
    • Dialog for user to set Custom Chart and Font Size
  • Build 73 – 28 July 2020
    • Reverse Series Order – now works on Pivot Charts
    • Select Chart Data Range – now works on multiple selected charts
    • Select Data button has expanded functionality
    • Added to ribbon
      • Center Active Chart in Underlying Grid
      • Active Chart Fill Underlying Grid
    • Fix Copy as Bitmap for chart sheet
  • Build 72 – 23 January 2020 – First Release

If your build number is outdated, contact Peltier Tech for a link to the new version.

Questions?

Contact Peltier Tech with any questions about Jon’s Toolbox.

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to print (Opens in new window)
  • Click to email this to a friend (Opens in new window)

Posted: Wednesday, November 25th, 2020 under .
Tags: .
Comments: none

Peltier Tech Charts for Excel

Peltier Technical Services, Inc. Copyright © 2021 – All rights reserved.

Admin

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.