Category: Advanced

Round to the nearest tenth, hundredth

With Excel, it’s very easy to round to the nearest tenth, hundredth, thousandth, …. Standard use of the function ROUND The function ROUND is a very common Excel function. You use it when you want to round a number. =ROUND(number, decimal) Number is your number Decimal is the number of decimal you want to keep …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/round-to-the-nearest-tenth-hundredth/

Mortgage calculator with Excel

In Excel, you can easily create a mortgage calculator with the PMT function Explanation of the PMT function PMT calculates the PayMenT for a loan for a constant interest rate. The arguments of the function are Rate    The interest rate for the loan. Nper    The total number of periods for the payments for the …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/mortgage-calculator-with-excel/

First day – Last day in Excel

On this page, you will find some formulas to calculate specific days automatically. Building specific dates, such as the first or last day, is always a very complex task, in Excel or any other software. The function TODAY has been used in all the examples but you can replace it with your own date value. …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/first-day-last-day-in-excel/

Negative numbers with parenthesis – Excel

Negative numbers in Excel In Excel, the basic way to format negative numbers is to use the Accounting number format. This option will display your negative number in red.   But, for some reports, negative numbers must be displayed with parenthesis. Let’s see how to do that Customize your number To display your negative numbers …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/negative-numbers-with-parenthesis-excel/

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/

Inspect a formula with F9

In Excel, you can scan a part of your formula with the shortcut F9 When use the shortcut F9? Let’s say you have a complex formula with a lot of VLOOKUP, INDEX, MATCH, Reference all over your workbook. The result is not correct and you need to find the reason To split your formula in …

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/