Microsoft Excel Basics 12/02
Version X,10/02
Skills
About Workbooks
Moving Around a Worksheet
Toolbars and Palettes
Entering, Editing, and Moving Cells
Worksheet Basics
Formula Basics
Refining the Document Look
Formatting Cells
Creating Charts
Text Boxes
Graphics
Printing
Launching Excel
When you start Microsoft Excel the Project Gallery will open. The Project Gallery allows you to access blank and pre-formatted documents in the form of templates and wizards. For now, make sure that Blank Documents (left side) and Excel Workbook (right side) are selected, click OK. If you do not see the Project Gallery at startup, you may be using an earlier version of Excel. For most skills, it will work the same way.
A blank Workbook will appear on your screen. You can create additional workbooks at any time by going to File/New Workbook.
Toolbars and Palettes
Depending on the version of Excel that you have, you may be working with the Formatting Palette as well as traditional toolbars. At times you may find that these items are missing or in the way and may need to be accessed and/or repositioned on your screen. Click the link above (or if you are attending a workshop, check the handout) to see how to reset these options.
Tip: By moving a mouse over any item on a toolbar or palette, the item's function will appear.
About Excel Workbooks
Note: If the NameBox is not visible, then go to the View Menu, and select Formula Bar.
An Excel Workbook is made up of Worksheets, which can be accessed by clicking tabs at the bottom left corner of the Workbook. Each sheet contains a grid made up of individual cells in which data can be placed. Each cell has a specific address that corresponds to its row and column headings. For instance, the address of the selected cell above is A1. Notice that the Name Box contains the address of the selected cell. The selected cell is also called the active cell.
Moving Around a Worksheet and Selecting Cells
Although there are many ways to move from cell to cell, the most commonly used methods appear below.
**Always click return, shift-return, tab, shift-tab or arrow keys after entering information in a cell.
- click a cell to select it, enter data
- click on a cell to select it, click the formula bar (edit line) to edit
- use the arrow keys to move up, down, left and right
- use the tab key to move to the right
- use return to move down
- type the desired address in the Name Box and hit return
- type the desired range (i.e. b7:G66) in the Name Box and hit return
- select multiple cells (a range of cells) by dragging over more than one cell
- naming a cell or range of cells in the Name Box creates easy access through the Name Box's drop down menu
- use the command (apple) key to select a range of non-adjacent cells
- select a row or column of cells by clicking the row or column heading (letter or number)
Entering, Editing and Moving Cells
Entering Cell Data
- to edit a cell either:
- double click it and enter desired data in the cell or formula bar (edit line)
- to scratch the edit, hit the escape key
- align text and numbers via the alignment tab on the formatting palette, or the formatting toolbar
- select a range of cells, type data for the top left cell
- use tab, return or shift-tab to continue entering-excel will keep within the bounds of your selection
- to fill a column or row with the same data
- select the cell with the desired data, along with the range of cells you wish to fill
- go to Edit/Fill, and select the appropriate fill direction
- to enter a pattern of text or numbers into a row or column (such as months of the year) follow the steps below:
- select a cell and enter your data (January)
- select an adjacent cell and enter data ( February)
- select the two cells by dragging the mouse
- carefully move the mouse over the lower right hand corner of the second cell, the cursor will appear as a box with two triangles (fill cursor, or a narrow black plus sign)
- drag the fill cursor over the cells to be filled with the remainder of the pattern
Moving Cell Contents
- move the cursor on the edge of the cell perimeter, when the cursor becomes a hand (or in older versions an arrow), drag the cell to the new location
- move a range of cells in the same way
- copy a row or column and use menu command Insert/Copied to place it inbetween two existing rows or columns
Inserting and Deleting Cells
- to insert cells, go to the Insert Menu and select Cells
- Excel will ask how other cells should be shifted
- to insert cell rows, go to the Insert Menu and select rows
- to insert cell columns, go to the Insert Menu and select columns
- to delete cells, select desired cells, go to the Edit Menu and select Delete
Note: Deleting a cell deletes the entire cell while clearing a cell clears either the contents, format or both.
Clearing Cell Contents
- select the cell you would like to clear, and go to Edit/Clear then choose
- all- to clear contents, format and notes
- contents- to clear contents but not format
- format- to clear format but not contents
- comments- to clear comments only
Comments
Comments can be added to any cell in order to make a notation about that specific cell. Comments can viewed or not, as desired. To create a comment:
- select a cell
- go to Insert/Comment
- type in a comment
- when the cell is deselected, the comment will dissappear, to see it, go to View/Comments
- go to View/Comments to access the Comment Toolbar
Freezing Panes
Panes can be frozen so that row and column headings will stay at the top of the screen.
- select the cell below the row you wish to freeze, or to the right of the column you wish to freeze
- go to Window/Freeze Panes
Panes can also be split by dragging the small blue split bubbles on the horizontal or vertical scroll bars, or going to Window/Split.
*Freezing and splitting panes is especially useful when you have a lot of data. It allows you to move data so that it is adjacent to column or row headings.
Worksheet Basics
Each Workbook comes with three Worksheets. You can select, rename or add Worksheets.
- to make a worksheet active, click its tab on the lower left hand corner of the window
- to rename a worksheet double click its tab and type the desired name, press return
- to add a worksheet click the sheet tab to the right of the desired insertion point, go to the Insert menu and select Worksheet
- to delete a worksheet select any cell in it, go to the Edit menu and select Delete Sheet
- to reorder a sheet, drag its tab to the desired location
- to copy a sheet, click its tab, go to the Edit menu and select Move or Copy Sheet
- select the desired Workbook
- click Create a copy (lower left-hand corner)
- click OK
- to view multiple sheets
- go to Window/New Window and select the sheet you wish to open
- go to Window/Arrange
- select the style in which you would like to see your windows, click OK
Formula Basics
Excel has the ability to apply mathematical calculations to cell contents. Instead of doing the math yourself, you can enter a formula in a cell and Excel will provide the answer. Below, the formula bar shows that cell E10 should contain the answer to the problem 6+3+3+4.
In the formula bar below, Excel is asked to add the contents of cells C3 and C4. All formulas begin with an equal sign, and when the user hits Return, the formula is tablulated. When the contents of either of these cells change later, Excel updates the answer.
Creating a Formula
"A formula is an equation that perfoms calculations of worksheet data" (Excel Help). All formulas are entered in the edit field and begin with the equal sign. Click the equal sign to the left of the edit bar to begin a formula.
To add the contents of two cells
- click the cell in which you wish the answer to go (destination cell)
- click the equal sign in the formula bar to let Excel know that a formula will be entered
- click the cell with the desired number (if you were adding 2 +3, you would click the cell with 2 in it)
- type an operator (such as +)
- click the cell containing the next operand (repeat the last two steps if you are adding more than two numbers)
- when you are done, click OK
- the answer should appear in your destination cell
By using the fill cursor, you can copy a formula from cell to cell. This is particularly useful in situations where you need to apply the same formula over and over. In the example below, the first column can be added by creating a formula. The last three columns can be added by moving the cursor over the lower right hand corner of cell B8, until the fill cursor appears. The fill cursor can be dragged to the right, until all columns are summed.
Alternatively, you can highlight a cell containing a formula, go to Edit/Copy, highlight the destination cell and go to Edit/Paste Special. Under paste, select the attributes you wish to paste.
Calculating with Functions
 Functions are predefined formulas that come with Excel in order to save you time. Sum and Average are just two of the many functions available. To use a function:
- click the destination cell
- type an = sign, the name of the function and an opening parentheses, i.e. Sum(
- click or drag over the cells to which the formula should be applied
- click Enter OR click the enter button (green check) on the formula bar
- to cancel the formula, click the red X
Using the AutoSum Button
The AutoSum button makes it easy to calculate common functions. Excel will automatically calculate columns first, then ranges or cells to the right or left of the destination cell.
- select a destination cell
- click and hold the small triangle to the right of the AutoSum button
- drag the mouse to select a function
- click return
Pasting Functions
- select the destination cell
- click the paste function button on the Standard Toolbar (fx)
- choose the desired function, click OK
- click OK
- select the cells to include, click OK
Calculating Numbers in Non-Contiguous Cells
- click the destination cell and type =
- enter a function
- click the cells you wish to include
- press return
Using the Formula Calculator
You can also use the Formula calculator to enter formulas. To access it, click the destination cell, go to the Tools menu and select Calculator.
Use the buttons provided to enter your formula.
Copying Formulas
- use the Fill Cursor to copy a formula from cell to cell
Refining the Look of the Document
Column and Row Size
- move your cursor to the line between column or row headings, it will become a double arrow
- drag the row or column to the desired size
- alternatively, double click the line between row or column headings to automatically adjust these sizes
OR:
- go to the Format Menu, choose Column or Row and select the desired choice
- type in the appropriate pixel value for width (or height), as desired
- click OK
Borders, Shading, and Autoformat
There are times when you will want to make your Worksheet look good, either for presentation or conceptual purposes. Excel gives you many ways to do this.
Add Borders
- highlight the desired cells
- click the Borders Type button on either the Formatting Toolbar or Palette
- choose the desired type
- to remove borders, highlight the cell, choose no borders from the Border button
 Add Shading
- highlight the desired cells
- click the Shading Color button on either the Formatting Toolbar (paint can) or Palette
- select the desired color
AutoFormat Cells
- select a range of cells
- go to the Format Menu and select AutoFormat
- choose the desired format, click OK
Merging Cells
There are times when you will want to merge cells to place a title or special data on your worksheet.
- highlight desired cells
- click the Merge and Center button on the Formatting Toolbar ( an "A" with two arrows) OR
- click the Merge checkbox on the Formatting Palette
Formatting Cells
Cells can be formatted in many ways. Some of the most useful are alignment and data type. Alignment formatting allows alteration of text placement and orientation. Data type formats cells according to certain data styles such as date or money. Cells must be selected before formatting can be applied.
- to select all cells, click the cell to the left of the letter A column heading and above the number 1 row heading (<>)
- to select an entire row or column, click its heading
Formatting Alignment
- go to Format/Cells or use the Text Alignment bar
on the Formatting Palette - click the Alignment tab (Format/Cells) or use the Font area of the Formatting Palette
- change horizontal, vertical alignment
- wrap text
- shrink text to fit
- merge cells
- change orientation of cell data
- font, borders and patterns can also be formatted here
- you can also use the Formatting Palette
Formatting Data Type
- go to Format/Cells, click Number or use the Number bar on the Formatting Palette
- increase or decrease the number of decimals
- use the number format menu to format as:
- number
- currency
- date
- time
- percentage
- fraction
- text
- and more
Sometimes you may find that a cell seems to format your data in an unexpected way. In this case, it is a good idea to check your formatting (number) style in the either the Formatting Palette, or the Format Menu.
Locking Cells
Worksheets, workbooks or individual cells can be locked to prevent changes.
- lock an entire worksheet
- go to Tools/Protection/Protect Sheet
- individual cells can only be unlocked from an unlocked sheet
- select the cells you wish to leave unlocked
- go to Format/Cells
- click the protection tab
- deselect locked, click OK
- go to Tools/Protection/Protect Sheet
Chart Terminology
Series- A series is a group of related data that makes up a part of a chart such as a column, bar or pie piece. For instance, class votes on types of pizza might include series on mushroom, pepperoni and cheese pizza.
Data Point - One piece of data in a series.
Plot Area- The area of a chart in which the data is plotted. Double click it to add some interesting backgrounds.
Chart Area- The background area of the chart. Double click it to add some interesting backgrounds.
- select the data you wish to chart
- click the chart wizard button on the Standard toolbar (column graph and wand)
- follow the chart wizard directions
- select chart type, click next
- check the data range, click next
- in the Chart Options box, type in a title and axis labels, where appropriate
- experiment with other options and change as desired
- click next
- in the Chart Location window, decide where you wish your chart to appear, in a new sheet, or in the current sheet
- click Finish
A chart will appear in your document.
Modifying a Chart
Perhaps the best way to understand all the chart options available is to experiment with chart modification. If you don't like what you see, just use the undo button on your Standard Toolbar or go to the Edit Menu and select Undo.
There are several tools to help you modify any aspect of the chart, including the Formatting Palette(left) and the Chart Toolbar (below). To access the Chart area of the Formatting Palette, a chart must be selected. Double clicking on any part of your chart will also give you the individual item's options. Alternatively, you can use the Chart Menu at the top of your screen.
On the toolbar, use the drop down menu to change the item for which you wish to see options.
On the Palette, most options are already visible.
If you add a series to your data and would like to add it to an already existing chart, just drag it onto the chart. You can also delete an entire series from your chart by clicking on it and hitting the delete key.
Sizing and Moving a Chart
Since your chart has many areas including plot and chart, it may be somewhat confusing the first few times you try to size or move your chart.
- size the plot area by clicking just outside the graph itself and dragging a handle
- size the entire chart area by clicking the outer area of the chart and dragging a handle
- size the legend by clicking on it and dragging a handle
- in some chart types, click a chart corner and drag it to change its orientation
- move a chart by dragging its chart area
Text Boxes
Text Boxes allow you to place text in a box that can be moved anywhere on your page. If you wish to add a paragraph or more to your document, this is a good way to do it. Then you can add shadows or various fill colors to make it look more appealing.
Creating a Text Box
- Go to View/Toolbars, drawing and click on the Insert Text Box button on the Draw toolbar, your cursor becomes a crossbar that you can drag across your document to create a Text Box.
- type a few lines in your box. Notice that the perimeter of the text box is made up of small striped lines. This means that the computer is allowing you to add and edit text. At this point, if you were to hit the delete key, text would be deleted.
- Move your mouse along the perimeter of the box. Notice that the cursor becomes a hand. You can move the box anywhere you wish by dragging its perimeter.
- Move the mouse over one of the white handles on the perimeter of the box. Notice it becomes a rectangle with two small arrows. Drag the handle to size your box.
- After clicking the perimeter, the stripes turn to dots indicating that the computer is now treating the box as a graphic, allowing it to be moved and sized. If you were to hit the delete key, the box would be deleted. If you wish to continue editing text, click inside the box.
Using the Draw Toolbar with a Text Box
- To add a line color to your text box, click the down arrow to the right of the pencil and choose a color (or no line at all).
- To add a fill color, click the down arrow to the right of the paint can and select a color, or no fill at all. More styles can be accessed by clicking the Fill Effects button.
The techniques above can also be accessed by selecting your text box and going to Format/Text Box. Click the Colors and Lines tab to see these and more options such as line style, size and transparency.
- To add a shadow, select your text box, click the Draw button (A and cube), go to Shadow and pick a style.
- To add a 3-D effect, select the text box, click the Draw button, go to 3-D and select your style.
- Go to Insert/Picture/Clip Art, Clip Gallery should open.
- Type in a keyword in the Search Field and click Search.
- Choose a category and then a graphic.
- Click Insert.
For more information on graphics, see the layout handouts. (You do not need to "wrap" graphics in Excel).
For more on graphics, see the Working with Graphics document.
Printing
Excel automatically paginates documents longer than one page. To see what a print out might look like, do the following.
- go to View/Page Break Preview
- adjust the breaks by dragging the blue lines
- go to View/Normal to return to the normal screen
OR
:
- make sure that graphs are not selected
- go to File/Print Preview
- click the right or left arrows to navigate between pages
- click Setup to change the orientation of your document or view other printing options
- the Page Setup dialogue box can also be accessed by going to File/Page Setup
- in the Page Setup dialog box, you can adjust menus, orientation, header/footers etc.
- click OK when you are done
- in the Print Preview window, either Print or click Close and adjust your document as necessary
You can select or deselect the print area by going to File/Print Area/Set Print Area or Clear Print Area.
Access Excel Help if you ever get stuck by going to Help/Search Excel Help. Type in a question and press return. Often the answers are detailed and to the point. If you don't get what you need the first time, try altering the keywords in your question.
|
