Frédéric LE GUEN

Author's posts

Gantt Chart

What is a Gantt chart? A Gantt chart helps you to visualize your project schedule containing different tasks and their deadlines. Your data To create a Gantt chart, you must have a table with: Task names Start dates Effort (number of days needed to carry out each task) Step 1: Return the End Date (based …

Continue reading

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

Time Formats in Excel

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

Continue reading

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

Dynamic SUM in Excel

Example dynamic CUM Exce

There is 2 methods to create a dynamic SUM in Excel.
The first one uses the technique with a Table, the second one uses the function INDEX.

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

Anonymise your data

If you are working on a workbook containing confidential data, you need to anonymise your data if you are collaborating 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 …

Continue reading

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

Date Format in Excel

What is a date in Excel? In Excel, you can display the same date in many different ways just by changing the date format. Dates are whole numbers Usually when you insert a date in a cell it is displayed in the format dd/mm/yyyy. Now if you change the cell’s format to Standard, the cell …

Continue reading

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

Move and Copy worksheets quickly

In Excel, there are a lot of tips and tricks to move or copy a worksheet. Here are some examples: Move sheets in a workbook To move a worksheet within a workbook, you just have to: Click on the tab of the worksheet you want to move Hold and drag your mouse Release the mouse button …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/move-and-copy-worksheets-quickly/

Inspect a Formula with F9

In Excel, you can scan part of your formula with the shortcut F9 When use the F9 shortcut? Let’s say you have a complex formula with a lots of VLOOKUP, INDEX, MATCH, references. But the result returned is not correct and you need to find the reason. Tips: To display your formula on multiple rows, …

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 common. Some apps use decimal format (48.85833) while others return the coordinates in degrees, minutes and seconds (48°51’29.99”). In this article I will show you how to convert from one format to the other and vice-versa. Add GPS coordinates to address If you are looking to add GPS coordinates to an address, please …

Continue reading

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

Keep your Last Updated Data

Keep the last updated row Let’s start from a file storing customer information. Some customers have multiple records in the file, as a new record was created each time they changed their address or phone number. How do you keep only the last updated row? Remove duplicates not applicable With a such file, you can’t …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/keep-your-last-updated-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 What it a logical test Logical tests are everywhere. Is my salary higher than my colleague’s? Is my rent higher than my neighbour’s? Is the quantity in stock larger now …

Continue reading

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