Spreadsheet best practices for great apps

Over the last few years we have developed and debugged thousands of spreadsheets. All these were meant to be converted into HTML, JavaScript and CSS but they were not necessarily ready for such process. This page summarises our common habits when developing Desktop spreadsheet tools. But it is useful to rework a few elements if your goal is to have a user-friendly and fast web application.

It is not about starting all over again. You we will see how simple things can change the way your app look and feel to the end user.

Checklist

Here are our main takeaways for improvement. All points are further developed bellow:

  1. Split interface and calculations in different tabs,
  2. Display element vertically,
  3. Signal input with a cell background colour, use the minimal amount of colours,
  4. Avoid background colour,
  5. Stick the user interface left and top, do not use empty columns or rows,
  6. Clean your spreadsheet: #REF, #DIV/0, POWER function instead of ^
  7. Appizy limitations: macros, images, pivotal table.

Split interface from calculations

What’s usually done?

We develop the interface (where inputs are set) and the intermediate calculations side-by-side. Lookup data ends on the side, a few columns aways from the main interface When time comes to show the spreadsheet to the world, the intermediates calculations are hidden (columns or row). Sometime the font is set to white on white background.

How to change this approach?

The first tab should be the end user interface. No columns or rows are hidden. Everything is here.

The more you hide in secondary tabs, the faster the application will be.

Move all the calculations in a second tab rather than in hidden rows or columns. When you are done with the development of your tool simply hide the tab. Your final application will be faster. Moreover you no longer need to wonder where is the intermediate formula that you put in cell AZ12 with white font several weeks ago.

Vertical element organisation

Think about Facebook or any other social media. We are used to scroll and understand content from top to the bottom. Your tool should reflect this vertical organisation.

Cells colours: keep it simple and clear

As a spreadsheet tool developer we know what inputs are necessary. We know where to find what and how to complete it. For the end user everything is new!

You should guide the user actions with some colours. Use the same colour scheme as the website where you want to embed your tool. But do not overuse paint for your tool. It could become to fuzzy and complicated to follow.

Start with two colours: one for the user input and a second to emphasise main results. Remember: less is more!

Same goes for the background color. Appizy generates transparent tools. Your app will get the background from the site where it is embedded.

No empty columns or rows

What’s usually done?

Depending of the size of their screen people tend to center the tool (= the user interface) in the middle of their screen. To do so, they had empty columns left from the inputs zone and some empty lines above.

How to change this approach?

Your future user interface should stick to the top left corner of the tab. No empty space. Once again, when you embed the tool you can adjust with some CSS. But carrying white space with the app itself will make things more complex afterward (especially for mobile display).

Clean your spreadsheet calculations

Excel or OpenDocument spreadsheet are more permissive in terms of calculations. They automatically handle and correct minor errors. The interactive web application made with Appizy is written in JavaScript. This language allows less flexibility and you need to pay attention to a few elements if you want everything to run smoothly:

Check formula references. Your spreadsheet should have not #REF displayed anywhere. Either remove or fix the formula, Remove division by 0 (warning: #DIV/0). In this case, use conditional calculations or add a default value to avoid such problem, If you need to calculate a^b (a to the power of b), use the POWER function: POWER(a; b).

Appizy limitation

Appizy algorithm gets better everyday. Even if we want to extend the capabilities as fast as possible there are still some limitations you must be aware of. This component won’t be converted into the and web application:

  • Images: we choose to ignore them in Appizy. We consider that the tool illustrations (ex: your logo) are around the tool itself,
  • Macro or pivotal tables will not be ported to Appizy.