New Excel features to make you even more productive
“The number one thing that I would tell people is to keep their software current,” says Ken Puls, FCPA, FCMA and an Excel consultant who prepares courses on the subject for CPA Canada (Shutterstock/dennizn)
Microsoft Excel can be the most important thing in an accountant’s toolbox. The software is designed to support all kinds of important accounting functions, such as budgeting, preparing financial statements, creating balance sheets and much more. But are you being as productive on Excel as you can be?
“The number one thing that I would tell people is to keep their software current,” says Ken Puls, FCPA, FCMA and an Excel consultant who prepares courses on the subject for CPA Canada. “We’re not working with Excel 2003 anymore—the world has significantly changed.”
Puls, who is bringing his efficiency lessons to a workshop as part of The ONE National Conference 2019 in Montreal, Que., says there have been a huge amount of changes inside Office 365—things you don’t see in the older versions.
Here is a list of a few of his favourite new features.
1) GEOGRAPHY
If you have a place—Vancouver or Toronto for example—in one of your cells, you can actually turn it into a geography data type. That creates a card in the left-hand corner that can be selected to show information about the city. You can get information on latitude and longitude, the population and even the mayor.
HOW TO: Type the details (such as country name, state, province, city, and so on) into a cell. With the cell selected, go to the Data tab and click Geography. Select one or more cells with the data type, and the Insert Data button will appear. Click the button and then click a field name, such as population, for more information.
2) STOCK TICKER
You can now go in and put a stock ticker in a cell and find data such as the 52-week high, the 52-week low, the last trade time and more.
“The other cool thing is that will give you the exchange rates for the Canadian to U.S. dollar based on the trade times. So for currency convergence for accountants, that’s awesome stuff,” Puls says.
HOW TO: Type the ticker symbol, company name or fund name into a cell. With the cells selected, go to the Data tab and click Stocks. If Excel finds a match between the texts in the cells and online sources, it will convert your text to the Stocks data type. Select one or more cells with the data type and the Add Column button will appear. Click the button and then click a field name, such as price, for more information.
3) IDEAS
Microsoft is utilizing powerful artificial intelligence with Ideas, a way to create professional documents, presentations and spreadsheets in less time. In Excel, Ideas helps identify patterns, trends and outliers in a data set.
“It will offer to build you a pivot table with a pivot chart, it will show you some trend charts,” Puls adds. “While it’s not necessarily true insights, it does allow you to get a quick glance at your data and maybe get some ideas of how to report it.”
HOW TO: Click a cell in a data range and then click the Ideas button on the Home tab. Ideas in Excel will analyze your data and return interesting visuals in a task pane. Pro tip: The tool works best when your data is formatted as an Excel Table with a single header row at the top.
4) ICONS
Does your table need some visuals? Don’t stop at arrows when you can add symbols for animals, food, sports, weather and so much more in Office 365. “You can insert a whole bunch of predefined icons that can really spice up your reports and dashboards,” Puls says.
HOW TO: On the Insert tab, click Icons. Scroll through icons to get some ideas or jump directly to a category by clicking the name in the navigation pane on the left. Choose an icon and click Insert.
EXCEL AT EXCEL
Become an Excel pro with these 11 Excel secrets for accountants. For an even deeper understanding, master Excel with CPA Canada’s Excel certificate program.