Blog: Excel

We’ve been busy over the last few months adding various new features and improvements to Analyse-it.

There are always many changes behind the scenes, which aren’t really news-worthy, but seek to make Analyse-it compatible with the latest changes to Microsoft Excel and Windows. Microsoft have recently embraced a more aggressive continuous-release deployment model with Office and Excel, and these releases have caused quite a few headaches for developers, notably with the .

For these reasons we advise you have active maintenance so you get these updates as they are released – if not we recommend you on your license to get the latest updates and ensure compatibility with the often fast-paced releases.

Starting with version 5.20 we introduced a new feature so you can set the scale (minimum, maximum and units) for a variable that will then be used, where possible, to set the scaling for chart axes. Normally Analyse-it will choose excellent axis scaling, but sometimes you know better and want a specific minimum, maximum, or units. Previously you would have to set the scale manually on the charts output from the analysis, and any changes would be lost on recalculate. Now you can control the scaling, see

Update 19-Sep-2019: Unfortunately this continues to be an issue for some users and unfortunately there is currently no solution from Microsoft except to suggest the use of compatibility mode as detailed below. We have requested this be fixed, so please up-vote it at

Update 27-Jun-2018: Although we have a fix for this issue on an internal build, it appears that Microsoft Office version 1807 (which is currently only available on the Office Insider track) fixes this issue. The missing user-interface problem was caused by a bug in Microsoft Office 1805/1806 updates. We will release our fix shortly, but the 1807 version update will also become available to everyone over the next month or so. If you want to get it immediately see .

Microsoft has recently released updates to both and to provide support for multiple monitor high DPI (dots-per-inch) displays.

In the early days of Microsoft Windows, monitors were assumed to have 96 DPI and all applications worked on that assumption: with a user interface fixed on that assumption. In the last 15 years, monitors with higher DPI have started to appear with the benefit that on-screen text and graphics look much smoother because there are so many more dots per inch. That caused problems for many applications which were fixed to assume 96 DPI, causing their user interface to scale improperly on high-DPI monitors. Applications like Analyse-it supported high DPI monitors and adjusted their user interface appropriately... until now.

Microsoft officially released a couple of days ago, and Analyse-it version 4.20 now adds support for Excel 2016.

Over the next few weeks we will tweak the Analyse-it user interface so it matches the new Office 2016 user interface styles. Line styles on the plots in Excel 2016 now also appear a lot thicker, due to anti-aliasing (smoothing), so we will decide whether to address that in a future update – let us know what you think.

If you have you can download and install the update now, see . If maintenance on your licence has expired you can renew it to get this update and forthcoming updates, see .

In clearly titling this blog post, we’ve probably already revealed the answer, but... Can you spot the difference between the two rows of values in the Excel spreadsheet shown below?

Sorry, it’s a trick question, because (visually) there is no difference. The difference is how the values are stored by Microsoft Excel. The value 57 in the cell on second row is actually stored as a text string, not a number.

When you type a value into a cell, Excel looks at what you’ve typed and decides whether it’s a valid number. If it is, the value is stored as a number, and if not it’s stored as text (a string of characters).

Considering this, how is it possible for Excel to store a value that looks like a number, as text? There are a few ways. Most common is when you copy-paste data from another application, and the application providing the data fools Excel into believing the values should be stored as text. Similarly, if you import data from a database field that contained numbers stored as text, the numbers will be imported as text. Finally, you can force Excel to store a number as text by prefixing it with an apostrophe (‘).

A customer contacted us last week to ask how to refer to cells on an Analyse-it report worksheet, from a formula on another worksheet. The customer often used Analyse-it's refresh feature, to repeat the statistical analysis and update the statistics, and direct references to cells on the report were being lost on refresh.

As an example, suppose you have used Analyse-it linear regression to calculate the linear relationship between installation cost and the number of employees required, distance to the site, and the cost of machine being installed. Analyse-it would calculate the effect of each variable on the final cost, technically known as regression coefficients, which you can then use to predict installation costs for jobs in future.

You might setup a worksheet to predict and quote installation costs for future jobs. You could use an Excel formula to reference the coefficients directly from the Analyse-it report, for example:

= Employees * CostAnalysis!C17 + Distance * CostAnalysis!C18 + MachineCost * CostAnalysis!C19

Although the charts in Analyse-it are large so they’re easy to read when printed, sometimes you need to print a chart to fill the full page. You can do so easily, without resizing the chart, in just a few steps:

Chart size is only limited by the page size your printer supports.