25 astuces pour maîtriser Excel

 

 Raccourcis clavier spécifiques à la gestion des tableaux croisés dynamiques.

Nous avons rassemblé 25 astuces à utiliser sur Excel, des raccourcis clavier spécifiques à la gestion des tableaux croisés dynamiques.

Microsoft Excel est sans doute le logiciel le plus complexe de la suite Microsoft Office. Il est assez simple à prendre en main pour créer des tableaux. Mais des astuces existent pour profiter des très, très nombreuses fonctionnalités du logiciel. Elles sont basées sur Microsoft Excel 2010. De légères variantes peuvent apparaître, notamment pour les versions antérieures à 2007. Si vous utilisez d’autres astuces au quotidien, vous pouvez les partager ! De la même manière, n’hésitez pas à commenter l’article si vous éprouvez des difficultés à réaliser telle ou telle action sur Excel, nous tâcherons d’y répondre rapidement.

Si vous souhaitez aller plus loin, vous pouvez aussi vous former pour devenir un expert d’Excel.

Les raccourcis clavier sur Microsoft Excel

Comme de nombreux logiciels, quelques raccourcis bien utiles permettent d’être plus productif sur Excel. Outre les traditionnels Ctrl+S pour sauvegarder un document ou Ctrl+C pour copier un élément, ces 10 raccourcis spécifiques à Excel permettent d’aller plus vite.

·         Recalculer les données : F9

·         Insérer la date du jour : CTRL+; (point virgule)

·         Insérer l’heure : CTRL+: (deux points)

·         Atteindre une cellule ou un tableau : F5

·         Sélectionner toute une colonne active : CTRL+Espace

·         Sélectionner toute une ligne active : SHIFT+Espace

·         Sélectionner toute la feuille Excel : CTRL+A

·         Faire un collage spécial sur Excel (pour ne coller que les données etc.) : CTRL+ALT+V

·         Insérer une somme automatique : ALT+=

·         Masquer (et surtout ré-afficher) le ruban des fonctionnalités : CTRL+F1

Les fonctions Excel

Sur Excel, les fonctions sont très nombreuses. Elles permettent d’effectuer des calculs, rechercher des données, ou obtenir des informations. Pour insérer une fonction, il suffit de cliquer sur l’icône Fx présent sous le ruban. Toutes les cellules qui contiennent une fonction commencent par le caractère =. Si vous connaissez bien certaines fonctions, vous pouvez y accéder directement en saisissant = puis le nom de la fonction au sein d’une cellule. Pour mieux cerner le potentiel des fonctions Excel, nous vous conseillons de consulter notre liste des fonctions Excel à connaître.

Nommer un tableau sur Excel

Dans certains cas, il peut être utile de nommer un tableau. Sélectionnez la plage à nommer (exemple : de la cellule B2 à la cellule E7). Cliquez ensuite sur le champ situé à gauche, sous le ruban, puis inscrivez le nom de votre choix. Dans cet exemple, notre tableau se nomme Data. Une fois nommé, vous pouvez utiliser les données de votre tableau très facilement : pour connaître la somme des données présentes dans notre tableau, un simple =SOMME(Data) suffit. Vous pouvez accéder au gestionnaire de noms via l’onglet Formules du ruban.

Le copier coller des fonctions Excel

Très souvent, les fonctions d’un tableau se suivent et se ressemblent. Pour cette raison, Excel utilise certaines règles lorsque vous copiez collez une fonction pour conserver la logique de vos calculs. Dans l’exemple ci-dessous, la colonne SOMME correspond la somme des valeurs de la ligne. Il suffit de copier la fonction D2 et la coller en D3 pour obtenir la somme des données de la troisième ligne.

Créer une suite logique grâce au cliquer glisser

Au lieu de copier coller les formules, vous pouvez les dupliquer grâce au cliquer glisser. Il suffit de sélectionner la(les) cellule(s) source, puis placer son curseur en bas à droite de la cellule. Une petite croix va apparaître, permettant de copier coller facilement la formule (ou une série de formules) vers le côté ou vers le bas. Certaines valeurs, telles que des plages de cellules, seront remplacées automatiquement pour conserver la logique de vos fonctions.

Vous pouvez également créer une suite logique grâce à cette astuce, mais vous devez obligatoirement sélectionner plusieurs cellules. Exemple : si deux cellules adjacentes contiennent deux dates qui se suivent (le 16 octobre et le 17 octobre par exemple), vous pouvez les sélectionner, cliquer sur la petite croix en bas à droite de la cellule et glisser votre curseur sur plusieurs cellules pour afficher le 18 octobre, le 19 octobre, le 20 octobre et ainsi de suite. Ceci marche aussi pour les suites de nombres.

Figer la ligne, la colonne ou la cellule dans une fonction Excel

Dans certains cas, on souhaiter « figer » une ligne, une colonne ou une cellule dans une suite de fonctions. Pour toujours diviser un calcul par la même cellule par exemple, quel que soit l’emplacement de la formule. Pour ce faire, lorsque vous saisissez votre formule, il suffit d’appuyer sur la touche F4 lorsque la cellule en question est mentionnée. Par exemple, inscrivez =SOMME(D4, puis appuyez sur F4. Le début de votre fonction va se transformer en =SOMME($D$4. Le symbole dollar indique que l’élément suivant est figé (ici, la colonne D et la ligne 4 sont figés). Il suffit d’appuyer à nouveau sur F4 pour figer uniquement la ligne ou la colonne. Une astuce à connaître !

Appliquer une mise en forme selon les données d’un tableau

Pour mieux visualiser les valeurs d’un tableau, vous pouvez appliquer une mise en forme conditionnelle. Rendez-vous dans l’onglet Accueil du ruban, puis Mise en forme conditionnelle et choisissez les règles de votre choix. Vous pouvez également afficher des icônes selon les données du tableau.

Modifier l’affichage d’une cellule selon le type de données

Excel permet de modifier l’affichage d’une cellule selon le type de données. Il peut s’agir d’un texte, d’une valeur, d’une date, d’un prix… Sélectionnez la cellule (ou la plage de cellule de votre choix), faîtes un clic droit, puis choisissez « format de cellule ».

Cette astuce permet aussi de choisir le nombre de chiffres après la virgule. Il suffit de choisir la catégorie « Nombre » puis le nombre de décimales après la virgule. Pratique !

Les Tableaux Croisés Dynamiques sur Excel

C’est LA fonctionnalité de Microsoft Excel. Souvent copiée, jamais égalée. Elle permet de créer des tableaux complexes, en croisant plusieurs plages de données. Nous allons prendre un exemple concret pour détailler les possibilités offertes par cet outil : celles des statistiques Facebook.

Nous avons donc exporté les statistiques de la page Facebook du Blog du Modérateur (publications) sur une période donnée (non significative) pour tenter d’évaluer la portée des publications selon l’heure, le jour, le type de contenu partagé. Nous avons retraité certaines données afin de mettre en évidence les heures et les jours des postes. Une fois nos données mises au propre, au sein de l’onglet Insertion, nous cliquons sur Tableau Croisé Dynamique pour en insérer un. Nous sélectionnons l’intégralité des données et créons notre TCD dans une nouvelle feuille de calcul.

Un tableau s’affiche alors, ainsi que la liste des champs du tableau croisé dynamique et quatre zones :

·         Étiquettes de colonnes : l’heure, dans notre cas,

·         Étiquettes de lignes : le jour, dans notre cas,

·         Valeurs : la portée des publications dans notre cas,

·         Filtre du rapport : le type de contenu dans notre cas.

Il suffit de cliquer déposer les champs disponibles dans les bonnes zones pour créer votre tableau croisé dynamique. En général, il est bon de vérifier les paramètres des champs de valeur (via un clic droit sur les données présentes dans la zone valeur). Dans notre cas, il fut nécessaire de choisir « Moyenne » plutôt que « Nombre » pour obtenir la moyenne des portées. Voici le tableau que nous obtenons, après avoir ajouté une mise en forme conditionnelle. Il permet de mettre en évidence la portée moyenne des publications selon l’heure et le jour de publication, filtré selon le type de post (il s’agit ici d’un exemple non-représentatif, les données ne sont pas assez nombreuses).

Modifier la hauteur d’une ligne ou la largeur d’une colonne

Pour modifier la taille d’une ligne ou d’une colonne, il suffit de faire un clic droit sur le numéro de la ligne ou la lettre de la colonne puis choisir « hauteur de ligne » ou « largeur de colonne ». Vous pouvez spécifier la largeur par défaut des colonnes grâce au bouton « Format » de l’onglet Accueil du ruban.

Insérer une ligne ou une colonne

Pour insérer une ligne, il suffit de faire un clic droit sur le numéro d’une ligne ; même principe pour une colonne, il suffit de faire un clic droit sur la lettre d’une colonne. Celle-ci sera toujours insérée avant. Si vous le souhaitez, vous pouvez aussi passer par le ruban (onglet Accueil) pour insérer une ligne ou une colonne. Vous pouvez également supprimer une ligne ou une colonne de cette façon.

Gérer plusieurs feuilles Excel sur un classeur

Un document Excel peut être composé de plusieurs onglets. On appelle un classeur le document complet, les onglets sont des feuilles. Vous pouvez ajouter facilement des feuilles grâce au bouton dédié en bas à gauche de la fenêtre (MAJ+F11). N’hésitez pas à nommer vos feuilles pour plus lisibilité. Vous pouvez également donner une couleur à un onglet pour mieux le retrouver (clic droit sur l’onglet).

Toujours afficher la première ligne / la première colonne sur Excel

Sur Excel, vous pouvez figer les volets : cela permet de toujours afficher la première ligne ou la première colonne. Vous pouvez également choisir de figer plusieurs lignes ou plusieurs colonnes ! Pour ce faire, sélectionnez la première cellule non-figée. Par exemple, si vous souhaitez figer la ligne 1 et la colonne A, sélectionnez la cellule B2. Dans l’onglet Affichage, choisissez Figer les volets et le tour est joué.

Créer un graphique à partir de données Excel

Créer un graphique sur Excel, c’est très facile. Le mieux est de sélectionner vos données, puis de choisir le type de graphique de votre choix dans l’onglet Insertion. Sur Excel, vous pouvez également créer un graphique au sein d’une cellule : on appelle cela un graphique Sparkline.

Utilisier un axe secondaire sur un graphique Excel

Cette astuce Word est également valable sur Excel : pour améliorer la visibilité d’un graphique, vous pouvez ajouter un axe secondaire en ordonnées. Ceci permet de mettre en forme deux séries de données dont les valeurs sont très éloignées. Une fois votre graphique créé, faîtes un clic droit sur l’une des courbes puis cliquez sur « Mettre en forme une série de données ». Vous n’avez plus qu’à choisir « Axe secondaire » pour améliorer la visibilité de votre graphique.

Faire un calcul matriciel sur Excel (résultat sur plusieurs cellules)

Dans certains cas, le résultat d’un calcul Excel ne peut être affiché sur une seule cellule. On parle alors de calcul matriciel. C’est notamment le cas de la fonction TRANSPOSE, qui permet de transposer une matrice. Pour ces fonctions matricielles, vous ne pouvez pas taper ENTER pour valider la formule. Vous devez sélectionner la plage de sortie, saisir votre formule, puis taper CTRL+SHIFT+ENTER. De cette façon, le résultat de votre calcul matriciel sera affiché sur l’ensemble des cellules sélectionnées.

Ajouter des compléments à Microsoft Excel

Aucun doute n’est permis : Microsoft Excel fait partie des logiciels les plus puissants. Mais dans certains cas, les fonctionnalités ne suffisent pas ! Il faut alors ajouter des compléments à Excel. XLSTAT ou Analysis ToolPak permettent par exemple de réaliser de nombreux calculs statistiques. Pour ajouter un complément à Excel, il suffit de se rendre dans les options (onglet Fichier), puis Compléments. Cliquez sur « Atteindre » pour gérer ou ajouter des compléments au logiciel.

Trier et filtrer les données d’un tableau Excel

Trier les données sur Excel, c’est très facile : sélectionnez d’abord votre tableau (avec ou sans les étiquettes), puis cliquez sur Trier dans l’onglet Données. Une fenêtre s’ouvre, vous invitant un trier votre tableau selon telle ou telle colonne et de telle ou telle façon (du plus petit au plus grand etc.). Dans l’exemple ci-dessous, nous avons trié un tableau Excel selon les notes obtenues en Français.

Évidemment, dans ce cas, l’intégralité de la ligne est conservé, seul le tri des lignes est modifié. Si vous le souhaitez, vous pouvez également filtrer les données. C’est pratique pour n’afficher que les données de certains clients par exemple (en filtrant la ville etc.). Il suffit alors de sélectionner votre tableau puis cliquer sur Filtrer, dans l’onglet Données du ruban.

Sélectionner un tableau en entier ou atteindre la fin d’Excel

Sur Excel, deux raccourcis clavier sont particulièrement utiles : CTRL+Flèche et SHIFT+Flèche. Le premier permet d’atteindre la dernière colonne ou la dernière ligne d’une série de données. Le second permet de sélectionner la cellule suivante. CTRL+SHIFT+Flèche permet donc de sélectionner tout un tableau. Il suffit de se placer sur la première cellule d’un tableau, utiliser CTRL+SHIFT+Flèche droite, puis CTRL+SHIFT+Flèche du bas. De la même manière, vous pouvez atteindre la fin d’une feuille Excel (XFD1048576) en utilisant CTRL+Flèche droite puis CTRL+Flèche du bas.

Personnaliser l’impression d’un tableau Excel

Les feuilles Excel sont pratiques pour agréger de nombreuses données. Mais bien souvent, l’impression brute des classeurs Excel donne des résultats peu convaincants. Pour améliorer l’impression des tableaux Excel, vous pouvez passer par l’onglet Mise en Page, puis « Imprimer les titres ». Une fenêtre vous permet alors de choisir une zone d’impression, les titres à imprimer etc. Vous pouvez aussi imprimer le quadrillage, les commentaires Excel et les erreurs générées par vos fonctions.

Restreindre les valeurs sur Excel

Sur Excel, vous pouvez spécifier des règles pour restreindre les données. Prenons l’exemple d’un tableau agrégeant les notes d’une série d’étudiants. Elles sont toutes comprises entre 0 et 20. Vous pouvez spécifier cette plage de données, afin qu’une erreur soit générée si une autre note est appliquée. Il suffit de sélectionner la plage de données de votre choix, puis choisir « Validation des données » au sein de l’onglet Données. Si une valeur incohérente est saisie, le message suivant est affiché.

Grouper les lignes sur Excel (calcul de sous-total etc.)

Grouper les lignes permet d’améliorer la lisibilité d’un tableau sur Excel. Dans l’exemple ci-dessous, nous avons par exemple groupé les lignes Grammaire et Orthographe sous la ligne Français, puisqu’il s’agit de matières liées au français. Il suffit de sélectionner les lignes secondaires, puis cliquer sur Grouper (onglet Données). Vous pouvez également dissocier les lignes ou calculer un sous-total.

Convertir un fichier Excel en PDF

L’enregistrement en PDF n’est pas réservée aux documents Word ! Vous pouvez aussi transformer un tableau Excel en document PDF. Mais tout dépend de votre version de Microsoft Excel :

Microsoft Excel 2010 et 2013 : il suffit d’utiliser la fonction Enregistrer sous. Choisissez alors PDF comme type de fichier, et le tour est joué.

Microsoft Excel 2007 : il suffit d’ajouter ce complément : Enregistrement en PDF ou XPS dans Microsoft et le tour est joué.

L’enregistrement des fichiers Excel en PDF pour les versions antérieures n’est pas possible sur Excel.

Les macros sur Excel

Sur Excel, difficile de se passer des macros. Elles permettent d’enregistrer des actions simples ou complexes. Il faut parfois du temps pour les créer, mais après, le logiciel se charge de reproduire vos actions. Les macros Excel permettent de gagner un temps fou !

Il suffit de passer par l’onglet Affichage, puis Macros, pour enregistrer une série d’actions, afficher les macros disponibles, modifier ou exécuter une macro Excel. N’hésitez pas !

Les options sur Microsoft Excel

Sur les logiciels de la suite MS Office, les options générales ne sont pas très accessibles. Vous devez passer par l’onglet Fichier, puis Options pour les afficher. Mais n’hésitez pas à y jeter un œil. De nombreux paramètres sont modifiables, vous pouvez notamment :

·         Décider de ne pas calculer automatiquement les fonctions de votre feuille Excel

·         Paramétrer l’enregistrement automatique de vos documents Excel

·         Modifier le déplacement de la sélection après validation d’une cellule

·         Personnaliser la barre d’accès rapide

·         Personnaliser le ruban

Convertir un CSV en fichier Excel

Pour convertir un CSV en fichier Excel, deux possibilités : vous pouvez faire un clic droit sur le fichier, puis Ouvrir avec… Excel. Ou ouvrir Excel, passer par l’onglet Données, et cliquer sur À partir du texte. Ensuite, laissez-vous guider par l’assistant d’importation. En général, les données sont séparées par une virgule, et les textes sont identifiés par des guillemets. Mais vous pouvez personnaliser ces paramètres grâce à l’assistant, ainsi que le format de chaque colonne importée.

Pour maîtriser l’ensemble des fonctionnalités, vous pouvez suivre des formations dédiées à Excel qui vous permettront de développer vos compétences.