Forget smartphones, use smart Excel!

By Kelly L. Williams, CPA, Ph.D.

Q. I love using Excel but know I'm not getting the most out of it. What would you recommend to boost my Excel productivity quickly?

A. I get questions like this a lot. People like many Excel features but don't have the time or energy to learn a bunch more. Well, there is good news for those who fit this description. Just like our mobile phones are "smart" these days, Excel is pretty smart now as well.

In many cases, you simply need to click a few buttons and Excel will do the work for you. This article shows you a couple examples.

Note that the content of this article is based on Microsoft Excel 365 for PCs. Other versions of Excel may work differently.

PivotTables

PivotTables are extremely useful tools. They allow the user to summarize and analyze large quantities of data. However, creating a PivotTable seems to be a daunting task to many who have not yet learned about them. As of Excel 2013 and later versions, Excel can create PivotTables for you. Click anywhere in your dataset, click on the Insert tab, and select Recommended PivotTables from the Tables group (shown in the screenshot below). If there is enough data, Excel will give you some options of PivotTables to choose from. Click any of the recommended PivotTables to preview how your data will look in the PivotTable.

techqa-1


Once you choose the PivotTable you want, click
OK. By default, your PivotTable will appear on a new worksheet. You can leave the PivotTable as is, or you can modify it. Click anywhere in the PivotTable and the PivotTable Analyze and Design tabs will be added to the Ribbon. You can make any desired modifications to your PivotTable from those tabs. We will use a PivotTable in the answer to the next question in this column.

Recommended charts

The number of charts Excel has to offer can be overwhelming. Excel 2013 and later versions can determine the right charts for your data, make recommendations, and create the chart for you. Click anywhere in your dataset, click on the Insert tab, and select Recommended Charts from the Charts group (as shown in the screenshot below). Click on any of the recommended charts to preview how your data will look in the chart.

techqa-2


Once you choose the chart you want, click
OK. You can leave the chart you chose as is, or you can modify the chart. Click anywhere in the chart and Chart Design and Format tabs will be added to the Ribbon. You can make any desired modifications to your chart from those tabs.


About the author

Kelly L. Williams, CPA, Ph.D., MBA, is an assistant professor of accounting at Middle Tennessee State University.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.

RESOURCES

Keeping you informed and prepared amid the coronavirus crisis

We’re gathering the latest news stories along with relevant columns, tips, podcasts, and videos on this page, along with curated items from our archives to help with uncertainty and disruption.

SPONSORED

Building process maps: Template and instructions

Documenting your financial close process and finding opportunities for automation are more important than ever. Our customizable slide deck has instructions, a risk assessment questionnaire, and bonus checklists that will help you map out your process.