Splitting columns

For a certain period of time, when I need to split data in a column, my first choice is to use the Power Query add-in and the Split Columns option. However, there are still users who do not know how to use it, so I remembered that for this purpose we can also use the Excel option Text to Columns. In this “recipe” you will find out a practical way how to split one column into several.

Consolidated list of products

Imagine this scenario: you register customers in an Excel spreadsheet, specifying their ID and then the products they use. Which customer uses which products? One way to get this answer is to create a pivot table, and then display customers, products and the number of products. The disadvantage of this report is that pivot tables always aggregate data. What if we only need a table with customer ID in one column and a consolidated list of products in another?

How old is the owner of JMBG?

The unique identity number of citizens (JMBG) was created in the Socialist Federal Republic of Yugoslavia, and is still used today in the Republic of Serbia, for the purpose of identifying citizens. It is a mandatory datum in the identity card, driver’s license, health card, passport and all other important documents. Its first 9 digits indicate the date of birth. How to calculate the age of the JMBG holder based on this data?

Office Scripts

For years, Microsoft has been trying to replace VBA (Visual Basic for Applications), the programming language that “lives” in the background of Excel. As they say, the reasons are of a security nature. This is not entirely without merit, but it seems to me that the motive of the Excel development team, to a greater extent, was to create scripts that would fit better into the Microsoft 365 and Power Platform ecosystem. Whether they succeeded in this is up to you to try.

3D charts

A large number of Excel charts also have their 3D version. Although in most cases this is just unnecessary embellishment, making it difficult to draw conclusions, sometimes 3D charts really do make sense. In the text that follows, you will find out how to make this type of chart, as well as how you can change the perspective of looking at the data…