Category Archive: Expert

Create an Offset Vlookup

In the case you have a table that contains the same ID for many – or empty cells – it is strongly recommended to reorder your data. To perform this modification, we will use 3 functions , INDEX , MATCH and OFFSET. Problem to be solved We have a table (column A:D ) that lists the sales …

Continue reading »

Create an online survey with Excel

Since it is possible to use Excel from a web browser, new special features are available with Excel Online. This is particularly the case for creating, posting and collecting the results of a questionnaire. And not only is it very easy to implement a surver but it is free. Click on this link to see …

Continue reading »

How to round in k$ without formula?

In many companies, it is difficult to present in a worksheet, big numbers. So, to avoid that, it is better to present  your figure rounded in kilo dollar (k$) or million (m$). But how to round in k$? In this article you will find 3 techniques to display a result in k$ (or m$ for the millions]. Each …

Continue reading »

How to Lookup Values to left column?

The VLOOKUP function searches for data in a table by returning data always to the right of the column containing your ID. However, it is sometimes necessary to us to return the value that is located into the left columns of your ID. As the VLOOKUP function is not able to do, we can solve the problem …

Continue reading »

How to make automatic calendar in Excel

  Creating a new calendar each month is a waste of time, but many people do as they think they need to rebuild it to get correct weekends and public holidays. But in fact, with few formulas, 2 conditional formatting rules, 1 dropdown list and 5 lines of VBA code, you can create an automatic …

Continue reading »

Creation of a population pyramid

It is common to have to create a population pyramid in Excel, in order to identify the distribution of its customers. Unfortunately, Excel doesn’t have this pattern in the Chart’s ribbon. Nevertheless, you can create your own population pyramid chart by following these steps.   The data for a Population Pyramid Data for a population pyramid …

Continue reading »

Calculation of tenths, hundredths and thousandths with Excel

3 Thousandths Looking at results of speed skating in the Olympic winter games at Sochi 2014, I found that the gap between the first and second of the 1500m event was only 3 thousandths. Decide athletes with thousandth is extremely unusual and this give me the idea to measure the gap between them. Why and …

Continue reading »

XLS or XLSX file ?

With the release of Excel 2007, extensions of Excel files (or Office in general) were changed. Previously, it was possible to save workbooks in xls. Since Excel 2007 you can also save your workbooks in xlsx or xlsm. It is now 6 years since extensions xlsx and xlsm were created but many people are still saving their Excel file with the xls extension. This article will …

Continue reading »

Change the language of Microsoft Office

In most of companies, Office is in English. But if you don’t feel comfortable with the functions name in English, you can easily switch the language of your Excel application to any other language. You just need to install an new Office Language pack. In this page, you will download the most common Language pack …

Continue reading »