Business Tech: Tips for increasing your spreadsheet efficiency

Bahar Ferguson 

Ever been in an interview or updating your LinkedIn profile and were asked if you were efficient in Excel? Most of us would say yes because we know how to enter data into the boxes and how to move onto the next one; however, how many of us are using it to its maximum capabilities or are navigating it in an easy fashion?

Whether your spreadsheet application of choice is Microsoft- or Google-based, look through these easy tips to impress your colleagues in your next share-screen session.

Google Sheets

Google Sheets is a web-based spreadsheet program from Google that makes it easy for users to organize data and numbers online. Google Sheets is free, making it a popular spreadsheet tool for individuals and workplaces alike.

Here are five helpful tips and tricks that are available on Google Sheets:

1. Comment and Collaborate. Being a web-based tool, Google Sheets makes it easy for teams to collaborate on the same document. Instead of sending large files back and forth, easily share notes and feedback directly within your Google Sheets document.

Just right-click on the cell you want to comment on and click “Comment.” You can also tag specific users and send emails to notify them of your feedback.

2. Copy Sheets Between Documents. Need to merge sheets between multiple documents? Google Sheets makes it easy to combine sheets right within your Google Drive.

Just right-click anywhere on a sheet that you’d like to copy. Select “Copy to” in the drop-down and select the spreadsheet you’d like to copy the sheet to.

3. Reference Other Google Sheets. Need to reference data in another document? The “Importrange” function makes it possible to link data from one Google Sheets to another. This function is incredibly useful if you have master data files that need to be used in other documents. Instead of having duplicates of the data that require updates, you can refer to one master document instead.

The syntax to link another Sheets document is =IMPORTRANGE (“spreadsheet_url”, “range_string”).

4. Use Keyboard Shortcuts. Like most software, Google Sheets has a range of keyboard shortcuts to improve your spreadsheet workflow. Some common (and unique) Google Sheets shortcuts to be aware of include:

• Find and replace: Ctrl + H.

• Insert new sheet: Shift + F11.

• Paste values only: Ctrl + Shift + V.

Some of these vary depending on your operating system. You can find an up-to-date list of shortcuts at https://support.google.com/docs/answer/181110.

5. Translate Content Within Spreadsheets. The benefit of Google-based software is the seamless integration with other Google features. If you work with data in different languages, Google Sheets can connect to Google Translate directly within your spreadsheet. This makes it easy to translate large ranges of text and keep the translations in one place.

To translate a cell, use the syntax:

= GOOGLETRANSLATE(text, [source_language, target_language]).

Microsoft Excel

Microsoft Excel is a spreadsheet software developed by Microsoft for its Office software suite. Excel can feel overwhelming for many users, but it’s a simple tool to organize data and numbers.

To help new and experienced Excel users alike, we’ve put together a list of five helpful Excel functions:

1. VLOOKUP. LOOKUPs in Excel sound scary, but they’re actually surprisingly simple. LOOKUP functions help you find items in your table by row.

For example, you have a list of 10,000 product names in Column A and their SKUs in Column B. The VLOOKUP function helps return the SKU by searching for the product name.

The syntax for a VLOOKUP function is: = VLOOKUP (lookup_value, table_array, column_index_num, [range_lookup])

• Lookup_value = the term you want to look up.

• Table_array = the range of cells you want to look for it.

• Column_index_num = the column number with the value you want to return.

• Range_lookup = if you want an exact (0/FALSE) or approximate match (1/TRUE).

2. SUMIF. A normal SUM function adds up everything within a specified range. The SUMIF function only adds up the numbers within a range, if a condition is met.

For example, you have types of different fruit in Column A and the cost in Column B. You can use a SUMIF function to only add up the costs for apples.

The syntax for a SUMIF function is: = SUMIF (range, criteria, [sum_range]).

• Range = the cell range you want to search for your search term.

• Criteria = the term you want to look up.

• sum_range = which cells to sum (or add up).

3. COUNTIF. The COUNTIF function is similar to SUMIF. But instead of summing or adding up the values, it counts the number of cells that meet the criteria.

The syntax for a SUMIF function is: = COUNTIF (range, criteria).

• Range = the cell range you want to search for your search term and count.

• Criteria = the term you want to look up.

4. Pivot Tables. While not technically a function, pivot tables are a powerful feature in Excel that helps you organize your data. A pivot table lets you perform functions like COUNTIF or SUMIF for several criteria or search terms.

Creating a pivot table in Excel is simple. Just select the data cells you want to use to create your pivot table. Then select Insert and Pivot Table, then select where you want to put your pivot table. You can then choose your fields to customize the data you want to output.

5. CONCATENATE. Need to combine the text in a bunch of rows or columns? Then the CONCATENATE function is for you. You can use this function to join up to 30 text items.

The syntax for CONCATENATE is: = CONCATENATE (text1, text2, [text3] ... ).

• Each text can be replaced with the appropriate cell.

As you start to play around with the applications, you will learn that whether you’re on Excel or Sheets, the formulas and calculations remain relatively the same. So, one helpful trick you learn in one will likely help you in the other. Happy formatting.

Bahar Ferguson is the president of Wasatch I.T., a Utah IT company for small and medium-sized businesses.