1
Introduction to Excel

In this chapter, students will learn to:

• Undertake basic operations in an Excel worksheet
• Perform mathematical calculations on worksheet data using formulas and functions
• Understand and apply relative and absolute cell referencing
• Visualize and interpret data sets in the form of charts

Excel is a Microsoft spreadsheet application widely used to store, organize, process and analyze many forms of data, including experimental data. It offers great flexibility and is, in many respects, unrivalled in terms of its functions as applicable to scientific experimental data. Researchers use spreadsheet applications such as Excel to work with experimental data. For example, they will transfer data to a spreadsheet such as Excel to:

• Store and organize experimental data
• Manipulate data using mathematical functions
• Visualize data, for example, through charts and tables
• Perform statistical analysis of data
• Apply curve fitting with linear and non‐linear regression

Besides Excel, other examples of spreadsheet applications exist including free, open source software packages such as LibreOffice Calc and Google Spreadsheets. They operate in a similar manner to Excel in general, but differ in some features and hence functionality. Microsoft® Excel® has the most features and is currently more widely used than these open source alternatives. That said, the landscape is rapidly changing and these open source software packages are increasing in maturity and popularity. If you have access to Excel, it is the spreadsheet software program of choice. As such, the tutorials in this book are designed specifically around Excel. However, if Excel is not accessible, open source alternatives are a good option to work through the tutorials to learn approaches to processing experimental data.

This chapter introduces basic standard worksheet operations in Excel that will be needed for the later chapters. The tutorial exercises have been designed around Excel for PC. If you are using Excel for Mac, you can expect minor deviations from the tutorial instructions, as formats and styles, and locations of commands and options can differ between the two versions. Likewise, accessing tools and commands may differ if you are using an early version of Excel. However, most functionality is equivalent between versions and so all tutorials here can be undertaken using any version of Excel. Of course, it is advisable to upgrade Office if you are using a particularly archaic version. Once you are up and running with Excel, it is worth spending time working through the tutorials in this chapter to ensure that the more basic spreadsheet functions of Excel are understood before moving to the more advanced topics and tutorials in later chapters.

1.1 Navigating the Workbook

1.1.1 The Worksheet

Launching Excel brings you into a workbook containing a set of spreadsheets. Excel refers to each spreadsheet within a workbook as a ‘worksheet’. Some basic aspects of the worksheet are labelled in Figure 1.1.

Figure 1.1 Highlighted aspects of Excel worksheet for navigation.

The Ribbon menu gives access to all tools and commands. Within the Ribbon tab, you can see several tabs – Home, Insert, Page Layout, Formula, Data, Review, and View. Each of these has their own Ribbon display, which comprises groups of buttons representing a variety of commands that are displayed when each tab is selected.

Contextual tabs are special types of tabs that appear only when an object is selected, such as a chart or a shape. These contextual tabs contain commands specific to whatever object you are currently working on. For example, after you add a shape to a worksheet, a new Format tab appears as a Contextual tab. These tabs only activate when you work with particular objects. You will use these tabs regularly in the tutorials in this book.

The Formula bar is the toolbar at the top of the worksheet window that can be used to enter or copy an existing formula into cells. It is labelled with the function symbol fx. By clicking the Formula bar, or when you type the equal (=) symbol in a cell, the Formula bar will activate.

1.1.2 Worksheet Tools

You can navigate the Excel worksheet fairly intuitively using standard Office365 operations. The first tutorial here will use an already populated worksheet to show you some of the tools available.

Tutorial 1.1 Using Basic Formatting and Analysis Commands

In this tutorial, you will work with a data set relating to the Periodic Table to learn some basic formatting and analysis commands in Excel.

• Open the workbook 1.1_Periodic Table.xls.
• In the worksheet, you will see columns of data related to the periodic table. Expand the width of the columns so that all text in each of the columns can be seen. To do this, bring the mouse cursor to where the row and column headers meet – see Figure 1.2. By clicking here you will select the whole worksheet. Then double‐click any one of the column partition lines. This will readjust all column widths so that you can visualize the data clearly.

Figure 1.2 Periodic table worksheet highlighting row and column formatting navigation.

• Now take a look at column D – Atomic Mass. The values in the cells have 7 decimal places reported which is unnecessary for our purposes. To reduce the number of significant figures, first highlight the data by clicking at the top of column D. Right click and select Format Cells. In the pop‐up dialogue box, select Number and enter 3 in the Decimal Places box. Press OK.
• Next, format the columns of data into a table so that you can sort the data. Highlight columns A to I and under the Home tab, click Format as Table. Choose a style you like in the dialogue box that pops up. Ensure Header Row is ticked in the Table Style Options under the contextual Design tab.
• Next, sort the data in increasing order of atomic radius. Select the greyed icon in G1 to the right of text Atomic Radius. Click on Smallest to Largest and exit out of the box.
  1. ○ Also try sorting the data indifferent ways according to the different properties listed.
• You can visualize the data by creating charts to represent the data. Try graphing Atomic Number against Atomic Mass. To do this, highlight columns C and D. Click the Insert tab and then click Scatter chart type as shown in Figure 1.3. This type of chart is very common when working with experimental data.

Figure 1.3 Generating a scatterplot in an Excel worksheet.

• To format the chart, select the chart and double click into each axis title and chart title to edit the text.
• Click on the x‐axis, and right click and select the Format Axis option. Select Tick Marks and in the Major Type box, and select Inside to add tick marks to the x‐axis. Repeat this for the y‐axis.
• Gridlines are the light grey horizontal and perpendicular lines that divide the chart area into squares to form a grid. To delete these, click on one of the horizontal gridlines, and then right click and select Delete. Repeat this step for the vertical gridlines.
• Click through the previews in the chart styles to change the layout or style to one you like. Depending on your chosen style, your chart might look something like in Figure 1.4.

Figure 1.4 Formatted chart showing the linear relationship between atomic mass and atomic number.

• Using the same approach, create charts to visualize the dependence of electronegativity and atomic radius on atomic number. Decide yourself on the chart type and format and design that you use.
• Save and close the 1.1_Periodic Table.xls workbook.

1.2 Mathematical Operations on Cells

1.2.1 Formulas and Functions

Once data is entered into a worksheet, operations can be performed to process the data. Excel performs mathematical operations using formulas and functions. Formulas can be written into the formula bar and always begin with an equals sign (=).

These formulas and functions act on specified cells in a worksheet, where variables can be defined in other cells that are referenced. There are two types of cell references used by Excel: relative and absolute. Relative and absolute behave differently when copied and filled from other cells. Using a letter‐number combination, e.g. A2, to describe a cell is known as relative referencing. By default, all cell references are relative references. These references change based on position relative to the original cell when the formula is copied and pasted into another cell. The effect is to keep the relative addresses between cells referenced in a formula, in effect making these variables.

In contrast, absolute referencing uses the format $letter$number, e.g. $A$2, and remains constant when copied and filled from other cells. If...