Mac and Windows

Install an Excel Add-In in Excel for Mac

Tuesday, March 16, 2021 by Jon Peltier 2 Comments

Tuesday, March 16, 2021 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Written by Ron de Bruin, Microsoft Excel MVP.

Excel Add-Ins

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. An add-in usually has some user interface elements, such as dialogs (UserForms), buttons on the ribbon, or buttons on context (right-click) menus, so you can access its features.

The add-in workbook itself is invisible, though it has one and often more hidden worksheets, where information related to the add-in may be stored. The add-in can be installed, which means it opens every time Excel opens, so that its functionality is always available. An Excel add-in can also be opened like a regular file when you need it, without installing it, though it is still hidden.

Preparing to Install the Add-In

Very often, an Excel add-in is packaged in a zip file. If you download a zip file in safari it will automatically Unzip the file and you will find the add-in in your Download folder, if not double click the add-in and it will unzip.

Where do I store the add-In?

The best place to store the add-in is in the User Add-in Library, which is:

/Users/UserName/Library/Group Containers/UBF8T346G9.Office/User Content/Add-Ins

To get to this folder, open a Finder window and click on Go in the Finder menu bar; if you press and hold the Option key you will see the hidden Library folder in the list so you can click on it to open it. Now drill down to Group Containers, UBF8T346G9.Office, User Content, and then Add-Ins. The strange label “UBF8T346G9.Office” was assigned by Apple to Microsoft, and we have to live with it.

You can actually store the add-in in almost any convenient folder you want, but when you use the Add-Ins folder (above), the add-in is listed in the Excel Add-ins dialog, and you will not have to browse to find it and add it to this list.

Tip: with the Add-ins folder open in Finder, use the shortcut Command+Control+t to add a link to this folder in the Finder Favorites sidebar so it is easy to access the folder another time.

Pro Tip: Go to Setup your Mac for Mac Office. Among other hints, this page includes a script that will install shortcuts to the Add-Ins folder and several other folders on the Mac desktop.

Installing and Uninstalling the Add-In

To install the add-in, follow these simple steps.

Open Excel.
From the top-level Mac menubar, click the Tools menu and select Excel Add-ins… to open the Add-ins dialog.
If the Add-In is listed, simply check it in the list.
If the Add-In is not listed, use Browse to select it, and click Open.
Press OK.
Done!

To uninstall the add-in, follow these steps.

From the Mac menubar, click Tools and select Excel Add-ins… to open the Add-ins dialog.
Find the Add-In in the list, and simply uncheck it.
Press OK.

The add-in will remain on the list, to make it easy to reinstall at some future time.

Using an Excel Add-In without Installing

When you install an add-in, it will load whenever Excel opens, so it is always ready to use. If you don’t want an Excel add-in open all the time, you can simply start it when you need it, using File menu > Open in Excel or double-clicking on the add-in file in Finder. This opens the add-in, but just like an installed add-in, there is no visible workbook or worksheet in Excel.

About the Author

I’ve known Ron de Bruin for over 20 years, when we were both lurking on the Microsoft news groups, in awe of the first generation of Excel MVPs, like John Walkenbach, Chip Pearson, Bob Umlas, Ron Bovey, and Stephen Bullen. We answered more than a few questions and learned all about Excel. I’ve worked quite a bit with Excel for Mac, but Ron has become an expert on all things Mac and Excel. I became an Excel MVP in 2001, and Ron followed soon after, in 2002. Ron has been a strong advocate for users of Excel on the Mac. Ron covers many Excel and VBA topics on Excel Automation, and he covers Mac-specific Excel issues at Mac Excel Automation.

Posted: Tuesday, March 16th, 2021 under VBA.
Tags: Add-Ins, installing add-ins, Mac and Windows.
Comments: 2

Designing UserForms for Mac and Windows

Thursday, November 9, 2017 by Jon Peltier 17 Comments

Thursday, November 9, 2017 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

The Problem.

There are many annoyances when designing a VBA project to run on both Windows and Mac computers. One of the most noticeable is related to the difference in screen resolution between the two platforms. On the Mac, it’s 96 dpi, where each dot represents a pixel. In Windows, screen resolution is 72 dpi (dots per inch) and each dot represents a “point”, while pixels are still 96 per inch. Confusing the issue is that VBA in Windows uses points as the measurement unit for designing UserForms, while VBA on the Mac uses pixels. The result is that without applying any correction, UserForms that come out just right in Windows are only 75% as large on the Mac, making them difficult to read.

For example, here is a UserForm designed in Windows, opened in Windows. The text might seem a bit small to me now that my eyes are getting older, but it’s still perfectly legible.

Here is that same UserForm opened on a Mac. The header text is fine, but the text in the dialog itself is too small to read comfortably.

In the past, I supported separate add-ins, one for Windows and the other for Mac. The dialogs for each were appropriately sized, and I took care of other coding idiosyncrasies in the separate files. But it’s a lot of extra work to lug around two separate versions of every file. Every adjustment you make has to be made twice, and it’s hard to remember what changes you’ve made across large projects.

The Solution: UserForms For Mac And Windows.

I have developed a simple routine that is called from each UserForm’s initialize event, which changes the size, position, and font of each control by a factor of 4/3. The text still may not always be perfect, since you can only specify whole number font sizes, so I suppose I should always round up the font size. I do make sure when I design the form that controls are more than large enough in case the text takes up extra space. Aside from this, it works pretty nicely.

Here is a UserForm designed in Windows, but which uses the resizing routine so it displays legibly on either computer. First, it is shown opened in Windows.

Below it’s shown opened on the Mac. The dialogs are equally legible, and the text came out pretty much the same. Sometimes a large block of text may not wrap the same, so if it really matters, you should hard code your line breaks by typing Ctrl+Return while entering the text.

Note: While Microsoft has substantially improved the VB editor on the Mac, you still can’t work with UserForms on the Mac. You have to build them into your file in Windows and them move the file to the Mac.

The Code.

This calls the routine from the UserForm’s code module:

Private Sub UserForm_Initialize()
  #If Mac Then
    ResizeUserForm Me
  #End If
End Sub

The #If Mac Then and #End If structure indicates a section of code which is conditionally compiled. When using a Mac, the constant Mac is True, so the code between #If and #End If is compiled and run, so the UserForm is resized. In Windows, this code is ignored, and the UserForm is displayed without rescaling.

The global resizing factor is placed in an appropriate place in the declarations section of a regular code module:

Public Const gUserFormResizeFactor As Double = 1.333333

And this routine in a regular code module does the resizing:

Sub ResizeUserForm(frm As Object, Optional dResizeFactor As Double = 0#)
  Dim ctrl As Control
  Dim sColWidths As String
  Dim vColWidths As Variant
  Dim iCol As Long

  If dResizeFactor = 0 Then dResizeFactor = gUserFormResizeFactor
  With frm
    .Height = .Height * dResizeFactor
    .Width = .Width * dResizeFactor

    For Each ctrl In frm.Controls
      With ctrl
        .Height = .Height * dResizeFactor
        .Width = .Width * dResizeFactor
        .Left = .Left * dResizeFactor
        .Top = .Top * dResizeFactor
        On Error Resume Next
        .Font.Size = .Font.Size * dResizeFactor
        On Error GoTo 0

        ' multi column listboxes, comboboxes
        Select Case TypeName(ctrl)
          Case "ListBox", "ComboBox"
            If ctrl.ColumnCount > 1 Then
              sColWidths = ctrl.ColumnWidths
              vColWidths = Split(sColWidths, ";")
              For iCol = LBound(vColWidths) To UBound(vColWidths)
                vColWidths(iCol) = Val(vColWidths(iCol)) * dResizeFactor
              Next
              sColWidths = Join(vColWidths, ";")
              ctrl.ColumnWidths = sColWidths
            End If
        End Select
      End With
    Next
  End With
End Sub

Example Workbook.

I’ve posted a simple workbook that illustrates this technique. Download it by clicking the icon below.

When you open the workbook, you’ll see a blank worksheet with three buttons.

The first button opens the Windows-only UserForm shown in the first and second figures above, in the description of the problem.

The second button opens the Windows-and-Mac UserForm shown in the third and fourth figures, in the description of the solution.

The third button opens another Windows-and-Mac UserForm which also has a simple mechanism to call the ResizeUserForm procedure, and resize the UserForm on the fly. Here is it opened in Windows.

Enter a number in the New Form Magnification box (for example 2) and click Apply, and the form is resized accordingly.

Enter 1.333333, the ratio between pixels and points, and this is how it will be resized for the Mac, though it’s too large for Windows.

Enter 0.75, the inverse of the above, and you get a sense for how small it would be on the Mac without rescaling. Yeah, way too small.

Here is the same form opened on a Mac. Note that it’s already resized to 1.333333. Resized nicely.

Let’s see how it looks at 200%. Pretty large.

If you enter 1 in the box, you can see how the form would look without resizing.

In all of your real projects, you should apply this technique to all of your UserForms. Before deploying them to your users, you should make sure that the UserForms that look fine in Windows rescale properly on a Mac. Occasionally text wraps differently or certain other controls may need adjustments. But this eliminates the need for separate UserForms for Mac and Windows.

Posted: Thursday, November 9th, 2017 under VBA.
Tags: Dialogs, Mac and Windows, UserForms.
Comments: 17

Dynamic Charts in Excel 2016 for Mac

Friday, February 12, 2016 by Jon Peltier 14 Comments

Friday, February 12, 2016 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

A reader emailed to ask whether you could make a dynamic chart using OFFSET-function-based Names in Excel 2016 for Mac. Good question, and I wondered if he’d encountered some unexpected problem, perhaps a bug, in Mac Excel. So I dusted off my MacBook Pro and tried it out.

Bottom line: There are several ways to make dynamic charts in Excel, and there seems to be no difference other than cosmetic in how they work between different versions of Excel, and between operating system. The protocols are the same for Mac Excel and Windows Excel, and perhaps it’s time for a quick review. This exercise was done completely in Mac Excel 2016, and other than not knowing a few of the shortcuts I use everyday, it was not very different from working in Windows Excel 2016.

Dynamic Charts in Excel

It’s pretty easy to set up data and create a chart in Excel. But once you’ve created a chart, it keeps plotting data from the same cells. If the data in the cells changes, so does the chart, but if the data extends to more cells (or shrinks to fewer cells), the chart doesn’t seem to notice.

Static Excel Chart

There are a couple ways to create charts that will grow with your data. The easiest way is to use Tables as the chart source data. A bit more complicated is to use Excel’s Names to define the series data for your chart. Using Names can lead to more flexibility in defining the data in your charts. I’ll describe how to make dynamic charts using Tables, using Names, and using Names in a more flexible way.

Dynamic Charts Using Tables

The easiest way to make a chart’s contents reflect the size of a range of data is to put the data into a Table.

Tables made their appearance in Excel 2003, and were called “Lists”. These lists were a more structured container for your data, with a database structure of fields (columns) and records (rows), field headers (column headers) and filtering tools. You could sort and filter your data range easily, and any formula that used a whole column of your List updated to automatically keep using that whole column of the list. Lists became the favored source data for charts and also for pivot tables.

In Excel 2007, Lists became known as “Tables”, and their capabilities have been expanded in every version since.

The screenshot below shows the same data and chart as above, but the data is now in a Table.

To get your data into a table, you select it (or select one cell and let Excel figure out how far it stretches), and on the Insert tab of the ribbon, click Table. Excel asks if your table has headers, then it applies a Table style (the yellow style is shown below), it adds AutoFilter dropdown arrows to the field headers, and it puts a small backwards “L” bracket at the bottom right corner of the table.

Excel Chart Based On Table

You can change the size of the Table by clicking and dragging the bracket at the bottom left corner of the Table. If you type or paste data directly below the Table, the Table will automatically expand to include this new data. And a chart that uses all rows of the existing Table will expand accordingly.

Excel Chart Expands to Include Added Table Rows

If you type or paste data directly to the right of the Table, the Table will also automatically expand to include this new data. A chart that uses all of the existing Table will expand accordingly.

Excel Chart Expands to Include Added Table Columns

This little trick of adding a new series if the data expands accordingly is nice, but it requires that the chart already contain all of the Table’s data.

Names (a/k/a “Defined Names”, “Named Ranges”, etc.)

A Name is what Excel calls a variable that resides in a worksheet or a workbook.

Names are often assigned to cells or ranges; for example, you might place a sales tax rate into a cell and name the cell SalesTax, and subsequently use the cell’s name rather than its address in a formula. Because of this Names have been nicknamed “Named Ranges”.

However, the definition of the name includes a formula. If my sales tax rate was stored in cell A1, then my Name SalesTax would have a definition of “=A1”. Because of this, John Walkenbach proposed that Names should be called “Named Formulas”, but he’s smarter than the rest of us, so his suggestion didn’t stick.

We can use Names in our charts, but we need a distinct name for each dynamic range that the chart will need. We’ll need one Name for the X values if the series use the same X values range, and we’ll need one Name for the Y values of each series. In our sample, we will need three Names. I’ll call them XValues, Y1Values, and Y2Values, and I will define them as follows:

XValues
=OFFSET(Names!$A$1,1,0,COUNTA(Names!$A:$A)-1,1)

This OFFSET formula uses cell A1 of worksheet Names as a starting point, offsets the range down by one row and right by zero rows, then makes it as many rows tall as the number of alphanumeric cells in column A minus one (we don’t want to include the “Category” label), and one column wide.

So starting with cell A1, our range begins in cell B1, and is 6 rows tall and one column wide; our final range is A2:A7. It’s easy to see that adding another value into cell A8 will expand this range to A2:A8. However, if we add a value in cell A57, it will also expand our range to A2:A8, so we need to make sure the rows below our data are kept blank.

The other two definitions are easier:

Y1Values
=OFFSET(Names!XValues,0,1)

Y2Values
=OFFSET(Names!XValues,0,2)

We’ve already figured out how large each range needs to be, since the X and Y values have the same number of cells, so both of these OFFSET formulas start with the first name XValues as an anchor, and offset no rows down but one or two columns to the right. If we don’t specify sizes, then the new Names will define ranges the same size as the anchor.

Okay, that’s how to build a formula definition for a Name. Let’s actually create a Name.

On the Formulas tab of the ribbon, click the Define Name dropdown, and select Define Name… This pops up the Define Name dialog, shown below for the Mac. The Windows dialog is a bit more extensive, and Windows Excel has a much better Names Manager (this dialog happens to serve as the Mac’s Names Manager). For a truly powerful Name Manager, you should try out the free Name Manager add-in at the website of my colleague, Excel MVP Jan Karel Pieterse.

Static Excel Chart

If you’ve selected data before opening the dialog, Excel tries to guess how you want to name data based on labels in the top row and left column of the selection. But I’ve cleared all of this so we’re starting fresh.

Here I’ve typed the name of the Name. Note that I’ve included the worksheet name and exclamation point, which means the Name will be “in scope” (i.e., available) for the worksheet “Names”. Otherwise it would be “in scope” for the entire workbook.

Static Excel Chart

Then enter the formula where it says “Select the range of cells”. You can enter any formula that refers to cells, or a formula that calculates a value, or a constant value. I don’t capitalize my function names when I enter them; that way, if there’s an error, Excel won’t capitalize a bad function name. A misspelled keyword is easier to recognize if it is not capitalized (“offfset” vs. “OFFFSET”).

Static Excel Chart

Click the OK button to add the Name and exit the dialog, or click the Plus icon to add the name and keep the dialog open.

The name is listed in the box at the left; the worksheet name is listed as well to remind us that the scope of the Name is limited to that worksheet. The sheet name is removed from the name in the top right box.

Static Excel Chart

You can make sure the name refers to the intended range if you click in the box with the formula defining the Name. With the cursor in the formula, the range A2:A7 is highlighted in the worksheet. Perfect.

Static Excel Chart

Now enter the name and formula for the next Name, and don’t forget to include the worksheet name.

Static Excel Chart

Click the plus icon to add the name, and click in the formula to make sure that the Name references the desired range, B2:B7. Check.

Static Excel Chart

Enter the name and formula for the last Name, remembering to include the worksheet name. Click the plus icon, and check that the formula refers to C2:C7.

Static Excel Chart

Whew! Now we’re finally ready to make our dynamic chart.

Dynamic Charts Using Names

Every chart series has a formula that defines the data in the chart. The blue series in the static chart below is

=SERIES(Names!$B$1,Names!$A$2:$A$7,Names!$B$2:$B$7,1)

This means it uses cell B1 of the sheet Names for the series name (“Alpha”), A2:A7 for the X values, B2:B7 for the Y values, and it’s the first series in the chart. The formula for the orange series is

=SERIES(Names!$C$1,Names!$A$2:$A$7,Names!$C$2:$C$7,2)

We can use the Select Data dialog to modify these, but it’s easiest to simply edit the formula directly.

Select the blue series of the dynamic chart, and observe the formula in the Formula Bar. It probably looks like the first SERIES formula above (I invariably start with a static chart of the data I want to plot dynamically). Edit the formula to read as follows, and press Enter.

=SERIES(Names!$B$1,Names!XValues,Names!Y1Values,1)

If Excel doesn’t like the new formula, make sure you’ve spelled the Names correctly.

Similarly edit the formula for the orange series to read

=SERIES(Names!$B$1,Names!XValues,Names!Y2Values,2)

At first the two charts look the same.

Static Excel Chart

When we select the static chart, we can see the chart’s source data highlighted in the worksheet.

Static Excel Chart

We see the same data highlighted when the dynamic chart is selected. It’s convenient that Excel is smart enough to highlight the chart data even if it is defined by dynamic Names. I’ll select the dynamic charts in the rest of this tutorial to show the range included in these charts.

Static Excel Chart

Now let’s extend the data by a couple of rows. The static chart isn’t clever enough to notice, but the dynamic chart keeps up nicely, illustrated by the highlighted data in the worksheet.

Static Excel Chart

If we extend the data by a new column, the static chart doesn’t change, and the dynamic chart doesn’t add a series to represent the new data.

Static Excel Chart

I’ve added a third chart which shows the new data. I had to add an extra Name to the worksheet:

Y3Values
=OFFSET(Names!XValues,0,3)

and then I had to add a third series to the chart with the following SERIES formula:

=SERIES(Names!$B$1,Names!XValues,Names!Y3Values,3)

My new chart contains all the data, though I had to include it in the chart manually.

Static Excel Chart

Before Excel 2003, the only way to get a dynamic chart was by using Names. It’s a lot of work, and hardly seems worthwhile if using Tables is so easy. But if we know how to make a chart using Names, we can make a dynamic chart that’s more complicated than just expanding to add a row.

Dynamic Charts Using Complicated Names

How about a chart that doesn’t show all the data, but only the last several points. This might be useful if you want to show the last six months of sales, or high temperature for the previous week.

We’ll make a dynamic chart that plots the last five values. The setup is almost identical to the previous dynamic chart, but our definition of XValues will change.

XValues
=OFFSET(Names!$A$1,COUNTA(Names!$A:$A)-1,0,-5,1)

This OFFSET formula uses cell A1 of worksheet Names as a starting point, offsets the range down by the number of alphanumeric cells in column A minus one, and right by zero rows. This means now we’re starting at cell A7 instead of A2. Then we make the range -5 rows high, meaning we count upward, and one column wide. Our new X values range should be A3:A7. After entering the new name as before, check to make sure the correct range is highlighted.

Edit the SERIES formulas of your dynamic chart as we did above. We see that our static chart shows all six points of the data, but the dynamic chart shows only the last five points, categories B through F.

Static Excel Chart

Let’s add a couple rows of data. The static chart is, well, static, but our dynamic chart shows the new last five points, categories D through H.

Static Excel Chart

Summary

Dynamic charts can easily be created in Excel using data ranges from Tables.

With a bit more work dynamic charts can be created using skillfully defined Names. These charts can be more flexible than Table-based dynamic charts, depending on the formula skills of the Excel user. There are a few gotchas that I didn’t mention: some Name definitions seem like they should work, for example, but Excel charts won’t recognize certain functions. Also, some Name names may cause problems, especially those beginning with the letter “c”; you can’t enter them into the SERIES formula, but you can use them in the Select Data dialog.

Posted: Friday, February 12th, 2016 under Dynamic Charts.
Tags: Dynamic Charts, Mac and Windows.
Comments: 14

Microsoft MVP Logo

Office 2016 For Mac Is Here (well…)

Monday, July 13, 2015 by Jon Peltier 22 Comments

Note: This article was originally published on 13 July. I have made modifications on 17 July.

The big announcement from Microsoft last Thursday was Office 2016 for Mac is here! Ed Bott has a glowing review at New Office 2016 for Mac makes life easier for the cross-platform crowd.

We’ve known for months that Microsoft has been working on Office 2016 for both Windows and Mac. I’ve been playing a bit with the Office 2016 for Windows preview for a while, but I haven’t done much with the Mac preview.

Highlights

Mac Office 2016 User Interface

The Office 2016 for Mac user interface is very much like that for Office for Windows. It no longer feels like a toy program built by an 8th grader in shop class. This is a very good sign. Now Office looks and feels the same across all platforms: Windows, Mac, Android, iOS.

New Charts (Windows only, so far)

Earlier, Microsoft Introduc[ed] new and modern chart types now available in Office 2016 Preview. The latest Office 2016 preview features a few new chart types, including some that were previously only available to users of add-ins like the Peltier Tech Charts for Excel. Microsoft has added waterfalls, histograms, paretos, treemaps, and sunbursts.

Lowlights

Office 365

The Windows Office 2016 preview has only been available through Office 365 (the kludgy online subscription service). I never can remember which of my umpteen Microsoft logins is valid for Office 365, and even when I look it up, the correct user ID and password don’t work the first time. Updating Windows Office 2016 preview versions has never gone without several major hitches for me, followed by complete uninstallation then reinstallation from scratch.

So far, Mac Office 2016 is only available via Office 365, which Microsoft treats as a good thing. In Windows, installing from Office 365 wipes out any older versions of Office, so you can’t run Office 2007, 2010, and 2013 side-by-side to support different clients. On the Mac, I was glad to see that I could install Office 2016 without wiping out the previously installed Office 2011. I did have to rebuild the LaunchPad links to Office 2011 and put the icons back onto the Dock, but that only wasted an hour.

Mac Office 2016 VBA

This is the painful one. Mac Office 2016 does still support VBA, of course, and Microsoft has rebuilt the VBA Editor from scratch, which was drastically. Unfortunately the VBA Editor is not really ready to use: you can’t insert new modules or UserForms, and while you can edit existing modules, you can’t even view the UserForms.

Microsoft is making small improvements to the VB editor, and these improvements will be part of the monthly updates. but they are not expecting to make the editor as functional as that in Windows. They encourage developers to build their add-ins in Windows versions of Excel or in Excel 2011, then test in Mac Excel 2016.

Mac Office 2016 Custom User Interface Elements

First, a little background…

In Office 2003 and earlier, developers and users could construct custom menus and toolbars to access built-in and custom functionality.

Starting in Office 2007, Office for Windows has had the ribbon. It could readily be modified for developers willing to delve into RibbonX, a variation of XML used to control and customize the ribbon. Since Office 2010, the ribbon could also be easily modified through the user interface. While custom menus and toolbars were no longer supported in Office 2007, any custom menu items were put into a special Add-Ins ribbon tab. Windows Office add-in developers, myself included, have migrated to custom ribbon interfaces, to great effect.

Mac Office 2011 introduced a low-functioning ribbon, which did not allow any customization programmatically or through the Office interface. However, the custom menus and toolbars that were discontinued in Windows Office are still supported in Mac Office 2011.

Mac Office 2016 has a better ribbon than 2011, but it is still not customizable by the user, nor can it be it controlled using RibbonX. Custom menus and toolbars are no longer supported in Mac Office 2016, but there is an Add-Ins ribbon tab to handle legacy menu items.

Microsoft is working on implementing RibbonX interface controls for Mac Excel, and it will be rolled out as part of a regular update. Until then a Mac Office add-in developer should still build legacy menu-type user interfaces.

Sandbox Mode

One more drawback is that Mac Office 2016 now runs in a sandboxed mode, so certain features are more difficult for developers to implement, features such as accessing folders and opening files. I don’t know the full ramifications of this behavior, since my code isn’t yet running properly in Mac Office 2016.

Bottom Line

Therefore, if you are using any add-ins to extend the features of Mac Office, you should not upgrade until you know whether these add-ins will work properly in Mac Excel 2016.

Peltier Tech Charts

Peltier Tech Chart Utility 2.0

Peltier Tech has taken steps to prepare for Microsoft Office 2016. The Windows version of the current Peltier Tech Chart Utility was designed for Office 2007 through 2013, and it can to run in the preview of Office 2016, but it will not work in the eventual commercial release of Office 2016. As always, there are a few minor changes that need to be made to accommodate new features and syntax of the new Office version.

Peltier Tech Charts for Excel 3.0

In addition, Peltier Tech is working on a major upgrade to the utility, called Peltier Tech Charts for Excel. This edition will work in Windows Office 2016, but also 2013, 2010, and 2007. It will also run in Mac Office 2016, when that has become capable of supporting add-ins, and of course, it will run in Mac Office 2011. The exciting part is that the same add-in file will work in both Windows and Mac, so users stuck between platforms will not need to license two separate add-ins.

The new Peltier Tech Utility will feature all of the old charts, including the ones that Microsoft is introducing in Office 2016, because some Peltier Tech customers will still be using older Office versions. New chart types are being developed, including grouped box plots and sensitivity tornado charts. If you have a favorite chart type that neither Microsoft nor I have made available, mention it in the comments below, and Peltier Tech will add it to the long long list of suggestions.

The new Utility will also offer some new features, including more powerful chart data manipulation tools. Again, if there’s a feature you can’t live without, let us know in the comments.

The new Utility will likely have Standard and Advanced editions like the current Utility, and these editions will likely be priced at the same levels as the current utility. Existing users of the current Utility will be able to upgrade at a 50% discount, and users of the older family of utilities will qualify for a 25% discount. In addition, bulk and academic discounts will be available.

Another exciting development is Peltier Tech Charts for PowerPoint, and in all likelihood Peltier Tech Charts for Word. These are still now in their infancy, but it will bring easier linking of standard PowerPoint and Word charts to Excel data, as well as at least a partial menu of Peltier Tech custom charts.

Peltier Tech Charts for Excel 3.0 Beta

So far the new utility is only in the alpha testing stage, meaning new pieces are being incorporated and tested in-house only.

In about a month a beta version will become available. Current and potential new users will be able to test and comment on it.

During the beta testing period, users of the current Utility will be able to lock in their upgrade at a discount of 60%, users of the older family of utilities at 35%, and new users at a discount of 10%.

More details will become available over the coming weeks.

Posted: Monday, July 13th, 2015 under Excel 2016.
Tags: Excel 2016, Mac and Windows, Utilities.
Comments: 22

Microsoft MVP Logo

Macs vs. PCs

Tuesday, September 23, 2008 by Jon Peltier 21 Comments

Chandoo has posted the new Microsoft Ads in Microsoft says ‘I am PC’ and it is brilliant. Contrary to my expectations, especially after the abortive Bill and Jerry spots, I enjoyed these ads. Microsoft finally has countered those insipid Mac vs. PC ads (see review in Slate), in a tasteful and clever way. Hey, I am PC too.

I thought I’d see just how PC my readers are. I checked my FeedBurner stats for the last 30 days, ending 9/21/2008, and here is the breakdown of operating systems.

PC vs Mac Blog Visitors

This fits pretty closely with my overall web site stats, too, which totals around ten times the traffic, but in FeedBurner it’s easier to get a quick list of numbers, and FeedBurner lists the versions of each OS.

So what’s all the buzz about Macs lately? Macs make up around 4% of my visitors. For every Mac visitor, I get more than 26 Windows visitors.

What’s all the buzz about Linux? Linux counts for 1% of my traffic if I round up.

Windows includes 95% of my traffic if I round down, and over 80% of that is XP. I guess the significant buzz is that under 20% of my Windows hits are from Vista.

I decided to compare the past month with the same month last year, which meant Google Analytics, since FeedBurner only goes back to March when I launched the blog. When I plotted the two months, the raw visits showed only that Windows and Mac visitors both increased, but there seemed to be a gain in percentage of visitors using Mac at the expense of percentage of visitors using Windows. (I neglected all the minor operating systems, but I will note that during each of these months I got two hits on Nintendo Wii.)

PC vs Mac Blog Visitors

This needed further investigation, so I collected data for the months in between. Raw visits showed a general up trend in Windows users, while Mac and Linux were lost in the weeds.

PC vs Mac Blog Visitors

On a percentage basis, Windows was up in the mid 90s, with a hint of a downward trend, and a gain, Mac and Linux were too low to resolve.

PC vs Mac Blog Visitors

Putting the data on a log scale flattened any trend in the Windows line, while it made Mac and Linus easier to see. There is possibly an upward trend in Mac and a downward trend in Linux. It’s sough to call, given the fluctuation in both lines.

PC vs Mac Blog Visitors

To evaluate the trends, I added trendlines. These do in fact show slight slopes in the directions indicated above. The slopes are very small, and correspond to changes on the order of a fraction of a percent per year. The R² values are also pretty low, indicating rather poor correlations.

PC vs Mac Blog Visitors

My conclusion is that Linux and Mac make up a very small fraction of my readership, averaging 1.3% and 3.5% respectively over the past year. Sorry, guys. No offense, but this supports my decision to concentrate on Windows versions of my utilities. My utilities are built in Excel 2000 and VBA 6, and so are compatible with Excel 2002, 2003, and even 2007. However, they are incompatible with the older VBA 5 found in Excel 97 and Mac Excel. In fact, Mac Excel 2008 doesn’t even support VBA at all.

Update 2015

Microsoft released Mac Office 2011 after this ancient article was posted. It included VBA, which had been removed from Mac Office 2008, and it had an uncustomizable ribbon to go with the usual Mac menu bar. I was able to write a version of my Excel Charting Utility that is compatible with Mac Excel 2011, and includes every feature present in the Windows version.

During this time, Mac traffic on my blog has increased, to over 5%, and sales of Mac versions of my software is around 10% of Windows sales.

Microsoft will soon release Office 2016 versions for both Mac and Windows. I am working on upgrades to my Utility, so that one add-in will work in both Windows and Mac computers. This is not just adapting the utility to Office 2016, there are also new features being added. Unfortunately changes in the Mac OS and in Office mean that I may not be able to completely mimic all Windows features on the Mac, but I am trying my darnedest.

Posted: Tuesday, September 23rd, 2008 under Statistics.
Tags: Chandoo, Mac and Windows, Trendlines.
Comments: 21

Microsoft MVP Logo