Frédéric LE GUEN

Author's details

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

Latest posts

  1. Inspect a formula with F9 — 07/04/2018
  2. Convert Latitude and Longitude — 07/01/2018
  3. Keep your last data update — 31/12/2017
  4. What is a logical test in Excel — 19/12/2017
  5. The correct value of the week number — 15/12/2017

Most commented posts

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

Author's posts listings

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/

Mixed reference

Mixed reference A mixed reference is a reference that is fixed only on part of the reference: either the row or the column Before showing you an example of a calculation using mixed references, we will detail the use of the $ symbol in a reference. An absolute reference has two $. There is one …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/mixed-reference/

Use the value of a cell in Excel

In Excel, it's very easy to use the value of a cell. You just have to call the reference of a cell. What is the reference of a cell? In Excel, a cell is often called by its references. In other word, the intersection of a row and a column, like A1, B5, D8,...     …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/use-the-value-of-a-cell-in-excel/

Function XOR

Function XOR is a new logical function that corresponds to the exclusive OR What is the XOR function? In logic, there are two ways to specify an OR context The inclusive OR. One or more of its parameters are true and the result of the test is TRUE, it is the function OR The exclusive OR. One and …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/function-xor/

Paste Special

There are situations where a standard copy-paste destroys the presentation of your worksheet. To avoid that it's better to use the Paste Special. Principle of paste special Once the "copy" has been activated, you can display a specific dialog box that offers a wide range of collages. To enable this option, you must click on the arrow …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/paste-special/

Older posts «