Introduction | Accessing and Analyzing Data with Microsoft Excel (Bpg-Other)

Many business computer users spend a majority of each working day analyzing data in a variety of electronic formats, whether the data is in spreadsheets, in databases, or on the Web. These users need to do more than just look at data; they need to make important business decisions based on this data— decisions that affect their individual workgroup, their own businesses, or their entire organization.

Finding an organization these days, large or small, that does not record facts and figures about its operations, products, services, and customers is rare. Perhaps you have encountered one of the following scenarios in your own organization or business:

Part of your role within your organization involves basing business decisions on the products or services that your customers purchase. By analyzing your customers’ purchasing trends, you know that if you take the right actions, you can increase sales and thereby increase your own value to the organization. Perhaps you aren’t quite sure how to determine these purchasing trends, or you’ve been analyzing purchasing trends but suspect that there’s a better way. Or maybe you’re using software applications from a company other than Microsoft to analyze these trends and you want to see what Microsoft has to offer to make your data analysis tasks easier.
Your manager has asked you to create some reports for her, summarizing the in-stock levels of your organization’s product over the last year. You’re not sure how to use your organization’s data analysis software to translate her request into the report format she prefers. Or maybe you frequently create your manager’s reports, but the process is always tedious, and you think there might be an easier way to do it.
For the first time, your organization’s chief operating officer (COO) asks you to present to him an analysis of your western region’s sales figures for the last fiscal quarter. You want to do a great job, but you’re a little nervous. You want to include compelling charts, callouts, highlights, recommendations for improvement, and footnotes to guide you in your presentation—and to guide your COO’s business decisions— but you’re not sure how to do all this with your data analysis software.
The business you started five years ago is reaching its stride and now it’s time to really get a hold of the financial numbers. You’ve increased the number of employees, struggled through several peak shopping seasons, and acquired a loyal customer base. Your current financial software applications are great for keeping track of your general ledger, but they fall short when it comes to really helpful data analysis. You purchased a copy of Microsoft Office at your local office supply warehouse and you’ve been using it to write and send out business letters, but you know there’s a lot more you can use it for, if you just knew how.
You develop software solutions for your organization, but you’ve never developed data analysis solutions before. Or perhaps you’ve developed software solutions but never any that use Microsoft applications. Or maybe you’re just looking to improve your overall skills for developing data analysis solutions with Microsoft software.
You are an information technology (IT) specialist, and you have just been put in charge of training users to increase their skills with Microsoft Office data analysis tools. You want to increase your own skills in this area as well as understand which skills you should incorporate into the training.

If you have experienced similar scenarios, then Accessing and Analyzing Data with Microsoft Excel will show you how to increase your skills with Microsoft data analysis software, which in turn will help you or your organization make smarter and faster business decisions. This introduction will help you further understand the purpose of this book, who the book is written for, and the topics it covers.

About This Book

Microsoft Excel provides valuable tools such as spreadsheet formulas, PivotTable reports, and graphs to present and analyze data. Other Microsoft Office applications—Microsoft Access, the Microsoft Office Web Components, and Microsoft Data Analyzer, to name a few—provide the means to store, present, and analyze data. This book will help you master the skills you need to use this suite of products to analyze business data, thereby enabling you to make better informed business decisions more quickly. Software solutions developers and IT specialists will also find useful information that can enhance their Microsoft data analysis solution development and system administration efforts.

Many books provide lists of a software application’s features and describe how to use them. This book, however, takes the model one step further by applying these features to the subject of data analysis. You will learn about the advantages and the drawbacks to several data analysis techniques, and you will learn many data analysis skills as you practice using Microsoft Excel and other Microsoft data analysis software.

Who This Book Is For

The audience for this book can be divided into four main groups of business computer users: data browsers, data analysts, business decision makers (including small business owners), and solution developers. This book provides helpful information to members of all four of these audiences.

Data browsers generally need read-only access to data. Data browsers view, sort, and filter existing data in spreadsheets, PivotTable reports, charts, and other deliverables created by data analysts. Data browsers don’t need to worry about connecting to data or creating new reports based on existing data. Data browsers make business decisions, but only as these decisions affect their individual workgroups or individual customers. Examples of data browsers include hotel front-desk clerks, bank tellers, retail cashiers, manufacturing shift workers, customer service representatives, sales representatives, and administrative assistants.
Data analysts create reports and other data analysis presentations for both data browsers and decision makers. Data analysts connect to original data sources and build spreadsheets, PivotTable reports, charts, and other deliverables from scratch. While data analysts occasionally field report requests from data browsers, much of a data analyst’s day is spent creating ad hoc data analysis reports at the request of business decision makers. Data analysts add richness to reports through callouts, footnotes, formatting, and comments. Data analysts frequently create new reports based on existing reports and advise business decision makers by providing preliminary data analysis. Examples of data analysts include actuaries, accountants, marketing representatives, human resource personnel, researchers, paralegals, and financial analysts.
Business decision makers guide organizations by making far reaching decisions based on data. Business decision makers might create their own reports, but they might also work closely with data analysts to define and refine key reports. In larger organizations, business decision makers educate data analysts on trends, key performance indicators, and other data facts associated with the business. They also educate data browsers to make good decisions, based on data, through business goals and objectives. Examples of business decision makers include small business owners, regional and district managers, unit managers, shift managers, chief financial officers, chief executive officers, chief operating officers, corporate vice presidents, and boards of directors.

Solution developers program Office applications and features in order to automate data reporting and analysis. Solution developers do not create reports or make business decisions, but they work throughout an organization to understand business and reporting requirements. They translate these requirements into programs that make the creation of reports—and the decisions that rely on those reports—faster, easier, and more predictable. Examples of solution developers include in-house software engineers, contract programmers, independent software vendors, and systems analysts.

Note

Academic researchers, physical and theoretical scientists, mathematicians and statisticians, and so on have their own unique data analysis needs. The needs of these professions are not specifically addressed in this book.

Organization of This Book

This book contains 12 chapters and 2 appendixes. The following is a brief summary of these chapters and appendixes. Understanding the book’s contents will help you go to specific parts of the book that interest you, or you can just start with the earlier chapters to gain general knowledge and then move to the later chapters to build on your skills.

Chapter 1, “Making Sense of Data,” introduces you to the field of data analysis, helps you understand what types of data you can and cannot analyze with Microsoft software applications, helps you understand the various data formats and how Microsoft Office applications handle these data formats, and describes how to troubleshoot data compatibility issues.
Chapter 2, “Basic Data Analysis Techniques,” introduces you to techniques such as sorting, filtering, formatting, importing, exporting, querying, creating charts, and pivoting data. This chapter also introduces how to work with data lists, relational and multidimensional data, and Extensible Markup Language (XML) data.
Chapter 3, “Analyzing Data with Microsoft Excel,” goes into detail about the features in Excel that can be used to sort, filter, format, import, and query data. This chapter describes how to work with charts and Web-based data and how to use the Analysis ToolPak and the Solver Add-In.
Chapter 4, “Analyzing Data with PivotTable and PivotChart Reports,” makes sense of PivotTable report and PivotChart report basics and describes how to use these tools for more advanced data analysis tasks.
Chapter 5, “Analyzing Data with Microsoft Access,” helps you understand the differences between relational and nonrelational data; how to use Access to get at external data; how to sort, filter, and query data; how to create reports; and how to analyze data by using PivotTable and PivotChart views in Access.
Chapter 6, “Analyzing Data with the Office Web Components,” introduces you to the features of the Spreadsheet Component, the PivotTable Component, and the Chart Component—tools you can use to present and analyze data on the Web.
Chapter 7, “Introducing Online Analytical Processing,” describes what online analytical processing (OLAP) is, why OLAP is important in the data analysis field, and how OLAP can be used to make better decisions. The chapter also includes an introduction to online transaction processing (OLTP) systems and the OLAP features in Microsoft Office.
Chapter 8, “Analyzing OLAP Data with Microsoft Excel,” continues the discussion of OLAP by demonstrating how to connect to, query, and work with OLAP data. The chapter describes how to create offline OLAP data cubes and in which scenarios this is helpful.
Chapter 9, “Analyzing OLAP Data with Microsoft Data Analyzer,” introduces Microsoft Data Analyzer and how you can use it to connect to and view OLAP data in a relatively new graphical application.
Chapter 10, “Working with XML Data in Excel and Access,” provides an overview of the Extensible Markup Language (XML) and its purpose, how to read an XML document, and how to use Excel and Access to analyze XML data.
Chapter 11, “Extending Office Data Analysis Features with Code,” describes how to program and automate Microsoft Office data analysis applications and features. Specifically, the chapter explains terms such as macros, procedures, and object models. You will learn the basics of how to program Excel, Access, the Office Web Components, and Microsoft Data Analyzer to build data analysis solutions
Chapter 12, “Maintaining Data Reporting and Analysis Systems,” introduces the requirements for maintaining Microsoft SQL Server and Microsoft Access databases to ensure the availability of your data storage systems and to foster data analysis.
Appendix A, “Data Analysis Quick Reference,” provides condensed procedures for working with the data analysis features of Excel, Access, the Office Web Components, and Data Analyzer.
Appendix B, “Additional Tools and Resources,” describes the data analysis tools provided on the CD accompanying this book and tools available on line at Microsoft’s Web site (http://www.microsoft.com).

Where to Go From Here

While you can read this book from cover to cover, you will get faster results by focusing on the types of data your organization uses and the role or roles you serve in analyzing that data.

All users should read Chapters 1 and 2.
Users of Microsoft Excel and the Microsoft Office Web Components should read Chapters 3, 4, and 6.
Microsoft Access users should read Chapters 5 and 6.
Microsoft SQL Server users should read Chapters 3 through 6.
Users of XML data should read Chapter 10.
Readers who need information about OLAP and Microsoft Data Analyzer should read Chapters 7 through 9.
Data browsers, data analysts, and business decision makers should read Chapters 3 through 6.
Solution developers should read Chapters 3 through 6 and Chapter 11.
System administrators should read Chapter 12.

Features of This Book

While you can certainly learn more about a given subject by reading a book about it from cover to cover, studies show that you can learn concepts more quickly and retain information longer by putting your newfound knowledge into practice. Throughout this book, you will see special sections with the headings Your Turn and Putting It Together. These sections provide opportunities for you to perform exercises to reinforce what you read. To perform these exercises, you must have access to the sample files that are included on the CD that accompanies the book, as well as a computer that meets the system requirements described later in this introduction. Notes, tips, cautions, warnings, and additional sidebars appear throughout the text to give you extra or important information.

Many of this book’s procedures work the same in Microsoft Office XP and Microsoft Office 2000 applications. However, for ease of reference, this book’s procedures follow the steps you would perform in Office XP. If you are using Microsoft Office 2000, you might need to follow slightly different steps, and these differences are often noted in the book’s text. Procedures that work only in Office XP are identified with a note.