Category Archive: Advanced

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/

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/

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/

Compare 2 columns

How to compare 2 columns in Excel?  It's very simple 😄😎👍 I will show you in this post how to do this with the functions VLOOKUP for the research ISNA for the test IF to customize the final result 😉 But first, let's start with a live demo 💛❤💙💜💚 Live demo In this worksheet (yes, it's …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/compare-2-columns/

Function DATE - YEAR - MONTH - DAY

Presentation of DAY, MONTH, YEAR The Functions YEAR, MONTH and DAY are very simple to understand. They extract each part of a date. DAY extracts the day of a date MONTH extracts the day of a date YEAR extracts the day of a date Individually, these functions do not interesting but used with the DATE function, they are …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/function-date-year-month-day/

Extract without duplicates with formula

It is very easy to extract without duplicates data from a table through the menu Data>Remove duplicates. It works great for one or more columns but the thing about this tool is that you can not control data that will be delete. I recently worked on a customer address file by keeping only the most …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/extract-without-duplicates-with-formula/

Weekends or weekdays in Excel

A basic job with Excel is to easily isolate the weekends and the weekdays. This is the case when you hace to build an automatic calendar for example.  The function WEEKDAY Presentation This function returns a value between 1 and 7 corresponding to the value of the day in a week. =WEEKDAY(Date,Parameter) This function is …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/weekends-weekdays-excel/

Create an online survey with Excel

Since it is possible to use Excel from a web browser, new special features are available with Excel Online. This is particularly the case for creating, posting and collecting the results of a questionnaire. And not only is it very easy to implement a surver but it is free. Click on this link to see …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/create-an-online-survey-with-excel/

How to round in k$ without formula?

Introduction of the article In Excel, you can easily transform your number kilo euros k$(or dollar) or million euros(m$) with 3 methods.  In this article you will find 3 techniques to display a result in k$ (or m$ for the millions]. Each technique have their own benefits and disadvantages. Honestly, the last one is the greatest. Paste Special …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/how-to-round-in-k-without-formula/

Older posts «