26 astuces Excel pour devenir un pro du tableur

Supposons que vous changiez non seulement l'habillage d'une cellule, mais également l'apparence entière - la police, la couleur, peu importe. Et vous voulez l'appliquer à beaucoup, beaucoup d'autres cellules. L'astuce est l'outil Format Painter , celui qui se trouve sur l'onglet Accueil qui ressemble à un pinceau.

Sélectionnez la vente que vous aimez, cliquez sur l'icône , puis cliquez sur une cellule différente pour peindre dans le format - elles correspondront en apparence, pas en contenu. Vous voulez l'appliquer à plusieurs onglets ? Double-cliquez sur l'icône du pinceau, puis cliquez sur plusieurs cellules.

Taper dans des cellules de feuille de calcul peut être frustrant, car le texte par défaut que vous tapez est de continuer indéfiniment, sans revenir à une nouvelle ligne. Vous pouvez changer cela. Créez une nouvelle ligne en tapant Alt + Entrée (appuyer sur Entrée seul vous fait sortir de la cellule). Ou, cliquez sur l' option Habiller le texte sous l'onglet Accueil en haut de l'écran, ce qui signifie que tout le texte s'habille juste au bord de la cellule dans laquelle vous vous trouvez. Redimensionnez la ligne/colonne et le texte s'adapte à nouveau.

Si vous avez plusieurs cellules qui ont des dépassements de texte, sélectionnez-les toutes avant de cliquer sur Envelopper le texte. Ou sélectionnez toutes les cellules avant même de les saisir et cliquez sur Envelopper le texte. Ensuite, tout ce que vous tapez sera enveloppé dans le futur.

C'est une évidence, mais si facilement négligée. Vous commencez à taper une série de choses répétitives comme des dates (1/1/20, 1/2/20, 1/3/20, etc.) et vous savez que vous êtes parti pour une longue journée. Au lieu de cela, commencez la série et déplacez le curseur sur l'écran vers la partie inférieure droite de la dernière cellule, la poignée de recopie. Lorsqu'il se transforme en signe plus ( + ), cliquez et faites glisser vers le bas pour sélectionner toutes les cellules que vous devez remplir. Ils se rempliront comme par magie en utilisant le motif que vous avez commencé. Il peut également monter une colonne, ou à gauche ou à droite sur une ligne.

Mieux encore, vous pouvez remplir automatiquement sans trop de motif. Encore une fois, sélectionnez une ou plusieurs cellules, déplacez-vous vers la poignée de remplissage, cliquez avec le bouton droit et faites glisser. Vous obtiendrez un menu d'options. Plus vous saisissez de données au début, mieux l'option Remplir la série créera vos options de remplissage automatique. Consultez ce tutoriel Microsoft.

Flash Fill remplira intelligemment une colonne en fonction du modèle de données qu'il voit dans la première colonne (cela aide si la ligne du haut est une ligne d'en-tête unique). Par exemple, si la première colonne contient tous les numéros de téléphone au format "2125034111" et que vous souhaitez qu'ils ressemblent tous à "(212)-503-4111", commencez à taper. À la deuxième cellule, Excel devrait reconnaître le modèle et afficher ce qu'il pense que vous voulez. Appuyez simplement sur Entrée pour les utiliser.

Cela fonctionne avec des nombres, des noms, des dates, etc. Si la deuxième cellule ne vous donne pas une plage précise, saisissez-en davantage - le modèle peut être difficile à reconnaître. Ensuite, allez dans l'onglet Données et cliquez sur le bouton Flash Fill. Consultez ce tutoriel Microsoft pour en savoir plus.

Il existe des moyens beaucoup plus rapides de sélectionner un ensemble de données que d'utiliser la souris et de faire glisser le curseur, en particulier dans une feuille de calcul pouvant contenir des centaines de milliers de lignes ou de colonnes. Cliquez dans la première cellule que vous souhaitez sélectionner et maintenez la touche Ctrl + Maj enfoncée , puis appuyez sur la flèche vers le bas pour obtenir toutes les données de la colonne ci-dessous, sur la flèche vers le haut pour obtenir toutes les données ci-dessus ou sur la flèche gauche ou droite pour tout obtenir. la ligne (à gauche ou à droite, bien sûr). Combinez les directions et vous pouvez obtenir une colonne entière ainsi que tout ce qui se trouve dans les rangées à gauche ou à droite. Il sélectionnera uniquement les cellules contenant des données (même des données invisibles).

Si vous utilisez Ctrl+Maj+Fin , le curseur passera à la cellule la plus basse à droite contenant des données, en sélectionnant tout ce qui se trouve entre les deux, même les cellules vides. Donc, si le curseur se trouve dans la cellule supérieure gauche (A1), c'est tout.

Ctrl + Maj + * (l'astérisque) peut être plus rapide, car il sélectionnera l'ensemble de données contigu d'une cellule, mais s'arrêtera aux cellules vides.

Supposons que vous ayez une colonne pleine de noms, du premier à l'avant-dernier, mais que vous vouliez deux colonnes qui les séparent. Sélectionnez les données, puis dans l'onglet Données (en haut), cliquez sur Texte vers les colonnes. Choisissez de les séparer par des délimiteurs (basés sur des espaces ou des virgules, parfaits pour les valeurs de données CSV) ou par une largeur fixe. La largeur fixe est utilisée lorsque toutes les données sont entassées dans la première colonne, mais séparées par un nombre fixe d'espaces ou de points. Le reste est magique, avec des options supplémentaires pour certains numéros.

Vous avez un tas de rangées. Vous voulez qu'ils soient des colonnes. Ou vice versa. Vous deviendriez fou en déplaçant les choses cellule par cellule. Copiez ces données, sélectionnez Collage spécial , cochez la case Transposer et cliquez sur OK pour coller dans une orientation différente. Les colonnes deviennent des lignes, les lignes deviennent des colonnes.

Pour une raison quelconque, vous devrez peut-être écrire la même chose encore et encore dans les cellules d'une feuille de calcul. C'est atroce. Cliquez simplement sur l'ensemble des cellules, soit en faisant glisser votre curseur, soit en maintenant la touche Ctrl enfoncée pendant que vous cliquez sur chacune d'entre elles. Tapez-le sur la dernière cellule, puis appuyez sur Ctrl + Entrée (pas sur Entrée seule) - ce que vous avez tapé va dans chaque cellule sélectionnée.

Cela fonctionne également avec les formules et modifiera les références de cellule pour fonctionner avec la ligne/colonne dans laquelle se trouvent les autres cellules.

Disons que vous avez une énorme quantité de nombres au format décimal que vous souhaitez afficher sous forme de pourcentages. Le problème est que le chiffre 1 ne devrait pas être 100%, mais c'est ce qu'Excel vous donne si vous cliquez simplement sur le bouton Style de pourcentage (ou appuyez sur Ctrl-Shift-% ).

Vous voulez que 1 soit 1 %. Vous devez donc le diviser par 100. C'est là que le collage spécial entre en jeu.

Tout d'abord, tapez 100 dans une cellule et copiez-le. Ensuite, sélectionnez tous les nombres que vous voulez reformater, sélectionnez Collage spécial , cliquez sur le bouton radio " Diviser ", et boum c'est la dynamite : vous avez des nombres convertis en pourcentages. Cela fonctionne également pour ajouter, soustraire ou multiplier instantanément des nombres, évidemment.

Vous pouvez mettre un graphique dans n'importe quel élément d'un graphique Excel. N'importe quel élément. Chaque barre, morceau de tarte, etc., peut soutenir sa propre image. Par exemple, ci-dessus, il y a un drapeau d'état du Dakota du Sud sur le graphique à secteurs (placé en sélectionnant la tranche, en utilisant le menu déroulant des options de la série et en sélectionnant " Remplissage d'image ou de texture "), plus un logo PCMag intégré (placé avec le bouton Images de l'onglet Insérer ). Vous pouvez même utiliser " pas de remplissage " du tout, ce qui a causé cette tranche manquante.

Les images clipart peuvent être coupées et collées sur un élément - des billets d'un dollar pour montrer les dollars dépensés, des gouttes d'eau pour les coûts de plomberie, ce genre de choses. Mélanger et assortir trop d'éléments graphiques rend la lecture impossible, mais les options dont vous disposez valent la peine d'être bricoler. Laissez votre graphiste résident les vérifier avant de les utiliser.

Excel a plus de types de graphiques que Jimmy Carter n'a de cacahuètes, mais il est presque impossible de trouver un graphique par défaut parfait pour votre présentation. Heureusement, la capacité d'Excel à personnaliser tous les graphiques est exemplaire. Mais quand vous devez en recréer un, c'est pénible. Ce n'est pas obligé. Enregistrez votre graphique d'origine en tant que modèle.

Une fois qu'un graphique est perfectionné, faites un clic droit dessus. Sélectionnez Enregistrer comme modèle . Enregistrez un fichier avec une extension CRTX dans votre dossier de modèles Microsoft Excel par défaut. Une fois cela fait, appliquer le modèle est un gâteau. Sélectionnez les données que vous souhaitez représenter graphiquement, accédez à l' onglet Insertion , cliquez sur Graphiques recommandés , puis sur l'onglet Tous les graphiques et le dossier Modèles . Dans la zone Mes modèles, sélectionnez celui à appliquer, puis cliquez sur OK .

Certains éléments, comme le texte réel dans les légendes et les titres, ne seront pas traduits à moins qu'ils ne fassent partie des données sélectionnées. Vous obtiendrez toutes les sélections de polices et de couleurs, les graphiques intégrés, même les options de série (comme une ombre portée ou une lueur autour d'un élément de graphique).

Celui-ci, appelé Somme 3D, fonctionne lorsque vous avez plusieurs feuilles dans un classeur qui ont toutes la même disposition de base, par exemple des relevés trimestriels ou annuels. Par exemple, dans la cellule B3, vous avez toujours le montant en dollars pour la même semaine correspondante au fil du temps.

Dans une nouvelle feuille de calcul du classeur, accédez à une cellule et tapez une formule telle que =sum('Y1:Y10'!B3). Cela indique une formule SOMME (en additionnant les choses) pour toutes les feuilles intitulées Y1 à Y10 (donc 10 ans), et en regardant la cellule B3 dans chacune. Le résultat sera la somme de toutes les 10 années. C'est un bon moyen de créer une feuille de calcul principale qui renvoie à des données en constante évolution.

Il est facile de masquer une ligne ou une colonne - il suffit de sélectionner le tout en cliquant sur l'en-tête de lettre ou de chiffre, de cliquer avec le bouton droit de la souris et de sélectionner "Masquer". (Vous pouvez afficher en sélectionnant toutes les colonnes de chaque côté en même temps, en cliquant avec le bouton droit de la souris et en sélectionnant "Afficher"). Mais que se passe-t-il si vous n'avez qu'une petite section de données mal placées que vous souhaitez masquer, mais que vous souhaitez toujours pouvoir travailler avec ? Facile. Mettez les cellules en surbrillance, cliquez avec le bouton droit de la souris et choisissez Format de cellule . Dans l' onglet Numéro en haut, accédez à Catégorie et sélectionnez « Personnalisé ». Tapez trois points-virgules ( ;;; ) dans le champ Type :. Cliquez sur OK. Maintenant, les nombres ne sont pas visibles, mais vous pouvez toujours les utiliser dans des formules.

Votre classeur Excel typique - le fichier dans lequel vous travaillez - peut être chargé avec de nombreuses feuilles de calcul (chaque feuille étant indiquée par un onglet en bas, que vous pouvez nommer). Masquez une feuille si vous le souhaitez, plutôt que de la supprimer, ce qui rend ses données toujours disponibles non seulement pour référence, mais également disponibles pour les formules sur d'autres feuilles du classeur. Cliquez avec le bouton droit sur l'onglet de la feuille inférieure et sélectionnez Masquer . Lorsque vous avez besoin de le retrouver, vous devez aller dans l' onglet Affichage en haut, cliquer sur Afficher et choisir le nom de la feuille dans la liste qui apparaît.

Il existe également un bouton Masquer dans le menu de l'onglet Affichage en haut. Que se passe-t-il lorsque vous cliquez dessus ? Il masque l' intégralité du classeur que vous utilisez. Il semble que vous ayez fermé le fichier, mais Excel continue de fonctionner. Lorsque vous fermez le programme, il vous demandera si vous souhaitez enregistrer les modifications apportées au classeur masqué. Lorsque vous allez ouvrir le fichier, Excel vous donne ce qui semble être un classeur vierge, jusqu'à ce que vous cliquiez à nouveau sur Afficher.

Lorsque vous affichez un classeur entier, vous verrez probablement un classeur répertorié que vous ne saviez pas que vous avez caché : le fichier Personal.XLSB. Il s'agit en fait du classeur personnel qu'Excel a créé pour vous ; il s'ouvre en tant que classeur masqué à chaque démarrage d'Excel. La raison de l'utiliser ? Macros .

Lorsque vous créez une macro, elle ne fonctionne pas sur chaque feuille de calcul que vous créez par défaut (comme c'est le cas dans Microsoft Word) - une macro est liée au classeur dans lequel elle a été créée. Cependant, si vous stockez la macro dans Personnel. XLSB, il sera disponible tout le temps, dans tous vos fichiers tableur.

L'astuce est que, lorsque vous enregistrez la macro, dans le champ "Enregistrer la macro dans", sélectionnez "Classeur de macros personnelles". (Enregistrez une macro en activant l'onglet Développeurs - accédez à l' onglet Fichier , sélectionnez Options , cliquez sur Personnaliser le ruban , puis dans la zone Onglets principaux , cochez Développeurs , cliquez sur OK .)

Des livres entiers ont été consacrés aux tableaux croisés dynamiques. Ce sont des résumés de votre gigantesque collection de données qui facilitent grandement l'analyse des informations en fonction de vos points de référence. Par exemple, si vous disposez de l'ensemble des notes de tous vos élèves pour tous les tests de l'année entière, un tableau croisé dynamique peut vous aider à limiter les choses à un élève pendant un mois. Il appartient à toute personne disposant de données volumineuses de jouer avec elles (faites une copie des données originales pour jouer avec en premier).

Pour en créer un, vérifiez que toutes les colonnes et lignes sont intitulées comme elles le devraient, puis sélectionnez Tableau croisé dynamique dans l' onglet Insertion . Mieux encore, essayez l'option Tableaux croisés dynamiques recommandés pour voir si Excel peut choisir le bon type pour vous. Ou essayez le PivotChart , qui crée un tableau croisé dynamique avec un graphique inclus pour le rendre plus facile à comprendre.

Vous regardez une énorme quantité de données et vous vous demandez où se trouvent les faits saillants ? Qui a le score le plus élevé (ou le plus bas), quels sont les cinq premiers, etc. ? La mise en forme conditionnelle d'Excel fera tout, de la mise en place d'une bordure autour des surbrillances au codage couleur de l'ensemble du tableau. Il construira même un graphique dans chaque cellule afin que vous puissiez visualiser le haut et le bas de la plage de nombres en un coup d'œil. (Ci-dessus, les nombres les plus élevés sont en vert rapide, les plus bas en rouge hésitant, avec un spectre entre les deux.) mots, dates récurrentes, valeurs en double, etc. Il existe même une option supérieur à/inférieur à pour que vous puissiez comparer les changements de nombre.

Vous créez une feuille de calcul que d'autres peuvent utiliser ? Si vous souhaitez créer un menu déroulant de sélections à utiliser dans des cellules particulières (afin qu'elles ne puissent pas tout gâcher !), c'est facile. Mettez la cellule en surbrillance, accédez à l' onglet Données , puis cliquez sur Validation des données . Sous "Autoriser :", sélectionnez " Liste ". Ensuite, dans le champ "Source :", saisissez une liste , avec des virgules entre les options. Ou, cliquez sur le bouton à côté du champ Source et revenez dans la même feuille pour sélectionner une série de données - c'est la meilleure façon de gérer de grandes listes. Vous pouvez masquer ces données plus tard, cela fonctionnera toujours. La validation des données est également un bon moyen de restreindre les données saisies, par exemple, donnez une plage de dates, et les gens ne peuvent pas saisir de dates avant ou après ce que vous spécifiez. Vous pouvez même créer le message d'erreur qu'ils verront.

Excel permet de prendre très facilement une capture d'écran de tout autre programme ouvert sur votre bureau et de l'insérer dans une feuille de calcul. Allez simplement dans l'onglet Insertion , sélectionnez Capture d'écran et vous obtiendrez un menu déroulant affichant une vignette de tous les programmes ouverts. Choisissez-en un pour insérer l'image en taille réelle. Redimensionnez-le comme vous le souhaitez.

Il y a trente-cinq ans, l'idée de placer des données d'Excel dans Word ou PowerPoint était époustouflante dans le monde des suites Office. Aujourd'hui, il n'en est rien. Que vous preniez des cellules de données ou un graphique complet, copiez et collez dans l'autre programme. La chose à savoir est qu'il s'agit d'un processus de liaison et d' intégration - si vous modifiez les données dans la feuille de calcul, cela changera également dans le Word DOC ou PowerPoint PPT. Si vous ne le souhaitez pas, collez-le sous forme de graphique. Utilisez l'outil spécial de collage de Word pour cela. Ou, lorsque vous le prenez à partir d'Excel, accédez à l' onglet Accueil en haut, sélectionnez le menu Copier et utilisez l'option Copier en tant qu'image . Ensuite, vous pouvez coller le graphique dans n'importe quel programme.

Lorsque vous écrivez une formule, vous référencez les cellules par leur position, comme A1. Si vous copiez une formule et la collez dans la cellule suivante, Excel décalera cette cellule référencée, de sorte qu'elle dira A2 à la place. Pour éviter tout décalage, utilisez le signe dollar ($). Tapez $A1 et coupez-collez-le dans une nouvelle cellule, par exemple, ce qui évite un décalage dans la colonne (A) ; A$1 empêche le décalage dans la ligne (1) et $A$1 empêche le changement de décalage dans n'importe quelle direction lors de la copie d'une formule.

C'est pratique lorsque vous avez une seule cellule à utiliser dans tout un tas de formules. Supposons que vous vouliez tout diviser par 100. Vous pourriez faire une formule comme =(A1/100), mais cela signifie que vous ne pouvez pas changer facilement le 100 à tous les niveaux. Mettez le 100 dans la cellule B1 et utilisez =(A1/B1) - mais ensuite, lorsque vous le coupez et le collez, il se transforme en =(A2/B2), puis =(A3/B3), etc. Le $ corrige cela : =(A1/$B$1) peut être coupé et collé sur une ligne, mais la référence $B$1 ne change jamais. Ensuite, vous pouvez modifier la valeur de 100 dans la cellule selon vos besoins pour expérimenter d'autres modifications.

Si vous ne savez pas exactement quelles informations vous souhaitez appliquer aux données dans Excel, essayez le menu Analyse rapide pour parcourir rapidement les options. Sélectionnez les données et cliquez sur la case Analyse rapide qui apparaît en bas à droite. Vous obtiendrez un menu qui apparaîtra avec des options pour appliquer rapidement une mise en forme conditionnelle, créer des graphiques, gérer des totaux, afficher des sparklines, etc.

Excel, comme tout bon logiciel, possède de nombreux excellents raccourcis clavier. Voici quelques-uns des meilleurs.

Ctrl+ ; —Insère la date d'aujourd'hui.
Ctrl+Maj+ : —Insère l'heure actuelle (les deux-points correspondent à ce qui se trouve dans une lecture d'horloge, comme 12h00).
Ctrl+Maj+# — Modifie le format d'une date.
Ctrl+5 : applique un barré au texte d'une cellule.
Ctrl+0 — Masque la colonne actuelle.
Ctrl+9 —Masque la ligne actuelle.
Ctrl+F6 —Bascule entre les classeurs ouverts (c'est-à-dire, ouvre des fichiers Excel dans différentes fenêtres).
Ctrl+` — C'est la marque d'accent, vers le haut par la touche 1. Ce combo bascule la vue dans la feuille pour afficher toutes les formules.
Ctrl+PageUp ou PageDown —Décalage rapide entre les feuilles du classeur actuellement ouvert.
F2 —Démarre la modification de la cellule actuellement sélectionnée (beaucoup plus rapide qu'un double-clic).
Maj+F10 —Ouvre le menu contextuel de la cellule dans laquelle vous vous trouvez.

Vous avez des chiffres dans une feuille de calcul sur laquelle vous souhaitez effectuer un calcul rapide, sans avoir à vous soucier d'aller dans une nouvelle cellule et de créer une formule SOMME pour le travail ? Excel offre désormais un moyen rapide de le faire. Cliquez sur la première cellule, maintenez la touche Ctrl enfoncée et cliquez sur une deuxième cellule. Regardez la barre d'état en bas et vous verrez la somme des cellules calculées pour vous.

Gardez votre doigt sur Ctrl et cliquez sur autant de cellules que vous le souhaitez, la barre d'état continuera d'afficher la somme de toutes les cellules. (Cliquez sur une cellule avec des lettres/mots comme contenu, elle est ignorée.) Mieux encore, faites un clic droit sur la barre d'état pour obtenir le menu Personnaliser la barre d'état et vous pouvez choisir d'ajouter d'autres éléments qui peuvent être calculés rapidement comme celui-ci, tels que comme voir la moyenne ou le nombre de cellules sur lesquelles vous avez cliqué (ou le nombre numérique, c'est-à-dire le nombre de cellules sur lesquelles vous avez cliqué qui ont en fait des nombres).

Travailler avec un ensemble de données volumineux dans une feuille de calcul peut être difficile, en particulier lorsque vous faites défiler vers le haut/bas ou vers la gauche/droite et que les lignes et les colonnes peuvent être difficiles à suivre. Il existe une astuce simple pour cela si vous avez une ligne ou une colonne d'en-tête, où la première ligne/colonne a un descripteur. Vous le figez pour que lorsque vous faites défiler, cette ligne et/ou colonne (ou plusieurs lignes et/ou colonnes) ne bougent pas.

Accédez à l' onglet Affichage et recherchez Figer les volets . Vous pouvez facilement geler la ligne du haut (sélectionnez Geler la ligne du haut ) ou la première colonne (sélectionnez Geler la première colonne ). Vous pouvez faire les deux à la fois en cliquant sur la cellule en B2 et en sélectionnant simplement Figer les volets . C'est là que c'est amusant - sélectionnez n'importe quelle autre cellule et gèlez également tous les volets au-dessus et à gauche de celle-ci. Sélectionnez la cellule C3 par exemple et les deux lignes au-dessus et les deux colonnes à gauche ne défileront pas. Vous pouvez le voir dans la capture d'écran ci-dessus, indiquée par les lignes de grille sombres.

Lorsque vous souhaitez vous débarrasser du gel, vous pouvez simplement sélectionner Dégeler les volets dans le menu.

Les feuilles de calcul peuvent être énormes et vous devrez peut-être interagir avec différentes zones de la feuille de calcul à ce qui semble être en même temps, comme couper et coller des informations de haut en bas encore et encore. S'il s'agit de centaines de milliers de cellules, le défilement qui prendrait pourrait vous donner la nausée. Ou, vous pouvez simplement ouvrir une deuxième fenêtre sur votre bureau avec une vue d'exactement la même feuille de calcul. C'est facile. Dans l' onglet Affichage , cliquez sur Nouvelle fenêtre . Vous pouvez également cliquer sur Réorganiser tout pour les organiser à l'écran d'une manière qui vous convient. Vous pouvez les voir disposés horizontalement ci-dessus. Ensuite, tapez quelque chose dans une cellule dans une fenêtre, vous pouvez le voir apparaître dans l'autre fenêtre. Cette astuce est particulièrement pratique si vous avez deux moniteurs.

Vous pensez tout savoir sur Microsoft Word ? Voici quelques astuces cachées et des astuces pour gagner du temps que vous devez connaître sur l'application de traitement de texte de Redmond.