Sometimes it’s desirable to make a regular chart from a pivot table, but Excel makes it difficult. If your active cell is in a pivot table, inserting a chart automatically inserts a pivot chart. Defining a source data range that intersects a pivot table automatically converts the chart into a pivot table. And once a [...]

Tony Rose of Decision Support Analytics tried the Export Chart add-in I posted in Enhanced Export Chart Procedure. Tony wondered about my statement that PNG images were so much superior to JPGs for Excel charts. So he did a quick and dirty experiment, and reported in Excel Add-in: Export Chart Feature that there was not [...]

Recently I posted several VBA procedures for exporting charts and other purposes. These make use of a number of procedures that I use when manipulating file names. I inadvertently left these out of the posts, so I am including them here.
The following shows what the functions return when passed a file name and path of [...]

You can add flexibility to your data analysis by grouping variables. For example, you may have daily sales figures that fluctuate greatly in value, some days having no sales at all, some days with small values, and some with large values. A plot of this data is very noisy, and it’s hard to see any [...]

A few items of interest, interesting to me at least.

From the WordPress dashboard:
Akismet has protected your site from 1,020 spam comments
This is since I switched hosting services, where I’d gotten up to at least a few hundred. Akismet seems very effective. I’ve only had I think one comment marked as spam which was [...]

In Export Chart as Image File I described the VBA command to export a chart as an image file, and I presented a simple routine that exported the active chart.
I said that one day I would enhance the procedure to use GetSaveAsFileName, which allows the user to browse to any folder to save the image [...]

In Category Axis Tricks for Line and Area Charts – 1, we saw how to use two line series to create a two-toned line chart. This is useful if we want to differentiate between actual values up to a certain date and predicted values thereafter, or if we want to distinguish values from different periods [...]

A lot of Excel procedures are packaged in Add-Ins. An Add-In is basically a workbook containing some code, that has some other unique properties. The workbook is invisible. The workbook can be installed, which means it opens when Excel opens, so that its functionality is always available.
Installing an Add-In is really not complicated. If it [...]

Let’s look at the behavior of line charts and the category axis. Here is some simple data I made up just for this exercise, and a typical line chart plotting the data.
 
 

Value

1
11

2
7

3
12

4
7

5
15

6
13

7
16

8
12

9
17

10
16

11
18

 
 
Okay, not too boring. Let’s see what happens if we split the data into two series, red and blue. The red series will include [...]

Often people want to save their charts as an image file. Excel doesn’t offer this as a native feature in the UI, but you can export a chart using VBA:
ActiveChart.Export “C:\My Charts\SpecialChart.png”
 
This command allows you to export your chart in any of the bitmap formats that you have the appropriate export filters for. This varies [...]