Excel : le saviez vous ? ou liste d'astuces ...
Exercices EXCEL, corrections & plus...
Jean-Marc Stoeffler © maj : déc 2013


une quarantaine d'astuces à imprimer, et à lire tranquillement, dans les transports en commun, par exemple...
astuce : ★★★★=indispensable, ★★=très utile, ★★ = utile, = à tester une remarques ? pour m'écrire par mail...
Astuce N°1, pour cette page : CTR+F va vous permettre de retrouver un mot (ou un morceau de mot) du texte que vous recherchez ici !

Nouveau : lien vers les vidéos associées avec ce pictogramme : 


->>>voir également les 100 nouvelles astuces simples (page créée en nov 2007)

astuceEXCEL : utilisation méconnue de la touche F2

touche F2 - pour ne plus utiliser sa souris lorsqu'on veut modifier une cellule
- au moment où on remplit une cellule vierge, pour pouvoir utiliser les touches de direction sans quitter la cellule active

- dans certaines boîtes de dialogue comme celle du format conditionnel,

à noter : le SHIFT+F2 permet de créer un commentaire dans la cellule
remarque : pour Windows
F2 = éditer, par exemple pour changer le nom d'un fichier avec l'explorateur.
============================================ EXCEL =====================
> touche CTRLtouche TAB (Ctrl+TAB) pour changer de classeur (dans Excel) lorsque plusieurs fichiers sont ouverts.
> touche CTRL(Ctrl+PageDown) (ou Ctrl+PageUp) pour changer de feuille dans un fichier Excel.


> astuce Windows qu'on peut utiliser sur Excel, Word, etc...: touche CTRLtouche FIN Ctrl+Fin = fin de page
astuce Internet Explorer touche FIN suffit pour aller en bas de page)
astuce Internet Explorer : F11<--->F11 Affichage plein écran <---> affichage normal
★★★astuce Windows :(Pomme+D)touche «Pomme» de Windows (touche Windows)  pour voir rapidement son bureau et revenir ensuite à son travail.
★★★★astuce Windows (Pomme+E)touche «Pomme» de Windows (touche Windows)  pour lancer l'explorateur, n'importe quand.
★★★> touche ALTtouche TAB (Alt+TAB)pour changer de fenêtre Windows:
mode court
=fenêtre précédente mode long sans lacher le Alt : on peut choisir...

★★>(new) pour forcer le passage à la ligne, à l'intérieur d'une cellule :touche ALT +[Entrée].
Ainsi, l'option suivante est activée :
Format > Cellule> onglet-Alignement > Renvoyer à la ligne automatiquement



★★★ : la calculatrice cachée d'EXCEL de la barre d'état : voir la vidéo associée dans la page des vidéos... 
(remarque : sur Excel 2007, cette fonctionnalité est démultupliée !)
pour avoir immédiatement, à l'écran, la somme, le nombre de cellules [compteur], le nombre de valeurs numériques [chiffre], le min, le max ou la moyenne des cellules sélectionnées, dans la barre d'état, en bas, bouton droit de la souris dans la zone cachée où apparaît parfois somme= ....
remarques :
- si la barre d'état n'apparait pas :
aller au menu affichage et cocher [x]barre d'état
- pour sélectionner des cellules non contigues, on utilise la touche [Ctrl]

> La fonction audit : elle permet de voir toutes les cellules qui entrent directement ou indirectement dans un calcul ...
un bouton magique et son antidote : au menu :
outils
    audit
         afficher la barre d'audit puis prendre une cellule avec un calcul sur des données provenant d'autres cellules et cliquer plusieurs fois l'icône ci-dessus (avec le symbole "+" = repérer les antécédents).
exemple :

Toutes les cellules dont les valeurs interviennent dans le résultat de la cellule analysée vont être reliées par des flèches bleues. (feuille imprimable avec ces flèches)

1er clic = cellules de 1er niveau de calcul, 
2ème clics = 2ème niveau  de calcul, 
3ème clics = 3ème niveau  de calcul, etc...

★★astuce EXCEL :
en configuration réseau, un classeur Excel peut-être utilisé en lecture-écriture par plusieurs utilisateurs,
en même temps. Plus de message : "en lecture seule" !

deverouller.gif

[voir détail cliquez ici★★]


> le double-clic magique sur le bouton de duplication d'une cellule... (c'est un de mes stagiaires qui m'a montré ça...)
Lorsqu'apparaît la croix, plutôt que tirer laborieusement la formule vers le bas, il suffit de double-cliquer dessus. La formule se duplique alors jusqu'à la dernière cellule, à condition qu'il y ait des données sur la colonne de droite ou de gauche. Attention aux trous qui peuvent se cacher. (Voir l'utilisation de la calculatrice cachée, plus haut, pour contrôler le nombre de duplications)

exemple avec un calcul d'âge (dont la formule est apparente) >>>> il suffit de double-cliquer sur la croix noire (1ère figure) pour que la formule se duplique jusqu'en B14 (2ème figure). Quasi indispensable avec les très grands tableaux


★★★ : le format conditionnel : l'utilisation astucieuse de ce format permet de mettre en évidence des cellules corrrespondantes à un ou plusieurs critères choisis, par exemple les doublons d'une liste (pour les doublons d'une liste, un exemple va venir...)

cliquer pour agrandir


★★Avoir une ligne sur deux, colorée : pour en faciliter la lecture !

Une application du format mise en forme conditionnel, que je me rejouis d'avoir inventée :
Pour avoir une ligne sur deux avec un fond en couleur, même après un tri, une suppresion ou insertion de ligne :

Autre avantage : en mode filtré, l'affichage montre bien que le tableau est filtré car l'ordonnancement d'une ligne sur deux dans une couleur n'existe plus. Ça rappelle qu'on ne travaille pas sur tout le tableau, ce qu'on a parfois tendance à oublier.

Explication :
- la fonction modulo [=MOD(A;B)] donne le reste de la division de A par B.
- Ici, pour chaque cellule, la division du numéro de ligne par 2 donnera soit 0 (ligne paire), soit 1 (lignes impaires).
- Or 1 est équivalent à VRAI donc la cellule prend la couleur choisie (ici le jaune clair). Donc [=MOD(A;B)] est équivalent à [=(MOD(A;B)=1)]

Guère plus compliqué :
Avoir les lignes paires avec une couleur et les impaires avec une autre couleur :



et le tour est joué... (Dans ce cas, on aurait pu appliquer une couleur verte à toutes les lignes, et le format conditionnel, ayant priorité sur le format de fond, aurait rempli son rôle, mais ça montre les possibilités de la formule.

remarque : idem avec les colonnes, en utilisant la fonction [=MOD(COLONNE();2)]


Ecrire sur plusieurs lignes dans la même cellule

Pour aller à la ligne, dans une seule cellule : ALT+Entrée là où on désire avoir le saut de ligne :

On fait d'une pierre-deux-coups :
1) on met le format de la cellule avec retour à la ligne automatique
2) on force le saut de ligne à l'endroit désiré...


> aujourd'hui et maintenant avec un code de touche...
(également vrai pour Access )

Pour inscrire et figer la date du jour il suffit d'appuyer sur la combinaison des 2 touches [CTRL]+ [;]

Pour inscrire l'heure courante et la figer il suffit d'appuyer sur la combinaison [CTRL]+ [:] 

Pour avoir dans une formule la date système il faut écrire la formule =aujourdhui()
Pour avoir la date + l'heure système la formule est =maintenant()
Ces deux dates évoluent lorsqu'on charge le classeur qui les contient et à chaque entrée de données dans les feuilles, ainsi qu'avec la touche [F9] (recalcul) 
Pour casser un format : CTR + R
Pour mettre un format date standard : CTR + J


astuceEXCEL : le format personnalisé
Mettre une unité aux valeurs affichées dans les cellules, par exemple 28 ans, 104 km/h,  12,3 litres, 14 m² :
Format cellule > onglet nombre > perosnnalisé > standard" ans" ou standard" km/h" ou standard" m²"
affiche.unite.1.gif

Plus difficile : un s au pluriel, rien au singulier !  [<2]Standard" litre" ; Standard" litres"
affiche.unite.2.gif

★★ Pour différencier les valeurs positives, négatives, nulles ou le texte d'une cellule :
Format de Cellule> Nombre > Personnalisé ... Type : si positif ; si négatif ; si nul ; si texte

par exemple, pour masquer des zéros sans utiliser outils>options>affichage...

exemple de format (à copier coller):

+[bleu]Standard;-[rouge]Standard;"" ;Standard
<------1------>;<------2------->;<3>;<--4--->
1 - les nombres positifs ont le signe + et sont affichés en bleu
2 - les nombres négatifs ont le signe - et sont affichés en rouge
3 - les nombres nuls sont masqués
4 - le contenu des cellules non numériques n'est pas coloré

pour signaler des données non numériques là où on attend du numérique, sans utiliser le format conditionnel.


> dépasser 23h59 dans les calculs d'heures = utiliser des crochets : les heures saisies dans les cellules (exemple 12:42) correspondent par défaut à des horaires. Lorsqu'on les additionne, elles ne dépassent jamais 23:59. Lorsqu'on veut calculer des durées, il faut utiliser le format personnalisé
Format de Cellule> Nombre > Personnalisé ... Type : [h]:mm

cliquez pour agrandir...




Un bouton pour masquer ou afficher le quadrillage d'Excel (new : avril 2007)

Voir la page dédiée à cette astuce >>> (attention Internet Explorer nécessaire, car la page correspond à un fichier Excel Htmlisé...)

pour Mozilla, voir l'image(PNG) ou l'image_(GIF)


astuce EXCEL : : la touche F4 : sert à reproduire la commande précédente : très utile pour les mises en forme, insertion suppression de ligne/colonne, reproduire les bordures (voir exo 1), etc...


★★ astuceEXCEL : aide à la saisie : choisir les valeurs dans une liste prédéfinie :
(menu : Données > Validation > Autoriser...liste ...


 astuce EXCEL :touche ALT + [ê] : aide à la saisie (que j'ai découvert récemment par hasard) :
permet de choisir une valeur déjà présente dans une colonne ; en mode filtre, remplace le clic sur le bouton de choix.

> calculer l'âge avec la date de naissance (c'est une fonction d'Excel non documentée...):

la formule à utiliser est la suivante :
=DATEDIF(date de naissance ; date de référence (par exemple AUJOURDHUI() ) ; "y")


> pour faire rapidement une somme : = = (ALT = =) offert par Samantha du DTS11


> comment activer les macros lorsqu'elle ne fonctionnent pas et qu'aucune question ne vous est posée ?
Lorsque vous chargez un fichier Excel comportant des macros (par exemple, le calendrier magique), une question doit vous être posée : voulez vous activer les macros ?


> Une objet texte peut être lié à une cellule (peut permettre de mettre du texte conditionnel en surimpression sur un tableau)
permet de ramener l' image d'une cellule au milieu d'autres cellules.
[voir exemple]

> Pour connaitre le nom complet d'un fichier Excel (lecteur:\chemin\[nom]onglet), il suffit de mettre dans une cellule la formule : 
=cellule("nomfichier";A1) sans espace
donnera par exemple ==> C:\WINDOWS\TEMP\[budget.xls]initialTrès utile car, depuis l'avénement des Windows 95/98/NT, lorsqu'on ouvre un fichier Excel, on n'est pas placé dans le dossier d'origine du fichier mais dans le dossier-par-défaut défini dans Outil-Option-Général--Dossier-par-défaut, donc lorsqu'on utilise Enregistrer sous... on l'enregistre dans ce répertoire par défaut, ce qui est très gênant!!
Pour avoir le nom de la feuille :  


> Pour conserver exactement le texte que l'on vient de saisir, sans qu'Excel prenne la liberté de le traduire "au mieux...", il suffit simplement de placer le caractère ' (apostrophe) en tête.

exemple :
- si on écrit 1/10 Excel traduit en 01-oct. Si on désire voir apparaître 1/10, (par exemple pour un code de rangement de type allée 1 rangée 10 il faut écrire '1/10 , et là, Excel s'incline... (remarque, on peut utiliser le format de cellule "texte", si on doit le faire sur un ensemble de cellules).
- remarque : si l'on veut travailler sur de vraies fractions, il faut taper :
0 1/10
(zéro, espace, dix) et là; on peut, en plus, faire des calculs (précision que m'a aimablement communiquée un internaute C. Ferlet...)


> une "radiographie pulmonaire" de votre feuille de calcul...
à cliquer, pour le fun...   = CTRL  +  " - astuce que m'a donnée une internaute et que j'utilise fréquemment

avant je faisais
:
 menu : Outils-Option-Affichage - et  Formules : cochée. 

(la largeur des colonnes est alors multipliée par deux our une meilleur lisibilité)

> dans un classeur, passer rapidement d'un onglet à l'autre : ou

> pour passer rapidement d'un classeur à l'autre (lorsqu'il y en a plusieurs d'ouverts)
= passage d'une fenêtre à l'autre, dans une application Windows: + dans un sens
(merci à vincent.stal@espas.fupl.asso.fr, moi je proposais Ctrl+F6 qui est nettement plus acrobatique)++ dans l'autre sens

> voir apparaître le nom des programmeurs d'Excel 97dans un paysage virtuel et s'y déplacer...
Attention "ça décoiffe" --->
[voir détail]

> pour centrer sur plusieurs colonnes sans utiliser [fusion-cellules] ...qui cause bien du souci lorsqu'on veut insérer une nouvelle colonne  avec Excel 97 ( (moins de problème avec Excel 2000 et +)
il suffit de placer son texte dans la première cellule à droite de la sélection sur laquelle on désire voir le titre centré puis:
format-cellule
..alignement
....horizontal
.....centré sur plusieurs colonnes. (option que l'on ne découvre pas immédiatement. J'ai mis plusieurs mois à la découvrir...)Je recommande même de dévier le bouton [Fusion-cellules] sur une macro du classeur perso qui centre sur plusieurs colonnes et qui est la suivante :'---------------------------------------------------
Sub CentreSurPlusieursColonnes()
' Macro enregistrée le 17/03/99 par JMS
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
End Sub
[d'autres macros-outils]

> révolution (silencieuse) dans le tableur Excel 97 : on peut utiliser les intitulés de colonnes dans les formules en oubliant les fameuses références A5, D3, H4 ... (la bataille navale, c'est terminé !!) -
C'est l'erreur d'une de mes stagiaires qui m'a révélé cette nouveauté (j'avais expliqué comment donner un nom à des groupes de cellules! et comment réutiliser ces noms dans des formules y faisant référence, et malgre l'oubli de la première étape, ça marchait quand même ! je n'en croyais pas mes yeux...

précision de Denise (janvier 2004) : pour que cela fonctionne il faut aller dans OUTILS/OPTIONS, onglet CALCUL et cocher "accepter les étiquettes dans les formules" pour toutes les versions excel


> Pour ajuster automatiquement l'impression sur 1 page en largeur, en laissant libre le nombre de pages en hauteur (par exemple pour imprimer une longue liste d'informations :
Fichier Mise en Page - onglet Page >
Ajuster à page en largeur et en hauteur : RIEN(dans cette dernière case, supprimer tout simplement la valeur avec la touche SUPPR...)
Pour ajuster automatiquement l'impression sur 1 page en largeur, en laissant libre le nombre de pages en hauteur

> Impression Excel : pour garder sur toutes les pages les intitulés de colonne de la première ligne :

Fichier > Mise en page > onglet Feuille > ligne à répéter en haut : $1$1 (ou la ligne de votre choix)
Impression Excel : pour garder sur toutes les pages les intitulés de colonne de la première ligne


> Jours fériés, heures, jours ouvrables etc...Calcul d'heures ouvrables entre deux dates+heures, de jours ouvrables entre deux dates, de jours fériés ... Je vous propose soit :
- un programme en VBA à insérer soit sous access, soit sous Excel (revu le 29/08/2006)  fichier VBA (zipé : sous VBA faire importer module...) à enregistrer puis importer sous Excel
fichier exemple ZIP (44 Ko) avec un fichier XLS d'exemples
> imprimer tout le contenu d'un classeur avec une numérotation qui se suit...
Dans le menu Fichier :
    imprimer
       classeur entier
             au niveau de la pagination sur chaque pied de page des feuilles : inscrire page &[Page]/&[Pages] 
                                                                           (en s'aidant des icônes qui vont bien)

un plus : pour le titre : &[Fichier] : &[Onglet] et pour le pied de page : imprimé le &[Date] à &[Heure]

★★★windows>(rappel du haut) : comment atteindre son bureau en un raccourci clavier (réduire toutes les fenêtres) Ce n'est pas de l'Excel, mais je l'utilise souvent lorsque je travaille sur Excel. 
  Pomme   que je viens de découvrir à l'instant, en voyant le "d" souligné de la ligne suivante et qui est mieux puisque réversible !!!
ou dans une zone vierge la barre de tâches :duire toutes les fenêtres,  


> Lorsqu'Excel présente en permanence un écran de ce type, essayez de prendre l'avion, de changer de pays et, si ça persiste, rachetez un ordinateur...

:



astuce Windows qu'on peut utiliser ici touche HOME (=début) =haut de page Internet Explorer,
ou sur Excel, Word, etc...: touche HOME (=début)Ctrl+Début = haut de page

il n'y a pas qu'Excel dans la vie... (cliquez ici) depuis le 5/11/2006-> http://perso.orange.fr/jeanmarc.stoeffler/excel ou http://doublevez.com