@Work | Tools

The one Excel skill you can learn right now to save time

Slash the hours or days spent each week doing data management tasks. This feature will change the way you work.

A Facebook IconFacebook A Twitter IconTwitter A Linkedin IconLinkedin An Email IconEmail

Microsoft Excel program open on the screen of a laptopLearning how to use Power Query can help free up time for accountants to focus on interpreting and analyzing information instead. (Wirul Kengthankan/Shutterstock)

A relatively new Excel feature is changing the way accountants work.

“My number 1 time-saving tip for Excel—and this goes for everyone who uses it—is to learn Power Query,” says Ken Puls, FCPA, FCMA, an Excel consultant who prepares courses on the subject for CPA Canada.

“This [feature] is so impactful that I have one client who saved 52 work days a year,” he says, explaining that this client had been spending one day each week cleaning data. After taking Puls’ Power Query workshop, she could complete the same work in 30 seconds, with the press of a button.

While other keyboard shortcuts for Excel can help shave minutes from the time spent on spreadsheet tasks each day, Power Query—which is designed to clean and organize data efficiently—is a far more significant skill for accountants to have.

Power Query allows users to automate the repetitive steps of a data-cleaning process, meaning they won’t have to repeat the individual steps the next time they receive similar data. As a result, accountants who learn to use Power Query can win back the hours—or even days—of time each week they previously spent transforming data that had been exported from another source. 

“It’s a huge amount of time, and every time you get a new file, you have to do it all over again,” says Puls, who travels the world training people to use Excel more effectively.

Unfortunately, many users don’t even know Power Query exists, Puls says, adding that this user group ends up spending about 80 per cent of their time doing repetitive data cleanup tasks.

“People find ways to do things, and they keep doing them the same way,” says Puls. “They stop looking for new ways. So, when I show them [Power Query], the look on their faces…they all have the same expression. It’s a mix of really happy and really angry that they’ve been wasting their lives.”

The feature is available for PCs as a free, downloadable add-on for Excel 2010 and 2013. It’s integrated into newer versions of Excel and can be found on the Data tab’s Get & Transform group as New Query in Excel 2016 or Get Data in Excel 2019/Office 365.

Power Query is not currently available on Mac versions of Excel.

HOW POWER QUERY WORKS

A wide range of sources can be connected to an Excel worksheet, including other Excel files, text files, a web page, a database and online sources such as Salesforce or Facebook.

Here’s how to create a query for importing from a single source:

  • In Excel 2019/Office 365, click the Data tab, then Get Data, then select your source from a drop-down menu.
  • In Excel 2016, click the Data tab, then New Query, then From Other Sources and select your source from a drop-down menu. 
  • In Excel 2013 and Excel 2010, be sure you have downloaded and installed the add-on for Power Query. Click Power Query, then select your source.
  • Follow the prompts to identify the file you wish you import.
  • In the Navigator pane that opens, you will see a list of tables that are available inside the source. Single click the items to preview them, or double click a table you wish to import.
  • A Query Editor window will open, showing the data you have just imported.
  • Shape the data in your habitual way, removing columns or rows that aren’t relevant.
  • Clean the data in your habitual way, replacing values and filtering the data.
  • Name the process in the Query Settings pane on the right side of the screen.
  • Now, load the query to a worksheet by clicking Close & Load in the upper left-hand corner of the Query Editor. A drop-down menu allows you to choose whether to load to a new worksheet or select an existing one.
  • When you wish to import new data from the same source, go to the worksheet. Right click anywhere in the data range, then click Refresh.

To follow a guided example of how the steps work, follow this Microsoft tutorial.

For information on how to merge data from two or more sources, see this Microsoft tutorial

USE YOUR NEW TIME WISELY

Puls suggests accountants put the hours they will save from learning Power Query towards automating other tasks they do frequently. By reducing the time they spend on time-consuming, repetitive tasks, accountants can focus on interpreting and analyzing information instead.

“Business intelligence is an exploding area,” says Puls, who shares news about changes to Excel in a monthly newsletter and also runs a discussion forum and knowledge base at excelguru.ca.

“Every company is asking for more. If you’re not tech literate, you’re at the bottom of the pile of resumes.”

The key is to stay curious about Excel and to keep looking for new ways to use the software, says Puls, though he admits that can be challenging.

“When your boss is breathing over your shoulder,” he says, “you don’t feel like you have time to look for new ways.”

But it’s worth it.

According to a 2017 IBM report, 90 per cent of the world’s data at the time was generated in the two years prior to the report. There’s massive power in that data, says Puls, but someone—such as an accountant—must process it first.

CPA CANADA OFFERS NEW DATA MANAGEMENT CERTIFICATE

To help accountants develop comprehensive business intelligence skills and harness the power of data for better decision-making, CPA Canada has developed a new five-course online certificate in data management. Puls created the first course in the certificate, Data preparation: Cleansing, modelling and dashboards, which focuses on Power Query. It can be taken individually or as part of the certificate. All five courses in the certificate can be purchased as a bundle.