It’s a lifesaver for countless financial professionals, data analysts, and teachers. But it’s also one of those programs that virtually everyone in any role can benefit from learning.

A handful of shortcuts can go a long way in increasing your productivity (and enjoyment) while using Excel.

In this article, I’ll detail some of the many shortcuts that I have found helpful throughout my studies and career.

Excel Shortcuts We’ll Cover:

• How to Execute Shortcuts in Excel

• Shortcut to Create a Table

• Shortcut to Create a Pivot Table

• Shortcut to AutoFit Column Sizes

• Shortcut to Open Format Cells

• Shortcut to Center Contents of Cell

• Shortcut to Fill Color

• Shortcut to Fill Contents Down (or Right)

• Shortcut to Show or Hide Gridlines

• Shortcut to Show all Formulas

• Shortcuts for Navigation in Excel

• Shortcut to Open the AutoFilter Menu in Excel

• Shortcut to Create a Slicer in Excel

• Shortcut to Create Checkboxes in Excel

• Shortcut to Create Charts in Excel

• Other Notes

• More Shortcuts

• Got Sheet

Here’s a video walkthrough of everything we’ll cover in this article:

How to Execute Shortcuts in Excel

In Excel, the more you can learn to do without touching your mouse, the better. Often, by keeping your hands on your keyboard, you can save a lot of time in each project.

As such, virtually every command imaginable is available as a keyboard shortcut.

The basic ones like CTRL + S for save and CTRL + C for copy are present. But Excel goes a step further…

The real power comes from the alt shortcuts. By pressing sequences of keys that typically start with alt, almost all of the actions in the Ribbon become available.

By simply pressing the alt key, all of the shortcut sequences available from the current view become highlighted in yellow on the ribbon:

Incidentally, some of the shortcuts’ sequences are separated by commas, while at other times two letters appear next to each other. Their execution is the same. Simply press them in sequence.

Below, you’ll find some of my favorites, but keep in mind that you can easily view all the available shortcuts at any time by pressing alt and then continuing to press the appropriate keys for the corresponding tabs and actions.

Shortcut to Create a Table

Keyboard Shortcut: ctrl + t

Tables in Excel are often the preferred format to begin manipulating and visualizing data. As long as the active cell is inside your data range, pressing ctrl + t will pop up a dialog and automatically select the data range to be used for the table.

It is adjustable if Excel gets a column or row wrong, and you can also toggle table headers on or off from this initial box.

Shortcut to Create a Pivot Table

Keyboard shortcut: alt + n, v, t

Want to appear a lot smarter than you are? Learn the basics of pivot tables in ten minutes. Your coworkers will remain impressed for weeks.

To create a pivot table, simply click somewhere in your data range and press ALT + n, v, t.

Excel is smart enough to recognize the data range you likely want included even if it isn’t already formatted as a table.

A dialog box will pop up for you to confirm the data range and location for the pivot table.

Shortcut to AutoFit Column Sizes

Keyboard Shortcut: alt + h, o, i

Ever get tired of resizing your columns so that the text in the cells doesn’t clip or spill? You can always double click the column boundary headings. This resizes the column to fit the widest cell’s contents.

The keyboard shortcut is much more efficient and allows you to autofit multiple columns in one fell swoop. Simply click and drag the column boundaries to select any number of columns. Execute the alt + h, o, i shortcut and all the columns in your active range will autofit their width.

You can also autofit cells individually by selecting one or more active cells and performing the same shortcut.

Shortcut to Open Format Cells

Keyboard Shortcut: CTRL + 1 or alt + h, fm

Unless you are content with mediocrity, you’ll be formatting the cells in your spreadsheet at some point to create more readable, user-friendly content.

The simplest shortcut to open up the Format Cells window is CTRL + 1, although if you want to flex your dexterity, alt + h, fm will get you there as well.

Shortcut to Center Contents of Cell

Keyboard Shortcut: (horizontal) alt + h, a, c,

(vertical) alt + h, a, m

We have it easy compared to web developers. There seems a never-ending supply of articles and videos reminding developers how to center divs.

In Excel, we need only remember two quick shortcuts.

• For horizontal centering: alt + h, a, c

• For vertical centering: alt + h, a, m

There are other shortcuts for left, right, top and bottom alignment, but most of the time when we change the original alignment, it’s to center it.

Shortcut to Fill Color

Keyboard Shortcut: alt + h, h

For a quick highlight, it takes precious seconds to mouse up to the fill color icon. Pressing alt + h, h quickly toggles the color selection open.

Once it’s open, you can leave your mouse to the side and arrow down to your favorite color

Shortcut to Fill Contents Down (or Right)

Keyboard Shortcut: (down) CTRL + D, (right) CTRL + R

One of the most powerful features of Excel is the ability to drag formulas and functions down or across many cells, effectively reproducing a single calculation many times on different pieces of data.

By typing a formula in cell A8 in the image below, we can then highlight A8 and drag down to our heart’s content. Then, by pressing CTRL + D, the formula will be copied down into every highlighted cell.

By default, it will also preserve the relative reference of the cell. In other words, the next cell will contain the formula A7 + A8 and then A8 + A9, and so on.

Shortcut to Show or Hide Gridlines

Keyboard Shortcut: alt + w, vg

The mark of a real data analyst is not the quality of their reports, but the precision of their workbook. Gridlines have got to go. If you need lines, you can add borders.

Toggle off the gridlines with alt + w, vg.

And if you need those borders immediately, highlight your data range and press alt + h, b to open up the border menu. If you simply need all borders, alt + h, b, a will do the trick

Shortcut to Show all Formulas

Keyboard Shortcut: CTRL + ~

You’ll likely never lose control of a workbook.

But in the event you access one of your less rigorous colleague’s workbooks and need to see what functions they’ve Frankensteined together, press CTRL + ~ to display all the functions instead in the spreadsheet.

Shortcuts for Navigation in Excel

Keyboard Shortcuts: CTRL + arrow keys (and others)

Navigating the grid can be very fast with the keyboard. Pressing CTRL + the arrows, the home and the end buttons will warp you all over the active sheet.

Using the arrows and CTRL, you go to the last nonblank cell in the row or column.

Using CTRL + home or end, you go to the beginning and the end of the workbook, respectively. (When inside a table, home and end take you to the beginning and end of the table only.)

Shortcut to Open the AutoFilter Menu in Excel

Keyboard Shortcut: alt + down arrow

Another superpower in Excel is the ease with which we can filter and sort large pieces of data. To access the AutoFilter Menu quickly, press alt + down arrow while in the header for the column you’d like to filter.

Shortcut to Create a Slicer in Excel

Keyboard Shortcut: alt + n, sf

For an even more user-friendly method of sorting, you can insert a slicer directly onto the spreadsheet by pressing alt + n, sf.

Shortcut to Create Checkboxes in Excel

Keyboard Shortcut: alt + n, cb

If you’re anything like me, you’ll find a way to use checkboxes in almost every spreadsheet you create. They’re extremely useful for toggling selections on and off in a workbook, and as of June 2024, Excel has made them available in production Excel.

Press alt + n, cb to insert a checkbox in a cell.

Shortcut to Create Charts in Excel

Keyboard Shortcut: alt + n, r

There are a ton of chart types in Excel. To quickly open up the recommended charts dialog box, we can press alt + n, r.

Or, if we know we want a specific type of chart, there are multiple options to shortcut straight to them, like alt + n, C1, alt + n, N1, alt + n, SA and so on.

More Shortcuts

There are a zillion shortcuts available in Excel. If you want to check out the full list, you can find a current version maintained by Microsoft here.

Got Sheet

Come join my free newsletter, Got Sheet. I show people how to get good at spreadsheets every week.

You can find me over on YouTube as well.

Well, new is a bit of an overstatement. Excel has had proper tables for many, many years, and it's been a point of contention in the spreadsheet community.

In this article, I'll break down what exactly tables are, why they're important, and then see how Google Sheet's new tables stack up against Microsoft Excel's.

Here is a video walkthrough of everything we'll cover:

What's a Table?

A table is a way of structuring and formatting data in a spreadsheet. It groups together rows and columns of data so that they can be more easily filtered, grouped, and analyzed.

Many people would look at the following bit of data and wrongly assume that it's already in a table.

Data in Excel

This is merely well organized rows and columns of data in Excel. Each column is a separate category of information, that is ids, names, emails, job titles, and salaries.

Each row represents one entry of that data. So, you'd put your id, name, email, job title and salary going left to right in a row.

Simple, yes?

A table contains all the same data, but by formatting it as a table we can unlock a whole lot of additional functionality.

The first of which is the appearance itself. When we create a table, Excel immediately colors our data with a dark header row and bands of alternating colors.

Table in Excel

Sheets does the same thing, as we can see below.

Table in Google Sheets

So, a table is simply a way of managing and grouping data more easily. But it goes much further than just formatting, as we'll see.

Why are Tables Important?

Tables help reduce errors. When dealing with data, we are always making sure the data is clean and that we don't have errors in our formulas.

Tables help keep things orderly simply by being structured and formatted well. But as we'll see in the formula section in a moment, they also allow us to reduce errors in formulas by dynamically calculating things for us

How to Create a Table in Excel and Sheets

In Microsoft Excel, creating a table is as easy as clicking anywhere in the data range and pressing CTRL + T. Immediately, Excel will predict the data range for the table and ask you to confirm this.

Excel table data range

Alternatively, you can find the same create table option from the Insert Menu in the Ribbon at the top.

Excel insert menu

Over in Sheets, you'll need to either right click in a cell in the data range, or select the option from the Format menu to Convert to Table.

Convert to table options in Google Sheets

One caveat in Sheets: if you right click in a cell, you have to select the whole data range for it to convert to a table. Whereas, if you select Format - Convert to table from the menu, it is (like Excel) smart enough to predict the whole data range.

A small thing. But Excel takes the prize for ease of creation.

Convert to table in Google Sheets

⭐Winner: EXCEL

How to Format Tables in Excel and Sheets

As we saw initially, some formatting is done as soon as we create a table.

From here, though, both programs allow for further customization.

In Sheets, we can select the dropdown in the top left next to the table name to access a few options immediately. For the most part, we can simply change the alternating colors of the table.

Formatting options in Google Sheets

If we select Custom, it opens up the full alternating colors menu that is also accessible through the Format menu. This gives us more control over the colors, but it's all aesthetic.

Alternating colors menu in Google Sheets

Meanwhile in Excel, we have the same options with a few more toggle selections for styling. For instance, we can check the first column to bold the text in the id column or we toggle between banded columns and/or rows.

And on the far right of the Table Design tab in the Ribbon, there are a ton of prebuilt styles that we can toggle on and off.

Table design in Excel

Both programs give plenty of options here, and this is mostly to make the tables look good. But Excel comes out on top with more options.

⭐Winner: EXCEL

How to Sort Tables in Excel and Google Sheets

In both programs, there is a dropdown toggle button in each of the header row's cells. Selecting this in Excel allows us to sort ascending or descending...or even by color.

sort in excel

For instance, if we have some of our data using a blue font color, we can actually sort it by that color:

Sort by color in Excel

What about Google Sheets? Yep, same deal there. It will also detect when different colors are used and allow you to do the same type of sorting.

Sort by color in Sheets

Excel does have a Custom Sorting dialog box that can drill down into more detail. For instance, you can add levels of sorting.

Using our color example, we can first sort by the blue font colors in the email color and then by the red font colors in the job title column.

Multiple column sorting in Excel

Google Sheets can do the same thing, but not from the header drop downs. The header drop down sorting is restricted to one row at a time in Sheets.

But, if you select the entire table's data range and then Data - Sort Range - Advanced range sorting options, you are able to sort by multiple columns in Google Sheets.

Advanced sorting in Google Sheets

Sheets' advanced sorting is not as powerful as Excel's, though. You are only able to sort ascending or descending by value. Excel takes the cake on this one by a hair.

⭐Winner: EXCEL

How to Filter Tables in Excel and Google Sheets

Filtering works exactly the same as sorting. In both programs, click the dropdown selector in the header row to see the options for filtering.

In both programs, we have the same options. We can filter by color just like in our sorting. We can filter by values by either selecting all, none, or individual values. And we can filter by condition.

Here's Google Sheet's menu:

Filtering in Google Sheets

And here's Excel's menu. All the same options are available. Both programs allow for custom filter formulas to be entered as well.

Filtering in Excel

⭐Winner: TIE

How to Use Tables in Formulas in Excel and Google Sheets

One of the big reasons to use tables lies in formulas. Whether you use Excel or Sheets, you are likely taking advantage of built-in functions and the ability to create custom formulas for analyzing your data.

By holding your data in a table, your formulas can reference that data dynamically.

Meaning, if you add rows of data to your table, any formulas referencing those table values will update automatically.

The risk of breaking things by adding data decreases dramatically with the use of tables.

Here's a simple example. If we wanted to combine the first and last names into one cell, we could concatenate them with this formula =Salary[first_name]&" "&Salary[last_name].

In Excel, we reference a table by its name, in this case, Salary. Then within brackets, we reference a column name, [last_name].

Spill formula in Excel

We can do the exact same in Sheets.

Formula referencing in Sheets

There's one powerful difference, though. In Excel, the formula will spill down. Meaning, we only have to write it once at the very top, but because it sees that we're referencing values in a table, it will spill down to every row in the table.

In Google Sheets, we still have to drag the formula down.

Now, sometimes, we may not want things to spill down. In this case we can use different syntax in Excel. Instead of the column name within brackets, we can add an @ sign and another set of brackets. This tells Excel to only make the calculation on one row:

Formula referencing in Excel

Excel flexes on this one. It's much more powerful to use tables in formulas in Excel than in Sheets.

⭐Winner: EXCEL

How to Change Table Range in Excel and Google Sheets

What if we want to extend our table or remove data from it? Both Google Sheets and Excel allow us to do this easily.

Say we want to add a column for the full name of a person. In both programs, if we simply type in full_name in G1 to the right of our last column, that column will become a part of the table's data range.

Anytime we type in an adjacent column or row to our table data, the programs will assume the table needs to extend to include it.

Then, we can use a version of the formula from our previous example to insert the full names. Now that we are inside of the table, though, it's not necessary to include the title of the table in our formula.

Now, all that's needed in Excel is =[@[first_name]]&" "&[@[last_name]].

Reference table columns in Excel

For Google Sheets, it's the same inside the table as outside it: =Table2[first]&" "&Table2[last]. Sheets also requires us to drag the formula down. It does not handle spilling like Excel (yet).

Reference table columns in Sheets

To add columns within a table, we can right click the column name and select insert.

Insert column in Excel

Google has a slight edge here in that you can select whether to insert to the left or the right, whereby Excel only inserts to the left.

Insert column Google Sheets

Inserting rows is exactly the same. Excel allows for inserting rows above, while Sheets allows you to select above or below.

Inserting rows Google Sheets

In both programs, deleting rows and columns is as simple as selecting the row(s) or column(s), right clicking, and selecting delete.

In Excel you have the added benefit of a keyboard shortcut. CTRL + - will delete the selected rows or columns.

Both programs will also allow you to convert a table back to a regular data range. In Excel, it's the Convert to Range button in the Table Design tab of the menu

Convert to Range option in Excel

And in Google Sheets, it's the Revert to unformatted data option from the table dropdown menu.

Revert to unformatted data option in Sheets

⭐Winner: TIE

How to Add a Total Row in a Table

There's a good chance you'll want to total up the amounts in a column. How easy is this to add in a table?

You can do it in both programs, but...

Excel makes it incredibly easy. There's a toggle option for this in the Table Design menu in the Ribbon. Toggle this on, and a Total row is automatically included and calculated at the bottom.

Total row in Excel

Can you do the same in Sheets?

Yes, you've just got to do it yourself.

Total row in Sheets

⭐Winner: EXCEL

Who Wins?

Well, it's no surprise that Excel comes out on top. Sheets users (myself included) have a lot to be excited about with the ability to finally create proper tables. By and large, the functionality is just as powerful as Excel.

And much like many features compared between the two programs, Sheets can probably get the job done for most use cases.

Excel, as per normal, simply does more and does it a little bit better.

I'm Eamonn, and I'll help you get good at spreadsheets. Join my free newsletter, Got Sheet, here.

If you want to follow along with the sheet I used, you can access it here. The link will prompt you to create a copy of the spreadsheet and accompanying Apps Script file.

What is an Image Lightbox?

image thumbnails in a spreadsheet

An image lightbox is what we call it when we hover over or click on an image and it pops up into a bigger version on-screen.

It’s something we’re used to seeing on websites, and it gives things a nice, professional touch when done well.

What about in a spreadsheet, though? 🤔

Well, we’ve got two versions of a solution:

1. Using built-in functions to display a larger version in a larger cell.
2. Using Apps Script to actually create a popup box on top of our spreadsheet.

As a bonus to the first solution, we’ll also include optional Apps Script to make things a little smoother. More on that below 😉.

As usual, here's a walkthrough video where I go through the whole process:

Image Popup With Built-In Functions

First, we need images in cells. From the top menu, Insert - Image - Insert image in cell will do the trick for this.

inserting image in spreadsheet cell

Next, we need to merge some cells together so that there’s a larger container that will hold our larger picture after the next step.

You can use one cell and change the width and height of it, but in my example sheet, the “lightbox” area is sharing rows with the rest of the data so I didn’t want to do that.

a large range of merged cells

In the column next to my image thumbnails, I’ve put checkboxes by selecting Data - Data validation - Criteria: Checkboxes from the top menu.

This will let us select which image to popup in our lightbox area.

data validation in google sheets

I’ve named the range A2:A11 as pics and the range B2:B11 as checkboxes to allow for easier readability in the function we’ll write next.

named ranges in google sheets

Now all that remains is one XLOOKUP() function to put inside our lightbox range.

=XLOOKUP(TRUE,checkboxes,pics,"") is the function that searches for a check and then displays the corresponding image. By putting this in a big cell or range of merged cells, we can display whichever small image we select in the bigger area.

xlookup function in google sheets

Remember, all a checkbox is doing is storing either a TRUE (checked) or a FALSE (unchecked) value.

checkboxes and image thumbnails in google sheets

⚠️WARNING⚠️

This does have one issue, though. Do you know what it is?

XLOOKUP() is going to return whichever checkboxes it comes to first with a TRUE value. So if you have multiple images checked, it’s only going to display the first one it gets to, not the most recently clicked one.

To get around this, let’s write some code.

Apps Script Improvement

Open up Apps Script by selecting Extensions - Apps Script from the top menu.

opening apps script in google sheets

Delete the built-in function in the code editor that opens. We'll start from scratch with an onEdit function:

function onEdit(e) {

We need to grab the range that we are currently editing.

var range = e.range

Then the checkboxes range.

var checkboxes = SpreadsheetApp.getActive().getRangeByName("checkboxes")

Then we need to check whether what we just edited is in that checkbox range.

if (range.getColumn() == 2 && range.getRow() >= 2 && range.getRow() <= 10)

If it was a checkbox, then we want to uncheck all the checkboxes and re-check the one we just checked.

// Uncheck all other checkboxes in the range
checkboxes.uncheck();
// Check the edited cell
range.check();

Now, there is a slight delay when you run the code. After clicking a checkbox, all of them are cleared right before the one you checked gets checked again.

Here’s what the full code looks like:

function onEdit(e) {
  var range = e.range;
  var checkboxes = SpreadsheetApp.getActive().getRangeByName("checkboxes")

  // Check if the edited cell is a checkbox in the desired range
  if (range.getColumn() == 2 && range.getRow() >= 2 && range.getRow() <= 10) {
    // Uncheck all other checkboxes in the range
    checkboxes.uncheck();
    // Check the edited cell
    range.check();
  }
}

A Real Popup Box with HTML

modal dialog box in google sheets

Okay, that’s all fine and dandy. What about the real thing, though?

This takes more Apps Script, but it’s doable thanks to the built-in howModalDialog method.

This is basically a popup window that can hold HTML. And since the internet is built with HTML, all we need to do is use a little bit to plug in an image.

This method requires an image live on the internet somewhere. So, we cannot reference the image that we've embedded in our sheet and use it in the HTML we're going to write.

Weird, I know.

Let’s find an image URL we can use. I’ve grabbed an eagle off of unsplash.

We’ll hold this in a variable.

var imageURL = "https://images.unsplash.com/photo-1715002383611-63488b956401?q=80&w=1887&auto=format&fit=crop&ixlib=rb-4.0.3&ixid=M3wxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8fA%3D%3D"

Then we need to build our HTML. In our case, all we want is one element, so we won’t worry ourself with constructing a full, semantically correct page (although we certainly could 😉)

Another variable will hold this img element:

var html = '<img src="' + imageURL + '" style="max-width: 100%; max-height: 100%;">';

We have access to Class Ui in Apps Script where we can “…add features like menus, dialogs, and sidebars.”

  var ui = SpreadsheetApp.getUi();

And finally, by calling the showModalDialog() method, we can generate HTML from our html variable using the Class HtmlService.

ui.showModalDialog(HtmlService.createHtmlOutput(html).setWidth(700).setHeight(1000), 'Eagle 🦅');

A final touch is to add a thumbnail version of our eagle image into our spreadsheet so that it is inserted on top of our cells (this next bit won’t work if it’s embedded in a cell itself).

Once it’s in our sheet, we can click the three black dots in the top right corner and assign a script directly to the image.

assigning a script to image in google sheets

We named our script displayImagePopup, so this is what we enter. Make sure to leave off the parentheses when typing it into the image's script form.

assigning script

Now, anytime we click the small image of the eagle, a popup box opens with the full image.

Here’s what the full code looks like:

function displayImagePopup() {
  // Get the active sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  var imageURL = "https://images.unsplash.com/photo-1715002383611-63488b956401?q=80&w=1887&auto=format&fit=crop&ixlib=rb-4.0.3&ixid=M3wxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8fA%3D%3D"
  // Create an HTML string for the popup
  var html = '<img src="' + imageURL + '" style="max-width: 100%; max-height: 100%;">';

  // Show the dialog
  var ui = SpreadsheetApp.getUi();
  ui.showModalDialog(HtmlService.createHtmlOutput(html).setWidth(700).setHeight(1000), 'Eagle 🦅');
}

Thank you so much!

I hope this was helpful for you.

Check out my YouTube channel and free newsletter to get good at spreadsheets!

Creating a grid of alphabets helps illustrate this practice. If the first letter in your text is A and the first letter in your key is L, you would find the encrypted letter, L, where the plaintext and key intersect:

screenshot of a grid of alphabets

So your text to encrypt might look like this: attackatdawn, your key might be this: LEMONLEMONLE, and the ciphertext would be this: LXFOPVEFRNHR.

This is the same example used on the Wikipedia page which I'll walk you through in this article.

You could create a cipher encoder/decoder with any number of programming languages. But spreadsheets are a nice tool to use for this kind of project because they come with some pretty robust built-in functions that will help us dissect the cipher.

I've chosen Google Sheets because it's a hair simpler to jump into, especially if you already have a Gmail account. But you can do all the same things in Microsoft Excel.

Here's the Google Sheet I used.

If you'd like to check out a video walkthrough, here you go 👇

Spreadsheet Preparation

This isn't too complicated. We need a grid of alphabets. No, you don't have to type them in over and over, either.

In fact, you don't even have to type the whole first alphabet. Let's use Unicode characters. For capital letters, the Unicode for the alphabet starts with 65.

The CHAR() function in spreadsheets converts a number into a character according to the current Unicode table.

The COLUMN() function returns a number corresponding to the current column where column A returns 1, column B returns 2, and so on.

So if we start our alphabet in column B, we can access each letter by adding 63 to the COLUMN() function and wrapping it in the CHAR() function:

=CHAR(COLUMN()+63)

Then we can drag this over to the right until we've got the full alphabet.

screenshot of CHAR and COLUMN functions

The same thing applies going down. We'll just use the ROW() function instead:

=CHAR(ROW()+63)

screenshot of CHAR and ROW functions

Then we can reference the cell above and to the right for every cell below to fill out the full alphabet grid:

screenshot of spreadsheet cell

The only one that will be different will be in the last column where we'll need to reference the first column to have the alphabet wrap:

screenshot of spreadsheet cell

We only have to do this on the second line. Then, select the full second row and drag the formulas down to fill out the grid:

screenshot of spreadsheet grid

At this point, you can copy CTRL + C and paste values only CTRL + SHIFT + V for all the alphabet. If you add or shift rows, the alphabets may get skewed since their formulas reference fixed columns.

The Game Plan

Now the fun stuff. We want our text to be encrypted automatically. Let's highlight three cells: one for the text to encrypt, one for a key, and then a blank cell for our encrypted text:

screenshot of text to encrypt

Note: the key needs to be the same length as the text to encrypt/decrypt. In this example, the key is the word LEMON, but we extend it by repeating it for every character we need to encrypt.

Now we need to do three things:

1. Split out each character of our text into its own cell
2. Loop through each character and encrypt it based on the corresponding key character.
3. Join all those characters together and place the result in our encrypted text cell.

Split Text to Setup

Interestingly, we cannot use the SPLIT() - or if you're in Excel, the TEXTSPLIT() - function without a delimiter. That is, we can't just tell it to split each character without there being something in between the characters.

So, we have to get fancy. In the video walkthrough, I explore a Google Sheets-specific approach using regular expressions...which are hard fun.

Here's what it looks like, and it involves creating character groups, inserting something in between each one, and then splitting using that same something as the delimiter. I used a blank space as the thing which I inserted and then split by:

=SPLIT(REGEXREPLACE(AG6,"(.)","$1 ")," ")

gif of woman saying, that's just hard

Excel users and mortals, fear not – there's a more elegant way that I'll show you here that can be done in either program by using the MID() and COLUMNS() functions.

The MID() function extracts a segment of a string, and COLUMNS() returns the number of columns in an array. By nesting COLUMNS() inside MID() we can one by one extract each character from the string:

=MID($AG$6,COLUMNS($AG6:AG6),1)

screenshot of MID and COLUMNS functions

By locking $AG6 as the first part of the array referenced in the COLUMNS() function, this number adds one for every column we drag our formula over.

Drag this over until you reach the end of the text to encrypt. Every character should be in its own cell now. Do the same for the key.

screenshot of spreadsheet cells

How to Use XLOOKUP() to Encrypt

Below each pairing of plaintext and key text, we will now do a double XLOOKUP().

Yes, it's as awesome as it sounds.

Here's what it looks like, and let's walkthrough what's happening:

=XLOOKUP(AD9,$B$2:$B$27,XLOOKUP(AD10,$B$2:$AA$2,$B$2:$AA$27))

screenshot of double XLOOKUP functions

For the beginning XLOOKUP(), we are looking up the plaintext and we're referencing the first plaintext alphabet for our lookup range. But then for our result range, we're opening up another XLOOKUP()...

This is because we still have to use the correct key alphabet to return an encrypted value.

So, we need the second XLOOKUP() to return a full alphabet based on the position of the key character.

We're using the key character as our lookup value, we're using the first alphabet along the top key row for our lookup range, and then we're returning the whole grid as our result range. This will let us return a full alphabet which is in turn referenced as the result range for our first XLOOKUP().

screenshot of alphabet grid and XLOOKUP example

You can see the second XLOOKUP() by itself in the picture above. The formula is in the top left. We're using the first letter of key text as the lookup value. The lookup range is the top alphabet in the purple box. The result range is the whole grid in the blue box. And the returned alphabet is the one that starts with L in the red box.

You can see it being returned underneath the formula where there is an arrow in the picture.

Drag the double XLOOKUP() function over to encrypt every character of the plaintext with the key text.

Join Text for Final Solution

Now that we've encrypted each letter, we want them to be joined into one string and returned in our encrypted text cell.

To do this, we can use the JOIN() function - or TEXTJOIN() for Excel - with an empty quote as the delimiter.

=JOIN("",AD11:AO11)

screenshot of JOIN function

And voilà! We've just encrypted a string using a double XLOOKUP().

How to Decrypt Text

Decryption works exactly the same. The only difference is the placement of the second XLOOKUP(). Instead of using it as the result range, we're using it as the lookup range:

=XLOOKUP(AD20,XLOOKUP(AD21,$B$2:$AA$2,$B$2:$AA$27),$B$2:$B$27)

screenshot of decryption double XLOOKUP example

Thanks for reading

I hope this was helpful for you and that you learned something new!

You can find more of my tutorials on YouTube, and please sign up for my newsletter about coding and spreadsheets here.

In this article, I'll walk you through creating an FAQ section with some expandable accordion elements.

I'll show you how to do this without any JavaScript, and then we'll add a bit of JavaScript to make it even better.

I've also made a video tutorial of the whole process here:

How to Make an Accordion Using

HTML has a disclosure element called <details> that can be in one of two states: open and closed. When opened, the information within the element is displayed. When closed, only the <summary> information is displayed.

This is an extremely easy version of the "accordion", although it's arguably not a true accordion by itself. When using <details>, more than one of the panels may be open at any one time – and because there's no JavaScript yet, panels will remain open until you click them again to close them.

Still, this is a quick and easy way to get an accordion-like element up and running. If you only need a few and aren't picky about functionality, this may be all you're looking for.

Here's what a basic example looks like. The <summary> is visible until clicked, at which point the rest of the content is displayed below it.

<!-- With just <details> -->
<section>
    <h2>Accordion using details</h2>
    <details open>
        <summary>Who is Eamonn?</summary>
        A guy from TN who makes content on the internet.
    </details>
    <details>
        <summary>What kind of content does he make?</summary>
        He focuses on productivity tips using coding and spreadsheets. He makes <a href="https://youtube.com/@eamonncottrell">YouTube</a> videos and writes articles on freeCodeCamp, <a href="https://www.linkedin.com/in/eamonncottrell/">LinkedIn</a> and his <a href="https://got-sheet.beehiiv.com/">personal newsletter</a>.
    </details>
    <details>
        <summary>What does he do for fun?</summary>
        Hangs out with his wife and four kids, and runs ultramarathons.
    </details>
</section>

We can also have the first details panel open by default by simply including the open command: <details open>.

Here's what that will look like with only a sprinkling of CSS:

screenshot of HTML accordion made with <details> only

This is a great start, but we can go further. A little bit of JavaScript will go a long way👇

How to Add JavaScript to an Accordion Element

Let's make another section with three more <details> elements. And let's add a class = "withJS" to each one so we can do some different things to them for comparison's sake.

<!-- With JavaScript -->
<section>
    <h2>Accordion with some JavaScript added</h2>
    <details  open>
        <summary class="withJS">What's the difference?</summary>
        We're adding JavaScript to these three.
    </details>
    <details >
        <summary class="withJS">Why add JavaScript?</summary>
        We can make it so only one panel can be open at a time.
    </details>
    <details >
        <summary class="withJS">Try clicking each of these</summary>
        See how one closes as soon as the other opens?.
    </details>
</section>

To keep things tidy and in one file, we can add a <script> tag at the bottom of our <body>.

First, select all of the summary elements with the .withJS class by using document.querySelectorAll():

const summaries = document.querySelectorAll(".withJS")

Then add a click event listener to each of them:

summaries.forEach(e=>{
    e.addEventListener('click',openCloseDetails)
})

This will run the function openCloseDetails every time one of these summary elements is clicked.

It won't do anything except give us an error until we declare that function, though...so, let's do that next.

Within the <script> after the forEach loop, let's have the openCloseDetails() function loop through those summaries again. This time, we want to modify the open status on the <details> element.

Remember how we have this setup: The first <details> element is set to open by default, and the others are closed.

We need a way to toggle the clicked element from open to closed, and close any previously open element when we click a new one.

To do this, we'll set a variable for the <details> element of each of the <summary> elements by setting it equal to e.parentNode in the forEach loop.

let details = e.parentNode;

The parentNode is the element directly preceding the current element. Since the <summary> elements are within the <details> elements, the parentNode for the <summary> elements will be the <details>.

gif of woman saying, "clearly"

From there, we check if that <details> is NOT this.parentNode. If it's not, then we'll remove the open attribute.

The native functionality of the <details> elements will open the clicked one, we just needed to make sure all the other ones close.

Here's the code. It's not complicated, but it may take a second to wrap your head around the logic:

summaries.forEach(e =>{
    let details = e.parentNode;
    if(details != this.parentNode){
        details.removeAttribute('open')
    }

And that's it. Now when we click each <details> the other ones automatically close:

gif of html accordion in action

Here is the entire HTML file for your reference:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Accordion</title>
</head>
<body>
    <style>
        body{
            background: rgb(255, 255, 230);
            color: #444;
        }
        details{
            font-family:'Trebuchet MS', 'Lucida Sans Unicode', 'Lucida Grande', 'Lucida Sans', Arial, sans-serif;
            margin-bottom: 5px;
            padding: 0.5em 0.5em 0;
        }
        details[open]{
            padding: 0.5em;
        }
        summary{
            font-weight: bold;
            margin: -0.5em -0.5em 0;
            padding: 0.5em;
            cursor: pointer;
        }
        details[open] summary{
            margin-bottom: 0.5em;
        }
    </style>

    <h1>Accordions</h1>

    <!-- With just <details> -->

    <section>
        <h2>Accordion using details</h2>
        <details open>
            <summary>Who is Eamonn?</summary>
            A guy from TN who makes content on the internet.
        </details>
        <details>
            <summary>What kind of content does he make?</summary>
            He focuses on productivity tips using coding and spreadsheets. He makes <a href="https://youtube.com/@eamonncottrell">YouTube</a> videos and writes articles on freeCodeCamp, <a href="https://www.linkedin.com/in/eamonncottrell/">LinkedIn</a> and his <a href="https://got-sheet.beehiiv.com/">personal newsletter</a>.
        </details>
        <details>
            <summary>What does he do for fun?</summary>
            Hangs out with his wife and four kids, and runs ultramarathons.
        </details>
    </section>


    <!-- With JavaScript -->
    <section>
        <h2>Accordion with some JavaScript added</h2>
        <details  open>
            <summary class="withJS">What's the difference?</summary>
            We're adding JavaScript to these three.
        </details>
        <details >
            <summary class="withJS">Why add JavaScript?</summary>
            We can make it so only one panel can be open at a time.
        </details>
        <details >
            <summary class="withJS">Try clicking each of these</summary>
            See how one closes as soon as the other opens?.
        </details>
    </section>

    <script>
        const summaries = document.querySelectorAll(`.withJS`)
        summaries.forEach(e=>{
            e.addEventListener('click',openCloseDetails)
        })

        function openCloseDetails(){
            summaries.forEach(e =>{
                let details = e.parentNode;
                if(details != this.parentNode){
                details.removeAttribute('open')
            }
            })
        }
    </script>
</body>
</html>

Thanks for Reading

I hope this was useful for you!

Come follow me on YouTube: https://www.youtube.com/@eamonncottrell

And sign up for my coding & spreadsheet newsletter: https://got-sheet.beehiiv.com/

Have a great one!

You'd be surprised how many different ways there are to do things in Excel. Below, I'll show you 9 ways to add two or more numbers. You can skip to a certain section if you'd like to see that method:

1. Manual
2. References
3. SUM()
4. SUMIF()
5. SUBTOTAL()
6. AGGREGATE()
7. VBA
8. Python
9. Highlight

Video Walkthrough

If you prefer to watch me go through each of these in a demo workbook, here's a video for that:

To write a formula or a function in Excel (which we'll be doing in the examples below), start out by simply typing an equals sign in a cell.

=

This triggers Excel to know that what follows will be a formula or a built-in function.

Manual

This is simplest version of a formula in Excel. Start out with the equals sign, and then type in the numbers and operation you want to do. Just like a calculator:

=6+102

Pressing enter will result in 108 being listed in the cell

screenshot of manual addition in Excel

References

The next step up in Excel is to start using cell references. Notice that on the top and on the left side of the main spreadsheet area, there are columns designated by letters and rows designated by numbers.

screenshot of rows and columns

We can refer to specific cells using "A1" notation. Like a set of (x,y) coordinates on a graph, this simply means that by referring to C4, for instance, we are referring to the cell found in column C, row 4.

To add numbers using references, we start again with the equals sign and refer to the values in specific cells directly.

This has the added advantage of being dynamic. If a value is changed in one of the cells, the result of the sum automatically updates.

=SUM(A1+A3) provides us with the value of the numbers in A1 and A3.

SUM()

The first two methods are examples of using formulas. We manually give Excel a series of instructions that it executes.

Excel also has built-in functions which we can use by starting with the equals sign and then referring to the function by name. Functions also take variables which we pass to them by using a set of parenthesis after the name of the function.

The SUM() function takes either a range or a comma-separated list of cell references. It then returns the sum of all the numbers in the range.

You can select a range by either clicking and dragging or by declaring one by typing in the A1 notation with a colon in between the top left cell and the bottom right cell of the range.

SUM(A5:A11) adds all the numbers in cells A5, A6, A7, A8, A9, A10, A11

screenshot of the SUM() function

SUMIF()

A more powerful version of SUM() is the SUMIF() function. This adds conditional logic. It needs at least two variables: a range and a condition. We could give it the same range as above and have a condition be that it only adds up numbers that are greater than zero.

A third, optional variable is a sum_range. This allows for us to match a condition in one range with the sum of values in another range.

In the example sheet, I have inserted checkboxes in column C. Checkboxes in Excel are a new feature. This is the range that I'm checking for a condition. The condition is TRUE. Now I enter the range that I want to sum if the condition in the corresponding row of the first range is indeed TRUE.

When using a range and a sum_range separately like this, they do have to be of the same size or it will not behave as you want it to.

screenshot of SUMIF() function in Excel

SUBTOTAL()

Ok, here's where things start to get interesting.

The SUBTOTAL() function allows us to do a ton of different things. Ultimately, it returns a subtotal of a list or database. But inside of SUBTOTAL() there are other functions. The first argument that we give SUBTOTAL() is a number corresponding to one of these functions:

screenshot of functions within SUBOTAL from Microsoft Excel

Looking at the function list, we see that 9 or 109 both correspond to the SUM() function which we want to use. If we have hidden rows in our range that we don't want to include in the sum, we use 109 to ignore those – if not, simply 9.

So the function looks like this: SUBTOTAL(9,B3:B12). This sums B3:B12 even if one or more of those rows are hidden.

screenshot of SUBTOTAL in Excel

AGGREGATE()

We can think of AGGREGATE() as a souped-up version of SUBTOTAL(). It works in the same way but has a lot more built-in functions (19 of them) and allows for detailed specificity on what values, if any, to ignore in the calculation.

AGGREGATE(function_num, options, ref1, [ref2], …) is the full reference formula. Again, we pass it a number corresponding to one of the 19 built-in functions, then an optional argument for what type of values to ignore, followed by the reference array and an optional second reference array.

screenshot of options for Aggregate function from Microsoft Excel

For our example, we again use 9 as our function number, but we can use option 5 to explicitly exclude hidden rows:

screenshot of Aggregate function in Excel

VBA

Now we're warmed up. Let's get overly complicated.

gif of complicated nonsense

Visual Basic for Applications is Microsoft's baked-in programming language in Microsoft Office applications.

Open it up by selecting Visual Basic from the Developer tab.

screenshot of VBA in Developer tab in Excel

If you don't see the developer tab, go to File - Options - Customize Ribbon and add it.

screenshot of Customizing Ribbon in Excel

Also, Alt +11 is the keyboard shortcut to open up VBA.

Once here, we can write code to do all sorts of things. Our example isn't very practical since a function will do it quicker, but the following code will Sum the range A1:A11, put the result in F11 and display a message pop-up with the result:

Sub AssignSumVariable()
   Dim result As Double
   'Assign the variable
   result = WorksheetFunction.Sum(Range("B1:B11"))
   'Show the result
   MsgBox "The total of the ranges is " & result
   'Put the result in cell F9
  Range("F9") = result
End Sub

screenshot of VBA in Excel

Python

Yes, this is now ridiculous. But, it's good to know what Excel can do when you have more complicated tasks that require tools like VBA or Python. At the time of this writing, Python is available in Excel for people using the Beta Channel of Excel.

gif of woman saying "seems like overkill"

You can check your eligibility and join Microsoft 365 Insider if you want to test out new features like this in the future.

Go to File - Account, and then select the 365 Insider channel button for more info.

screenshot of Microsoft 365 Insider options

Once Python is usable in Excel, you activate it by typing =py and then the tab key. This turns the cell into a Python command line.

screenshot of Python command line in Excel

From here, we can write Python code directly in the cell. The following code uses the custom xl() function for Python to use a range. We hold the range in the numbers variable and then using dot notation, we sum that range with the numbers.sum() line:

numbers = xl("'Sum➕'!$B$3:$B$12")
numbers.sum()

Now to execute the Python code, click CTRL + ENTER.

What we now see is that we've got a Python Series in the cell:

In order to just display the answer, we can click the Python Output selector just to the left of the formula bar and select Excel Value:

screenshot of Python Output in Excel

Now, our cell is updated with the correct value.

screenshot of cells in Excel

The real value of Python in Excel comes with manipulating dataframes using built-in libraries like Matplotlib, NumPy, or Pandas.

Okay, take a breath, we'll finish with something simple and easy...👇

Highlight

Bonus time. If you highlight cells in Excel by either clicking and dragging mouse over a range, or by CTRL+ Left-Click individual cells, some automatic calculations are visible in the bottom right of the window, including the Average, Count and Sum:

If the sum isn't immediately visible, right clicking will pull up auto-calculations that you can toggle on and off:

screenshot of auto-calculation options in Excel

Thanks for reading!

Hope this is helpful for you!

Follow me on LinkedIn: https://www.linkedin.com/in/eamonncottrell/

And YouTube: https://www.youtube.com/@eamonncottrell

Have a great one! 👋

In this article, we'll create our own dark mode. One way to do that would be by selecting all the cells and manually changing their background color and font color. This will get the job done, but we can automate the process and add more style options to choose from.

gif of cartoon character grabbing a computer

Here’s what we’ll build: a style selector that has four different styles triggered by either a new dropdown menu or by clicking a custom button icon.

screenshot of Google Sheets with different background modes

And here's the video walkthrough if you'd like to follow along with that instead:

How to Create Dark Mode in Apps Script

We’ll create four functions, one for each style mode. Each of our functions will do the following:

1. Set background color
2. Set font color
3. Set font family
4. Set border color and style

Let’s walk through how to create a darkMode function in Apps Script. We'll define each function with the function keyword, followed by whatever we’ll name it.

Because the functions take no arguments (they just run without needing more info from us). That is, we have open and closed parentheses with nothing inside them followed by an open curly brace.

All of our code for the function goes between the darkMode function's curly braces:

function darkMode() {
  SpreadsheetApp.getActive().getRange('A1:Z')
    .setBackground("#333333")
    .setFontColor("white")
    .setFontFamily("Google Sans")
    .setBorder(false,false,false,false,true,
     true,"#444444",SpreadsheetApp.BorderStyle.SOLID)
}

To select all the cells, we used the built-in methods from the SpreadsheetApp class: getActive() and getRange(). These select the active sheet as well as a given range.

In our case, we’ll plug in A1:Z as the range, but you can extend this further if you’d like. For instance, A1:AZ, would add columns AA:AZ and then apply our styling to them.

The four lines that follow are simply dot notation extensions telling what styles to apply. You can write this on one line if you’d like, but it’s good practice to have line breaks to make the code easy to read.

gif of man saying, that’s clever

How to Set Colors and Fonts

You'd notice that we used both setBackground(#333333) and setFontColor("white") in the code. This is because we can use CSS notation colors in either hex format or by using the color’s name.

Using setFontFamily("Google Sans"), we gave it the font family name within quotations. Being a Google product, you can use any of the Google Fonts as well as Google’s own Google Sans font as I found out making this project.

How to set the Border

The setBorder(false,false,false,false,true, true,"#444444",SpreadsheetApp.BorderStyle.SOLID) function let’s you enter true or false values for the top, left, bottom, right, vertical, or horizontal borders, in that order, followed by the color and style.

To set the style, we had to invoke a built in Enum Attribute — BorderStyle — to change the style of the border.

How to Create the Style Menu

To be able to select any of the styles we’re making from the actual spreadsheet, we need a menu.

To add the menu, we'll create a new function called onOpen() that runs as soon as the spreadsheet opens and then the built-in methods from the getUi() to build our custom menu.

We can create the menu with .createMenu() and then add each of our functions to the menu with the addItem() function.

Here's the code:

function onOpen(){
  SpreadsheetApp.getUi()
    .createMenu('Style')
    .addItem("Dark","darkMode")
    .addItem("Papyrus","papyrusMode")
    .addItem("Light","lightMode")
    .addItem("Synth","synthMode")
    .addToUi();
}

Google Apps Script automatically integrates with Google Workspace apps (like Google Sheets) so the functions we've added in the code will make the functionalities accessible in your Google Sheets.

How to Add Icon Buttons

As a bonus, I added four icon images to the sheet, and by selecting Assign script we can have these icons act as buttons to trigger any of the four styles we’ve built:

screenshot of icons in spreadsheet

When you type the function name into the Assign script dialog box, make sure you do not to use parentheses. You should only enter the name of the function itself:

screenshot of assigning script to image

And bingo! We’ve got ourselves a style picker. You can add or edit these to create any number of combinations that you can easily toggle on and off in your own Google Sheet!

screenshot of Synth Mode Style

Conclusion

This article shows how you can create can code dark, and other background modes for your Google Sheets using Apps Script and JavaScript.

I hope this has been useful for you!

More Resources😄

▶ Find all my video tutorials and walkthroughs on YouTube: https://www.youtube.com/@eamonncottrell?sub_confirmation=1

▶ Connect with me on LinkedIn where I share daily tips: https://www.linkedin.com/in/eamonncottrell/

Google Sheets has five built-in functions that help you import data from other sheets and other web pages. We'll walk through all of them in order from easiest (most limited) to hardest (most powerful).

Here they are, and you can click each function to skip down to its dedicated section. I've made a video as well that walks through everything:

Section Shortcuts

• How to use the IMPORTRANGE function
• How to use the IMPORTDATA function
• How to use the IMPORTFEED function
• How to use the IMPORTHTML function
• How to use the IMPORTXML function

Here's the Google Sheet we'll be using to demo each function.

If you'd like to edit it, make a copy by selecting File - Make a copy when you open it.

screenshot of Google Sheet

How to use the IMPORTRANGE function

This is the only function that imports a range from another sheet rather than data from another web page. So, if you've got another Google Sheet, you can link the two sheets together and import the data you need from one sheet into the other sheet.

For instance, here's a sheet with a bunch of random Samsung Galaxy data in it.

You can see that we have a few hundred rows of data about phones. If we want to import this data into another spreadsheet, we can use IMPORTRANGE(). This is the simplest to use of the five functions we'll look at. All it needs is a URL for a Google Sheet and the range we want to import.

Check out the tab for IMPORTRANGE in the Google Sheet here, and you'll see that in cell A5, we've got the function =IMPORTRANGE(B4,"data!a1:K"). This is pulling in the range A1:K from the data tab of our second spreadsheet whose URL is in cell B4.

screenshot of IMPORTRANGE function

Once your data is pulled into your spreadsheet, you can do one of two things.

1. Leave it linked through the IMPORTRANGE function. This way, if your data source is going to be updated, you'll pull in the updated data.
2. Copy and CTRL+SHIFT+V to paste values only. This way, you have the raw data in your new spreadsheet and you won't have to be dependent on something changing with the URL down the road.

How to use the IMPORTDATA function

This is pretty straightforward. It'll import .csv or .tsv data from anywhere on the internet. These stand for Comma Separated Values and Tab Separated Values.

.csv is the most commonly used file type for financial data that needs to be imported into spreadsheets and other programs.

Like IMPORTRANGE, we only need a couple pieces of information for IMPORTDATA: the URL where the file lives, and the delimiter. There's also an optional variable for locale, but I found that it was unnecessary.

In fact, Google Sheets is pretty smart – you can leave off the delimiter too, and it will usually decipher what type of data (.csv or .tsv) lives at the URL.

You can see that I've found a New York government data website where there lives some winning lottery number data. I've put the URL for a .csv file in A5, and then used the function =IMPORTDATA(A5,",") to pull in the data from the .csv file.

Screenshot of IMPORTDATA function

You could alternatively download the .csv file and then select File - Import to bring in this data. But in the event that you do not have download permissions or simply want to get it straight from a site, IMPORTDATA works great.

How to use the IMPORTFEED function

This imports RSS feed data. If you're familiar with podcasting, you may recognize the term. Every podcast has an RSS feed which is a structured file full of XML data.

Using the URL for the RSS feed, IMPORTFEED will pull in data about a podcast, news article, or blog from its RSS information.

This is the first function that begins to have a few more options at its disposal, too.

All that's required is the URL of a feed, and it'll bring in data from that feed. However, we can specify a few other parameters if we like. The options include:

1. [query]: this specifies which pieces of data to pull from the feed. We can select from options like "feed " where type can be title, description, author or URL. Same deal with "items " where type can be title, summary, URL or created.
2. [headers]: this will either bring in headers (TRUE) or not (FALSE)
3. [num_items]: this will specify how many items to return when using Query. (The docs state that if this isn't specified, all items currently published are returned, but I did not find this to be the case. I had to specify a larger number to get back more than a dozen or so).

You can see from the screenshots below that I am querying one of my feeds to pull in the episode titles and URLs.

First, to get all the titles, I used IMPORTFEED(A3, "items title", TRUE, 50:

Screenshot of IMPORTFEED

Then, similarly for the URLs, I used IMPORTFEED(A3, "items url", TRUE, 50):

Screenshot of IMPORTFEED #2

How to use the IMPORTHTML function

Now we're getting into scraping data straight off of a web site. This will take a URL and then a query parameter where we specify to look for either a "table" or a "list".

This is followed by an index value representing which table or list to look for if there are multiple on the page. It is zero indexed, so input zero if you're looking for the first one.

IMPORTHTML looks through the HTML code on a website for <table> and <list> HTML elements.

<!--Here's what a simple table looks like:-->
<table>
    <thead>
        <tr>
            <th>table header 1</th>
            <th>table header 2</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>table data row 1 cell1</td>
            <td>table data row 1 cell2</td>
        </tr>
        <tr>
            <td>table data row 2 cell1</td>
            <td>table data row 2 cell2</td>
        </tr>
    </tbody>
</table>

<!--Here's what an ordered list looks like:-->
<ol>
    <li>ordered item 1</li>
    <li>ordered item 2</li>
    <li>ordered item 2</li>
</ol>
<!--Here's what an unordered list looks like:-->
<ul>
    <li>unordered item 1</li>
    <li>unordered item 2</li>
    <li>unordered item 3</li>
</ul>

In the sample sheet, I've got the URL for some stats about the Barkley Marathons in cell B3 and am then referencing that in A4's function: =IMPORTHTML(B3,"table",0).

Screenshot of IMPORTHTML

FYI, freeCodeCamp created ScrapePark as a place to practice web scraping, so you can use it for IMPORTHTML and IMPORTXML coming up next👇.

How to use the IMPORTXML function

We saved the best for last. This will look through websites and scrape darn near anything we want it too. It's complicated, though, because instead of importing all the table or list data like with IMPORTHTML, we write our queries using what's called XPath.

XPath is an expression language itself used to query XML documents. We can write XPath expressions to have IMPORTXML scrape all kinds of things from an HTML page.

There are many resources to find the proper XPath expressions. Here's one that I used for this project.

screenshot of XPath cheat sheet

In the sheet for IMPORTHTML, I have several examples that I encourage you to click through and check out.

For example, using the function =IMPORTXML(A11,"//*[@class='post-card-title']") allows us to bring in all the titles of my articles because from inspecting the HTML on my author page here, I found that they all have the class post-card-title.

screenshot of inspecting a web page with dev tools

In the same way, we can use the function =IMPORTXML(A11,"//*[@class='post-card-title']//a/@href") to grab the URL slug of each of those articles.

screenshot of IMPORTXML

You'll notice that it does bring in the full URL, so as a bonus, we can simply prepend the domain to each of these. Here's the function for the first row which we can drag down to get all those proper URLs: ="https://www.freecodecamp.org"&B13

screenshot of prepending domain to slug

Follow Me

I hope this was helpful for you! I learned a lot myself, and enjoyed putting the video together.

You can find me on YouTube: https://www.youtube.com/@eamonncottrell

And, I've got a newsletter here: https://got-sheet.beehiiv.com/

gif of guy saying aren't ordinary people adorable

And, these days, it's actually giving Excel a run for its money. It's picked up a lot of features and processing power over the years that used to be exclusive to Excel.

But we're here to talk about the basics today.

🧾I'm going to cover:

1. How to create new Google Sheets
2. Intro to Templates
3. Menu and toolbar overview
4. Basic data entry and calculations
5. Basic formatting
6. How to create a table
7. How to sort and filter data
8. Intro to formulas and functions

Video Walkthrough

I've made a video walkthrough of the things we'll be covering in this article. You can check it out below:

How to Create a New Sheet

Spreadsheets can be intimidating even in their most basic form.

Don't be scared, though.👇

To use Google Sheets, you need a free Google account. (If you're using Gmail, you already have this.) Go here to sign up if you don't have one yet.

pic of the Google Sheets homepage

Go ahead and go to sheets.new to create a brand new Google Sheet. It will also prompt you to sign up if you don't have an account yet.

💥This is what you'll see:

pic of a blank spreadsheet

😓Some of you may already have begun perspiring because a spreadsheet looks like an unapproachable blank slate reserved for data analysts, financial gurus, and overly ambitious content creators.

gif of man stressing out

💪Fear not.

A spreadsheet, and particularly a Google Sheet, is ripe with possibilities for the average person.

You can use them to enhance productivity after learning only a few basic things.

📊A spreadsheet is a big grid made up of columns labeled with letters and rows labeled with numbers. Each of the rectangles of the grid are called a cell, and the active cell is the one with the blue outline.

If you start typing, whatever you type will appear in the active cell.

The cells can contain numbers, words, formulas, dates, pretty much anything...

pic of spreadsheet cell

Google Sheets Templates

Google has supplied us with a respectable amount of templates to get started with.

I will not be covering these in any detail because it's important to get the basics down first. I just want you to be aware of them if you need a starting point after you've gotten comfortable with Sheets.

You can look through the template gallery here.

pic of Google Sheets templates

Menu and Toolbar Overview

pic of Google Sheets menu and toolbar

You will likely be familiar with the menu and toolbar setup. As with most modern applications, they are at the top of the page. The menu has many familiar options like File, Edit, View, Insert, and so on. And the toolbar below it consists of mostly icons related to formatting and text options.

The File menu has options to share, download, copy, import, rename and other such things related to the whole spreadsheet.

pic of file menu

Edit and View have familiar options regarding copy/paste and choosing different levels of visibility for your spreadsheet.

Insert gives us a host of options of things to import into our sheet like charts, pivot tables, checkboxes, emojis, dropdown lists, and many more.

pic of insert menu

🎨Format contains many options for formatting our sheet. We can add color, borders, tables styles, and more from here.

The Data menu has a lot of spreadsheet specific functions. It contains shortcuts to sort and filter data, to protect different ranges of cells, to group ranges together by naming them, data validation, and advanced items like connecting data sources.

pic of the Data menu in Google Sheets

Tools and Extensions will be lesser used menus, at least in the beginning. But some really exciting stuff is possible by leveraging the power of Google Apps Script through the Extensions menu. This lets us write programs in a language similar to JavaScript all while in a spreadsheet.

But, that's for later – or check out some of my other videos and articles to get a taste of Apps Script.

Basic Data Entry and Calculations

What is data? According to my Google search, data are "facts and statistics collected together for reference or analysis."

Spreadsheets thrive on data. Yes, think numbers, dates, percentages...things that are easily calculable.

In Google Sheets, we can enter in some data. Say, a list of names, dollar amounts, and dates.

pic of some data

Now, we have a simple list of data. Because it's small, we can scan it and analyze it ourselves pretty easily.

1. We can see that it's ordered by date
2. We can see that Paul either has or owes the most
3. We can see that Sara either has or owes the least

But imagine this is a list of thousands of records (each row of an amount, a name, and a date can be referred to as one record).

Suddenly it gets a lot harder to analyze or make sense of the data.

Then add a dozen or a hundred more columns of data for each record. It becomes virtually impossible for a human to draw anything meaningful from the data without the help of a spreadsheet or computer program.

Next, we'll see how some organization and simple spreadsheet operations can help us draw insights from our data.👇

gif of woman saying this is so easy

Basic Formatting

Highlight the table. You can do this by clicking and dragging across the whole range of cells. Now, you should have an active range instead of an active cell:

pic of Google Sheets table

Take a moment and click through some of the formatting options on the toolbar. I've changed the text, the font size, and then made bold the first header row.

I've also highlighted only the dollar amounts, and changed their format to currency instead of just a number.

pic of Google Sheets formatting

Already, the information is a little more readable. In the next steps we will go further.👇

gif of Alice falling down rabbit hole

How to Create a Table in Sheets

Alas, we don't (yet) have a swift shortcut like Excel does to make a table. But if we highlight our data again, and select Format -> Alternating Colors from the toolbar, we can create table formatting for enhanced readability.

pic of Alternating Colors table style in Google Sheets

Now, we have banded rows of alternating colors. This becomes very useful in large data sets, but is equally pleasing in our small example. Once satisfied with your color selections, click done.

Now, if we add rows of data to the bottom of our table, Google Sheets is smart enough to know that we probably want to extend the table downward. It will extend the alternating color formatting as we add two more lines:

pic of extended Google Sheets table

How to Sort Data with a Filter in Sheets

Tables are useful because, with some small steps, we can sort and filter data in ways that make it easier to draw conclusions or extract meaning from our data.

Click anywhere in the range of data, and select Data -> Create a filter from the Menu bar.

pic of creating a filter in Google Sheets

Again, Google Sheets knows to create a filter for the entire data set, and you can see two things visually represented:

1. The rows and columns of the data set are now given a highlight color to show that a filter has been applied.
2. To the right of each header label, there are three horizontal lines that look like an upside-down pyramid. These are the filters.

pic of filter on a Google Sheets table

From here, we can both sort and/or filter our data. If we sort, for instance, Z -> A, we can display each row from highest to lowest dollar amount.

sorted table

If we click the data column's pyramid, we can see that for filtering options, we can either filter by condition or by value.

pic of filtering options in Google Sheets

Filtering by values lets us toggle on or off certain dates. Filtering by condition allows for all sorts of options. We can choose dates that are before or after certain dates.

pic of filter options in Google Sheets

Or we can select "Is between" and then write in a period of dates we want to filter for.

pic of filter options in Google Sheets

This becomes immensely helpful to narrow down a field of results for analysis. Now we can focus on only the subset of filtered results.

Intro to Formulas and Functions

The final piece we'll touch on is the ability to write formulas and use functions.

Formulas in Google Sheets

Formulas are instructions we manually type into a cell to manipulate data. If we wanted to add the values in cells A16 and A17, we could write a formula that did this like so: =A16+A17.

pic of a formula in Google Sheets

We can do basic math very easily like this, but there's an even better way to do this by using functions.

Functions in Google Sheets

Functions are built-in formulas that we use by typing their name. Again, we start out by typing the equals sign, but then we type the name of the function, in this case SUM: =SUM(A16:A17).

When we begin typing a function, a tooltip will pop up giving us options for the different functions available. You can hit the TAB key to select a function, click one from the list, or complete the spelling of the function and type an open parentheses to select a function.

pic of available functions in Google Sheets

Once selected, the tooltip will display helper text about that function in case we need an explanation of what it does and/or the variables it needs:

pic of the SUM function in Google Sheets

These are straightforward examples of adding numbers, but there are over 400 built-in functions that range greatly in complexity.

What's Next?

As you've probably figured out, this barely scratches the surface of Google Sheets. Once you're comfortable with the basics, it's time to dive deeper and use Sheets to solve some problems of your own.

Check out the templates I've linked above for inspiration, spin up a personal finance tracker, make a workout calendar, track stock prices, build a cost of goods sheet for a small business, build an amortization sheet to see if you can afford a house, track your time with a project management sheet...the possibilities are vast.

🔗Check out my YouTube channel and newsletter for more Sheets content and projects.

🔗Connect with me on LinkedIn.

Hope you have a great one!

gif of Gosling waving bye

I am too, but I'm also intrigued by the technology behind it.

If you'd like to dive into some of the details behind how AI actually works and take a break from the many creepy AI examples on the internet, check this out:

Google has released a free set of training courses for generative AI. Here's their announcement post.

Picture for generative AI courses

Video Walkthrough

I've made a short video of the coursework if you'd prefer a visual walkthrough of my first impressions:

Overview

There are 10 courses in this free learning path. Many are aimed at beginners with no prerequisite knowledge. Some suggest a background in Python, SQL, and/or machine learning. All of them deliver content through a combination of videos, articles, labs and quizzes.

There are badges to show that you've completed them, and I'm happy to see a freely available set of courses around the technology behind AI.

Screenshot of Google course badge

The amount of content produced by and highlighting AI is overwhelming right now, but it's a safe bet that this is going to stick around. As such, it's best to arm yourselves with as much information about it as you can so that you can responsibly create tools and content for the future.

Courses Material

I've created a Google Sheet with all the course info pulled from each of these courses. Check it out here. Google has done a fine job of creating a brief FAQ for each of the courses, and I've pulled them all into one sheet for easy comparison.

Screenshot of Google Sheet summary of all courses

Inside each course, you'll be presented with a straightforward navigation for the content. It's broken into video instruction, a curated collection of readings to peruse, and quizzes. Some courses also have online labs to complete in a certain amount of time.

Video content for the Create Image Captioning Models course

The curated list of readings is quite comprehensive, and you could easily exceed the estimated 45 minutes of time to complete the modules if you read through every one. But it is nice to have such a large list of resources compiled in one place.

The list of readings for the Intro to Large Language Models course

There are also Labs hosted in some of the courses. If you've taken any of Google's Coursera courses, you'll be familiar with this delivery method. You're given a handful of tasks and a countdown timer, in this case 1hr, will begin when you start the lab.

Screenshot of lab work

Here is the full list of courses:

1. Introduction to Generative AI – designed to be an overview of what generative AI is and how it differs from machine learning methods.
2. Introduction to Large Language Models – explores what large language models are, where they are used, and how to use prompt tuning. (If you haven't noticed, prompt writing is being touted as a skill of the future right now.)
3. Introduction to Responsible AI – an ethical course on what responsible AI is, how it's implemented in Google products, and why it's important. This course introduces Google's 7 AI Principles. I didn't know this was a thing, but there's a whole page devoted to it. Covering topics from social responsibility, to safety, accountability and privacy design principles, I was happy to see that there is large effort being paid to build in solid principles.
4. Generative AI Fundamentals – A quiz covering topics from the first three courses.
5. Introduction to Image Generation – An introduction to diffusion models which are a family of models used in image generation. Some pre-existing knowledge of machine learning, deep learning, convolutional neural nets and/or Python programming is suggested.
6. Encoder-Decoder Architecture – overview of a machine learning architecture for tasks like machine translation, text summarization, and question answering. Python and Tensorflow knowledge is suggested as a prerequisite.
7. Attention Mechanism – a technique that allows neural networks to focus on specific parts of an input sequence. Some pre-existing knowledge of machine learning, deep learning, natural language processing, and/or Python programming is suggested.
8. Transformer Models and BERT Model – Bidirectional Encoder Representations from Transformers...this is what BERT stands for in case you didn't know. You'll learn the main components of the Transformer architecture and intermediate machine learning experience as well, and knowledge of Python and TensorFlow are recommended.
9. Create Image Captioning Models – how to create an image captioning model using deep learning. Deep learning, machine learning, natural language processing, computer vision and Python are recommended prerequisites.
10. Introduction to Generative AI Studio – you'll walk through demos of the Generative AI Studio which helps prototype and customize generative AI models. There is a hands-on lab at the end.

My Thoughts

After spending some time browsing through all of these courses, it's a nice mix of truly beginner-friendly content and some intermediate level stuff that requires previous knowledge of machine learning, Python, deep learning, and/or natural language processing.

I appreciate how there are answers to many common questions in the drop down menus for each course (and I've compiled all of those in one place in this Google Sheet).

Screenshot of Google Cloud certifications

This is a great starting point if you are truly interested in the inner-workings of AI. It also looks to be a potential on-ramp to some of Google Cloud's larger certifications as there are links to further training sprinkled in the courses.

What do you think?

👋My name is Eamonn Cottrell, and I'm building content around Google Sheets & Workspace.

Get my 📨 newsletter here. Find me on 📺 YouTube here.

Please ask questions in the comments, or send me a message; I love making new spreadsheet connections!

Coefficient produced a Google sheets extension with the ability to use OpenAI's GPT models from within a spreadsheet. It’s called GPT Copilot and it’s available to try for free.

screenshot of Coefficient's GPT in Sheets page

I have been putting off looking into this tool for quite some time, though I've been following Coefficient's Google Sheets work for a while now.

To be honest, I'd gotten a little tired of all the AI talk and was wary of yet another effort in that realm. Do I really need to use AI in spreadsheets?

🤷Yes and no.

Video Example

I made a quick video walking through a handful of the GPTx functions, and I'll detail all of them in the article below. If you have a sec, check out the 2.5 min video and give it a 👍.

The extension is pretty cool.

AI is not some thing that is going to do all of my work for me. But it is a pretty powerful tool with which I can do work faster, more efficiently, and in some cases get some unique inspiration from.

gif of ted lasso saying, smells like potential

GPT Copilot Functions

In the GPT Copilot extension on Google Sheets, we are given a list of several built-in GPTX functions we can now use directly in the sheet itself.

Here is a document with all the functions listed out, and we'll go through each below.

And here's the spreadsheet I used for all my samples and the video.

The first function, =GPTX(prompt) simply lets us generate text using Open AI's GPT model.

I used this to generate a subject line for an email below. This generic GPTX function can be used to query ChatAI's model for anything you can think of:

Screenshot of GPTX() function

Then we get **=GPTX_LIST(prompt)** which lets us generate a list of values in the same way.

This is similar to the first example, but the list of items will appear in separate cells so it's handy if you want...say, the top 10 video games by sales in Korea:

_Screenshot of GPTXLIST() function

**=GPTX_EDIT(text,[task])** was useful as I went through and created email body messages based on a prompt that I fed into the function. So it lets us perform a task or transformation on an input text.

_screenshot of GPTXEDIT function

**GPTX_MAP(search_keys, inputs)** performs a fuzzy search, given a list of search keys in the list of input values, and then outputs the most similar search for each input value.

If we have a list of games and platform companies, it can map the company values to the correct game:

_Screenshot of GPTXMAP()

**GPTX_FILL(text, [task])** fills in missing information in the table based on example rows.

This will let you feed it some example data and have GPTX fill in the rest:

_Screenshot of GPTXFILL

**GPTX_TABLE(prompt, [header_row])** generates a table of values. This one was particularly cool because you can give it a real query to get data from, or have it fill out a table with dummy data.

Here we can pull in some population info on the top 5 cities in Tennessee: 😀

_Screenshot of GPTXTABLE()

**GPTX_FORMAT(text, language)** converts input values into the specified format.

**GPTX_TAG(text, tags)** does what it sounds like: it applies one or more tags matching a piece text.

If you have a list of movies, and a list of genres, you can tag them here. Interestingly:

1. GPT doesn't seem to have a sense of humor as it didn't classify Waterworld as a flop.
2. It also won't blindly fill in genres that are missing from the list (Goldeneye and Seven)

_Screenshot of GPTXTAG()

Another cautionary tale, though...when I added crime to see if it would fill in Seven's blank, it actually came back with different answers on some of the others...and left off Casper and Jumanji this time. So, while nothing is really tagged incorrectly, it does have varying results.

In this case, it does behave like a human, in a way. You'll get different people answering this type of tagging question in different ways too. 🤷‍♂️

**GPTX_CLASSIFY(text, labels)**, in the same way, classifies text given a set of labels or categories.

**GPTX_EXTRACT(text, info_to_extract)** extracts the desired information from the input text. So if you have an address in a cell, it can extract the city name from the address.

Caution though; I encountered several instances while writing these GPTX functions where I got #ERROR!s and had to refresh the page, close it and reopen, or simply wait a while...

**GPTX_SUMMARIZE(text, language)** summarizes the input text according to the given format.

I tried to be clever here. I pasted the first chapter of The Great Gatsby into a cell. Pretty quickly got a "too large" error 😂)

_screenshot of GPTXSummarize error

But! Reducing down to the first 726 words was sufficient to have it actually summarize the beginning of this awesome novel:

_Screenshot of GPTXSUMMARIZE() summarizing the start of The Great Gatsby

**GPTX_TRANSLATE(text, language)** translates the input text into the specified language.

This one's straightfoward and not incredibly useful for my spreadsheets...but nonetheless, it's pretty neat:

_Screenshot of GPTXTRANSLATE()

**GPTX_CONVERT(text, format)** converts the input text into the specified structured format.

**GPTX_CODE(task, language)**generates code in a specified language, which performs the specified task.

GPTX_CODE() was really neat. I also see the most chances for problems here because novices will be tempted to blindly use code not knowing how or if it works.

This seems most beneficial as a helper for when I know how the code should work, but can prompt for the basic skeleton instead of needing to search for methods on my own.

Limits to GPT Copilot

Are there limits to the number of prompts you can feed these functions? Yes. Of course there are. 😆

To stay free, you get 10,000 executions. Monthly plans start at 100,000 executions per month.

screenshot from the Coefficient GPT CopilotFAQ

What I Think

I walk through some real basic stuff that I could have generated on my own in the example video that I encourage you to watch. But I do see the potential for this to be a very helpful tool as the ecosystem matures.

Insofar as dummy data is concerned, it is really helpful to just generate stuff in a spreadsheet quickly.

And it works great to fill in the blanks where you previously might do a little bit more manual labor, extracting values, formatting things, classifying things, and so on.

In addition to these built-in functions, the Coefficient add-on also contains a pivot builder, a formula builder, and a chart builder tool.

screenshot of Coefficient's extension

These allow you to feed a prompt into the extension based on the data in your spreadsheet, and it will generate either a pivot table, a formula, or a chart.

These are kind of crutches for doing some pretty basic operations in Google Sheets. But they do have an interesting utility and are pretty reliable when you give well-written prompts to them.

Wrapping Up

🤔 AI tools can be a big time sink. It's pretty easy to spend more time getting mediocre results from them than it would have taken to do things manually.

But, that's part of the learning curve for any toolkit. Upfront investments can certainly pay off in the long run. I'll keep using these tools to hopefully make better spreadsheets more efficiently.

❓What do you think? Is this another example of over-engineering simple things? Or is it the next big thing in Google Sheets?

Come join the conversation on my YouTube channel: https://www.youtube.com/@eamonncottrell?sub_confirmation=1

Or my LinkedIn: https://www.linkedin.com/in/eamonncottrell/

Have a great one! 👋

I bet I've used the CTRL + F shortcut more than any other keyboard shortcut in my life. CTRL + Z probably comes close, but I use CTRL + F to find things...

💥ALL 💥

💥💥THE 💥💥

💥💥💥TIME 💥💥💥

gif of man saying we need more

And yes, it'll work just fine in a Google Sheet to find information.

But sometimes I want to display a range of results based on a word I'm searching for. To do that, we'll create a dynamic search bar in our Google Sheet.

You can also follow along in this walkthrough video:

The Search Bar

Our search bar is nothing more than a cell or range of cells. In the example below, it starts in J2.

Screenshot of a search bar on Google Sheets

By adding a =QUERY() function in J5 we can look at whatever is typed into J2 (the red circle) and display the search results below it (the blue rectangle).

In my example, I'm searching through a bunch of personal finance transactions (with randomized amounts😀) that are in columns A:F.

Screenshot of finance data on Google Sheets

The Query Function

The =QUERY() function looks in the Transactions range (which is that A3:F range where all the transactions live).

And it grabs all the info in either column B or column D that CONTAINS what we type in J2.

So it searches through all our transaction descriptions in column B and categories in column D for whatever we type in J2. The LOWER command turns the info in B and D into lowercase. This makes it easier to search because the CONTAINS command is case-sensitive.

=QUERY(Transactions, 
       "SELECT A,B,C,D,E WHERE LOWER(B) CONTAINS '"&J2&"' OR LOWER(D) CONTAINS '"&J2&"'")

• the only caveat is that if you type in uppercase in the search bar, it won't work properly.

The Filter Function

By using the =FILTER() function in combination with the =SEARCH() function, we can do the same thing in a little bit shorter formula and without having to worry about case sensitivity.

=IF(ISBLANK(J2),"",FILTER(Transactions,SEARCH(J2,B3:B225)))

The tradeoff here is that when we want to add multiple conditions like we did in the =QUERY() statement, it breaks down. Both =FIND() and =SEARCH() did not work properly when trying to use them more than once inside the =FILTER().

I was able to find a work around by using the plus operator and constructing the formula in this way:

=IF(ISBLANK(J2),"",FILTER(Transactions,(B3:B225=J2)+(D3:D225=J2)))

Unfortunately when you filter in this way, partial matches are not included in the search results.

In the case of Query, partial answers are always returned.

So, when we enter "hom" all the lines with "home" in it would be returned. When using multiple conditions with filter, nothing would return unless you entered the whole word "home".

What About XLOOKUP?

The issue with XLOOKUP is twofold. One, it doesn't handle partial matches well unless you add wildcard characters:

=XLOOKUP("*"&J2&"*",B3:B225,A3:F225,,2)

This increases complexity but still works.

The more important difference is that it will only return one result so it's not going to work at all for us for this use case.

The Winner is Query

Query takes the prize simply because it doesn't need further manipulation to add multiple conditions, and it will return all the values that meet our search criteria.

It may take you a minute to wrap your head around the syntax, but it's just as powerful and more versatile than Filter in the long haul.

The only thing to make sure to remember is case sensitivity. If you're using the LOWER command in your query, don't use any uppercase search letters.

Make it Neat

In the full formula, I've added an =IF() function at the start to handle the blank search bar. We want to return nothing in that case:

=IF(ISBLANK(J2),"", QUERY(Transactions "SELECT A,B,C,D,E WHERE LOWER(B) CONTAINS '"&J2&"' OR LOWER(D) CONTAINS '"&J2&"'")

Screenshot of a blank search bar in Google Sheets

Follow Along

Come follow me over on YouTube as I make tutorials each week.

Sign up here to get my newsletter in your email each week.

Eamonn's Sheets | Coding | Education logo

By using a very short custom function in Google Apps Script, we can add a list of events from a Google Sheet to a Google Calendar. 🤯

And we'll even have it email our guests as well. 🔥

Here's the video walkthrough to accompany the article:

Google Sheets Setup

Our sheet is quite straightforward. We have event names, dates, start times, end times and guest emails.

The only curious thing is the formatting of our dates and times - I'll cover this as we go on, but you can see that columns B and C are repeating information from columns D, E and F...

Google Calendar needs to receive the start and end times in the form of a full date/time object. But in the Google Sheet, there's not an easy way to create a dropdown data validation for users to select a date/time object.

In column D, I've put data validation to select a valid date.

And in columns E and F, I've created a dropdown list of valid times.

Columns B and C combine these together into a format that's useable to send to Google Calendar by using the =TEXT() function to concatenate the date and times together.

I promise it'll make more sense in a second! 😃

Calendar Setup

Let's make a new calendar in Google Calendar.

Underneath your calendars on the left sidebar of Google Calendar, click the plus icon to add a new one.

Give it a name and a description if you want, and then we're ready to roll.

Scroll down a bit in the calendar's settings to the Integrate Calendar section. Copy the calendar ID. This is how we'll get Apps Script talking to Calendar!

Apps Script + CalendarApp

Apps Script is awesome. 👏

The Class CalendarApp allows a script to access a user's Google Calendar and make changes to it.

Here is the full script, and we'll walk through what's going on below.

// Creates an events variable which is an array of arrays
function createCalendarEvent() {
    let events = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("events").getValues();

    // Creates an event for each item in events array

    events.forEach(function(e){
        CalendarApp.getCalendarById("f7574e7b4d1ad00c9ecd7f1eba5bed329e8600e317cd387a400748d67f301d06@group.calendar.google.com").createEvent(
          e[0],
          new Date(e[1]),
          new Date(e[2]),
          {guests: e[6],sendInvites: true}
    );
  })
}

I've named the range A3:B8 as "events". Then in Apps Script, we create a variable named events that grabs all the values in that whole range. We used a small range, but you could make this as many rows long as need be.

let events = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("events").getValues();

Then, we loop through each item and add the events to our calendar.

The first part is where we use that calendar ID string we grabbed from Google Calendar for the getCalendarById method.

Then we use the createEvent method to pull data from each row in our Google Sheet and make new events.

Here's the createEvent description from the developers page:

You can think of each row of data in the Google Sheet as an array of values. In the zero position is the event name, in the one position is the event date and start time, and so on.

By using e[0] we can access the element that is in the zero position for every time we loop through the forEach loop...effectively looping through each row of data.

And this is where the funky stuff we did with the start and end times comes into play.

Because the values in columns B and C are strings since we concatenated them together, we need to turn them back into complete date objects now.

That's why we're passing new Date(e[1]) and new Date (e[2]) into our createEvent function.

It's a bit of a cumbersome way to allow ourselves to use those dropdown selections in Google Sheets rather than painfully typing in a full date/time object.

User experience > code. 👍

And lastly, we add an optional parameter to send invites to guests.

Send with Button

That's all there is to the Apps Script. 🎉

As an added feature, we've attached a script to the rounded rectangle drawing to make it function like a button. Any time this is pressed, the events in the Google Sheet will populate the Google Calendar.

I hope this has been useful for you!

Please come check out and subscribe to my YouTube channel where I'm making weekly videos on coding and spreadsheets.

If you'd like my newsletter in your inbox, check it out here.

Yes, you can use them for finance dashboards, personal budgets, and project management (and we'll cover these as future topics). But in this article, I'll go through the basics of Apps Script by building a simple and playable Tic Tac Toe gameboard.

Here's the link to the spreadsheet we'll be making if you'd like to check it out while you follow along:

Let's go gif

Video Walkthrough Available

If you'd like to check out a video walkthrough of the Google Sheet, here you go:

Project Setup

Create a new Google Sheet by either going to your Google Drive and selecting NEW -> Google Sheet or by simply typing in sheets.new in the URL bar of your browser.

screenshot of Google Drive

Since this is a gameboard, we are going to give it a little bit of formatting to look good, add data validation and conditional formatting to add functionality to the game, and create useable buttons for our scorecard.

Here's what we'll end up with:

screenshot of Tic Tac Toe Google Sheet

Let's remove gridlines, add borders to the gameboard and the scorecard, and set an alternative font for the board.

To get rid of gridlines, select View -> Show -> Gridlines to uncheck this option.

screenshot of Google Sheets view options

To get a nice square grid with large X's and O's, I set the row height and column width of rows 2 - 4 and columns B - D by highlighting them, right clicking, and selecting the resize options.

screenshot of resizing columns in Google Sheets

I chose 150 pixels for the height and width. You'll need to do these separately – you cannot change both the row height and column width at the same time.

screenshot of resizing columns in Google Sheets

For the font size of the board, select 100, and for the Font, I am using Lexend. You can add additional Google Fonts from the toolbar dropdown:

screenshot of Fonts options in Google Sheets

Add a border to the board and the scorecard areas by highlighting the cells and then selecting the border options from the toolbar.

Click and drag over cells to select the whole range, and hold down the CTRL button to click and drag a second area. You can style these at the same time.

screenshot of border options in Google Sheets

Data Validation

Highlight the gameboard (B2:D4) and select Data -> Data validation from the menu.

This allows us to select Dropdown as the Criteria and add X and O as the two options to select.

Then click Advanced options and select Reject the input if the data is invalid, and plain text for the display style. This will keep the dropdown chips and handles from cluttering the gameboard.

screenshot of Data validation menu in Google Sheets

Conditional Formatting

We'll use conditional formatting for our gameboard also. We need to check for all the winning conditions, and if one of the players gets three in a row, we will highlight those cells.

Keeping the gameboard highlighted, select Format -> Conditional formatting.

screenshot of Format window in Google Sheets

There are eight conditions we'll check for three-in-a-row: going across x3, going down x3, and diagonal x2.

But we only need to write four formulas (two for the diagonals, one for across, and one for down) since we can use dollar ($) signs to drag the formula down and across for those.

For the down three-in-a-row:

//Apply to range B2:D2 
=AND($B2=$C2,$B2=$D2,ISTEXT($B2))

For the across three-in-a-row:

//Apply to range B2:D2
=AND(B$2=B$3,B$2=B$4,ISTEXT(B$3))

For the diagonals we have to define them separately:

//Apply to range B2, C3, D4 
=AND($B$2=$C$3,$B$2=$D$4,ISTEXT($B$2))

//Apply to range B4, C3, D2 
=AND($B$4=$C$3,$B$4=$D$2,ISTEXT($B$4))

We test for equality of each cell and whether there is anything in the cell with the =ISTEXT() function. By wrapping each item in an =AND() function, we'll only apply the formatting if all conditions are met.

I selected a green background for the conditional formatting.

screenshot of conditional formatting window

Apps Script

Now for the scorecard logic. Let's open Apps Script by selecting Extensions -> Apps Script from the menu:

screenshot of Apps Script menu

We'll write four functions to handle our logic:

1. xScore() will increment X's score in the scorecard
2. oScore() will increment O's score in the scorecard
3. clearBoard() will clear the board but keep the scores
4. reset() will clear the board and set the scores back to zero

To make things more legible, let's set a few named ranges.

Highlight the gameboard again and select Data -> Named ranges. Give this range a name of Board. Do the same for cells G4 and H4 for xScore and oScore, respectively.

Now for the scores, we'll have the exact same function for each using only the two different ranges: xScore for X and oScore for O. Here's how those will look using the xScore as an example:

xScore() & oScore():

function xScore() {
    var sheet = SpreadsheetApp.getActive(); 
    var xScore = sheet.getRangeByName('xScore').getValue();
    sheet.getRangeByName('xScore').setValue(xScore+1); clearBoard();
}

• Line 1: This sets a variable (we'll do this in each function) for the active spreadsheet.
• Line 2: This sets a variable for xScore as the value in the named range xScore (cell G4)
• Line 3: This sets a new value for the xScore cell as whatever it was plus 1.
• Line 4: This runs the clearBoard() function which we'll write next...

clearBoard():

This will simply clear the gameboard but leave the score board untouched.

function clearBoard() {
    let sheet = SpreadsheetApp.getActive();
    let board = sheet.getRangeByName('Board');
    board.clearContent(); 
}

• Line 1: Our sheet variable again.
• Line 2: Our board variable. This is grabbing the range B2:D4 which we named 'Board'
• Line3: This built-in clearContent() method simply clears everything in those cells. Pretty simple.

reset():

Now we need a function to set the score board and gameboard back to their original states.

function reset() {
    let sheet = SpreadsheetApp.getActive(); 
    sheet.getRangeByName('xScore').setValue(0);
    sheet.getRangeByName('oScore').setValue(0); clearBoard(); 
}

• Line 1: our active sheet
• Line 2: we grab our xScore range and set its value to 0.
• Line 3: we do the same for our oScore
• Line 4: we run the reset function to handle the gameboard.

And that's it! Now we can run any of these functions from within the Apps Script editor and see that they work.

screenshot of running code within Apps Script editor

How to Create Buttons

It would be a lot nicer to have buttons in our actual spreadsheet to be able to run the functions.

To do this, we'll draw a button and then assign a script to it.

Select Insert -> Drawing from the menu.

Screenshot of Insert menu in Google Sheets

You can draw anything you'd like, but I chose the basic rounded rectangle.

Screenshot of shapes in Google Sheets drawings menu

Double click in the shape to add text, and resize, recolor, restyle as needed.

Screenshot of button in Google Sheets

Once you've created your button, click Save and Close. Then resize and position it where you'd like it in the Google Sheet. I've put mine right under the scorecard, and I made one for each score as well as a reset button.

Finally, to make the button work, click the three little dots at the top right of the button and select assign script. Then type in the name of the script (without the parentheses).

Screenshot of assigning a script to a button in Google Sheets

Now, all you've got to do is click one of the buttons and the assigned script will run 🔥.

Two notes:

1. The first time you run a script, there will be a pop-up dialog box asking you to accept the security allowances. It's a safety net to make sure you know you're running the code that's written in Apps Script, and to examine it if you didn't write it. You'll need to click through those and accept the risk to allow it to run.
2. If you need to move a button after assigning the script, you may get frustrated when clicking it doesn't bring up the three dots for the menu and only runs the script. To get around this and allow for movement and the three dot menu, right click the button.

Wrapping Up

I hope this has been helpful for you!

Please subscribe to my YouTube channel here for more content like this.

Have a great one!

It follows five other successful entry-level certificate programs in IT Support, Data Analytics, Digital Marketing & E-commerce, Project Management and UX Design.

I made a first impressions video to accompany this article that you can find here:(more links in the summary at the bottom of the article, too)

What is "Grow with Google"?

The Grow with Google program is for people interested in "training, tools and resources to grow skills, careers, or businesses". You can find all the certificate programs and resources here.

Screenshot of Grow with Google website

Grow with Google's mission is to enable anyone to have access to quality technology training. They've been training digital skills since 2017, and currently partner with Coursera for course delivery.

Coursera's been in the MOOC (Massive Open Online Course) space since 2012, and has grown to be a leader in the online education world.

Screenshot of FAQ from Grow With Google

Though Google has released a few Advanced level courses recently (I wrote about this here) in the Data Analytics, Business Intelligence and IT Automation tracts, the new Cybersecurity certificate has been designed with beginners in mind.

There is no experience necessary to begin. The structure of the certificate program starts in a foundational introductory module, works through some technical topics like SQL, Linux, and Python, and then culminates with a module on preparing for Cybersecurity Jobs.

Get Job Ready

This is also part of Google's Grow With Me pathways: they are partnering with businesses and universities to pair together jobs and program graduates.

The initial job platform was deprecated as of February 7th, 2023, but the new one for this year is maintained on CareerCircle.

Screenshot of CareerCircle

This is a strong draw to participants, especially as the entry-level salaries for many of the fields approach and exceed six figures. Cybersecurity has a $100K+ amount listed in the marketing material.

Screenshot of Cybersecurity marketing material for median entry salaries

Naturally, these amounts are easily skewed and may not be realistic depending on the applicant's background. But it's no secret that on the whole, these technical jobs do lead to higher paying salaries.

What's the Coursework Like?

I took a look through the 8 courses that make up the Cybersecurity certificate, and found it to be a similar makeup to the first Data Analytics certificate that I completed a couple years ago.

It begins with some basic overviewing of the field, and then moves into an assortment of high level concepts and technical skill building. Here is the full list:

• Foundations of Cybersecurity
• Play it Safe: Manage Security Risks
• Connect and Protect: Networks and Network Security
• Tools of the Trade: Linux and SQL
• Assets, Threats, and Vulnerabilities
• Sound the Alarm: Detection and Response
• Automate Cybersecurity Tasks with Python
• Put it to Work: Prepare for Cybersecurity Jobs

As you can see from some of the titles, Google does get a little general in their topics, which is to be expected in an introductory course.

Screenshot of Cybersecurity Professional Certificate coursework

They have also been long-winded in the video courses in my experience, and I recommend increasing the video playback speed to 1.5x or 2.0x normal speed. I found I had little difficulty in keeping up with the presenters when I did this in other courses.

More Course Details and $$$

Coursera would like you to pay $49 a month for this certificate program, and that's perfectly reasonable. Especially since Google estimates you can complete it in 6 months by working only 7 hours a week

...and I estimate you can complete it in a max of 3 months working 7-10 hours a week. 😀

But, if you're strapped for cash, you can click into any one of the individual courses and audit the whole thing for FREE.

Screenshot of Coursera course audit

You won't get the certificate, or access to the CareerCircle network, but if you're just leveling up your knowledge, you can do that for free.

You can also see each module's details within each of the courses. This detailed view breaks down each lesson to exactly what topics will be covered and how much time will be spent on each video, reading, or quiz.

This is a great place to look in case you're not starting from zero and wonder if it will be repeating information you already know. I found that many of the items were very beginner-based (as they stated), especially in the Python course.

Summary, Links, and Video

This looks to be another solid offering from Google into a field that promises to be intriguing to many people.

If you're interested in Cybersecurity and have been wondering where to start, I believe this is as good an entry point as any.

• Check out my first impressions video here.
• Check out the Coursera page here.
• Check out Google's Cybersecurity program overview page here.

Have a great one!