Sep
26
Convert rows to columns & columns to rows in Google Sheets – transpose
Do you think your Google Sheets table will do better if you switch columns and rows? Well, you've come to the right place then :) Today, we'll transpose! Continue reading
Posts tagged "Google Sheets functions"
Apr
14
Merge two Google sheets: update cells, add related columns & non-matching rows
Did you know that when you merge 2 Google sheets you can not only update records in one column but also pull whole related columns and even non-matching rows? Today I will show you how it's done with VLOOKUP, INDEX/MATCH, QUERY functions and the Merge Sheets add-on. Continue reading
Mar
18
Merge two Google sheets: lookup, match and update records
Today's blog post features all ways to merge 2 Google Sheets. You will use VLOOKUP, INDEX/MATCH, QUERY and Merge Sheets add-on to update cells in one sheet from records from another one based on matches in common columns. Continue reading
Feb
17
How to find and remove duplicates in Google Sheets
Looking for a simple way to find duplicates in Google Sheets? How about 7 ways? :) That's all you need for numerous use cases :) I will show you how to use formula-free tools (no coding — promise!), conditional formatting and a few easy functions for avid formula fans. Continue reading
Nov
16
How to split one Google sheet or spreadsheet into multiple separate tables or files
When you work with a large Google spreadsheet, you may find yourself constantly filtering the table to get only particular datasets. Wouldn't it be best to split all info into multiple separate sheets or even files in Drive? If so, today I invite you to split your sheets and spreadsheets together. Pick the way you want to get your data and follow the steps described there. Continue reading
Nov
4
CELLCOLOR & VALUESBYCOLORALL: functions for Google Sheets to work with colored cells
This tutorial introduces 2 new functions from our Function by Color add-on for Google Sheets: CELLCOLOR & VALUESBYCOLORALL. Use them to sum & count cells not only by their colors but also by the common contents. Ready-made SUMIFS & COUNTIFS formulas are included ;) Continue reading
Jul
22
Find and replace Google Sheets data and special characters
Getting tired of all those smart quotes, accented letters, and other unwanted special characters in Google Sheets? We have a few ideas on how to replace them effortlessly. Continue reading
Jul
8
Extract data from Google Sheets cells: text, numbers, URLs, hyperlinks, email addresses, dates, and time
This next bit of our operations with text in spreadsheets is devoted to extraction. Find out ways to extract various data – text, characters, numbers, URLs, email addresses, date & time, etc. – from various positions in multiple Google Sheets cells at once. Continue reading
Apr
29
Google Sheets: remove the same text or certain characters from multiple cells at once
Learn formulas and formula-free ways for Google Sheets to trim whitespaces, remove special symbols (even the first/last N characters) and the same text strings before/after certain chars from multiple cells at once. Continue reading
Apr
15
Add text to multiple cells in Google Sheets at the same position
This tutorial will teach you to add text in Google Sheets cells at the same position of all cells at once: at the beginning, at the end, after the N-th character, before or after particular characters. Continue reading
Feb
23
How to change case in Google Sheets: UPPER, lower, Capitalize Each Word, Sentence case, tOGGLE
Changing text case in Google Sheets may seem an intricate task as there is no such option in the standard menu. Even so, the task is very much resolvable. In this blog post, I share different ways to capitalize your words or turn them into lowercase, uppercase, and sentence case. Continue reading
Sep
3
Count words and characters in Google Sheets: LEN function
Though word and character count in Google Sheets are used in rare cases, it is still the functionality that some of us expect to see right in the menu. But unlike Google Docs, for Google Sheets, it is the LEN function that does that. Continue reading
Aug
18
Calculate days, months and years between two dates in Google Sheets: DATEDIF formulas
Today's blog post is all about figuring out the difference between two dates in Google Sheets. Come find lots of DATEDIF formulas to count days, months and years, and learn how NETWORKDAYS is used to count only workdays even if your holidays are based on a custom schedule. Continue reading
Feb
4
INDEX MATCH in Google Sheets – flexible Vlookup for your spreadsheets
When you need to find data in your sheet that corresponds to a certain key record, it is usually Google Sheets VLOOKUP you turn to. But there you go: VLOOKUP slaps you with limitations almost immediately. That's why you'd better increase the resources for the task by learning INDEX MATCH. Continue reading
Jan
28
Google Sheets QUERY function: a spreadsheet cure-all you have yet to discover
If you've been following this blog for a while, you may remember QUERY. I mentioned it as a possible solution for a couple of cases. But those are far from enough to uncover its full potential. Today, it's high time we get to know this spreadsheets superhero properly. And guess what – one equally noteworthy tool will also be there :) Continue reading
Jan
16
Google Sheets FILTER function: formulas and tools to filter data in spreadsheets
If the only way you know of creating a filter in Google Sheets is the standard tool, I have a surprise for you. :) Come explore the FILTER function with me. There are plenty of ready-made formulas you can borrow, along with a new powerful tool that complements the filtering toolset immensely. Continue reading
Oct
8
How to insert Google Sheets checkmarks and cross marks
Lists help us put things in order. Stuff to buy, tasks to solve, places to visit, movies to watch, books to read, people to invite, video games to play - everything around us is practically filled with those lists. And if you use Google Sheets, chances are that it would be best to track your endeavours there. Let's see what instruments spreadsheets offer for the task. Continue reading
Sep
26
Useful Google Sheets functions that don't exist in Excel
This blog post brings the majority of those Google Sheets functions that Excel doesn't have to your attention. They are conveniently classified by Google based on their primary task. So just pick the group from the table of contents and you'll find their descriptions with the simplest examples. Continue reading
Aug
22
How to merge several Google sheets into one without copy-pasting
Every once in a while each Google Sheets user faces the inevitable: combining several sheets into one. Copy-pasting is tedious and time-consuming, so there has to be another way. And you're right - there are several ways, in fact. So get your tables ready and follow the steps from this article. Continue reading
Aug
13
How to change Google Sheets date format and convert dates to numbers and text
In this guide, you'll find out how Google stores dates and how you can format them for your better convenience. Some date formats are offered to you by spreadsheets while others should be created from scratch. There are even a couple of handy functions for the task. I also describe a couple of ways to convert your dates to numbers and text if necessary. Continue reading
Aug
6
Google Sheets formulas for 12 most useful Google Sheets functions
This time we decided to provide you with the most simple Google Sheets functions that you definitely need to learn. They will not only help you with the plain calculations but also contribute to extending your knowledge of building Google Sheets formulas. Continue reading
Jul
23
CONCATENATE function: formulas to merge cells in Google Sheets
"Concatenate" usually means to link something together in a series or a chain. This operation is used in Google spreadsheets whenever you need to join records from multiple cells. This article collects the most popular and easy solutions to help you solve the concatenation puzzle. Continue reading
Apr
30
How to compare data in two Google sheets or columns
Whether there's summer knocking on our doors or winter invading Westeros, we still work in Google Sheets and have to compare different pieces of tables with one another. In this article, I'm sharing ways of matching your data and giving away tips on doing that swiftly. Continue reading
Apr
18
Merge data from duplicate rows in Google Sheets based on a unique column
Combining duplicate rows in your spreadsheets may turn to one of the most intricate tasks. Let's see what Google formulas can help and get to know one smart add-on that does all the job for you. Continue reading
Apr
2
Google Sheets: everyday formulas examples
Google Sheets is a great platform to manage data tables. But are there any easy functions you can use for daily calculations? Find out inside. Continue reading
Mar
26
Back to Basics: create and edit Google Sheets formulas
After reading this article, you’ll be able to make and edit your own simple Google Sheets formulas. Here you’ll find examples of nested functions and a few tips on how to swiftly copy a formula to other cells. Continue reading
Mar
19
Back to Basics: formulas in Google Sheets
Today I'm going to bring formulas in Google Sheets to the table. I'll start with the elements they consist of, remind you how they are calculated, and tell a difference between plain and complex formulas. Continue reading
May
17
If VLOOKUP in Google Sheets is not working
How to do a vlookup without errors in Google Sheets? Take a closer look at the most common mistakes made when working with the VLOOKUP function. Continue reading
Mar
21
SUMIFS in Google Sheets to sum cells with multiple criteria (AND / OR logic)
Google Spreadsheets provide two functions to conditionally sum cells, SUMIF and SUMIFS. Regular visitors of our blog are already familiar with the SUMIF function that we covered last week, and today we are going to have a closer look at its plural version. Continue reading
Mar
15
SUMIF in Google Sheets with formula examples
Google Sheets has two functions to add up numbers based on conditions: SUMIF and SUMIFS. The former evaluates just one condition while the latter can test multiple conditions at a time. In this tutorial, we will focus solely on the SUMIF function. Continue reading
