4 outils Microsoft Excel cachés que vous ne connaissiez probablement pas

Microsoft Excel a des centaines d'outils et de fonctions, ce qui signifie qu'il est assez facile d'oublier certains des plus utiles. Dans ce guide, je partagerai quatre astuces cachées moins connues qui pourraient transformer la façon dont vous utilisez le logiciel de feuille de calcul populaire.

4

Recherche d'objectifs pour une planification à long terme

L'outil de recherche d'objectifs peu connu de Microsoft Excel est là pour vous aider lorsque vous connaissez l'objectif final, mais je ne sais pas comment y arriver.

Disons que vous prévoyez d'acheter une nouvelle maison dans deux ans. Actuellement, vous économisez 400 $ par mois à un taux d'intérêt annuel de 2,5%, ce qui signifie qu'au cours des deux années, vous économiserez 9833,55 $, comme calculé par la fonction de valeur future (FV):

=FV(B1/12,B2,-B3)

où B1 / 12 transforme le taux d'intérêt annuel (taux, cellule B1) en un taux d'intérêt mensuel, B2 est le nombre de paiements (NPER), et B3 La valeur de paiement (PMT) est-elle indiquée comme un nombre négatif (car il quitte votre compte ordinaire).

Cependant, vous visez à collecter un total de 15 000 $ pour couvrir le coût du dépôt et d'autres dépenses qui sont inévitablement accompagnées d'une maison en déménagement, vous devez donc déterminer combien vous devez économiser par mois sur deux ans pour atteindre cet objectif. C'est là que l'outil de recherche d'objectif d'Excel entre en jeu.

Tout d'abord, sélectionnez la cellule contenant le calcul (dans ce cas, cellule B4), et dans l'onglet Données sur le ruban, cliquez sur What-If Analysis> Recherche d'objectifs.

La valeur totale doit être un calcul. La recherche d'objectifs ne fonctionnera pas si vous entrez manuellement cette valeur.

La boîte de dialogue de recherche d'objectif qui apparaît a trois champs:

• Set Cell: Il s'agit de la cellule qui contient le calcul total actuel, qui, dans cet exemple, est la cellule B4. Depuis que vous avez sélectionné cette cellule avant de lancer la boîte de dialogue, elle s'est remplie automatiquement.
• Pour évaluer: C'est la valeur que vous devez atteindre pour atteindre l'objectif final. Dans ce cas, c'est 15000.
• En changeant de cellule: La variable qui peut être modifiée dans ce calcul est le montant enregistré par mois (cellule B3).

Après avoir cliqué sur « OK », Excel exécute plusieurs itérations de calcul avant d'ajuster la cellule en modifiant que vous avez référencé plus tôt. Dans ce cas, il vous indique que vous devez économiser 610,16 $ par mois pendant deux ans pour atteindre votre objectif.

Cependant, à ce stade, vous vous rendez compte que vous ne pouvez pas vous permettre d'économiser ce montant chaque mois. Au lieu de cela, vous décidez de continuer à mettre à 400 $ chaque mois, et vous déménagerez une fois que vous aurez atteint l'objectif de 15 000 $. Donc, cette fois, vous voulez Excel pour vous dire combien de temps cela prendra.

D'abord, réinitialiser manuellement le PMT Valeur dans la cellule B3 à 400 $. Ensuite, sélectionnez la cellule contenant le calcul de la valeur finale, puis cliquez sur What-If Analysis> OBJECT CHOSE dans l'onglet Données.

Encore une fois, la cellule définie est B4, et la valeur finale est toujours de 15000, mais la cellule qui peut être modifiée est la cellule du mois, qui est B2.

Enfin, cliquez sur « OK » pour révéler que l'économie de 15 000 $ en payant 400 $ par mois avec un taux d'intérêt de 2,5% prendra un peu plus de 36 mois.

Donc, l'outil d'objectif d'Excel vous a aidé à déterminer que si vous êtes désespéré de déménager dans deux ans, vous devrez collecter 610,16 $ par mois. D'un autre côté, si vous voulez continuer à payer seulement 400 £ par mois, vous pouvez déménager dans un peu plus de trois ans.

En plus de vous aider avec vos économies, l'outil d'objectif d'Excel peut vous aider à déterminer le nombre de produits que vous devez vendre pour atteindre votre objectif, ou le taux d'intérêt que vous devez obtenir pour rendre un prêt abordable.

L'outil de caméra de Microsoft Excel vous permet de prendre un instantané de certaines données et de le coller en tant qu'image. De plus, cette image est dynamique, ce qui signifie qu'elle met à jour pour refléter tout changement dans les données d'origine.

Cet outil peut être utile si vous souhaitez créer un tableau de bord qui tire les données de plusieurs feuilles de calcul dans votre classeur, et il peut également être pratique si vous travaillez avec de grands ensembles de données et souhaitez conserver des informations clés en vue. Étant donné que les données sont dupliquées en tant qu'image, elle se trouve au-dessus des cellules d'Excel sans affecter la disposition de votre feuille de calcul.

De tous les outils dont je discute dans ce guide, la caméra est probablement la plus cachée car elle n'est pas immédiatement accessible à travers le ruban. Au lieu de cela, vous devez l'ajouter à votre barre d'outils à accès rapide (QAT).

Pour ce faire, cliquez sur la flèche vers le bas sur le côté droit du ruban. Si vous pouvez voir «Masquer la barre d'outils à accès rapide», le QAT est déjà activé. Cependant, si vous voyez « Afficher la barre d'outils d'accès rapide », cliquez sur cette option pour l'activer.

Ensuite, cliquez sur la flèche vers le bas dans le QAT en haut de la fenêtre Excel et sélectionnez « Plus de commandes ».

Ensuite, dans la commande Choisissez dans le menu déroulant, cliquez sur « Toutes les commandes » et faites défiler vers et sélectionnez « Camera ». Ensuite, cliquez sur « Ajouter » pour l'ajouter à votre QAT, puis cliquez sur « OK » pour fermer la boîte de dialogue.

Maintenant, l'outil de caméra est assis sur votre QAT, prêt à être utilisé.

Imaginons que vous devez prendre un instantané de certaines données entrées dans une table Excel. Pour ce faire, sélectionnez les cellules contenant ces données et cliquez sur l'icône « Caméra » dans votre QAT.

Pour sélectionner toutes les cellules dans une table Excel, sélectionnez d'abord une cellule, puis appuyez sur Ctrl + a.

Maintenant, cliquez sur la cellule où vous voulez que la version d'image de ces données copiées soit dupliquée.

L'outil de caméra peut fonctionner sur des classeurs séparés, mais les modifications des données d'origine ne seront reflétées que dans l'image collée que si vous avez signé votre compte Microsoft et activé automatiquement sur les deux fichiers.

Étant donné que les données sont reproduites en tant qu'image, vous pouvez cliquer et faire glisser les poignées pour redimensionner le graphique, et vous pouvez formater l'instantané à l'aide de l'onglet Format d'image sur le ruban.

L'outil de caméra fonctionne également sur d'autres éléments d'Excel, comme les graphiques et les formes. Sélectionnez simplement les cellules derrière et autour de l'objet et cliquez sur l'outil de la caméra dans votre QAT. Une fois que vous avez collé les données en tant qu'image, recadrez l'image pour éliminer tout espace indésirable des bords.

Masquer les grilles (alt> w> v> g) et les boutons filtrants (ctrl + shift + l) à partir de vos données avant de les capturer avec l'outil de la caméra. Faire cela donne à l'image un aspect plus cher, et comme les boutons de filtre ne fonctionnent pas dans le graphique collé, il n'est pas nécessaire qu'ils soient là.

2

Groupes de données pour gérer de grands ensembles de données

Si vous travaillez sur une feuille de calcul Microsoft Excel qui contient de nombreuses colonnes, vous pourriez en avoir assez de faire défiler à plusieurs reprises à gauche et à droite pour lire vos données. De plus, parfois, certaines colonnes peuvent ne pas avoir toujours besoin d'être visibles.

Supposons que vous ayez ce classeur contenant des chiffres de vente mensuels pour divers magasins et un total annuel dans la colonne la plus à droite.

Pour analyser les données annuelles et rendre les chiffres plus faciles à lire, vous souhaitez masquer les colonnes B à Q, ce qui signifie que les colonnes A et R resteront côte à côte sur votre feuille de calcul.

À ce stade, vous pourriez être tenté de sélectionner les colonnes B à Q, cliquez avec le bouton droit sur l'un des en-têtes de colonne sélectionnés, et cliquez sur « Masquer ». Cependant, il est facile d'oublier que ces colonnes sont cachées et que le processus de non-non-là-bas est lourd.

Au lieu de cela, sélectionnez les colonnes B à Q en cliquant sur l'en-tête de la colonne B et en faisant glisser la sélection à la colonne Q.

Excel vous permet également d'utiliser la même méthode pour créer des groupes de lignes.

Ensuite, dans l'onglet Données, cliquez sur « contour » et sélectionnez la moitié supérieure du bouton « Group ».

Maintenant, vous pouvez cliquer sur la ligne d'indicateur MINUS SIGN ou groupe au-dessus de ces colonnes pour masquer ce groupe.

Remarquez comment, lorsque vous le faites, le signe moins se transforme en un signe plus, que vous pouvez cliquer pour réexaminer ce groupe si vous le souhaitez.

Vous pouvez également créer des sous-groupes au sein des groupes principaux, à condition que chaque sous-groupe soit séparé par une colonne vierge ou totale.

En utilisant le même exemple que ci-dessus, disons que vous souhaitez également effondrer les données mensuelles, ne laissant que les totaux trimestriels et annuels visibles. Cela est possible car les colonnes E, I, M et Q agissent comme des barrières entre chaque sous-groupe.

Ainsi, pour créer un sous-groupe pour Q1, sélectionnez les colonnes B à D, puis cliquez sur « Groupe » dans l'onglet Données.

Maintenant, en plus d'avoir un groupe principal qui inclut les cellules B à Q, votre feuille de calcul a également un sous-groupe qui comprend les cellules B à D.

Répétez ce processus pour les trimestres restants jusqu'à ce que tous les sous-groupes aient des lignes d'indicateurs de groupe individuelles et des signes moins.

Ensuite, cliquez sur les signes moins des quatre sous-groupes pour afficher uniquement les totaux trimestriels et annuels, en vous rappelant que vous pouvez cliquer sur les panneaux Plus pour les développer à nouveau.

En plus de cliquer sur les signes moins et plus pour s'effondrer et développer un groupe ou un sous-groupe, vous pouvez cliquer sur les boutons numériques à gauche des lignes d'indicateur de groupe. Cliquer sur le plus haut nombre révèle toutes les données qui ont été regroupées, tandis que le clic sur le plus bas nombre réduit les données au plus petit sous-groupe que vous ayez créé.

1

Formats de nombres personnalisés pour une visualisation des données rapide

L'outil de format de numéro personnalisé est l'une de mes fonctionnalités cachées préférées dans Microsoft Excel car elle m'économise probablement le plus.

Chaque cellule d'une nouvelle feuille de calcul a le format de numéro général.

Cela signifie que les nombres sont affichés exactement tels qu'ils sont dactylographiés, sauf s'il s'agit d'un nombre décimalisé trop large pour la cellule ou un nombre qui commence par zéro.

Cependant, lorsque vous insérez un certain type de nombre – comme une date ou un pourcentage – dans une cellule, Excel le reconnaît et met à jour le format de nombre en conséquence.

Un format de numéro caché est personnalisé, qui vous permet de formater une cellule d'une manière qui n'est pas répertoriée dans les formats de numéro standard d'Excel.

Sélectionnez la cellule ou les cellules auxquelles vous souhaitez appliquer le formatage que vous êtes sur le point de saisir, et dans l'onglet Home sur le ruban, cliquez sur le lanceur de la boîte de dialogue dans le coin du groupe numérique.

Alternativement, appuyez sur CTRL + 1 pour lancer la boîte de dialogue des cellules du format, appuyez sur Tab pour lancer le menu de catégorie et tapez Cus.

Les informations que vous entrez dans le champ de type dépend des types de données que vous souhaitez formater.

Le premier argument que vous entrez formate des nombres positifs, les nombres négatifs du deuxième formats, le troisième formats zéros et le quatrième formats de texte. Remarquez comment les demi-colons sont utilisés pour séparer les arguments.

POSITIVE;NEGATIVE;ZERO;TEXT

Une combinaison que vous pouvez taper dans ce champ est:

;;;

Ce qui indique à Excel que vous souhaitez que la cellule apparaisse vide car vous n'avez spécifié aucun formatage pour aucun type de données.

D'un autre côté, tapant:

(Green)#.00;(Red)#.00;(Blue);

Transforme les nombres positifs en une valeur verte avec deux décimales, les nombres négatifs en valeur rouge avec deux décimales, Zeros Blue, et produit une cellule vierge pour toute valeur de texte.

Vous pouvez être habitué à atteindre le même résultat en utilisant l'outil de mise en forme conditionnel d'Excel, car il est plus important sur le ruban que le format de numéro personnalisé. Cependant, cela vous oblige à saisir diverses règles et prend beaucoup plus de temps.

Si vous souhaitez formater des valeurs en utilisant des couleurs autres que le noir, le blanc, le rouge, le vert, le bleu, le jaune, le magenta et le cyan, tapez le code de couleur entre parenthèses carrées au lieu du nom de couleur.

Vous pouvez également ajouter des symboles au format de numéro personnalisé d'une cellule. Par exemple, taper:

▲;▼;"=";

Affiche une flèche vers la place au lieu de nombres positifs, une flèche vers le bas au lieu de nombres négatifs et un signe égal pour les zéros.

Si vous êtes le système d'exploitation Windows, appuyez sur Alt + 30 pour ▲ et Alt + 31 pour ▼.

De plus, vous pouvez combiner tous les codes ci-dessus pour créer des cellules bien formatées et améliorer l'attrait visuel de vos données. Par exemple, taper

(Green)▲#%;(Red)▼#%;"-";

Pour le format personnalisé des cellules D2 à D12 produit ce résultat:

---

Les raccourcis clavier de Microsoft Excel sont également une autre fonctionnalité cachée qui peut vraiment accélérer votre flux de travail. Parallèlement à l'utilisation du célèbre CTRL + C (Copie) et Ctrl + V (collez), prenez le temps d'apprendre certains des raccourcis moins connus, comme F2 pour entrer le mode d'édition d'une cellule et Ctrl + Shift + L pour ajouter des boutons de filtre à la première ligne de vos données.