GOOGLE SHEETS – ADVANCED TIPS AND TRICKS | Day 21: HLOOKUP

We are well into our Google Sheets tips and tricks series and will now explore high-level functions and formulas that data analysts need to be familiar with. Last week, we explored VLOOKUP or vertical lookup, where we searched for matches in columns from left to right. Today, we will explore HLOOKUP or horizontal lookup, which returns matches in rows, from top to bottom.

Why do we need a horizontal lookup in spreadsheets? Well, it depends on the data that you analyse. When booking a flight, we do lookups automatically in our head: we know our origin and destination city and we want the search results to list flights based on the airline, departure/arrival time, or the fare. HLOOKUP will help us do something similar.

In HLOOKUP function, the “value” the user wants must correspond to the ‘search key’ in the top-most row of the table. Let’s look at the screenshot below, which shows the origin city, destination city, and airlines flying to and fro from these cities. In this example, we will use HLOOKUP to know which airline (value) is flying from the origin city (search key).

Usage:

Formula: HLOOKUP(search_key, range, index, [is_sorted])

search_key: The search key is the main thing you want to search in a table. In this case, the search key will be the Origin city in row 2

range: This is the range within which we will search. In this case, we will take rows 2, 3, and 4, and columns A:G, basically our whole table.

index: This is the row number where the output is given. In this case, the airline is in row 4, but since we started the table from row 2, the index # becomes 3. Think of it this way Row 2 = 1, Row 3 = 2, Row 4 = 3.

index numbering: The index numbering will always start from where you select the table. If we had selected the table from Row 1 then the index # would have been 4. Here is how: Row 1 = 1, Row 2 = 2, Row 3 = 3, Row 4 = 4.

is_sorted: You have two options – true or false. FALSE means you want to find an exact match. TRUE will give you an approximate match.

Let’s try to get our formula in cell J4 like it is shown in the screenshot

Let’s decode the formula: HLOOKUP(J2,B2:G4,3,FALSE)

• J2 is London from Row 2 and our search key
• B2 to G4 is the table range where Airline is present for each city
• 3 is the index number, in this case, Name of the airline (Row 4)
• FALSE – We want the exact match

If this is still looking too complex, look at the screenshot below.

You can change the city name in cell J2 and your answer will automatically change. If you put a city that does not exist, then you will get #N/A.

Go ahead and try this exercise on Google Sheets. You can replace the city names and airlines with anything else. Let me know in the comments below if you need help with this formula.

GOOGLE SHEETS – ADVANCED TIPS AND TRICKS | DAY 22: IF FORMULA

In today’s post, we will look at the function of the ‘IF’ formula in Google Sheets. A lot of times, we want results in binary (yes or no). It’s kind of like how a legal expert might interrogate a witness in the courtroom and expect her to answer questions with a simple yes or no. The truth could be nuanced and full of complexities, but the facts may be expressed in binary. It’s no different with data! A marketing head who is casually looking at a budget report might quickly want to know if the department has crossed a particular budget threshold across different advertising channels. A school teacher might want to know if students in a particular batch have secured first class or failed (either this or that). And in the era of COVID-19, data scientists would fill up entire spreadsheets with data, but a government analyst will quickly want to know if it’s safe for the lockdown to be lifted or not in specific localities.

Let’s take the class report card example. The screenshot below shows a list of students and the percentage they scored. We want to know how many of the students in this batch have achieved First Class (<70%). We could manually calculate this, but imagine if a batch consists of 500 students across different classes! The IF conditional formula will help us pull up this data instantly. We can even add variations: number of students who scored more that 70% will be classified as Distinction.

Syntax: What does the IF formula consist of

IF(logical_expression, value_if_true, value_if_false)

=IF(C2<70%,“First Class”,“Distinction”)

• logical_expression – an expression or reference to a cell containing an expression that evaluates to either TRUE or FALSE.
• value_if_true – the value that the formula returns if the logical_expression evaluates as TRUE. This can be a number, text, or even another formula that returns a value.
• value_if_false – this is the value that the IF formula returns if the logical_expression evaluates as FALSE. Similar to the value_if_true, this can be a number, text or another formula that returns a value.
• Adding the value_if_false condition is optional and we can skip this. In that case, we will only get the data for the value_if_true field and the second value will be blank.

Let’s apply the IF Formula in Cell D2 as seen in the screenshot below.

=IF(C2<70%,“First Class”,“Distinction”)

• C2<70%: We are checking if the score is less than 70% since all the scores are in Column C
• First Class: The value is given in double quotes since it’s a text output. This is the value that will be shown if the result is true. i.e. the score is less than 70%
• Distinction: The value is given in double quotes since it’s a text output. This is the value that will be shown if the result is false. i.e. the score is greater than 70%

Now copy the same formula across all the cells in Column D and see if the result matches what is given in the screenshot below.

Things to remember:

• If the text is being used as the argument, then it must be enclosed in double-quotes.
• The last argument (value_if_false) is optional and if left blank will produce a blank result

There you have it, a simple but powerful IF formula usage! In the next Google Sheets advanced formulas post, we will look at the different variations of the IF formula like using it with logical operators like AND or OR. For example: If students score greater than 70%, it is Distinction AND if it is less than 60%, it would be classifed as Second Class.

Look forward to your comments.

GOOGLE SHEETS – ADVANCED TIPS AND TRICKS | DAY 20: VLOOKUP

Many of you won’t believe me when I say this, but most of us have probably been using a bunch of Google Sheets and Excel formulas in our day-to-day life. Shopping for clothes and accessories on your favorite e-commerce site? You probably apply Filtering to look for a particular item of clothing and then go through the drop-down for a specific price range. We rely on Sorting while shopping at a supermarket, comparing the price of gluten-free biscuits with organic kale chips (high to higher). These formulas are nothing but mental shortcuts that allow us to arrive at conclusions and make decisions faster.

Here’s a fun exercise for my readers: Come up with some real-life situations or examples of where you see yourself using Google Sheets formulas or tools for pivot tables, graphs, summation, frequency counting, and protecting your cells and sheets?

Demystifying VLOOKUP

When working with a lot of data, one of the challenges is finding information across multiple tables and combining them in your output. VLOOKUP is a very powerful formula that allows you to cross-reference value from one column against a search key in another column.  Let me demonstrate this with an example. When you log on to a travel portal to book flights online, you will want to check the departure and arrival time of your preferred flight. There’s a lot of complex and multi-layered VLOOKUP queries being run in the background so you get the list of flights in the Arrival and Departure columns. That’s the magic of this formula!

How VLOOKUP Works

Let me highlight another example where you can apply the logic of VLOOKUP in real life. Say you visit a restaurant and browse through a big menu. The table of contents is a group of items (Appetizer, Starter, Main Course, Dessert, Beverages) with page numbers alongside. You will need to go to the individual sections to further look for items within each course and check the price of each item. Basically, you are cross-referencing the individual items to the broad categories in the table of contents. If this were a menu on Google Sheets, you would use VLOOKUP to cross-reference value from Column 1 (Table of Content in one table) against Column 2 (menu items in another table).

A lot of people think that VLOOKUP is a very complex function but it’s not that hard once you understand the basics of what you need to do. VLOOKUP function can be performed across a single sheet, multiple tabs in a sheet or multiple sheets as well.

Using VLOOKUP

In the screenshot below, you will see two tables: Table A and Table B.

• Table A contains the employee code, department, and salary.
• Table B contains only employee code and employee name.

Now I want to see the salary for each employee in a new column, column H. Sure, you could think why don’t we just copy-paste this data? For a simple table like this, copy-paste would be easy. However, if the data were in different tabs or another Google Sheets, or the table had thousands of rows, you wouldn’t want to spend time copy-pasting!

This is where we get VLOOKUP to work for us.

Usage

Formula: VLOOKUP(search_key, range, index, [is_sorted]

Let’s try the formula in cell H3 for getting salary details for EMP001 (Vincent). In the screenshot below, I have highlighted the formula.

H3 = VLOOKUP($F3,$B$2:$D$19,3,FALSE)

Let’s break down this formula H3 = VLOOKUP($F3,$B$2:$D$19,3,FALSE)

• F3 is EMP001 in Table A.
• B2 to D19 is the table range where salary is present for each employee
• 3 is the index number, in this case, Salary (column D) is column no 3
• FALSE – We want the exact match

search_key: The search key is the main thing you want to search in a table. In this case, the search key will be the employee code since that’s the common thing in both the tables

range: This is the range where we want to search. In this case, we will take Table A and columns B, C, and D. The reason we want to start from column B is that the employee code starts from that column

index: This is the column number where the output is given. In this case, the salary is in column D. But since we started the table from column B the index # becomes 3. Think of it this way B = 1, C=2, D=3.

index numbering: The index numbering will always start from where you select the table. If we had selected the table from column A then the index # would have been 4. Here is how: A=1, B=2, C=3, D=4.

is_sorted: You have two options – true or false. FALSE means you want to find an exact match. TRUE will give you an approximate match.

Notice that we have got dollar signs ($) against some of the values. $ locks the cell or the range in place. This is pretty useful if you want to copy the same formula across different cells. In our case, in the first value of the formula ($F3), we have locked only the column F but not the row.

In the case of the range ($B$2:$D$19), we have locked the start and the endpoint of the range. This is important otherwise the range will keep on shifting automatically when the formula is copied to a different cell.

If this is still looking too complex, look at the screenshot below.

Now copy (drag) the formula to the other cells in column H. You should get the result as shown in the screenshot below.

Things to remember when doing VLOOKUP

• Google Sheets VLOOKUP cannot look at its left, it always searches in the first (leftmost) column of the range.
• VLOOKUP in Google Sheets is case-insensitive, meaning it does not distinguish lowercase and uppercase characters.
• When is_sorted is set to TRUE or omitted (VLOOKUP with the closest match), remember to sort the first column of range in ascending order.
• If there are multiple matches VLOOKUP will match with the first instance that it finds in the table

I hope it has been easy to understand how VLOOKUP and other Google Sheets formulas can be used in different situations and contexts based on the examples I shared. Don’t let formulas scare you off data and don’t let data turn you off from mastering spreadsheets. Formulas are just shortcuts to help you manipulate data, but they are secondary. The primary goal of mastering a spreadsheet is to convey something meaningful with your data. There’s beauty in that!

GOOGLE SHEETS – TIPS AND TRICKS | DAY 19: CONDITIONAL FORMATTING

As we dive deeper into the Google Sheets tips and tricks series, you will see that the focus has shifted from how to manipulate data to how we can visualize data. Let’s dig into this term: visualize. What does it imply? Your raw or unstructured data — data that is not cleaned up or validated yet, is just a bunch of numbers and metrics. Unless you add context to this data, whether in Excel or Google Sheets, it’s useless. In written English, for instance, we use punctuations, capitals, or bold/Italics to lend meaning and context to a statement. Even when we speak, we use formatting: we pitch our voice, modulate our tone, add pauses while talking, and use gestures and emphasis to convey meaning!

What is Your Data Saying?

Similarly, visualizing how you want to structure your data is one of the most important steps in data analysis. This is where conditional formatting can help! I am sure many of you would have seen colour-coded Excel and Google Sheets with fonts in a certain style, headers in different font sizes, and cells in another colour? It’s all thanks to conditional formatting. Take a look at the screenshot below. Every second row and all the columns are in different colours. Instead of manually selecting each row or column and changing the colour, the conditional formatting tool will allow you to do all this in a simple way.

Importance of Context

Google Sheets allows you to add context or highlight sections, cells, columns, rows, and results in your spreadsheet through conditional formatting. Say you want to highlight critical rows throughout the sheet in one colour and format, the header text in a different colour and font style, and the rest of the sheet to be neutral. With conditional formatting, you can change all these aspects, such as background colour as well as text font size, style and colour based on the rules set by you.

Conditional formatting allows you to highlight cells or rows that meet certain criteria and help you better understand spreadsheets at a glance.

Using Conditional Formatting

The screenshot below shows the salaries of 17 employees from various departments (HR, IT, Admin, Operations). Say we want to highlight row 1 to 17 in column D in a particular colour. Easy-easy, right? Just select these cells and click on Fill colour.

Example 1

Let’s make this complex. What if we wanted to highlight only those cells in the salary column where the figure is higher than 60,000? See the screenshot below. We will use the conditional formatting tool to make this happen.

Usage

• Choose all the data in the sheet
• Go to Format in the menu and select Conditional Formatting
• Look at the below screenshot

• Conditional format rules editor box opens up on the right side of the sheet

• Go to the Format rules Dropdown in the editor and select Greater than option as shown in the screenshot below

• Type the number 60,000 as we want to check all values greater than 60,000 (screenshot below)
• The background colour changer option “Formatting style” is below the Format rules option. Choose any colour you want through the Fill icon. I went with saffron.
• Click Done
• Column D (Salary) will now highlight the cells with figures greater than 60,000 (screenshot below)

Example 2: Highlight specific rows

If you scroll all the way to the beginning of this post, I began with an example of a screenshot with different rows and columns highlighted in different colours. Conditional formatting makes this an easy exercise. Let’s begin by applying the formula to highlight specific rows in a spreadsheet, based on certain criteria.

Take a look at the screenshot below. This is what we want to achieve: highlight entire rows with the word HR in it.

Usage

• Choose the whole table. Select Conditional Formatting from the Format tab
• Conditional format rules editor box opens up on the right side of the sheet
• In the Rules box select Custom Formula. It’s all the way at the bottom of the drop-down (screenshot below)
• We need to use Custom formula since we want to highlight entire rows that meet specific criteria. In our case, rows that contain the word HR.

• Below the Custom formula field is a blank box
• Type =$C2=”HR”
  • Since we are looking for rows with the word HR, we ask Google Sheets to look at Column C
  • $ signs locks the C column in place
  • When you applying this formula, Google Sheets will automatically highlight every subsequent row with the word HR. You don’t have to manually input additional row numbers.
• Choose the background colour for your rows. Have a look at the screenshot below to see if your steps are matching the ones written in this blog

You could apply complex formatting rules to your spreadsheet by selecting different criteria in the Conditional Rules editor. Just be sure of what you want the data to do for you and select the right values and parameters, Google Sheets will take care of the rest! Hope you found this post valuable. Go ahead and give this formula a try and let me know if you need help.

 

GOOGLE SHEETS – TIPS AND TRICKS | DAY 18: PIVOT TABLES

When I began the Google Sheets tips and tricks series, my intention was to create a step-by-step tutorial for understanding and applying functional formulas and tools in a spreadsheet. Halfway through this series, we have explored how data needs to be clean before anything else can be expected from it. We also saw data being frozen, filtered, sorted, locked, imported, in short, everything possible to manipulate and break it down into bite-sized portions.

Why I Love Pivot Tables

I know there are many data analysts who appreciate the simple and intuitive nature of Google Sheets as it helps us transform data into stories. The real magic happens behind the scenes! As I explained in my last post on plotting a graph, the fancy scatter, doughnut, pie, and bar graphs are just representations of a narrative that you control. Charts or graphs are visual representations of data. What if you want a numerical representation or summary of your data? In today’s post, we will explore how pivot tables work and how to create one.

Pivot tables summarise large amounts of data in a dashboard format without adding a lot of formulas.

Let’s look at the screenshot below. The three columns show a list of crimes, the months in which the crimes occurred, and the number of crimes. Our favourite crime-fighting police chief who loves his crime stats, would like to transform this unstructured sheet (the type of crime and months repeat) into an easy-to-read table that summarises the types and number of crimes across each month.

Before: Unstructured Data

Now, we could use various formulas like sumifs (to add up the total number of each type of crime) and countifs (to count the instances of a particular crime and the months in which it occurs) to analyse this data, but that is a pretty time-consuming and long-winded route to summarizing the data! A simpler way to summarise the type of crime (along rows) and the months (across columns) is to use the Pivot table feature.

After: Structured Data

How to Create a Pivot Table

• Select columns B, C, and D
• Go to Data in the main menu tab (screenshot below).
• Select Pivot table

• The Create pivot table menu pops up (screenshot below)
• Select New sheet to create the Pivot table
• Click Create

• The sheet (tab) that opens up is called Pivot Table
• The headers (rows, columns and values) are marked, we only need to fill in the data
• Look at the Pivot table editor box on the right (screenshot below)

• Under Rows, we want a list of crimes. Let’s see how to do this (screenshots below)
• Click Add next to the Rows and select Crime from the drop-down list
• Under Columns, we want a list of months
• Click Add next to the Columns and select Month from the drop-down list

• You should see a table with the type of crimes and months along the rows and columns, but with no values filled in for each month (screenshot below)
• If you don’t get a table like this, then you need to go back to the Pivot table editor and repeat the steps

• Now we need to add values to this table (screenshot below)
• The Pivot table editor box will still be open
• Below Rows and Columns, you will see Values. Click Add
• In the drop-down list, click on Number of crimes

• If you have done everything right to this point, all the values should show up in the Pivot table (screenshot below)
• The values are the addition of all the crimes and distributed as per the month

We are not done yet!

• You can also calculate the average of crimes across different months or compute various mathematical operations in this Pivot table.
• Go to the Values section of the editor
• Click the SUM drop-down (screenshot below)

• Depending on what you choose the numbers in the pivot table will change accordingly
• For example, if you choose average, then you will get the average of each crime as per the month in the Pivot table
• You can also convert the numbers to percentages automatically
• Next to SUM drop-down, there is a drop-down called Show as (screenshot below)

• If you have done all the steps correctly your pivot table should look like the result below
• All the numbers are converted to a percentage of the total

Post Script

Creating a Pivot table and plotting a graph from it are easy outcomes. The difficult bit is figuring out what you want from the raw data. Once you know that, it’s as simple as clicking on the Data menu option and Pivoting. What has been a guiding star for me when I look at unstructured data is the narrative I would like to be able to spin for the client/vendor/customer/team/boss. What do they need to know that will be game-changing for their business or project? What data points can I summarise in order for them to take critical decisions? Will it impact their analysis if I don’t show the results of a particular data set? Begin with these questions, then filter and proceed!

 

 

GOOGLE SHEETS – TIPS AND TRICKS | DAY 17: CREATING GRAPHS

Any data set you have is only as good as the interpretation you lend it. You wouldn’t want your teams, client or a customer to see raw or unstructured data that lists various things without giving it a recognizable shape, would you? I equate this structure to storytelling. Your structured data conveys a story about a project, season, campaign, performance, timeline, or budget across a multitude of domains and sectors. Data is a powerful tool to weave a compelling narrative about things you want to highlight and at the same time, underplay aspects that you wish to keep subtle or hidden.

Dazzled by Data

We are all dazzled by data visualization tools and infographics that percolate the media. Infographics are nothing but the information presented in graphical format! The graphics are just the outcome; the real work (hours of labor) is the research from various sources that data visualizers have to sift and filter to come up with a meaningful interpretation for us – the audience.

Data as Narrative

In the Google Sheets tips and tricks series, we have learned how to do various kinds of stuff with data, including conditional addition, frequency counting, filtering, sorting, and most recently, how to protect the sheet. So far, all of these formulas and functions allowed us to manipulate and represent data in non-graphical formats. In today’s post, I am not going to dazzle you with fancy infographics (you will need to be pretty nimble with graphic design software such as Photoshop and Illustrator for that), but we will look at easy ways to showcase complex data in an impactful manner: through graphs.

Why do we need graphs? Graphs are visual summaries of our data. It’s much easier to find meaning in a beautifully illustrated pie chart or bar graph than a list of rows and columns under bold headers. Like they say, a picture is worth a thousand words!

Creating a Graph

Let’s take the example of a marketing manager who has received this quarter’s budget from her boss for the months of April, May, and June allocated for digital ads in various social media channels. Take a look at the screenshot below.

How do we go about visually representing this data in a graph?

• Select the whole table
• Click on the graph icon on the formatting menu tab on the far right of the Google Sheets (in the screenshot above, a red arrow points to the icon)
• Or go the Insert tab on the Main menu and select Chart as shown in the image

• If you did the above steps correctly you should see the image with the graph ready. Simple? So far, so good!

• Hold on, we are not done yet since this graph doesn’t show us the value of each bar.
• Let’s go to the Graph editor (right bar in the screenshot above)
• See Chart Type with a drop-down menu (zoomed into the chart editor menu)

• Google Sheets gives suggestion on the best chart type for the data you have selected
• Let’s keep the default Column chart in this case
• Look at the screenshot below

• The data range is the table we have selected in our example
• The X-axis is column A which contains the line item Facebook, Instagram, Linkedin, and Twitter Ads
• Look further in the screenshot below as you scroll down the graph editor

• Series are the columns B, C, and D which contain the data for April, May, and June
• You should click the checkbox which says Use row 1 as headers since our row 1 contains the months
• We are using column A as labels so click the checkbox for that as well

---

Customizing the Graph

• Click on customize on the chart editor and you will see the menu options as below

• In the screenshot below, we have clicked on Chart Style and we see a few options. I always prefer choosing 3D as it gives depth to the graphs. Select the checkbox as shown in the image below
• The other options exist by default, we don’t need to touch them

• The next one is Chart title. This is where we can choose the title of the graph and make changes to the font (size and style)
• I chose to name my graph as Social Media Spends as shown below

• Everything else above will be set at default
• The next one we need to do is Series

• Click on Data labels to allow values to be shown on the graph. The options below it allow us to make changes to the font
• The last part is the Legend. Click on it for menu options

• If you click on position it’s by default in Auto, but if you click the drop-down you can position the legend on the top, right, or left side
• Here, the legend is the months of April, May, and June as seen in the graph below

As I said in the beginning of this post, mastering the tools to create graphs – bar diagrams, pie charts, line graphs, scatter plot – will allow you to gain mastery over how you tell a story. You control the narrative and present data as per the situation, audience or the needs of your client. So go ahead and plot yourself a graph! Let me know in the comments below where you need help and I will be happy to answer your questions.

 

 

 

GOOGLE SHEETS – TIPS AND TRICKS | DAY 16: PROTECTING CELLS AND SHEETS

We are halfway through the Google Sheets tips and tricks series and we have learned how to manipulate data through validation, as well as apply formulas to sort, filter, and combine data. So far, we were going by the assumption that you alone would be in-charge of the Google Sheets or your department’s data. But what if you have to share the sheets within the team, or with another department, or even externally, with a sales, audit, or a financial services vendor who needs to input their data in specific columns/rows or cells? This is the norm in some organizations or start-ups and I know most of us would just make multiple copies of the Google Sheets – one as the master copy, a second for internal circulation, and another for external purpose. But that’s just not practical, is it?

Protecting the sheet

In today’s post, we will explore how you can protect cells and entire sheets in Google Sheets as per your needs. The main purpose of this tool is to protect the formulas you have applied in the Google Sheets. It’s similar to how we would ‘lock’ our mobile phones or keep the phone unlocked, but apply ‘password protect’ to certain apps (like our email account, bank and payments apps, and WhatsApp/Facebook, etc).

Let’s take another example of why you would need to protect certain cells or sheets. Say you are the marketing head of your company and want your team to fill in specific rows and columns of the quarterly marketing budget mastersheet (social media, paid search, display, influencer marketing, and so on, all of which might be handled by different employees within the marketing team). You have pre-populated the mastersheet with formulas that add up to the total budget in column X. There are chances that someone could edit the formula by mistake and change the entire calculation or output.

Learning by example

Let’s look at the screenshot below, which highlights the budgets across different marketing activities for April, May and June. The marketing head wants her team to fill in the data for the individual months. We want to protect the whole sheet (data on view) but allow only certain cells to be edited. This helps us keep the formula applied in column E safe.

The cells in Column E are the addition of each line item of columns B, C, and D (E4 is the addition of B4 + C4+ D4) and so on. If you don’t remember the sum or addition formula you can refer to it in this Google Sheets post.

Apply the protect formula in Google Sheets

Click anywhere on the sheet and go to Data in the tab menu and then click Protected Sheet and Ranges (right at the bottom) as shown in the image below.

• After clicking, you will see a sidebar to the right of your window
• Click ‘Add a sheet or range’ as shown in the image below

• You will see the additional option to choose between Range or Sheet. Click ‘Sheet’. This option allows you to choose the entire sheet
• Click Set permissions (chose if the collaborators can edit the sheet or only you) as shown in the image below.

• Now we need to allow the marketing team to fill in the ad expenditure for the months of April, May, and June in columns B, C, and D. Let us give them the permissions.
• If you see in the image below, there is a checkbox ticked next to ‘Except certain cells’
• Let’s click on that and see what happens

• I want the marketing team to be able to fill in the data from B4:D5 hence I have chosen that range as you see in the image below.
• Because we want the team to fill all the other data in columns B, C and D we will add the subsequent ranges as shown in the image below.

• Once done, click Ok
• Below OK you will see Change permissions. Let’s click that and see what happens

• You can choose to be the only editor or you can click on Custom and invite other collaborators and set their view, edit permissions.

Protecting Cells in Google Sheets

Suppose we wanted to protect only certain cells and not the whole sheet. Let’s look at the same example of filling up the marketing sheet (screenshot below). Here, we want to protect only row 13, which is the sum of each month in cells B13, C13, D13, and E13.

• Click anywhere on the sheet and go to Data in the main menu tab and then click on the ‘Protect sheets and ranges’ option as shown in the screenshot below.

• This time, instead of clicking on ‘Sheet,’ click on ‘Range’. In our case, it will be from A13:E13. Take a look at the screenshot below.

• Once you click OK you will get the option to set permissions.
• Just like in the earlier example, you can set permissions for your collaborators or only for yourself, depending on the requirement.

That’s how you can protect cells or the entire sheet in Google Sheets using the Protect sheets and ranges tool. I hope the detailed guidance and screenshots of every step towards applying the formulas and functions are proving to be helpful? Go ahead and practice this and let me know in the comments if there’s anything I can help you with!

GOOGLE SHEETS – TIPS AND TRICKS | DAY 15: COMBINING DATA #CONCATENATE

Many of the formulas or tools that we use in Google Sheets is directly based on the meaning or definition of the function in the English language. In yesterday’s post, we explored how the ‘sorting’ tool works: arranging data in order, either descending or ascending, which is exactly what sorting means! In an earlier post, we saw how the ‘freezing’ tool literally helped us freeze a row or/and column, while the rest of the sheet became scrollable. In much the same way, in the post about how to use the ‘filtering’ tool in Google Sheets, we saw how we can highlight only the necessary data. So, doesn’t it seem like we are brushing up our English vocabulary along with our data literacy skills?

On Day 14 of the Google Sheets tips and tricks series, in today’s post, we will explore a tongue-twister of a formula: concatenate (pronounciation: cun-cat-uh-nate), which literally means joining or combining two things. In technical terms, the concatenate function joins together any set of data across different columns and rows. In simple terms, data in cell a and data in cell b can be combined together to be displayed in cell c.

Why do we need concatenate? Data that you get in its raw form is not always structured. Often, you may want to split the content of one cell into individual cells, or do the opposite, combine data from two or more cells or columns/rows into a single column/row.

Common examples that require the use of the concatenation formula in Google Sheets are joining columns of first names and last names (would be useful for the HR department), or combine the name column with the address, displaying date and time together in the desired format (05 May 2020 08:40 AM), to name a few.

Let us look at an easy example first where we use the concatenate formula to join two columns comprising first names and last names in the Google Sheets screenshot below (we call this unstructured data). The first names are in column B and last names are in column C.

Usage: Combine data from two columns (without any spaces)

• =concatenate(string1, string2)

It doesn’t matter whether you write concatenate in capitals or title case, Google Sheets will automatically change it to all caps. Let’s see what we get when we join cell B2 (Paul) and cell C2 (Smith) with the result in cell D2 (see screenshot below).

Usage: Combine data in two cells (with spaces

But this doesn’t feel right as there needs to be a delimiter (space) between both the words. The next step in concatenate is using delimiters such as space to differentiate two values, words, or strings.

Let us look at the screenshot below where we have added space between two words/values by editing the concatenate formula.

Here, we join cells B3 (Robert) and C3 (Frost) and add a space between them (Robert Frost). The formula is applied in cell D3: =concatenate(B3,” “,C3) 

Explanation:

If you noticed, we changed the formula slightly.

The original formula was =concatenate(string1,string2). 

The revised formula is =concatenate(string1,” “,string2) 

• Here is how we did it.
  • Double quotes between B3 and C3 in the formula is actually one space
  • You can use any delimiter you want within the double quotes (space, colon, semi-colon)

Usage: With a delimiter (comma, colon)

Suppose you want to combine the names the other way round by placing the last name at the beginning and first name at the end. Also, you want to separate the names with a comma. Let’s look at the screenshot below where Emily Dickinson is combined in column D as Dickinson,Emily. We applied the concatenate formula in cell D4: =concatenate(C4,”,”,B4)

• We have taken the last name in cell C4 (Dickinson) and the first name in cell B4 (Emily) and separated both names with a comma and no space
• If you notice the only thing we did differently in this version of the concatenate formula was place cell C4 first, added the comma in double quotes and then placed cell B4.

Things to remember when using the CONCATENATE formula:

• Concatenate formula requires at least one text argument to work (one of the fields can’t be left blank).
• If at least one of the concatenate function’s arguments is invalid, the formula returns a #VALUE! error
• The result of the concatenate function is always a text string, even when all of the source values are numbers (if you have numbers in any cells, they will not be added automatically with any other formula. They need to be converted to a number).

There you have it – three different ways in which you can use the concatenate formula in Google Sheets to combine data! Go ahead, give it a try at home/office and let me know through the comments if you faced any issues.

P.S. Well done if you recognized the names of the English poets and authors featured in the first screenshot of this post!

GOOGLE SHEETS – TIPS AND TRICKS | DAY 14: SORTING DATA

Less than two weeks into our Google Sheets tips and tricks series and we are already exploring slightly complex functions and tools that are useful under various scenarios. This month, I will demonstrate the use of formulas that will help you sort, filter, manipulate, and display data as per your needs. Professionals, academics, students, and creative people from different industries can apply these formulas on a spreadsheet to tell/paint/convey/highlight a multitude of stories through numbers. As I said right at the beginning of this series, data never lies.

In today’s post, we will explore a tool called ‘Sorting’. Now, let’s look at the literal meaning of the word.

Sorting is any process of arranging items systematically, and has two common, yet distinct meanings: ordering: arranging items in a sequence ordered by some criterion; categorizing: grouping items with similar properties.

Back in school, we learned how to write numbers in ascending and descending order – smallest to highest and vice versa, from highest to smallest respectively. When working on a spreadsheet, it’s important to be able to sort your data in different ways, depending on the context, in order to help you make sense of it.

For example, let’s see how our favorite police chief (if you have been following this series from Day 1, you would know this reference) would use the sorting data to keep track of crime numbers every month, sorting from highest to lowest might be helpful to him. But sorting isn’t all about numbers! Suppose he wants to sort the list alphabetically, as per the type of crime (arson, murder, robbery) or what if he wanted to sort both, number of crimes and the type of crimes together, then sorting is a very handy tool.

Usage: Sorting with one column

Let us look at the screenshot below of crime stats across different months and cities (I have named the cities as alphabets). The data isn’t sorted in any order. Let’s see how to sort column D – number of crimes – in descending order, i.e. from highest to lowest.

• Select the whole data set (see screenshot below)
• Go to the Data tab on the main menu and sort range as shown in the screenshot

• You get the below step as shown in the image.

• Check the box which says “Data has header row,” since our data has a header row (Sl No, Crime, Month, Number of Crimes, City).
• Once you do that the sort by column will automatically show the current headers

• Be sure to select Number of crimes and since we want it in descending order, select Z->A

• The image below displays column D – number of crimes – in descending order, starting with the highest to the lowest.
• All the data is sorted in descending order automatically including the other columns

Now suppose we want to sort it by type of crime as well. Let us see how to do it.

Usage: Sorting with more than one column

Follow the same steps as demonstrated above till you get the box below for sorting columns. The only change: this time select the type of crime header first.

• Now we add one more column by adding another sort column as shown below

• See the image below. Chose number of crimes from the drop-down and Z->A for descending order

The result can be seen in the image below, where the data is sorted in ascending order as per the type of crimes and descending order for the number of crimes. The crimes are listed alphabetically and the number of crimes is listed in descending order for each crime.

You don’t have to be a police chief to want to make sense of crime stats in your city or state. Being able to analyse a spreadsheet is a skill that will come in handy in many situations. Let me know in the comments below which industry or sector you work for and I can help you see the value of applying Google Sheets formulas at work.

GOOGLE SHEETS – TIPS AND TRICKS | DAY 13: FREEZING ROWS & COLUMNS

It’s been more than 10 days into the Google Sheets tips and tricks series. We began with simple formulas to help us add up numbers and then explored other higher math operations (COUNTIFS, COUNTIF, SUMIF, SUMIFS). This week, we have moved into the more functional and exciting part of the series, focusing on tools that will allow you to manipulate how you find, compare, filter, and display data in Google Sheets.

In today’s post, we will explore a Google Sheets tool called ‘freezing’ that is used frequently in spreadsheets that have a lot of scrollable content. Freezing is a function in Google Sheets that locks a row or/and a column in place and allows you to scroll through content in the rest of the document. This is useful across multiple types of spreadsheets.

Say you are a marketing executive and have created a spreadsheet with the year’s marketing campaign metrics – money spent on paid, social, search, organic traffic across the headers, and conversions and user growth in the first column sidebar. If you want to keep the header and sidebar static, while showcasing the metrics to your team, the Freeze feature is handy!

Freezing would allow you to keep the headers (rows on top) or the column (on the left bar) static.

And we will call upon our crime-fighting police chief once again to illustrate the tool. Let us look at the image below – a list of different crimes across different months and the frequency of the crimes – where the number of rows extends beyond what’s captured in the screenshot.

Usage: Freezing Row

Select the row which you want to lock, in this case, we will lock the header row (Sl No, Crime, Department, Number of crimes)

• Select the header row (Row 1)

• Click the View tab on the menu bar, hover over Freeze, and click on the third option from the top: Up to current row (1).

• You can see from the below image that the header row is locked.

Usage: Freezing Column

In the image below, we want to lock column D (number of crimes), so that it remains static when we scroll right to view the rest of the columns/content. We will select the same menu option that we used to Freeze the header row in the example above.

• Select Column D
• Click the View tab on the menu bar, hover over Freeze, and click on the last option at the bottom: Up to column (D).
• You can see from the screenshots below how column D (number of crimes) is locked

There you have it, on Day 12 of the Google Sheets tips and tricks series, we explored a simple way to Freeze rows and columns. Have you tried applying this function to your spreadsheet, let me know in the comments below!