I thought it would be a good idea to collate some Google Sheets Troubleshooting information. This guide includes some Frequently Asked Questions and some not so Frequently Asked Questions, but still interesting nonetheless. When I originally set out to make this post, I thought it would be a short and sweet entry into the log. Cut to more than 80 pages (in Google Docs) later, here we are. It really did escalate quickly.

Here is an ordered list of the questions, but ordered by my own perceived level of complexity (easiest to hardest):

1. Why is my Default Currency not my location’s Default Currency?,
2. Conditional Formatting,
3. Reference Other Sheets and Other Google Spreadsheets,
   1. How can I import data from another sheet in Google Sheets,
4. Remove Duplicates from a List,
5. List of Unique IDs,
6. Multiple Dependent Dropdown List,
7. What is a Pivot Table and how to use one?,
8. How to use REGEXREPLACE to replace a specific bit of text?,
9. How to view ARRAYFORMULAs, and
10. How to look at Excel like SQL.

https://docs.google.com/spreadsheets/d/1-8kI_nAM8IuWRoh-Hm39dsrgwMuntIlmgQclAOOdeDU/edit#gid=0

1. Why is my Default Currency not my location’s Default Currency?

If you’re like me, you can get frustrated at the time wasted formatting a cell with a few clicks as opposed to using a combination of three keys. Working from Australia, I used to come across this fairly regularly – the quick access number formatting was set to a region outside of my own:

If you’re working on your own projects then it’s not a big deal, but it really does throw you when you’re not expecting the Pound symbol to show.

For a while, my fix to this would be:

1. Selecting the cell(s),

2. Click Format,

3. Click Custom number format,

4. Enter the following code into the custom field, if the Australian Dollar option wasn’t available (image above):

[$$]#,##0.00

But, there are two other ways to solve this issue; one short-term and one long-term solution.

Short-term Solution

You can adjust the settings in your current Google Sheet so that any time you open it, it will resolve to the default setting you select.

1. File > Settings:

2. General > Locale:

3. Adjust Locale to your location:

Every time you open this spreadsheet, the default Locale will be set to what you set.

Long-term Solution

If this short-term solution isn’t what you’re looking for, here is one that will save you ultimately hours of wasted formatting time.

Log into your Google Account: https://myaccount.google.com/ and click Personal info on the left-hand side,

Scroll down to the bottom and find General preferences for the web then click Language:

Select your preferred language and make sure it is set to the location you are working from:

The first option allows you to select the language:

Then the second option allows you to select the location:

Whenever you create a new Google Sheet, the number formatting will be set automatically to this selected location.

2. Conditional Formatting

When you format a cell, you’re creating aesthetics to help it stand out from other cells and draw the user’s attention. Sometimes you might even use it to disguise information from a user. In this case, we want to highlight some information. First, let’s start with highlighting individual values. I have some sales data with salespeople’s names, monthly sales, and their work locations:

Let’s say we want to highlight some value. I’ll start by selecting the sales data:

Then I’ll click Format > Conditional formatting:

Under Format cells if… I’ll select Greater than:

I want to see if any salespeople have earned more than $99,000 in sales for the month:

Now, let’s see how many people earned more than $75,000:

This is pretty cool, but this time I’d like to highlight all of that person’s information; Name, Sales, and Location. Since we want to apply this to the entire row, we can’t just change the Apply to range portion of the Conditional Formatting menu:

Instead, we need to write a custom formula to trigger the entire row match. So, let’s change Greater than in Format cells if… to Custom formula is:

I will enter the following formula into the custom formula space:

=IF($B2>90000,1,0)

The reason for adding the $ in front of B means that the conditional formula lookup will only look for values in column B for the range A2:C11. Otherwise, it will return all cells being TRUE, hence colouring all of the cells green. Alternatively, you could also add a variable cell in the spreadsheet, allowing you to adjust a lookup value. Let’s make $D$2 the variable cell:

Then set it to 60,000:

3. Reference Other Sheets and Other Google Spreadsheets

Sometimes it seems more manageable to store different information in different sheets (within the same document). When you get to Column AN and row 1198, part of you thinks that it might be possible to break it up a little bit and make it more readable. However, once you break those bits of information up, how do you actually make reference to it?

I’m going to start by making a new sheet called FAQ3 and place the same three headers in FAQ2:

Now, I’ll go to cell A2 and start a new formula with the equals sign, click on FAQ2, then click on cell A2 in FAQ2:

When I press enter, I should now see ‘Jaxon Sparks’ in FAQ3, cell A2:

You can also see the sheet FAQ2 has been referenced in FAQ3’s cell A2:

If we drag the little blue ‘copy cell’ dot across to B2 (Sales), we should now see that value copied over from sheet FAQ2:

The same will happen when we copy the cells down:

What if we wanted to create and store bonus information for people on this sheet though. Let’s now transform column C into a bonus column. Those who receive sales in excess of $20,000 will see a .5% bonus, $40,000 will receive a .75% bonus, $50,000 is 1%, $70,000 is 2%, and $90,000 is 3%. I’ll use the VLOOKUP approach for this one:

I have made two versions of the Bonus to show how you can use different Sheet referencing.
Method 1: FAQ2 cell reference the sheet for Sales then using FAQ3 cell referencing for the bonus calculation:

=B2*VLOOKUP(B2,$F$6:$G$10,2,1)

Method 2: Sheet referencing FAQ2 for bonus calculation:

=‘FAQ2’!B2*VLOOKUP(‘FAQ2’!B2,$F$6:$G$10,2,1)

Import data from other Google Sheets Document

Let’s say you create a new Google Sheet file for each year, with monthly Sales figures for each salesperson. If you wanted to compare those two months (a year apart), you might have to open both documents then compare. However, you can actually import ranges into a Google Sheet from other Google Sheets. (Who remembers this meme?)

Let’s step through it. In this folder, I have two different documents, ‘50 – Google Sheets FAQs’ and ‘50a – Google Sheets FAQs’: 

In 50a, I have created a dataset from the “year prior”:

I would like to use this year’s information and compare it to last and see how the salespeople have tracked in the same month, a year apart. I’ll start by entering the Name, Sales (2024), and Sales (2023) headings in a new table:

Then I’ll gather the names using the sheet referencing technique we have just learned:

Now the next part. Let’s click on cell C16 and type =IMPORTRANGE(:

This function allows us to link between Google Sheets and import ranges. So, let’s get the 50a sheet url:

https://docs.google.com/spreadsheets/d/1-dx5QN625JIqhdpWtZmMXZtYgWQyj1LwRiu-rJuuBVA/edit#gid=0

Then paste that into the IMPORTRANGE formula:

And then go back to 50a to see which range we want to import:

The cell range is “Sheet1!B2:B11”:

After you press enter, Google will do its thing and ask for permission for the two documents to talk to one another. 

Hover your mouse over the #REF! Error, then click Allow access:

The information will now appear in your spreadsheet:

Let’s now do a comparison between 2024 and 2023. If the salesperson has more of fewer sales, I want to make note of that, so I will using the following IF function:

=IF(B16<C16,ROUND(B16/C16,2)*10&“% fewer sales”,ROUND(B16/C16)*10&“% fewer sales”)

You could also incorporate Conditional Formatting to highlight those who have more of fewer sales:

4. Remove Duplicates from a List

A frustrating thought is knowing there are multiples of the same data in your dataset. This can limit the analysis of your data as it can impact metrics like mean, median, mode, etc. But, there are ways we can cleanly get rid of those duplicates. First, let’s observe the data that we are currently using:

I want to see how many unique names we have listed in the set and how many total names (including duplicated):

So, by the end of this process, we should only have 103 entries in both of those evaluations. Let’s start the process by going to Data > Data Cleanup > Remove Duplicates:

Next, we can select which column(s) we want the duplicates to be found in and decide whether the data has a row header. This dataset actually does, so I had selected that top option:

After clicking Remove duplicates, it has found the 35 we were looking for and removed them.

We now have 103 unique name entries, but 104 unique rows (when we include the header):

5. List of Unique IDs

A common query is how to assign unique IDs to a list of people, but the list may include people allocated with IDs. Since we’ve already learned some conditional formatting, let’s use those techniques to find duplicate entries in this list:

Select the dataset and click Format > Conditional formatting:

Format cells if… > Custom formula is:

Enter the following formula:

=COUNTIF($A$2:$A,A2)>1

Shows how many names have been duplicated in the list:

Now we know which names are duplicates and should be assigned the same number. Instead of having to filter through and remove already assigned people, here is a function that automatically adds the next number in sequence to an unassigned person:

=IFERROR(VLOOKUP(A3,$A$2:$B2,2,0),MAX($B$2:$B2)+1)

To start with though, I’m going to place ID into cell B1 then give the first entry, Kaylee Mann, the unique ID 1:

Next, I’m going to enter the VLOOKUP function (above) into cell B3:

We’re off to a good start. Let’s copy the function down a few rows:

We can see that it’s numbering everyone as it should be. Let’s go toward the bottom of the list and see where the duplicates are coming in and how they’re being handled:

We can see that there are some duplicates being accounted for, specifically Anaya Rogers (ID #90). Anaya is in cell A91, but also A105. They have been provided the same unique ID.

Let’s break down the formula though:

VLOOKUP(A3,$A$2:$B2,2,0)

Will look at the person’s name between the top of the list, $A$2, and the growing bottom of the list, A2. As the formula moves down the list, it will search names in the cells above the current entry.

MAX($B$2:$B2)+1

If there isn’t an entry for that new name, it will be assigned a new number; the next number in the sequence. This code will look for the largest number in the growing array and add 1 to it.

6. Multiple Dependent Dropdown List

Multiple dependent drop-down lists, also known as cascading drop-down lists, allow you to create a hierarchy of dropdowns where the choices in each subsequent dropdown are dependent on the choice made in the previous dropdown. You can achieve this in Google Sheets using the FILTER function and named ranges. Here’s how to do it.

Let’s say you have a list of car brands (BMW and Audi) in column A (A2:A7), and a list of car models (3/5/7 Series and A3/4/5) in column B (B2:B7), where each car model belongs to a specific car brand:

Depending on how many dropdown lists I could make, I can make things easier by creating named ranges. For this case, I’m going to create two, one for Brand and one for Model. I’m going to select the range of cells containing the brand list (A2:A7) then write ‘Brand’ in the top-left corner of Sheets:

I’ll do the same for the model list (B2:B7):

Now I’m going to make the first drop-down list. As I’m not going to add to this list, I’ll place it below the table, but better practice would be placing it to the right-hand side of the table. After I select the cell, A10, I’ll click Data > Data validation:

Next, Add rule:

Under Criteria, select Dropdown (from a range):

After clicking the little bordered cell square, I will enter the named range, Brand. This will populate the dropdown list with the values from that Brand named range. Now click OK then Done:

Now onto the second dropdown list, which is dependent on the first one. I’ll write ‘Filter’ in cell D4 then enter the following formula into cell D5:

=FILTER($B$2:$B$7,$A$2:$A$7=$A$12)

Now I can select cell B10 and perform the same dropdown menu process as before:

Now we have two dropdown menus, one dependent on the other:

7. What is a Pivot Table and how to use one?

A pivot table is a powerful tool in Google Sheets (and other spreadsheet software) used for summarising, analysing, and visualising large datasets. It allows you to organise and manipulate data quickly and easily, providing insights that might otherwise be challenging to discern.

Here’s how to create and use a pivot table in Google Sheets:

Ensure that your data is organised into a consistent format with headings for each column. It’s also helpful if your data includes categories or labels by which you can group and analyse the data. Click and drag to select the range of cells containing your data. Make sure to include the headings, as Google Sheets will use these to create the pivot table.

Create the Pivot Table by going to Insert > Pivot table at the top of the screen.

 A new sheet will be created with a blank pivot table. You will now have a blank Pivot Table:

Customise the Pivot Table by using the right-hand sidebar. You’ll see several options for customising your pivot table:

• Rows: Drag the column headings that you want to use as row labels into the “Rows” box. This will group your data by the values in these columns.
• Columns: Drag the column headings that you want to use as column labels into the “Columns” box. This will create additional groupings for your data.
• Values: Drag the column headings that you want to use for calculating values into the “Values” box. By default, Google Sheets will calculate the sum of these values, but you can change this by clicking on the arrow next to the column heading and selecting a different aggregation function (e.g., Average, Count, Min, Max, etc.).
• Filter: Drag the column headings that you want to use for filtering the data into the “Filter” box. This will allow you to display only the data that meets certain criteria.

Start by dragging and dropping the different dimensions on the right-hand side, Date, Color, Region, etc, into the Rows, Columns, or Values section of the editor:

I’m interested in observing the sales for each region, so I’ll start by dragging Region into Rows and Sales into Values:

If I want to sort the regions by the most to least sales, I’ll navigate to Region in the editor then click Sort by > Sum of Sales > Order > Descending:

Maybe I want to see what the average sales by each region is, so I can change Sum to Average in Values > Sales > Summarize by > AVERAGE:

I can also separate the Regions into their Colours, so I’ll add that dimension, Color, to the Columns editor:

Once you’ve set up your pivot table, you can start analysing and visualising your data. For example, you can click and drag the column headings in the “Rows” or “Columns” boxes to rearrange the order of the groupings, or you can click and drag the column headings in the “Values” box to add or remove values from the pivot table.

If your data changes after you’ve created the pivot table, you may need to refresh the pivot table to reflect the changes. The thing is, Google Sheets doesn’t currently contain a Refresh data button:

After you’ve added additional data to your original table, you will need to update the row/column count in the Pivot table editor:

Update the row count:

It will update the entire table for you:

You can customise the appearance of your pivot table by clicking on the “Pivot table” menu at the top of the screen and selecting Pivot table options. From here, you can change the formatting, sorting, and other options for your pivot table. Once you’ve finished customising your pivot table, you can save it by clicking on the File menu at the top of the screen and selecting Download. Choose the format you want to use (e.g., Excel or PDF) and follow the on-screen instructions to save your pivot table to your computer.

If you want to share your pivot table with others, you can do so by clicking on the Share button in the top-right corner of the screen and entering the email addresses of the people you want to share it with. You can also choose whether to give them view-only access or editing access. Finally, don’t be afraid to explore and experiment with your pivot table. There are many ways you can customise and analyse your data using a pivot table, so don’t hesitate to try new things and see what works best for your needs.

8. How to use REGEXREPLACE to get a specific bit of text?

In Google Sheets, REGEXREPLACE is a powerful function that allows you to replace text based on a regular expression pattern. This can be incredibly useful when dealing with complex text data that needs to be manipulated in a specific way. Let’s explore how you can use REGEXREPLACE to effectively manage your data.

What is REGEXREPLACE?

REGEXREPLACE is a function in Google Sheets that replaces text based on a regular expression pattern. A regular expression (regex) is a sequence of characters that define a search pattern. REGEXREPLACE replaces all occurrences of the pattern within a specified text with a replacement text.

Here’s the syntax of the REGEXREPLACE function:

REGEXREPLACE(text, regular_expression, replacement)

• text: The text in which to search and replace.
• regular_expression: The regular expression pattern to use for the replacement.
• replacement: The text to replace the matched pattern with.

How to Use REGEXREPLACE?

Let’s say you have the following text in cell A1:

“The quick brown fox jumps over the lazy dog.”

You want to replace all occurrences of “brown” with “red” in this text. You can use the following formula in another cell:

=REGEXREPLACE(A1, “brown”, “red”)

This formula will return “The quick red fox jumps over the lazy dog.” as the output because all instances of “brown” have been replaced with “red”.

More Examples

Here are a few more examples of how to use the REGEXREPLACE function.

Replace all instances of “brown” or “lazy” with “red”:

=REGEXREPLACE(A1, “brown|lazy”, “red”)

Replace all instances of numbers with “X”:

=REGEXREPLACE(A1, “\d+”, “X”)

Replace all instances of punctuation with a space:

=REGEXREPLACE(A1, “[.,;:!?]”, ” “)

Please note that the regular expression patterns used in these examples are simplified and may not cover all possible cases. You may need to adjust the patterns to match your specific requirements.

In addition to REGEXREPLACE, Google Sheets also provides other regular expression-related functions, such as REGEXMATCH and REGEXEXTRACT, which can be used for matching and extracting text based on regular expression patterns, respectively.

9. How to view ARRAYFORMULAs

In Google Sheets, an ARRAYFORMULA is a special function that allows you to perform an operation on a range of cells in a single step, rather than having to apply the operation to each cell individually. This can be particularly useful when dealing with large datasets or when you want to perform a calculation across multiple rows or columns.

So, what is an ARRAYFORMULA?

An ARRAYFORMULA is a type of function in Google Sheets that allows you to apply a formula to an entire range of cells. It can be used to perform calculations, manipulate data, or generate results based on a given set of criteria.

How do I use ARRAYFORMULA?

Using the ARRAYFORMULA function will be useful for doing processes that take numerous steps or cross between various arrays. Firstly, let’s spit out a bit of numbers to see what we can do with the function. I would like to generate a list of numbers from 1 to 4 in cells A3:A6:

=ARRAYFORMULA({1;2;3;4})

If I want to make them horizontal, I can replace the semicolons with commas:

=ARRAYFORMULA({1,2,3,4})

I’ll go back to that first one, with the semicolons, but I want to place a duplicate list in column B. To do this, I will need to add another array between 1 to 4, but separate that second array with a comma, not semicolon:

=ARRAYFORMULA({1;2;3;4},{1;2;3;4})

It doesn’t have to be numbers. We can do letters (or strings) too:

=ARRAYFORMULA({“a”;“b”;“c”;“d”})

=ARRAYFORMULA({{“a”;“b”;“c”;“d”},{“a”;“b”;“c”;“d”}})

Let’s see if we can add some arithmetic in the second column this time. I’ll output the letters again, but this time, B3 will be 1+1, B4 will be 1+2, etc to B6 1+4:

=ARRAYFORMULA({{“a”;“b”;“c”;“d”},{1+1;1+2;1+3;1+4}})

Let’s play around with some “meaningless” data. I’ll enter some values into the cells A10:B13:

If I wanted to find the multiplication of each row, multiplying column A by column B, I would traditionally enter A10*B10 then copy that formula down. What ARRAYFORMULA allows me to do is bypass those extra steps. So I’m going to enter:

=ARRAYFORMULA(A10:A13*B10:B13)

How can we make this meaningful?

Let’s translate that meaningless data into meaningful data:

If I asked you to calculate ‘What is the total cost of all Goods (qty and price)?’ what would you do? Like we saw before, one way is to manually enter each term and find the sum:

=A23*B23+A24*B24+A25*B25+A26*B26

Another way is using SUMPRODUCT():

=SUMPRODUCT(A10:A13,B10:B13)

Here’s how we can achieve this task by using ARRAYFORMULA():

=ARRAYFORMULA(SUM(A10:A13*B10:B13))

That was using ARRAYFORMULA with maths, but let’s use it with VLOOKUP. Let’s say we have a series of data points:

I’m interested in returning the right column, so I would need to set up my VLOOKUP accordingly:

=VLOOKUP(A33,$A$33:$B$42,2,0)

That works, but what if I want to see the list of a – j? Let’s try putting the lookup value as the A33:A42 array:

=VLOOKUP($A$33:$A$42,$A$33:$B$42,2,0)

It still only comes back with the single value. However, if we wrap that entire formula in an ARRAYFORMULA function, it will spit out the entire array for us:

=ARRAYFORMULA(VLOOKUP($A$33:$A$42,$A$33:$B$42,2,0))

10. How to look at Google Sheets like it’s SQL

This is up there with one of my favourite (newly found) datasets: https://github.com/tdmitch/DunderMifflin. Like I said in the past, ‘It seems like SO many people who work in data or IT love The Office.’ And this is how we’re going to start. We’re going to do a brief quality check on this dataset, .txt files, then importing some of them into Google Sheets. I will be importing: ‘Employee’, ‘EmployeeStatus’, and ‘EmployeeLocation’.

First, the ‘employee’ dataset – this is just a copy and paste deal from TextEdit. You can see that each line is printed into an individual cell from A1 down:

Now use the following formula to separate each unique piece of data into an individual cell, and implementing ‘null’ values where there are no pieces of information present:

=ARRAYFORMULA(IF(ISBLANK(SPLIT(A1:A57, “|” , TRUE, FALSE)), “null”, SPLIT(A1:A57, “|”, TRUE, FALSE)))

This Github dataset did not come with a data dictionary, meaning that I am currently unable to marry each column with its intended dataset, i.e., Customers.txt, Orders1.txt, etc. Next, I’ll copy and paste the EmployeeStatus dataset:

Then separate the data out (again):

=ARRAYFORMULA(SPLIT(A120:A124, “|” , TRUE, TRUE))

Next will be the Employee Location table:

And the formula to separate the information out:

=ARRAYFORMULA(SPLIT(A135:A148,“|”,1,1))

Before the next portion of the process, joining the tables, I took liberties and created a Location column in the original dataset and added their location (using my anecdotal knowledge) using the index key value for the first few employees. Next, the information table. This is essentially the “Fact” table of the diagram. I am going to use an INDEX() approach to this part, but include VLOOKUPs and reference the employee status and location tables. For efficiency, I could add defined names to the tables.

I’m only interested in the first 11 people and I want the following columns; First name, last name, status, and location:

So, I’ll create the INDEX function (referencing the original table, looking up the current row and column (with the value of one added due to the columns being offset by one):

=INDEX($A$61:$V$117,$A168,B$167+1)

However, the 20th and 21st columns are reporting numbers for the status and location. So, let’s add the VLOOKUP function to both of these elements. First, the status. I want to lookup this number value that the INDEX function returns within the employee status table (A128:B132) and return the exact value in the 2nd column:

=VLOOKUP(INDEX($A$61:$V$117,$A168,B$167+1),$A$128:$B$132,2,0)

This is the same logic for the location VLOOKUP function:

=IFERROR(VLOOKUP(INDEX($A$61:$V$117,$A168,B$167+1),$A$151:$C$164,2,0),“null”)

Now, the “SQL” version. Firstly, this is how you query a dataset:

=QUERY(A61:V117, “SELECT *”, 0)

This is how you query certain attributes of the dataset:

=QUERY(A61:V117, “SELECT Col3, Col2, Col5”, 0)

The queries get a little tricker when you want to start joining tables together. This is where we start taking advantage of the Google Sheets’ ARRAYFORMULA function.

Joining the first table. This array formula is looking at two sets of data; 1 – QUERY of the initial table (First and Last name), and 2 – the values of the 21st column of the initial table. These values are the lookup value for the VLOOKUP function, which is pointing to the EmployeeStatus table (A128:B132). It will provide the worded description of this lookup and find the exact value. It will not provide a value if there is an error:

=ARRAYFORMULA({

  QUERY($A$61:$V$117, “SELECT Col3, Col2”, 0), 

  IFERROR(VLOOKUP($U$61:$U$117, $A$128:$B$132, 2, FALSE))

})

Joining the second table. This array formula is looking at three sets of data; the first two (as above) plus a third – the 22nd column of the initial table. These values are the lookup value for the (second) VLOOKUP function, which is pointing to the EmployeeLocation table (A151:B164). It will provide the worded description of this lookup and find the exact value. It will provide a ‘null’ value if there is an error:

=ARRAYFORMULA({

 QUERY($A$61:$V$117, “SELECT Col3, Col2”, 0), 

  IFERROR(VLOOKUP($U$61:$U$117, $A$128:$B$132, 2, FALSE))

  IFERROR(VLOOKUP($V$61:$V$117, $A$151:$B$164, 2, FALSE),“null”)

})

That’s how I tend to look at Google Sheets from the SQL perspective, especially when joining tables together.

There’s my top 10 list of NS/FAQ. Is there anything on this list that you already knew? Was there something that has finally allowed you to check something off your to-do list? Let me know in the comments and link me to your work! I’d love to check it out.

Homework

1. Default currency

See if you change your location to Sweden, freak out a little like I did because you realise you can’t read Swedish, observe the Krona symbol in Google Sheets, then change your location back to your desired one.

2. Conditional formatting:

Can you enter a custom formula that will highlight those people who have earned more than an amount you determine in cell D2 and live in Tulsa?

Hint: use the AND() function.

3. Referencing other sheets

Create two sheets – one labelled SheetA and the other SheetB.

Text (Substitute) and Numerical (Average) manipulation.

Substitute the following words, in the following texts, in SheetB into SheetA:

Exchange/SUBSTITUTE “want” for “here”: I want to exchange the word want for here.

Exchange/SUBSTITUTE “here” AND “alibi” for “guilty”: Now, here in this text, I also want to change the word here and alibi to guilty.

Determine the SUM, AVERAGE, and MEDIAN of the following numbers 29, 0, 11, 3, 6, 24, 9, 29. Allow this calculation to automatically update if any of these values change.

4. Remove duplicates

Remove the duplicates from this list:

• Kiera Sosa
• Dulcie Lindsey
• Kian Weeks
• Rio Goodman
• Dulcie Lindsey
• Kian Weeks
• Dulcie Lindsey
• Sandy Koufax
• Sunny Lament

5. List of Unique IDs

Make a list of Unique IDs using ‘Emp#’ for the list of names in the previous question. Hint: You could potentially use the RIGHT, LEN, and SEARCH functions in your solution.

6. Multiple dependent dropdown menus

Make your own dependent dropdown menu using either your own information or the following:

• Hersey
  • Whatchamacallit
  • PayDay
• Nestlé
  • 100 Grand Bar
• Mars Inc.
  • Snickers
  • Mars

7. Pivot tables

Use the following dataset to create your own pivot table: https://teamusa.usahockey.com/page/show/3887046-2018-olympic-winter-games-u-s-women-s-roster

8. REGEXREPLACE

In A1 use this text:

Replace some numbers, like 2, 3, 5, 9, with the letter Y, and replace all ‘e’s with ‘x’s. Also the periods, commas, and inverted commas; no semicolons; with ‘p’s. You’ve essentially made a cipher. How cool!

In A2, follow the instructions from the text in A1 to achieve the following output:

Rxplacx somx numbxrsp likx Yp Yp Yp Yp with thx lxttxr Yp and rxplacx all pxps with pxpsp Also thx pxriodsp commasp and invxrtxd commas; no sxmicolons; with pppsp Youpvx xssxntially madx a ciphxrp How cool!

9. ARRAYFORMULAs

Follow the instructions:

1. Make a 4 x 4 matrix, where 1 is in the top row (for four columns), 2 is in the second row (for four columns), and the same for 3 and 4 in the third and fourth rows.
2. Square each value in the 2nd column.
3. Find the square root for each value in the 3rd column.
4. Divide the last column by a random number between 0 and 4.
5. If there’s an error with any calculation, return “WHOOPSY!” in that cell.

10. View Google Sheets like SQL

Using the data from FAQ7, use the QUERY function (hint, you’ll need two for this one) to get the following columns: Name, Height, Weight, and DOB. Find the BMI for each athlete, Height/((Weight*Weight)/100)*100, then use a VLOOKUP function to place them into one of the 4 categories:

18.5 – Underweight

19 – Healthy Weight

25 – Overweight

30 – Obese