Category Archive: Expert

Convert Latitude and Longitude

Nowadays, GPS localization is a common data. Some apps accept the decimal writing (48.85833), other return the coordinates in degree, minute, second (48°51’29.99”). In this article you will see how to convert one writing to the other and vice-versa. How to write a coordinate is Excel GPS coordinates are written with the symbols generally write …

Continue reading »

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

Keep the last update of your data

Too many data for the same contact Let’s start from this file where we have different informations about our clients. Sometimes, we have many different data 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 …

Continue reading »

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

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/

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 solve We have a table (column A:D ) with the list of sales …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/create-an-offset-vlookup/

How to Lookup Values to left column?

Problem of the VLOOKUP function The normal behavior of the VLOOKUP function is to return data from a table. But you can only return data on the right of the column containing the ID. However, in some cases, you can not change the order of the columns. Like for instance in this document where the …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/how-to-lookup-values-to-left-column/

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 »

Permanent link to this article: https://www.excel-exercise.com/creation-of-a-population-pyramid/

Relationship between tables

 

Permanent link to this article: https://www.excel-exercise.com/relation-between-tables/

Power View

Permanent link to this article: https://www.excel-exercise.com/power-view/

Conditional Formatting – Highlighting dates

With Date functions in Excel, it is possible to perform calculations like addition or subtraction and thus, produce automated or semi-automated worksheets (using the NOW function in particular). Date functions mix with conditional formatting, allow you to create spraedsheets displaying date alerts automatically when a deadline is near. Basics of Conditional formatting with dates In the menu Home …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/highlighting-dates/

Population Pyramid

It is common 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. But you can create easily a population pyramid chart by following these steps. The data for a Population Pyramid When you want to draw a population …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/population-pyramid/

Older posts «