PTS Blog

There are two types of controls for use in Excel worksheets: Forms controls and ActiveX Controls. The Forms controls aren’t very complicated, and they have been part of Excel for longer (I’m thinking Excel 4, and they were used in Excel 5/95’s dialog sheets) than the Controls Toolbox (Excel 97), so it stands to reason that they’d be more seamlessly integrated. Being newer, the ActiveX controls have richer formatting possibilities. Both can link to cells and ranges in the worksheet. Some advanced users have attributed buggy behavior to the ActiveX controls from the Controls Toolbox. I’ve recently had problems with a buggy slider from the Forms toolbar, but I suspect the parent workbook was corrupt.

There are outward similarities, mostly in appearance, between Excel’s Forms controls and ActiveX controls. The ActiveX controls are actually more similar to the ActiveX controls available within VBA for use on UserForms. In Classic Excel and VBA, Forms and ActiveX controls are available on toolbars, while in Excel 2007, they’ve been moved to the Developer tab of the Ribbon.

Forms Controls

ActiveX Controls

VBA Controls

Forms and ActiveX Controls in Excel 2003

Read more »

Ganesh, a reader of my web site, asked whether I could make a chart like this one:

The series are shown as a fluctuating blue Value line and a steadily increasing red Limit line in the line chart below, but Ganesh wanted to color code regions in the chart, so that regions where the value exceeded the limit were shown in green, and regions where the value fell short of the limit were shown in yellow.

The data (Dates, Value, and Limit) are shown below with some calculated columns. These will be described as we need them. Read more »

As part of the development of Excel 2007, Microsoft completely rebuilt the Excel charting engine. During overall development of the Office 2007 user interface, all of the charting dialogs were redesigned, and many features were added to the Chart Tools contextual tabs of the Ribbon. Office 2007 has been out now for well over a year, and I’ve been using it in some capacity since the beta kicked off over a year before its commercial release. I’ve decided it’s about time I gave a coherent review, rather than post bits and pieces in various forums. I hope that after this much time has gone by, my comments will be seen as the well-thought analysis I’ve tried to make it, and not a knee-jerk Luddite reflex.

Perhaps this assessment will help converts to Excel 2007 who are having difficulty with the changed charting environment, and hopefully any criticisms and suggestions will be taken to heart by developers of future releases of Excel. There is also a companion post reviewing general changes between Classic Excel and Excel 2007.

Read more »

Someone asked in the Microsoft Excel Charting newsgroup, “How can I put four pie charts in a printed page, but only have one legend?” Well, the answer to that question has three parts.

1. Use a single legend for multiple charts.
2. Use data labels.
3. Don’t use pie charts.

I’ll address these answers in the order stated above, even though the most important part is the last.
Read more »

From xkcd.com, “A webcomic of romance, sarcasm, math, and language.”

Stove Ownership

I feel a Homer moment coming on… Mmmmm, bacon.

Introduction to Control Charts

While employed as a scientist/engineer for a large manufacturing corporation, I pulled a stint as trainer for my company’s Total Quality program. This role occupied half of my time for three or four years. A large component of our Total Quality initiative was related to Statistical Process Control (SPC). And of all the volumes of course materials used in this training, the most useful was a small, 136-page book called Understanding Variation.

Understanding Variation: The Key to Managing Chaos
Donald J. Wheeler

What made the book so useful was its down-to-earth approach to SPC and its reliance on real business and manufacturing examples. Read more »

Statistical Process Control (SPC) is a set of statistical and related methods for monitoring processes with an aim to improve productivity and reduce costs, time, and waste incurred by these processes. In fact, SPC is a philosophy surrounding the monitoring, analysis, and adjustment of process variables to produce continuous improvements in the process.

There are a lot of terms that mean SPC, or that are a part of SPC. I plan to address some of these topics in more detail in future posts.

• Process Control
• Process Capability
• Quality Assurance
• Quality Control
• Total Quality Management
• Reliability Engineering
• Six Sigma
• Lean Manufacturing
• Continuous Improvement

Statistical tools commonly used in SPC include the following (with links to pages on the Peltier Tech web site):

• Run Charts (Control Charts)
• Box and Whisker Charts
• Probability Plots
• Histograms
• Pareto Charts
• Scatter Plots
• Regression Analysis
• Analysis of Variance

I will discuss several of these charting techniques in future posts.

Here are a few online resources you can visit to learn more about SPC.

• National Institute of Standards and Technology
  NIST/SEMATECH e-Handbook of Statistical Methods
  Process Control Techniques
  
• Business Process Improvement
  SPC Articles and Newsletter
  SPC for Excel Software
  
• Wikipedia
  Statistical Process Control on Wikipedia
  

Stay tuned for my follow-up posts on SPC. I will build a running index on this page as I add topics.

Excel is a mighty powerful application, and yet, there are any number of utilities available to extend its capabilities. I’m sure everyone has their favorites, and here I’m going to talk about mine. My reliance on these utilities is as a developer of Excel applications, that is, solutions that combine add-ins, templates, and regular workbook to accomplish specific objectives within specific operating parameters. My favorite utilities lean toward developer tools, rather than worksheet productivity tools. This is an incomplete list: there must be hundreds of worthy utilities written by dozens of developers, but these are the ones I have tried and kept. If you think I’ve left out an important utility, tell me about it in the comments. Read more »

In Using Pivot Table Data for a Chart with a Dual Category Axis, I showed how to make a Chart with a Dual Category Axis using a pivot table to properly arrange the source data. I generally prefer using a regular chart, because pivot charts are pretty inflexible when it comes to formatting. Unfortunately, a regular chart doesn’t know when the pivot table has been resized by a refresh. In Dynamic Chart using Pivot Table and Range Names I showed how to use dynamic ranges to allow a regular chart to update when the pivot table changes. However, that technique only accommodates a change in the number of points in a series, not the number of series in the chart. To allow for changing height and width of a pivot table, you either need to revert to a pivot chart, or use a VBA procedure, which I describe below.

This example begins with the same data range, pivot table, and chart as the previous ones. Read more »

In Using Pivot Table Data for a Chart with a Dual Category Axis, I showed how to make a Chart with a Dual Category Axis using a pivot table to properly arrange the source data. I generally prefer using a regular chart, because pivot charts are pretty inflexible when it comes to formatting. Unfortunately, a regular chart doesn’t know when the pivot table has been resized by a refresh. If you know that the pivot table will only grow in one direction, that is, the number of series remains the same but the number of points in a series may change, you can define dynamic ranges that stretch with the pivot table.

Read more »

Tony commented on the previous post, Chart with a Dual Category Axis, asking whether I’d use a pareto chart for this data. I commented that it almost was a pareto chart, since at least within each category the data is sorted from high to low. Then I got to thinking, if I put my data into a table, and use a pivot table to sort both the main category and sub category by number of defects, I would essentially have a pareto chart.

A pareto chart is a column or bar chart that lists items in decreasing order of their occurrence. A pareto chart may include a line chart series showing the cumulative occurrence of the items. The left-most items are most prevalent, so if your pareto chart shows instances of failure modes, you start fixing the left-most failure modes first.

Read more »

Through appropriate arrangement of your source data, you can give your chart a dual category axis. This approach works with chart types that have an “Category” type category (X) axis, that is. line charts, column charts, and bar charts. The chart below shows defect rates in several different components, which are grouped into a smaller number of categories.

Read more »