change language:

This article provides some advice on how to avoid compatibility issues. An overview is also given of a number of important differences between the various Excel versions. This does not concern differences in ease of use or operation, but in particular differences that can lead to compatibility problems.

Dealing with compatibility issues

Many different versions of Excel have already been released. When a new version is released, an attempt is made to ensure that it is "backward compatible" as much as possible, which means that it is possible to read older versions and edit them in the newer version. This is generally quite good, only with macros / VBA this can sometimes cause problems. If applications are developed that must also be suitable for older versions, then here are a few points to consider:

  • New functionality will obviously not work in older versions. This also applies to VBA code based on this new functionality. A next section provides an overview of new functionality per version.
  • If in Excel 2007 or higher the file is saved in an Excel 97-2003 format by means of Save If, ​​an automatic check for compatibility problems follows. Macros / VBA are not checked. This compatibility check can also be started via File → Info → Check for Issues → Check Compatibility.
  • With Application.Version the Excel version used can be read with VBA. If desired, specific VBA code can be created per version.
  • Make use of late binding in VBA. This makes used libraries independent of the version number of this library and prevents them from not being loaded in older versions.
  • Try to make VBA code version independent as much as possible. For example, use Rows.Count instead of "hard" coding the last row of a worksheet (65536 for Excel 2003 and 1048576 for Excel 2007 and higher).

32-bits versus 64-bits version

From Excel version 2010, a 32-bit and a 64-bit version of Excel is available. The 64-bit version does not offer any additional functionality over the 32-bit version, but the 64-bit version is able to address larger memory ranges and can run faster. In the vast majority of cases, the 32-bit version is fine. The main drawback of using the 64-bit version is that it can cause compatibility issues with previous versions. This only applies if VBA/macro's are used. For example, not all (ActiveX) controls of the 32-bit version are supported in the 64-bit version (e.g. the TreeView, ListView, ImageList, Slider etc.). In addition, adjustments will have to be made to the VBA code in case so-called API calls are used.

Overview of differences in functionalities between Excel versions

In this and the next section, a number of important differences in functionalities between the various Excel versions are mentioned. Excel version 2003 is used as the basis for the comparison. An indication is given of what has changed in the 2007, 2010, 2013, 2016, 2019 and 365 version compared to the 2003 version. This does not include the 64-bit versions and the versions for the Mac, tablets and phones are also disregarded. The differences between the different service packs of the versions are also not discussed. The overview is not complete and it is not the intention to present a complete overview. That is almost impossible. The list would then become very long and very detailed. This only concerns a number of important differences.

Subject 2007 2010 2013 2016 2019 365¹)
General
1,048,576 rows instead of 65,536
16,384 column instead of 256
menu operation by means of ribbon
file extension xlsx/xlsm/xlsb instead of xls
single document interface (SDI instead of MDI)
sort data on more than 3 levels
table instead of list
table slicers
sparklines
flashfill
dynamic array formulas
Formatting
16 million colors instead of 256
themes and styles
Conditional formatting
more than 3 conditions
can refer dirctly to other worksheets
Pivot tables
slicers
timeline slicer
count unique values (distinct count)
repeat item labels or field labels
Graphs
box- & whisker
histogram
chartgraph
pareto
funnelgraph
waterfall
VBA²)
property CountLarge
property DisplayFormat
event Workbook_AfterSave
command Application.FileSearch

¹) Whether or not something is available in Excel for Office 365 depends on the type of subscription

²) All functionalities newly introduced in a version also have consequences for VBA, of course, but that will not be considered here.

Overview of which function is available in which version

Function 2007 2010 2013 2016 2019 365¹)
AGGREGATE
AVERAGEIF
AVERAGEIFS
BASE
COUNTIFS
DAYS
DECIMAL
IFERROR
IFNA
IFS
FILTER
FORMULATEXT
ISFORMULA
ISOWEEKNUM
LAMBDA
LET
MAXIFS
MINIFS
MODE.SNGL
MODE.MULT
NETWORKDAYS.INTL
RANDARRAY
RANK.AVG
RANK.EQ
SEQUENCE
SHEET
SHEETS
SORT
SORTBY
SUMIFS
SWITCH
TEXTJOIN
UNIQUE
WEBSERVICE
WERKDAY.INTL
XLOOKUP
XMATCH
XOR

¹) Whether or not something is available in Excel for Office 365 depends on the type of subscription

Questions / suggestions

Hopefully this article has helped to better understand the compatibility between Excel versions. If you have any questions about this topic or suggestions for improvement, please post a comment below.

arrow_up