Generation of MS Excel spreadsheets with conditional formatting in Bitrix24

Work planning, production, distribution and control of tasks are an important component of any business where terms and deadlines play a key role. There are many examples: logistics, project or event management, education or medicine — the list goes on and on. Using Bitrix24 as a tool for planning and control greatly facilitates the work of everyone: from the manager, who globally sees the whole picture of events and the stage of progress, to ordinary executors, who always know what and when they need to do. However, there are cases when performers do not need to work in the portal, but simply follow the proposed tasks, and most often — do it completely offline. And this is where software comes to the rescue, which we have already mentioned more than once as an excellent addition to Bitrix24 — MS Excel. More precisely, the use of conditional document formatting of electronic spreadsheets generated using Bitrix24. In practice, this method was implemented by the developers of the Avivi company as part of one large project.

Tasks and Gantt chart

Before sharing the experience, it is worth giving an example of the Gantt chart implemented in Bitrix24, which significantly optimizes work with tasks. It is a handy tool that solves problems with time allocation for tasks and deadlines. It is good that this method does not require additional modules or plugins and is available to users of portals with any tariff.

Tasks are presented in the form of visualized blocks directly in the calendar grid; the left edge of the block shows the beginning of the task, and the right — its completion. The deadline is marked on the block with a vertical red line, and if the task is overdue, everything to the right of the deadline will be filled in red:

Task 1 — Overdue task. The vertical red line is the deadline;

Task 2 — A task started two days ago with a due date (November 2nd). This is a task that has subtasks;

Task 3 — Subtask. Should be finished by the end of the day today;

Task 4 — Current tasks without a deadline;

Task 5 — A task that is planned for the future.

Detailed settings can color different colors for working days, weekends and holidays, as well as color the period when the employee kept records of working hours while performing tasks. In the Gantt chart, you can link tasks to each other, define main and subtasks, change lines and much more. This method is very convenient thanks to visualization and quick editing of the entire process.

Solutions for MS Excel

Conditional formatting, according to the developers' idea, in MS Excel is used in research with data analysis, to find problems and clarify trends. However, these properties of the program are quite suitable for creating the possibility of monitoring tasks, like a Gantt chart. Avivi developers took advantage of this, because conditional formatting helps to highlight cells or ranges that are of interest to the user, and visualize data using the standard functionality of a table editor — i.e. color, merge, highlight, and much more.

The most difficult part of this process is to "teach" Bitrix24 to create appropriate MS Excel spreadsheets according to the given logic. The functionality of Bitrix24 allows you to generate documents of various complexity. However, the phpspreadsheet generator was used to correctly create documents with built-in formulas.

How does it work together?

To create a complex document, the user needs to upload information about all future tasks to Bitrix24. Next, a document generator is included in the process, which at the input receives a prepared template with one cell, on the basis of which an MS Excel spreadsheet with conditional formatting of formulas is created. The grid of the table will be built from the very first task to the last in order. Time frames will be presented as blocks consisting of painted cells. There is also a vertical line in the document that shows today's date.

And then the document begins to live its life without access to the global network. Every day, or rather, every time the user opens it, changes will occur, cells will be filled, the "date change line" will move and much more. The work of the formulas will provide the user with a visualization of all current processes, similar to a Gantt chart in Bitrix24. Perhaps the only drawback of this method is binding the document to the date: if the user's computer is offline and the date/time settings are messed up, MS Excel will take the date that the operating system provides to the program.

Wide range of applications

As you can see, you can adapt an MS Excel spreadsheet with conditional formatting of formulas for many areas of business. Improvements to Bitrix24 functionality are possible both for the box version and for the cloud edition. In the first case, everything is solved by customization, in the second — by creating a special application. Of course, in each case it is an individual development for the needs of the client and the document he needs.

Thus, we once again made sure that MS Excel cannot replace Bitrix24, but perfectly complements this product and solves related business tasks. And if you need to create spreadsheets with conditional formatting, our specialists will gladly help you implement it!