Using Excel 2004 on Macs

Getting to Know the Excel 2004 Screen
Viewing the Spreadsheet

Show helpful Toolbars
Enlarge or "zoom"
Keep parts visible when you scroll

Data Entry Hints

Select Cells
AutoFill
Cut-Copy-Paste or Scrapbook

Formatting Cells

Vertical & Horizontal Alignment
Wrap Text (or Shrink to Fit)
Select two different sections
Copy & Paste formats of cells with Format Painter

Formula Hints

Use operators in calculations
Basic mathematic and statistical functions
Use AutoSum or AutoList

Printing Options

Print sideways on paper
Center vertically and/or horizontally on page
Create headers & footers

Dates, Page Numbers, Pictures, etc.

Print lines "gridlines"
Repeat columns at top or rows at left of each page

Creating Graphs

Customize appearance of titles, labels, etc.
Change patterns/colors of chart

Getting Started with Excel

Click here for a Screen Layout in a .pdf version to see the parts of the Excel 2004 for Macs screen identified.

Display helpful toolbars

It may help if the Standard and Formatting toolbars and Formula Bar are displayed at the top of the screen.
Excel04 Toolbars

If the desired toolbars are not visible, go to VIEW>TOOLBARS and make selections

Make screen larger to read

Go to VIEW>ZOOM and adjust magnification or click on the Zoom box on the Standard Toolbar
Note: This does not affect the print size of any reports, only the size displayed on the screen.

Keep parts visible when you scroll

For larger spreadsheets, it is helpful to make certain areas stay visible when you scroll down and/or right.
Click below the column headings and/or to the right of row headings you want to remain visible and go to WINDOW>FREEZE PANES. As you scroll, the headings will be "frozen".
To remove a "frozen" area, go to WINDOW>UNFREEZE PANES

Hints for Entering Data in Excel

Undo & Redo Features

The Undo

button on the Standard Toolbar allows you to "undo" the last step(s) you performed. Click on the Undo button (or go to EDIT>Undo last command or Apple-command+Z) to "undo" the very last thing you did; click on the down arrow next to the button to "undo" other previous steps.

The Redo Redo button

button on the Standard Toolbar allows you to repeat the last step(s) you performed. Click on the Redo button (or go to EDIT>Repeat last command or Apple-command+Y) to "redo" the very last step you performed.

Selecting cells

Select adjacent cells	Click in upper left cell; hold Shift key and click in last desired cell and all cells between and including those cells will be selected
Select non-adjacent cells	Click in first desired cell or highlight first desired range; hold Apple-command key down and select other desired cells. (On Windows machines, hold the Ctrl key to select non-adjacent ranges)

Fill commands

"Fill" is similar to copying and pasting data; however it copies formulas relative to their new cell locations and/or copies series of text in a consecutive manner and makes data entry faster.

Fill can be accomplished by using the Fill options under the Edit menu or dragging the Fill Handle in the cell. The Fill Handle is located in the bottom right corner of the active cell. When you roll over the bottom right corner of a cell, the cursor changes shape to a thin black plus sign as shown. When the cursor is this thin black plus sign, you can drag to adjacent cells to fill a series.

To fill a series like months or days of the week, enter Jan or January in a cell and then drag the fill handle (bottom right corner of cell) to an adjacent column or row as shown
Select row to fill

Then the adjacent cells will be filled with the incremented series as shown.
Month names filled right

Other examples are shown. (Note: Series with 1st, 2nd, 3rd, etc will work with AutoFill unless they are spelled out.)
Six Weeks Filled Right

If a series of numbers or text needs to be incremented by one or another step, enter two numbers or items to begin the series and select both cells before dragging the fill handle to cells in the adjacent column or row.
Numeric List Filled Down

Cut, Copy, & Paste

Use one of the following methods for Copying, Cutting, and Pasting data:

EDIT menu choices
Buttons on Standard Toolbar (to Cut; to Copy; to Paste)
Ctrl+click (to access pop-up menu available or use right-click button on a two-button mouse)
Keyboard shortcuts: (Apple-command+X to Cut, Apple-command+C to Copy, Apple-command+V to Paste)
Use mouse to drag and drop to Cut and Paste (Note: This does not work for Copying contents of cells)

Procedures to follow for duplicating ("copying") or moving ("cutting") data:

Click in cell or select range of cells to COPY or CUT
Blinking-dashed outline will move around cell(s)
Move to new location and PASTE

Scrapbook feature

The scrapbook allows you to store multiple files or multiple selections of copied or cut items that can be used in all MS Office applications. To view the scrapbook, go to TOOLS>SCRAPBOOK and you will see options for adding new items and organizing items in the scrapbook.

Formatting Cells in Excel Workbooks

Most options for formatting text and numerical data are found under the FORMAT menu, the Formatting Toolbar, Ctrl+click>FORMAT CELLS, or the Formatting Palette. If the Formatting Toolbar or Formatting Palette are not visible, go to the View menu to select them.

Alignment Options

To wrap text within a cell	FORMAT>CELLS Click Alignment Tab Check Wrap Text
To format text at an angle	FORMAT>CELLS Click Alignment Tab Select Orientation as needed
To combine cells	FORMAT>CELLS Click Alignment Check Merge Cells Example also shows additional options: Vertical alignment set to Center Blue "Pattern" or Fill Color added
To resize text to fit width of cell	FORMAT>CELLS Click Alignment Check Shrink to Fit Example shows top cell with normal text control bottom cell with Shrink to Fit option applied so the font size is resized to fit
To indent text within cells	FORMAT>CELLS Click Alignment Set Horizontal Alignment to Left (Indent) Set Indent = 1 (or other as needed)

Selecting Non-adjacent Cells

To format two groups of cells that are not adjacent, select one group then hold the Apple-command key* and select the next group(s) of cell(s). Go to Format menu and select the desired format for the numbers, text, etc. (*Note: Use the Ctrl key to select non-adjacent ranges in Excel on Windows computers.) The procedure for selecting non-adjacent cells is also helpful in creating charts in Excel.

Use Format Painter to Copy Formats of Cells

If you want to copy the "format" of a cell rather than the contents of the cell, the Format Painter button on the Standard Toolbar is helpful.

Format a cell as needed (alignments, patterns/fill colors, size, etc.)
Click on the Format Painter button to copy the "format" of the cell (Note: If more than one cell needs the new format, double-click on the Format Painter button so that it will remain as a loaded cursor until you turn off the Format Painter button when finished Step 3.)
Click in another cell to "paint" or paste the new format on top of the data in the cell.

Basic Formulas & Functions for Calculations

All formulas in Excel must begin with an = equal mark. Formulas can be used with numbers (like =6578*432) or with cell references (like =B8*C8). Formulas can be entered using the keyboard or using the mouse to point-and-click on the cells to include.

The operators used for basic calculations in Excel are:

+	Addition
-	Subtraction
*	Multiplication
/	Division
( )	Parentheses for order of operations
%	To convert numbers to percentages

Functions are "built-in" formulas that use reserved words and ranges or pieces of information in their arguments. Functions provide efficient ways of handling larger amounts of data quickly. Functions require an = equal mark at the beginning, like other formulas do. The range of cells in the argument of a function can be entered using the keyboard or highlighted using the mouse instead.

Some commonly-used functions in Excel are:

SUM	=SUM(B3:B35)	to add a range of numbers
AVERAGE	=AVERAGE(B3:B35)	to calculate an average of a range of numbers
COUNT	=COUNT(B3:B35)	to determine the number of cells that contain numbers within a range
MAX	=MAX(B3:B35)	to determine the highest number in a range of cells
MIN	=MIN(B3:B35)	to determine the lowest number in a range of cells
IF	=IF(C6>1000,"Sell","Keep")	to compare a number with a condition, and then perform something if it is true or perform something else if it is false. The format of the syntax is =IF(condition,T,F)
SUMIF	=SUMIF(B3:J3,"<78",B3:L3)	to compare a range of numbers with a condition and then add a range of numbers when the condition is met
COUNTIF	=COUNTIF(B3:B35,">93")	to determine the number of cells that contain numbers within a range when criteria is met
VLOOKUP	=VLOOKUP(D6,$B$45:$C$60,2)	to lookup a value in a columnar table (also on an Excel spreadsheet) and assign it a value based on the column you specify. The format of the syntax is =VLOOKUP(lookupvalue,$Table$Range,2) The Table Range should contain absolute references, as indicated by the $ dollar signs.

Shortcuts for Functions & Calculations:

	The AutoSum button on the Standard Toolbar provides a quick way of calculating totals for ranges of cells above or to the left of the active cell.
	The AutoList (the down arrow next to the AutoSum button on the Standard Toolbar) provides additional shortcuts for other functions.
	The AutoCalculate area in the bottom right corner of the Excel spreadsheet screen enables you to see a quick sum, average, count, etc. of a selected range of cells without entering any formulas or functions.

Printing Options

Get in the habit of previewing your reports before you send them to the printer. FILE>PRINT PREVIEW or the Print Preview Print Preview button

button on the Standard Toolbar is very helpful to use to determine if your spreadsheet data fits on the page correctly, needs different margins or page orientation, needs to have headers/footers, needs to have column or row headings repeat on additional pages, needs gridlines, etc.

Many important options are under FILE>PAGE SETUP

To print "sideways" Change Orientation to LANDSCAPE To force all your data to fit on ONE page Change scaling to Fit To 1 page wide by 1 page tall
To center spreadsheet on the page vertically or horizontally, check appropriate selection under "Center on Page" on the Margins Tab of Page Setup dialog box.
To add custom headers and/or footers, go to either FILE>PAGE SETUP and click on Header/Footer tab or VIEW>HEADER AND FOOTER To add text and/or codes to print at the top of each page Click on Custom Header To add text and/or codes to print at the bottom of each page Click on Custom Footer Note: Headers and Footers are not displayed on the spreadsheet screen unless you are in Page Layout View (under VIEW menu). Otherwise, they are displayed as they will print under FILE>PRINT PREVIEW. As shown below there are many options for information to include in headers and footers. The options available on the buttons on the Header/Footer toolbar can be used alone or combined to provide the information you need. For example, to print "Page 1 of 6", "Page 2 of 6", "Page 3 of 6", etc on the footer of a report in Excel: Choose Customize Footer Click in the section you want it Type "Page" Click on the Insert Current Page Number button Type " of " (with spaces) Click on Insert Total Page Numbers button. The ampersand "&" symbol is a special character that is used in many of the codes used in headers and footers. If you want to actually include the & symbol in a header or footer, type it twice.

To print lines Check gridlines SHEET tab of Page Setup options To have certain column headings print at the top of each page Next to "Rows to repeat at top" click on collapse symbol Highlight the correct row(s) on the Excel worksheet that contains the label(s) Click on expand button to return to Page Setup dialog box (The same procedure can be applied for repeating columns at the left)

Create Charts

Graphs ("charts") are used to illustrate relationships between values in a Excel worksheet. A chart can either become an object (like a picture) in an existing worksheet or be placed on a sheet all by itself. Charts are automatically saved as part of the Excel worksheet file; there is no need to attempt to save it separately. Charts created in Excel can later be added to a Word document or PowerPoint slide.

The range of cells in the graph should include numbers ("values") and the labels that identify those values. Remember, Excel allows you to select non-adjacent ranges if you hold Apple-command when selecting the separate ranges.

Select appropriate range(s)
Go to INSERT>CHART or click on the Chart Wizardbutton to launch a "wizard" that will prompt you with choices to make regarding how to the format the chart
Choose a Chart Type and Chart Sub Type
Find the button and hold mouse button on it to see how your will display with the selected chart type
Click NEXT on wizard and move through prompts
On Step 3 of 4 of Chart Options

Enter Titles and Axis Labels as needed for your chart (X-axis is horizontal; Y or Z is vertical axis)
Select the placement for the Legend, etc.
Turn on Data Labels if needed

Customize a Chart

Any part of the chart can easily be customized after it is created. Excel must be in the chart mode to format the chart; therefore, click the chart to select it and then

Go to CHART>CHART OPTIONS or Hover mouse over any section to change and Ctrl+click (or right-click with two-button mouse) to access the pop-up menus to Format Axis, Format Data Series, Format Chart Area, Format Plot Area, etc. to adjust sizes, colors, numbers on scales, etc.
To change to a different style of chart (pie, bar, line, etc.)	CHART>CHART TYPE
To remove gray background color	Hover mouse over gray area Ctrl+click (or right-click) for pop-up menu to appear Choose CLEAR Note: If you accidentally clear the wrong thing, select EDIT>UNDO
To change colors or patterns on bars, lines, or slices of chart	Hover mouse over data series of chart Ctrl+click (or right-click) for pop-up menu to appear Choose FORMAT DATA SERIES Click on COLORS & LINES tab at top Under Fill Color choose FILL EFFECTS Click on Gradient, Texture, Pattern, or Picture tab at top Make selection as desired Note: On Pie charts, make sure only one slice is selected before you change patterns, otherwise all slices of the pie chart will look the same.
To change size of axis labels	Click on labels and change font size on Formatting Toolbar
To add a subtitle to main title	Click once on the chart's title to select the object Click again to have an "I" beam appear and to be in edit mode
To add frame "border" around chart	Hover mouse over white area around outside edge of chart When screen tip appears that says "Chart Area", Ctrl+click and Choose FORMAT CHART AREA from pop-up menu Adjust line color, style, and weight as desired
To fix "Series" displayed in legend	CHART>SOURCE DATA Click on SERIES tab at top Type appropriate title for legend next to "Name"
To change format of numbers on chart	Click once on the axis to select it Ctrl+click (or right-click) for pop-up menu to appear Choose FORMAT AXIS Click on the Number tab at top Make changes as necessary (number of decimal places, etc.) Note: The beginning and ending numbers on the axis "scale" can also be set on the Scale tab on the Format Axis dialog box.
To choose different cells of data displayed on chart	CHART>SOURCE DATA Click on DATA RANGE tab at top Click on collapse button at end of Data Range input box Return to Excel worksheet and use mouse to select range to include in chart OR If blue handles are displayed around the data in the Excel worksheet, Click and drag them to include other cells as necessary
To change the location of a chart (from being an object in a worksheet to be on a separate sheet or vice versa)	CHART>LOCATION and make selection as needed
To print in black-and-white	FILE>PAGE SETUP Check Black and White as shown

Page Created by Doris Gardner, ITRT, Rockingham County Public Schools
June 2006