Spreadsheet Basics

before you start

In previous tutorials, you learned there are a variety of spreadsheet applications offered by third parties that are available for free or for purchase.

If you have a spreadsheet available, keep it open in a separate browser tab so you can practice the skills presented in this tutorial. Look for the sections marked Try It as you work.

1. Getting Started with Spreadsheets

watch

You might be new to spreadsheets and looking to improve your technology skills , or you might already know about their computing power and usefulness in organizing information. In either case, check out this success story of one designer’s experience with spreadsheets as a tool to boost her productivity.

1a. Key Terms

To open a blank spreadsheet (sometimes called a workbook), double-click the blank spreadsheet option. Most spreadsheets look something like this:

fx	A	B	C
1
2
3
4

Before using a spreadsheet, it is helpful to know a few key terms.

• A cell is the area where you will enter data.
• The rows are groups of cells aligned horizontally.
• The columns are groups of cells aligned vertically.
• A sheet (sometimes called a worksheet) is a single page within a spreadsheet. Like the tabs in an internet browser, the tabs in a spreadsheet show different pages, or sheets. A spreadsheet may have many sheets included in it.

In the illustration below, the spreadsheet has one sheet and one tab, which is labeled Sheet 1. The selected tab shows the selected sheet. Clicking the + button will add another sheet. When you save a spreadsheet, all of the sheets in it are saved.

term to know

Spreadsheet

A computer software program used to organize and manipulate data; also called a workbook.

Cell

An area where you enter data into a spreadsheet.

Row

A group of cells aligned horizontally in a spreadsheet.

Column

A group of cells aligned vertically in a spreadsheet.

Sheet

A single page within a spreadsheet; also called a worksheet.

1b. Adding Data to a Table

Spreadsheets are used to clearly organize data. Numerical data can be used in a variety of ways. The first step, however, is simply to enter data into a table.

First, open a blank spreadsheet. Next, click on the first cell for your data. This will almost always be A1.

fx	A	B	C
1	Enter data here.
2
3
4

If you want to organize your data in a column, you will hit the Enter key to move to the next cell down. In this case, the next cell is A2.

fx	A	B	C
1	..........
2	Enter more data here.
3
4

If you wish to organize your data in a row, you will hit the Tab key to move to the next cell over. In this case, the next cell over is B1.

fx	A	B	C
1	..........	Enter more data here.
2
3
4

Enter all of your data into each individual cell until you are ready to save your spreadsheet.

try it

Open a new sheet in your spreadsheet application. Use the Tab key and Enter key to enter numerical data into the cells of your sheet. If you were building a budget with income and expenses, where might you place them?

---

2. Introduction to Basic Formatting and Layout

Spreadsheet programs will default to certain fonts, texts, and styles when you create a new sheet. However, it is easy to format individual cells with cell styles or multiple cells with a table style. Not only can formatting help make your tables more attractive, but it can also indicate information about the contents, such as whether the data in a cell reaches a target goal.

2a. Table Styles

 A common task is to format a cell or table with a particular style.

hint

Styles let you apply color schemes to tables that can make them more readable. You can select a default scheme or create a scheme of your own.

fx	A	B	C
1	Variable Expense	Projection	Actual
2	Gasoline	$100	$122
3	Auto Repairs	$50	$18

In some programs, you can obtain styles for free through third parties by clicking Get Add-Ons from the Add-Ons drop-down menu at the top of the Home page:

In other programs, several styles are provided by default. If so, you’ll find the controls for table styles in the Styles group of the ribbon on the Home page.

2b. Cell Format

Spreadsheets will default to certain styles when you create a new sheet. This includes the way that numbers are displayed and whether or not commas are automatically included. In this section, we will take a look at changing these defaults.

You will need to format your cells to properly display the information you are entering. When possible, consider formatting your cells before you enter the data. Otherwise, the program may convert some of the entries and you will need to re-enter that information.

step by step

1. Begin by highlighting the cells you plan to use.
2. Most programs have formatting shortcuts in the ribbon at the top of the Home page. The ribbons might look like those shown below.
3. Click on the desired format. Formats include dollars, percent, comma placement, number of decimal places, and more.

fx	A	B	C
1	Variable Expense	Projection	Actual
2	Gasoline	$100	The format for cells B2 and B3 is ($) with no decimal places.
3	Auto Repairs	$50

There are several format options available for cells. When you select one, the program will provide you with a description and examples of how the information will display in the cells.

try it

Open your spreadsheet application. Try to enter a few values inside the cells in terms of dollars and cents. Afterward, see if you can apply a table style to several rows and columns in your sheet. If this were an actual budget, which table styles would make for better reading?

---

3. Rearranging Tables

At times, you may find it necessary to change the number of rows or columns within your table, move columns or rows, or even alter the standard width of rows and columns. All these tasks are easy to accomplish.

3a. Add Columns and Rows

Note, for this section, the word row will be used throughout but the same steps can be used to add a column. After you have entered several rows of data into a worksheet, you may realize you need to add another row in the middle of what you have already typed.

step by step

1. Select the row number where you want to insert a new row.
2. Right-click and select Insert 1 Above or Insert 1 Below from the popup menu (some programs are just labeled Insert).
3. A new row will appear above (or below) the row that you right-clicked.

EXAMPLE

Suppose you want to insert a row between rows 2 and 3. Right-click on the number 3 in the fx column.

fx	A	B	C
1	Variable Expense	Projection	Actual
2	Gasoline	$100	$122
3	Auto Repairs	$50	$18

Next, select Insert 1 Above (or just Insert) from the popup menu. The result is a table with four rows instead of three. Row 3 is now ready to accept new data.

fx	A	B	C
1	Variable Expense	Projection	Actual
2	Gasoline	$100	$122
3
4	Auto Repairs	$50	$18

3b. Delete Columns and Rows

Note, for this section, the word column will be used throughout, but the same steps can be used to delete a row. After you have entered several columns of data into a worksheet, you may realize you need to remove one column.

step by step

1. Right-click on the column letter of the column you wish to delete.
2. From the popup menu, select Delete Column (some programs are just labeled Delete).

EXAMPLE

Suppose you want to delete column B. Right-click on the letter B at the top of the column.

fx	A	B	C
1	Variable Expense	Projection	Actual
2	Gasoline	$100	$122
3	Auto Repairs	$50	$18

Next, select Delete Column from the popup menu. The result is a table with three columns instead of four. The data in the original column B has been removed.

fx	A	B
1	Variable Expense	Actual
2	Gasoline	$122
3	Auto Repairs	$18

3c. Shortcuts: Cut, Copy, Undo

The following commands are helpful when modifying data in a spreadsheet. Begin by selecting a cell or group of cells. Then apply the command using the keyboard shortcut:

Command	Keyboard Shortcut	Action
Cut Paste	Ctrl + X Ctrl + V	Move data temporarily to the clipboard. Paste data in a specified location.
Copy Paste	Ctrl + C Ctrl + V	Copy data temporarily to the clipboard. Paste data in a specified location.
Undo	Ctrl + Z	Undo the previous command.

Use cut and paste when you want to move data from one cell to another. Use copy and paste when you want to duplicate data. The undo command is useful if you make a mistake or series of mistakes and would like to revert to an earlier version of your work. Use of shortcuts like these can save you time and increase productivity.

try it

Open your spreadsheet application. Try to insert and delete columns and rows. If you make a mistake, apply the Undo command. Practice using Ctrl + X and Ctrl + C to cut and copy data from cell to cell.

---

4. Changing Width of Columns and Rows

After you have entered several columns of data into a worksheet, you may realize you cannot read all the information in every cell. In this case you may want to resize the column width (or row height). Alternatively, for visual appeal, you may want all the columns in your table to have the same width. The steps below explain how to resize a column, but the same steps can be used to resize a row as well.

4a. AutoFit Column Width to Text

First, we will discuss making a column width AutoFit to the length of text in that column. Note that when you AutoFit column widths, the program will make the column length match that of the longest string of text in any cell in that column.

The # symbols in this illustration indicate a value that exceeds the designated width of the column. Resizing column B will fix the error.

	A	B	C
1	Income	Projection	Actual
2	Employment Income	###########
3	Investment Income	###########

step by step

1. Identify the column you wish to resize.
2. Hover your mouse on the right boundary of the topmost cell of that column so that you see a double-headed arrow (some programs have a T shape instead). If you are resizing a row, you will hover over the bottom boundary.
3. Double-click on that right boundary.
4. The column will automatically resize to fit the longest string of text in that column.

4b. Set Columns to Same Width

For visual reasons, you may want all columns (or rows) to have the same width (or height), regardless of the amount of text in them. Or you may want a single column to have a specified width.

In this case, you will use the pixel information provided by your spreadsheet program to manually resize the columns.

step by step

1. Identify the column you wish to resize.
2. Right click on the column letter at the top of that column. If you’re resizing a row height, right-click the row number.
3. Choose Resize Column from the popup menu.
4. Enter the Column Width that you desire in pixels (see illustration below).
5. Click OK.

hint

Some programs allow you to change the column width dynamically. In such cases, hover your mouse on the right boundary of the topmost cell of that column so that you see a T shape. Click and drag the column border side to side to see the pixels displayed dynamically.

try it

Open your spreadsheet application. Try changing the column width of certain columns (or row heights of certain rows) using AutoFit to text. Next, see if you can make several columns the same width (or rows the same height) by manually entering the number of pixels. Has the appearance of your spreadsheet improved? Why or why not?

term to know

Pixel

Short for picture element; a minute, single point in a picture on a display.