19 trucs, astuces et raccourcis Excel faciles

Parfois, Excel semble trop beau pour être vrai. Tout ce que j'ai à faire est d'entrer une formule, et à peu près tout ce que j'aurais besoin de faire manuellement peut être fait automatiquement.

Besoin de fusionner deux feuilles avec des données similaires ? Excel peut le faire.

Besoin de faire des calculs simples? Excel peut le faire.

Besoin de combiner des informations dans plusieurs cellules ? Excel peut le faire.

Dans cet article, je passerai en revue les meilleurs conseils, astuces et raccourcis que vous pouvez utiliser dès maintenant pour faire passer votre jeu Excel au niveau supérieur. Aucune connaissance avancée d'Excel n'est requise.

Qu'est-ce qu'Excel ?

Microsoft Excel est un puissant logiciel de visualisation et d'analyse de données, qui utilise des feuilles de calcul pour stocker, organiser et suivre des ensembles de données avec des formules et des fonctions. Excel est utilisé par les spécialistes du marketing, les comptables, les analystes de données et d'autres professionnels. Il fait partie de la suite de produits Microsoft Office. Les alternatives incluent Google Sheets et Numbers.

Trouvez plus d'alternatives à Excel ici.

Excel est principalement utilisé pour créer des documents financiers en raison de ses fortes puissances de calcul. Vous trouverez souvent le logiciel dans les bureaux et les équipes comptables car il permet aux comptables de voir automatiquement les sommes, les moyennes et les totaux. Avec Excel, ils peuvent facilement donner un sens aux données de leur entreprise.

Alors qu'Excel est principalement connu comme un outil de comptabilité, les professionnels de tous les domaines peuvent utiliser ses fonctionnalités et ses formules - en particulier les spécialistes du marketing - car il peut être utilisé pour suivre tout type de données. Il élimine le besoin de passer des heures et des heures à compter des cellules ou à copier et coller des chiffres de performance. Excel a généralement un raccourci ou une solution rapide qui accélère le processus.

Vous pouvez également télécharger les modèles Excel ci-dessous pour tous vos besoins marketing.

Après avoir téléchargé les modèles, il est temps de commencer à utiliser le logiciel. Couvrons d'abord les bases.

Les bases d'Excel

Si vous débutez avec Excel, nous vous suggérons de vous familiariser avec quelques commandes de base. Ce sont des choses comme :

• Créer une nouvelle feuille de calcul à partir de zéro.
• Exécuter des calculs de base comme additionner, soustraire, multiplier et diviser.
• Rédaction et mise en forme du texte et des titres des colonnes.
• Utilisation des fonctions de remplissage automatique d'Excel.
• Ajouter ou supprimer des colonnes, des lignes et des feuilles de calcul uniques. (Ci-dessous, nous verrons comment ajouter des éléments tels que plusieurs colonnes et lignes.)
• Garder les titres des colonnes et des lignes visibles lorsque vous les faites défiler dans une feuille de calcul, afin que vous sachiez quelles données vous remplissez lorsque vous vous déplacez plus loin dans le document.
• Trier vos données par ordre alphabétique.

Explorons quelques-uns de ces plus en profondeur.

Par exemple, pourquoi le remplissage automatique est- il important ?

Si vous avez des connaissances de base sur Excel, il est probable que vous connaissiez déjà cette astuce rapide. Mais pour couvrir nos bases, permettez-moi de vous montrer la gloire de la saisie automatique. Cela vous permet de remplir rapidement les cellules adjacentes avec plusieurs types de données, notamment des valeurs, des séries et des formules.

Il existe plusieurs façons de déployer cette fonctionnalité, mais la poignée de remplissage est parmi les plus simples. Sélectionnez les cellules que vous souhaitez utiliser comme source, localisez la poignée de remplissage dans le coin inférieur droit de la cellule, puis faites glisser la poignée de remplissage pour couvrir les cellules que vous souhaitez remplir ou double-cliquez simplement :

De même, le tri est une fonctionnalité importante que vous voudrez connaître lors de l'organisation de vos données dans Excel.

Parfois, vous pouvez avoir une liste de données qui n'a aucune organisation. Peut-être avez-vous exporté une liste de vos contacts marketing ou de vos articles de blog. Quel que soit le cas, Fonction de tri d'Excel vous aidera à classer par ordre alphabétique n'importe quelle liste.

Cliquez sur les données de la colonne que vous souhaitez trier. Cliquez ensuite sur l'onglet "Données" dans votre barre d'outils et recherchez l'option "Trier" sur la gauche. Si le "A" est au-dessus du "Z", vous pouvez simplement cliquer une fois sur ce bouton. Si le « Z » est au-dessus du « A », cliquez deux fois sur le bouton. Lorsque le "A" est au-dessus du "Z", cela signifie que votre liste sera triée par ordre alphabétique. Cependant, lorsque le "Z" est au-dessus du "A", cela signifie que votre liste sera triée par ordre alphabétique inverse.

Explorons ensuite davantage les bases d'Excel (ainsi que les fonctionnalités avancées).

Comment utiliser Excel

Pour utiliser Excel, il vous suffit de saisir les données dans les lignes et les colonnes. Ensuite, vous utiliserez des formules et des fonctions pour transformer ces données en informations.

Nous allons passer en revue les meilleures formules et fonctions que vous devez connaître. Mais d'abord, regardons les types de documents que vous pouvez créer à l'aide du logiciel. De cette façon, vous avez une compréhension globale de la façon dont vous pouvez utiliser Excel au quotidien.

Documents que vous pouvez créer dans Excel

Vous ne savez pas comment vous pouvez réellement utiliser Excel dans votre équipe ? Voici une liste de documents que vous pouvez créer :

• Comptes de résultat : Vous pouvez utiliser une feuille de calcul Excel pour suivre l'activité commerciale et la santé financière d'une entreprise.
• Bilans : Les bilans font partie des types de documents les plus courants que vous pouvez créer avec Excel. Il vous permet d'avoir une vision globale de la situation financière d'une entreprise.
• Calendrier : vous pouvez facilement créer un calendrier mensuel de feuille de calcul pour suivre les événements ou d'autres informations sensibles à la date.

Voici quelques documents que vous pouvez créer spécifiquement pour les spécialistes du marketing.

Il ne s'agit que d'un petit échantillon des types de documents marketing et commerciaux que vous pouvez créer dans Excel. Nous avons créé une longue liste de modèles Excel que vous pouvez utiliser dès maintenant pour le marketing, la facturation, la gestion de projet, la budgétisation, etc.

Dans l'esprit de travailler plus efficacement et d'éviter un travail manuel fastidieux, voici quelques formules et fonctions Excel que vous devrez connaître.

Formules Excel

Il est facile de se laisser submerger par le large éventail de formules Excel que vous pouvez utiliser pour donner un sens à vos données. Si vous débutez avec Excel, vous pouvez vous fier aux formules suivantes pour exécuter certaines fonctions complexes, sans ajouter à la complexité de votre parcours d'apprentissage.

• Signe égal : avant de créer une formule, vous devez écrire un signe égal (=) dans la cellule où vous souhaitez que le résultat apparaisse.
• Addition : Pour additionner les valeurs de deux ou plusieurs cellules, utilisez le signe + . Exemple : =C5+D3 .
• Soustraction : Pour soustraire les valeurs de deux ou plusieurs cellules, utilisez le signe - . Exemple : =C5-D3 .
• Multiplication : Pour multiplier les valeurs de deux ou plusieurs cellules, utilisez le signe * . Exemple : =C5*D3 .
• Division : Pour diviser les valeurs de deux ou plusieurs cellules, utilisez le signe / . Exemple : =C5/D3 .

En rassemblant tout cela, vous pouvez créer une formule qui additionne, soustrait, multiplie et divise le tout dans une seule cellule. Exemple : =(C5-D3)/((A5+B6)*3) .

Pour les formules plus complexes, vous devrez utiliser des parenthèses autour des expressions pour éviter d'utiliser accidentellement l'ordre des opérations PEMDAS. Gardez à l'esprit que vous pouvez utiliser des nombres simples dans vos formules.

Fonctions Excel

Les fonctions Excel automatisent certaines des tâches que vous utiliseriez dans une formule typique. Par exemple, au lieu d'utiliser le signe + pour additionner une plage de cellules, vous utiliseriez la fonction SOMME. Examinons quelques fonctions supplémentaires qui aideront à automatiser les calculs et les tâches.

• SOMME : La fonction SOMME additionne automatiquement une plage de cellules ou de nombres. Pour compléter une somme, vous saisiriez la cellule de départ et la cellule finale avec deux-points entre les deux. Voici à quoi cela ressemble : SUM(Cell1:Cell2) . Exemple : =SOMME(C5:C30) .
• MOYENNE : La fonction MOYENNE calcule la moyenne des valeurs d'une plage de cellules. La syntaxe est la même que la fonction SOMME : MOYENNE(Cellule1:Cellule2). Exemple : =MOYENNE(C5:C30) .
• IF : La fonction IF vous permet de renvoyer des valeurs basées sur un test logique. La syntaxe est la suivante : IF(logical_test, value_if_true, [value_if_false]) . Exemple : =IF(A2>B2,"Over Budget",,"OK") .
• VLOOKUP : La fonction VLOOKUP vous aide à rechercher n'importe quoi sur les lignes de votre feuille. La syntaxe est : VLOOKUP(lookup value, table array, column number, Approximate match (TRUE) or Exact match (FALSE)) . Exemple : =RECHERCHEV([@Procureur],tbl_Procureurs,4,FAUX).
• INDEX : La fonction INDEX renvoie une valeur à l'intérieur d'une plage. La syntaxe est la suivante : INDEX(array, row_num, [column_num]) .
• MATCH : la fonction MATCH recherche un certain élément dans une plage de cellules et renvoie la position de cet élément. Il peut être utilisé en tandem avec la fonction INDEX. La syntaxe est : MATCH(lookup_value, lookup_array, [match_type]) .
• NB.SI : La fonction NB.SI renvoie le nombre de cellules qui répondent à certains critères ou ont une certaine valeur. La syntaxe est : NB.SI(plage, critères). Exemple : =COUNTIF(A2:A5,"Londres").

OK, prêt à entrer dans le vif du sujet ? Allons-y. (Et à tous les fans de Harry Potter là-bas… vous êtes les bienvenus à l'avance.)

Note : Les GIFs et visuels sont issus d'une version précédente d'Excel. Le cas échéant, la copie a été mise à jour pour fournir des instructions aux utilisateurs des versions plus récentes et plus anciennes d'Excel.

1. Utilisez les tableaux croisés dynamiques pour reconnaître et donner un sens aux données.

Les tableaux croisés dynamiques sont utilisés pour réorganiser les données dans une feuille de calcul. Ils ne modifieront pas les données dont vous disposez, mais ils peuvent résumer les valeurs et comparer différentes informations dans votre feuille de calcul, en fonction de ce que vous souhaitez qu'ils fassent.

Prenons un exemple. Disons que je veux jeter un œil au nombre de personnes dans chaque maison à Poudlard. Vous pensez peut-être que je n'ai pas trop de données, mais pour des ensembles de données plus longs, cela sera utile.

Pour créer le tableau croisé dynamique, je vais dans Data > Pivot Table . Si vous utilisez la version la plus récente d'Excel, accédez à Insertion > Tableau croisé dynamique . Excel remplira automatiquement votre tableau croisé dynamique, mais vous pouvez toujours modifier l'ordre des données. Ensuite, vous avez le choix entre quatre options.

• Filtre de rapport : cela vous permet de ne regarder que certaines lignes de votre ensemble de données. Par exemple, si je voulais créer un filtre par maison, je pourrais choisir de n'inclure que les étudiants de Gryffondor au lieu de tous les étudiants.
• Étiquettes de colonne : il s'agirait de vos en-têtes dans l'ensemble de données.
• Étiquettes de ligne : il peut s'agir de vos lignes dans l'ensemble de données. Les étiquettes de ligne et de colonne peuvent contenir des données de vos colonnes (par exemple, le prénom peut être glissé vers l'étiquette de ligne ou de colonne - cela dépend simplement de la façon dont vous voulez voir les données.)
• Valeur : Cette section vous permet de regarder vos données différemment. Au lieu de simplement extraire n'importe quelle valeur numérique, vous pouvez additionner, compter, moyenne, max, min, compter des nombres ou faire quelques autres manipulations avec vos données. En fait, par défaut, lorsque vous faites glisser un champ vers Valeur, il compte toujours.

Étant donné que je veux compter le nombre d'étudiants dans chaque maison, je vais accéder au générateur de tableau croisé dynamique et faire glisser la colonne Maison vers les étiquettes de ligne et les valeurs. Cela résumera le nombre d'étudiants associés à chaque maison.

2. Ajoutez plusieurs lignes ou colonnes.

Au fur et à mesure que vous jouez avec vos données, vous constaterez peut-être que vous devez constamment ajouter plus de lignes et de colonnes. Parfois, vous devrez peut-être même ajouter des centaines de lignes. Faire cela un par un serait super fastidieux. Heureusement, il existe toujours un moyen plus simple.

Pour ajouter plusieurs lignes ou colonnes dans une feuille de calcul, mettez en surbrillance le même nombre de lignes ou de colonnes préexistantes que vous souhaitez ajouter. Ensuite, faites un clic droit et sélectionnez "Insérer".

Dans l'exemple ci-dessous, je souhaite ajouter trois lignes supplémentaires. En mettant en surbrillance trois lignes, puis en cliquant sur Insérer, je peux ajouter rapidement et facilement trois lignes vides supplémentaires dans ma feuille de calcul.

3. Utilisez des filtres pour simplifier vos données.

Lorsque vous examinez de très grands ensembles de données, vous n'avez généralement pas besoin d'examiner chaque ligne en même temps. Parfois, vous souhaitez uniquement consulter les données qui correspondent à certains critères.

C'est là qu'interviennent les filtres.

Les filtres vous permettent de réduire vos données pour ne regarder que certaines lignes à la fois. Dans Excel, un filtre peut être ajouté à chaque colonne de vos données - et à partir de là, vous pouvez ensuite choisir les cellules que vous souhaitez afficher en même temps.

Jetons un coup d'œil à l'exemple ci-dessous. Ajoutez un filtre en cliquant sur l'onglet Données et en sélectionnant "Filtre". En cliquant sur la flèche à côté des en-têtes de colonne, vous pourrez choisir si vous souhaitez que vos données soient organisées par ordre croissant ou décroissant, ainsi que les lignes spécifiques que vous souhaitez afficher.

Dans mon exemple Harry Potter, disons que je ne veux voir que les élèves de Gryffondor. En sélectionnant le filtre Gryffondor, les autres lignes disparaissent.

Conseil de pro : copiez et collez les valeurs dans la feuille de calcul lorsqu'un filtre est activé pour effectuer une analyse supplémentaire dans une autre feuille de calcul.

4. Supprimez les points ou ensembles de données en double.

Les ensembles de données plus volumineux ont tendance à avoir un contenu dupliqué. Vous pouvez avoir une liste de plusieurs contacts dans une entreprise et ne vouloir voir que le nombre d'entreprises que vous avez. Dans des situations comme celle-ci, la suppression des doublons est très pratique.

Pour supprimer vos doublons, mettez en surbrillance la ligne ou la colonne dont vous souhaitez supprimer les doublons. Ensuite, allez dans l'onglet Données et sélectionnez "Supprimer les doublons" (qui se trouve sous le sous-en-tête Outils dans l'ancienne version d'Excel). Une fenêtre contextuelle apparaîtra pour confirmer les données avec lesquelles vous souhaitez travailler. Sélectionnez "Supprimer les doublons" et vous êtes prêt à partir.

Vous pouvez également utiliser cette fonctionnalité pour supprimer une ligne entière en fonction d'une valeur de colonne en double. Donc, si vous avez trois lignes avec les informations de Harry Potter et que vous n'avez besoin d'en voir qu'une seule, vous pouvez sélectionner l'ensemble de données complet, puis supprimer les doublons en fonction de l'e-mail. Votre liste résultante n'aura que des noms uniques sans aucun doublon.

5. Transposez les lignes en colonnes.

Lorsque vous avez des lignes de données dans votre feuille de calcul, vous pouvez décider de transformer les éléments de l'une de ces lignes en colonnes (ou vice versa). Il faudrait beaucoup de temps pour copier et coller chaque en-tête individuel, mais ce que la fonction de transposition vous permet de faire, c'est simplement de déplacer vos données de ligne dans des colonnes, ou inversement.

Commencez par mettre en surbrillance la colonne que vous souhaitez transposer en lignes. Faites un clic droit dessus, puis sélectionnez "Copier". Ensuite, sélectionnez les cellules de votre feuille de calcul où vous voulez que votre première ligne ou colonne commence. Faites un clic droit sur la cellule, puis sélectionnez « Collage spécial ». Un module apparaîtra - en bas, vous verrez une option pour transposer. Cochez cette case et sélectionnez OK. Votre colonne sera maintenant transférée vers une ligne ou vice-versa.

Sur les nouvelles versions d'Excel, une liste déroulante apparaîtra au lieu d'une fenêtre contextuelle.

6. Divisez les informations textuelles entre les colonnes.

Que se passe-t-il si vous souhaitez diviser les informations contenues dans une cellule en deux cellules différentes ? Par exemple, vous souhaitez peut-être extraire le nom de l'entreprise de quelqu'un via son adresse e-mail. Ou peut-être souhaitez-vous séparer le nom complet de quelqu'un en un prénom et un nom de famille pour vos modèles de marketing par e-mail.

Grâce à Excel, les deux sont possibles. Tout d'abord, mettez en surbrillance la colonne que vous souhaitez diviser. Ensuite, allez dans l'onglet Données et sélectionnez "Texte aux colonnes". Un module apparaîtra avec des informations supplémentaires.

Tout d'abord, vous devez sélectionner "Délimité" ou "Largeur fixe".

• "Délimité" signifie que vous souhaitez diviser la colonne en fonction de caractères tels que des virgules, des espaces ou des tabulations.
• "Largeur fixe" signifie que vous souhaitez sélectionner l'emplacement exact sur toutes les colonnes où vous souhaitez que la division se produise.

Dans l'exemple ci-dessous, sélectionnons "Délimité" afin que nous puissions séparer le nom complet en prénom et nom de famille.

Ensuite, il est temps de choisir les délimiteurs. Cela peut être une tabulation, un point-virgule, une virgule, un espace ou autre chose. ("Quelque chose d'autre" pourrait être le signe "@" utilisé dans une adresse e-mail, par exemple.) Dans notre exemple, choisissons l'espace. Excel vous montrera alors un aperçu de ce à quoi ressembleront vos nouvelles colonnes.

Lorsque vous êtes satisfait de l'aperçu, appuyez sur "Suivant". Cette page vous permettra de sélectionner les formats avancés si vous le souhaitez. Lorsque vous avez terminé, cliquez sur "Terminer".

7. Utilisez des formules pour des calculs simples.

En plus de faire des calculs assez complexes, Excel peut vous aider à faire de l'arithmétique simple comme ajouter, soustraire, multiplier ou diviser n'importe laquelle de vos données.

• Pour ajouter, utilisez le signe +.
• Pour soustraire, utilisez le signe -.
• Pour multiplier, utilisez le signe *.
• Pour diviser, utilisez le signe /.

Vous pouvez également utiliser des parenthèses pour vous assurer que certains calculs sont effectués en premier. Dans l'exemple ci-dessous (10+10*10), le deuxième et le troisième 10 ont été multipliés ensemble avant d'ajouter les 10 supplémentaires. Cependant, si nous le faisions (10+10)*10, le premier et le deuxième 10 seraient additionnés en premier .

8. Obtenez la moyenne des nombres dans vos cellules.

Si vous voulez la moyenne d'un ensemble de nombres, vous pouvez utiliser la formule =AVERAGE(Cell1:Cell2) . Si vous souhaitez résumer une colonne de nombres, vous pouvez utiliser la formule =SUM(Cell1:Cell2) .

9. Utilisez la mise en forme conditionnelle pour que les cellules changent automatiquement de couleur en fonction des données.

La mise en forme conditionnelle vous permet de modifier la couleur d'une cellule en fonction des informations contenues dans la cellule. Par exemple, si vous souhaitez signaler certains nombres supérieurs à la moyenne ou figurant dans les 10 % supérieurs des données de votre feuille de calcul, vous pouvez le faire. Si vous souhaitez colorer les points communs entre les différentes lignes d'Excel, vous pouvez le faire. Cela vous aidera à voir rapidement les informations importantes pour vous.

Pour commencer, mettez en surbrillance le groupe de cellules sur lequel vous souhaitez utiliser la mise en forme conditionnelle. Ensuite, choisissez "Formatage conditionnel" dans le menu Accueil et sélectionnez votre logique dans la liste déroulante. (Vous pouvez également créer votre propre règle si vous voulez quelque chose de différent.) Une fenêtre apparaîtra vous invitant à fournir plus d'informations sur votre règle de formatage. Sélectionnez "OK" lorsque vous avez terminé, et vous devriez voir vos résultats apparaître automatiquement.

10. Utilisez la formule IF Excel pour automatiser certaines fonctions Excel.

Parfois, nous ne voulons pas compter le nombre de fois qu'une valeur apparaît. Au lieu de cela, nous souhaitons saisir des informations différentes dans une cellule s'il existe une cellule correspondante contenant ces informations.

Par exemple, dans la situation ci-dessous, je souhaite attribuer dix points à tous ceux qui appartiennent à la maison Gryffondor. Au lieu de taper manuellement des dizaines à côté du nom de chaque élève de Gryffondor, je peux utiliser la formule IF Excel pour dire que si l'élève est à Gryffondor, il devrait alors obtenir dix points.

La formule est : IF(logical_test, value_if_true, [value_if_false])

Exemple ci-dessous : =IF(D2=”Gryffondor”,,”10″,,”0″)

En termes généraux, la formule serait SI (test logique, valeur de vrai, valeur de faux). Examinons chacune de ces variables.

• Logical_Test : Le test logique est la partie "IF" de l'instruction. Dans ce cas, la logique est D2 = "Gryffondor" car nous voulons nous assurer que la cellule correspondant à l'élève indique "Gryffondor". Assurez-vous de mettre Gryffondor entre guillemets ici.
• Value_if_True : c'est ce que nous voulons que la cellule affiche si la valeur est vraie. Dans ce cas, nous voulons que la cellule affiche « 10 » pour indiquer que l'étudiant a obtenu les 10 points. Utilisez uniquement des guillemets si vous souhaitez que le résultat soit du texte au lieu d'un nombre.
• Value_if_False : c'est ce que nous voulons que la cellule affiche si la valeur est fausse. Dans ce cas, pour tout élève qui n'est pas à Gryffondor, nous voulons que la cellule affiche "0". Utilisez uniquement des guillemets si vous souhaitez que le résultat soit du texte au lieu d'un nombre.

Note : Dans l'exemple ci-dessus, j'ai attribué 10 points à tout le monde à Gryffondor. Si je voulais plus tard additionner le nombre total de points, je ne pourrais pas le faire car les 10 sont entre guillemets, ce qui en fait du texte et non un nombre qu'Excel peut additionner.

La vraie puissance de la fonction IF vient lorsque vous enchaînez plusieurs instructions IF ensemble, ou imbriquez-les. Cela vous permet de définir plusieurs conditions, d'obtenir des résultats plus spécifiques et, finalement, d'organiser vos données en blocs plus gérables.

Les plages sont un moyen de segmenter vos données pour une meilleure analyse. Par exemple, vous pouvez classer les données en valeurs inférieures à 10, 11 à 50 ou 51 à 100. Voici à quoi cela ressemble en pratique :

=SI(B3<11,« 10 ou moins »,SI(B3<51,« 11 à 50 »,SI(B3<100,« 51 à 100 »)))

Cela peut prendre quelques essais et erreurs, mais une fois que vous aurez compris, les formules IF deviendront votre nouveau meilleur ami Excel.

11. Utilisez des signes dollar pour conserver la même formule d'une cellule, quel que soit l'endroit où elle se déplace.

Avez-vous déjà vu un signe dollar dans une formule Excel ? Lorsqu'il est utilisé dans une formule, il ne représente pas un dollar américain ; au lieu de cela, il s'assure que la colonne et la ligne exactes sont conservées même si vous copiez la même formule dans des lignes adjacentes.

Vous voyez, une référence de cellule - lorsque vous faites référence à la cellule A5 à partir de la cellule C5, par exemple - est relative par défaut. Dans ce cas, vous faites en fait référence à une cellule qui se trouve cinq colonnes à gauche (C moins A) et dans la même ligne (5). C'est ce qu'on appelle une formule relative. Lorsque vous copiez une formule relative d'une cellule à une autre, elle ajuste les valeurs de la formule en fonction de l'endroit où elle est déplacée. Mais parfois, nous voulons que ces valeurs restent les mêmes, qu'elles soient déplacées ou non - et nous pouvons le faire en transformant la formule en une formule absolue.

Pour changer la formule relative (=A5+C5) en une formule absolue, nous ferions précéder les valeurs de ligne et de colonne par des signes dollar, comme ceci : (=$A$5+$C$5) . (En savoir plus sur la page d'assistance de Microsoft Office ici.)

12. Utilisez la fonction RECHERCHEV pour extraire des données d'une zone d'une feuille à une autre.

Avez-vous déjà eu deux ensembles de données sur deux feuilles de calcul différentes que vous souhaitez combiner en une seule feuille de calcul ?

Par exemple, vous pouvez avoir une liste de noms de personnes à côté de leurs adresses e-mail dans une feuille de calcul, et une liste des adresses e-mail de ces mêmes personnes à côté de leurs noms d'entreprise dans l'autre — mais vous voulez les noms, adresses e-mail et noms d'entreprise de ces personnes à apparaître en un seul endroit.

Je dois beaucoup combiner des ensembles de données comme celui-ci - et quand je le fais, la RECHERCHEV est ma formule de prédilection.

Avant d'utiliser la formule, cependant, assurez-vous d'avoir au moins une colonne qui apparaît de manière identique aux deux endroits. Parcourez vos ensembles de données pour vous assurer que la colonne de données que vous utilisez pour combiner vos informations est exactement la même, sans espaces supplémentaires.

La formule : =VLOOKUP(valeur de recherche, tableau de tableau, numéro de colonne, correspondance approximative (TRUE) ou correspondance exacte (FALSE))

La formule avec les variables de notre exemple ci-dessous : =VLOOKUP(C2,Sheet2!A:B,2,FALSE)

Dans cette formule, il y a plusieurs variables. Ce qui suit est vrai lorsque vous souhaitez combiner des informations dans la feuille 1 et la feuille 2 sur la feuille 1.

• Valeur de recherche : Il s'agit de la valeur identique que vous avez dans les deux feuilles de calcul. Choisissez la première valeur dans votre première feuille de calcul. Dans l'exemple qui suit, cela signifie la première adresse e-mail de la liste, ou la cellule 2 (C2).
• Tableau Tableau : Le tableau tableau est la plage de colonnes de la feuille 2 à partir de laquelle vous allez extraire vos données, y compris la colonne de données identique à votre valeur de recherche (dans notre exemple, les adresses e-mail) dans la feuille 1 ainsi que la colonne de données que vous essayez de copier dans la feuille 1. Dans notre exemple, il s'agit de "Feuille2 ! A : B". "A" signifie la colonne A de la feuille 2, qui est la colonne de la feuille 2 où les données identiques à notre valeur de recherche (e-mail) dans la feuille 1 sont répertoriées. Le "B" signifie la colonne B, qui contient les informations qui ne sont disponibles que dans la feuille 2 que vous souhaitez traduire dans la feuille 1.
• Numéro de colonne : Cela indique à Excel dans quelle colonne se trouvent les nouvelles données que vous souhaitez copier dans la feuille 1. Dans notre exemple, il s'agirait de la colonne dans laquelle se trouve « Maison ». « Maison » est la deuxième colonne de notre gamme de colonnes (tableau), donc notre numéro de colonne est 2. [ Remarque : Votre plage peut comporter plus de deux colonnes. Par exemple, s'il y a trois colonnes sur la feuille 2 - Email, Age et House - et que vous souhaitez toujours amener House sur la feuille 1, vous pouvez toujours utiliser un VLOOKUP. Vous avez juste besoin de changer le "2" en "3" pour qu'il récupère la valeur dans la troisième colonne : =VLOOKUP(C2:Sheet2!A:C,3,false).]
• Correspondance approximative (VRAI) ou Correspondance exacte (FAUX) : utilisez FAUX pour vous assurer que vous n'obtenez que des correspondances de valeurs exactes. Si vous utilisez TRUE, la fonction tirera des correspondances approximatives.

Dans l'exemple ci-dessous, la feuille 1 et la feuille 2 contiennent des listes décrivant différentes informations sur les mêmes personnes, et le fil conducteur entre les deux est leurs adresses e-mail. Supposons que nous souhaitions combiner les deux ensembles de données afin que toutes les informations sur la maison de la feuille 2 soient traduites dans la feuille 1.

Ainsi, lorsque nous tapons la formule =VLOOKUP(C2,Sheet2!A:B,2,FALSE) , nous apportons toutes les données de la maison dans la feuille 1.

Gardez à l'esprit que VLOOKUP ne retirera que les valeurs de la deuxième feuille qui se trouvent à droite de la colonne contenant vos données identiques. Cela peut entraîner certaines limitations, c'est pourquoi certaines personnes préfèrent utiliser les fonctions INDEX et MATCH à la place.

13. Utilisez les formules INDEX et MATCH pour extraire les données des colonnes horizontales.

Comme VLOOKUP, les fonctions INDEX et MATCH extraient des données d'un autre ensemble de données dans un emplacement central. Voici les principales différences :

• VLOOKUP est une formule beaucoup plus simple. Si vous travaillez avec de grands ensembles de données nécessitant des milliers de recherches, l'utilisation de la fonction INDEX et MATCH réduira considérablement le temps de chargement dans Excel.
• Les formules INDEX et MATCH fonctionnent de droite à gauche, tandis que les formules VLOOKUP fonctionnent uniquement comme une recherche de gauche à droite. En d'autres termes, si vous devez effectuer une recherche avec une colonne de recherche à droite de la colonne de résultats, vous devrez alors réorganiser ces colonnes afin d'effectuer une RECHERCHEV. Cela peut être fastidieux avec de grands ensembles de données et/ou entraîner des erreurs.

Donc, si je veux combiner des informations dans la feuille 1 et la feuille 2 sur la feuille 1, mais que les valeurs des colonnes dans les feuilles 1 et 2 ne sont pas les mêmes, alors pour faire une VLOOKUP, je devrais changer mes colonnes. Dans ce cas, je choisirais de faire un INDEX et MATCH à la place.

Prenons un exemple. Supposons que la feuille 1 contienne une liste de noms de personnes et leurs adresses e-mail à Poudlard, et que la feuille 2 contienne une liste d'adresses e-mail de personnes et le patronus de chaque élève. (Pour les non-fans de Harry Potter, chaque sorcière ou sorcier a un gardien d'animal appelé "Patronus" qui lui est associé.) L'information qui vit dans les deux feuilles est la colonne contenant les adresses e-mail, mais cette colonne d'adresse e-mail se trouve dans des numéros de colonne différents sur chaque feuille. J'utiliserais les formules INDEX et MATCH au lieu de VLOOKUP pour ne pas avoir à changer de colonne.

Quelle est donc la formule ? La formule est en fait la formule MATCH imbriquée dans la formule INDEX. Vous verrez que j'ai différencié la formule MATCH en utilisant une couleur différente ici.

La formule : =INDEX(table array, MATCH formula)

Cela devient : =INDEX(table array, MATCH ( lookup_value, lookup_array))

La formule avec les variables de notre exemple ci-dessous : =INDEX(Sheet2!A:A,(MATCH(Sheet1!C:C,Sheet2!C:C,0)))

Voici les variables :

• Table Array : La plage de colonnes de la feuille 2 contenant les nouvelles données que vous souhaitez transférer vers la feuille 1. Dans notre exemple, « A » signifie la colonne A, qui contient les informations « Patronus » pour chaque personne.
• Valeur de recherche : Il s'agit de la colonne de la feuille 1 qui contient des valeurs identiques dans les deux feuilles de calcul. Dans l'exemple qui suit, cela signifie la colonne « e-mail » sur la feuille 1, qui est la colonne C. Donc : Sheet1!C:C.
• Lookup Array : Il s'agit de la colonne de la feuille 2 qui contient des valeurs identiques dans les deux feuilles de calcul. Dans l'exemple qui suit, cela fait référence à la colonne « e-mail » de la feuille 2, qui se trouve être également la colonne C. Donc : Sheet2!C:C.

Une fois que vous avez vos variables droites, tapez les formules INDEX et MATCH dans la cellule la plus haute de la colonne vide Patronus sur la feuille 1, où vous voulez que les informations combinées vivent.

14. Utilisez la fonction NB.SI pour qu'Excel compte les mots ou les nombres dans n'importe quelle plage de cellules.

Au lieu de compter manuellement la fréquence d'apparition d'une certaine valeur ou d'un certain nombre, laissez Excel faire le travail pour vous. Avec la fonction NB.SI, Excel peut compter le nombre de fois qu'un mot ou un nombre apparaît dans n'importe quelle plage de cellules.

Par exemple, disons que je veux compter le nombre de fois que le mot "Gryffondor" apparaît dans mon ensemble de données.

La formule : =COUNTIF(plage, critères)

La formule avec les variables de notre exemple ci-dessous : =COUNTIF(D:D,"Gryffondor")

Dans cette formule, il y a plusieurs variables :

• Plage : La plage que nous voulons que la formule couvre. Dans ce cas, puisque nous nous concentrons uniquement sur une colonne, nous utilisons "D:D" pour indiquer que la première et la dernière colonne sont toutes les deux D. Si je regardais les colonnes C et D, j'utiliserais "C:D .”
• Critères : Quel que soit le nombre ou le morceau de texte que vous souhaitez qu'Excel compte. Utilisez uniquement des guillemets si vous souhaitez que le résultat soit du texte au lieu d'un nombre. Dans notre exemple, le critère est "Gryffondor".

Il suffit de taper la formule COUNTIF dans n'importe quelle cellule et d'appuyer sur "Entrée" pour me montrer combien de fois le mot "Gryffondor" apparaît dans l'ensemble de données.

15. Combinez les cellules en utilisant &.

Les bases de données ont tendance à séparer les données pour les rendre aussi précises que possible. Par exemple, au lieu d'avoir une colonne qui affiche le nom complet d'une personne, une base de données peut avoir les données sous la forme d'un prénom, puis d'un nom de famille dans des colonnes séparées. Ou, il peut avoir l'emplacement d'une personne séparé par ville, état et code postal. Dans Excel, vous pouvez combiner des cellules avec différentes données dans une seule cellule en utilisant le signe "&" dans votre fonction.

La formule avec les variables de notre exemple ci-dessous : =A2&" "&B2

Passons en revue la formule ensemble à l'aide d'un exemple. Imaginez que nous voulons combiner les prénoms et les noms de famille en noms complets dans une seule colonne. Pour ce faire, nous plaçons d'abord notre curseur dans la cellule vide où nous voulons que le nom complet apparaisse. Ensuite, nous allons mettre en surbrillance une cellule contenant un prénom, taper un signe "&", puis mettre en surbrillance une cellule avec le nom de famille correspondant.

Mais vous n'avez pas terminé - si tout ce que vous saisissez est =A2&B2, il n'y aura pas d'espace entre le prénom et le nom de la personne. Pour ajouter cet espace nécessaire, utilisez la fonction =A2&" "&B2 . Les guillemets autour de l'espace indiquent à Excel de mettre un espace entre le prénom et le nom.

Pour que cela soit vrai pour plusieurs lignes, faites simplement glisser le coin de cette première cellule vers le bas, comme indiqué dans l'exemple.

16. Ajoutez des cases à cocher.

If you're using an Excel sheet to track customer data and want to oversee something that isn't quantifiable, you could insert checkboxes into a column.

For example, if you're using an Excel sheet to manage your sales prospects and want to track whether you called them in the last quarter, you could have a “Called this quarter?” column and check off the cells in it when you've called the respective client.

Voici comment procéder.

Highlight a cell you'd like to add checkboxes to in your spreadsheet. Then, click DEVELOPER. Then, under FORM CONTROLS, click the checkbox or the selection circle highlighted in the image below.

Once the box appears in the cell, copy it, highlight the cells you also want it to appear in, and then paste it.

17. Hyperlink a cell to a website.

If you're using your sheet to track social media or website metrics, it can be helpful to have a reference column with the links each row is tracking. If you add a URL directly into Excel, it should automatically be clickable. But, if you have to hyperlink words, such as a page title or the headline of a post you're tracking, here's how.

Highlight the words you want to hyperlink, then press Shift K. From there a box will pop up allowing you to place the hyperlink URL. Copy and paste the URL into this box and hit or click Enter.

If the key shortcut isn't working for any reason, you can also do this manually by highlighting the cell and clicking Insert > Hyperlink .

18. Add drop-down menus.

Sometimes, you'll be using your spreadsheet to track processes or other qualitative things. Rather than writing words into your sheet repetitively, such as “Yes”, “No”, “Customer Stage”, “Sales Lead”, or “Prospect”, you can use dropdown menus to quickly mark descriptive things about your contacts or whatever you're tracking.

Here's how to add drop-downs to your cells.

Highlight the cells you want the drop-downs to be in, then click the Data menu in the top navigation and press Validation.

From there, you'll see a Data Validation Settings box open. Look at the Allow options, then click Lists and select Drop-down List. Check the In-Cell dropdown button, then press OK.

19. Use the format painter.

As you've probably noticed, Excel has a lot of features to make crunching numbers and analyzing your data quick and easy. But if you ever spent some time formatting a sheet to your liking, you know it can get a bit tedious.

Don't waste time repeating the same formatting commands over and over again. Use the format painter to easily copy the formatting from one area of the worksheet to another. To do so, choose the cell you'd like to replicate, then select the format painter option (paintbrush icon) from the top toolbar.

Excel Keyboard Shortcuts

Creating reports in Excel is time-consuming enough. How can we spend less time navigating, formatting, and selecting items in our spreadsheet? Glad you asked. There are a ton of Excel shortcuts out there, including some of our favorites listed below.

Create a New Workbook

PC: Ctrl-N | Mac: Command-N

Select Entire Row

PC: Shift-Space | Mac: Shift-Space

Select Entire Column

PC: Ctrl-Space | Mac: Control-Space

Select Rest of Column

PC: Ctrl-Shift-Down/Up | Mac: Command-Shift-Down/Up

Select Rest of Row

PC: Ctrl-Shift-Right/Left | Mac: Command-Shift-Right/Left

Add Hyperlink

PC: Ctrl-K | Mac: Command-K

Open Format Cells Window

PC: Ctrl-1 | Mac: Command-1

Autosum Selected Cells

PC: Alt-= | Mac: Command-Shift-T

Other Excel Help Resources

Use Excel to Automate Processes in Your Team

Even if you're not an accountant, you can still use Excel to automate tasks and processes in your team. With the tips and tricks we shared in this post, you'll be sure to use Excel to its fullest extent and get the most out of the software to grow your business.

Editor's Note: This post was originally published in August 2017 but has been updated for comprehensiveness.