Votre compte

Excel

Gagnez du temps dans l'utilisation des outils bureautiques

Retour
Compter le nombre de jours ouvrés compris entre deux dates
Si vous faites une simple soustraction entre deux dates, vous obtiendrez le nombre de jours compris entre ces deux dates. Si vous souhaitez obtenir uniquement le nombre de jours fériés, vous allez devoir utiliser la fonction NB.JOURS.OUVRES.

Cette fonction a pour arguments la date de départ, la date de fin, et la liste des jours fériés.

Pour commencer, créez votre liste de jours fériés dans une plage de données (vous pouvez facilement récupérer cette liste sur Internet et la coller sur votre feuille Excel). Vous pouvez ensuite entrer votre formule comme ci-dessous. Dans notre exemple, nous avons fait deux calculs : le premier pour l’année entière et le second pour les trois premiers mois de l’année. Dans la colonne A se trouvent les dates de départ et dans la colonne B les dates de fin. La plage de cellules H2:H14 contient la liste des jours fériés.


Vous noterez que les jours ouvrés tiennent compte des week-ends entiers par défaut, à savoir les samedis et les dimanches.

Peut-être souhaitez-vous compter les samedis en tant que jours ouvrés. Dans ce cas, vous allez utiliser la fonction NB.JOURS.OUVRES.INTL qui permet de définir ce qu’est un week-end, c’est-à-dire les jours qui ne sont pas des jours travaillés. Cette fonction ajoute donc un argument, celui du week-end. Donc, pour reprendre notre exemple précédent, la formule sera :

=NB.JOURS.OUVRES.INTL(A2;B2;11;$H$2:$H$14)


L’argument 11, en rouge dans notre exemple, signifie que seuls les dimanches sont considérés comme étant non travaillés. Lorsque vous saisissez votre formule, Excel vous propose une liste de choix permettant de correspondre aux usages d’une entreprise ou d’un pays :

Incrémenter les jours de semaines travaillés
Tapez Lundi dans une cellule, cliquez sur le coin inférieur droit de cette cellule et faites glisser vers le bas ou vers la droite pour obtenir une incrémentation. Vous pouvez également incrémenter une date. C’est un grand classique.

Cependant, vous aimeriez avoir une liste sans les samedis ni les dimanches. C’est très simple :

Pour faire votre incrémentation, utilisez non pas le bouton gauche, mais le bouton droit de la souris. Lorsque vous relevez le doigt, vous allez sélectionner, parmi les propositions, Incrémenter les jours de semaine.


Et voilà ! Votre liste de dates ne contient plus de samedis ni de dimanches :


Affecter un nom à une valeur
Imaginons que, dans vos calculs, vous utilisiez couramment les taux de TVA 20% et 5,5%. Plutôt que de saisir à chaque fois ces pourcentages, vous allez leur donner un nom et c’est ce nom que vous utiliserez dans vos formules. Deux avantages principaux :
  • La formule devient plus lisible.
  • Le taux peut être modifié : il le sera automatiquement dans toutes vos formules.
  • Vous ne monopolisez pas de cellule pour ce nom.

    Pour ce faire :
    1. Sous l’onglet Formules, groupe Noms définis, cliquez sur Définir un nom.
    2. Dans la boîte de dialogue Nouveau nom, donnez un nom et dans la zone Fait référence à :, tapez le montant. Dans notre exemple, nous allons choisir le nom taux_réduit et la valeur 5,5% (ou 0,055).


    3. Cliquez sur OK
Dès à présent, dans toutes vos formules, vous pourrez utiliser le nom taux_réduit pour calculer le montant de la TVA réduite. Pour cela, démarrez normalement votre formule, par exemple =A1*, puis tapez le début du nom, Excel proposera automatiquement le nom taux_réduit dans la liste des fonctions et valeurs proposées. Double-cliquez sur le nom pour l’insérer dans la formule.



Votre formule deviendra donc :


Si, par la suite, ce taux venait à être modifié, il conviendra simplement de changer la valeur affectée au nom taux_réduit que vos formules soient toutes mises à jour :
  • Sous l’onglet Formules, groupe Noms définis, cliquez sur Gestionnaire de noms.
  • Dans la boîte de dialogue Gestionnaire de noms, sélectionnez le nom en question, taux-réduit dans notre exemple et cliquez sur Modifier.
  • Changer la valeur, cliquez sur OK et fermez la boîte de dialogue : vos formules sont à jour.
Convertir des lignes en colonnes et inversement
Toutes versions.

Vous avez créé un tableau dont les données sont organisées en lignes et vous souhaitez maintenant qu’elles le soient en colonnes. C’est tout simple :
  • Sélectionnez votre tableau et copiez-le.
  • Cliquez dans la première cellule où vous souhaitez coller ce nouveau tableau et parmi les options de collage, choisissez Transposer.
    Dans les versions récentes, deux solutions :
    1. Un clic sur le bouton Coller, choisir Transposer.
    2. Un clic droit sur la cellule de destination, et choisir Transposer.

Un graphique standard en deux temps trois mouvements
Par graphique Standard, entendez Histogramme.

Pour générer un histogramme très rapidement, vous aurez besoin d’environ un dixième de seconde !

Le curseur étant situé dans le tableau (inutile de sélectionner pour un tableau entier), appuyez sur :
  • Alt+F1 pour générer un graphique dans la feuille du tableau
    ou
  • F11 pour générer un graphique sur une nouvelle feuille graphique.
Vous pouvez ensuite peaufiner ce graphique comme bon vous semble.
Mettre une image en commentaire
Toutes versions

Pour insérer un commentaire dans une cellule, vous affichez l’onglet Révision et vous cliquez sur Nouveau commentaire. Vous pouvez également faire un clic droit sur la cellule et choisir Insérer un commentaire. Vous saisissez ensuite votre texte.

Et pourquoi ne pas insérer une image ? C’est en effet très possible si vous suivez la procédure suivante :
  • Sélectionnez la cellule et choisissez d’insérer classiquement un commentaire.
  • Dans la zone dédiée au commentaire, supprimez votre nom si nécessaire.
  • Faites un clic droit sur la bordure de la zone de commentaire, et choisissez Format de commentaire...


  • Dans la boîte de dialogue Format de commentaire, affichez l’onglet Couleurs et traits.
  • Sous la rubrique Remplissage, cliquez sur la liste déroulante Couleur : et sélectionnez Effets de remplissage…
    NB : selon les versions, cela peut être Motifs et textures…


  • Sous l’onglet Image, cliquez sur Sélectionner une image...
  • Sélectionnez votre image puis optez pour verrouiller ou non les proportions de l’image. Sachez que l’image va prendre toute la place de la zone de commentaire. Si les proportions ne sont pas verrouillées, l’image peut être déformée. À l’inverse, une image dont les proportions sont verrouillées risquera d’être tronquée si son format ne correspond pas au format de la zone de commentaire mais ne sera jamais déformée. Par ailleurs, vous pourrez, si vous le souhaitez modifier la taille et la forme de la zone de commentaire.


    C'est fait, votre commentaire est sous forme d'image.


Quelle semaine sommes-nous ?
Lorsque je dis « nous sommes le 2 janvier, quel est le numéro de la semaine ?», généralement on me répond Semaine 1… Et moi je dis que cela dépend de l’année… en tous les cas pour les Européens. Si vous êtes américain, nous ne serons pas sur la même longueur d’ondes !

En Europe, et donc en France, la représentation des dates est soumise à la norme ISO 8601. Cette norme dit que la première semaine, celle qui a le numéro 1, est la semaine qui contient le premier jeudi de l’année. Et donc une semaine qui commence un vendredi ne compte pas, ou plutôt compte pour faire partie de la dernière semaine ISO de l’année précédente.

Excel propose la fonction NO.SEMAINE pour calculer le numéro de la semaine. Ses arguments sont d’une part, la date, et d’autre part, la méthode de calcul selon le premier jour considéré d’une semaine. En France, le premier jour d’une semaine est un lundi alors qu’aux États-Unis est un dimanche. L’argument par défaut est le 1 (dimanche). Si vous êtes européen, vous mettrez le 2. Voici un exemple pour le 3 janvier 2020 qui est un vendredi :



Et le résultat est bien 1 (première semaine)



Nous allons modifier la date et se vieillir d’un an. Nous allons donc demander le numéro de la semaine pour le 3 janvier 2021 :



Le résultat est encore 1. Et pourtant si nous regardons le calendrier, nous voyons que le 1er janvier 2021 est un dimanche, que cette semaine ne contient donc que trois jours, et que pour nous, européens, la semaine numéro un est celle qui commence le lundi 4 alors qu’elle sera numérotée 2 aux États-Unis.

À partir de la version 2010
Ce problème a engendré de nombreux débats sur les forums Excel et Microsoft a fini par apporter une correction. Le fait de mettre l’argument 1 ou 2 pour le premier jour de la semaine ne change rien à l’affaire et même la complexifie (voir le tableau à la fin de l’article). En revanche, vous pouvez utiliser la méthode dite 21. Ainsi la formule se présentera ainsi :

=NO.SEMAINE(A1;21) et là, vous aurez le résultat escompté, à savoir 53 pour le 3 janvier 2021 et 2 pour le 4 janvier 2021. Ne soyez pas étonné de trouver une cinquante troisième semaine car si vous divisez 365 jours (ou 366 pour une année bissextile) par 7 (jours par semaine), vous n’obtenez pas 52, mais un petit peu plus, d’où cette cinquante troisième semaine qui peut comporter un, deux ou trois jours.

À partir de la version 2013
Une nouvelle fonction a été introduite permettant de couper court aux railleries et aux discussions : la fonction NO.SEMAINE.ISO. Cette fonction n’a qu’un seul argument : la date dont on souhaite connaître le numéro de la semaine. Vous n’aurez ainsi plus à vous poser de question.



Multiplication et division avec des heures
Pré-requis : Voir l'astuce précédente "Calcul avec les heures"

Votre plombier vous a préparé un devis : ce sera 50€ de l’heure. Vous allez donc faire quelques estimations pour savoir ce que cela va coûter au total.

Dans une cellule, vous saisissez le nombre d’heures prévues, soit 4h30, ce qui se saisit dans Excel : 4:30. Dans une autre cellule, vous saisissez donc le montant horaire (50) et enfin dans la troisième cellule, vous faites la multiplication :

Et vous obtenez… 09:00 ‼

Bien entendu, vous repérez que cette cellule C2 a été automatiquement mise au format heure. Vous choisissez donc un format qui va mieux, le format Compatibilité par exemple, et là, vous obtenez… 9,38 € ‼

Vous vous dites que ce serait bien mais malheureusement, vous vous dites aussi que le calcul n’était sûrement pas le bon !

En effet ! Dans l’astuce de calculs avec les heures, nous avons expliqué qu’une heure est un jour divisé divisé par 24 (heures). Il va donc falloir re-multiplier par 24 pour obtenir le montant réel de votre multiplication. La formule sera donc : =A2*B2*24


Et cette fois vous obtiendrez bien le montant à payer pour les quatre heures et trente minutes travaillées, soit 225 €. Ne nous arrêtons pas là ! Vous savez que votre plombier a couru le marathon de Paris l’an passé… Est-il aussi performant en course à pieds qu’en course à pieds ? Avec Excel, vous allez pouvoir calculer la vitesse moyenne pour ce marathon. Pour cela, vous allez bien sûr diviser la distance parcourue par le temps passé. Dans notre exemple, si vous divisez 42,20 (la distance parcourue) par le temps passé (4:27), vous allez obtenir un résultat de 227,60… ce qui paraît quelque peu surestimé ! Votre plombier serait-il mythomane ? Par le moins du monde ! Nous avons oublié, cette fois, non pas de multiplier mais de diviser par 24 :



Le résultat sera donc de 9,8 km/heure, ce qui n’est pas si mal ‼
Saisir des montants avec décimales sans s’occuper des virgules
Vous passez du temps à saisir des montants avec décimales et vous trouvez cela très agaçant de sans arrêt avoir à taper sur la virgule, surtout si vous n’avez pas de pas numérique, vous obligeant ainsi à passer du blocage au déblocage de la touche Majuscule.

Le réglage suivant est fait pour vous :

Cliquez sur l’onglet Fichier puis sur Options, et enfin sur Options avancées.

Sous la rubrique Options d’édition, cochez l’option Décimale fixe. Le nombre de décimales est réglé à 2 par défaut. Modifiez le nombre Place (emplacement de la virgule à partir de la droite) si nécessaire.


Cliquez sur OK et testez : saisissez un nombre décimal dans une cellule sans la virgule. Par exemple au lieu de taper 35,42 tapez simplement 3542. La virgule viendra automatiquement se placer avant les deux derniers chiffres et vous obtiendrez bien 35,42.

Soyez certains qu’avec ce réglage votre saisie va s’en trouver beaucoup plus rapide ! N’oubliez pas de revenir en arrière lorsque vous aurez terminé votre saisie.
Concaténer ou concaténer…
Le terme concaténation (substantif féminin), du latin cum (« avec ») et catena (« chaîne, liaison »), désigne l'action de mettre bout à bout au moins deux chaînes de caractères (Wikipedia).

La concaténation, dans Excel, vous permet donc d’assembler en une seule cellule, plusieurs éléments textuels situés dans diverses cellules. Trois fonctions vous permettent ce type de manipulation. Lesquelles et surtout quelles sont les différences ?

Notre démonstration va porter sur l’exemple simple suivant :



Nous voulons concaténer en D1 l’ensemble des éléments des cellules de A1 à C1 afin d’obtenir : « Michel Hubert Paris »

Avant 2016
Il n’existe qu’une seule fonction, la fonction CONCATENER(), mais il existe deux façons de l’utiliser, la première en la nommant et la seconde sans la nommer :

=CONCATENER(A2;" ";B2;" ";C2)

=A2&" "&B2&" "&C2


La première méthode est affichée sous forme de fonction classique, avec les arguments entre parenthèses et séparés par des points-virgules.

La seconde méthode utilise l’argument & afin de mettre bout à bout chaque élément.

Vous remarquerez que nous avons séparé chaque élément par une espace (ajouté entre chaque élément puisqu’il s’agit de texte).

À partir de 2016 (version 365 avec abonnement)
Il existe à présent deux autres fonctions de concaténation.

La première est la fonction CONCAT(). Cette fonction remplace la fonction CONCATENER() qui est conservée pour compatibilité ascendante.

Ces deux fonctions s’utilisent presque pareil. La différence est que la fonction CONCAT, contrairement à la fonction CONCATENER() autorise les références à une plage de cellules de type CONCAT(A2:D3) par exemple. Bien entendu, si ces cellules ne comportent d’espaces, les éléments seront collés.

La seconde est la fonction JOINDRE.TEXTE()
Vous avez bien perçu la limite des deux fonctions précitées : il faut ajouter des séparateurs. La fonction JOINDRE.TEXTE est plus intéressante parce que, si l’on souhaite ajouter un séparateur, comme ci-dessus, pour espacer chaque élément, il suffira de l’indiquer une seule fois. Par ailleurs, cette fonction autorise également, comme pour CONCAT(), les références à une plage de cellules, mais de plus, on pourra préciser si les cellules vides doivent être ou non utilisées :

=JOINDRE.TEXTE(" ";VRAI;A2:C2)

Dans la formule qui précède, le premier argument est le délimitateur, une espace (" "), le second argument (VRAI) indique que l’on doit ignorer les cellules vides (à l’inverse, on mettrait FAUX) et le troisième argument est la plage de cellules.
Créer des listes déroulantes
On a souvent besoin, dans Excel, d'utiliser des listes déroulantes. Nous allons donc voir deux solutions très simples d'incorporer ce type de liste dans Excel.

  • Ouvrez le classeur dans lequel va se trouver la liste déroulante et quelle que soit la méthode, commencez par insérer une feuille Excel dans laquelle vous allez entrer les données de la liste.
  • Tapez votre liste sous forme de tableau et dans l'ordre dans lequel vous souhaiterez qu'elle s'affiche (vous pourrez éventuellement la trier).
  • Nommez la plage de cellules qui contient les données :
  • Sélectionnez la plage.
  • Dans la zone Nom, saisissez un nom de votre choix (sans espace et sans ponctuation).
  • Appuyez sur la touche Entrée.


  • Première méthode : La validation
    • Sélectionnez la feuille et la/les cellules qui doit/doivent contenir la liste déroulante.
    • Sous l’onglet Données, cliquez sur Validation des données.
    • Dans la boîte de dialogue Validation des données, onglet Options, rubrique Autoriser, sélectionnez Liste.
    • Dans la zone de saisie Source, tapez le nom que vous avez donné à la plage de données ; si vous l'avez oublié, vous pouvez appuyer sur la touche F3 pour avoir accès à tous les noms utilisés dans le classeur.


    • Cliquez sur OK : la cellule contient la liste déroulante ! il faut la sélectionner pour la voir.


    Deuxième méthode : la barre d'outils Formulaires
    • Sous l’onglet Développeur, onglet Contrôles, cliquez sur Insérer, cliquez sur Zone de liste déroulante (Contrôle de Formulaire).
      • Tracez votre liste déroulante en faisant glisser la souris sur la feuille du classeur.
      • Cliquez sur la liste avec le bouton droit et choisissez Format de contrôle… ou bien cliquez sur le bouton Propriété (sur le ruban).
      • Dans la boîte de dialogue Format de contrôle, affichez l’onglet Contrôle.
      • Dans la zone de saisie Plage d'entrée, saisissez le nom de la plage de données.
      • Dans la zone de saisie Cellule liée, tapez la référence d'une cellule qui recevra le résultat ; optez pour une cellule quelconque se trouvant sur la même feuille qui contient la liste des données. Cette cellule liée affichera le numéro du nom sélectionné dans la liste.
        • Cliquez sur OK.
        • Sélectionnez maintenant la cellule qui devra afficher le résultat et dans laquelle vous allez entrer une formule à l'aide de la fonction INDEX. La fonction INDEX devra indiquer la plage de données (nommée "responsables" dans notre exemple) et le numéro de la ligne où se trouve la donnée (qui correspond au contenu de la cellule liée), ce qui donne :
          =INDEX(responsable;Feuil2!$B$1)
        • Votre liste déroulante est prête à être utilisée : sélectionnez une donnée, celle-ci va s'afficher dans la cellule qui contient la formule


Calcul avec les heures
Toutes versions :

Une heure est une unité (le jour) divisé par 24 (heures). Si vous saisissez dans une cellule 12:00, et que vous mettez volontairement un format standard, et si vous saisissez dans une autre cellule la formule =12/24, le résultat sera identique, c'est-à-dire 0,5, soit une demi-journée.

Lorsque vous saisissez une heure dans une cellule, le format d'heure s'applique automatiquement, à condition que vous ayez saisi correctement l'heure en question. Il n'y a d'ailleurs pas plusieurs manières de saisir une heure dans Excel, le seul séparateur heures:minutes:secondes étant les deux points (:).

Les additions et soustractions d'heures ne posent en principe pas de problème, il suffit d'utiliser les signes + et -.

Exemple : Nous allons additionner les heures se trouvant dans les cellules A1 et B1, puis nous allons les soustraire :



Affichage au-delà de 24 heures :

Nous allons maintenant modifier l'heure saisie en A1 et conserver l'addition :



Vous pouvez vous rendre compte que le résultat affiché est faux. En effet, le format Excel utilisé par défaut ne permet pas d'afficher au-delà de 24 heures. Il faut utiliser le format [hh]:mm afin de cumuler les heures et en afficher le total. Si vous faites des calculs d'heures, le mieux est de toujours utiliser ce format.

Notez que les crochets [ ] indiquent le cumul, ils peuvent également s'appliquer aux minutes, et aux secondes.




Affichage des heures négatives :

Nous allons maintenant modifier l'heure saisie en A1 et conserver la soustraction :



Eh oui ! Excel ne sait pas afficher des heures négatives ! Notez qu'il s'agit bien d'un problème de format et d'affichage. Voyez à ce sujet l’astuce "Les heures négatives"


Utilisation d'heures, minutes et secondes dans une formule :

Comme nous l'avons mentionné plus haut, l'unité est le jour, le jour étant lui-même composé de 24 heures, ou de 1440 minutes ou encore de 86400 secondes ; en conséquence, si dans une formule vous souhaitez utiliser :
- un nombre d'heures, vous devrez le diviser par 24
- un nombre de minutes, vous devrez le diviser par 1440
- un nombre de secondes, vous devrez le diviser par 86400
Vous devrez également mettre le format adéquat. Exemples :



Utiliser le remplissage instantané

Depuis la version 2013, il existe une fonctionnalité intéressante et (presque) intelligente, j’ai nommé : le remplissage instantané ! Exemple de données qu’il serait difficile d’utiliser : une liste d’adresses avec le code postal et le nom de la ville.

Voici l’exemple que nous allons utiliser :
Image 1
Imaginons que vous ayez besoin de filtrer/trier ces adresses par le code postal et/ou la ville.

Voici le résultat à atteindre :
Image 1
Pour cela il faudrait isoler le code postal et la ville mais dans cet exemple c’est plutôt difficile de trouver une formule efficiente ou d’utiliser la fonctionnalité de conversion.

Voici comment extraire (facilement) le code postal et la ville de cette liste :
• Saisissez dans la cellule adjacente (B2) le 1er code postal et le nom de la ville.
• Placez le pointeur de cellule sur la cellule B2
• Cliquez sur l’onglet Données et, dans le groupe Outils de données, cliquez sur Remplissage instantané.
• Admirez…

Dans cet exemple Excel comprend que l’on cherche à extraire le code postal et la ville. Dans d'autre cas vous devrez mettre plusieurs exemples afin qu'Excel trouve la correspondance entre vos exemples et les données de la/les colonnes précédentes.

La manipulation peut être quasi automatique si vous activez une option dans l’onglet Fichier, Options, Options avancées, puis dans Options d’édition cochez la case Utiliser automatiquement le remplissage instantané. Cela permet de ne pas utiliser le bouton Remplissage instantané mais vous obligera à saisir au moins 2 exemples.

Pour information, la fonctionnalité Remplissage instantané est aussi disponible sous l’onglet Accueil, groupe Edition, liste Remplissage la commande Remplissage instantané.

Version : A partir d’Excel 2013

Les heures négatives

Excel ne sait pas afficher des heures négatives, pour preuve :
• Dans la cellule A1 saisissez 10:00
• Dans la cellule A2 saisissez 20:00
• Dans la cellule A3 saisissez =A1-A2

Excel affichera alors une série de ######

Si vous placez la souris sur cette cellule une info bulle indiquera « Les dates et heures négatives s’affichent sous la forme ######. » Image 1

Voici 2 astuces permettant de lui faire changer d’avis. À noter que si l’affichage n’est pas correct, vous pouvez continuer à faire des calculs avec les heures négatives.

Première astuce : (la plus simple)
Tout d’abord, assurez-vous que votre classeur ne contient aucune date sous peine de les voir avancer de 4 ans (et 1 jour).

1. Cliquez sur l’onglet Fichier puis Options. 2. À gauche choisissez la catégorie Options Avancées et à droite, sous Lors du calcul de ce classeur cochez la case Utiliser le calendrier depuis 1904 et OK.
3. Dorénavant pour CE classeur les heures négatives s’affichent sans que vous n’ayez besoin de faire autre chose.
Image 2

Comme second inconvénient vous ne pouvez pas utiliser ce classeur en liaison avec d’autres qui utilisent le calendrier standard car les dates sont décalées.
Par contre si votre classeur contient déjà des dates et qu’il doit être utilisé avec d’autres utilisant le calendrier standard, voici une deuxième méthode.

1. Au lieu d’avoir une simple soustraction vous allez utiliser la fonction TEXTE qui transformera le résultat en texte et la fonction ABS qui rendra la résultat positif et affichera correctement les heures négatives mais ne sera plus utilisable dans les calculs.
2. Au lieu de =A1-A2 vous allez écrire =TEXTE(ABS(A1-A2);"-[h]:mm")
Image 3

Et voilà !

Déplacer/Copier rapidement des cellules dans Excel

Voici quelques astuces pour gagner du temps et des manipulations. Ces méthodes ne fonctionnent que sur le classeur actif et sur des sélections simples (pas de CTRL pour sélectionner).

* Pour déplacer :
1. Sélectionnez ce que vous voulez déplacer.
2. Placez le pointeur de la souris sur la bordure de la sélection (le pointeur prend la forme d’une quadruple flèche) puis faites glisser jusqu’à l’endroit voulu. Image 1

Si les cellules de destinations contiennent quelque chose, Excel va vous demandez de confirmer le remplacement. Image 2

* Pour déplacer et insérer :
L’inconvénient de la méthode précédente est qu'elle « écrase » la destination, nous allons donc la « perfectionner » de façon à déplacer et insérer les nouvelles cellules.
La manip est presque la même, mais pendant que vous faites glisser vers le nouvel emplacement il faut maintenir la touche "Shift".

Pour indiquer à quel endroit les cellules seront déplacées, la sélection prend la forme d’un « - » Image 3 ou d’un « I » Image 4 (Suivant si le pointeur de la souris se trouve sur le bord supérieur ou inférieur d’une cellule ou sur le bord gauche ou droit).

NB : Lâchez la touche "Shift" qu’après avoir lâché le bouton de la souris.


* Pour copier :
Cette fois on cherche à dupliquer (rapidement) des cellules.
1. Sélectionnez ce que vous voulez copier,
2. Placez le pointeur de la souris sur la bordure de la sélection,
3. Maintenez enfoncée la touche "Ctrl" (le pointeur prend la forme d’une flèche avec un "+") puis faites glisser jusqu’à l’endroit voulu.

Si les cellules de destinations contiennent quelque chose, Excel va vous demander de confirmer le remplacement.

NB : Lâchez la touche "Ctrl" qu’après avoir lâché le bouton de la souris.

Voici encore d'autres astuces :

* Pour copier et insérer :
Nous y voilà, il manquait cette possibilité bien sympathique qui consiste à copier/coller de façon inhabituelle et fun.

Nous avons vu précédemment qu’il fallait maintenir la touche "Ctrl" pour copier et qu’il fallait maintenir "Shift" pour insérer… donc si vous sélectionnez ce qui est à copier, et que vous maintenez les touches "Shift" et "Ctrl" pendant le déplacement de la souris. Vous cumulez les 2 fonctionnalités (copier ET insérer).

* …et si vous utilisez plusieurs feuilles…

En admettant que vous vouliez utiliser ces méthodes pour déplacer/copier vers une autre feuille, vous aurez peut-être essayé de passer sur une autre feuille en faisant glisser le pointeur de la souris sur son onglet et… voila la feuille active qui défile vers le haut :)

Allez, cerise sur le gâteau, si vous ajoutez la touche "Alt" lors du déplacement de la sélection cela permet d’activer la feuille voulue.

Ajouter des $ aux formules



Savez-vous mettre les $ aux bons endroits sans avoir à modifier manuellement la formule ?

Pour utiliser une adresse absolue il faut lui ajouter des $ . Les adresses absolues sont indispensables quand on utilise une formule que l’on duplique et qui se réfère toujours à une seule cellule.

Pour rappel le $ signifie « bloqué pendant la recopie » et donc :
• $A$1 signifie que la colonne A ET la ligne 1 ne changeront pas lors de la duplication vers d’autres cellules.
• A$1 signifie que la ligne 1 ne changera pas lors de la duplication vers d’autres cellules.
• $A1 signifie que la colonne A ne changera pas lors de la duplication vers d’autres cellules.

Voici comment mettre presque automatiquement les $ aux bons endroits.

Pour illustrer cette astuce prenons pour exemple une série de valeurs Hors Taxes, une cellule contenant le taux de TVA et une formule qui multiplie le HT par la TVA. Nous dupliquerons cette formule tout le long de la colonne.

Voici la version finale attendue :

Image 1

La 1ère formule de la cellule B2 sera donc :
=A2*F$1 (ou =A2*$F$1 mais en l’occurrence bloquer la colonne F n’est pas utile (…mais pas faux)).

Pour cela :
1. Cliquez dans la cellule B2 pour l'activer,
2. Appuyez sur la touche = du clavier,
3. Cliquez sur la cellule A2 pour l'activer,
4. Appuyez sur la touche * du clavier,
5. Cliquez sur la cellule F1 pour l'activer,
6. Puis appuyez une fois sur la touche [F4].

Excel affiche =A2*$F$1 mais ce n’est pas exactement ce que l’on voudrait. Appuyez sur la touche [F4]. Cette fois Excel affiche =A2*F$1, vous pouvez valider dès maintenant.

Vous pouvez appuyer encore une fois sur [F4], vous verrez que le $ se déplace à la colonne F et si vous appuyez encore les $ disparaissent.

Vous aurez compris que la touche [F4] permet de placer où bon vous semble ces $.

Vous pouvez dupliquer la cellule B2 jusqu’à B11 et inspecter les formules. Vous remarquerez que la première partie de la formule change mais pas la seconde.
Si vous changez le taux de TVA (cellule F1) les formules sont mises à jour.

Version : toutes

Mettre un tableau à zéro

Vous avez certainement un tableau rempli de données pour une période que vous voudriez réutiliser pour une nouvelle période. Pour cela il faut supprimer les données et surtout garder les formules.

Sur un tableau "simple" c’est facile, mais imaginez un tableau avec un tas de formules un peu partout et pas forcément à l’extérieur des données comme dans cet exemple que nous avons simplifié mais qui ressemble certainement à un tableau que vous avez eue à remettre à zéro un jour, et ce jour-là vous avez passé un certain temps à sélectionner les données afin de les supprimer.

Voici une astuce permettant de sélectionner en une seule fois (et sans erreur) toutes les données variables et d’un geste sur la touche Suppr de vider ces cellules.

Image 1


1. Cliquez sur le bouton Rechercher et sélectionner, choisissez la commande Atteindre (vous pouvez aussi utiliser la touche F5 ou les touches Ctrl+T) et cliquez sur le bouton Cellules…

Image 2


2. Dans la boite de dialogue Sélectionner les cellules cochez Constantes et sous Formules cochez seulement Nombres et cliquez sur OK.

Image 3

Voilà, seules les cellules qui contiennent des valeurs fixes sont sélectionnées, il vous reste plus qu'à cliquer sur la touche Suppr.

Image 4


Version : toutes

Faire une liste numérotée, sans trous, rapidement !

Il vous est certainement arrivé de devoir numéroter une liste, mais soit ce sont des numéros incrémentés, soit une formule du genre : cellule du dessus +1

Le problème est que lorsque l’on supprime une ligne (ou une cellule de la liste) la numérotation sous la suppression est à refaire ou bien le joli message d’erreur #REF! apparaît sur toutes les cellules sous la ligne supprimée (normal, la cellule du dessus a été supprimée).


Voici une astuce simple pour avoir une liste numérotée qui ne souffre pas de ces défauts.
Pour être sûr de la formule que vous devrez adapter, utilisons une liste qui sera numérotée de 1 en 1 dans la colonne A (donc en vertical) à partir de la ligne 3.

- Dans A3, saisissez la première valeur de votre liste (ici 1)
- Dans A4, saisissez la formule suivante : =NB(A$3:A3)+1
- Maintenant vous n’avez plus qu’à recopier cette cellule A4 sur les autres lignes
- La formule va devenir =NB(A$3:A4)+1, =NB(A$3:A5)+1 etc etc

Si vous supprimez une ligne, elle s’adaptera, sauf s’il s’agit de la première qui sert de base à toutes les formules, mais là encore une astuce supplémentaire est possible.

La voici :
- Utilisez la cellule qui sert de titre à votre colonne, car si elle contient un texte. Ici c’est le nombre de valeur numérique qui est compté, et un texte vaut 0 (zéro).

Réduire le ruban.

Depuis Office 2007 le « ruban » est apparu et a remplacé la traditionnelle ligne de menu. Que l’on aime ou non c’est maintenant la norme dans tous les programmes de la suite Office et certains écrans (ceux des notebooks notamment) n’ont pas grandis suffisamment en rapport de la place supplémentaire prise par ce ruban. C’est pour cela qu’aujourd’hui nous vous proposons quelques astuces pour masquer (temporairement ou durablement) ce ruban et ainsi gagner de la place pour le contenu sur tous les programmes de la suite office de Microsoft.

Ne cherchez pas dans les menus/onglets une option pour afficher ou masquer ce ruban, il n’y en a pas.

Deux types de manipulations sont possibles ; avec la souris ou avec le clavier.

Avec le clavier :

  • 1. Utilisez les touches [Ctrl]+[F1]. Le ruban disparait ; il réapparait avec la même manipulation.

  • 2. [Ctrl]+[Maj]+[F1] va plus loin et masque complètement les noms des onglets en plus du ruban. Même manipulation pour faire tout réapparaitre.

Avec la souris, vous avez l’embarras du choix :

  • 1. Double clic sur un des noms d’onglet pour masquer le ruban
  • 2. Un clic simple sur un nom d’onglet le réaffiche temporairement, un double clic sur un nom d’onglet le réaffiche « durablement », du moins jusqu’à ce que vous le masquiez à nouveau.

  • 3. Il est aussi possible de cliquer sur la petite flèche qui ressemble à un accent circonflexe qui se trouve tout à fait au bout à droite du ruban.

  • 4. Comme précédemment, un clic sur un onglet fait réapparaitre le ruban temporairement, et pour la garder visible il suffit de cliquer sur la punaise qui s’affiche à la place de l’accent circonflexe sur lequel vous avez cliqué précédemment.

  • 5. Il reste encore le bouton qui ressemble à ceci et qui se trouve tout en haut de l’écran.

  • Une liste de choix vous propose alors de définir l’affichage qui vous convient.

Transformer le nom d’un mois par son numéro d’ordre (sa valeur numérique)

Il vous est peut-être déjà arrivé de recevoir un fichier avec des données textuelles comme le nom du mois au lieu de sa valeur numérique (janvier = 1, février = 2 etc ) et pour vos calculs vous avez besoin de cette valeur car le nom est difficilement exploitable.

Une des solutions consiste à faire une table de correspondance et de rechercher dans celle-ci la valeur correspondante avec un RECHERCHEV par exemple… Une autre (nettement plus lourde) est de faire toute une série de SI imbriqués, mais même Excel 2013 est limité dans le nombre de SI et cela devient vite impossible à lire.

Voici une formule toute simple qui affiche le n° du mois selon son nom : =MOIS(A1&1)

Dans A1 juste le nom du mois en texte

Voici un exemple :

Répéter les lignes/colonnes de titre sur chaque page lors de l’impression
Lorsque vous avez un tableau plus large et/ou plus haut que la page, il va s’imprimer sur « N » pages et la réduction risque de vous demander une grosse loupe pour déchiffrer les pates de mouches que vous auriez imprimées.

Voici comment procéder avec un tableau d’exemple contenant 210 lignes et 376 colonnes qui représente des ventes par villes et départements français sur l'année par jour et par mois (avec un total annuel).

Ici les colonnes servant de libellés de titres sont les colonnes A et B. La ligne servant de libellé de titre est la ligne 1.

Affichez la boîte de dialogue "Mise en page" (accessible en cliquant sur le lanceur de boîte de dialogue de l'onglet Mise en page).

Cliquez sur l’onglet "Mise en page", dans le groupe «Mise en page», cliquez sur le bouton «Lanceur de boite de dialogue » (il ressemble à ça (voir image1)) puis activez l’onglet «Feuille».

Pour cet exemple, sélectionnez dans la zone «Lignes à répéter en haut» la ligne 1 et dans la zone «Colonnes à répéter à gauche» les colonnes de A à B (voir image 2)

C’est tout. Vous pouvez maintenant cliquer sur le bouton « Aperçu » pour voir le résultat : sur chaque page vous retrouvez les départements, les noms des villes ainsi que les jours de l’année.

NB : Seule l'impression sera affectée par ces modifications.

Créez « votre » fonction de calcul en VBA
Excel contient beaucoup de fonctions, mais n’avez-vous jamais pesté parce que la fonction qui vous serait utile chaque jour n’existe pas ?

Si oui, voici une astuce qui vous apprend à créer votre propre fonction de calcul que vous utiliserez ensuite comme, dans une formule, comme n’importe quelle fonction.

Pour cela il va falloir utiliser le VBA.

Nous allons utiliser un exemple simple. Vous pourrez par la suite créer vos propres fonctions peut-être plus complexes.

Comment calculer la surface d’un cercle ? Vous le savez sans doute, la Surface d’un cercle est égale à Pi * R², nous allons donc créer une fonction qui calculera cette surface d’après cette formule.

Commençons par créer un classeur, puis :
  • 1. Appuyez sur Alt+F11, c’est le raccourci clavier permettant d'afficher l’éditeur VBA
  • 2. Dans ce dernier, cliquez sur le menu Insertion et choisissez Module
  • 3. A l’endroit où se trouve le curseur, tapez Function SurfaceDunCercle et appuyez sur la touche Entrée

>L’« éditeur VBA » complète votre saisie comme ceci :


Function SurfaceDunCercle()

End Function


Le curseur se trouve automatiquement placé entre les instructions Function et End Function

A l'emplacement du curseur, tapez ceci: SurfaceDunCercle=Rayon^2*3.14 et appuyez sur la touche Entrée. Il manque encore quelque chose, c’est la valeur du rayon que nous allons passer à la fonction. Entre les () ajoutez le mot Rayon. Vous devez à présent obtenir ceci :



Vous pouvez maintenant fermer l’éditeur VBA et utiliser votre nouvelle fonction.

Dans la cellule A1 tapez la valeur du rayon du cercle dont vous voulez calculer la surface.

Dans la cellule B1 tapez =surf ; vous remarquerez que Excel a complété votre saisie, appuyez donc sur la touche Tabulation pour choisir votre fonction, ou bien continuez à saisir jusqu’à la parenthèse ouvrante… maintenant cliquez sur la cellule A1 et appuyez enfin sur la touche Entrée.



NB : cette fonction n’est disponible QUE dans CE classeur et vous devez enregistrer ce dernier en tant que « Classeur Excel (prenant en charge les macros) », c’est-à-dire avec une extension XLSM.

Pour que cette fonction soit disponible depuis n’importe quel classeur, vous pourrez l’enregistrer dans le classeur de macros personnelles, comme expliqué ici.
Bloquer les lignes/colonnes de titre à l'écran

Lorsque vous avez un tableau plus large ou plus haut que l’écran, vous devez faire défiler la page afin de voir ou de saisir dans les parties éloignées des libellés de titre. Le problème que cela pose c’est que vous ne savez plus sur quel libellé vous travaillez à moins d’avoir mémorisé le numéro de la ligne ou le nom de la colonne et de ne pas avoir à en mémoriser d’autres.

Voici une astuce qui permet de garder visible en permanence à l’écran, les libellés de titre en vertical et/ou en horizontal.

En pratique :
Voici par exemple un tableau contenant 210 lignes et 376 colonnes et qui représente des ventes par villes et par départements français, sur l'année, par jour et par mois (avec un total annuel). Les colonnes servant de libellés de titres sont les colonnes A et la B. La ligne servant de libellé de titre est la ligne 1.

  • 1. Commencez par afficher les lignes et/ou colonnes contenant les libellés des titres (les colonnes A et B et la ligne 1).
  • 2. Placez le pointeur de cellule SOUS la ligne de titre ET/OU à DROITE de la colonne de titre (« C2 », car la colonne « C » est bien à droite des 2 colonnes de titres, et « 2 » car la ligne 2 est bien sous la ligne de titre).

  • 3. Cliquez sur l’onglet « Affichage », « Fenêtre », « Figer les volets » puis sur « Figer les volets ».
    Vous voyez maintenant un trait vertical sous la ligne 1 et un autre à droite de la colonne B. Cela indique les colonnes/lignes qui resteront visibles quel que soit vos déplacements dans ce très grand tableau.



    Faites défiler les lignes et/ou les colonnes au-delà des bords de l’écran pour bien voir le résultat. A noter que si vous n’avez qu’une ligne OU qu’une colonne à bloquer, vous pouvez vous passer du placement du pointeur car se sera toujours la ligne du haut OU la colonne de gauche qui restera bloquée.
  • 4. Pour désactiver ce blocage, cliquez sur l’onglet « Affichage », « Fenêtre », « Figer les volets » puis sur « Libérer les volets ».



Protection de certaines zones d'un tableau
Soit un tableau dans lequel vous souhaitez protéger certaines cellules afin que leur contenu ne soient pas effacé ou modifié volontairement ou accidentellement. Suivez la procédure suivante :

  • 1. Sélectionnez les cellules qui seront autorisées à la saisie
  • 2. Faites un clic droit sur une des cellules de la sélection et choisissez la commande Format de cellule
  • 3. Cliquez sur l’onglet Protection
  • 4. Décochez la case Verrouillée et cliquez sur OK.
  • 5. Cliquez sur l’onglet Révision et sur le bouton Protéger la feuille.
  • 6. L'option « Protéger la feuilles et le contenu des cellules verrouillées » doit restée cochée.
  • 7.Activez/désactivez les options que vous désirez autoriser/interdire et cliquez sur OK.

A ce stade, il n’est plus possible de saisir ailleurs que dans les cellules que vous avez déverrouillées précédemment. Cependant, n’importe qui peut désactiver cette protection en cliquant sur l’onglet Révision, puis le bouton Ôter la protection de la feuille, nous allons donc y ajouter un mot de passe.




  • 1. Cliquez sur l’onglet Révision et sur le bouton Protéger la feuille.
  • 2. Saisissez un mot de passe dans la zone Mot de passe pour ôter la protection de la feuille et cliquez sur OK.


  • 3. Excel vous demande de ressaisir ce mot de passe afin de vérifier que vous n’avez pas fait d’erreur de saisie la première fois, cliquez sur OK pour terminer.



Cette fois, pour enlever la protection il faudra saisir le mot de passe que vous avez choisi.

Repérer les données en double

Excel possède une fonction pour repérer visuellement des données qui seraient en double dans une plage données.

  • Sélectionnez les données sur lesquelles vous voulez vérifier si 2 cellules ou plus contiennent la même chose.
  • Sous l’onglet Affichage, groupe Style, cliquez sur « Mise en forme conditionnelle », « Règle de mise en surbrillance des cellules » puis « Valeur en double ».
  • Vous n’avez plus qu’à sélectionner la mise en forme que vous voulez donner aux données qui sont en double, ou inversement, celle que ne le sont pas.
La fonction DATEDIF

Elle permet de calculer le nombre de jours, de mois ou d’années entre deux dates.

Syntaxe : DATEDIF(date_début;date_fin;unité)

  • Date_début correspond à la date de début de la période
  • Date_fin correspond à la date de fin de la période
  • Unité correspond au type d'information qui doit être renvoyé par la fonction

Les différentes unités disponibles avec la fonction DATEDIF sont :

  • "y" : Renvoie le nombre d'années entières comprises dans la période
  • "m" : Renvoie le nombre de mois entiers compris dans la période
  • "d" : Renvoie le nombre de jours compris dans la période
  • "ym" : Renvoie la différence entre le mois de Date_début et celui de Date_fin sans tenir compte des jours et des années
  • "yd" : Renvoie la différence entre le jour de Date_début et celui de Date_fin sans tenir compte des années
  • "md" : Renvoie la différence entre le jour de Date_début et celui de Date_fin sans tenir compte des mois ni des années

Quelques exemples de résultats :

  • =DATEDIF("01/01/2007";"01/01/2008";"y")
    Le résultat obtenu est 1, ce qui correspond à une année entière dans la période
  • =DATEDIF("01/01/2007";"01/01/2008";"m")
    Le résultat obtenu est 12, ce qui correspond à 12 mois entiers dans la période
  • =DATEDIF("01/01/2007";"01/01/2008";"d")
    Le résultat obtenu est 365, ce qui correspond à 365 jours sur la période
  • =DATEDIF("01/01/2007";"01/02/2008";"ym")
    Le résultat obtenu est 1, ce qui correspond à un mois entier entre janvier et février, le critère "ym" ne tenant pas compte des jours ni des années
  • =DATEDIF("01/01/2007";"01/02/2008";"yd")
    Le résultat obtenu est 31, ce qui correspond à 31 jours sur la période allant du 1er janvier au 1er février, le critère "yd" ne tenant pas compte de l'année
  • =DATEDIF("01/01/2007";"02/07/2008";"md")
    Le résultat obtenu est 1, ce qui correspond à 1 jour sur la période du 1er au 2, le critère "md" ne tenant pas compte des mois ni des années