Excel Data Analysis For Dummies, 2nd Edition (2014)

Part IV. The Part of Tens

Chapter 14. Almost Ten Tips for Presenting Table Results and Analyzing Data

In This Chapter

Working hard to import data

Designing information systems to produce rich data

Remembering to forget about third-party sources

Always exploring descriptive statistics

Watching for trends

Cross-tabulating and re-cross-tabulation

Charting it, baby

Being aware of inferential statistics

Throughout the pages of this book, here and there I scatter tips on analyzing data with Excel. In this chapter, however, I want to take a step back from the details of data analysis and offer a handful of general tips. Mostly, these tips summarize and generalize the things that I discuss in the preceding chapters of this book.

Work Hard to Import Data

Working to import good, rich data into Excel workbooks really is worthwhile. I know that 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 — I promise — 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, as a young systems consultant with Arthur Andersen (yes, that Arthur Andersen), I designed accounting systems and financial information systems for large companies. In those days, we 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 I think that 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.

A simple example of this will show you what I mean. It applies even to the smallest businesses. The QuickBooks accounting system, which I discuss a little bit in earlier chapters in this book, is an extremely popular accounting tool for small businesses. Hundreds of thousands of small businesses use QuickBooks for their accounting, for example. And the one thing that I would say about QuickBooks users in general is that they often want to use the QuickBooks system simply for accounting. They want to use it as a tool for producing things like checks and invoices and for creating documents that report on profits or estimate cash flow information.

And that’s good. If you’re a business owner or manager, you definitely want that information. But even with a simple system like QuickBooks, businesses should collect richer sets of data … very detailed information about the products or services a firm sells, for example. By doing this, even if you don’t want to report on this information within QuickBooks, you create a very rich data set that you can later analyze for good effect with 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, I submit, 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 — such as the types that I describe in the earlier chapters of this book — 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.

Earlier in the book, for example, I mention that the slowdown in computer book sales and in computer book publishing first became apparent to me based on an Excel workbook supplied by one of the major book distributors in North America. Without this third-party data source, I would have continued to find myself bewildered about what was happening in the industry in which I work.

A quick final comment about third-party data sources is this: the Web Query tool available in Excel (and as I describe in Chapter 2) 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 I don’t think 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.

Again, in echoing earlier tips in this chapter, 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, even if this book is your first introduction to these tools.

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. You don’t need to understand anything more than this book describes to use or share this information.

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. I want to echo this here, pointing out that 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, which I describe in Chapter 4, is a wonderful tool. Cross-tabulations are extremely useful ways to slice and dice data. And as I note in Chapter 4, the neat thing about the PivotTable tool is that you can easily re-cross-tabulate and then re-cross-tabulate again.

I go into a lot of detail in Chapter 4 about why cross-tabulation is so cool, so I don’t repeat myself here. But I do think that 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

In Chapter 15, I provide a list of tips that you might find useful to graphically or visually analyze data. In a nutshell, though, I think that 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

To varying degrees in Chapters 9, 10, and 11, I introduce and discuss some of the inferential statistics tools that Excel provides. 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 this book, you probably don’t possess enough raw statistical knowledge to fairly perform inferential statistical analysis.