 |
With Excel, it is easy to extract characters from a cell.
In the following article, we will see how easily it is to extract characters from the left, the right or anywhere in a cell.
In the next few days, we will see how to extract more complex parts with other formulas.
To complete the COUNTIFS function , the SUMIFS function allows you to add a part of your data based on some criteria.
Because the formula is based on the function COUNTIFS, only the exercises and their results are listed on this page. For the explanation of the construction of the formula, please refer to the page COUNTIFS.
Since Excel 2007, a new useful feature allows you to count the number of rows that meet multiple criteria (multiple column).
In the following document, we are able to count the number of orders for a specific client (cell K1) for a time period (cell K2 and K3)
You will find in the function COUNTIFS page how to use this function through several exercises. All examples are real Excel spreadsheets and you can make inside the cells, all the changes you want.
There is a difference of numbering the weeks between the United States and Europe. The function was developed by the U.S., this is why the function returns the American method. To find the number of weeks for the European countries, you must use a very complex formula.
But since the arrival of Excel 2010, WEEKNUM function takes into account the two calculation methods.
In this article you will find a summary of different methods to calculate the number of weeks depending on your Excel version and also the implementing rules of calculating the number of weeks between Europe and America North.
For managers, time management is a priority. A Gantt chart can easily present the delay of the tasks and end of the task. However, you must care of the weekends. So, to add exactly the number of workdays, you must use the WORKDAY function.
Follow these steps to see how to build a Gantt chart.
I can not resist to share with you this Windows gadget developed by John Walkenbach. This tool adds directly with your other gadgets (like the watch, the weather, ...). All your Office files are now visible on your desktop and to open one of these files, you just have to click on its name.
You can download this gadget on the page of the author.
Excel offers a lot of chart models (curves, bars, Radars, ...). However, if you want to make a population pyramid, you can not find this representation in the different models.
But with a few tips, you can make a population pyramid like this.
In this tutorial, you can see the different steps to create a population pyramid.
The shortcut key F9 allows you to view the result of a part of your formulas. For example, if you have a very complex formula (as in the video below) and this formula returns an error, the F9 key will help you to easily find which part of your formula is responsible for the error.
At this address , you'll find the parsing tool, created by Roberto Mensa, Krisztina Szabó and Gábor Madács. You can download it for free 
And you can review the other Shorcut method to improve your speed with Excel
When you work on columns that contain empty cells, the shortcut Ctrl + arrow does not work. But you can use the function keys F5 and F8, to select a huge range of non-continuous cells.
This 3-minute video shows you the technique and errors to avoid.
And find othet shortcut method in this page.
Good morning all,
I have just finished to create a page where I have put all the date's formula like:
Well, all the complex functions but so useful 
Bref, toutes les fonctions complexes que nous avons eu temps de mal à traiter regroupées en une seule page
As we have seen for counting the number of item in a list, you can also add values ​​for a specific criteria. In the next article, I show you how to add the quantity of pens sold.
But, you can also add data to a greater than, equal or lower than another. A very good example of practice is with the dates. For instance, I show you how to calculate the sum of the order greater than a specific date.
You can change easily the language of Excel and, in the same time, change the function's name. This video will show you how to do
If you work in several countries, it's not easy to find the name of the function in the local language.
Fortunately, this dutch website, display the correspondance between each function's name in most of the language in Europe.
http://wwwhome.ewi.utwente.nl/~trieschn/excel/excel.html
With Excel, you can create a series of data (Number, Date or Text) very easily.
Watch the next 3 videos to know the method.
The INDEX function returns data from array with 1 or 2 dimensions.
But this function can also return the reference of a cell. So you can include the function INDEX inside another function (like the function SUM for instance)
Did you know that Pivot Tables allow you to separate in a new worksheet each element of a column. Almost as if you created a filter and you want to put this result in a new sheet.
Impossible? No, Excel and the Pivot Table
The dates are false friends, because when we make calculations between dates, we are stuck because of the different number of days per month and breaks in the months and years. Additions is not simple (in this case the DATE function is very useful) but for the differences, this is even more complicated.
And then, there exists a function to perform incredible differences in the dates, it is the DATEDIF function (click on the picture to open the page)
The most amazing is that Microsoft decided to hide this function. It does not appear in the list of functions or in the online help.
Do not worry, you'll find in this article all the explanations learn how to use this function and an exercise to practise.
Often, you would like to use data in cells, inside the reference of formula (like VLOOKUP, COUNTIF,...). But, normally, you can not include the value of a cell in a reference ; except if you use the function INDIRECT
If you want to return how many times you have one item in a column, the function COUNTIF is the function you need.
Beyound 100 rows, the mouse is not necessary to copy-paste.
In this case, the shortcuts are really useful. Let's show this video.
