Design a site like this with WordPress.com
Get started

Excel – format formulas

Hello everyone.

Do you want to transform your formulas into clean, beautiful and readable ones? In the following tutorial, I will show how to do that.

Follow these steps :

I – Add the “Advanced Formula Environment” Add-in from the store

This add-in is developed by Microsoft

II – Copy your formula (the unformatted one) from the formula bar

III – Add the formula to the “Advanced Formula Environment” as shown in the picture bellow

IV – After clicking on “Add”, your formula will be added to your environment and beautifully formatted. You need just to copy/paste the formatted formula to the formula bar 


Hope that this tutorial will be useful.

See you next time 😉

Advertisement

Power BI – Oran 2022 Mediterranean Games Statistics

Dear readers, I share with you a report that I created, partially, during a training session that helps to read easily some statistics of the last Mediterranean games hosted by the very beautiful city of Oran in Algeria.

The link to open the report : Oran 2022 Statistics Report

IT IS Better to open this report in Desktop or Tablet.

The data provide from the competition official website (oran2022.dz). To extract the data, I used Power Automate Desktop

If you have a question let me know it.

I hope that this work will be useful and don’t hesitate to contact me if you have questions.

Excel – Translate numbers from digits to words in Arabic, English and French (no VBA)

numbers to words

Dear readers, hereafter you can watch a video on how we can create formulas in Excel to translate numbers from digits to words. You can also download the file.

I made this when I was working on a project for Excel dashboard and I decided to share it with the community. I know that many people want to do things in Excel without using VBA, like me 😁

The file is here, it has a password (you can enter a number and the currencies names), because the goal of this post is to learn how I did it, so the best way is to rewrite the formulas in order to understand them.

If you have a question let me know it.

Keep learning and see you next time 😊

Excel – Split a list separated by delimiter with functions only (no FilterXML)

Dear readers, hereafter you can watch a video that explain how to split a list of values separated by a delimiter by only using functions (no FilterXML).

This is an answer to a question from MrExcel video on Youtube, the Link, in which Mr Excel asked Excel team to add a build-in function that can split a list of values.

Many persons on the comment and on other forums says that we can do it only by using FilterXML function ! In this tutorial, I will show you how to do it with other functions :

If you have a question let me know it.

Keep learning and see you next time 🙂

Excel – Remove duplicates from 2 columns (also if values are permuted)

Dear readers, recently, on a forum, a person asked how to remove duplicates from 2 columns also if the values are permuted ! For example, (a, b) and (b, a) are a duplicate values of (a, b).

So, this is a quick trick on how to achieve that.

First, we combine the values into a new column :

Add “Combin” column

Next, we add a column in which we count the number of times a value and its inverse appear in the previous rows with the bellow formula, then fill down into the column
= COUNTIF ($D$2:D2, [@[Col 1]]&[@[Col 2]])
+ COUNTIF ($D$2:D2, [@[Col 2]]&[@[Col 1]])

Add “Nb value and inverse Previous rows” column

If we have the same value in “Col1” and “Col2”, the “Combin” value will be counted 2 times, so we divide according to the following condition IF([@[Col 1]]=[@[Col 2]],2,1). The new formula will looks like :

Add the division

And finally, to get the distinct list of values, we just need to keep the results that equals to 1.

Filter the results

If you have a question let me know it.

Keep learning and see you next time 🙂

Power BI – Analyze online job offers in Algeria (emploitic data)

Dear readers, I share with you a report that I created recently during a training session that helps to get an idea about online job offers in Algeria. The report will be daily updated (1 time per day).

The link to open the report : Emploitic data Report

https://app.powerbi.com/view?r=eyJrIjoiOGIwNWZhMzEtNWNiNi00MzZkLWE0NjItZTllN2NjNjY1MTA3IiwidCI6IjgzOTk4NzA1LTJlM2QtNDA3NC1hMGNlLTRiZGFiMDY0NWI5MiIsImMiOjh9

IT IS Better to open this report in Desktop or Tablet.

The data provide from emploitic website which is one of the most known employment website n Algeria. Its database can be used as a sample of jobs offers across Algeria.

If you have a question let me know it.

I hope that this work will be useful and don’t hesitate to contact me if you have questions.

Power Query – Permutations & Combinations – List all possible selections with Excel & Power BI

Dear readers, with this tutorial, you will learn how to create tables that list all selections made by Permutations and combinations.

Everything is explained in this video on youtube :

You can download the Excel file hereafter :

If you have a question let me know it.

I hope that this tutorial will be useful and don’t hesitate to contact me if you have questions.

Power BI – Bank of Algeria monthly balance sheet

Dear readers, I share with you a report that I created recently that helps to get easily the monthly balance sheets of the Algerian central bank with data starts from January 2014 until the last month available on the Ban of Algeria website.

The link to open the report : BOA balance sheets

https://app.powerbi.com/view?r=eyJrIjoiOGIwNWZhMzEtNWNiNi00MzZkLWE0NjItZTllN2NjNjY1MTA3IiwidCI6IjgzOTk4NzA1LTJlM2QtNDA3NC1hMGNlLTRiZGFiMDY0NWI5MiIsImMiOjh9

IT IS Better to open this report in Desktop or Tablet.

Unfortunately, on the website of the Bank of Algeria we cannot get the information as a table or a database but on PDF files. The data is updated monthly but with a delay of 4 or 5 months. Therefore, I decided to create a report that extract information from the files in order to help people better access to the data and to make the comparison between year and month easily. I added some statistics analysis to help readers to analyze the data.

If you have a question let me know it.

I hope that this work will be useful and don’t hesitate to contact me if you have questions.

Power BI / Sankey diagram – Algerian finance law (Resources and Budget)

Dear readers, I share with you a report that I created recently about the resources and expenditures (budget) in the Algerian finance law from 2012 to the last available fiscal year.

The link to open the report : FL Budget Report

https://app.powerbi.com/view?r=eyJrIjoiOGIwNWZhMzEtNWNiNi00MzZkLWE0NjItZTllN2NjNjY1MTA3IiwidCI6IjgzOTk4NzA1LTJlM2QtNDA3NC1hMGNlLTRiZGFiMDY0NWI5MiIsImMiOjh9

Better to open this report in Desktop or Tablet.

Unfortunately, the Algerian Ministry of Finance doesn’t give the information as a table or a database but on PDF files for each year. Therefore, I decided to create a report that extract information from the files in order to help people better access to the data and to make the comparison between year easy. I added a Sankey diagram (or flow diagram) to make the analysis easy.

If you have a question let me know it.

I hope that this work will be useful and don’t hesitate to contact me if you have questions.

Power BI / OCR online – DZD daily exchange rate from BOA

Dear readers, I share with you a report that I created recently about rate exchange of the currency of my country, the Algerian dinar (or DZD) against the main world currencies used by banks and Algerian companies.

The link to open the report : BOA daily spot Report

https://app.powerbi.com/view?r=eyJrIjoiNjMyOTA4ZWItYjA2Zi00NWNkLTliNTAtNDY0MDEyYjM5ZGUyIiwidCI6ImIyMmYxNTQxLTllNDYtNGFjYS05OTUzLWZkMTA3ZGExZjdiZiJ9

Better to open this report in Desktop or Tablet.

Unfortunately, the central bank of Algeria (BOA) gives the exchange rates on an image table, so many analysts waste a lot of time to daily rewrite the tables on an Excel sheets ! Therefore, I decided to create a report that use an OCR engine to read the data from a scanned report table. This report was created throw 4 steps:

  1. Extract images URLs from the BOA website.
  2. Use a free api from http://ocr.space/ website. A very power engine to read data from image text.
  3. Extract data and load them on the report (there can be some errors)

If you have a question let me know it.

I hope that this work will be useful and don’t hesitate to contact me if you have questions.