Tech Solutions

In defence of the spreadsheet: Tricks and hacks to help you Excel…

Jonathan Crossfield
Business and Technology Journalist

Jonathan is an award-winning journalist, blogger, writer and editor, locked in a daily fight for the keyboard with two very impatient cats.

Jonathan Crossfield
Business and Technology Journalist

Jonathan is an award-winning journalist, blogger, writer and editor, locked in a daily fight for the keyboard with two very impatient cats.

With a few tips, tricks and hacks the humble spreadsheet can save you time, energy and even money by managing your business data better.

Love ‘em or loathe ‘em, there’s no escaping that many small businesses and freelancers still rely on spreadsheets for many core functions. Whether creating invoices, crunching numbers, cleaning data, categorizing information or circulating details, many workflows will start out as a spreadsheet before migrating to a dedicated app or more powerful software later on. 

The image shows a laptop with a spreadsheet open on the screen. Spreadsheets can still be extremely powerful tools for businesses – but only if used wisely.

Microsoft Excel remains the most widely used spreadsheet software but it can still be an intimidating program if you’re a beginner. No wonder so many people still use Excel as nothing more than a fancy list-maker, or simply to add columns of numbers together.

You probably don’t have time to take an advanced course in Excel but there are still a few simple ways you can save time and squeeze more value out of your spreadsheets.

Get the knack…

Excel may have hundreds of powerful and advanced features lurking beneath the surface, and not all will be relevant to you. Yet, there are some functionalities and less obvious tricks that could change how you use spreadsheets every day.

Here are just a few nominated by Smarter contributors as ones they couldn’t live without. You may already be familiar with some, but even one new trick could give your workflows a boost.

Enter long data sequences in seconds

The nifty Flash Fill feature can save you from having to input lots of sequential data manually. When first setting up a spreadsheet, you may need to add columns of identifying numbers or values that match a simple pattern – for example; the number of each row (1 – 500), sequential product or invoice numbers (INV-001 – INV-250) or even calendar dates (01/01/17 – 31/12/17).

Say you’re setting up a project schedule and you need to write in month after month of dates – 365 cells overall. Rather than type each date out manually, you would enter the first date in the first cell, the next in the following cell, and then use Flash Fill to input the rest for you.

  • Enter at least two sequential values into consecutive cells (either in a row or column). For example, the first two dates in the sequence.
  • Highlight these cells and hover your mouse over the bottom right corner of the last cell until a black cross appears
  • Click and drag the cross over the remaining cells you want to fill. A little pop-up will show you the data that it plans to input into those cells as you’re dragging your mouse cursor across.

This feature can be used horizontally or vertically and also works well for common text values, such as days of the week or months of the year. You can also use flash fill to replicate the same value across multiple cells. 

Instantly apply a formula to multiple cells

Depending on how you want to crunch your data, some formulas can be quite complex – and frustrating to enter cell by cell, particularly if there are hundreds of lines of data. Flash Fill can really save you time and reduce the risk of mistakes by applying the same formula to multiple cells in a trice.

Imagine you want the cell at the end of each row to contain a formula, totalling or otherwise crunching the data from the preceding row. While the rules of the formula should be the same – eg: =SUM(B5:H5) ­– it needs to be updated to correspond with each new row.

  • Set the appropriate formula in the first cell – eg: =SUM(B5:H5)
  • Highlight this cell and as before, drag the bottom right corner of the box over the remaining cells that need to perform
    the same function
  • As you drag the box down the column, Flash Fill automatically adjusts the values in the formula for each new row – eg: =SUM(B6:H6)
Find what you need quicker with Sort & Filter

It’s easy for spreadsheets to rapidly grow into less user-friendly and confusing beasts. Everyone asks for more information to be included until there are more columns than can fit on the screen, or the sheer number of rows blows out over time so that you find yourself constantly scrolling up and down, left and right, in search of that one piece of information you need right now.

Thankfully, filters and sort functions make it easier to review only the data or information you need for the task at hand, hiding everything else that might distract or confuse you.

Say you have a spreadsheet that lists every Australian actor, but you only want to see the actors who live in NSW while casting your new play. Instead of manually scrolling through the very long list – with the risk of missing someone who might be perfect – use a filter to display only those listed as living in NSW.

  • Click on the header row to select it.
  • On the ‘Data’ tab, click on the ‘Sort & Filter’ button on the far right of the toolbar. This will create ‘drop-down’ buttons in each of those headings.
  • To see only actors from NSW, click on the drop-down button in the ‘State’ column.
  • In the dialogue box, de-select all of the options/values except for ‘NSW’. This will automatically filter the data so that only the actors who live in NSW appear. 
  • When you’re done, use the same drop-down button to select all options/values again to reveal the full spreadsheet once more.

 

This same feature can be used to automatically sort your data into a less random or chaotic order – such as by ‘State’ or alphabetically by ‘Surname’ – which is particularly useful with large data sets. Click on the drop-down button in the relevant column and in the ‘Sort & Filter’ dialogue box you can choose to sort the values alphabetically in either ‘Ascending’ or ‘Descending’ order.

Identify and remove duplicate entries

Duplication is a common problem for businesses managing large and dynamic data sets. Data entry errors, inconsistent processes, or even the same customer providing slightly different information in multiple transactions can all result in duplicated records (dupes) in your spreadsheet. Of course, some dupes are not mistakes – two customers living at the same address or three employees of the same business, for example. However, you may still want to identify these to wastefully avoid sending multiple emails or mail-outs when one would suffice.

For instance, it’s not uncommon for a large email list to inadvertently contain the same customer or email address more than once. These duplications (dupes) should ideally be removed before the spreadsheet is uploaded into your email marketing campaign, particularly as they could skew your results. Dupes could also get you into trouble if a customer has already tried to unsubscribe only to continue receiving emails because there is more than one record.

Here’s one method to quickly identify duplicate entries so you can then decide how to correct, merge or delete the records (dedupe).

  • Select the data range you want to dedupe.
  • Go to the ‘Home’ tab. Click on ‘Conditional Formatting’. In the dropdown menu, select ‘Highlight Cells Rules’ and then ‘Duplicate Values’.
  • In the popup window, you have the option to highlight cells that contain either ‘duplicate’ or ‘unique’ values with your choice of colour scheme. Click on the field and select ‘duplicate’.
  • All fields containing duplicate data will be highlighted with your chosen colour scheme, making them easy for you to review and action accordingly.

…or find the hack

Sometimes, the spreadsheet functionality just doesn’t exist to do everything you need. However, there may be a way to hack your way to a solution, by which we mean finding a way around the limitations of the software or even use it in far more creative and unintended ways. You may find you can use spreadsheets to solve business problems far beyond what you previously thought was possible.

One very easy way to extend the functionality of any software is to integrate it with other tools you use, automating processes wherever possible. Zapier offers thousands of easy-to-use integrations (called ‘Zaps’) between 750+ web apps – including online spreadsheet platforms such as Excel 2016 and Google Sheets. Each Zap automates a specific and customisable task between two or more web apps. For example, one Zap makes it easy to integrate Excel with Slack, which might be useful if you want to capture and archive online customer conversations. Or maybe you could save time by automatically updating your accounts in Google Sheets with PayPal transactions from your online store, each one added as a new row in the spreadsheet.

You can even simplify the onerous task of transferring information from one spreadsheet to another – Excel to Excel, for example. With the right Zap in place you can specify certain rules so that when you enter values into one sheet they will automatically update the corresponding cells in another. Entering a customer sale on one sheet might automatically add the customer’s contact details to your separate email marketing list, while the item and quantity information might synchronise with your stock control sheet, reducing your data entry and saving time.

Used wisely, and with just a little planning, process and practice, spreadsheets can still be extremely powerful tools that almost everyone is familiar with. 

Read about the productivity hacks that can help you squeeze more from each working day.

Find out more

man using a tablet, laptop and smartphone
Tech Solutions
Tech Solutions
Why a managed mobile fleet is a smart call

With mobile technology increasingly holding the key to efficient and profitable business operations, it’s time to take the management of this technology fleet seriously and imp...

Family-friendly general practice takes top spot at Telstra Queensland Business of the Year Awards
Success Stories
Success Stories
Family-friendly general practice takes top spot at Telstra Queensland Business of the Year Awards

Putting the patient first was just the right medicine for a family-friendly general practice, Family Doctors Plus, to be awarded the 2017 Telstra Queensland Business of the Yea...

BIG hART Team
Success Stories
Success Stories
Charity with a big heart wins top honour at Telstra Tasmanian Business Awards

A charity committed to combatting cases of social injustice in Australia has been named the 2017 Telstra Tasmanian Business of the Year.

people celebrating around award
Success Stories
Success Stories
Building innovator takes top spot at the 2017 Telstra ACT Business Awards

An innovative exterior design business which is bucking the trends and leading the pack across the commercial building industry is the 2017 Telstra Australian Capital Territory...