Pivot Table Resources

Pivot Table Tutorials

There are numerous articles and tutorials about Pivot Tables on the Peltier Tech blog. This is a nearly complete list of Pivot Table posts.

Explore Your Data With Pivot Tables

Pivot tables are one of Excel’s most outstanding features. They allow fast dynamic and interactive analysis of data from the workbook or from any number of external sources. This tutorial shows the power of pivot tables for fast and detailed analysis.

Preliminary Data Exploration with Excel Pivot Tables

In this tutorial we first normalize the data, then generate easy Marginal Means Plots and Interaction Plots to quickly explore the data.

Making Regular Charts from Pivot Tables

Pivot charts are powerful: they are dynamic, interactive, and can be pivoted. However, sometimes you need something that a regular old chart can do, that pivot charts cannot. This tutorial shows how to create a regular chart from a pivot table.

Create and Update a Chart Using Only Part of a Pivot Table’s Data

This tutorial shows how to use a little VBA to create and maintain a chart that uses only part of a pivot table’s data.

Update Regular Chart when Pivot Table Updates

When a  pivot table is pivoted or updated, it may have different numbers of rows and columns, and a regular chart must be updated manually to keep up. Or you could use a little VBA to keep the chart in line with the pivot table.

Using Pivot Table Data for a Chart with a Dual Category Axis

You can easily lay out a pivot table so that two nested fields can be used to populate the labels in a dual category axis.

Grouping by Date in a Pivot Table

If you collect daily data, the day-to-day variation may overwhelm longer-term trends. You can group dates in your pivot table by larger units (weeks, months, years) and perform broader analysis.

Clean Up Date Items in An Excel Pivot Table

Grouping a pivot table by dates can make the table and related chart easier to read. This article shows how to remove extraneous date entries.

Referencing Pivot Table Ranges in VBA

You can create great analyses by merging the power of pivot tables with the flexibility of VBA. This tutorial removes much of the confusion about how to refer to parts of the pivot table in code.

Copy a Pivot Table and Pivot Chart and Link to New Data

If you copy a chart to a new worksheet, it still reflects data from the old worksheet. You can try to fix all the links, but even this tedium will not help with a pivot table. But the approach described here will do the trick.

Pivot Table Conditional Formatting with VBA

Use VBA to format cells within a pivot table according to various conditions.

Dynamic Chart using Pivot Table and VBA

This article shows how VBA can update a regular chart when its pivot table data changes.

Dynamic Chart using Pivot Table and Range Names

This article shows how dynamic range names without VBA can update a regular chart when its pivot table data changes, as long as the number of charted series is unchanged.

Pivot Chart Formatting Changes When Filtered

When you apply a filter or slicer to a PivotChart the custom formatting can change with each change in the filter. Here’s what you can do about it.

Pivot Table Books

Here are a few books about pivot tables, available from Amazon and elsewhere. I’ve selected these books because I know the authors and I’ve used the books, so I can vouch for their quality. Disclosure: if you purchase one of these books using my link, I’ll get a teeny commission.

Beginning PivotTables in Excel 2007: From Novice to Professional

Microsoft Excel Most Valuable Professional Debra Dalgleish explains what PivotTables are, how you can benefit from using them, how to create them and modify them, and how to use their enhanced features. Using a Pivot Table in Microsoft Excel 2007 is a quick and exciting way to slice and dice a large amount of data. Debra carefully explains the benefits of using Pivot Tables for fast data analysis, provides a step-by-step approach to those new to Pivot Tables, and offers tips and tricks that cannot be found elsewhere.

Excel Pivot Tables Recipe Book: A Problem-Solution Approach

Debra Dalgleish, Microsoft Excel Most Valuable Professional since 2001, and an expert and trainer in Microsoft Excel, brings together a one-stop resource for anyone curious about representing, analyzing, and using their data with PivotTables and PivotCharts. Debra presents tips and techniques in this collection of recipes that can’t be found in Excel’s Help section, while carefully explaining the most confusing features of PivotTables to help you realize their powerful potential.

Excel 2013 Pivot Table Data Crunching (MrExcel Library)

This book will help you leverage all the amazing flexibility and analytical power of Pivot Tables. You will learn how to generate complex pivot reports complete with drill-down capabilities and accompanying charts. Then you’ll discover how to build a comprehensive, dynamic pivot table reporting system for any business task or function. Microsoft Excel Most Valuable Professionals Bill Jelen and Mike Alexander include step-by-step instructions, real-world case studies, plus complete and easy recipes for solving your most common business analysis problems.

Peltier Tech Chart Utility

Save and Retrieve Information With Text Files

Introduction

There are many techniques one can use to write information from a running Microsoft Office program. You can save data in a workbook, but this is unwieldy for saving small items, such as program settings. You can use ini files or the registry, but both can get complicated. Using the registry is more invasive than you may want to be, and it’s very hard to transfer registry settings among computers. One of the easiest methods to save and retrieve data is by using simple IO commands with text files. Such files are small, they are processed rapidly, they can be read with a simple text editor, and they can be easily copied from computer to computer.

Visual Basic I/O

Visual Basic contains some commands which are useful for fast and simple text file input and output. Since the Visual Basic for Applications (VBA) used to program Microsoft Office is based on VB, Excel and the other Office applications can use these commands to read and write text files. I frequently use this technique to save application settings or to store debugging information.

The VBA help files contain detailed information about the various IO commands. Rather than beat the topic to death, I’ll present a couple simple examples that write and read text from a text file, then I’ll show some practical examples. The reader is directed to the help files for further details.

The subroutine TextIODemoWrite, shown below, opens a specified text file, puts a simple text string into the file, then closes the file. Files are opened using integers starting with 1; the FreeFile function assigns the next available integer to the file, to prevent accidentally assigning the same integer to two files. When you Open the file, a buffer is allocated to it, and the access mode (e.g., Output) is assigned. If the file does not already exist, it is automatically created. Subsequent commands (Write and Close) refer to the file using its integer. Text can be written using Write or Print; refer to the help files for specifics. Closing a file deallocates the buffer and disassociates the file from the file number.

Sub TextIODemoWrite()
  Dim sFile As String
  Dim sText As String
  Dim iFileNum As Integer

  sFile = "C:\test\textio.txt"
  sText = "Testing 1 2 3."

  iFileNum = FreeFile
  Open sFile For Output As iFileNum
  Write #iFileNum, sText
  Close #iFileNum
End Sub

The subroutine TextIODemoRead below shows the reverse operation, opening a text file, retrieving some text, closing the text file, then displaying the text in a message box.

Sub TextIODemoRead()
  Dim sFile As String
  Dim sText As String
  Dim iFileNum As Integer

  sFile = "C:\test\textio.txt"

  iFileNum = FreeFile
  Open sFile For Input As iFileNum
  Input #iFileNum, sText
  Close #iFileNum
  MsgBox sText
End Sub

Save and Retrieve Settings

The VBA procedures above can be expanded to allow us to save settings for later retrieval. The simplistic approach used in function SaveSetting reads variable names and values from a file, and writes them to a temporary file. If the named variable already exists, its new value is written to the temporary file in place of the existing value. When all variables have been read and written, the original file is replaced by the new file.

SaveSetting uses some additional functions, IsFullName, FullNameToPath, FullNameToFileName, and FileExists, which are shown at the end of this article.

Function SaveSetting(sFileName As String, sName As String, _
      Optional sValue As String) As Boolean
  
  Dim iFileNumA As Long
  Dim iFileNumB As Long
  Dim sFile As String
  Dim sXFile As String
  Dim sVarName As String
  Dim sVarValue As String
  Dim lErrLast As Long
  
  ' assume false unless variable is successfully saved
  SaveSetting = False
  
  ' add this workbook's path if not specified
  If Not IsFullName(sFileName) Then
    sFile = ThisWorkbook.Path & "\" & sFileName
    sXFile = ThisWorkbook.Path & "\X" & sFileName
  Else
    sXFile = FullNameToPath(sFileName) & "\X" & FullNameToFileName(sFileName)
  End If
  
  ' open text file to read settings
  If FileExists(sFile) Then
    'replace existing settings file
    iFileNumA = FreeFile
    Open sFile For Input As iFileNumA
    iFileNumB = FreeFile
    Open sXFile For Output As iFileNumB
      Do While Not EOF(iFileNumA)
        Input #iFileNumA, sVarName, sVarValue
        If sVarName <> sName Then
          Write #iFileNumB, sVarName, sVarValue
        End If
      Loop
      Write #iFileNumB, sName, sValue
      SaveSetting = True
    Close #iFileNumA
    Close #iFileNumB
    FileCopy sXFile, sFile
    Kill sXFile
  Else
    ' make new file
    iFileNumB = FreeFile
    Open sFile For Output As iFileNumB
      Write #iFileNumB, sName, sValue
      SaveSetting = True
    Close #iFileNumB
  End If
  
End Function

The function is called using this syntax (bTest is declared as a Boolean). The filename (with or without path), the variable name, and the value are all passed to the function as strings. If a path is not included as part of the file name, then the workbook’s own path is used. The value of the function is true unless an error is encountered.

bTest = SaveSetting("C:\test\settings.txt", "test variable", "test value")

Subroutine GetSetting enumerates the variables in the file until the named variable is found, then it extracts the value of this variable.

Function GetSetting(sFile As String, sName As String, _
      Optional sValue As String) As Boolean
  
  Dim iFileNum As Long
  Dim sVarName As String
  Dim sVarValue As String
  Dim lErrLast As Long
  
  ' assume false unless variable is found
  GetSetting = False
  
  ' add this workbook's path if not specified
  If Not IsFullName(sFile) Then
    sFile = ThisWorkbook.Path & "\" & sFile
  End If
  
  ' open text file to read settings
  If FileExists(sFile) Then
    iFileNum = FreeFile
    Open sFile For Input As iFileNum
      Do While Not EOF(iFileNum)
        Input #iFileNum, sVarName, sVarValue
        If sVarName = sName Then
          sValue = sVarValue
          GetSetting = True
          Exit Do
        End If
      Loop
    Close #iFileNum
  End If
  
End Function

The function is called using this syntax. The filename (with or without path) and the variable name are passed to the function as strings. The value of the function is true unless an error is encountered (i.e., the file or the variable is not found), and the value of the variable is passed back by the function.

If GetSetting("C:\test\settings.txt", "test variable", sValue) Then
  MsgBox sValue
End If

Save Debugging Information

During development or debugging of a program, it’s useful to save information during its execution. Subroutine DebugLog saves information in a text file with a name like debuglog051225.txt in the parent workbook’s directory. It saves the date and time along with the debug message, so the timing of the messages can be followed. You can log any string value, including markers indicating how far program execution has progressed, what the value of a key variable is, what an error may have occurred, etc.

Public Sub DebugLog(sLogEntry As String)
  ' write debug information to a log file

  Dim iFile As Integer
  Dim sDirectory As String
  
  sDirectory = ThisWorkbook.Path & "\debuglog" & Format$(Now, "YYMMDD") & ".txt"

  iFile = FreeFile

  Open sFileName For Append As iFile
  Print #iFile, Now; " "; sLogEntry
  Close iFile

End Sub

The following are a few examples of how to use DebugLog.

DebugLog "Starting Execution"

DebugLog "Variable MyVar = " & MyVar

DebugLog "Error " & Err.Number & ": " & Err.Description

The following is an excerpt from an actual debuglog file, debuglog051223.txt created by one of my old projects.

12/23/2005 8:00:33 AM  Excel Version 9.0, Build 8924
12/23/2005 8:00:33 AM  Program Starting
12/23/2005 8:00:33 AM  - File Name: ABC Engineering 2005-12-23-0.doc
12/23/2005 8:00:33 AM  - Order Number: ABC Engineering 2005-12-23-0
12/23/2005 8:00:38 AM  - saved as C:\Orders\ABC Engineering 2005-12-23-0.doc
12/23/2005 8:00:38 AM  - file exists: True
12/23/2005 8:00:38 AM  - no backup directory specified
12/23/2005 8:01:25 AM  - Checking Row 17: 3chars
12/23/2005 8:01:26 AM  - Checking Row 16: 3chars
12/23/2005 8:01:26 AM  - Checking Row 15: 3chars
12/23/2005 8:01:26 AM  - Checking Row 14: 3chars
12/23/2005 8:01:26 AM  - Checking Row 13: 3chars
12/23/2005 8:01:27 AM  - Checking Row 12: 3chars
12/23/2005 8:01:27 AM  - Checking Row 11: 3chars
12/23/2005 8:01:27 AM  - Checking Row 10: 3chars
12/23/2005 8:01:27 AM  - Checking Row 9: 15chars
12/23/2005 8:01:30 AM  - removing extra empty paragraphs
12/23/2005 8:01:56 AM  Program Finished

Supplementary Functions

Function IsFullName(sFile As String) As Boolean
  ' if sFile includes path, it contains path separator "\"
  IsFullName = InStr(sFile, "\") > 0
End Function

Function FullNameToPath(sFullName As String) As String
  ''' does not include trailing backslash
  Dim k As Integer
  For k = Len(sFullName) To 1 Step -1
    If Mid(sFullName, k, 1) = "\" Then Exit For
  Next k
  If k < 1 Then
    FullNameToPath = ""
  Else
    FullNameToPath = Mid(sFullName, 1, k - 1)
  End If
End Function

Function FullNameToFileName(sFullName As String) As String
  Dim k As Integer
  Dim sTest As String
  If InStr(1, sFullName, "[") > 0 Then
    k = InStr(1, sFullName, "[")
    sTest = Mid(sFullName, k + 1, InStr(1, sFullName, "]") - k - 1)
  Else
    For k = Len(sFullName) To 1 Step -1
      If Mid(sFullName, k, 1) = "\" Then Exit For
    Next k
    sTest = Mid(sFullName, k + 1, Len(sFullName) - k)
  End If
  FullNameToFileName = sTest
End Function

Function FileExists(ByVal FileSpec As String) As Boolean
   ' by Karl Peterson MS MVP VB
   Dim Attr As Long
   ' Guard against bad FileSpec by ignoring errors
   ' retrieving its attributes.
   On Error Resume Next
   Attr = GetAttr(FileSpec)
   If Err.Number = 0 Then
      ' No error, so something was found.
      ' If Directory attribute set, then not a file.
      FileExists = Not ((Attr And vbDirectory) = vbDirectory)
   End If
End Function

Final Words

This article was originally posted on an old site which no longer exists. I was able to retrieve my work from this defunct site, so I am reposting it here as is. There are newer techniques for working with text files in Windows, but sometimes the old ways are as simple and effective as the new.

Peltier Tech Chart Utility

The Advanced Chart Utility Is Here

Happy Birthday to Me!

As promised last week, the Advanced Edition of the Peltier Tech Chart Utility is here.

What’s In It?

The new software has all of the goodies of the current product, and nearly doubles it with lots of new custom charts and a handful of new features.

Here are how the products line up.

Comparison of custom charts offered by the Standard and Advanced Editions of the Peltier Tech Chart Utility

You’ll notice all of the new charts, including several more variations on waterfall charts. I guess you can never have too many variations on that theme, because folks are always asking for this or that “minor” adjustment. (“You can do it, you’re smart.”) The split bar is like having a separate waterfall for each bar of the waterfall

There are a couple variations on the histogram. The bars are the same, but the horizontal axis is built in two different ways, both of them better than plain old column charts in Excel, and better than the histogram in the Excel Analysis Toolpak.

Comparison of custom charts offered by the Standard and Advanced Editions of the Peltier Tech Chart Utility

While mulling over a thread in the Mr Excel forum not long ago, I came up with the routine that nicely exports a range to an image file. This is a nice companion to the Export Chart feature that’s been a staple of the Standard Edition. The Export Chart to Word and PowerPoint are goodies that I would toss into projects for clients, because they are cool and they didn’t know they wanted them.

Switch X and Y is a feature I built for my own use back in the day, when my company was still using Excel 97 and the rest of the world was using 2003. Very handy.

After I played with Jon Acampora’s tool to Move and Align Chart Titles, Labels, Legends with the Arrow Keys, I dusted off and finished my own arrow key positioning device and added a resizing function. It’s pretty cool, but there’s still some funny business when you make the first adjustment to the size of the plot area, at least in Excel 2013.

You should also notice that against all odds, the green column has every single one of the items in the yellow column. My previous product release was the Mac version of the Standard Edition of the Utility, and I was very pleased that I was able to get everything into the Mac version. Of course, the devil now owns 3/8 of my soul. Or Steve Jobs. Or do I repeat myself?

What’s coming?

First, I’m going to have to write some articles about using the Utility. I figure each topic will yield a blog post and a section of the online documentation.

Next, or in parallel, I’m going to put together a Mac version. I still have 5/8 of a soul for collateral.

Finally I’ll keep working on more custom charts, and this Advanced Edition will be the repository for them. On my slate are run charts, grouped box plots, tornado (sensitivity) diagrams, simple panel charts, and finally, a revival of the trellis charts based on my old Panel Chart Utility.

Among the charting features I have in mind are a zooming function, where you draw a box around points on the chart, and the axes will adjust to blow up those points. This box-drawing tool could be repurposed to allow selection of a subset of data, placing it into a new series for its own analysis. Also I was working on a point dragger, where you would click and drag on a plotted point, and the cell data would change to reflect the new position of the point. That was a popular feature in Excel 2003 and earlier, and Excel 2007 killed it along with all of those kittens.

How Can You Get The Utility

Go to the Peltier Tech Chart Utility page to license the utility. There are a few special deals.

From now through next Wednesday (June 18), you can buy the Advanced Utility for the price of the Standard Edition, by entering a coupon code found on the Utility page.

If you have already licensed the Standard Edition, email Peltier Tech to get a code to let you upgrade to the Standard Edition at no charge.

If you have a valid license for any of the older one-chart utilities, email Peltier Tech to get a different coupon code that will get you the Standard or Advanced Edition at a 50% discount.

As always, there are discounts for quantities greater than one license.

Peltier Tech Chart Utility

Peltier Tech Chart Utility for Excel – Advanced Edition

Announcing the Peltier Tech Advanced Chart Utility

I’m happy to announce the release of the Advanced Edition of the Peltier Tech Chart Utility on Thursday, 12 June 2014.

Why the 12th?

Am I superstitious? Am I avoiding Friday the 13th?

No!

June 12th is my birthday, and this is my present to myself.

Also, the ten days between now and then should give me almost enough time to ready the utility for release. In the days between now and then I will finish up the last couple of features, perform a little testing, and write about some of the new (and old) features here.

History

In September 2008 I introduced the Peltier Tech Waterfall Chart Utility. This was much more popular than I would have expected, and soon it became my primary activity. From this modest beginning five and a half years ago, I grew the product line into a series of small chart utilities, each having a single function: create a non-native chart in Excel with a click of a button. This set of utilities was popular, but it made addition of new charts and features hard to accomplish. A whole new product just for that little thing?

A bit over a year ago I released the Peltier Tech Chart Utility, which combined most of the smaller individual program into one grand utility. The individual utilities were upgraded (and enhancements are continually being made), and were combined with several useful charting features. Now I have a place for new features, and my customers can get all of the goodies in one simple program.

A month ago I came out with a Mac version of the Standard Utility, with all of the features of the Windows version, and that has been twice as popular as I had expected.

Of course, I had loads of other ideas for my utility, but I didn’t have time to get everything into the final product.  So I released what I had into the Standard Edition of the tool. In the meantime I worked on these additional features.

These additional features are being incorporated into this new Advanced Edition, which includes everything from the Standard Edition and a whole lot more.

Comparison

In the grid below you can see that the Standard Chart Utility has quite a lot of custom charts, including the popular WaterfallCluster-Stack, and Box and Whisker charts. The Advanced Edition includes a few more variations on waterfall charts, a Diverging Stacked Bar Chart useful for plotting survey results, Histograms (two types) and Pareto Diagrams, and Cycle Plots.

Comparison of Custom Charts in Standard and Advanced Editions of the Peltier Tech Chart Utility

The following grid shows the extensive lineup of features of the Standard and Advanced Chart Utilities. In addition to functions that easily edit the series formula, handle labeling of series so the legend isn’t needed, opening the folder in which the active sheet is stored, and exporting a chart as an image file, the Advanced Edition can also export a range as an image file, export one or more charts into PowerPoint or Word in several formats, switch various X and Y aspects of a chart (data, axis scales, and/or axis titles).

Comparison of Charting Functions in Standard and Advanced Editions of the Peltier Tech Chart Utility

In addition, new charts and features under development will be added to the Advanced Chart Utility, and anyone with a valid license can simply email me to request a link to the update.

Macintosh Version

Is there an Advanced Chart Utility for the Mac?

No.

Will there be an Advanced Chart Utility for the Mac?

Of course. It will take a little time to develop, because Mac and Microsoft do not exactly spell compatibility. But plans are to have it available by the end of June.

Special Deal

The Peltier Tech Advanced Chart Utility will be available on Thursday, 12 June 2014. Anyone who has a valid license for the Standard Edition before this release date can upgrade to the Advanced Edition for free. You simply need to email me asking for a coupon code for a free upgrade between now and 11:59 pm EDT, Friday, 13 June 2014.

The Peltier Tech Advanced Chart Utility will retail for $99 US (the Standard Edition is $79). On Thursday and Friday, 12-13 June 2014, you can get the Advanced Edition for the same $79 price as the Standard Edition. There will be a coupon code for this discount right next to the Add to Cart button on the Peltier Tech Chart Utility web page.

As always, users of the older versions of the Peltier Tech Utilities can email me to get a coupon code for a 50% discount.

 

Peltier Tech Chart Utility

Link Excel Chart Axis Scale to Values in Cells

Excel offers two ways to scale chart axes. You can let Excel scale the axes automatically; when the charted values change, Excel updates the scales the way it thinks they fit best. Or you can manually adjust the axis scales; when the charted values change, you must manually readjust the scales. Wouldn’t it be great to be able to link the axis scale parameters to values or, even better, formulas in the worksheet? This page shows how to use VBA to accomplish this.

If you want a ready-to-use solution, try Tushar Mehta’s AutoChart Manager add-in, available as a free download at http://www.tushar-mehta.com/excel/software/autochart/index.html.

There are a few pieces you need to make this technique work. You need a chart, a set of values for the scaling parameters, and some VBA code to change the axis scales. The code can be either linked to a button, or run from a Worksheet_Change event procedure.

The Chart

The actual mechanics of creating this chart are incidental to the discussion, but we’ll use the following simple data and chart (named “Chart 1″, the default name of the first chart created in a worksheet).

Simple data and scatter chart in Excel

Axis Scale Parameters in the Worksheet

You need a place to put the axis scale parameters. In this example, the range B14:C16 is used to hold primary X and Y axis scale parameters for the embedded chart object named “Chart 1″. This example can be expanded to include secondary axes, or to change other charts as well.

Simple data and scatter chart with range containing axis scale parameters

The cells B14:C16 can contain static values which the user can manually change, or they can contain formulas with your favorite axis scaling algorithms. See how to set up axis-scaling formulas in Calculate Nice Axis Scales in Your Excel Worksheet.

Change Chart Axes with VBA

The parts of Excel’s charting object model needed here are the .MinimumScale.MaximumScale, and .MajorUnit properties of the Axis object (the .MinorUnit property could also be controlled, but I usually do not show minor tick marks). These properties can be set equal to constant values, to VBA variables, or to worksheet range or named range values, as illustrated in this code sample:

    With ActiveChart.Axes(xlValue, xlPrimary)
      .MaximumScale = 6
        ' Constant value
      .MinimumScale = dYmin
        ' VBA variable
      .MajorUnit = ActiveSheet.Range("A1").Value
        ' Worksheet range value
    End With

If you have a Line, Column, or Area chart with a category-type X axis, you can’t use the properties shown above. The maximum and minimum values of a category axis cannot be changed, and you can only adjust .TickLabelSpacing and.TickMarkSpacing. If the X axis is a time-scale axis, you can adjust .MaximumScale.MinimumScale, and .MajorUnit. You should turn on the macro recorder and format an axis manually to make sure you use correct syntax in your procedure. Any chart’s Y axis is a value axis, and this code will work as is.

VBA Procedure to Rescale Chart Axes

Press Alt+F11 to open the VB Editor. In the Project Explorer window, find the workbook to which you want to add code.

Project Explorer window of the VB Editor

Double click on the module to open it. If there is no module, right click anywhere in the workbook’s project tree, choose Insert > Module. Or use Insert menu > Module. Up will pop a blank code module.

Empty code module

If your module does not say Option Explicit at the top, type it in manually. Then go to Tools > Options, and in the Editor tab check the Require Variable Declaration checkbox. This will place Option Explicit at the top of every new module, saving innumerable problems caused by typos. While in the Options dialog, uncheck “Auto Syntax Check”. This will save innumerable warnings about errors you already know about because the editor turns the font of the offending code red.

Tools menu > Options in the VB Editor

You can use a simple procedure that changes the axes on demand. The following changes the scales of the active chart’s axes using the values in B14:C16. Select the chart, then run the code.

Sub ScaleAxes()
  With ActiveChart.Axes(xlCategory, xlPrimary)
    .MaximumScale = ActiveSheet.Range("B14").Value
    .MinimumScale = ActiveSheet.Range("B15").Value
    .MajorUnit = ActiveSheet.Range("B16").Value
  End With
  With ActiveChart.Axes(xlValue, xlPrimary)
    .MaximumScale = ActiveSheet.Range("C14").Value
    .MinimumScale = ActiveSheet.Range("C15").Value
    .MajorUnit = ActiveSheet.Range("C16").Value
  End With
End Sub

You can type all this into the code module, or you can copy it and paste it in.

Code module with sample code

Select the chart and run the code. You can run the code by pressing Alt+F8 to open the Macros dialog, selecting the procedure in the list of macros, and clicking Run.

Macros dialog with ScaleAxes procedure

Or you could assign the code to a button in the worksheet.

Here is the chart after running the code.

Simple data and scatter chart after rescaling axes

Worksheet_Change Event Procedure to Rescale Chart Axes

A more elegant approach is to change the relevant axis when one of the cells within B14:C16 changes. We can use the Worksheet_Change event to handle this.

For an introductory description of events in Microsoft Excel, check out the Events page on Chip Pearson’s web site (http://cpearson.com/excel/Events.aspx).

The Worksheet_Change event procedure fires whenever a cell in the worksheet is changed. To open the code module for a worksheet, right click on a worksheet tab and select View Code from the pop up menu. Or double click on the worksheet object in the Project Explorer window. The code module for the worksheet is opened. Now that we’ve set Require Variable Declaration, note that Option Explicit has appeared automatically atop the module.

Empty worksheet code module

You can write the entire procedure yourself, but it’s easier and more reliable to let the VB Editor start it for you. Click on the left hand dropdown at the top of this module, and select Worksheet from the list of objects.

Worksheet code module - Objects dropdown

This places a stub for the Workbook_SelectionChange event in the module. Ignore this for now.

Click on the right hand dropdown at the top of this module, and select Change from the list of events.

Worksheet code module - Events dropdown

You now have a couple event procedure stubs.

Worksheet code module with event procedure stubs

Delete the Worksheet_SelectionChange stub, which we will not be needing, and type or paste the Worksheet_Change code into the Worksheet_Change stub.

Worksheet code module with Worksheet_Change event procedure

The code is given below, so you can copy it. When the event fires, it starts the procedure, passing in Target, which is the range that has changed. The procedure uses Select Case to determine which cell was changed, then changes the appropriate scale parameter of the appropriate axis.

Private Sub Worksheet_Change(ByVal Target As Range)
  With ActiveSheet.ChartObjects("Chart 1").Chart
    Select Case Target.Address
      Case "$B$14"
        .Axes(xlCategory).MaximumScale = Target.Value
      Case "$B$15"
        .Axes(xlCategory).MinimumScale = Target.Value
      Case "$B$16"
        .Axes(xlCategory).MajorUnit = Target.Value
      Case "$C$14"
        .Axes(xlValue).MaximumScale = Target.Value
      Case "$C$15"
        .Axes(xlValue).MinimumScale = Target.Value
      Case "$C$16"
        .Axes(xlValue).MajorUnit = Target.Value
    End Select
  End With
End Sub

Peltier Tech Chart Utility

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.