Tag:

spreadsheet

Three AI Tools for Creating Spreadsheet Formulas

by Dr. Bruce Ellis June 12, 2023

written by Dr. Bruce Ellis

If you’re like me, you find yourself using spreadsheets for a multitude of tasks beyond the common ones such as budgeting or inventory management. Spreadsheets empower us to handle data in ways that enhance our efficiency and productivity. When we begin to incorporate formulas into our spreadsheets, they gain new powers and can become even more insightful. However, as you may have noticed, crafting the right formula for a complex task in a spreadsheet — especially if it’s a task that’s new to you or one you haven’t tackled in a while — can be quite challenging. Fortunately, a variety of artificial intelligence (AI) tools have recently been introduced to the market specifically designed to assist spreadsheet enthusiasts in finding the right formulas. Let’s take a look at some AI tools for spreadsheet formulas.

1. Sheet+

Consider Sheet+ this handy AI assistant your new best nerdy friend. The freemium tool gives you five credits each month. But you can jump to the pro account to get unlimited credits and a soon-to-be-released add-on for Google Sheets and/or Excel. While I have been able to stay within the five requests per month, this is one tool that I’d be willing to pay for since I use spreadsheets for so many things.

I have mainly used the formula generator which is where you tell it what you want to accomplish and it generates the formula to make it happen. If you already have a formula and aren’t sure how to tweak it, then check out the formula explainer which will analyze your formula and make it understandable, so you can approach it with confidence. And if you have a formula that doesn’t seem to be working correctly, use the Debugger tool to have it detect and correct any errors in your formula.

Screenshot of Sheet+ tool — Screenshot by author

2. Ajelix

Ajelix is a robust tool for folks who may be working on formulas but may also want to write scripts (Excel VBA Script and Google Apps Script) to extend the functionality of the spreadsheet. An Excel add-on is available for you to download and use. The template generator will generate an Excel spreadsheet template based on your explanation of what you want the sheet to do. You can download the file and open it in Microsoft Excel.

Because it doesn’t have all the data for your spreadsheet, you can expect to tweak the spreadsheet more to get exactly what you want. You are able to preview the template before you download it. If you are hoping to have conditional formulas and dropdowns to choose from data that is in a particular range, then you are exceeding the limits of the template. But you can have Ajelix help with such sub-tasks of developing your spreadsheet by using the Google or Microsoft formula generator.

Screenshot of Ajelix tool — Screenshot by author

ChatGPT

Yes, ChatGPT can even help with crafting formulas for Google Sheets and Microsoft Excel. When using ChatGPT, be sure to declare the role you want your AI assistant to take on. For these purposes, ask it to take on the role of a “Formula Expert” or a “Spreadsheet Consultant.” This will help ChatGPT know how to better guide you. And, as previously mentioned for Sheet+, be sure to specify cell references and/or ranges when you can so that ChatGPT can create accurate formulas tailored to your spreadsheet.

One example of a prompt request might be: “Take on the role of a formula expert for Google Sheets. I will explain what I want the formula to do, and I need you to craft an accurate but efficient formula. I want a formula that concatenates cells A2, B2, and C2. A space should be added between cells A2 and B2. Parenthesis should be added before and after B3. Add a line return after B2. After sharing the formula with me, explain it as if I’m five years old. Do you understand?”

Screenshot of ChatGPT — Screenshot by author

Similar to Ajelix, ChatGPT can also write scripts to add to Google Sheets and Microsoft Excel to automate some tasks. For this type of task, it’s best to use the highest version of ChatGPT available. After having it generate the script, ask it to analyze the output it gave for any errors.

Let us know which of these tools you’ve used to enhance your spreadsheet superpowers. And, if you found a different AI tool you like to help with spreadsheets, let us know of it along with a brief description of why you like it.

June 12, 2023 0 comment

Google Tips and Tricks Microsoft Tips and Tricks

Google Sheets Tip: Conditional Formatting Custom Formulas

by Miguel Guhlin November 2, 2022

written by Miguel Guhlin

Do you ever wish the color of your spreadsheet rows would change color to match the content? If yes, you may enjoy learning about conditional formatting. Conditional formatting is a wonderful feature in Google Sheets and Microsoft Excel. Let’s explore how to use custom formulas that can change the color of cells and rows of data based on the data in a cell.

Diving 🏊‍♂️🥽🌊 into #GoogleSheets with Biology to show how Ss grew from pre- and post-Quarter 1 test using TEKS and a little conditional formatting. Source: Lauren Hawkins’ (@EdTechHawk) Tweet

Meet the Problem

“Oh, I use conditional formatting all the time. But I want to change the colors of the first column when the data in the last column changes. How could I do that?”

It might help to see what this problem looks like. Most spreadsheet users know you can use conditional formatting.

Consider this spreadsheet, which represents a list of workshops and the number of registrants for each course:

Conditional formatting could allow me to change the colors. For example, I could add conditional formatting rules for different conditions. Let’s take a look at examples.

1) Create a sliding color scale.

These settings would adjust the colors of the cells where values appear. For example, the minimum number “0” would be red. The midpoint (“15”) would be yellow, and the maxpoint (“30”) would be green. You can see what that looks like below, as well as take a look at the color scale:

2) Apply single colors.

This is the most often used conditional formatting solution. In this case, you can use a variety of format rules. These format rules say, “Format cells if…” certain conditions are met. Here is the list of most conditions you can apply:

I often use these when getting data from a Google Form, and I want to see how YES or NO responses look.

As you can see in the screenshot above, conditional formatting changes the color of the cell. The colors depend on the value in the cell. In this case, “Yes” is green, and “No” is red.

But what happens when you want the entire row to be green or red based on the YES or NO? That’s where custom formulas come to the rescue.

Introducing Custom Formula

Custom formula makes it easy to apply colors and formatting to an entire row of data. After you highlight the rows you want to apply conditional formatting to, go to Format -> Conditional Formatting. Then, enter a formula into the “Custom Formula” option, as shown below:

You can see that you will need to enter a “value or formula” into the custom formula box:

This will allow you to enter a formula for the conditional formatting rule. In this case, I want two rules to be present:

When the word “Yes” appears in column “C,” the entire row to turns green
When the word “No” appears in column “C,” the entire row to turns red

But how do you do that? What do you type into the custom formula box?

Here’s what that looks like with one custom formula rule applied:

Notice that the $ in the formula applies formatting that matches the row of data where it appears (A2 to C2, A3 to C3). This is referred to as “absolute references” rather than relative references. If I were to not include the dollar sign, only the first column would change color, as you can see below:

To add the red fill color to each cell in a row, I have to add another rule:

Both of the custom formula rules appear below:

When the word “Yes” appears in column “C,” the entire row to turns green: =$C2= “Yes”
When the word “No” appears in column “C,” the entire row to turns red: =$C2= “No”

And that looks like this:

Isn’t that cool?

Want to Learn More?

As a Google Sheets user, I often find myself learning new things about stuff I thought I knew. I’ve been using conditional formatting for years. Yet, I never thought to scroll to the bottom of the formatting rules to see what else was there! Now that you know about custom formulas in conditional formatting, how will you use them?

Learn more about conditional formatting and Google Sheets:

If you don’t use either Google or Microsoft, give OnlyOffice a try. It also offers many features in spreadsheets without the price. You can read my blog entry about OnlyOffice here.

November 2, 2022 0 comment

Google Tips and Tricks

Bend It Like Beckham, er… Google Sheets!

by Miguel Guhlin July 13, 2016

written by Miguel Guhlin

“Bend it like Beckham” is a term that meant little to me as someone not interested in soccer. That is, until I saw an engaging movie with my family a few years ago. One of my favorite movies, Bend It Like Beckam, is described in this way by Wikipedia:

The film is about the 18-year-old daughter of Punjabi Sikhs in London. She is infatuated with football (a.k.a. soccer in the United States) but her parents have forbidden her to play because she is a girl. She joins a local women’s team, which makes its way to the top of the league. Its title refers to the football player David Beckham, and his skill at scoring from free kicks by curling the ball past a wall of defenders.

So what does that have to do with Google Sheets? The idea is that you can use a technology (such as the free Google Sheets) to score and to get the job done, in spite of difficulties and obstacles. At a recent Google Educator Level 1 Certification class I had the opportunity to help facilitate, I had the opportunity to share some of my favorite Google Sheets add-ons and tools. Here is a quick overview of those:

Mail Merge Tools: Looking for a quick way to send out bulk email to others? Take advantage of one or both of these tools, listed in order of preference:

FormMule – Allows you to quickly add create information in Google Sheets, then “email merge” it together. This is a great feature because it allows you to organize the mail merge fields in columns in Google Sheets, then drop the column headers into the text of the email, blending them together. It’s my go-to tool when sending email via Gmail and there is a need to send complex instructions or links that are customized for each individual (represented by a row in the Google Sheet).
Yet Another Mail Merge – Allows you to quickly “spam” or bulk email people.

Styles: This add-on allows you to customize the look and feel of your spreadsheet, adding headers, coloring rows in alternating colors (e.g. row 1 is grey, row 2 is white, row 3 is grey, row 4 is white, and so on). It meets a need those of us who use MS Excel have to quickly customize the look and feel of a table in a spreadsheet. The Styles add-on makes it easy to achieve a similar effect.

QR Code Generator: This is an easy way to generate QR codes for students or to inventory items. One of my favorite uses for this involves creating HTML or web links to various resources with a QR code, then having QR Code Generator auto-create them. After that, it’s just a matter of printing them out on sticky labels or having students stick them on their intended targets. This add-on also makes it easy to create a text label for each QR code generated so you are not left guessing as to what you are looking at, or trying to scan it with your smartphone’s cross-platform (e.g. Android, iOS, Blackberry) Inigma QR Code Reader app.

Split Names: This is an absolute must-have add-on. Although you can use complex formulas to split names (e.g. “Juan Guhlin” to “Juan” and “Guhlin” in different columns), Split Names makes it a cinch. You are also able to separate out salutations and other elements commonly included.

PowerTools: This particular add-on has so much to offer, you probably will want to read the web site for it. Some of its best features include making the process of dealing with extra spaces, formulas and data in the wrong format, inserting or deleting cells and shifting adjacent data, changing case, or swapping values easier. Also, you can click once to AutoSum numbers in every row or column, as well as sum and count cells by color. PowerTools also makes it easier to search all selected sheets for certain values, formulas, notes, and hyperlinks, as well as split values in a column by any delimiter, string, or by position (think Split Names with more features). You can also automate a variety of tasks, and much more.

What are some of YOUR favorite Google Sheets’ add-ons that help you “bend it like Beckham?” That is, get the job done for free? Please share them in the Comments section below.

July 13, 2016 0 comment