Excel Data Analysis For Dummies
Book image
Explore Book Buy On Amazon

Here are a handful of general tips on analyzing data with Excel. Mostly, these tips summarize and generalize much more detailed processes for analyzing data.

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 . . . like the accounting system, or some third-party report, or newsletter, or publication. 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

More than 20 years ago now, designers concentrated on creating systems that produced the reports that managers and decision-makers wanted and that produced forms (such as invoices and checks and purchase orders) that businesses required to operate.

Those items are still obviously key things to think about while you design and install and manage information systems, such as an accounting system. But 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.

The bottom line is that 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.

A quick final comment about third-party data sources is this: 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.

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.

Again, 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 — including measurements such as a sum, an average, a median, a standard deviation, and so forth — 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. For example, knowing the smallest value in a data set or the largest value can be very useful. Knowing the mean, median, or mode in a data set is also very interesting and handy. And even seemingly complicated sophisticated measures such as a standard deviation (which just measures dispersion about the mean) are really quite useful tools.

Watching descriptive statistics change (or not change) over time such as from year to year often gives you extremely valuable insights.

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.

Slicing and dicing: 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

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 your only exposure to inferential statistical techniques is minimal, you probably don't possess enough raw statistical knowledge to fairly perform inferential statistical analysis.

About This Article

This article can be found in the category: