Google Spreadsheets is a powerful tool that allows you to create and work with large databases and is similar to Excel, which we all know and love. At Badin, we prefer Google Sheets over Excel, primarily because it facilitates sharing documents and tables; also, every update is automatically saved and immediately visible to all colleagues who have access to the document, making it easier for us to organise and exchange data.

In the following text, Ivana Gocić shares with us a few tips and tricks that make her daily work with data and numerous calculations easier, and which Badin’s finance department uses all the time.

Freeze

If the table is large, with many rows and columns, dragging left, right, up and down is tiring and we often lose track of which row or column contains the information we need. The Freeze option makes it easier because you can “freeze” columns or rows and in that case they do not move when scrolling.

Group

This feature is useful, because it allows you to hide columns and rows that you don’t always want to see when you open the spreadsheet. It differs from the Hide function in that the Group function creates a button and therefore it is easier to reveal and hide the desired columns and rows.

You need to select the columns or rows you want to hide, then go to: View > Group > Group columns/rows.

Version history

Google spreadsheet is great because it remembers all the changes since the creation of the document and keeps track of the editing dates and the users who worked on the document. If some data has been deleted or changed by mistake, you can always restore the previous version of the document by clicking on File > Version history > See version history.

Here you will find the needed version, and you can click Restore this version.

Sheet or range protection

Considering that in the spreadsheet several accounts can have access and work in the tables, it is sometimes necessary to lock certain sheets or ranges. This option does not allow the data in the selected sheet or range to be changed by other accounts, except for the owner of the document and the accounts to which permission is given. By right-clicking on the sheet that we want to protect, a drop-down menu opens and we can click Protect the sheet.

Clicking on Protect the sheet opens a menu where we can enter a short description of the sheet and choose whether we want to lock a specific range or the entire sheet.

By clicking Set permissions, we can set permissions for other accounts and by clicking Done, the sheet or range is now successfully protected.

Queries

Google Sheets, like Excel, have a large number of functions, which is the main reason why these tools are used for large databases. With them, you can perform all calculation operations, calculate values ​​according to certain criteria and filter data. Some of the most basic and commonly used are:

• SUM – adds all values from the given range
• SUMIF – sums all values from the given range according to one specific criterion
• SUMIFS – sums all values from the given range according to several criteria
• VLOOKUP – finds items in a table or range by row based on specified criteria
• IF – allows making logical comparisons between actual and expected values 

Here we will deal with a slightly different type of function, called Query. With it, you can write a query and extract the data you need from a large database.

The query is written as follows:

=QUERY(data, query, [headers])

If, for example, you sell clothes online, you keep records of each customer since the beginning of the year and you want to see how much and what a certain customer ordered from you, Query makes it easy for you to search and calculate. In this case, your customer record, (the sheet we’ll call Data), is the database we want to filter. Assuming that you have all the data about each purchase (customer name, item, quantity, price, discount, month in which the purchase was made, customer contact information, customer address, etc.) you can filter that data with the help of a query.

For example if you want to know how much and what the customer Petar Perić ordered from clothes in the past period, write the query as follows:

=QUERY (Data!A1:G, “Select * where A = ‘Petar Perić’”)

Data!A1:G is the range where your records are located.

“Select * where A = ‘Petar Perić’” is a query that says “Get me all the data where Petar Perić is in column A.

If you don’t want everything, but only certain information, you can select only those columns and in that case the query will be:

=QUERY (Data!A1:G, “Select A,B,E,F where A = ‘ Petar Perić'”)

The Select A,B,E,F part means that only the data entered in these columns in the Data sheet will display.

Search and replace

If you have written some formulas in several different cells and you realise that you need a different range or criterion, it is tedious to go cell by cell and manually change each formula. This is where Search comes to the rescue, because with it you can change the range in each formula at once, in a specific sheet or the entire spreadsheet.

For example, you entered the range A1:A500 in the formula and realised that you need A1:A1000, press Ctrl+F and the Search modal window will open in the upper right corner.

Clicking the ellipsis button reveals advanced search options:

In the Find field, enter the range you want to change, e.g. A1:A500
In the Replace with field, enter the range you need, e.g. A1:1000
In the Search field, choose whether you want to apply the changes to the sheet you are currently working in, a specific range or to all sheets
Check the checkbox for Also search within formulas.
Click on Find Replace all Done.

You have now successfully changed the formulas.

You can combine all the above functions and options. You can also combine queries with other functions such as SUM where, in addition to filtering data, it would add the values ​​you want by a certain criterion, group it by various criteria, search in value ranges, etc. Google Sheets offer many possibilities, among other things, running JavaScript, and we will talk about all of this in one of our upcoming blogs.