9 Tips for Presenting Table Results and Analyzing Data in Excel

By Stephen L. Nelson, E. C. Nelson

Let’s take a step back from the details of data analysis in Excel and offer a handful of general tips. Mostly, these tips are summaries and generalizations that will help you with the basics of Excel.

Work hard to import data

Working to import good, rich data into Excel workbooks really is worthwhile. Sometimes importing data can be problematic. Headaches and heartbreaks can happen when trying to grab data from other management information systems and when trying to work with a database administrator to get the right data into a format that provides for useful data analysis with Excel.

But in spite of the hassles of obtaining the data, you will find that importing good data into Excel is well worth the effort. Traditionally, people make decisions by using very standard information sources. And those traditional sources produce traditional insights, which is great. But when you can work with a richer, deeper data set of raw information, you often glean insights that simply don’t appear in the traditional sources.

Design information systems to produce rich data

While you may want to concentrate on creating systems that produce reports that managers and decision-makers want and that produce forms (such as invoices and checks and purchase orders) that businesses required to operate, this isn’t the only way.

You also need to recognize that there will probably be unplanned, unorthodox, unusual but still very valuable ways in which the data that is collected by these management information systems can be analyzed. And so, if you work with or design or participate in implementing information systems, you should realize that raw data from the system can and should be passed to data analysis tools like Excel.

Having rich, detailed records of the products or services that a firm sells enables that firm to see trends in sales by product or service. Additionally, it allows a firm to create cross-tabulations that show how certain customers choose and use certain products and services.

Organizations need to design information systems so that they also collect good, rich, raw data. Later on, this data can easily be exported to Excel, where simple data analysis can lead to rich insights into a firm’s operation, its opportunities, and possible threats.

Don’t forget about third-party sources

One quick point: Recognize that many third-party sources of data exist. For example, vendors and customers might have very interesting data available in a format accessible to Excel that you can use to analyze their market or your industry.

The Web Query tool available in Excel makes extracting information from tables stored on web pages very easy.

Just add it

You might think that powerful data analysis requires powerful data analysis techniques. Chi-squares. Inferential statistics. Regression analysis.

But that’s not necessarily so. Some of the most powerful data analysis that you can do involves simply adding up numbers. If you add numbers and get sums that other people don’t even know about — and if those sums are important or show trends — you can gain important insights and collect valuable information through the simplest data analysis techniques.

The key thing is collecting really good information in the first place and then having that information stored in a container, such as an Excel workbook, so that you can arithmetically manipulate and analyze the data.

Always explore descriptive statistics

The descriptive statistical tools that Excel provides are really powerful tools. Don’t feel as if these tools are beyond your skill set.

Descriptive statistics simply describe the data you have in some Excel worksheet. They’re not magical, and you don’t need any special statistical training to use them or to share them with the people to whom you present your data analysis results.

Note, too, that some of the simplest descriptive statistical measures are often the most useful.

Watch for trends

Peter Drucker, perhaps the best-known and most insightful observer of modern management practices, noted in several of his last books that one of the most significant things data analysis can do is spot a change in trends. Trends are almost the most significant thing you can see. If your industry’s combined revenues grow, that’s significant. If they haven’t been growing or if they start shrinking, that’s probably even more significant.

In your own data analysis, be sure to construct your worksheets and collect your data in a way that helps you identify trends and, ideally, identify changes in trends.

Cross-tabulation

The PivotTable command is a wonderful tool. Cross-tabulations are extremely useful ways to slice and dice data. And, the neat thing about the PivotTable tool is that you can easily re-cross-tabulate and then re-cross-tabulate again.

If you have good rich data sources and you’re not regularly cross-tabulating your data, you’re probably missing absolute treasures of information. There’s gold in them thar hills.

Chart it, Baby

An important component of good data analysis is presenting and examining your data visually.

By looking at a line chart of some important statistic or by creating a column chart of some set of data, you often see things that aren’t apparent in a tabular presentation of the same information. Basically, charting is often a wonderful way to discover things that you won’t otherwise see.

Be aware of inferential statistics

Excel provides inferential statistics tools. Inferential statistics enable you to collect a sample and then make inferences about the population from which the sample is drawn based on the characteristics of the sample.

In the right hands, inferential statistics are extremely powerful and useful tools. With good skills in inferential statistics, you can analyze all sorts of things to gain all sorts of insights into data that mere common folk never get. However, quite frankly, if you don’t have extensive knowledge of inferential statistics, you probably don’t possess enough raw statistical knowledge to fairly perform inferential statistical analysis.