Frédéric LE GUEN

Author's details

Name: Frédéric LE GUEN
Date registered: 25/12/2012

Latest posts

  1. Time format in Excel — 17/10/2018
  2. Dynamic SUM in Excel — 17/10/2018
  3. Anonymise your data — 16/10/2018
  4. Date format in Excel — 02/08/2018
  5. Inspect a formula with F9 — 07/04/2018

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 listings

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

Date format

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, there is some amazing shortcuts and F9 is one of them Let's say you have a complex formula with a lot of VLOOKUP, INDEX, MATCH, Reference all over your workbook. And you have a mistake Not easy to find where is the mistake. Except if you use the shortcut F9 😏 When do …

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/

What is a logical test in Excel

When should you create a logical test? Creating a logical test is THE starting point for these 3 important functions in Excel: IF COUNTIFS SUMIFS But it is also used for conditional formatting. Yes, you can automatically change the color of your cells according the result of a test. What it a logical test Logical …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/logical-test-excel/

The correct value of the week number

The function WEEKNUM In Excel, to return the week number of a date you have the function WEEKNUM. =WEEKNUM(Date) Easy ? Sure ? 🙄🤔 Well in fact, it's not so simple. It depends if you are in USA or in another country. The rule of calculation for the week is different between the USA and the …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/correct-value-week-number/

Add days without the weekend

Common error when adding days When you build a schedule for your team, you mustn't include the weekend in your result. So you can't write a formula like this =B2+C2 Look at the first results! 🧐🧐🧐 Even if the first result looks correct, it isn't. The result is Monday but it should be Wednesday. If …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/add-days-without-weekend/

Older posts «