Getting up to Speed with Excel 2007 March 9, 2011

Getting Up to Speed with Excel 2007
March 9, 2011

Nicole McGee

I. Introduction

II. Objectives

Learn basic definitions and navigation skills
Create, edit, and format a simple spreadsheet
Learn basic calculation functions

III. Definitions

Cell
Worksheet
Workbook
Row
Column
Spreadsheet
Data
Chart
Formulas
Functions (math functions )
Database

IV. Getting around (navigating in Excel) – see also Customguide.com Quick Reference Sheet

V. Selecting Cells, Cell Ranges, Columns, and Rows

Your mouse cursor can look several different ways depending on where you put it in your spreadsheet – in relation to the cells. It can be:

a chunky cross
a black thin cross
the typical arrow
a four-pointed arrow
a two-pointed arrow
a two-pointed diagonal arrow

Selecting cells is useful when you want to apply the same operation to one or more cells, such as when you add formatting (add color, display numbers as dollar amounts, etc.)

Select a single cell by putting the mouse cursor into the cell you want and click. Note the cell address, which consists of the column letter and row number. Each cell has an address that includes its coordinates- the column first and then the row, e.g. A1.
Select a range of cells (a block) by clicking in the upper right-hand corner of the range you want. Then hold down the shift key while you click in the lower left-hand corner of the block you want. This will select the entire block of cells. Another option is to click in the first cell and drag your mouse diagonally down to the last cell you want so that all the cells you want are highlighted.

click

NOTE that another way to refer to a cell range is by referencing the first cell and last cell with

acolon in the middle, e.g. A1:E11

Select a column or row by clicking on the letter or number that corresponds to the column/row you want.
Select non-adjacent cells by clicking in one, then pressing the Ctrlkey while clicking in the next cell you want to highlight. You can reference these cells in formulas using a comma, e.g. A1,B3,D6,E4.

VI. Creating a spreadsheet (e.g. annual expenses)

Using a sample file, we’ll be completing a spreadsheet. In the process, we’ll be using a variety of shortcuts and functions.

Entering data:
Using the handout provided, complete the spreadsheet by entering the data for Movies and Music in rows 11 and 12.
Complete the months in row 2:
Click in cell C2.
Hover your mouse cursor in bottom right corner of the cell until it become a handle cross (+)and drag it across row 2 until cell H2. This usesExcel logic to complete the months of the year. It can be done with any logical sequence, e.g. numbers, alphabet, times, etc.
Type TOTALS in A13 and O2.
Type AVERAGES in P2.

Editing and Deleting data:
You can edit data by either:
Clicking in the cell you want to modify and editing the text that appears in the formula bar above the spreadsheet OR
Double-clicking in the cell you want to modify and make your changes within the cell.
You can delete data by clicking in the cell or by selecting a range of cells and hitting the Delete key.
Moving Data by Cutting and Pasting
You can move data by cutting it and pasting it where you want. The simplest way to do this is to use the Clipboard group under the Home tab.
Click the cell that has the data you want to move and then click the Cut button (with the scissors picture).
Click in the new location for your data and the click the Paste button (with the clipboard picture). NOTE that if you paste into a cell that already has data, it will be replaced with the data you are pasting.
If you want to insert data in between rows or columns that already have content, you can either:
click in the data you want to move and choose Cut. Then click in the cell where you want it to go and right click and select“Insert Cut Cells” from the menu that appears.
Or cut the data you want to move, click in the new location, and choose Insert Cut Cells from the Insert function in the Cells group.
Let’s move the row for Movies and the row for Music up to the row below Pets.

Inserting and Deleting Rows and Columns
You can insert rows or columns by using the Cells group under the Home tab.
Note that a column will be added BEFORE the column you select, and a row will be added ABOVE the row you select.
To insert a row, click on the row BELOW where you want the new row and select “insert sheet row” from the Insert button in the Cells group.
To insert a column, click in the column AFTER where you want the new column and select “insert sheet column” from the Insert button in the Cells group.
Delete rows or columns by highlighting the row/column you want to delete and then click on the Delete function in the Cells group and select either “delete sheet row” or “delete sheet column” as appropriate.

You should now have a spreadsheet that looks like this:

VII. Using Basic Formulas

Create totals for columns C-Hin row 13 and totals for rows 3-13 in column O:
Click in cell C13.
Click AutoSum button in Editing group under Home tab and hit Enter.
Note that the colon used in the formula = totaling a RANGE of cells.
Click in cell C13 and hover your mouse in the bottom right-hand corner of the cell until it becomes a handle cross (+) and drag across to column N. This automatically copies the formula to all cells in that range.
Click in cell O3.
Click AutoSum button in Editing group under Home tab and hit Enter.
Click again in O3 and hover your mouse in the bottom right-hand corner of the cell until it becomes a handle cross (+) and drag down to row 13.

Create averages for rows 3-12:
Click in P3.
Click the arrow next to the AutoSum button in the Editing group under Home tab.
Choose the Average function and hit Enter.
Note the cells included in the formula (C3-O3) are now surrounded by a blinking dashed line.
We actually DON’T WANT column O (TOTALS) included in our averages. We can change the cell range by editing the formula in the formula bar above the spreadsheet:
Click in the formula bar right and change I3 to H3, so it should look like this:
=AVERAGE(C3:H3)

You should now have a spreadsheet that looks like this:

VIII. Formatting Cells

Wrapping Text within cells (great for cells with lots of text)
Click on column A.
ChooseWrap Textfrom the Alignment group under the Home tab.
Widen the columns
Put the mouse cursor over the line between columns A and B and double-click. This will automatically widen the column to fit the text that might run over, such as the text in row 6.
Another way to do this is to drag the mouse to the right after you have the cursor in place instead of double-clicking.
Add colors to improve readability
Add color to the labels in column A
Select cells A3- A13.
Click on Cell Styles from the Styles group under the Home tab and select a color.
Add color to the months in C2-N2.
Select cells C2-N2.
Click the paint car icon in the Font group under the Home tab to select a color.
Add color to the totals in row 13 and column O.
Select cells A13-O13
Right-click on your mouse and either:
Click on the paint can icon to select a color OR
Choose “format cells” from the menu that appears to select the FILL tab to select a color.
Repeat for cells O2-O13.
Add color to the Averages in column P.
Select cells P2-P13.
Click on Cell Styles from the Styles group under the Home tab and select a color.
NOTE that if you highlight the entire column by clicking on the cell above A1, you’ll get ALL of it when you print - ALL 1 million rows!
NOTE also the Styles group under the Home tab, which is best used before you label your rows and columns.
Add gridlines to the table to improve readability when printed
Highlight the entire table by:
clicking in cell A1 and dragging the mouse down to P13
OR click in A1 and then hold down the shift key and click in P13.
Click on the borders/lines icon in the Format group under the Home tab and choose “all borders.”
Note the other options-lines just on top of cells, bottom of cells, etc.
Short cuts after selecting the area you want:
right-click on your mouse and choose the borders/lines icon and select where you want your lines OR
Right-click on your mouse and choose “format cells” from the menu that appears and select the Border tab to select where you want your lines.
Format totals to dollar amounts
Click on row 13 to select the entire row.
Click the dollar symbol in the Numbers group under the Home tab.
Repeat for column O and P.

You should now have a nice final spreadsheet that looks something like this:

STILL WANT MORE?

IX.Creating a pie chart

Highlight totals in column O (cells O2-O12).
Click Pie function in the Chart group under the Insert tab.
Choose the 3D style.
Click on the chart and drag it with the mouse down below the table.
Edit the Legend by clicking on the legend area next to the pie chart.
Click on Select Data from the Designtab in the Chart Tools group in upper right-hand corner.
Click Edit in the “Horizontal Axis labels” area.
Click in cell A3 and drag to A12 and hit Enter.
Click OK. This will add the labels in column A to the chart so the numbers have meaning.
Click the chart once more and play around with Chart Layouts group available in the Design tab under Chart Tools. You can have data in each piece of the chart, have labels inside the chart or outside the chart, etc.
Play with the 3D effects such as pulling out a piece to separate all pieces.

You should now have a spreadsheet and pie chart that looks something like this:

Resources

Frye , Curtis D. Microsoft Office Excel 2007 step by step. Redmond, WA : Microsoft Press, 2007.
Walkenbach, John. Excel 2007 bible. Hoboken, N.J. : John Wiley [distributor], 2006.
Muir , Nancy C. Excel 2007. Hoboken, N.J. : Wiley, 2007.
Interactive: Excel 2003 to Excel 2007 command reference guide.
About.com: Spreadsheets
Microsoft Excel 2007 Tutorials.