Blog

Data is King

Every single person, event, product, or interaction, can be dissected and broken down into data points. From the personal, such as names, birthdates, and addresses, to the complex, such as tax returns, click rates, or growth rates; we are able to mine data anywhere. It has often been said that the greatest resource of the digital age is data, and knowing how to handle data could very well spell the difference between the success and failure of every business.

In the pursuit of continuous learning and skill development, Innovuze Solutions Inc. conducted an Advance Excel & Google Sheets training for its employees last May 2, 2022. Conducted by Project Manager Engr. Earl Jaducana, the training focused on the more advanced side of spreadsheet handling. The virtual training was attended by employees that were on site as well as those working remotely from their homes.

Excel vs Google Sheets

Sure, for the most part Excel and google sheets are pretty similar. They are both spreadsheet software that uses cells that allow you to perform calculations, formatting, reorientation, data analysis, graphing, and even macro programming on thousands of data points. But as similar as they are, there are certain features that one does better than the other, but these differences aren’t as massive as one might think and the skills are always transferable. If you’re interested in knowing more about Excel vs Google Sheets, check out this article from Google support of the difference between the two.

Sample Data Table

Sorting, Filtering, and Conditional Formatting

To start off the training, Engr. Jaducana, shared a premade Google Sheet spreadsheet that contained sample data that the participants will use throughout the afternoon. Already divided into tabs per topic, the instructions and sample questions and formulas were all ready for the participants to work with.

The first three concepts that were discussed were Sorting, Filtering, and Conditional Formatting. Basically, Sorting is manipulating your data by arranging it alphabetically or numerically. This has two types, Sort Sheet which organizes all of the data in your spreadsheet by one column and Sort Range which sorts the data in a range of cells, the latter being helpful when working with a sheet that contains several tables.

Up next is Filtering, or the selection of the data which you want to work with based off of, well, filters! You can either use the create filter option or use the FILTER function. And then we have Conditional Formatting, which enables you to highlight cells with a certain color, depending on the cell's value. This is an extremely useful tool when working with lots of data sets, and you need visual cues to find outliers or specific values.

Sample Table that has been Filtered to HR and AT, Sorted from Highest to lowest Pay and color graded.

Data Validation, Pivots, and a few What IFs?

The first part was a bit easy, but up next our speaker introduced a bit more of the intermediate functions that spreadsheets have to offer. Data Validation, Pivot, and the IF function. Data Validation is a way for the user to define what can be entered in a certain field. If you are working with employee details, you wouldn’t accept numbers in an employee number field or letters in an age field would you? Well, Data validation is there to help you catch errors and maintain your data’s integrity. Pivot is an extremely useful feature that allows you to select certain columns, rows, and values from larger tables. As the speaker puts it, pivot tables are the fastest and easiest way to quickly analyze data in Excel.

At this point, Engr. Jaducana is demonstrating in his presentation how the pivot table editor works and the choices that it provides. In turn participants were also able to explore and play around with the data sets with their copy of the working spreadsheet. Finally, we have the IF function. The function returns one value if a logical expression is `TRUE` and another if it is `FALSE`. The versatility of the IF function makes it a staple for anyone working with spreadsheets as it is limited only by one’s imagination or need. If you need to verify that a data point is less than, greater than, or equal to a reference? Do you need to check if a cell is empty? Or is it the correct type of data? The IF function is all you need.

What IF?

Text Manipulation

Moving on with the training, Engr. Jaducana now introduces the many ways text can be manipulated in a spreadsheet. These include Split which divides text around a specified character or string, and puts each fragment into a separate cell in the row. The TEXT function that lets you change the way a number appears by applying formatting to it with format codes. LEFT, RIGHT, and MID functions which return the first character or characters in a text string, based on the number of characters you specify. And CONCATENATE which is the joining of two or more text strings into one string. Despite these being a bit too specific and albeit niche in their functions, you never really know what you might need them for or the problems you need to solve. So, knowing how to manipulate text and their formatting is an essential skill for anyone working with spreadsheets.

Concatenate best practices

Queries, Arrays, and Emails

Moving even further with more advanced topics, Engr. Jaducana introduced the participants to more complex yet extremely useful features of spreadsheets. This round, the discussion led on to how to search down the first column of a range for a key that returns the value of a specified cell in the row found, which would be the function VLOOKUP.

How VLOOKUP Works

Upnext came IFERROR which returns a value you specify if a formula evaluates to an error, where you can display specific error messages. The QUERY function, which according to the speaker is the most powerful function in google sheets, which allows you to use commands that can manipulate your data and in some instances do the job of other functions.

Query formula

Next that was discussed was ARRAYFORMULA which allows you to output a range of cells, rather than a single value. It also lets you use non-array functions with arrays of data. Then came INDEX & MATCH where you can do horizontal and vertical lookups, 2-way lookups, left lookups, case-sensitive lookups, and even lookups based on multiple criteria.

Truly, there is almost nothing that spreadsheets can do. At this time, the participants are getting a bit of an information overload, but that’s OK, everyone doesn't need to memorize and know everything right away. What’s important is that they have an idea of what can be done, and since the resources are available, they can recall these functions if and when they need them.

Finally, the participants are introduced to Mail Merge Email, a way for people to automatically populate an email template with data from Google Sheets. Participants coming from the HR department shared that this is something that they are familiar with and that it is very useful especially when it is part of your job to send emails regularly to many different people. The last part of the training was spent trying to create a new mail merge template and send them to the participants. Still, mail merge is a service provided and there are limits to how many you can send in a day. But just before ending the seminar, trainer Engr. Jaducana left the participants with an assignment on the use of mail merge! But no need to worry, he gave the participants ample time to submit their output which was due a week later. It would be an ideal time to review and familiarize themselves with all that they have learned during the training and put them in real world situations. After all, data is everywhere, and knowing how to use and analyze data is a skill that everyone should have.

Thanks were given all around as participants were given a true deep dive into spreadsheets and what they can do. Some even requested for a second session! For now, the participants can think on what they have learned and relate it to their tasks. It was a great experience overall and learning new skills is always a win-win for everyone to stay excellent.

-END-