Excel and data analysis

Abstract

Introduction Thorough analysis of experimental data frequently requires extensive numerical manipulation. Many tools exist to assist in the analysis of data, ranging from the pocket calculator to specialist computer based statistics packages. Despite limited editing and display options, the pocket calculator remains a well-used tool for basic analysis due to its low cost, convenience and reliability. Intensive data analysis may require a statistics package such as Systat or Origin. As well as standard functions, such as those used to determine means and standard deviations, these packages possess advanced features routinely required by researchers and professionals. Between the extremes of the pocket calculator and specialised statistics package is the spreadsheet. While originally designed for business users, spreadsheet packages are popular with other users due to their accessibility, versatility and ease of use. The inclusion of advanced features into spreadsheets means that, in many situations, a spreadsheet is a viable alternative to a statistics package. The most widely used spreadsheet available for personal computers (PCs) is Excel by Microsoft. Excel appears within this book in the role of convenient data analysis tool with short sections within most chapters devoted to describing specific features. Its clear layout, extensive help facilities, range of in-built statistical functions and availability for both PCs and Mac computers make Excel a popular choice for data analysis. This chapter introduces Excel and describes some of its basic features using examples drawn from the physical sciences. Some familiarity with using a PC is assumed, to the extent that terms such as ‘mouse’, ‘pointer’, ‘Enter key’ and ‘save’ are assumed understood in the context of using a program such as Excel. What is a spreadsheet? A computer based spreadsheet is a sophisticated and versatile analysis and display tool for numeric and text based data. As well as the usual arithmetic and mathematical functions found on pocket calculators, spreadsheets offer other features such as data sorting and display of data in the form of an x – y graph. Some spreadsheet packages include more advanced analysis options such as linear regression and hypothesis testing. An attractive feature of many spreadsheets is the ability to accept data directly from other computer based applications, simplifying and speeding up data entry as well as avoiding mistakes caused by faulty transcription.