Online College Courses for Credit

+
Spreadsheet Basics

Spreadsheet Basics

Rating:
Rating
(0)
Author: Sophia Tutorial
Description:

Use productivity skills to organize income and expenditures in a tabular financial plan.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

37 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 32 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial
what's covered
In this lesson, you will learn how using a spreadsheet is a convenient and productive way to organize your finances. You’ll use your technology skills to help build the elements of a budget such as income and expenses. Specifically, this lesson will cover:
  1. Getting Started with Spreadsheets
    1. Key Terms
    2. Adding Data to a Table
  2. Introduction to Basic Formatting and Layout
    1. Table Styles
    2. Cell Format
  3. Rearranging Tables
    1. Add Columns and Rows
    2. Delete Columns and Rows
    3. Shortcuts: Cut, Copy, Undo
  4. Changing Width of Columns and Rows
    1. AutoFit Column Width to Text
    2. Set Columns to Same Width

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

before you start

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

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.

Fashion by the Numbers

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.

a basic spreadsheet interface showing a row, column, cell, and tab for accessing multiple sheets near the bottom

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

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:

a drop down menu showing Add-ons followed by table styles

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.
two style menus showing buttons for dollars, percent, decimal places, and comma placement

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?

Technology: Skill Reflect
How are you feeling at this point? Do you feel like your technology skills are where they need to be to use a tool like Excel or Google Sheets? If not, what steps can you take to become more comfortable with these tools?


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 length. 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.

a popup window that reads, resize column A; pixel width is set to 100 pixels

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.
summary
By getting started with spreadsheets, you now have an efficient way to organize your budgets and financial plans in tabular form. Adding data to a table starts with an understanding of key terms: cell, row, column, and sheet. Most data is easier to interpret if you optimize the formatting and layout of your tables. For instance, you can apply custom colors using table styles or format the cell contents to suit your needs. Taking the time to improve your technology skills so you can use tools like this will pay off in the end.

You can also improve your productivity skills by using the shortcuts for rearranging tables. Knowing the steps for adding or deleting columns and rows will save you time. Changing the width of columns and rows is also important so your reported income and expenses are accurate and legible.

Source: THIS WORK IS ADAPTED FROM “CREATING A NEW WORKBOOK” BY SHELLI CARTER, PROVIDED BY LUMEN LEARNING IN ACCORDANCE WITH CREATIVE COMMONS LICENSE CC BY ATTRIBUTION 4.0 INTERNATIONAL.

Terms to Know
Cell

An area where you enter data into a spreadsheet.

Column

A group of cells aligned vertically in a spreadsheet.

Pixel

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

Row

A group of cells aligned horizontally in a spreadsheet.

Sheet

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

Spreadsheet

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