extra-credit-header-2018

See descriptive statistics in Excel

By Wendy Tietz, CPA, Ph.D.; Jennifer Cainas, CPA, DBA; and Tracie Miller-Nobles, CPA

Excel’s Analysis ToolPak allows users to do a wide variety of more advanced data analysis. One function it offers is a tool that can quickly calculate a series of basic descriptive statistics for a dataset, including mean, standard deviation, minimums, and maximums.

Using the Descriptive Statistics tool

Find the Descriptive Statistics tool by clicking on Data and then Data Analysis in the ribbon. (Note: If you don’t have the Data Analysis option, you will need to install the Analysis ToolPak add-in. See our previous article on how to do this.)

data-analysis-ec


A new box will open, allowing you to select which analysis tool you would like to use. Select Descriptive Statistics and then OK.

descriptive-statistics


Next, identify the input range of the data that you want to perform the descriptive statistics on. In the example below, I’ve selected Column F (the transaction amount). If your data includes labels, you should check the box titled Labels in First Row. Then, identify where you want your output to appear (such as a new worksheet or new workbook) and ensure that the Summary statistics box is checked. Click OK.

summary-statistics


You will now see a variety of descriptive statistics (such as mean, median, standard deviation, minimum, maximum, and count) about your data.

amount


Wendy Tietz, CPA, CGMA, Ph.D., is a professor of accounting at Kent State University in Kent, Ohio; Jennifer Cainas, CPA, DBA, is an instructor of accountancy at the University of South Florida in Tampa; and Tracie Miller-Nobles, CPA, is an associate professor of accounting at Austin Community College in Austin, Texas. See their site
AccountingIsAnalytics.com for resources they have developed for teaching data analytics in introductory accounting. To comment on this article or to suggest an idea for another article, contact JofA senior editor Courtney Vien at Courtney.Vien@aicpa-cima.com.

SPONSORED REPORT

Get your clients ready for tax season

Upon its enactment in March, the American Rescue Plan Act (ARPA) introduced many new tax changes, some of which retroactively affected 2020 returns. Making the right moves now can help you mitigate any surprises heading into 2022.

100th ANNIVERSARY

Black CPA Centennial, 1921–2021

With 2021 marking the 100th anniversary of the first Black licensed CPA in the United States, a yearlong campaign kicked off to recognize the nation’s Black CPAs and encourage greater progress in diversity, inclusion, and equity in the CPA profession.