COSC 122 - Computer Fluency
Lab 9: Spreadsheets using Microsoft Excel

In this lab, we will use Microsoft Excel to produce spreadsheets.

Spreadsheets

A spreadsheet organizes information into a two-dimensional array of cells (a table). Each cell has a row number and column label which combine to represent its address. A cell location can store a number, text, or formula. The power of a spreadsheet is that we can write simple formulas (commands) to perform calculations and immediately see results of those calculations. Spreadsheets are very common in accounting and reporting applications.

TASK: Open Microsoft Excel (Start->All Programs->Microsoft Office 2013->Excel 2013) and explore its interface.

Basic Editing

Data Entry

Data entry can be performed by typing data into a cell or formula bar. The data entered may be a text label, a value, or a formula. Formulas start with an equal sign ("=").

Selecting Cells

Cells can be selected in a variety of ways:

• With the mouse, (left) click and drag the mouse to select a rectangle region of cells.
• With the keyboard, hold the SHIFT key and use the arrow keys to select a rectangle region of cells.
• With the mouse and keyboard, while holding the CTRL key, (left) click on individual cells to select non-contiguous cells.
• Click on a row number to select a whole row.
• Click on a column header to select a whole column.

Cut, Copy, Paste

The common operations of cut, copy, and paste can be used with spreadsheet cells. However, there are some special features that simplify these activities:

• Filling - Grab the bottom right corner of the cell and drag to copy values to adjacent cells.
• Moving - A cell can be moved by grabbing it on one of the edges and dragging to the target cell.

TASK: Enter data as shown below. Use copy techniques to avoid typing!

Formulas

A formula is any expression that begins with an equal sign ("="). The equal sign indicates to the spreadsheet that a calculation must be performed to compute the value of the cell. An aggregate formula computes a summary function over a range of cells. The values can either be literals or cell locations. We will use the SUM() aggregate function for formulas in this lab.

TASK: Create formulas that calculate values for the Year-to-Date, Total Expenses, and Net Surplus/Deficit cells as below.

Formatting

Standard formatting consists of changing font types and sizes, colors, bold, italic, underline, etc., and can be performed like in a word processor using similar menu items and command buttons. It is also possible to format cells by selecting them and then selecting the Format menu option, then Cells...

TASK: Format cells as shown and described below.

• Merge cells A1 to F1. Change font to Arial 14 and bold.
• Change font color to blue for cells B3 to F3.
• Bold cells A4, A6, A14, and A16.
• Put cells F4 to F14 in italics.
• Bold cells B16 to F16 and put a border line on the bottom of each cell.
• Format all values as currency.

Conditional Formatting

Conditional formatting allows you to change the cell format based on data values.

TASK: Create conditional formatting on cells B16:F16 where cell font is red if negative and green if positive.

Expert Tips

• Pressing the Ctrl and Home keys at same time will select cell A1.
• Column width can be auto-adjusted to fit maximum column width by double clicking on the line between two columns.
• Using filling will automatically fill in days of week and months of year appropriately.
• To see all spreadsheet formulas, press Ctrl and ~ at same time. Press Ctrl and ~ to go back to the normal view.

Charts

A chart is a graphical representation of spreadsheet data. A chart is of a particular type (line, bar, etc.) and requires the user to supply data that will be displayed in the chart.

TASK 1: Create a new 3D cluster-column chart with data cells B16:F16 and B3:F3. Details and diagram below.

• Chart title="Net Surplus/Deficit", No legend, Show data values.
• Create as new chart on sheet called "Bar Chart".
• Formatting: White background, red bars, all values should be in font Arial 18 and black. Each axis and title should be in Arial 18 bold and black.

TASK 2: Create a new 3D pie chart with data cells A7:A12 and F7:F12. Details and diagram below.

• Data labels should show percent. Legend on bottom. Chart title="Where My Money Goes".
• Format all labels, legend, and chart title to be Arial 18 bold. Make sure they display nicely. Colors do not matter.
• Create as new chart on sheet called "Pie Chart".

Grading (10 Marks Total)

1. +2 marks - for data entry
2. +2 marks - for formulas
3. +2 marks - for formatting
4. +2 marks - for bar chart
5. +2 marks - for pie chart