Vérifier les données saisies, ou comment éviter de saisir des données erronées dans vos cellules,
Trouver des doublons dans une liste de valeurs

Calculs sur les dates avec la fonction =DATEDIF()
Astérix et Obélix au pays d'Excel (des chiffres romains dans vos tableaux)
Des sous totaux dans vos tableaux
Afficher des heures négatives avec le signe "-"
Additionner des heures au delà de 24 heures
Multiplier des heures par des francs
Arrondir un montant aux 5 centimes les plus proches
Arrondir un montant aux 50 centimes les plus proches
Masquer les messages d'erreur affichés dans les cellules
Arrondir un nombre au quart de point supérieur
Transformer un nombre en Heures : Minutes : Secondes
Changer une référence d'absolue vers relative et inversement
La fonction audit permet de voir toutes les cellules qui entrent directement ou indirectement dans un calcul ...
Passer d'heure Excel en heure centième
Détecter si une date est dans une année est bissextile
Rechercher le dernier jour d'un mois
Afficher le jour d'une date avec une majuscule pour la première lettre
Rechercher le nombre de valeurs différentes dans une plage de cellules
Comment utiliser une formule pour faire appel à une adresse qui se trouve dans une cellule
Calculer et obtenir des heures négatives
Calculer le nombre de date en fonction d'un mois Avec NB.SI
Le double-clic magique sur le bouton de duplication d'une cellule
On peut utiliser les intitulés de colonnes dans les formules !
Visualiser, et imprimer, toutes les formules de calcul de votre feuille
La fonction CHOISIR() à la place de la fonction SI()

Vérifier les données saisies

Pour limiter les risques d'erreur lors de l'ajout de données dans une feuille, Excel propose d'appliquer au contenu d'une cellule des critères de validation. Par exemple, il est possible d'autoriser uniquement des nombres entiers compris entre deux valeurs, ou encore uniquement une date valide…
 C'est très simple. Vous sélectionnez une cellule ou un champ, puis vous vous dirigez vers le menu : Données > Validation. (Dans Excel 2007 Onglet "Données" puis outil "Validation des données").
L'onglet "options" permet de définir les critères de restriction. On peut même y indiquer une formule mathématique personnalisée !

 Imaginons que nous voulions restreindre le contenu à une date comprise entre le 1er janvier 2007 et le 31 décembre 2008. Il suffit de sélectionner Date dans la liste déroulante, puis d'entrer les dates de début de fin. Validez en cliquant sur OK.

Pour n'autoriser que que quelques mots ou nombres utilisez "Liste" et saisissez votre liste de mots ou nombres autorisés (ou faites références à un plage de cellules de votre classeur contenant les mots ou nombres autorisés)

 Les onglets "Message de saisie" et "Alerte d'erreur" proposent également l'affichage d'un message à l'instant où la cellule est cliquée (pour indiquer à l'utilisateur quel type d'informations inscrire) et quand les données tapées ne sont pas valides (pour lui signifier l'erreur de saisie).

Trouver des doublons dans une liste de valeurs (merci à Serge Veillette)

voici une formule qui combiné a une condition et pour enjoliver a l'élimination des erreurs, nous permet de trouver des doublons entre deux colonnes de données de toutes sortes.

Exemple : dans la colonne A une liste de valeurs (chiffre ou texte) ; dans la colonne B une autre liste de valeurs. On va construire en colonne C une formule qui détectera les valeurs de la colonne B qui sont également présentes dans la colonne A.

Voici sa syntaxe :
 
=SI(ESTERREUR(RECHERCHEV(B1;$A$1:$A$26;1;FAUX));"";RECHERCHEV(B1;$A$1:$A$26;1;FAUX))

La formule est à construire en C1 et à recopier autant de fois que nécessaire (jusqu'au bas de la liste). Ici la formule recherche dans la colonne de gauche "A" la valeur d'une cellule de la colonne B qui pourrait être présente et l'inscrit dans la troisième colonne (là ou se trouve la formule)

Décortication de la formule :
Recherche la valeur de "B1";dans la plage de cellules de la colonne de gauche "A1 à A26" (a noter les $ présents afin de conserver la plage de sélection fixe lors de la recopie de cette formule dans les autres cellules de la colonne "C"); si la valeur de la cellule n'est pas trouvée, n'inscrit pas un code d'erreur mais laisse la cellule de la  colonne C (là ou se trouve la formule) blanche (sans valeur); si la valeur est trouvée (doublon) alors indique la valeur du doublon

Calculs sur les dates avec la fonction =DATEDIF()

La fonction =DATEDIF() permet de calculer la différence entre deux dates en années, mois et jours. Elle conmporte 3 arguments :

Syntaxe : =DATEDIF(Date1;Date2;code)

Cette fonction renvoie la différence entre Date1 et Date2 (attention, Date2 >= Date1) selon l'argument Code, qui peut prendre les valeurs suivantes :

• "y" : différence en années
• "m" : différence en mois
• "d" : différence en jours
• "ym" : différence en mois, une fois les années soustraites
• "yd" : différence en jours, une fois les années soustraites
• "md" : différence en jours, une fois les années et les mois soustraits

La fonction =DATEDIF() peut être en particulier utilisée pour calculer des âges. Par exemple, si la cellule A1 contient une date de naissance et la cellule A2 la date du jour :

Obélix au pays d'Excel (des chiffres romains dans vos tableaux)

Pour obtenir l'affichage d'un nombre en chiffre romains, il suffit d'utiliser la formule =ROMAIN()

par exemple,

• vous tapez le nombre 1963 dans la cellule A1,
• en A2 vous écrivez : =ROMAIN(A1)
• vous obtenez : MXMLXIII

Des sous totaux dans vos tableaux

La fonction =SOUSTOTAL(no_fonction;réf1;réf2;...) vous permettra d'effectuer tout un tas d'opérations portant sur une série de données. Cette fonction exige 2 arguments :

• no_fonction représente le nombre compris entre 1 et 11 indiquant quelle fonction utiliser pour calculer les sous-totaux d'une liste (voir ci-dessous).
• Réf1, réf2, représentent les 1 à 29 plages ou références pour lesquelles vous voulez un sous-total.

La liste des opérations disponible dans le 1er argument est la suivante :

Par exemple, =SOUSTOTAL(9;A1:A18) fera la somme des cellules A1 à A18. Chose interessante, si ces cellules contiennent elles-même un sous total, il ne sera pas pris en compte (voir ci-dessous)

Remarques

• Si d'autres sous-totaux se trouvent à l'intérieur de la plage définie par les arguments réf1, réf2,... (ou sous-totaux imbriqués), ces sous-totaux imbriqués ne sont pas pris en compte afin d'éviter tout comptage en double.
• La fonction SOUS.TOTAL ne prend pas en compte les lignes masquées suite à un filtrage. Le sous-total ne porte que sur les données visibles résultant du filtrage d'une liste.
• Si l'une des références est une référence 3D, la fonction SOUS.TOTAL renvoie la valeur d'erreur #VALEUR!

Afficher des heures négatives avec le signe "-"
Il faut créer et utiliser le format de nombre suivant : [Rouge]-[h]:mm

Additionner des heures au delà de 24 heures

Essayez d'addidtionner : 15:30 et 20:50 , vous obtiendrez : 12:20 !

Comment dès lors pouvoir additionner des nombres saisis au format heures-minutes ?

Il suffit en fait d'appliquer un format de nombre personnalisé à la cellule contenant la formule de somme . Le format est le suivant (format à créer dans le menu "format/cellule/onglet nombre/catégorie personnalisé" et écrire dans la zone "type")

Multiplier des heures par des francs

Un petit truc maintenant pour calculer en heures REELLES et en finir avec les centièmes.

soit 825,00 . Ne pas oublier de formater C1 en monnaie.

Arrondir un montant aux 5 centimes les plus proches

Etant donné un nombre dans la cellule A1, la formule est la suivante :

Arrondir un montant aux 50 centimes les plus proches

Etant donné un nombre dans la cellule A1, la formule est la suivante :

Masquer les messages d'erreur affichés dans les cellules

Il arrive que, en fontion des données de votre feuille de calcul, vos formules affichent des messages d'erreur.
Cela peut se produire notamment lorsque les formules sont préparées à l'avance et que les cellules utilisées dans une formule sont vide. Par exemple dans la cellule A3 on tape la formule : =A1/A2. Cette formule affichera #DIV/0! si A2 est vide.

Pour masquer ce message d'erreur, on peut utiliser la mise en forme conditionnelle d'Excel.

1/ on sélectionne la cellule contenant la formule fournissant éventuellement un message d'erreur, (dans notre exemple la cellule A3),
2/ on utilise le menu "Format - Mise en forme conditionnelle",
3/ dans condition 1, on déroule la liste et on choisi "La formule est",
4/ dans le champ de droite on écrit : =ESTERREUR(A3)
5/ on clique sur le bouton "Format" et dans l'onglet "Police" on choisi le blanc come couleur de police,
6/ on valide en cliquant sur OK pour les 2 boîtes de dialogue.

Si A3 est rempli, le résultat du calcul apparaît, sinon la cellule semble vide.

Changer une référence d'absolue vers relative

Selectionner dans la barre de formule la référence de cellule à changer et taper sur la touche "F4"

La fonction audit permet de voir toutes les cellules qui entrent directement ou indirectement dans un calcul

Vous voulez repérer les cellules prises en compte dans un calcul ? : Menu "Outils-Audit-Repérer les antécédents".

Vous voulez savoir si une cellule est utilisée dans un calcul ? : Menu "Outils-Audit-Repérer les dépendants".

Vous voulez supprimez les flêches générées par le menu "Outils-Audit" ? : Menu "Outils-Audit-Supprimez toutes les flêches.

Passer d'heure Excel en heure centième

Passer d'heure Excel en heure centième : HeureExcel*24 . Ex A1=1:30. A1*24 donne 1,50 (ne pas oublier de mettre la cellule contenant la formule dans un format numérique et non date)

Détecter si une date est dans une année est bissextile

Détecter si une date est dans une année est bissextile.
La formule suivante "=SI(MOIS(DATE(ANNEE(A1);2;29)) =2;VRAI;FAUX)" renvoie vrai si la date en A1est bissextile et faux dans le cas contraire.

Rechercher le dernier jour d'un mois

Rechercher le dernier jour d'un mois.(30, 31, 28 ou 29) 

A1 contient une date au format excel évidemment.

Afficher le jour d'une date avec une majuscule pour la première lettre

Afficher le jour d'une date avec une majuscule pour la première lettre :

A1 contient une date au format excel
On obtient le même résultat avec :

A noter que "jjjj" peut être remplacer par "mmmm" (pour obtenir le mois) ou encore par "jjjj mm aaaa"....

Rechercher le nombre de valeurs différentes dans une plage de cellules

Rechercher le nombre de valeurs différentes dans une plage :

Saisir :

et valider avec touches Ctrl-Maj-Entrée (Merci à Laurent Longre..)

Comment utiliser une formule pour faire appel à une adresse qui se trouve dans une cellule

L'indirection ! ou comment utiliser une formule pour faire appel à une adresse qui se trouve dans une cellule
A2 contient le nom d'un classeur ("Exefac.xls" par exemple)
A3 contient la formule: =INDIRECT(A2&"B6") 
Donc A3 aura la valeur contenu dans la cellule B6 de Exefac.xls (qui doit etre ouvert, bien entendu)

Calculer et obtenir des heures négatives

Calculer et obtenir des heures négatives :

Dans ce cas si

Calculer le nombre de date en fonction d'un mois Avec NB.SI

Calculer le nombre de date en fonction d'un mois Avec NB.SI

en G2, saisir :

=NB.SI($A$2:$D$3;">"&DATE(ANNEE(G1);MOIS(G1);0))-NB.SI($A$2:$D$3;">"&DATE(ANNEE(G1);MOIS(G1)+1;0))

(Cette formule fait suite à une question d'un internaute ,qui lui même chercher à répondre à une internaute, etc...)

Le double-clic magique sur le bouton de duplication d'une celluleMerci à jean-marc stoeffler pour ce truc

Lorsqu'apparaît la croix, plutôt que tirer laborieusement la formule vers le bas, il suffit de double-cliquer dessus. La formule se duplique alors jusqu'à la dernière cellule, à condition qu'il y ait des données sur la colonne de droite ou de gauche.

exemple avec un calcul d'âge (dont la formule est apparente) >>>> il suffit de double-cliquer sur la croix noire (1ère figure) pour que la formule se duplique jusqu'en B14 (2ème figure).

On peut utiliser les intitulés de colonnes dans les formules !
Merci à jean-marc stoeffler pour ce truc

en oubliant les fameuses références A5, D3, H4 ... (la bataille navale, c'est terminé !!) -

Attention : Dans Excel 2003 il faut activer l'option dans le menu "outils/ options/ onglet calcul" et cocher la case : Accepter les étiquettes dans les formules.

Visualiser, et imprimer, toutes les formules de calcul de votre feuille
Merci à jean-marc stoeffler pour ce truc

une "radiographie pulmonaire" de votre feuille de calcul...fonction Outils-Option-Affichage - et l'on clique Formules :

La largeur des colonnes est alors multipliée par deux et vous visualisez (et pouvez imprimer) vos formules de calcul.

La fonction CHOISIR() à la place de la fonction SI()

Exemple avec des taux de remise variables en fonction d'un code (1,2 ou 3) affichant des remises de 5%, 10% ou 15 % :

La cellule A1 contient le code d'escompte (1, 2 ou 3),
La cellule en B1 s'écrit :

où Si le code de remise est "1" la valeur de B1 sera 5%, si le code de remise est "2" la valeur de B1 sera 10% et si le code d'escompte est "3", la valeur de B1 sera 15%.

La formule peut contenir autant de variables que vous le désirez.

Arrondir un nombre au quart de point supérieur

Si le nombre se trouve dans la cellule A1, la formule sera la suivante :

Transformer un nombre en Heures : Minutes : Secondes

Il suffit de diviser le nombre par 86400 et mettre ensuite la cellule au format : HH:MM:SS