Que diriez-vous d'écrire une formule qui insère des images ou des logos dans votre feuille de calcul? (Essayez de taper = CHAR (128202) dans votre feuille de calcul.)

Vérifiez, vérifiez et vérifiez. Tout est possible et couvert dans ce livre.

Ces conseils proviennent de ma série d'e-mails Google Sheets Tips , une série hebdomadaire partageant un seul conseil Google Sheets ou Apps Script utilisable par e-mail tous les lundis.

Considérez ces conseils comme votre expresso du lundi matin, sous forme de feuille de calcul.

Chaque semaine présente une idée unique de la taille d'une bouchée que vous pouvez appliquer immédiatement dans votre travail.

Ce livre contient les 100 premiers conseils, y compris des conseils pratiques et ésotériques, faciles et difficiles, courts et longs, et tout le reste.

Vous en avez peut-être déjà vu auparavant, mais j'espère que vous trouverez de la magie dans ces pages, pour vous inspirer ou vous montrer de nouvelles façons d'utiliser des feuilles de calcul que vous ne saviez pas possibles.

Les commentaires sont les bienvenus! Si vous trouvez des erreurs ou si vous pensez que quelque chose mérite des éclaircissements, n'hésitez pas à me contacter.

De même, si vous avez votre propre façon préférée, inhabituelle ou ordonnée d'utiliser des feuilles de calcul et que vous souhaitez partager, j'aimerais avoir de vos nouvelles. Je créditerai toute personne qui contribue.

Ben Collins

Mai 2020

PS Ce livre est disponible en format PDF ici.

#1. Raccourcis clavier dans Google Sheets

Commençons cette série avec l'un des meilleurs conseils de productivité de Google Sheet.

Voici mes 5 meilleurs raccourcis clavier:

Effacer tout le formatage dans une cellule ou une plage

Mac: ⌘ + \

PC / Chromebook: Ctrl + \

Insérer la date actuelle dans une cellule

Mac: ⌘ +;

PC / Chromebook: Ctrl +;

Sélectionnez toutes les données dans un tableau

Mac: ⌘ + A

PC / Chromebook: Ctrl + A

Trouver et remplacer

Mac: ⌘ + Maj + H

PC / Chromebook: Ctrl + H

Ouvrez le menu déroulant sur la cellule filtrée

Mac: Ctrl + ⌘ + R

PC / Chromebook: Ctrl + Alt + R

Aujourd'hui, je vous mets au défi d'utiliser des raccourcis clavier.

Cela peut sembler maladroit au début, mais persévérez et cela sera payant à mesure que vous devenez plus efficace dans votre travail.

Pour voir tous les raccourcis disponibles, accédez à Aide> Raccourcis clavier

# 2. Notifications dans Google Sheets

Collaborez-vous avec des collègues dans Google Sheets?

Voici comment vous pouvez rester informé lorsque des modifications sont apportées à vos feuilles, par exemple lorsque de nouvelles données sont ajoutées.

Restez informé des modifications apportées à une feuille Google en définissant des règles de notification pour vous envoyer un e-mail.

Vous le trouverez sous ce menu: Outils> Règles de notification ...

Ici, vous pouvez décider de ce qui doit déclencher une notification et de la manière dont elle doit être envoyée (immédiatement ou un résumé quotidien):

Ne manquez plus jamais une mise à jour de vos feuilles de calcul Google!

# 3. Datetime T ip dans Google Sheets

Saviez-vous que les dates et heures de votre feuille de calcul sont stockées sous forme de nombres, comme 43 241,5866? De vieux chiffres simples!

(C'est le 21/05/2018 14:04:43 au fait. Voulez-vous voir par vous-même? Essayez d'entrer une date et de la mettre en forme sous forme de nombre.)

Cela signifie que nous pouvons utiliser les mêmes formules aux dates que nous utilisons pour les nombres.

Supposons que vous ayez une date / heure dans la cellule A1.

Si vous entrez = INT (A1) dans la cellule B1, vous obtiendrez la date avec le temps supprimé. En effet, la partie décimale du nombre représente le temps, et la fonction INT renvoie uniquement la partie entière (la partie entière) d'un nombre:

Si vous entrez = MOD (A1,1) dans la cellule B1, vous obtiendrez l'heure seule sans la date. La fonction MOD effectue l'arithmétique Modulo et renvoie simplement le reste (partie après le point décimal), qui dans ce cas est le temps:

Comprendre que les dates et les heures ne sont que des chiffres vous aidera vraiment à travailler efficacement avec elles dans vos feuilles!

# 4 . Super cool (et intelligent) pour combiner du texte et des nombres dans une cellule

Souvent, lorsque vous référencez un nombre à plusieurs reprises, vous le placez dans une cellule distincte, vous pouvez donc le lier dans vos formules. C'est peut-être un taux de taxe ou un seuil pour un ensemble de calculs.

Eh bien, voici une astuce intéressante pour améliorer ces cellules de référence: vous pouvez utiliser une mise en forme personnalisée pour ajouter une étiquette de texte à un nombre dans une cellule, mais toujours l'utiliser comme un nombre.

Mettez en surbrillance votre cellule de référence et accédez au format numérique personnalisé ... dans le menu:

Dans la zone d'option de format personnalisé, ajoutez votre étiquette à l'intérieur des guillemets, suivie d'un 0 ou 0,0 ou 0,00 pour spécifier le nombre et les décimales si nécessaire. Par exemple:

"Seuil:" 0,00

comme indiqué ici:

Lorsque vous cliquez sur Appliquer, la cellule affiche désormais votre étiquette de texte devant le numéro.

Vous pouvez utiliser cette cellule dans les calculs comme si elle venait d'être écrite sous forme de nombre simple, car il s'agit toujours d'un type de données numérique sous le capot.

# 5. Remplacer les fonctions SI numériques par MIN ou MAX

N'écrivez pas d'instructions IF inutilement verbeuses qui pourraient facilement être remplacées par une fonction MIN ou MAX. Cette astuce consiste à rédiger efficacement des formules. Voici comment remplacer les formules IF numériques par des fonctions MIN ou MAX.

Supposons que vous ayez une colonne de valeurs que vous souhaitez plafonner à un certain niveau, de sorte que tout ce qui dépasse une valeur seuil, par exemple 200, soit défini sur cette valeur.

La plupart d'entre nous aborderaient cela en écrivant une formule IF qui vérifie si la valeur est supérieure à 200, puis la définissait sur 200 si VRAI, ou la valeur réelle si FAUX, comme ceci:

Cependant, vous pouvez remplacer la fonction IF entière par une fonction MIN beaucoup plus succincte, qui choisit la valeur 200 si la valeur réelle est plus grande, puisque 200 est le minimum:

= MIN ( A2 , 200 )

De même, nous pouvons utiliser la fonction MAX pour remplacer les instructions IF lorsque nous examinons un seuil bas.

Par exemple, supposons que les petites valeurs de votre jeu de données ne vous intéressent pas et que vous souhaitiez écrire une formule pour les remplacer toutes, par exemple par 10. Cette fois, nous pouvons utiliser la fonction MAX:

= MAX ( A2 , 10 )

Il est recommandé d'écrire des formules efficaces, car c'est plus rapide et vous êtes moins susceptible de faire des erreurs.

# 6. Façons rapides de copier des données dans une colonne

Follow this tip to copy data down columns or across ranges more efficiently: you can quickly fill down columns by double-clicking the blue button or using Ctrl + D (Cmd for Mac) or Ctrl + Enter (Cmd for Mac).

Suppose you have a column of values with a formula or value in the top cell of the adjacent column, as shown in the following image:

To copy the formula quickly down the column, double-click the blue mark in the corner of the highlighted cell, shown by the red arrow. This will copy the cell contents and format down as far as the contiguous range in preceding column (column A in this case).

An alternative way to quickly fill in a column is to highlight the range you want to fill, including the first value that you want to copy down, as shown in the following image:

Then press Ctrl + D (PC and Chromebook) or Cmd + D (Mac) to copy the contents and format down the whole range, like so:

You can also do this with Ctrl + Enter (PC and Chromebook) or Cmd + Enter (Mac), which will fill down the column. This shortcut also has the nice property that if you highlight a range of cells, like this:

It will fill out the whole range, down the column and across the rows:

Note, that with all these cases, the upper left cell is the one that will be copied down (and across) your range.

#7. Multiply an Entire Column With a Single MMULT Formula

Follow this tip to quickly transform a column of numbers, for example changing the sign, scaling up or scaling down. Here’s how to multiply or divide value columns with a single MMULT formula or ArrayFormula.

Suppose you have a column of values in column A, and you want to negate all the numbers. I recently needed to do this when I wanted to display a chart series as negative values.

To quickly transform an entire column of numbers to negatives (or positives if values are already negative), use a single MMULT formula at the top of the column, like so:

To scale a number up by a constant factor, simply replace the -1 above with the new constant, for example 10 as shown in this image:

And if you want to divide your values, you can use MMULT with a decimal to achieve the division, for example to divide by 10 use 0.1:

Pretty cool huh?

MMULT multiplies matrices together and this works because the number of columns of the first matrix, A2:A1000, is 1, which equals the number of rows of the second matrix, a single 1 by 1 matrix (i.e. 1 row).

Another way to achieve this calculation is to use an Array Formula, for example:

=ArrayFormula( A2:A1000 * -1 )

#8. The OFFSET Function

Here’s how to move ranges of data with the OFFSET function.

The OFFSET function returns a reference to a range that is offset from a starting point in a worksheet.

It's not a function you need particularly often, but it's worth knowing about because it allows you to move ranges around very easily.

For example, look at the following table where the columns of data are not lined up in the first row:

You want to align these columns at the top of the column, as shown in the following image:

The OFFSET function can do this with a single formula for each column, which is quicker and easier than copy-pasting or creating complex nested formulas.

The OFFSET formula in this example is:

=offset( B2:B6, 1, 0, count(B2:B6) )

The first argument, B2:B6, is the original range of data we want to move. This is what the formula starts with:

The second argument, 1, is the number of rows we want to offset. In this case it's 1 because we want to move down one row to grab the first value, like this:

The third argument, 0, is the number of columns we want to offset. In this case we don't have a column offset so we set this to 0.

The final argument is an optional value for the height of the range to return. In this case I've set it to count(B2:B6) because I only want to return the four values and don't need to include the 5th empty cell, so the formula is essentially grabbing this range:

Finally, this new range is output in cell F2, or wherever I've typed the OFFSET function:

For the data in column C, I need to move that up by two rows, so I need to change my OFFSET formula slightly to this, which I typed in cell G2 in my example:

=offset( C2:C6, 2, 0, count(C2:C6) )

#9. The ISEMAIL Function for Better Data Entry

Voici une astuce simple pour forcer les utilisateurs à entrer des adresses e-mail valides dans les cellules: utilisez la fonction ISEMAIL avec validation des données pour vous assurer que seules les adresses e-mail valides sont acceptées.

La fonction ISEMAIL de Google Sheets vous permet de vérifier si un e-mail est valide ou non, ce qui est particulièrement utile si vous travaillez avec des données de contact ou de client dans Sheets.

C'est une formule super facile et fonctionne comme ça. Voici un exemple d'e-mail valide:

Combinez-le avec la validation des données et vous pouvez vous assurer que seules des adresses e-mail valides sont entrées dans vos feuilles, ce qui garantit une meilleure précision des données à l'avenir.

Dans la cellule où une adresse e-mail sera saisie, par exemple A1 dans l'image ci-dessus, cliquez avec le bouton droit et choisissez Validation des données ... ou allez dans l' option de menu Données> Validation des données ... , ce qui ouvre cette fenêtre contextuelle.

Choisissez la formule personnalisée dans la deuxième option et entrez = isemail (A1) , comme indiqué dans cette image:

J'ai choisi de rejeter l'entrée si la formule est évaluée à FAUX (en raison d'une adresse e-mail non valide), ce qui affiche cet avertissement et laisse la cellule vide:

La validation des données dans la cellule affiche également un indice si l'utilisateur survole cette cellule, dans ce cas, l'invitant à entrer une adresse e-mail valide. Ceci est personnalisable dans le générateur contextuel de validation des données.

Doux!

La validation des données est une technique utile pour garantir que votre saisie de données est propre, correcte et sécurisée.

#dix. La fonction CHAR pour insérer des caractères spéciaux, des images et des emojis

Avez-vous entendu parler de la fonction CHAR? C'est un petit bijou! Vous pouvez utiliser cette fonction pour insérer des caractères spéciaux, des images ou même des emojis dans les cellules.

La fonction CHAR dans Google Sheets est une petite fonction astucieuse qui convertit un nombre en caractère selon la table Unicode actuelle.

C'est super facile à utiliser.

Voici un exemple:

J'utilise trois fonctions CHAR différentes pour ajouter du contexte et forcer un saut de ligne.

CHAR ( 8594 ) produit la flèche droite.
CHAR ( 10 ) produit un retour chariot (nouvelle ligne).
CHAR ( 8595 ) produit une flèche vers le bas.

It makes the table much easier to interpret and reduces the chance of a user misreading the data.

Here's a few more CHAR function outputs you can try on for size:

There are thousands more CHAR characters to explore, so I encourage you to go and experiment.

I've found this tool, Graphemica, to be the most useful for finding them.

Search for a character, and then use the number from the HTML Entity (Decimal) metadata section in your CHAR function.

#11. Group Rows or Columns so You Can Toggle Between Showing or Hiding Them

If you highlight several rows (or columns) by clicking on their numbers (or letters), you can then right click and choose to Group rows (or columns) which adds a button above the rows (or columns), which you can toggle to show/hide these grouped rows (or columns).

Here's an example:

#12. Use the F4 Key to Quickly Repeat Your Previous Action in a New Cell

For example, say you've just given a cell a red background and you want to apply that again to another cell or range. Simply highlight the new cell or range and hit F4 before doing anything else, to change the background to red. Voila!

(Note, this will also copy-paste static values, but not formulas. If you type some text into a cell and then hit F4 in a different cell before doing anything else, it'll copy that same text to the new cell. This quirk only works cell to cell, not cell to range though.)

#13. Tag People in Comments in Google Sheets

Comments are one of the killer collaboration features embedded in Google Sheets.

You add a comment by right-clicking a cell and choosing Insert comment, or through the menu Insert > Comment.

You can then tag specific people in each comment if you wish, which means they'll receive an alert that they've been mentioned in a comment.

For example, you might tag Jenny in Finance to ask her to check if your calculation is correct. It's way easier than trying to explain something via email!

You tag someone in a comment very easily by typing a "+" or "@" in the comment box, and then adding the email address of the person you want to notify. As you start typing, Google will auto-suggest email addresses from your contacts list.

#14. Conditional Formatting Across Entire Row

Here’s how to apply conditional formatting across an entire row using the custom formula option.

Conditional formatting is a super useful technique for formatting cells in your Google Sheets based on whether they meet certain conditions.

You can apply the formatting across an entire row based on the value in a single cell, by using custom formulas. For example, you could highlight rows in your tables if a value exceeds some threshold. And of course, the power of conditional formatting means this is dynamic, so it will apply the formatting to any rows that change and subsequently meet the condition.

Choose “Custom formula is” at the end of the drop-down list in the conditional formatting editor and enter your formula. Add a $ (dollar sign) in front of the column reference only, to apply the formatting to the entire row. Read more details on the blog.

For example, I’ve highlighted all of the students who scored less than 60 in class, using this formula in the custom formula field:

= $C2 < 60

#15. Trick to Round Numbers to the Nearest Ten, Hundred, etc.

You can use the ROUND function with negative places to round numbers to the nearest ten, hundred, etc.

The ROUND function is a handy function that's used to round decimal places in your numbers when you don't need that level of precision.

Did you know you can specify a negative number of places in the function though?

Avec un nombre de places négatif spécifié, il arrondira vos nombres aux dix, cent, mille, etc. les plus proches.

Par exemple, cette formule arrondirait un nombre de la cellule A1 à la dizaine la plus proche:

= rond ( A1 , -1 )

C'est évident avec le recul mais quelque chose que je ne connaissais pas depuis des années! Voici un exemple:

Les formules de cet exemple sont les suivantes:

# 16. Accélérez la saisie de vos données

Pour cette astuce rapide et facile, vous pouvez simplement mettre en surbrillance une plage de lignes et utiliser votre touche Entrée pour vous déplacer horizontalement.

Remplissez les plages de données horizontalement et utilisez la touche Entrée pour terminer la saisie des données et vous déplacer latéralement vers la cellule suivante.

Voici un exemple d'ajout efficace d'en-têtes de colonne:

Commencez par mettre en surbrillance la plage où les en-têtes de colonne existeront.
Tapez le premier en-tête de colonne dans la première cellule.
Appuyez sur Entrée et le curseur sautera sur le côté, pas vers le bas, jusqu'à la cellule adjacente, où vous pourrez entrer le prochain en-tête de colonne.

Vous pouvez également y parvenir sans la mettre en évidence, en utilisant la touche TAB, mais je trouve que c'est un mouvement maladroit qui va à l'encontre de 15 ans de mémoire musculaire. 😂

# 17. Générez des lettres aléatoires avec la fonction CODE

Nous utilisons les fonctions RAND ou RANDBETWEEN pour générer des nombres aléatoires, mais qu'en est-il des lettres aléatoires?

Il n'y a pas de fonction équivalente de générateur de lettres aléatoires, nous devons donc utiliser quelques fonctions combinées pour créer la nôtre.

J'ai déjà écrit sur l'utilisation de la fonction CHAR pour convertir des nombres en caractères selon la table Unicode actuelle. Eh bien, il a un jumeau complémentaire, la fonction CODE, qui transforme les caractères en chiffres.

Sachant cela, nous pouvons construire un générateur de lettres aléatoires.

Step 1: convert the letters A and Z into numbers using the CODE function

=CODE("A") => 65

=CODE("Z") => 90

Step 2: Use RANDBETWEEN to generate a random number between these two numbers

=RANDBETWEEN(65,90) => e.g. 78

Step 3: Convert this number back to a letter using the CHAR function

=CHAR(78) => N

The full formula is:

= CHAR( RANDBETWEEN( CODE( "A" ) , CODE( "Z" )))

Each time you make a change in your Sheet, this formula will generate a new random number.

#18. Create Heatmaps With Your Data

Here’s how to create heatmaps from your data using conditional formatting.

Heatmaps are a great way to add context to your data.

Suppose we have this small dataset of bounce rates:

It's not easy to see, at a glance, which days did well and which did poorly. It takes effort on our part to read and compare each number, and there's a risk that we miss something.

Heatmaps will bring attention to the high and low values in your data, those outliers that demand more attention in your analysis, and they're very easy to create.

Simply highlight the range of data you're using, open Conditional Formatting (under Format menu) and select the Color scale option. Then choose one of the prebuilt color scales or create your own:

You can immediately see the difference.

It's now very easy to identify the days our website underperformed (in red, high bounce rate) and which days it did well (in green, lower bounce rate).

Heatmaps in the wild

Here's another example of a slightly more complex heatmap example, taken from my Data Analysis in Google Sheets course, depicting the customer retention rate for a fictional SaaS company:

Again, see how the heatmap adds context and gives you a clear sense of what's happening.

#19. Quickly Copy Pivot Tables

This tip shows you how to quickly copy Pivot Tables using the top left corner cell.

Oftentimes you'll find yourself wanting to replicate a Pivot Table, perhaps as a starting point for further data exploration.

There's a quick trick for copying an existing Pivot Table, rather than starting over. It also gives you the option of moving your Pivot Table to a different tab.

Click into the top left corner cell of your Pivot Table and click copy (Cmd + C on a Mac, or Ctrl + C on a PC/Chromebook). This adds the Pivot Table to your clipboard and you can paste it wherever you want in your Sheet (Cmd + V on a Mac, or Ctrl + V on a PC/Chromebook).

Note: You need to ensure there is enough space available wherever you wish to paste a copy of your Pivot Table (i.e. enough empty cells) or you'll see the #REF! error.

#20. Rename Pivot Table Column Headings

With this simple tip, you can rename your Pivot Table headings to make your data much more presentable and understandable.

Did you know that you can type over those ugly default column headings in your Pivot Tables?

For example, instead of your column heading saying "SUM of Sales price", you could change it to "Revenue, $", which is more presentable and easier to understand.

Go on, give it a try!

#21. Hyperlink to Different Parts of Your Sheet

You can add hyperlinks to different tabs or even directly to individual cells within your Google Sheets.

They're super easy to create. You don't even have to write any formulas yourself. Simply:

Right click on the cell that you want to turn into a clickable hyperlink
Click "Insert link"
Choose either "Sheets in this spreadsheet" or "Select a range of cells to link"

That's it!

Here's a few examples of how you could use this:

Add a Home button to every tab in your Sheet so you can quickly get back to the first tab
Create a "table of contents" for your Sheet
Link to important calculation cells so they can be easily accessed

#22. Repeat Number Groups With the ROW & MOD Functions

Suppose you're organizing a seating plan or a conference or some other event that requires putting people into teams ("All the one's on me! All the two's with John! All the three's with Sue!"), like this:

Rather than create the grouping manually, which involves a lot of tedious copying and pasting, you can use the ROW and MOD functions to automate the process.

Step 1: Insert the row formula into the cell where your first group starts

=ROW()

Step 2: Subtract the current row number (e.g. if you're on row 1 subtract 1, if you're on row 17 subtract 17), so your formula looks like this:

=ROW() - 1

Step 3: Wrap the formula in step 2 with the MOD function, which gives the repeating number pattern, and set the divisor to equal the number of groups you want (e.g. 3):

=MOD( ROW() - 1, 3)

Step 4: Add 1 to the formula to start your group index from 1 instead of 0

=MOD( ROW() - 1, 3) + 1

Step 5: Drag the formula down as far as you need and it'll create repeating numbered groups for you!

Another example: If you wanted to start your grouping on row 17 and repeat in groups of 7, your formula would look like this:

=MOD( ROW() - 17, 7) + 1

#23. Create Google Sheets Instantly

Create a new Google Sheet instantly with just your web browser.

Type sheets.new into your web browser to instantly create a new Google Sheet (if you're not logged into your Google account you'll be prompted to do that first).

It also works with Docs, Forms, Sites and Slides as well, and it worked when I tested it in a Firefox browser too.

Wow!

#24. Advanced Formula Workout Challenge

(Editor’s note: this example worked at the time of writing (November 2018) but if Wikipedia makes changes to the webpage in this example, the formula may no longer work.)

This tip will show you how to solve challenging formulas by taking baby steps.

Let's suppose we want to scrape the population data from this table in Wikipedia:

And put it into our Google Sheet like this, so we can use it for analysis:

The challenge is to do this with a single formula...!

Ready?

Step 1: Open a new Google Sheet (bonus points for using the last tip and just typing sheets.new into your browser window)

Step 2: In cell A1, insert a basic IMPORTHTML formula to scrape the raw table of population data from Wikipedia:

=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_cities_proper_by_population","table",2)

The data has some issues, but it's a start.

Step 3: Pick just the columns we want, by wrapping the Import formula with a Query formula. Note that we have to use the Col1 notation rather than the column letter in our Select statement.

=QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_cities_proper_by_population","table",2),"select Col2, Col8, Col4",1)

Step 4: Hmm, that population column is messed up! Regex to the rescue!

At this point, we'll deal with the population column on its own and come back to our main formula later.

So make a copy of this Import formula in cell F1 and change it to:

=QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_cities_proper_by_population","table",2),"select Col4",1)

Now we should have a copy of just the population column in column F, like so:

Step 5: Wrap this population only formula in cell F1 with a Regex formula:

=REGEXEXTRACT(QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_cities_proper_by_population","table",2),"select Col4",1),"♠([0-9,]*)")

Hmm, that gives us a #N/A error... 🤔

Étape 6: Transformez cela en une formule matricielle et obtenez la colonne des nombres de population!

= ARRAYFORMULA (REGEXEXTRACT (QUERY (IMPORTHTML ( "https://en.wikipedia.org/wiki/List_of_cities_proper_by_population" , "table" , 2 ), "select Col4" , 1 ), "♠ ([0-9,] *) ) " ))

La formule Regex utilise cette expression "♠ ([0-9,] *)" pour extraire les nombres après le drôle symbole ♠.

Nous avons encore deux problèmes à résoudre: i) nous devons convertir les chaînes en nombres réels et ii) corriger l'en-tête de colonne # N / A ...

Étape 7: Utilisez la fonction SUBSTITUT pour supprimer les virgules et convertir les chaînes en nombres:

= ARRAYFORMULA (SUBSTITUTE (REGEXEXTRACT (QUERY (IMPORTHTML ( "https://en.wikipedia.org/wiki/List_of_cities_proper_by_population" , "table" , 2 ), "select Col4" , 1 ), "♠ ([0-9, ] *) " ), ", " , " " ) * 1 )

La multiplication par 1 à la toute fin contraint les chaînes en nombres après que les virgules ont été supprimées.

Étape 8: Utilisez la fonction IFERROR pour corriger cette erreur embêtante # N / A en haut de notre en-tête de colonne, et remplacez le # N / A par le mot "Population":

= ARRAYFORMULA (IFERROR (SUBSTITUTE (REGEXEXTRACT (QUERY (IMPORTHTML ( "https://en.wikipedia.org/wiki/List_of_cities_proper_by_population" , "table" , 2 ), "select Col4" , 1 ), "♠ ([0- 9,] *) " ), ", " , " " ) * 1, " Population " ))

Bien, nous avons maintenant notre colonne de population en chiffres:

Étape 9: Revenez à notre formule principale dans la cellule A1 et supprimez l'ancienne colonne de population (celle avec tous les nombres amusants). Donc, notre formule en A1 devrait maintenant être:

= QUERY (IMPORTHTML ( "https://en.wikipedia.org/wiki/List_of_cities_proper_by_population" , "table" , 2 ), "sélectionnez Col2, Col8" , 1 )

Étape 10: Il ne reste plus qu'à joindre ces deux plages, dans les colonnes A, B et F, en utilisant la notation entre accolades, comme ceci (montré avec des sauts de ligne pour illustrer les deux plages):

= {

QUERY (IMPORTHTML ( "https://en.wikipedia.org/wiki/List_of_cities_proper_by_population" , "table" , 2 ), "select Col2, Col8" , 1 )

ARRAYFORMULA(IFERROR(SUBSTITUTE(REGEXEXTRACT(QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_cities_proper_by_population","table",2),"select Col4",1),"♠([0-9,]*)"),",","")*1,"Population"))

}

Ok, we're done 🤪

The output is:

(Feel free to delete the workings in column F.)

Debrief:

Yes, it would have probably been much quicker to cut and paste the table from Wikipedia and fix the funny formats manually.

But where's the fun in that? 🤣

In all seriousness though, think of this as an exercise in combining some of the most useful single functions in Google Sheets to create really powerful formulas.

Lastly, there are probably other ways to solve this! That's the beauty of spreadsheets.

Resources:

The Query function is covered in Days 14 and 15 of my free 30 Day Advanced Formulas course and in this blog post.

Array Formulas are covered in Day 17 of my free 30 Day Advanced Formulas course and in this blog post.

The Import function is covered in Day 19 of my free 30 Day Advanced Formulas course and in this blog post.

The Regex function is covered in Day 20 of my free 30 Day Advanced Formulas course.

#25. Neat Trick With Freeze Panes

Freeze panes is a useful and simple technique to lock the top row (or rows) of your spreadsheet, so that they remain in view even as you scroll down your datasets. You're effectively anchoring them in place.

It's really useful because you can keep your column headings in view as you move about your data.

Traditionally, you freeze panes through the View > Freeze menu, but there is another quicker way.

Hover over the bottom of the column heading letters until you see the hand icon, like this:

Click and hold down your mouse button, and then drag the thick line down:

Let go when you've reached the row level you want to freeze (in this case, just the top row):

This technique also works for freezing columns!

#26. Protect Individual Sheets

Control who can edit individual tabs (sheets) within a Google Sheet with the Protect Sheet option.

You have a lot of control when it comes to sharing Google Sheets and deciding who can edit which parts of your Google Sheet.

Suppose you're working on a complex Google Sheet with multiple team members (or maybe sharing a Google Sheet with lots of students) and you're worried about somebody messing up the calculations tab, or the charts tab, etc.. What you really want is for your team members to only work on their tab.

Under Tools > Protect sheet... you can set tab level permissions for users of a Google Sheet.

You can ensure people just edit their sections, and you retain overall control of the Sheet.

And then under Set permissions, you can check or uncheck who has access to the specific tab:

#27. Creating a New Sheet in the Folder You’re In

Previously, I shared how you can type Sheets.new into your browser to create a new Google Sheet. It has the one drawback of creating the Sheet in your root Drive folder, so you have to manually move it to the folder you want.

Instead, if you have a specific folder open in Drive and you want to create a Sheet in that folder, just hit the shortcut Shift + S when you're inside that folder to create a new Google Sheet there.

#28. Automate your Sheets with Macros

Macros are small programs you create inside of Google Sheets without needing to write any code. They allow you to automate tasks.

They work by recording your actions as you do something and saving these actions as a “recipe” that you can reuse again with a single click.

You can also write Apps Script functions directly and import them into your Macros menus, which gives you a lot of flexibility.

Here's my favorite macro, written in script and imported to the macro menu:

Resetting Filters

I use filters on my data tables all the time, and it's annoying that there’s no way to clear all your filters in one go. You have to manually reset each filter in turn or completely remove the filter and re-add from the menu, both of which are tedious.

So let’s create a macro to remove and re-add the filter!

Then we can be super efficient by running it with a single menu click or, even better, with a shortcut key.

1. From your Google Sheet, open the Script Editor window: Tools > Script editor

2. Copy in the following code:

function resetFilter() {

var sheet = SpreadsheetApp.getActiveSheet();

var range = sheet.getDataRange();

range.getFilter().remove();

range.createFilter();

}

3. Back in your Google Sheet, use the Macro Import option to import this function as a macro (Tools > Macros > Import)

4. When you run it, it will remove and then re-add filters to your data range in one go.

For example, in the following GIF, I have a data table with three filters applied. Rather than remove them one by one, I simply run the macro which automatically removes and re-adds filters to my table to get me back to my unfiltered data.

#29. 5 Million Cells

Vous avez maintenant 5 millions de cellules avec lesquelles jouer dans vos feuilles de calcul Google!

Quand j'ai entendu cette nouvelle, la première chose que j'ai faite a été d'ouvrir une feuille de calcul Google et de continuer à ajouter des lignes jusqu'à ce que je dépasse les 2 millions et reçoive le nouveau message d'erreur à 5 millions (parce que je suis un nerd comme ça):

Je n'ai pas encore eu l'occasion de tester les performances de feuilles beaucoup plus grandes, mais je partagerai les résultats lorsque je le ferai.

Lorsque l'équipe de produits Sheets a annoncé pour la première fois la limite de 5 millions de cellules lors de la conférence Google Next 18 , elle a insisté sur le fait que l'aspect performance de l'équation serait suffisamment capable.

Alors la question est, que ferez-vous avec tout cet immobilier supplémentaire?

Pour référence, voici toutes les limites de taille de fichier pour Google Drive (y compris les 5 millions pour Sheets).

# 30. Collaboration et filtres

Vous pouvez utiliser les vues de filtre pour permettre à différents utilisateurs d'une feuille de calcul Google d'utiliser leurs propres filtres sur un ensemble de données.

Plus tôt cette semaine, ma femme et moi écrivions nos cartes de Noël pour cette période des Fêtes. Bien sûr, nous utilisons également une feuille Google pour garder une trace de toutes les personnes à qui nous voulons envoyer des cartes.

Le problème était que je voulais filtrer l'ensemble de données pour n'afficher que les noms sur lesquels j'écrivais, tandis que ma femme voulait pouvoir voir les noms sur lesquels elle travaillait. Chaque fois que j'appliquais un filtre à l'ensemble de données, cela modifiait également la vue dans la feuille Google de ma femme. Argh! Que faire?

La réponse consiste à enregistrer votre ensemble de filtres en tant que vue de filtre, à lui donner un nom et à l'utiliser ensuite sans déranger les autres utilisateurs du même ensemble de données.

Si vous avez déjà une configuration de filtre, vous pouvez simplement l'enregistrer en tant que vue de filtre et lui donner un nom mémorable. Alternativement, vous pouvez également créer un nouveau filtre dans ce menu:

Votre vue de filtre apparaîtra alors, avec une boîte gris foncé autour d'elle.

Ici, vous pouvez le renommer (1), ajuster la plage (2) et la mettre à jour, la supprimer ou la dupliquer (3). Le X sur le côté droit vous permet également de fermer la vue Filtre:

Une fois que vous avez enregistré une vue de filtre, vous pouvez la réutiliser dans le menu du bouton de filtre, avec le menu d' options de la vue de filtre :

Le principal avantage de ces vues de filtre est que les autres personnes travaillant avec cet ensemble de données dans la même feuille ne sont pas affectées. Ils peuvent continuer à voir l'ensemble de données ou créer leurs propres vues de filtre, indépendamment de la vôtre.

Another benefit of course, is that you can save more complex filters using multiple columns to easily return to them.

#31. Create Pictures With the CHAR Function

The CHAR function converts a number into a character according to the current Unicode table, for example CHAR(127764) creates the waxing gibbous moon symbol.

These are the unicode characters used in the picture above:

(Unfortunately, the Christmas tree may not show up for you, so feel free to replace it with something else! Just Google "unicode character for XYZ" and then grab the number part of the HTML Entity decimal.)

#32. Use the YEARFRAC Function to Calculate the % of the Year Left

This tip was inspired by a Twitter account that simply shares the % of the year completed:

Let's replicate it in Google Sheets using the obscure YEARFRAC function.

We'll build it up in steps, starting with today's date in cell A1:

=TODAY()

Extract the year from this date by changing the formula to:

=YEAR(TODAY())

Then calculate the date at the start of the year:

=DATE(YEAR(TODAY()),1,1)

Next, use the YEARFRAC function with the start of the year date you just calculated and today's date as the start/end dates, like so (spaces added for comprehension):

=YEARFRAC( DATE(YEAR(TODAY()),1,1) , TODAY() )

Finally, format the output as a percentage.

As you can see, when I took this screenshot, we were over 99% the way through the year:

An extra challenge for you: use the SPARKLINE formula to create a mini in-cell bar chart showing the year's progress.

#33. Format Individual Data Points In Charts

Here’s how to format individual data points in your Google Sheets charts to make them stand out.

By formatting specific parts of your chart, you can draw attention to them. This makes it quicker and easier for your audience to read your chart. And that's a good thing.

For example, you could highlight the largest values, or values over a certain threshold.

Google Sheets has the ability to format data points individually in charts, like this:

It's a really useful addition to the chart tool and it's very easy to use too.

There are two methods: either right click a specific data point on your chart and select "Format data point", or click the the Chart Editor > CUSTOMIZE > Series > FORMAT DATA POINT button in the chart editor sidebar:

Can you format multiple data points in a chart?

Yes, you can!

Does it work for other types of charts?

Yes, it works with bar charts, column charts, line charts and pie charts. It does not work with the area charts.

Can you set it to format points automatically, for example when they go above a threshold value?

Unfortunately not. However, I'd envisage this being possible in the future. I hope they add it to Apps Script too, so it's possible programmatically.

#34. Quickly Summarize Your Data

Summarize your data at a glance by highlighting it and looking at the status bar tool.

This is a super easy tip.

You don't need to write any formulas or click any menus to get summary statistics about your data.

Simply highlight the data and look down in the bottom right corner of the status bar, where you'll see metrics about your data:

Easy, huh?!

FAQ's

Can you display different metrics?

Yes! Click on the metric to open the menu where you can choose Count, Count of Numbers, Average, Min and the Max metrics.

Does the data have to be continuous?

No! Whatever data you highlight will be included in the summary metrics, e.g.:

#35. How To Remove Duplicates

This tip is a single formula you can use to quickly remove duplicates in Google Sheets.

Check out the accompanying article covering five different methods to remove duplicates in Google Sheets using Add-Ons, Formulas, Pivot Tables, Conditional Formatting or Apps Script.

Here’s how to use the UNIQUE function to remove duplicates in Google Sheets.

This single formula removes duplicates from your data ranges. It's handy both for de-duplicating datasets and inside nested formulas to remove duplicate entries.

It's super easy to and only requires one argument (the range of data), e.g.:

=UNIQUE(A1:D11)

The UNIQUE formula removes the duplicate rows in this example. In other words, it compares all of the columns to find duplicates.

The output looks like this, with the original table on the left and the de-duplicated table on the right:

And it's as easy as that!

#36. Numbers Starting With Zero

This tip explains how to deal with leading zeros in your data. You can use an apostrophe ( ' ) in front of numbers starting with zero to convert them to text and keep any leading zeros.

Have you ever found yourself entering numbers into Google Sheets that begin with 0? Maybe zip codes or invoice numbers or product part numbers.

You've probably been frustrated when leading zeros disappear.

Thankfully there's an easy trick to keep that leading zero. Simply precede the number with an apostrophe ( ' ) to keep the number exactly as it is.

Note: It changes the number into a text value (so you can't do math with it), but since we're talking about zip numbers or invoice numbers etc. this is acceptable.

For example, consider the zip code for Boothbay Harbor in Maine, which has a zip code of 04538 (incidentally, this was the most northerly point of a bike tour I did with my brother in 2014).

Type that zip code into Google Sheets and this is what happens:

Hmm, that's no good.

Now type it with the apostrophe in front of the number and you'll see the difference (the apostrophe does not show up in the cell display):

You'll notice that the zip code is now left-aligned because it's now stored as text rather than a number.

(Note: for true numeric values with leading zeros that you want to keep, you'll want to use Custom Number Formatting. See Tip #59 for more details about Custom Number Formatting.)

#37. Vlookup Formula Challenge

This tip is a formula challenge walkthrough. We'll look at how you can use array formulas to build "virtual" tables nested inside other formulas, like a Vlookup. You’ll learn how to create new data tables dynamically to nest as ranges inside other functions.

Problem we want to solve:

We have a data table and we want to search for "Bob Davis" using Vlookup and return the amount associated with him.

The problem is that in the data table, names are split across two columns, First Name and Second Name. So a standard Vlookup isn't possible at the moment.

Solution:

There's an easy solution: create a quick helper column to combine the first and last names into a full name and use this as the search column.

But what about doing it without creating a new column in the data table?

The trick is to create a new table dynamically, which has the helper column. Let's see it in three steps:

Step 1. Create the full name column

First we need to generate the array of full names using this formula:

=ArrayFormula(A2:A9&" "&B2:B9)

The " " adds a space between the first and last names. The output of this single formula is an array of full names:

(This is an Array formula. You enter the formula and then hit Ctrl + Shift + Enter, or Cmd + Shift + Enter (Mac) to add the ArrayFormula designation.)

Step 2. Add the other columns from the original table

In this step we build the new search table by adding the other columns from the original table that we want to search, using this formula:

=ArrayFormula( { A2:A9&" "&B2:B9 , C2:D9 } )

The curly braces { … } combine arrays. Using a comma (,) between curly brace arrays treats them as columns next to each other, which is what we want, as you can see in the image:

This array formula combines columns A and B into a full name column and then adds back columns C and D to create a new table in H2:J9.

Step 3. Perform the Vlookup

Now we have created the new table, we simply nest it as the range input in a standard Vlookup, with this formula:

=ArrayFormula( VLOOKUP( G2 , { A2:A9&" "&B2:B9 , C2:D9 } , 3 , false ) )

which gives the desired output of $383:

Nice!

Note: if you want to use this Vlookup on another row to look up another full name, you'll want to lock those range references to avoid getting errors.

This concept of creating "virtual" tables that you nest inside of other formulas is super useful. You can also use the Filter function, the Query function etc. to create nested tables.

#38. Text Trickery Part I

This tip was prompted by a recent question from a reader:

How do I make a diagonal line to split a cell, so that I can enter text into two triangular subdivisions?

You can't split a cell explicitly, but you can use some text trickery to achieve this effect. 😈

Here’s how to use Text Rotation to customize the look of your tables in Google Sheets.

Here's the example of a cell with a diagonal line to clearly show your row and column heading labels in a single cell:

To achieve this effect above, use the CHAR function and rotate the text.

CHAR(10) adds a line break.

CHAR(8213) adds a horizontal bar.

Combine these into this formula:

="Sales"&char(10)&char(8213)&char(8213)&char(8213)&char(8213)&char(8213)&char(8213)&char(8213)&char(10)&"Regions"

where you can add as many CHAR(8213) as you require to create the horizontal line.

Center align the cell and then, under the Format menu, rotate the text downwards:

You can choose a custom degree of rotation to get the best effect.

Finally, the formula will look something like this:

#39. Text Trickery Part II

When I shared the first part of this tip (see #38) in an email, I had tons of responses and readers suggested some great, often simpler, ways to do it.

Here's the example from the last tip, with the diagonal line to clearly show your row and column heading labels in a single cell:

Here are some of the great suggestions that I received.

From Melissa

I wouldn't recommend doing it in the sample case you gave, because you've just made the column headings a mess for pretty much anything else, like pivot tables or charts. The labels in column A really need their own proper column heading.

This is a great point, Melissa! I should have included this caveat in last week's email. It's really only suitable for a presentation table where you don't need to do anything further with the data. 👍

From Doug

I played around with it and was able to get a solid line by using an n-dash, which I typed with option-dash on my Mac. That’s CHAR(8211) for the way you did it.

Nice! I agree, the solid line looks better 👍

From Brian

Tighten that up using formulas from a previous lesson!

="Sales"&CHAR(10)&REPT(CHAR(8213),7)&CHAR(10)&"Regions"

Brian has used the REPT function to repeat the CHAR function 7 times, instead of typing it in multiple times. Neat! Thank you 👍

From JC

There is a much simpler way for this one.

First, just type your text in the cell, using Ctrl-Enter at the end of each title to put the next title on another line in the same cell. For the middle line, see my next step.
Second, for the dividing line, use a series of Em-dashes (—) or En-dashes (–), which are shorter. You can enter these directly from the keyboard by holding down the Alt key while you type from the 10-key pad the numbers 0151 for Em-dash, or 0150 for En-dash.
After this, use rotation as in your example. Choosing “–45° Angle” probably works best.

Yes! Simpler is better 👍

From Willem

The way I do this using CONTROL + ENTER for the line break and a few of this character: — to form a line and then another CONTROL + ENTER

After that of course rotate.

I found the — character on https://www.copypastecharacter.com/

All characters you find there you can paste wherever in your docs and sheets!

Yes! Simpler is better 👍

From Martin

I think this is much simpler.

="Row

"&rept("-",20)&"

Col"

Better still would be a formula to replace the "20" above with something like sqrt(sq(rowheight())+sq(colwidth())) but I can't find anything that will return either column width or row height.

Oooh! Interesting idea. 🤔

Create a function to determine the required number of dashes based on the width and height of the cell. Not possible with standard formulas but it sounds like a great contender for a custom formula using the getColumnWidth and getRowHeight methods in Apps Script...

Thank you to everyone who responded! I enjoyed reading all these suggestions and love that people have shared better, simpler ways to do things.

#40. Text Trickery Part I

Ciao, come stai?

If you deal with multiple languages in your Google Sheets, then this tip is for you. Here’s how to combine the GOOGLETRANSLATE function and the DETECTLANGUAGE function to build a translation engine in your Google Sheet.

Google Sheets have an amazingly diverse and powerful set of functions. There are functions for everything from engineering to finance, from statistics to web scraping, from images to hyperlinks, and there's even a function to translate foreign languages!

GOOGLETRANSLATE translates text from one language into another. It takes three arguments: 1) the text to translate, 2) the language of the original text (as a 2 letter code), and 3) the language you want to translate into (as a 2 letter code).

You might have a function like this:

=GOOGLETRANSLATE("Ciao, come stai?", "it", "en")

which translates from Italian to English and gives the answer "Hello how are you?". You can also reference text in other cells, like this:

=GOOGLETRANSLATE(A1, "it", "en")

The DETECTLANGUAGE function takes text (or a cell with text in) and determines the language. It returns the two letter code for the language detected. So:

=DETECTLANGUAGE("Ciao, come stai?")

returns the answer "it", since the function has detected Italian as the language.

So, combine these two functions to create a powerful translation engine in your Google Sheets!

=GOOGLETRANSLATE(A1, DETECTLANGUAGE(A1), "en")

This will translate whatever text is in cell A1, whether it's Italian, Spanish, Russian, Arabic, Chinese, or hundreds of other languages.

Amazing, huh? To have that much power at your fingertips!

Formula Challenge #1

Here’s a formula challenge for you to try. I'll share a solution in the next tip.

Start with a straightforward IMAGE function in cell A1, like this:

=IMAGE("https://blog.hubspot.com/hubfs/image8-2.jpg")

(You can use whatever image you like.)

Your Challenge:

Modify the formula in cell A1 only to repeat the image across multiple columns (say 5 as in this example), so it looks like this:

Rules: You're only allowed to use a single formula in cell A1. 🤪

#41. ROW/COLUMN Functions

(Editor’s note: since this tip was written (March 2019), Google released the SEQUENCE function which makes vector creation even easier. See tip #72)

Here’s how you can use the ROW and COLUMN functions to create lists of numbers to use in Array Formulas.

ROW returns the row number of a specific cell. COLUMN returns the column number of a specific cell.

To create a row or column of numbers, use the ROW or COLUMN function inside an Array Formula:

=ArrayFormula(ROW(1:5))

which gives a column output like this:

The column function version is

=ArrayFormula(COLUMN(A:E))

which gives a row output like this:

If you work with Array Formulas, then you'll find these sorts of helper functions super useful.

For example, you can use these number vectors to help with sorting data.

Ok, now we know how to create these vectors, let's see how we can use them to solve the formula challenge from the previous tip.

Formula Challenge #1 Solution

The Challenge:

Starting with a straightforward IMAGE function in cell A1, like this:

=IMAGE("https://blog.hubspot.com/hubfs/image8-2.jpg")

The challenge was to modify the formula in cell A1 to repeat the image across multiple columns (say 5 as in this example), so it looked like this:

The problem is that the IMAGE function can't be nested inside a REPT function, so you have to get a bit more creative.

Solution 1: using ROW or COLUMN counts:

=ArrayFormula(IF(COLUMN(A:E),IMAGE("https://blog.hubspot.com/hubfs/image8-2.jpg")))

Using this week's tip, the array formula COLUMN(A:E) will output an array 1 to 5. The IF statement treats the numbers as TRUE values, so prints out the image 5 times. For brevity, we can omit the FALSE value, since we don't call it.

Solution 2: using REPT inside the IMAGE formula!

Thanks to Ryan K., Federico J. and Goran K. for this ingenious solution!

As I mentioned, the REPT function doesn't work with the IMAGE function, but flip it round, with the REPT inside the IMAGE function, and it does work!

Start with this formula in cell A1, which creates a single string of joined URLs, with a pipe ( | ) delimiter between them:

=ArrayFormula(REPT("https://blog.hubspot.com/hubfs/image8-2.jpg"&"|",5))

Now, split these into an array of 5 separate URLs:

=ArrayFormula(SPLIT(REPT("https://blog.hubspot.com/hubfs/image8-2.jpg"&"|",5),"|"))

Finally, wrap this with the IMAGE function to get the five images in a row:

=ArrayFormula(IMAGE(SPLIT(REPT("https://blog.hubspot.com/hubfs/image8-2.jpg"&"|",5),"|")))

What I like about this solution is that you could put the number 5 into a different cell and reference it, so that you can easily change how many times the image is repeated. You could even embed another formula to calculate how many times to repeat the image ;)

#42. The Endless Possibilities of Custom Functions

This tip will demonstrate how to easily insert company logos into your Google Sheets using a custom function combined with the IMAGE function. You’ll learn how to create Custom Functions using Apps Script to solve unique problems and save time.

The company Clearbit provides a free logo service API, which will show a company's logo based on only their website domain.

To use it, you append a website domain to their logo url:

https://logo.clearbit.com/{domain}

and it will return an image of that company's logo.

By passing that url into the IMAGE function you can display the company logo in your Google Sheet. For example this will display the Google logo:

=IMAGE("https://logo.clearbit.com/google.com")

or the Nike logo:

=IMAGE("https://logo.clearbit.com/nike.com")

That's pretty cool!

But we can make it more user friendly by creating a custom function -- let's call it LOGO -- to create that Clearbit url for us. Custom functions are little Apps Script programs that perform custom actions or calculations. With some exceptions, you can literally create your own Google Sheet functions to do whatever calculations you want!

In your Google Sheet, under Tools > Script Editor clear out the existing code and paste in the following code:

function LOGO(input) {

return 'https://logo.clearbit.com/' + input;

}

All this does is combine the Clearbit URL and the domain name, to save you having to type it out every time.

Save the project.

Back in your Google Sheet, add a domain in cell A1 and then call the custom function:

=LOGO(A1)

Wrap it with the IMAGE function and ta-da! You'll have your company logos.

=IMAGE(LOGO(A1))

If you want to explore further, you can add a @customfunction comment between the stars /**...*/ to get the auto-complete box:

The code to add this auto-complete box is:

/**

* Returns the Clearbit Logo URL.

* @param {number} input The domain for Clearbit.

* @return The Clearbit API url.

* @customfunction

function LOGO(input) {

return 'https://logo.clearbit.com/' + input;

}

#43. The SUBTOTAL Function

Here’s how to use the SUBTOTAL function with filtered datasets to display a total for the shown values only.

The SUBTOTAL function is a powerful aggregation function. It returns a subtotal for a range of data, based on an aggregation function. In its most common form, which I'll show below, it's used to show a sum of values.

Suppose you have a dataset of values with filters added, like so:

When you work with this dataset, maybe you filter on different categories during your research, e.g. all the Referral clients or all the Buyer deals.

Wouldn't it be nice to add a total value that changed to match whatever filter you had applied?

You can do this by adding a SUBTOTAL function into row 1 above the values columns:

=SUBTOTAL(9, E3:E)

The first argument of the SUBTOTAL function (9) specifies the type of aggregation you want to apply. In this case, the 9 tells the function to SUM the values.

Maintenant, lorsque vous filtrez sur Referral par exemple, la fonction SUBTOTAL sera mise à jour pour afficher uniquement le total des références:

La fonction SOUS-TOTAL dans la cellule E1 affiche un total de 4,1 millions de dollars pour les références uniquement, sur les 10 millions de dollars environ pour l'ensemble de données entier.

Remarque: vous pouvez modifier la fonction d'agrégation en modifiant le premier nombre de la fonction SOUS-TOTAL. Par exemple, au lieu de 9 (SUM), le nombre 1 calcule MOYENNE ou le nombre 2 calcule COUNT. Pour une liste complète, consultez la documentation .

# 44. Année en cours Sparkline

Jetons un coup d'œil aux Sparklines, qui sont des graphiques miniatures qui existent à l'intérieur d'une seule cellule. Je vais vous montrer comment utiliser la fonction SPARKLINE pour ajouter des graphiques miniatures à vos cellules dans Google Sheets.

Dans le conseil n ° 32, j'ai partagé une formule qui calcule à quel point nous sommes dans l'année:

= YEARFRAC (DATE (YEAR (TODAY ()), 1 , 1 ), TODAY ())

En nous inspirant de ce compte Twitter, qui tweete les progrès annuels chaque jour, recréons un graphique similaire dans notre feuille Google:

Commencez avec la formule Year Fraction ci-dessus dans la cellule A1 et enveloppez-la avec des accolades {} pour créer un tableau et ajoutez une deuxième valeur de 1:

= {YEARFRAC (DATE (YEAR (TODAY ()), 1 , 1 ), TODAY ()), 1 }

Cette opération est effectuée pour fournir une deuxième valeur à la formule Sparkline, afin qu'elle puisse également afficher le pourcentage de l'année restante.

Enveloppez cette formule entière avec la fonction Sparkline et, dans le deuxième argument, spécifiez le type comme "graphique à barres", entre crochets:

= SPARKLINE ({YEARFRAC (DATE (YEAR (TODAY ()), 1 , 1 ), TODAY ()), 1 }, { "charttype" , "bar" })

Ensuite, ajoutez une autre option à la Sparkline pour définir la valeur maximale à 1, ce qui garantit que la sparkline affiche correctement les 24%:

= SPARKLINE ({YEARFRAC (DATE (YEAR (TODAY ()), 1 , 1 ), TODAY ()), 1 }, { "charttype" , "bar" ; "max" , 1 })

Utilisez le point-virgule pour séparer les options de votre fonction Sparkline.

Votre sortie ressemblera maintenant à ceci:

Vous pouvez remplacer les couleurs par défaut en spécifiant les options pour "color1" et "color2", comme ceci:

=SPARKLINE( { YEARFRAC(DATE(YEAR(TODAY()),1,1) , TODAY() ) , 1 } , { "charttype","bar" ; "max", 1 ; "color1" , "black" ; "color2" , "#a9a9a9" } )

Finally, you can add a title and the % formula to show the complete picture:

This will automatically update throughout the year, to periodically remind you how quickly time is passing 😉

For more information about Sparklines and other examples, including mini-line and -column charts, check out this article: Everything you ever wanted to know about Sparklines in Google Sheets.

For European Google Sheets users, your syntax is a little different so check out this article which highlights the differences.

#45. Find the Most Frequent Word

You can use the MODE function to find the most commonly occurring value in a dataset.

The MODE function takes a range of numbers for an input and finds the most commonly occurring value.

However, what happens if you have a range of text values and what to find the most frequent?

For example, what's the most frequent U.S. State in a set of survey responses. Well you can still use the MODE function but you need to add some other functions into the mix to make it work.

Imagine you have this dataset and you want to know the most frequent State:

As per usual, let's build the formula in steps.

In cell B1, add this formula:

=ArrayFormula(MATCH(A1:A20,A1:A20,0))

which outputs an array of the position of the first occurrence of the words in column A. In the image above, you'll have a 2 next to every occurrence of Texas for example, because the first time it occurred was in position 2.

Now, wrap it with the MODE function to find the most frequently occurring position:

=ArrayFormula(MODE(MATCH(A1:A20,A1:A20,0)))

MODE returns the most frequent value, which in this case is the first position of the most frequently occurring text value.

You can then retrieve that by adding the INDEX function like this:

=ArrayFormula(INDEX(A1:A20,MODE(MATCH(A1:A20,A1:A20,0))))

This will give the output Texas in this specific example. Nice!

#46. Google Sheets Features Roadmap

The original tip #46 was a list of features coming soon to Google Sheets announced at Google Next 19 conference.

Many of those features have now been released (although we’re still waiting for Connected Sheets general release, which is going to be HUGE. Literally.)

Instead, I thought I’d share a couple of useful places to keep track of the Google Sheets roadmap:

What's New in G Suite

List of Recent Releases

G Suite Updates Blog

#47. Macro to Show/Hide Tabs

You can create a simple tool to focus on your active tab, with just a few lines of Apps Script.

Imagine this: Jess works for a large real-estate brokerage that runs on G Suite. She's a sales team lead and manages a large Google Sheet.

It’s become a sprawling monster, and she finds it hard to focus on the task at hand because the Sheet is so complex.

She likes to hide all the tabs in her Sheet, except the one she’s working in. It helps keep her focussed.

Doing this manually is tedious, so she wrote a few lines of Apps Script to automate the process. Check it out:

Much better. Now she feels like she’s working with just one tab, so she can focus all her energy on it.

But what about when she wants to start using the other tabs again?

No problem, she adds another few lines of Apps Script to automatically unhide the tabs again.

Jess then adds a custom menu (only 8 more lines of code!) so that she can run the script from her Google Sheet directly (as shown in the GIF images above).

In total, it’s about 25 lines of code and takes a couple of minutes to put together.

Want to set this up yourself?

From your Google Sheet, open the script editor: Tools > Script editor

Paste in the following code:

// menu in Google Sheets

function onOpen() {

SpreadsheetApp.getUi()

.createMenu('Focus')

.addItem('Focus', 'focus')

.addItem('Unfocus', 'unfocus')

.addToUi();

}

// function hides all Sheets except the active one

function focus() {

var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

sheets.forEach(function(sheet) {

if (sheet.getName() !== SpreadsheetApp.getActiveSheet().getName())

sheet.hideSheet();

});

}

// function unhides all the Sheets except the active one

function unfocus() {

var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

sheets.forEach(function(sheet) {

sheet.showSheet();

});

}

Hit save and give your project a name.

Still in the Script Editor, run the onOpen function: Run > Run function > onOpen

Click through the permissions popup to allow your Script to run.

(You may see "This app isn't verified", in which case click on "Advanced" and then "Go to [name] project - unsafe" and then click "Allow". These are extra security steps introduced by Google for non-verified scripts. Since we're the author of this script though, it's safe to run. For more info on permissions click here).

Voila! You’ll have a new menu in your Google Sheet where you can run the focus/unfocus tool. (You'll need multiple tabs to see it in action!)

It’s pretty cool what you can do with a little Apps Script!

#48. Night Mode, Anyone?

Check out this fun Apps Script program to switch your Sheets from "day" to "night" mode. Here’s how to change the background color of your Sheets with just a few lines of Apps Script.

Granted this may not have real-world, practical implications, but it's a fun little program that demonstrates how to interact with Sheets from Apps Script.

You can add a custom menu to run the script from your Google Sheet directly (as shown in the GIF images above).

In total, it’s about 45 lines of code and takes a couple of minutes to put together.

The loop code may look complex if you're unfamiliar with Apps Script, but it's not hard to learn.

The script grabs all the rows of data and loops over each row in turn. With each row, it loops over all the cells and changes any white backgrounds to black, and any black backgrounds to white. Any text in the Sheet also gets flipped from black to white, or vice-versa.

Want to set this up yourself?

From your Google Sheet, open the script editor: Tools > Script editor

Paste in the following code:

// menu in Google Sheets

function onOpen() {

var ui = SpreadsheetApp.getUi();

ui.createMenu('Night Mode Menu')

.addSubMenu(ui.createMenu('Choose theme...')

.addItem('Day theme','dayTheme')

.addItem('Night theme','nightTheme'))

.addToUi();

};

// set night theme

function nightTheme() {

var color1 = '#000000';

var color2 = '#ffffff';

changeTheme(color1, color2);

}

// set day theme

function dayTheme() {

var color1 = '#ffffff';

var color2 = '#000000';

changeTheme(color1, color2);

}

// change colors

function changeTheme(color1, color2) {

var sheet = SpreadsheetApp.getActiveSheet();

var cells = sheet.getRange(1,1,sheet.getMaxRows(),sheet.getMaxColumns());

var backgrounds = cells.getBackgrounds();

backgrounds.forEach(function(row) {

row.forEach(function(cell, i, array) {

if (cell === "#ffffff") {

array[i] = color1;

}

if (cell === "#000000") {

array[i] = color1;

}

});

cells.setBackgrounds(backgrounds);

cells.setFontColor(color2);

}

Hit save and give your project a name.

Still in the Script Editor, run the onOpen function: Run > Run function > onOpen

Click through the permissions popup to allow your Script to run.

Voila! You’ll have a new menu in your Google Sheet where you can change your background color.

Granted this only works in limited cases but it’s a cool demonstration of what you can do with a little Apps Script!

#49. Create New Tabs Instantly

This tip is nice and short, but super handy! You can create new tabs in your Google Sheets instantly with Shift + F11.

Using shortcuts in spreadsheets dramatically speeds up your workflows and makes you a more efficient worker.

They're super easy to use, but it takes a little while to train your muscle memory!

#50. Images in Cells

Did you know you can add images directly to cells from the Insert menu?

Before this feature was released, the only way to add an image to a cell was to use the IMAGE() function. This is rather cumbersome and requires a URL for the image, which means the image has to be publicly visible online somewhere.

Alternatively, you can add floating images above the cells. This has the disadvantage that the images are not "locked" in place, so they don't feel part of the Sheet. Images sit on top of the Sheets row/column grid and do not move with the data.

Now though, you can insert images directly into cells. There's no requirement to use a URL, which means you can add images from your local drive.

Images inside cells move with the data. If you add rows, filter or sort the data, then the images will move too.

This is a great feature for adding company logos to your Google Sheets, or icons to your dashboards for example.

You find this image feature under the menu: Insert > Images > Image in cell

#51. Remove Duplicates

You can use the new Remove Duplicates feature under the Data Menu, instead of formulas or add-ons, to remove duplicate entries.

We all need to remove duplicate entries from our data from time-to-time. So it seems crazy to me that we had to wait this long to get a native remove duplicates feature.

It means you'll no longer have to wrestle with the UNIQUE function (unless you have reason to) or use a (paid!) add-on to do this simple task.

The new feature is super easy to use. You find this feature under the menu: Data > Remove Duplicates

Lorsque vous cliquez sur Supprimer les doublons, vous serez invité à choisir les colonnes dont vous souhaitez vérifier les doublons.

Vous pouvez supprimer les doublons là où les lignes correspondent entièrement, ou vous pouvez choisir une colonne spécifique, comme un numéro de facture, quelles que soient les données des autres colonnes.

# 52. Afficher les formules

Vous pouvez afficher toutes les formules de votre feuille avec le raccourci Ctrl + `

Il s'agit d'un raccourci très pratique lorsque vous essayez de déboguer (corriger) votre feuille de calcul Google ou si vous avez reçu la feuille de calcul de quelqu'un d'autre et que vous devez le comprendre.

Appuyez sur Ctrl et sur la coche arrière (sous le bouton ESC) pour basculer entre l'affichage des résultats de formule et les instructions de formule.

Regardez comment cela a aidé à découvrir le désordre des formules dans cette feuille:

Formula Challenge # 2

Commencez avec ce petit tableau de données dans votre feuille Google:

Votre défi consiste à créer une formule unicellulaire qui prend une chaîne de termes de recherche et renvoie tous les résultats qui ont au moins un terme correspondant dans la colonne Termes.

Par exemple, cette recherche (dans la cellule E2, par exemple)

Framboises, orange, pomme

retournerait les résultats (dans la cellule F2 disons):

Une

Deux

Cinq

Six

Sept

Neuf

comme ceci (où le jaune est votre formule):

Si ces instructions ne sont pas claires, voyez si ce modèle de formule défi prêt à l'emploi vous aide .

Je partagerai quelques solutions dans le prochain conseil!

# 53. Utilisez la fonction SPLIT pour séparer le texte dans une sortie de tableau de pièces constitutives

Dans l'astuce # 52, je vous ai mis au défi de commencer avec ce petit tableau de données, dans la plage A2: A11, dans votre feuille Google:

And create a single-cell formula in cell F2 that takes a string of search Terms as an input, from cell E2, and returns all the Results that have at least one matching term in the Terms column, like this (where the yellow is your formula):

Formula Challenge #2 Solution

Solution 1:

=FILTER(A2:A11,REGEXMATCH(B2:B11,JOIN("|",SPLIT(E2,", "))))

Congratulations to Maryam B. for this extremely elegant solution, which was also the shortest solution submitted.

There were a lot of similar entries that had an ArrayFormula function inside the Filter, but this is not required since the Filter function will output an array automatically.

How does this formula work?

Let's begin in the middle and rebuild the formula in steps:

=SPLIT(E2,", ")

splits out the three fruits in cell E2 into separate cells:

Raspberries Orange Apple

Next, join them back together with the pipe "|" delimiter with

=JOIN("|",SPLIT(E2,", "))

so the output is now:

Raspberries|Orange|Apple

Then bring the power of regular expression to the table, to match the data in column B. The pipe character means "OR" in regular expressions, so this formula will match Raspberries OR Orange OR Apple in column B:

=REGEXMATCH(B2:B11,JOIN("|",SPLIT(E2,", ")))

On its own, this formula will return a #VALUE! error message. (Wrap this with the ArrayFormula function if you want to see what the array of TRUE and FALSE values looks like.)

However, when we put this inside of a FILTER function, the correct array value is passed in:

=FILTER(A2:A11,REGEXMATCH(B2:B11,JOIN("|",SPLIT(E2,", "))))

and returns the desired output. Kaboom! 💥

Solution 2:

=QUERY(A1:B11,"select A where B contains '"&JOIN("' or B contains '",SPLIT(E2,", "))&"'")

Thanks to Eric S. for this excellent solution. Again, there is no requirement to use an ArrayFormula anywhere. Impressive!

This formula takes a different approach to solution 1 and uses the QUERY function to filter the rows of data.

The heart of the formula is similar though, splitting out the input terms into an array, then recombining them to use as filter conditions.

=join("' or B contains '",split(E2,", ",0))

which outputs a clause ready to insert into your query function, viz:

Raspberries' or B contains 'Orange' or B contains 'Apple

The QUERY function uses a pseudo-SQL language to parse your data. It returns rows from column A, whenever column B contains Raspberries OR Orange OR Apple.

Wonderful!

Click here to open a read-only version of the template with solutions added. (File > Copy to make your own editable copy.)

I hope you enjoyed this challenge and learnt something from it. I really enjoyed reading all the submissions and definitely learnt some new tricks myself.

#54. Colorful Sheets!

Today's tip is an easy but useful technique to improve the clarity of your Google Sheets: use colors to add context to data in your Google Sheets.

Here are four ideas for how you might add colors to your Google Sheets to make them more readable or understandable:

1. Alternating Colors

Make your tables easier to read by adding alternating colors. You can optionally define Header and Footer styles too. Add via the Format > Alternating colors... menu

2. Heatmaps

Make your data pop and bring attention to the highest or lowest values. Add via the Format > Conditional formatting... > Color scale menu

3. Distinguish new columns in your datasets

I find it helpful, from an audit perspective, to know which columns are original columns from the data source and which are calculation columns I've added. It becomes more important when you're cleaning big datasets. Simply highlight the entire new column and give it a background color.

4. Highlight formula cells

To make it easier to find my formulas, I often highlight the formula cells yellow :)

#55. Use the MID Function to Return a Segment of a String

Google has hidden a Pride-color theme in Sheets (these hidden features are called Easter Eggs, because, well, you have to hunt for them…).

Let's find it with the MID function.

Start with this formula in cell A1 of a blank Sheet:

=COLUMNS($A$1:A1)

Notice how the first A1 is an absolute reference surrounded by $ signs (so that it's locked in place) but the second A1 is a relative reference without any $ signs (so it will move).

Drag this across row 1 and you'll get a set of ascending numbers: 1, 2, 3 etc.

Let's bring the MID function into play and split the word "HELLO" into its separate letters. Try this in cell A1:

=MID("HELLO",COLUMNS($A$1:A1),1)

Drag across 5 columns (staying on row 1) and you'll get an "H" in cell A1, an "E" in cell B1, an "L" in C1 etc. to split out HELLO.

Cool, huh!

Let's up the ante a bit and create a single array formula to achieve this.

In a new tab, type this formula into cell A1:

=LEN("HELLO")

Gives the answer 5, not rocket science. Next, add this:

="1:"&LEN("HELLO")

which outputs 1:5 in cell A1.

Wrap this with the INDIRECT function to convert this string into a valid range reference. Then wrap it with the ROW function and hit Ctrl+Shift+Enter to convert to an Array Formula, like this (still in A1):

=ArrayFormula(ROW(INDIRECT("1:"&LEN("HELLO"))))

This gives a column of numbers, 1 to 5, in column A.

Great! Now we're ready to use these numbers with the MID function again to split our word again. Add the MID function to our formula in cell A1:

=ArrayFormula(MID("HELLO",ROW(INDIRECT("1:"&LEN("HELLO"))),1))

This single formula splits HELLO into separate letters in column A. Finally, let's transpose that from a column to a row:

=ArrayFormula(TRANSPOSE(MID("HELLO",ROW(INDIRECT("1:"&LEN("HELLO"))),1)))

Looking good. That formula outputs HELLO as separate letters across row 1.

So what the heck has any of this got to do with colors or Pride I hear you ask?

Swap the word "HELLO" in your formula in cell A1 to "PRIDE" and watch your Google Sheet light up!

=ArrayFormula(TRANSPOSE(MID("PRIDE",ROW(INDIRECT("1:"&LEN("PRIDE"))),1)))

And here's the output!

Note: you can also just type the letters “P”, “R”, “I”, “D” and “E” into the 5 adjacent columns to see this effect.

#56. Quick Entry & Exit With Formulas

Use F2 or Shift + Return to enter into your formulas. Use Escape to exit your formulas.

Have you ever found yourself needing to copy part of a formula to use elsewhere?

Or maybe you've found yourself trying to click out of your formula, but Sheets thinks you want to highlight a new cell and it messes up your formula...

Here are the shortcut keys you need to quickly enter and exit your formulas!

Start by selecting a cell containing a formula.

Press the F2 key, or press Shift + Return, to enter into the formula.

Press the Escape key to exit your formula and return to the result view.

Any changes are discarded if you hit the Escape key (to save changes you just hit the usual Return key).

Here's another quick trick that's helpful for longer formulas:

When you're inside the formula view, press the Up arrow to go to the front of your formula (in front of the equals sign). Similarly, pressing the Down arrow takes you to the last character in your formula.

#57. Discover New Formulas Today

At the time of writing this tip (July 2019) there are 466 (!) documented functions available in Google Sheets. How many have you used?

I encourage you to find and use a new function today!

Here are a few ways you can explore the functions available in Google Sheets:

1) Import all 490+ Google Sheets functions into your own Google Sheet with this function:

=IMPORTHTML("https://support.google.com/docs/table/25273" , "table" , 1)

How meta!

2) Avec la fonction ci-dessus dans la cellule A1, vous avez une liste de fonctions dans les colonnes A à D. Sélectionnez une fonction aléatoire dans cette liste avec cette formule:

= INDEX (A: D, RANDBETWEEN (2, 467))

* le 467 fait référence au nombre total de fonctions dans la liste, vous devez donc le mettre à jour avec le numéro le plus récent (492 au moment de la rédaction)

3) Autre méthode: saisissez une seule lettre après un signe égal et parcourez la liste des fonctions dans le menu de saisie semi-automatique:

C'est ainsi que j'ai découvert la fonction GESTEP!

# 58. Rendre la mise en forme conditionnelle permanente

Cette astuce a été inspirée par une question d'un lecteur qui souhaitait supprimer les règles de mise en forme conditionnelle mais conserver la mise en forme appliquée.

Comment pouvons-nous faire cela?

Vous pouvez utiliser Collage spécial> Coller uniquement pour rendre la mise en forme conditionnelle permanente.

You've probably used conditional formatting to color rows when certain conditions are met, or perhaps to highlight data on specific dates.

Sometimes you're finished with the "conditional" part and your data is set. You want to delete the conditional formatting rule to make your sheet simpler (so it's faster and less complex for others to interact with). But how?

If you simply delete the conditional formatting rule then ZAP! away goes all the formatting...

This is what you need to do instead:

Highlight the whole range to which you've applied the conditional formatting
Copy this range
Delete the Conditional Formatting rule
Right click or go to the Edit menu and use Paste Special > Paste Format Only

Bingo! All that formatting comes back and this time it's permanent.

#59. Use Custom Number Formatting to Pluralize Data

Have you ever used Custom Number Formatting? It's hidden deep under the format menu so it's not well known. However, it's an incredibly powerful tool to have in your toolbox.

Let's imagine a scenario where we have a column for counting the number of days:

We can apply custom number formatting to add "day" or "days" to the numbers, as follows:

Take a look at the formula bar and notice that the underlying data is still just a number.

In other words we haven't changed the datatype from number to text. We can still add numbers to this column for example.

To apply Custom Number Formatting, go to the Format menu:

Format > Number > More Formats > Custom number formats...

And set the rule to

[=1]0" day";0" days"

This neat little formula tests for whether the number in the cell is equal to 1 and, if it is, adds " day" to the number to give "1 day".

Everything else in the cell will have " days" added to give e.g. "3 days"

This is just scratching the surface with what's possible with Custom Number Formatting.

You can add colors. Format numbers to "k", "m" for thousands and millions etc. Set negative numbers with brackets. Etc. There are hundreds, maybe even thousands, of different scenarios you could apply it to.

I encourage you to experiment with Custom Number Formatting!

#60. Be Creative With Your Google Sheets!

Over 50 years ago, on July 20th, 1969, the lunar module from the Apollo 11 mission touched down on the surface of the moon. Astronauts Neil Armstrong and Buzz Aldrin became the first human beings to step foot on another world.

Last year, I thought it was fitting to celebrate the 50th Anniversary by showcasing a few space themed formulas in Google Sheets!

First off, we begin our journey on planet Earth, which we create with the CHAR function:

To get to the moon, we need a gigantic Saturn V rocket, which we can draw by supplying a series of coordinates to the SPARKLINE function:

If you give a sparkline a range of x- and y-coordinates it will create 2-d shapes.

You'll notice columns A and B, the data range for the sparkline function, are filled with numbers, which are coordinates for the sparkline formula to trace our rocket outline.

This was created by an iterative process, starting with a square with a triangle on top, and working from there.

Feel free to make your own copy of the Saturn V template here (File > Make a copy...)

Saturn V stood 363 feet tall, about the same height as a 36-story tall building, and weighed 6.5 million pounds (2,950,000 kg) at liftoff. Wow!

Apollo 17 was the only night launch:

Here the following CHAR formulas are used: CHAR(10024) creates the stars, CHAR(11939) creates the exhaust outflow and CHAR(128293) creates the fire!

This engineering leviathan carried three astronauts to the moon. We can draw the moon in our Google Sheet with the CHAR function:

What creative things have you done with Google Sheets?

#61. Date Validation

If you enter dates into a cell in your Sheets then you'll love this one.

Here’s how to use Data Validation to ensure valid dates are added in a cell:

1) From the menu: Data > Data validation... or right click in a cell and choose Data validation...

2) Select Date in the Criteria option of the data validation window

This will ensure that only dates can be added in this cell.

Additionally, you can double click on the cell to bring up the date picker:

It's that easy!

#62. Add Star Rating System

Here's how to add a star rating system to your Sheets with the REPT and CHAR functions.

It's quick and easy to add a star rating system to your Google Sheets.

Here's one that shows some of the top movies in 2019, with some fictional ratings (I haven't seen any of these movies so can't comment on the veracity of these ratings!):

In column B is a numerical value representing the rating.

In column C, that value is transformed into a more visually appealing star rating system.

The star is created with our friend the CHAR function. Then we use the REPT function to show the correct number of stars.

The formula for row 2 is:

=REPT( CHAR( 9733 ) , B2 )

You can drag this down the column to fill in all the other star ratings.

It's that easy!

P.S. If you want to practice your Array Formulas, see if you can convert the formula above into an array version to fill out the whole column with a single formula.

#63. Create QR Codes With Formulas

Every time I discover new functionality in Google Sheets I'm blown away by how powerful and versatile it is. This tip is no exception.

Here’s how you can create QR codes in your Google Sheets with a formula.

We call an API with the IMAGE function and it returns a QR code based on our data. It's pretty neat!

We can then scan the QR code with your phone camera or a QR reader to quickly access that data, e.g. here's a QR code I created to access benlcollins.com website (phone screenshot):

The formula to generate the QR code is:

=IMAGE("https://api.qrserver.com/v1/create-qr-code/?data="&A1,4,70,70)

where your data (e.g. a web url or name or address etc.) is in cell A1.

It uses this QR code generator API.

What would you use this function for?

#64. Apps Script Button

A friend recently asked for pointers on creating a script to clear out values in an invoice template with a single button click. This tip will show you how to do it.

In this example I create a basic invoice template with placeholders to hold information:

The user can enter information into cells B5, B8, E5 and E6 (shown in yellow).

In the script editor, accessed through Tools > Script Editor, I add a very simple script to clear these specific cells out:

function clearInvoice() {

var sheet = SpreadsheetApp.getActiveSheet();

var invoiceNumber = sheet.getRange("B5").clearContent();

var invoiceAmount = sheet.getRange("B8").clearContent();

var invoiceTo = sheet.getRange("E5").clearContent();

var invoiceFrom = sheet.getRange("E6").clearContent();

}

I can run this function from the script editor and it will clear out the contents of the invoice. But I want to do that from the front-end of my Google Sheet.

To do that, I can add either a menu or a button.

In this example, I add a button via the Insert > Drawing menu. This brings up the drawing editor where I can easily add a box and style it to look like a button:

When I click Save and Close, this drawing gets added to my Google Sheet. I can click on it to resize it or drag it around to reposition it.

To assign a script, I click the three little dots in the top right of the drawing and select Assign Script:

Then I type in the name of the function I want to run from my Apps Script code, in this case the clearInvoice function.

Now, when I click the button it will clear out the invoice for me!

One last point to note: to edit or move the button after you've assigned it to a script, you now need to right-click on it.

#65. QA Checklists

Here’s how you can use Quality Control checklists to reduce errors in your work.

Whenever I work with complex Sheets, especially ones for clients, I spend time checking my work before sharing it.

The last thing I want to do is to share a Sheet full of errors. It's disastrous for both parties.

Complex projects benefit from robust Quality Control checks

But are you consistent in how you do it? Or is it an afterthought?

Next time you check a Sheet, write down all the spot checks you do.

Now, whenever you update this Sheet, you can run through the same list of pre-defined quality control checks.

Cela réduira la probabilité d'erreurs et vous fera économiser du temps et de l'énergie, car vous n'avez pas à déterminer les vérifications à faire à chaque fois.

Peu importe la taille ou l'insignifiance du chèque, notez-le sur votre liste. Ce pourrait être celui qui détecte une erreur.

Voici un exemple de feuille de liste de contrôle QA que j'utilise pour un projet client:

( cliquez ici pour ouvrir la feuille )

Dans cet exemple, je regroupe les chèques en fonction de l'onglet de ma feuille auquel ils se rapportent.

J'ajoute des cases à cocher et une règle de mise en forme conditionnelle afin de pouvoir cocher les choses au fur et à mesure que je les termine. Je peux voir en un coup d'œil ce que j'ai ou pas encore fait.

Voici la règle de surbrillance conditionnelle que j'utilise dans ce cas (les cases à cocher sont dans la colonne F):

Une liste de contrôle du contrôle qualité réduira les erreurs et vous rendra plus efficace.

# 66. Utiliser les fonctions de l'analyseur pour formater les données du tableau

J'ai récemment posé une question à un lecteur: comment puis-je formater un tableau dans Google Sheets pour que la sortie soit présentée sous forme de pourcentage plutôt que de nombre?

Voyons comment vous pouvez le faire avec les fonctions de l'analyseur.

Les fonctions de l'analyseur sont une famille de fonctions qui mettent en forme les données d'une manière très spécifique:

Pour répondre à la question d'origine, vous utilisez la fonction TO_PERCENT à l'intérieur d'un ArrayFormula pour présenter la sortie en pourcentage.

C'est un excellent cas d'utilisation pour ces fonctions.

Voyons un exemple.

Voici une formule de tableau artificiel qui affichera 0,1, 0,11, 0,12, 0,13, etc. jusqu'à 0,2:

= ArrayFormula (ROW (Sheet1! 10: 20) / 100)

Pour le formater en pourcentages, ajoutez la fonction TO_PERCENT à l'intérieur de la ArrayFormula:

= ArrayFormula (TO_PERCENT (ROW (Sheet1! 10: 20) / 100))

The output now is 10%, 11%, 12%, 13% etc.

Why not just use the formatting buttons I hear you say?

Well, if your arrays are dynamic, i.e. they change size, then using a Parser function ensures the formatting is applied to any new data too.

#67. Manage Apps Script Projects From the Dashboard

This tip will show you how to use the Apps Script dashboard to centrally manage your Apps Script projects and triggers.

As you create more and more Apps Script files, keeping track of them (and their triggers!) can become a challenge. Imagine having to open all your Google Sheets to peer into each Editor window to find a specific, container-bound script.

Of course you don't have to do that.

Instead, use the Apps Script dashboard to see and manage all your script files and triggers in one central place.

The dashboard lets you:

See and search through all your script files at once
Create new script files
View details about script files
Monitor the usage and see any failures of scripts
Add, remove and edit project triggers
And more...

Access your Apps Script dashboard at: https://script.google.com/home

And read more about the dashboard in the Google documentation here.

#68. Brilliant Apps Script Keyboard Shortcuts

Use these keyboard shortcuts to work more efficiently in the Apps Script editor. These simple shortcuts are SO useful when you're working with Apps Script that I implore you to take a few minutes today to try them out!

Auto Comment with Ctrl + /

This works on individual lines or blocks of your Apps Script code.

Move code up and down with Alt + Up/Down

If you find yourself wanting to move code around, this is SUPER handy.

Tidy up indentation with Tab

Keeping your code properly indented makes it much easier to read and understand. This handy shortcut will help you do that. It's especially useful if you've copied code from somewhere else and the indenting is all higgledy-piggledy.

Bring up the Apps Script code auto-complete with Ctrl + Space

How many times have you been typing a class or method, made a spelling mistake only to see the helpful auto-complete list disappear... Bring it back with Ctrl + Space:

#69. Video Sidebar With Apps Script

Here’s how to embed videos in the sidebar of your Google Sheets using Apps Script.

You can use Apps Script to add sidebars to your Google Sheets. It's a super useful technique for gathering or displaying information for users.

Sidebars can contain HTML and CSS content. You can even embed elements like videos, as in this example.

For example, you could create an internal Sheets add-on for your organization where users can access relevant video tutorials directly inside their Sheets.

Here's an example of a sidebar with an embedded YouTube video:

The sidebar is accessed through a custom menu.

It's a total of 21 lines of code in this example.

In your script editor, add the following code to your code file:

function onOpen() {

var ui = SpreadsheetApp.getUi();

ui.createMenu('Video in Sidebar')

.addItem('Show sidebar', 'showSidebar')

.addToUi();

}

function showSidebar() {

var output = HtmlService.createHtmlOutputFromFile('sidebar').setTitle('Video in Sidebar');

SpreadsheetApp.getUi().showSidebar(output);

}

Create a second HTML file, called sidebar.html, and insert this code:

<!DOCTYPE html>

<html>

<head>

</head>

<body>

</body>

</html>

#70. Extract Hyperlink URLs With a Formula

Suppose you have a Google Sheet that contains a long list of hyperlinks, which have all been created with formulas like this:

=HYPERLINK("https://www.benlcollins.com/","Ben Collins Website")

The second argument is the text to display in the cell as the link.

In this scenario, you want to extract all of the underlying URLs from the hyperlinks.

There are two functions you'll need.

Firstly, turn the hyperlink into text so that the URL is showing, with this formula (assuming the hyperlink formula is in cell A1):

=FORMULATEXT(A1)

Next, wrap this with a REGEXEXTRACT function, which extracts just the URL:

=REGEXEXTRACT(FORMULATEXT(A1),"""(.+?)""")

Now you can drag this formula down the column if you have more hyperlinks to extract.

Quicker than doing it manually!

#71. Annotate Line Charts

We haven't seen many chart tips yet, so let's look at one now. It's a simple technique that helps you add context to your charts. Here’s how to use a helper column to add annotations to line charts in Google Sheets.

Let's see it in action with an example.

Suppose you have time series data that you show with a line chart and it would be super helpful to highlight today's value.

You can manually annotate individual points in your chart manually in the chart editor (under the Customize > Series menu option) but it's static and won't change over time.

If you want to highlight today's datapoint you'll need a dynamic approach with a helper column. In the helper column, use an IF statement that basically says:

IF today THEN value ELSE blank cell

Here's an example dataset with a helper column:

The IF formula in this example, starting on row 2, is:

=IF( A2 = today() , B2 , "" )

Create a chart and use columns A, B and C. The date column (A) will be your x-axis, and columns B and C will be your two series. Format series C to show the value and have a large mark size to make it stand out even more (via the menu Customize > Series to select the Today series and then Line Thickness & Data Label).

When you view this chart tomorrow or the next day, the marker point will have automatically shifted along too, so it's always showing today's value.

#72. Build Numbered Lists With the SEQUENCE Function

The SEQUENCE function is a relatively new function in Google Sheets and it's very clever and useful. It's a powerful way to generate numbered lists.

Previously, you had to resort to obscure array formulas like =ArrayFormula(row(1:5)) to get lists of numbers. Things got ugly fast if you wanted to customize these lists.

Thankfully today, we have the SEQUENCE function.

As arguments, you specify: 1) the number of rows, 2) the number of columns, 3) a start value, and 4) a step size.

Arguments 2, 3 and 4 are optional. However, if you want to set them you need to include the previous ones (e.g. if you want to set a step size in argument 4, then you need to set 1, 2 and 3 as well).

Keep this order in mind as you look through the examples below and you'll soon understand how the function works.

1. Ascending list of numbers

=SEQUENCE(5)

2. Horizontal list of numbers

Set the row count to 1 and the column count to however many numbers you want e.g. 5:

=SEQUENCE(1,5)

3. Two-dimensional array of numbers

Set both row and number values:

=SEQUENCE(5,5)

4. Start from a specific value

Set the third argument to the value you want to start from e.g. 100:

=SEQUENCE(5,1,100)

5. Use a custom step

Set the fourth argument to the size of the step you want to use, e.g. 10:

=SEQUENCE(5,1,1,10)

6. Descending numbers

Set the fourth argument to -1 to count down:

=SEQUENCE(5,1,5,-1)

7. Negative numbers

Set the start value to a negative number and/or count down with negative step:

=SEQUENCE(5,1,-1,-1)

8. Dates

Dates are stored as numbers in spreadsheets, so you can use them inside the SEQUENCE function. You need to format the column as dates:

=SEQUENCE(5,1,TODAY(),1)

(Editor’s note: these are US date format MM/DD/YYYY)

9. Decimal numbers

Unfortunately you can't set decimal counts directly inside the SEQUENCE function, so you have to combine with an Array Formula e.g.

=ArrayFormula( SEQUENCE(5,1,10,1) / 10 )

10. Constant numbers

You're free to set the step value to 0 if you want an array of constant numbers:

=SEQUENCE(5,1,1,0)

Wow! I love the SEQUENCE function 🤩

#73. Double Click Dates to Use the Date Picker

Ok, I have a task for you to try right now.

Find a cell in Google Sheets with a date value in (important that it's just a date value and not a date generated by a formula).

Double click it.

Voilà! The date picker comes up for you to select different dates if you wish.

It's a handy trick for folks to change dates without having to type them in.

(Editor’s note: This feature was also covered in tip #61 about Date Validation.)

#74. Use the RANK Function to Find the Position of a Value in a Dataset

Suppose Eva recently took an exam and scored a highly respectable 83%. She wants to know how she fared in relation to her fellow students who took the same exam.

She can use the RANK function to do this:

=RANK( 83 , A2:A101 )

It gives a result of 21 in this example.

In other words, Eva's score of 83% placed her 21st out of 100 students who took this exam. Not bad!

There is an optional third argument for the RANK function, which determines whether to consider the data as ascending (100 is the best score, 0 the worst score in this example) or descending (0 is the best score, 100 is the worst score in this example).

If this third argument is set to 0, then this is ascending. If it's set to 1, it's descending.

The default option is 0, which is the ascending scenario. Hence we omitted it from our formula above. However, to be really explicit, we could have written the formula like this:

=RANK( 83 , A2:A101, 0 )

#75. A Fun Use of the FACT Function

We'll start this tip with a mind-blowing fact, and then use the FACT function in Google Sheets to explain it.

Pick up a standard 52 card deck and give it a good shuffle.

The order of cards in a shuffled deck will be unique.

One that has likely never been seen before in the history of the universe and will likely never be seen again.

I'll let that sink in.

Isn't that mind-blowing?

Especially when you picture all the crazy casinos in Las Vegas.

Let's understand why, and in the process learn about the FACT function and basic combinatorics (the study of counting in mathematics).

Here’s how to use the FACT function in Google Sheets to show that a shuffled deck of cards is unique, one that's never been seen before.

Four Card Deck

To keep things simple, suppose you only have 4 cards in your deck, the four aces.

You can create this deck in Google Sheets with the CHAR function:

The formulas to create these four cards are:

Ace of Clubs = char( 127185 )

Ace of Spades = char( 127137 )

Ace of Hearts = char( 127153 )

Ace of Diamonds = char( 127169 )

Let's see how many different combinations exist with just these four cards.

Pick one of them to start. You have a choice of four cards at this stage.

Once you've chosen the first one, you have three cards left, so there are 3 possible options for the second card choice.

When you've picked that second card, you have two cards left. So you have a choice of two for the third card.

The final card is the last remaining one.

So you have 4 choices * 3 choices * 2 choices * 1 choice = 4 * 3 * 2 * 1 = 24

There are 24 permutations (variations) with just 4 cards!

Visually, we can show this in our Google Sheet by displaying all the different combinations with the card images from above:

(I've just shown the first 6 rows for brevity.)

You can see for example, when moving from row 1 to row 2, we swapped the position of the two red suits: the Ace of Hearts and the Ace of Diamonds.

Five Card Deck

This time there are 5 choices for the first card, then 4, then 3, then 2, then 1.

Le nombre de permutations est donc de 5 * 4 * 3 * 2 * 1 = 120

Déjà beaucoup plus! Je ne l'ai pas dessiné dans une feuille Google et je laisse cela comme un exercice facultatif pour vous si vous le souhaitez.

La fonction FACT

La fonction FACT dans Google Sheets est une fonction mathématique qui renvoie la factorielle d'un nombre donné. La factorielle est le produit de ce nombre avec tous les nombres en dessous.

En d'autres termes, exactement ce que nous avons fait ci-dessus.

Quatre:

La formule du jeu de 4 cartes est = FACT (4) qui donne une réponse de 24 permutations.

Cinq:

La formule du jeu de 5 cartes est = FACT (5) qui donne une réponse de 120 permutations.

Six:

Un jeu de 6 cartes est = FACT (6) qui donne une réponse de 720 permutations.

Douze:

Un jeu de 12 cartes, = FACT (12) , a 479 001 600 façons différentes d'être mélangées.

(Vous avez plus de chances de gagner à la loterie Powerball à 1 chance sur 292 millions que d'obtenir deux jeux de cartes mélangés, même avec seulement 12!)

Cinquante-deux:

Continuez jusqu'à un jeu complet de 52 cartes avec la formule = FACT (52) , et c'est un nombre incroyablement élevé.

Tapez-le dans Google Sheets et vous verrez une réponse de 8.07E + 67, qui est 8 suivi de 67 zéros!

(Cette notation numérique est appelée notation scientifique, où les nombres énormes sont arrondis aux premiers chiffres multipliés par un 10 à la puissance d'un certain nombre, 67 dans ce cas.)

Cette réponse est plus que le nombre d'étoiles dans l'univers (environ 10 suivies de 21 zéros).

Autrement dit: si les 6 milliards d'humains sur terre commençaient à mélanger des cartes à 1 paquet par minute tous les jours de l'année pendant des millions d'années, nous ne serions même pas encore près de trouver toutes les combinaisons possibles.

🤯

Voici un autre fait: la combinatoire fait tourner la tête après un certain temps, donc ça va faire pour l'instant.

# 76. Contrôlez qui peut modifier les onglets

Voici comment contrôler qui peut modifier des feuilles individuelles (onglets) dans votre feuille Google avec le menu "Protéger la feuille".

L'un des super pouvoirs de Google Sheets est ses fonctionnalités collaboratives.

Vous pouvez travailler sur une seule feuille de calcul avec plusieurs autres simultanément.

C'est magique, surtout si vous avez l'habitude de travailler avec des feuilles de calcul de manière asynchrone.

Contrôle d'accès

Si vous partagez une feuille Google avec de nombreuses personnes, mais que vous ne voulez pas nécessairement que tout le monde puisse tout modifier, vous disposez de nombreuses options pour contrôler l'accès.

Aujourd'hui, nous verrons comment restreindre qui peut modifier des feuilles individuelles (onglets) dans votre feuille de calcul Google.

Par exemple, vous pouvez avoir un onglet contenant des données commerciales (par exemple, les objectifs annuels du service) que vous ne voulez pas que quiconque change (accidentellement ou intentionnellement).

Pour protéger une feuille (onglet), faites un clic droit dessus et choisissez " Protéger la feuille "

Ou accédez-y via le menu: Données> Feuille de protection

Cela ouvre une fenêtre contextuelle dans le volet droit, où vous pouvez " Définir les autorisations " pour la feuille choisie.

Cliquez dessus, puis sélectionnez les personnes que vous souhaitez modifier la feuille:

In this case, my name is the only one checked, so I'm the only one who can edit the sheet called Sheet1. You could choose multiple people if you wish.

Protected sheets are marked with a small padlock symbol. Others will still be able to see this sheet, just not edit it.

You can always change or remove Protected ranges and sheets too.

#77. Custom Formulas Data Validation

Here’s how to use Custom Formulas in Data Validation to restrict what types of data can be entered into cells.

Data Validation is a technique you can use to improve the quality of data entered into your Sheets.

For example, you could use Data Validation to ensure that only numbers are entered into a column. This way, you won’t get text or dates or other funny things going on, which would cause issues with calculations.

Standard data validation options include numbers, text, dates, lists of items or lists from ranges in your Sheet.

However, you can take it one step further and create your own custom data validation rules using formulas.

Let’s see some examples.

Select the cell you want to apply the data validation too. Right click to bring up the data validation menu, or choose Data > Data Validation from the main menu.

Select the “Custom formula is” option from the “Criteria” drop down in the data validation editor.

Example 1

Suppose you want each row to have a number entered that is greater than the preceding row.

Use this formula, starting in cell A2:

= A2 > A1

To also ensure that the data is a number, expand the formula to include the ISNUMBER function:

= AND( A2 > A1 , ISNUMBER( A2 ) )

The value in A2 must be a number that is greater than the value in cell A1. Copy the cell down as far as you need to apply to more cells.

If you try to enter text or a smaller number into cell A2, you’ll see this error message:

Example 2

Use this custom formula in the criteria of the data validation to ensure that only a formula can be added into the cell:

= ISFORMULA( A1 )

Example 3

Here’s an interesting one!

Restrict anybody from entering data into a cell until the column width is greater than a specific number of characters. Perhaps you know that this column will contain long text values and you don’t want people making it smaller.

Use this formula as your custom formula in the criteria of the data validation:

= CELL( "width" , A1 ) > 25

Example 4

Check for a valid email address in cell A1 with this formula:

= ISEMAIL( A1 )

Show Warning or Reject Input?

You can choose to show a warning if the data entered does not pass the validation rule or you can flat out reject it in which case the cell contents won’t change.

Validation Help Text

Additionally you can choose to show validation help text when someone clicks on the cell. For example, you might add “Please enter a valid email” to give users some information about what data can be entered in that cell.

#78. Line Chart Trick

In this tip, we'll look at how to dynamically annotate a line chart with a vertical line to highlight today's value. Here’s how to use formulas to create a helper column AND a helper row. Then use a trendline to annotate line charts in Google Sheets.

We're aiming to add a vertical line (the red one in this image) to a line chart to show where today's value is relative to the rest of the data.

In tip #71, I showed you a basic method for annotating today's value on a line chart, so that it stands out. We did that by adding a dummy series with just a single value for today.

Let's take that a step further and add a full line to highlight today.

Suppose you have this dataset:

To get the line effect we need to display the row for today twice, so that we can trick the chart into letting us use a trendline for that vertical line.

Use this formula in cell D2 to duplicate the row with today's date:

= SORT( { A2:A17 ; TODAY() } )

Then in cell E2, add this formula and copy it down:

= IF( D2 = TODAY() , 0 , "" )

And then in cell F2, use a VLOOKUP formula to retrieve the original data from the dataset:

= VLOOKUP( D2 , $A$2:$B$17 , 2 , false )

Your new dataset will look like this in columns D, E and F:

Look closely and you'll see the row for today's date is now repeated twice (courtesy of the { A2:A17 ; TODAY() } array construction. The SORT wrapper ensures they're in the correct order.).

Now you can highlight columns D, E and F and create a line chart with this data.

Now this is where the trick comes in.

Under the Customize menu (1), choose Series (2) and then the "Today" series (3). Add a trendline (4) to that series and it'll give you the vertical line:

The nice thing about this method is that it's dynamic, so the vertical line will update as today becomes tomorrow, i.e. it'll keep pace with the correct date.

Feel free to make your own copy of the Google Sheet Tip 78 template.

(Note: I've set my file sharings to allow anyone with the link to view this file. You may not able to open this file because it's from an outside organization, and my G Suite domain is not whitelisted at your organization. You may be able to ask your G Suite administrator about this.

In the meantime, feel free to open in an incognito window and you should be able to view it.)

#79. Find Every Formula

Excel has a nifty feature called GoTo, which lets you easily jump around your spreadsheets and find specific information or items.

Google Sheets does not have this functionality, but we can use alternative techniques to achieve the same goal.

Let’s look at how to use conditional formatting to highlight every formula in your sheet.

Suppose your Google Sheet is full of formulas and you want to see where they all are, so you can check them. You don't want to simply look through the Sheet because you might miss some.

Instead, here's a method to highlight them all for you.

Click on the box in the top left corner, between the column A and the row 1 headings. This will highlight your entire Sheet.

Then choose Format > Conditional Formatting from the menu.

Select "Custom formula is" under the Format rules.

Set the formula to:

=ISFORMULA( A1 )

Change the formatting style if you wish. I like to use a bright yellow.

Click Done and all the formulas in your Sheet will be highlighted, making them much easier to see.

You can use this same technique with other conditional formatting rules like ISERROR to find all your errors.

Thanks to reader Robert B. who shared this idea with me!

#80. Simple Formula Clock

In this tip, we'll build a simple digital clock in a Google Sheet. We’ll subtract the TODAY function from the NOW function to get the current time.

In a blank Google Sheet, add this formula to cell A1:

=TEXT( NOW() , "hh:mm am/pm" )

The NOW() part returns the date and time.

The TEXT function formats this answer as hours and minutes with an AM/PM designation.

To make it update, go to File > Spreadsheet settings, and set the spreadsheet calculation settings to be “On change and every minute”.

#81. Hyperlinked Image

Here’s how to combine the IMAGE and HYPERLINK functions to create clickable images.

This is a fun tip that came up at a in-person workshop I taught recently.

The question was: can I create a hyperlinked image?

Answer: yes, you can!

Simply drop the IMAGE function into the link label argument of the HYPERLINK function.

Here's an example of the National Christmas tree in the White House garden:

Clicking the image link will take you to the National Park Service info page about the tree.

Here's another, pointing to Google's homepage:

It's a little easier to see the formula because the URLs are shorter.

=HYPERLINK( "https://www.google.com/" , IMAGE( "https://www.google.com/favicon.ico" ) )

Could be a nice way to add some visual elements to your Sheets!

#82. Dates Are Really Just Numbers

Dates in spreadsheets -- and this applies in Excel as well as in Google Sheets -- are actually just numeric values with a special date format applied.

So today, 12/30/2019, is actually stored by Google Sheets as the number 43,829.

Try it:

Put today's date in a cell (shortcut: Ctrl + ; ) and change the formatting of the cell to a number format.

These numbers are known as serial numbers. Each day is represented by one whole number.

The number 1 corresponds to 12/31/1899 in Google Sheets (but 1/1/1900 in Excel). So today's number tells us that 43,829 days have elapsed since then.

And if I add the time, then 12/30/19 at 11am is 43,829.45833, where the decimal places represent the time, a percent value of how far through this 24 hour period we are.

Here are two columns of identical numbers, one formatted as numbers, the other formatted as dates:

Once you understand this fact about dates and how spreadsheets really "see" dates, it makes it much easier to work with them. When you subtract two dates, all you're really doing is subtracting two numbers.

#83. Working With Excel Files

Did you know you can work with Excel files directly in Google Sheets without converting them?

Have you ever been sent Excel files from colleagues or external partners?

I'm sure you have.

Perhaps you imported the data into your Google Sheet, or opened the Excel and simply copy-pasted the data directly.

Then you did your work, slicing and dicing that data in Google Sheets. Finally you downloaded your Sheet as a new Excel file to send back to your contact (who doesn't use Sheets).

However, there's a better workflow when you know that the file begins and ends in Excel.

You can open the Excel file in Google Sheets and work on it, without converting it into a Google Sheets file.

That's right, it's still an Excel file but it looks and feels like a Google Sheet. So you have all the functionality you're familiar with. And it auto-saves as the original Excel.

How to open an Excel file but not convert it:

This works for Excel files saved in your Google Drive.

From your Drive folder, double-click the Excel file you want to open.

It will open in Preview mode. At the top of the preview window, there's a button called "Open with Google Sheets". Click it.

It opens in Sheets and looks like a regular Google Sheet but you'll notice an XLSX flag at the top to tell you that you're working on the Excel file.

Your changes are autosaved directly into the original Excel.

Note that not all the functionality transfers from Google Sheets to Excel, and vice versa. For example, slicers and scripts are both implemented differently and won't transfer between the tools.

#84. Colored Checkboxes

Here’s how you can use Checkboxes and Conditional Formatting to change the color of "checked" rows.

Checkboxes are an easy way to add some interactivity to your Google Sheets.

Users of the Sheet can check or uncheck the box to cause an action to happen.

Un exemple classique serait une liste de tâches, où la vérification indique qu'une tâche est terminée. Vous pouvez améliorer visuellement cela en combinant la case à cocher avec une mise en forme conditionnelle pour changer la couleur des lignes qui ont été cochées.

Supposons que vous ayez cette liste de tâches de base:

Vous ajoutez des cases à cocher en cliquant avec le bouton droit sur une cellule et en sélectionnant Validation des données , ou dans le menu Données> Validation des données . Choisissez ensuite Case à cocher dans le menu déroulant Critères.

Vous pouvez maintenant ajouter une mise en forme conditionnelle pour mettre en surbrillance les lignes terminées et rendre la compréhension des données plus rapide:

Pour ce faire, allez dans le menu Format> Formatage conditionnel

Définissez les règles de format pour que la formule personnalisée soit

Et entrez cette formule:

= $ B2 = VRAI

Dans cet exemple, j'ai défini le format sur la couleur d'arrière-plan rouge, le texte rouge foncé et le barré.

Cela appliquera la mise en forme conditionnelle à la ligne entière.

Il y a quelques points importants à noter:

Nous nous référons à la colonne "B" parce que les cases à cocher sont dans la colonne B. Nous utilisons la ligne 2 parce que nos données commencent sur la ligne 2 (nous omettons les en-têtes de la ligne 1). Le détail crucial est le "$" devant le B, qui verrouille le test conditionnel à la colonne B pour chaque ligne. Ainsi, la mise en forme conditionnelle teste uniquement la formule personnalisée pour la colonne B, mais l'applique à l'ensemble de chaque ligne.

Le test consiste à savoir si la valeur est égale à VRAI, ce qui correspond à la case à cocher en cours de vérification. Si elle est cochée, la mise en forme est appliquée.

Les cases à cocher non cochées ont la valeur FALSE.

# 85. Fonction TRANSPOSE: Partie I

This is a nice, easy tip - plus here’s another Formula Challenge. (You might find this tip useful for this challenge!)

The TRANSPOSE function is a handy function that changes data from rows into columns, or vice versa.

Suppose you have a column of data in the range A1:A10. This formula (in cell B1) will show that data as a row:

=TRANSPOSE(A1:A10)

It works with tables of data too, so it's a quick way to re-orientate your datasets. You can also nest the TRANSPOSE function inside of other functions, which is useful when you're doing data wrangling.

All in all, it's a really useful function to know about!

Formula Challenge #3

Ok, are you ready for this?

Suppose you have a list of words in a single cell, separated by commas and not in alphabetical order, like so:

Epsilon,Alpha,Gamma,Delta,Beta

Your challenge is to create a single formula (i.e. in a single cell) that reorders this list into alphabetical order, to give an output like this (in the yellow cell):

Alpha,Beta,Delta,Epsilon,Gamma

I'll share answers in the next tip!

#86. TRANSPOSE Function: Part II

So, in tip #85 I challenged you to find a single formula that could alphabetically sort a list of words in a single cell.

We'll build this solution in steps, and you'll see that it's not too difficult!

Start with a list of words in a single cell, separated by commas and not in alphabetical order, like so:

Epsilon,Alpha,Gamma,Delta,Beta

Your challenge was to create a single formula (i.e. in a single cell) that reorders this list into alphabetical order.

Formula Challenge #3 Solution

Step 1

Use the SPLIT function to separate the comma-delimited string into separate cells.

=SPLIT(A1,",")

(Split has two additional arguments and you have to be precise with your delimiter. In this simple example, we can omit the two additional arguments.)

Step 2

Use the TRANSPOSE function from last week's tip to change from row orientation to a column orientation, so that we can sort in Step 3.

=TRANSPOSE(SPLIT(A1,","))

Step 3

Sort the data with the SORT function!

You don't need to specify a column or direction, because we only have 1 column and we want ascending order, which is the default direction. This keeps our formula brief.

=SORT(TRANSPOSE(SPLIT(A1,",")))

Step 4

Finally, join the column back together with the JOIN function, again using a comma as the delimiter. There's no need to use a second transpose because the JOIN function works equally well with a column of data as it does with a row of data!

=JOIN(",",SORT(TRANSPOSE(SPLIT(A1,","))))

Bingo!

#87. Create Data With Randarray

Here’s how to use the RANDARRAY function to generate data automatically.

Have you ever needed to create a quick table of values? Perhaps to test out your formulas or pivot tables, or maybe to teach a concept for which you need fictitious data.

It's tedious to create manually.

Instead, you can create it with the RANDARRAY function.

Step 1: Create a random array of numbers in your Google Sheet with the RANDARRAY function. Specify the size of your dataset with a number of rows and columns:

= RANDARRAY( 3 , 4 )

Step 2: To get whole numbers multiply by 100 (or some other scaling factor) and use the ROUND function to turn into whole numbers:

= ROUND( RANDARRAY( 3 , 4 ) * 100 )

Step 3: Turn into an Array Formula to fully expand again, by pressing Ctrl + Shift + Enter (or Cmd + Shift + Enter on a Mac):

= ArrayFormula( ROUND( RANDARRAY( 3 , 4 ) * 100 ) )

The output of this formula is:

The RANDARRAY function is a volatile function, meaning it will recalculate every time you make a change in your Sheet.

To avoid this, highlight the whole dataset created by the RANDARRAY, copy it (Ctrl + C or Cmd + C) and paste-special as values (Ctrl + Shift + V or Cmd + Shift + V) over the top. This converts the array formula into a dataset of hard-coded values, which don't change.

#88. SWITCH Function

The SWITCH function is a useful tool for categorizing data. In the right circumstances, it can save you from messy, nested IF statements.

The SWITCH function is used to test an expression against a list of cases. It returns a value when the expression is EQUAL to one of the cases.

It has some similarities to the IFS function, but differs because SWITCH tests for exact matching rather than whether a condition is true (e.g. X > Y). SWITCH also has a default option to return a value if no match is found.

Let's see an example. Suppose we have this data set of student grades and we want to add context to each grade level.

Use this SWITCH function to categorize these grades:

=SWITCH(

B2,

"A","Top marks! Great job!",

"B","Keep up the good work",

"C","Could do better",

"Failed. Remidial study needed"

)

Let's break it down:

B2 is the condition we're going to test. It's the grade letter from column B and it's the input to our SWITCH function.

We check the value of B2 against the first case "A". If they match (i.e. the grade in B2 was also "A") then SWITCH returns the string "Top marks! Great job!".

If the value from B2 doesn't match "A", we move on and test it against "B" and if that fails, test against "C".

If the condition doesn't match any of the cases, the last string is returned: "Failed. Remedial study needed". It's our catch-all solution. Anything that's not "A", "B" and "C" will return this answer.

This is what the solution looks like:

Give it a try and SWITCH things up!

#89. Upgrade Your Apps Script!

In early 2020, Google announced the launch of the V8 runtime for Apps Script, which is the same runtime environment that powers Chrome.

(A runtime environment is the engine that interprets your code and executes the instructions.)

This V8 engine means our scripts run much faster and allow us to take advantage of all the modern JavaScript features. Here’s how to switch your Apps Script to V8,

When you open the Apps Script editor (Tools > Script Editor), you’ll see a yellow notification bar at the top of your editor window prompting you to enable the new V8 runtime.

You can also select from the menu: Run > Enable new Apps Script runtime powered by V8

With V8 enabled, your Apps Script code will run much faster.

In addition, you can use modern JavaScript syntax in your code, like:

Default parameters
Better multi-line strings
let and const keywords
Arrow functions
and much, much more...!

Here's one example, showing how you can now specify default parameters when defining functions (the bits highlighted in yellow are new in V8):

function addNumbers(x = 1, y = 2) {

Logger.log(x + y);

}

The function addNumbers simply logs the value of x + y.

If we don’t tell the function what the values of x and y are, it uses the defaults we’ve set (x is 1 and y is 2) and outputs an answer of 3.

#90. Rolling Average Formula

The INDEX function is one of the most useful and versatile spreadsheet functions. It's a powerful way to access your data.

And you know what's even better than an index function? Two index functions!

Here’s how to use the INDEX():INDEX() formula trick to get data dynamically.

This tip is one of the most challenging tips I've shared but it's worth the effort to learn.

The index function has a curious, hidden property that's extremely handy.

If you put the index function into a range reference it returns a valid cell address, which means you can build dynamic ranges of data.

Let's see an example.

Suppose we have a column of values -- perhaps it's recent transaction values or new leads each day -- and we want to compute a rolling 7-day average.

Double Index Trick

We start by using this double index trick to extract the last 7 values from the list.

Whenever new data is added, the double index formula includes the new data and "rolls" down the range.

Here's the formula to extract the last 7 values from column A:

=INDEX(A2:A,COUNTA(A2:A)-6):INDEX(A2:A,COUNTA(A2:A))

In the image you can see that the double index formula in cell C2 has returned the last 7 values from column A. If we add additional data to column A then the formula will update to show the last 7 values.

(You can change the number 6 to something else if you want a different period.)

Can you see how it works?

The COUNTA() simply counts how many values we have in column A.

The INDEX(A2:A,COUNTA(A2:A)) returns the value at the position we specify. The first INDEX gets the value 7th from the bottom and the second index gets the bottom value.

Cependant, selon l'astuce d'index ci-dessus, lorsque nous mettons une fonction INDEX à côté d'un deux-points ":" dans une référence de plage, elle renvoie l'adresse de la cellule au lieu de la valeur! Sensationnel!!

Ainsi, les deux fonctions d'index renvoient respectivement l'adresse de cellule A10 et A16, au lieu des valeurs dans ces cellules! Cela donne la référence de plage A10: A16 qui renvoie les 7 valeurs pour nous.

Cette formule équivaut à dire:

= ArrayFormula (A10: A16)

Sauf que le double index continue de "rouler" avec les données pour obtenir les 7 dernières valeurs à mesure que de nouvelles données sont ajoutées.

Moyenne mobile

La formule à double index peut être utilisée pour créer une moyenne mobile, en enveloppant ce double index avec une fonction MOYENNE standard:

= MOYENNE (INDEX (A2: A, COUNTA (A2: A) -6): INDEX (A2: A, COUNTA (A2: A)))

# 91. Sparklines conditionnelles

La fonction SPARKLINE est l'une de mes fonctions préférées dans Google Sheets, donc je suis ravi de partager cette astuce. Je vais vous montrer comment imbriquer une fonction IF à l'intérieur de SPARKLINE pour créer un graphique sparkline conditionnel qui change de couleur en fonction des données.

Les graphiques sparkline sont de petits graphiques qui existent à l'intérieur d'une seule cellule. Ils sont créés avec la fonction SPARKLINE dans Google Sheets.

Voyons comment créer un graphique sparkline qui change de couleur en fonction des données sous-jacentes.

Dans ce scénario, les valeurs de la colonne A varient entre 1 et 10 et je souhaite que les valeurs supérieures à une valeur seuil de 6 deviennent vertes:

La formule de la colonne B pour créer ces graphiques est la suivante:

= SPARKLINE (A1, {"charttype", "bar"; "max", 10; "color1", IF (A1 <7, "red", "green")})

Vous pouvez voir la fonction IF insérée comme option pour le paramètre "color1" du graphique à barres sparkline.

N'hésitez pas à modifier cette instruction IF avec différentes valeurs de seuil et / ou couleurs (vous devrez peut-être également régler le paramètre "max").

If you want to have multiple tiers of colors, you can nest an IFS function instead of a single IF, for example:

=SPARKLINE(A1,{"charttype","bar" ; "max",100 ; "color1", IFS(A1<25 , "red" , A1<75 , "black" , A1<=100 , "green")})

This formula, applied to numbers between 1 and 100, gives an output like this:

#92. Trace Precedents With F2

The F2 key can be used to trace precedents in formulas.

Press the F2 key to enter the "formula view" of a cell with a formula in. You're probably familiar with that shortcut key.

Google Sheets highlights ranges in your formula expression and in your actual Sheet with matching colors. It applies different colors to each unique range in your formula.

However, it has another useful property too.

If you position your cursor over a range of data in your formula and then press the F2 key again, it will highlight that specific range of data for you (even if it's in a different tab):

In this example, the VLOOKUP was on Sheet2 but the data was on Sheet1. When I press F2 the first time it highlights the ranges in the formula but I still don't know where the lookup table is. So I hover over the lookup table reference and press F2 again. Voila! It takes me right there.

It's a handy feature when you're auditing Sheets or working with someone else's formulas. It helps you understand where the underlying data is.

#93. Group Columns Trick

Use the group columns feature to build "sidebars" in your Google Sheets.

I'm sure most of you know that you can group rows or columns in Google Sheets. (It was featured way back as tip #11!)

If you highlight several columns, you can right click and choose to Group columns. This adds a button above the columns, which you can toggle to show/hide these grouped columns. (It works for rows as well.)

You can set the +/- toggle button to be on the left or right of the group by right-clicking on the +/- button and selecting left or right.

Here's an example:

Knowing this, let's see how you can add a sidebar to your Google Sheets.

Add or select columns to the right (or left) side of your Sheets. Group them.

Next, add the text "Click [+] for info" into the top cell next to your grouped columns.

Rotate that text to make it vertical and then merge cells with the rows beneath so it has space to be fully shown.

Change the background color of this column so that it stands out.

And voilà! It's a really neat way to tidy up your Google Sheets!

Here's a zoomed in view of the vertical text bar:

#94. Organize Tabs

Use separator tabs to keep your Google Sheets organized.

I had a question recently about whether it's possible to group tabs in a Google Sheet file, to keep things organized when you have a lot of them.

(A "tab" in an individual Google Sheet file is also called a "Sheet", but here I've used the term tabs to avoid confusion.)

Unfortunately it's not possible to group tabs, but that doesn't mean you can't do anything to organize them.

Here's a trick I use to keep multiple tabs organized:

I insert a tab between the "groups" of tabs to create a breakpoint that's easy to find.
Name this tab with a reference to the "group" of tabs that follow. My convention is to add some arrows to indicate it's an organizer tab e.g. Forecast Sheets >>
Add a color to that tab.

It looks something like this in practice:

For the tab itself, I delete all rows and columns except A1. In cell A1, I add information about the group that matches the tab name. I also use the same color scheme (click to enlarge):

It's simple but it works surprisingly well to keep your larger Google Sheets organized.

#95. Pivot Date Trick

Here’s how to create pivot date groups from dates in your pivot tables.

For years, Google has been adding features to pivot tables in Google Sheets to increase their functionality and bring them closer to their Excel counterparts.

In this tip, we'll look at date grouping, which is hugely useful and a valuable addition to Sheets pivot tables.

Suppose you have a table of data with a column of dates and you're interested in summarizing that daily data at a higher level, e.g. by month.

In this example, we have website pageviews on a daily basis (with the dates in US format dd/mm/yy):

It's rather cumbersome to summarize with formulas but it's just a few clicks in a pivot table.

Step 1: create your pivot table (menu: Data > Pivot table).

Step 2: add the date column to the rows section of your pivot table and add a value (pageviews in this case)

Step 3: right click on any date in the pivot table and choose "Create pivot date group" and select the grouping level you want

The individual dates will collapse down to the date groups you've chosen.

For example, here I've chosen the "Day of the week" group and elected to show an average pageview for each day of the week:

It's as easy as that!

Lastly, there's one nuance to keep in mind. Toggling the "Show totals" checkbox for the rows of dates will undo the grouping you've set. You can add it right back, but it's easier to remove the totals before grouping to avoid this behavior.

#96. Pivot Custom Text Groups

Voici comment créer des groupes de texte personnalisés dans vos tableaux croisés dynamiques.

Dans le prolongement du dernier conseil sur le regroupement de dates personnalisé dans les tableaux croisés dynamiques, examinons une autre option de regroupement personnalisé disponible dans les tableaux croisés dynamiques.

Cette fois, supposons que vous ayez un tableau de données et que vous souhaitiez résumer ces données de manière très spécifique.

Par exemple, vous souhaiterez peut-être regrouper les données de transaction en zones géographiques pour des commerciaux particuliers.

En commençant par le simple tableau croisé dynamique suivant:

Imaginez que nous voulons regrouper les résultats de l'état pour chaque représentant commercial différent.

Étape 1: sélectionnez les éléments que vous souhaitez grouper en cliquant dessus et en maintenant la touche Ctrl (PC / Chromebook) ou Cmd (Mac) enfoncée

Étape 2: clic droit et sélectionnez "Créer un groupe pivot"

Cela créera un groupe personnalisé dans votre tableau croisé dynamique étiqueté avec les éléments du groupe.

Étape 3: Pour renommer le groupe, tapez simplement sur le haut de l'étiquette par défaut.

Pour créer plus de groupes, répétez les étapes ci-dessus.

Une fois notre regroupement terminé, notre nouveau tableau croisé dynamique avec regroupement personnalisé pourrait ressembler à ceci:

C'est aussi simple que ça!

(Si vous prévoyez d'utiliser ces groupes fréquemment, vous souhaiterez peut-être créer une colonne dans votre ensemble de données avec chaque ligne classée dans son groupe. De cette façon, vous pouvez les utiliser directement dans vos tableaux croisés dynamiques.)

# 97. Bacs de table pivotante

À la suite de l'astuce 95 (regroupement des dates dans les tableaux croisés dynamiques) et de l'astuce 96 (regroupement de texte personnalisé dans les tableaux croisés dynamiques), cette astuce explique comment regrouper les valeurs des tableaux croisés dynamiques en groupes ou bacs. Pensez aux histogrammes.

Supposons que vous ayez une colonne contenant les âges des individus dans votre jeu de données et que vous souhaitiez savoir combien se situent dans la tranche d'âge 20-29 ans, la tranche 30-39 ans, la tranche 40-49, etc.

Vous pouvez facilement le faire avec le regroupement de valeurs dans votre tableau croisé dynamique.

Étape 1: ajoutez les âges dans la section des lignes de votre tableau croisé dynamique.

Étape 2: ajoutez une métrique COUNT dans la section des valeurs, dans cet exemple un décompte de la colonne des âges.

Étape 3: Faites un clic droit sur l'un des âges dans le tableau croisé dynamique et sélectionnez «Créer une règle de groupe pivot»

Étape 4: Choisissez les limites et la taille d'intervalle appropriées. Utilisez les valeurs minimale et maximale de votre ensemble de données pour vous aider à déterminer ces valeurs. Expérimentez avec les tailles d'intervalle pour voir ce qui fonctionne pour vous.

Le tableau croisé dynamique fini ressemblera à ceci:

Ce que cela nous dit, c'est qu'il y a 14 enregistrements dans notre ensemble de données qui avaient une valeur d'âge comprise entre 20 et 29 inclus. Ensuite, il y a 9 enregistrements entre 30 et 39, etc.

C'est une fonctionnalité vraiment utile pour l'analyse des données. Vous pouvez créer des graphiques à partir de ces tableaux croisés dynamiques, ce qui vous aidera à déterminer si la distribution de vos données est normale.

(Les données utilisées dans l'exemple ci-dessus ont été générées par la fonction RANDBETWEEN et ne sont donc pas distribuées normalement!)

Il y a une nuance à garder à l'esprit. La désactivation de la case à cocher "Afficher les totaux" pour les lignes de dates annulera le regroupement que vous avez défini. Vous pouvez l'ajouter immédiatement, mais il est plus facile de supprimer les totaux avant de les regrouper pour éviter ce comportement.

# 98. Utilisez le graphique combiné pour créer des graphiques à puces

Les graphiques à puces sont des variations de graphiques à barres qui incluent une mesure principale par rapport à une gamme d'autres mesures à des fins de comparaison.

Par exemple, vous pouvez afficher vos revenus par rapport à des valeurs de prévisions basses et hautes pour évaluer les performances.

Il n'y a pas d'option de graphique à puces natif dans Google Sheets, nous devons donc utiliser l'option de graphique combiné pour en créer une. Et voici comment nous procédons.

Ensemble de données de graphique à puces

La colonne B contient la mesure principale (appelée "Disponible"). Les colonnes C et E ont les valeurs de plage basse et haute. Les colonnes D et F sont des étiquettes facultatives.

Configuration du graphique à puces

Mettez en surbrillance l'ensemble de données
Insérer> Graphique
Sous l'option de configuration, choisissez le type de graphique pour être un graphique combiné
Sous Personnaliser, définissez la série sur les valeurs suivantes:
Disponible doit être une colonne
Faible devrait être une zone en escalier
Haute devrait être une zone en escalier
Pour les séries Low et High, ajoutez des étiquettes de données et définissez Type sur Custom et Position sur Center
N'hésitez pas à supprimer la légende (Position> Aucune)
Expérimentez avec les couleurs de la série.

Votre graphique final devrait ressembler à ceci:

# 99. Utiliser des formules matricielles pour vérifier si les cellules sont vides

Ajoutez des données à la colonne A. Il peut s'agir d'une liste de nombres, ou d'une liste de noms, ou de dates, ou tout ce que vous voulez vraiment. Peu importe si vous incluez des cellules vides ou non.

Peut-être que cela ressemble à ceci:

Dans la cellule C1, ajoutez la formule suivante:

= ArrayFormula (A: A <> "")

Cela vérifie chaque ligne de la colonne A pour voir si elle est vide ou non. La sortie est un vecteur de valeurs VRAI / FAUX dans la colonne C, avec VRAI pour toutes les lignes qui ne sont pas vides dans la colonne A.

Ajoutez un multiplicateur ROW (A: A) à la formule:

= ArrayFormula (ROW (A: A) * (A: A <> ""))

Cela prend les valeurs VRAI / FAUX, les convertit en 1 et 0 et multiplie par le numéro de ligne.

Il vous reste donc quelques valeurs de ROW et beaucoup de zéros.

Maintenant, les données ressemblent à ceci:

Il ne reste plus qu'à prendre le maximum de cet ensemble:

= ArrayFormula (MAX (ROW (A: A) * (A: A <> "")))

ce qui donne la réponse 10 dans cet exemple.

MATCH Alternative

Une autre alternative pour trouver la dernière ligne non vide est d'utiliser la fonction MATCH, comme ceci:

= ArrayFormula (MATCH (2,1 / (A: A <> ""), 1))

FILTRE Alternative

Encore une autre alternative pour trouver la dernière ligne non vide est d'utiliser la fonction FILTER, comme ceci:

= MAX (FILTRE (RANGÉE (A: A), A: A <> ""))

Ce qui a la belle propriété de ne pas avoir besoin d'être une formule matricielle.

RECHERCHE pour récupérer la valeur

Et pour récupérer la dernière valeur elle-même, pas le numéro de ligne, vous pouvez utiliser la fonction LOOKUP:

= ArrayFormula (LOOKUP (2,1 / (A: A <> ""), A: A))

La clé de ces deux formules est que la valeur de recherche "2" est plus grande que n'importe quelle valeur de la plage de recherche, qui consiste en des 1 ou des erreurs. Essayez de construire la formule en morceaux pour voir comment cela fonctionne, comme nous l'avons fait dans le premier exemple.

# 100. Utilisez la fonction Hyperlien pour créer un lien vers la ligne vide suivante

Nous utiliserons la formule de l'astuce précédente dans le cadre d'un lien hypertexte pour passer à la ligne vide suivante. Cela peut être utile si vous disposez de tables de données volumineuses que vous utilisez pour la saisie de données.

Étape 1

Cliquez avec le bouton droit sur la cellule A1 dans une feuille, sélectionnez l'option "Obtenir le lien vers cette cellule", puis collez l'URL dans une cellule vide quelque part dans votre feuille. Vous remarquerez que cela prend la forme:

https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXX/edit#gid=0&range=A1

où "XXXXXXXX" est votre ID de feuille de calcul unique.

Étape 2

Dans une cellule vide, créez un lien hypertexte avec ce lien vers A1, comme suit:

= HYPERLIEN ("https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXX/edit#gid=0&range=A1", "Ajouter des données")

n'oubliez pas d'échanger le "XXXXXXXX" pour votre ID de feuille de calcul unique.

Étape 3

Maintenant, utilisez la formule de la semaine dernière pour modifier la référence de plage de A1 à la ligne vide suivante.

Supprimez le "1" à la fin de la référence de plage (la partie "... plage = A1") et remplacez par une esperluette et la formule pour trouver le dernier numéro de ligne (la section verte dans la formule suivante):

= HYPERLINK ("https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXX/edit#gid=0&range=A" & ArrayFormula (MAX (ROW (A: A) * (A: A <> "")))) , "Ajouter des données")

Cliquez sur ce lien hypertexte pour accéder à la dernière entrée non vide de la colonne A.

Étape 4

Pour que le lien hypertexte vous amène à la ligne vide suivante, ajoutez simplement 1 à la formule de tableau:

= HYPERLINK ("https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXX/edit#gid=0&range=A" & ArrayFormula (MAX (ROW (A: A) * (A: A <> "")) + 1 ), "Ajouter des données")

Voici cette formule en action sur un plus grand ensemble de données:

Commentaires de clôture

Merci d'avoir téléchargé et lu cet ebook!

C'était très amusant à assembler, donc j'espère que vous l'avez apprécié et que vous l'avez trouvé utile.

Les corrections, commentaires ou suggestions sont encouragés. Laissez-moi savoir ce que vous pensez.

Vous avez des astuces ou des trucs de tableur bizarres et farfelus? Je travaille sur les 100 prochains conseils et je créditerai toute personne qui contribue.

N'oubliez pas, continuez à additionner, trier, filtrer, pivoter, cartographier et analyser, mais surtout continuez à vous amuser!

Merci!

Ben Collins

Mai 2020

Détails du contact

Inscrivez-vous ici pour recevoir l'e-mail des astuces Google Sheets:

https://www.benlcollins.com/google-sheets-tips/

Contactez moi ici:

https://www.benlcollins.com/contact/

Archives complètes des didacticiels Google Sheet ici:

https://www.benlcollins.com/

The Collins School of Data (cours de formation en ligne pour Google Sheets):

https://courses.benlcollins.com/courses

Twitter:

https://twitter.com/benlcollins

This blog post is actually just a Google Doc! Create your own blog with Google Docs, in less than a minute.