Frédéric LE GUEN

Most commented posts

  1. How to make automatic calendar in Excel — 26 comments
  2. Date format in Excel — 5 comments
  3. NOW & TODAY — 4 comments
  4. Function COUNTIF — 3 comments
  5. Split Time and Date — 3 comments

Author's posts

Negative numbers with parenthesis – Excel

Negative numbers in Excel In Excel, the basic way to format negative numbers is to use the Accounting number format. This option will display your negative number in red.   But, for some reports, negative numbers must be displayed with parenthesis. Let’s see how to do that Customize your number To display your negative numbers …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/negative-numbers-with-parenthesis-excel/

Pivot Table – Presentation

A Pivot Table is a powerful tool which calculates or aggregates data very easily. For many people, a pivot table is a complex tool for advanced users. But not at all. It’s just a tool to aggregate your data. It’s really simple and all the calculation are made by the tool. You, you just have …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/pivot-table-presentation/

Gantt chart

What is a Gantt chart? A Gantt chart helps you to visualize your project schedule with the different tasks and their dead-line. Download the file If you don’t want to follow the next steps and open the file with the Gantt chart already done, download the following document. Your data To create a Gantt chart, …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/gantt-chart/

Time format in Excel

In Excel, working with the time is not difficult but you need to know few rules to avoid big mistakes. In this article you will see these rules and how to manage the time format. Difference between date and time in Excel In Excel, hours are always a fraction of a day. So it’s decimal …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/time-format-in-excel/

Dynamic SUM in Excel

Create a SUM is something very easy with Excel. But when you want to add only some specific cells and not all of them, the formula is not so easy but it’s possible. Let’s see how to create a dynamic SUM.   Insert your data in a Table But before to explain how to create …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/dynamic-sum-in-excel/

Anonymise your data

If you work on a workbook with confidential data, you need to anonymise your data if you collaborate with other people. The technique is not really complex but you have to respect the following steps. The initial document Imagine you are a journalist and you receive the following file in your mailbox (it’s all fake data). …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/anonymise-your-data/

Date format in Excel

In Excel, you can display the same date in many different ways just by changing the date format 01/01/2018 Jan 2018 Mon, 1 Jan 2018 … What is a date in Excel? Dates are whole numbers Usually, when you insert a date in a cell, you have this result Now, if you change the cell’s …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/date-format-in-excel/

Inspect a formula with F9

In Excel, you can scan a part of your formula with the shortcut F9 When use the shortcut F9? Let’s say you have a complex formula with a lot of VLOOKUP, INDEX, MATCH, Reference all over your workbook. The result is not correct and you need to find the reason To split your formula in …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/inspect-a-formula-with-f9/

Convert Latitude and Longitude

Nowadays, GPS localization is in common usage. Some apps use decimal format (48.85833), others return the coordinates in degrees, minutes and seconds (48°51’29.99”). In this article I will show you how to convert one format to the other and vice-versa. How to write a coordinate in Excel GPS coordinates are generally written using the symbols ° (degrees), ‘ …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/convert-latitude-longitude/

Keep your last data update

Too many data entries for the same contact Let’s start from a file where we store client information. Sometimes, we have many different data entries for the same customer. We want to keep only the newest information for each customer based on the column Update. Remove Duplicates not applicable With a such file, you can’t …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/keep-last-update-data/