Excel 2007 Basics Tutorial

Microsoft Excel and other spreadsheet software is often used for Data Analysis. This will be the theme of this tutorial because entering and analyzing data requires proficiency with many of the basic functions, operations, and features in Excel.

The three main steps to data analysis are 1) Entering Data, 2) Analyzing the Data, and 3) Reporting the Data. Part 1 of this tutorial will help you enter your data into Excel, edit it, create simple formulas, and format cells.

Before You Begin ...

Before beginning this tutorial, click on the help link below and organize the windows on your screen so that you can read the online help as you complete the tutorial. If you have not already done so, please read through the introduction and watch the demo video.

Entering and editing data
(This link opens in the Excel Help window)

 Top of Page

Exercise 1

Objective Create a table of income and expense data that looks exactly like the one shown below.

• Entering and Editing Data
• Creating Simple Formulas
• Basic Cell Formatting

 Top of Page

Step 1 - Enter Months and Income Data

A. Enter the month names in column A and the monthly income, 2600, in column B.

Enter Numbers or Text

Hint Because the monthly income is the same in each row, you can use the Ctrl+Enter shortcut (see help link below).

Enter the same data into several cells at once

If you mess up and need to edit a cell, double click on the cell or select the cell and press F2.

Edit cell contents

B. Reduce the width of column A.

Change the column width and row height

There is an easier way to enter the months. Try using the tip below.

Tip Some data such as month names, days of the week, and series of numbers (1,2,3,4,...), can be entered more quickly using the Fill Handle. Fill data automatically in worksheet cells

 Top of Page

Step 2 - Enter Expense Data

A. Enter the Expense data into Column C

If you are brand new to Excel, you may want to try entering a few of the numbers manually. If you mess up, read the following help article.

Undo, redo, or repeat an action

Important One of the most important aspects of data analysis is to make sure your data is accurate. Entering data manually is inherently error prone (especially if you have a tendency to fall asleep doing repetitive tasks). Finding the most efficient and accurate way of entering data is part of the art of data analysis.

B. Instead of manually entering the expense data, try copying the numbers from the table below and pasting them into Excel.

Copy and paste multiple items by using the Office Clipboard

Using your mouse, click and drag to select the entire table below. Then press Ctrl+c to copy the selection, or right-click on the selection and choose Copy.

Note FireFox Users: If you are using FireFox, you must start your selection on the line above the first entry in the table (immediately after the word "HERE").

Now, in Excel ... Select cell C1 in your worksheet, and click on the Paste button (or press Ctrl+v). Immediately after doing so, you will see the Paste Options button show up at the bottom-right of the data you pasted. Click on the button and select "Match destination formatting" to avoid pasting the borders.

 Top of Page

Step 3 - Add Title and Labels

After completing Step 3, the table title and labels should look like this...

A. Insert two rows above the first row of data.

Insert rows on a worksheet

Basically, you just right-click on the row number of row 1 and select Insert. Notice all of the other options you have available to you when you right-click on a row number or column letter.

B. Change the font of the title to Bold, 12pt. The font commands are in the Home tab. The shortcut for bold is Ctrl+B.

Format text as bold ...

C. "Merge and Center" cells A1:A4 to get the title to be centered across all of the columns.

Merge cells or split merged cells

D. Add the Income, Expense, and Net labels. The text will be left-aligned by default. Use the Align Right and Align Center commands in the Home tab, Alignment group.

E. Add borders to the bottom of the title and labels.

Apply or remove cell borders ...

Tip You can use the new keyboard shortcut system in Excel 2007 to access commands on the ribbon. For example, to merge and center, Press Alt, then H, then AR. Future references to these types of keyboard shortcuts will be specified like this: Alt | H | AR.

 Top of Page

Step 4 - Create Formulas

A. Add the formula for the Net calculation in cell D3,
Net = Income - Expense.

Create or delete a formula

1. Select cell D3
2. Type "="
3. Select cell B3
4. Type "-"
5. Select cell C3
6. Press Enter

Video If you would like a training video to help you become familiar with references and entering formulas, take a look at "Get to know Excel 2007: Enter formulas". Play Video

B. Copy the formula from cell D3 to the rest of the cells in column D.

Important Make sure you read the help reference below, especially the part about how Relative and Absolute references behave when copying formulas.

Move or copy a formula

Tip Double-clicking on a cell that contains a formula or clicking in the formula bar will highlight the cells that are being referenced. This is VERY handy for verifying that the formula is referencing the correct cells.

C. Add the Income total at the bottom of column B

1. Select cell B15
2. Type "="
3. Type "SUM("
4. Select the range B3:B14 as shown in the image below
5. Type ")" to finish the SUM formula
6. Press Enter

D. Add the Expense total by copying cell B15 to cell C15

E. Create the formula in cell D15 for the Total Net.

Is is better to use Total Net = Total Income - Total Expense, or Net = SUM(D3:D14)? Does it matter? You decide.

 Top of Page

Step 5 - Format Numbers

The final step in this exercise is to modify the number formats in the table to try to match the screenshot below. No details will be provided. You're on your own here.

Formatting numbers

Hint Okay, one tip. Try using the Format Cells dialog box: Alt | H | FU

Tip It looks like the make believe person in this example is actually living within their means. Not a bad life lesson to take home.

 Top of Page

[ Excel Tutorial Home ]