Using Microsoft Excel for Social Research

Charlotte Brookfield

• Publisher: SAGE Publications
• Year: 2021
• Edition: 1
• eBook ISBN:
  9781529753226
• Topic: Social Sciences
• Subtopic: Data Processing
• Index: Social Sciences

Full of practical advice and real-world examples, this step-by-step guide offers you an accessible introduction to doing quantitative social research using Microsoft Excel.

1 Introduction

This book is designed to help develop skills for quantitative research. Specifically, it focuses on how Microsoft Excel can be used for research projects in the social sciences. Each chapter begins with an outline which details the research methods skills covered in that chapter as well as the specific Excel skills described. The outline also includes the full name of the datasets used in the examples presented in the chapter, which allows the reader to replicate the statistical analysis discussed. All the datasets used in the book are open access and can be downloaded easily free of charge. Chapters are colour coded with ‘green’ chapters for beginners, ‘amber’ for those students with a little more experience and familiarity with quantitative research, and ‘red’ for those confident with the terms and techniques described elsewhere in the book. Each chapter contains at least one activity and one teaching idea. Space is provided for you to complete these activities. The teaching ideas draw on the author’s own experience of teaching quantitative research methods to both pre-university and university-level social science students. Additional readings are also signposted throughout the book.

The rest of this introductory chapter makes the case for using Excel for research projects in the social sciences. It also outlines the contents of the book, so that readers can go directly to specific chapters if they wish. Key terms and Excel shortcuts are included at the end of this chapter. These key terms and shortcuts will be referred to extensively throughout the book and will be useful to return to periodically. As you use Excel with greater frequency, these terms and shortcuts will become more familiar to you.

1.1 Using Excel for Research Projects

Excel is a spreadsheet program that can be used to analyse and visualise data. While lots of statistical software packages exist, the ubiquitous nature of Microsoft Office programs means that Excel can be a viable and important alternative (Barton and Reichow, 2012). A strong case for students and graduates to develop Excel skills has been advanced (Warner and Meehan, 2001). Many work organisations do not have expensive licences for specialised statistical software packages; however, there is often an expectation that graduates and even placement students should have a familiarity with spreadsheets and be able to undertake statistical analysis in Excel. It has also been suggested that the simplicity of the input and output of data in Excel can make the program more user friendly for those new to statistical analysis. Specifically, compared with more specialised packages, Excel does not produce as great a volume of redundant output and therefore it can be easier for users to identify key figures to report, analyse and interpret. This can mean that the analysis is much more akin to examples set out in introductory statistics textbooks, again enabling students to report and interpret their findings with more confidence. In addition to this, students often have a familiarity with Excel or, more broadly, Microsoft Office. The familiar look and menu options can help alleviate some students’ fears of quantitative data analysis.

In this book, examples will be given to demonstrate how Excel can be used to undertake research projects in the social sciences. The data used in these examples can be broadly classed as social science examples and draw on topics including health, work and education. Excel 2016 is used to demonstrate the functionality of the program and its application to social science research. However, many of the techniques and formulae shown can be used on older versions of the program.

Throughout this book you will be introduced to a number of Excel formulae that can be used to undertake statistical analysis. All formulae in Excel begin with an ‘=’ sign. Some useful operators that you will frequently see in formulae are listed in Table 1.1.

Operators often used in formulae in Excel

Table 1.1

In this book, the term ‘select’ is used to describe one single left click of the mouse. When it is necessary to double click, or right click, this is clearly stated.

1.2 A Note on Teaching and Learning Statistics

Previous research has highlighted that some social science students are surprised to encounter number as part of their degree programme (Williams et al., 2008; Chamberlain et al., 2015; MacInnes, 2018a). Therefore, this book aims to highlight the relevance of statistics to studying and understanding the social world. Those students who are apprehensive (or even reluctant) to engage with such techniques may also find these additional resources helpful in increasing their confidence in using numerical approaches:

Jones, R.C. 2020. Essential Maths Skills for Exploring Social Data. London: Sage.

MacInnes, J. 2018b. Little Quick Fix: Know your Numbers. London: Sage.

1.3 Introducing the Chapters

• Chapter 1: Introduction: Introducing key features of this book.
• Chapter 2: Planning and Undertaking a Research Project in the Social Sciences: Considering topics for research and some of the challenges when starting a research project.
• Chapter 3: Selecting, Evaluating and Cleaning Data Using Microsoft Excel: Preparing data for analysis in Excel.
• Chapter 4: Getting Familiar with Your Data: Understanding and describing data using Excel.
• Chapter 5: Exploring Bivariate Relationships: Crosstabulations and Chi-Square Statistic: Exploring whether respondents belong to different categories and investigating whether they are statistically significantly more likely to belong to one response category compared with another using Excel.
• Chapter 6: T-tests, ANOVA and Non-parametric Equivalents: Exploring whether two independent groups are statistically significantly different using Excel.
• Chapter 7: Exploring Bivariate Relationships: Correlation: Exploring the strength and direction of an association between two variables using Excel.
• Chapter 8: Exploring Multivariate Relationships: Linear Regression: Similar to correlation, but exploring how three or more variables relate to each other. Investigating which variables can statistically significantly predict a particular outcome using Excel.
• Chapter 9: Bringing It All Together: Writing and Presenting Research for Different Audiences: Presenting and visualising data for different audiences using Excel.

1.4 Key Words

• Workbook: An Excel file.
• Worksheet: A page (or sheet) in an Excel file. You may have multiple worksheets in an individual workbook.
• Variable: Contains data relating to a specific quality/characteristic/attitude/belief. Datasets contain numerous variables. For example, a dataset may contain the variables highest qualification, age, weight or attitude towards school subjects.
• Observation: An individual case, one respondent’s data.
• Rows: Observations are organised in rows. These go horizontally across the worksheet. This means all the data stored in one row belongs to one independent person. Rows are labelled numerically.
• Columns: Variables are organised in columns. These go vertically down the worksheet. This means all the data stored in one column relates to a particular variable. Columns are labelled alphabetically.
• Cells: Where the rows and columns meet. Each cell contains data relating to a specific participant for a specific variable.

1.5 Excel Shortcuts

• CTRL+O = Open Workbook
• CTRL+W = Close Workbook
• CTRL+S = Save Workbook
• CTRL+0 = Hide Selected Columns
• CTRL+9 = Hide Selected Rows
• CTRL+SHIFT+0 = Unhide Columns
• CTRL+SHIFT+9 = Unhide Rows
• CTRL+A = Select Entire Table
• (CTRL+A)×2 = Select Entire Worksheet
• CTRL+; = Insert Current Date
• CTRL+: = Insert Current Time
• ALT+Enter = Start New Line in Cell
• ALT+H+W = Make All Text in a Cell Visible
• CTRL+Spacebar = Select Entire Column
• SHIFT+Spacebar = Select Entire Row
• CTRL+‘ = Copy Formula from Cell Above
• CTRL+SHIFT+“ = Copy Value from Cell Above
• CTRL+D = Fill Down (Fill Column)
• CTRL+F = Search Worksheet
• CTRL+R = Fill Right (Fill Row)
• CTRL+Y = Repeat Last Entry
• CTRL+ SHIFT+~ = Switch between Viewing Formulae and Values in Cells
• CTRL+N = Create New Workbook
• SHIFT+F11 = Insert New Worksheet
• CTRL+PgDn = Go to Next Worksheet
• CTRL+PgUp = Go to Previous Worksheet

2 Planning and Undertaking a Research Project in the Social Sciences Considering topics for research and some of the challenges when starting a research project

• Colour Code for Chapter:
• Green
• Study Skills:
• Planning, Time management, Thinking ethically
• Research Methods Skills:
• Research ethics, Research management
• Microsoft Excel Skills:
• Creating a Gantt chart

Visit https://study.sagepub.com/brookfield to downloa...

Table of contents

• Cover
• Half Title
• Publisher Note
• Title Page
• Copyright Page
• Brief contents
• Detailed contents
• Author Biography
• Acknowledgements
• Online resources
• 1 Introduction
• 2 Planning and Undertaking a Research Project in the Social Sciences Considering topics for research and some of the challenges when starting a research project
• 3 Selecting, Evaluating and Cleaning Data Using Microsoft Excel Preparing data for analysis in Microsoft Excel
• 4 Getting Familiar with Your Data Understanding and describing data using Microsoft Excel
• 5 Exploring Bivariate Relationships: Crosstabulations and Chi-Square Statistic Exploring whether respondents belong to different categories and investigating whether they are statistically significantly more likely to belong to one response category compared with another using Microsoft Excel
• 6 T-tests, ANOVA Test and Non-parametric Equivalents Exploring whether two independent groups are statistically significantly different using Microsoft Excel
• 7 Exploring Bivariate Relationships: Correlation Exploring the strength and direction of an association between two variables using Microsoft Excel
• 8 Exploring Multivariate Relationships: Linear Regression Similar to correlation, but exploring how three or more variables relate to each other and investigating which variables can statistically significantly predict a particular outcome using Microsoft Excel
• 9 Bringing it All Together: Writing and Presenting Research for Different Audiences Presenting and visualising data for different audiences using Microsoft Excel
• References
• Index