Docstoc

Excel Overview for Mac Users

Document Sample
Excel Overview for Mac Users Powered By Docstoc
					              Excel Overview for Mac Users

              LC Notebook Tab VII – Computer Skills

              Agenda

                  Excel Basics: Definitions and the Excel Interface

                  Working in Excel: Navigation and Data Entry

                  Formulas and Functions

                  Formatting Worksheets

                  Finding and Sorting Data

                  Printing Worksheets


                  Excel Pointers and Additional Resources



Objectives. By the end of this session, participants will:

   •   Be familiar with the Mac Excel interface and be able to create a workbook from scratch.
   •   Be able to enter various types of data and use shortcuts where applicable.
   •   Be able to format a worksheet – including preparing a worksheet for printing.




Handout Quick Table of Contents:
   Overview                    p. 2    Order of operations         p. 10   Merging cells                      p. 16
   Keyboard shortcuts          p. 4    Entering data               p. 10   Formatting text                    p. 17
   Keyboard navigation         p. 4    Entering formulas           p. 10   Adding/naming worksheets           p. 18
   Touchpad navigation         p. 4    Using functions             p. 11   Rearranging/copying worksheets     p. 18
   Freezing columns/rows       p. 5    AutoFill                    p. 14   Finding data                       p. 20
   Worksheet views             p. 6    Editing/deleting formulas   p. 14   Sorting and Filtering data         p. 22
   Cell contents /data entry   p. 7    Copying formulas/values     p. 15   Printing                           p. 23
   Formulas                    p. 9    Pasting formulas between            Hiding columns/rows                p. 24
                                       worksheets                  p. 15   Excel help                         p. 25




                                                                               Workshop facilitated by Maggie Saponaro
        Excel Overview for Mac Users
        Component VII: Computer Skills


Overrviiew off tthe Excell IIntterrfface
Ove v ew o he Exce n e ace
Excel is a spreadsheet program used to create worksheets that store information in workbook files. The built-in functions
of Excel allow you to create and edit formulas, copy and move data, format worksheets, and prepare worksheets for printing.
This workshop provides an introduction to a few of the basic concepts of Excel, as well as using Excel to create charts and
graphs.

Starting Excel

There is no better way to learn Excel than to start working with it! Steps you should perform appear in highlighted text
throughout this handout packet.

⇒ Launch Excel. To do this, click on the Excel icon on your desktop.

(Note: Depending upon how your machine was set up, you may see the Project Gallery – New displayed. Select the Excel Workbook
icon and click Open to continue.)

Once you launch Excel, you will see a screen that looks similar to this:




Let’s take a moment to examine each of the parts of this screen before we start entering data.

At the top of your screen, next to the Apple icon, is the menu bar, which contains lists of commands you will use with Excel.
Many of the menus on this bar – such as file, edit, format, or help - will seem familiar if you use other Microsoft products.

Excel Overview for Mac Users – UM University Libraries                                                                 2
Beneath the menu bar is the standard toolbar, which contains buttons with images that correspond to some frequently
used menu commands:




To see what a toolbar button does, pause your mouse pointer over the button and a brief description will appear.

⇒ Do this now—review the functions of the buttons on your standard toolbar now. We will use several of them later.

If you want to view additional functions, click on the more buttons icon        at the end of the standard toolbar. You will see a
list of available options from which to select. Click on the option you would like to execute.

NOTE: If a particular option appears “grayed out” on a toolbar, that means it is not available for your current activity.

At the top of the Excel worksheet window is a bar called the title bar. The title bar contains the name of the workbook you
are working in (automatically called “Workbook 1” until you rename it). The title bar also includes the minimize,
maximize/restore, and close buttons. (NOTE: Workbooks generated in Excel for Windows are automatically named
“Book1”.)

The largest portion of the screen is taken up by a view of the worksheet area. Worksheets are used to list and analyze
data. At the bottom left of the screen are the worksheet tabs that indicate the active worksheet, and additional worksheets
in the workbook. By default, new workbooks contain three worksheets. Use the tab scrolling buttons, to the left of the
worksheet tabs, to scroll between worksheets.

⇒ Try looking at all three worksheets now – being sure to click on “Sheet 1” when you are finished.

Along the left and top of the worksheet area are row headings and column headings. Rows are denoted by numbers from
1 through 65,536, of which normally the first 15 to 20 rows are visible. Columns are labeled A through Z, AA through AZ,
BA through BZ, etc. The last column is IV. Normally, only the first 6-8 columns are visible when you open a workbook. Use
the scroll bars at the edge of the screen to view columns or rows that are off screen.

                                                    Cells in an Excel worksheet are formed by the intersection of the column
                                                    and row grid (B3, A1, etc.). The blue rectangle surrounding a cell
                                                    indicates the active cell. In the example to the left, the active cell is A1.

                                                    The active cell’s reference number – a combination of the column letter
                                                    and row number (A1) appears in the name box.


                                                    To the right of the name box is the formula bar, which is where text and
formulas can be entered and edited for each cell.



                                              An amazing Excel fact!
How many cells are in an Excel Workbook? Almost too many to count! Each worksheet has 256 columns (from A
to IV) and 65,536 rows. Multiplied together, that gives you 16, 777, 216 cells per worksheet. Each new workbook
you open has three worksheets, giving you 50,331,648 cells initially. You can also add worksheets to workbooks,
to even further expand the number of cells available. Amazing!

Excel Overview for Mac Users – UM University Libraries                                                                      3
At the bottom of the screen you will see the status bar, which displays information to keep you informed as to the current
state of Excel. When you first start Excel, you will see the word “Ready” in the lower left corner of the status bar. It will
change as needed to display terms such as “Enter” as you enter text or formulas, or “Edit” as you edit cells. The far right
side of the status bar will indicate if you have turned on such items as Caps Lock, Scroll Lock, or Num Lock. The example
below shows Caps and Num lock turned on:




⇒ Try pressing the Caps, Scroll, and Num Lock keys now to see how it changes the appearance of the Status bar (look
to the right side of the status bar). Be sure to turn these functions OFF when you are finished.

                                       Helpful Excel Keyboard Shortcuts
As in most Microsoft Office products, are a number of keyboard shortcuts exist in Excel that can save you time:

    +A                      Selects the entire worksheet
    +C                      Copies text from the active cell
    +F                      Displays the “Find” dialog box
    +P                      Displays the “Print” dialog box
    +V                      Pastes text into the active cell
    +Y                      Repeats the last action
    +Z                      Used to undo the last action

Naviigattiing IIn Excell
Nav ga ng n Exce
Once you understand the basics of an Excel workbook, you need to know how to navigate within it. You may either navigate
in Excel using the keyboard or the touchpad (or an external mouse).

Keyboard navigation
Here are a few common keystrokes or shortcuts that may be used to navigate within Excel:

             Keystroke                Direction
               or Tab                 Moves the active cell to the right, column by column
                 or Shift +Tab        Moves the active cell to the left
             ↑                        Moves the active cell up one row
             ↓ or Enter               Move the active cell down one row
             Home                     Cell in Column A of the current row
             Page Up                  Cell moves one full screen up in the same column
             Page Down                Cell moves one full screen down in the same column
             Ctrl + Home              First cell (A1) of the worksheet
             Ctrl + End               Moves from the active cell to the last cell that contains data
             Ctrl + Page Up           Moves from one worksheet to another. (You may also click the
             Ctrl + Page Down         worksheet tabs at the bottom of the worksheet to do this.)

Touchpad navigation
In addition to using keystrokes or shortcuts to navigate within Excel, you may use the touchpad (or a mouse) to perform
many functions. As you use your touchpad to move the cursor across the screen, you will notice that it changes shape to

Excel Overview for Mac Users – UM University Libraries                                                                  4
indicate a change in function. Some of the most common shapes you will see are:

             Mouse Pointer            What it means
             Shape
                   (Cross)            Appears when you move the pointer around the cells of the current
                                      worksheet. Use this pointer to select the cells you need to work with.
                                      Appears when you position the pointer on a toolbar, the menu bar,
                  (Arrow)
                                      or on one of the edges of a block of cells that have been selected.
                                      Appears when you position the pointer between two columns, so
                                      that you can resize them.
                                      Appears when you position the pointer between two rows, so you
                                      can resize them.
                                      Appears when you position the mouse in the lower-right corner of a
                                      cell selected with the cell pointer. Use this to create a series of
                                      sequential entries, or to copy an entry or formula in a block of cells.

Here are several common ways to make a cell active by using the touchpad:

                     Item to Select           How to select using the touchpad:
                     Cell                     Click on the cell
                     A range of cells         Click on the Cross (see above) to click and drag across
                                              a group of cells
                     Entire row               Click on the row number
                     Entire column            Click on the column letter
                     Multiple rows            Click and drag down the row numbers
                     Multiple columns         Click and drag across the column letters
                     Entire worksheet         Press      +A
                     Non-contiguous           Select the first cell or range of cells, and then hold
                     (non-consecutive)        down and select the other cells or ranges.
                     cells

⇒ Try this now! Test the various function keys. Also test the various ways to navigate within a spreadsheet – using both
the shortcuts and the mouse. Select and unselect the entire worksheet.


Freezing column and row labels
As your worksheets grow, you will likely insert rows to add column headers to assist you in managing your data. You may
find it useful to “freeze” column and/or row labels so that you can view them with related data.

IMPORTANT NOTE: The Freeze Panes option is not available when the worksheet is in page layout view. If necessary – go
to the View menu and select Normal.

To freeze rows:
     1. With your cell pointer in Column A - click on the cell below any column labels you want to freeze.
     2. Open the Window menu and select Freeze Panes.

To freeze columns:
     1. Click on the cell to the right of the row labels you want to freeze.
     2. Open the Window menu and select Freeze Panes.

Excel Overview for Mac Users – UM University Libraries                                                             5
To unfreeze rows or columns – go to Window and select Unfreeze Panes.

⇒ Try this now! Freeze the first two rows of your worksheet, and then use the arrow key or Page Down key to navigate
down in the worksheet. Notice on the worksheet below, rows one through three are frozen:




⇒ “Unfreeze” the rows, and repeat this procedure, freezing the first two columns of your worksheet. Use the arrow
keys to navigate to Column “S”. The first two columns can still be seen on the left. “Unfreeze” the columns when you are
finished. Notice on the worksheet below, columns A and B are frozen.




Helpful Hint: To freeze both column and row titles -- select the cell that is both directly below the column titles and to the
right of the row titles.

Changing the Worksheet View

There may be times (such as when you want to freeze rows and columns) that you may wish to change the way your Excel
worksheet appears. To change the appearance of your worksheet, go to the View menu and do the following:
To change the view to this:                                 Choose this option:
The worksheet as one uninterrupted surface, continuous      Normal
both horizontally and vertically.

The worksheet with toolbars hidden so that the worksheet         Full Screen
can fill the entire screen.

The worksheet divided into its printable pages, each page        Page Layout
displaying all of its elements as they are set up for printing
— headers, footers, charts, graphics, etc. — and with most
elements available for direct editing.



Excel Overview for Mac Users – UM University Libraries                                                                  6
To change the view to this:                                       Choose this option:
The worksheet with page breaks marked for ease in                 Page Break Preview
changing their positions.



Celll Conttentts and Datta Enttrry
Ce Con en s and Da a En y
In Excel, you can enter four types of values into cells: text, numbers, dates and times, and formulas. Each cell can hold up to
32,762 characters.

Text
In Excel, text is considered to be any combination of letters, numbers, spaces or nonnumeric characters. All cells that are
considered text are left aligned (although you can change text alignment later). All of the following would be considered text
entries:
 Maggie Z. Saponaro         msaponar@umd.edu            X4-1499           7103MCK           College Park, MD

Helpful Hint: When entering E-mail addresses – Excel automatically adds the hyperlink (appearing as underline and blue
font). To remove the hyperlink, select the cell containing the hyperlink and go to Insert    Hyperlink. From the menu that
appears, select Remove Link and click OK.


If the text entered is wider than the cell can
display, the data spills over into the
neighboring cell or cells on the right as long as
those cells remain blank. In the example to the
right, notice how several titles in Column A spill
over to Column B:




If you later add information to a cell that
contains spillover text from a cell to its left (for example, entering items in Column B when text from Column A spills over into
it), Excel cuts off the spillover from the longer text entry – to make room for the new information. See what happens to the
same spreadsheet – and note how the information in Column A has been truncated:




                                                                          If this happens -- don’t panic!!! This information
                                                                          has not disappeared! To redisplay the “missing”
                                                                          portion (Column A), you can either resize the text in
                                                                          the original column, or widen the column itself.




Excel Overview for Mac Users – UM University Libraries                                                                   7
Numbers
Numbers include numeric characters 0-9 and the following special characters:

                                                         ,+ -( ) /Ee$%

Numerical values are right aligned. If a number is wider than the cell, ######## is displayed in the cell, and the complete
number is displayed in the formula bar. In the example below, the contents of cell A2 cannot be displayed:


                                                                                   To display the cell contents of cell A2, the
                                                                                   column must be resized, or you may
                                                                                   reformat the cell to display fewer decimal
                                                                                   points (as seen in cell A1).




                                      By default, negative numbers are preceded by a minus sign. They can be formatted to
                                      be displayed in red or enclosed in parentheses.

                                      Entering a dollar sign ($) before a number or a percent (%) after the number will change
                                      the way it is displayed, as you can see in the illustration to the left.




Helpful Hint: If you ever need to enter a number or a formula as text – type an apostrophe (‘) before the entry. For
example: enter ‘20742 for the zip code of College Park. When you do this, a green triangle will appear in the upper-left
corner of the cell.           . Click on the cell to display a dialog box indicating what special formatting had been applied
to the cell.

When entering numeric values with decimal places, use the period as the decimal point. When you enter decimal values,
Excel automatically adds a zero before the decimal point (0.34 is displayed, instead of .34) and drops trailing zeroes after
the decimal point (12.5 is displayed, instead of 12.50).

If you want to enter fractions instead of decimals – you can do so, as long as you enter a space between the number and
the fraction (2 3/4 – with a space between the 2 and 3). For simple fractions such as 2/3 or 5/8, precede the fraction by a
zero – as in 0 2/3 or 0 5/8 (otherwise, Excel thinks you are entering dates – February 3rd or May 8th).

⇒ Try This Now! In your worksheet, enter the following and note your results:
                                  In Cell            Enter the following:
                                  B2                 0.34
                                  B3                 12.50
                                  B4                 $10.00
                                  B5                 -10
                                  B6                 0 2/3
                                  B7                 2/3
                                  B8                 2 3/4
Do any of these values display as something other than what you expected? Which one(s)? Can you determine why?


Excel Overview for Mac Users – UM University Libraries                                                                   8
Dates and times
Excel treats dates and times as numbers, and they are right aligned in a cell. Although this may seem strange, this allows
Excel to use dates and times as values that can be used in formula calculations. If dates and times were treated as text, this
could not be done. (For example, by treating dates as values, you can set up a formula to subtract a more recent date from
an older date).

                     Date/Time Entry           Format                            Displayed As:
                     10/5/04                   m/d/y                             10/5/2004
                     5-October-04              dd-mmmmm-yy                       5-Oct-04
                     October 2004              mmmmm-yyyy                        Oct-04
                     5-October                 dd-mmmmm                          5-Oct
                     9:00 AM                   h:mm AM/PM                        9:00 (for an AM entry)
                     9:00 PM                                                     9:00 PM (for PM entry)
                     9:00:25 AM                h:mm:ss AM/PM                     9:00:25 (for an AM entry)
                     9:00:25 PM                                                  9:00:25 PM (for PM entry)
                     4/8/04 9:00 AM            m/dd/yy h:mm                      10/5/2004 9:00 (for AM)
                     4/8/04 9:00 PM                                              10/5/2004 21:00 (for PM)

Helpful Hint: If you do not enter a date/time into Excel using one of its accepted formats, the date/time will be treated as
text and will appear left aligned in a cell. If you want the date/time to be treated as a number, use one of the formats above.

Formulas
The true utility of Excel is its ability to use formulas. A formula is used to calculate the value of the contents of a specific cell
or range of cells. All formulas must start with the equal sign (=). The formula for a particular cell or range of cells will
appear in the formula bar, while the results are displayed in the cell itself. Formulas can refer to other cells.

The following are several common operators used in formulas:

             Operator                  Function                                  Example
                       *               Multiplication                            2*4
                       /               Division                                  2/4
                       +               Addition                                  2+4
                       -               Subtraction                               2–4
                       ^               Exponentiation                            3^3 (the same as 3*3)
                       :               Colon – used to specify a range           D3:D7
                                       of cells (see illustration below).
                        ,              Comma – used to combine                   SUM (D3:D7, F15, G9)
                                       multiple cell references into one
                                       reference
                                       Combination – you can combine             (A1+A2+A3)/3 [The average of
                                       operators as needed.                      the contents of cells A1-A3]


                                                               Selecting a range
                                                               In the illustration to the left, the selected range would be referred
                                                               to as B3:D5.


Excel Overview for Mac Users – UM University Libraries                                                                       9
Order of operations in formulas
Just like in mathematics, Excel formulas follow a certain “order of operations”. When creating formulas, keep in mind the
following:
     • Operations are performed from left to right.
     • Parentheses may be used (as in the “combination” example on the previous page) to group operations to be
          performed first.

                          Order         Operator              Description
                          1              ( )                  Parentheses
                          2             ^                     Exponents
                          3             */                    Multiplication/Division
                          4             + -                   Addition/Subtraction

                   Try this now! What results do you get from the following equations?
                   =4+5*7-3         (a) 60               (b) 36              (c) 24
                   =(4-1)^3*5+4     What answer do you get?




Entering Data

To enter data (text, dates, formulas, etc.) into a cell, select the cell by clicking on it, and begin typing. The text will appear in
the formula bar:




Press the Enter key to store data in a cell and make the cell in the next row the active cell.

(NOTE: Pressing the Tab key behaves the same way – except that it makes the cell in the next column active.)

                     REMINDER: In Excel, you can only enter or edit information in the active cell!


Entering Formulas

You can either type formulas directly into the cell or formula bar, or you can use Excel’s built-in Function Wizard.

To enter a formula directly into a cell:
    1. Type an equal sign.
    2. Click on a cell and Excel will enter the cell reference into the
        formula. (NOTE: You can even refer to cells in other
        worksheets or workbooks this way.)
    3. Combine cell references using the operators listed on
        page 9. (For example A1+A2+A3)
    4. When you are finished entering the formula, hit Enter or click
        on the green check mark       next to the calculator symbol
        on the formula bar (see the example on the right). (NOTE:
        One helpful feature of Excel is that cells included within
        formulas are color-coded.)


Excel Overview for Mac Users – UM University Libraries                                                                        10
                                                                          Notice in the example to the left that once you hit
                                                                          Enter (or click on the green check mark in the
                                                                          formula bar), the total of your equation (in this case,
                                                                          the number 30) appears in Cell A4, while the
                                                                          formula for that cell appears in the formula bar.




Once you enter a formula that refers to the values of certain cells, you can now change the values in those cells and Excel
will automatically recalculate the values for you!

Entering formulas with the Function Wizard

Instead of entering formulas by hand, you can also use the Function Wizard. To do this:

1. Click in the cell where you want the operation to occur (as seen in the following example) and enter an equal sign (=) in
the formula bar.




Once you have entered the equal sign, you will notice the name box changes from displaying the cell reference (A4) to the
word SUM, with a down arrow next to it.

                           2. Click on the down arrow next to the word SUM to list functions available. If you do not see the
                           one you want, you can choose “More Functions…” from the list.



                                3. Select the title of the function/formula you would like to use (in this case, SUM) and verify
                                   or edit the results in the dialog box that appears. Note the formula has been entered in
                                   the formula bar for you. (See the top of page 12 for an illustration of the dialog box).


                           4. Click OK when you are satisfied with the
                           formula.




⇒ Try This Now! Enter the number 10 in cells A1, A2 and A3. In cell
A4, add them together by entering the following formula =A1+A2+A3.
In cell A5, use the function wizard and follow the steps above to Sum
the total of cells A1 through A3 (not including A4).

Excel Overview for Mac Users – UM University Libraries                                                                    11
Using Excel Functions
Excel functions are predefined formulas. The SUM function used previously could be used to return the same value as the
formula =A1+A2+A3. When entering this function, you could choose to enter it as:

                                  =SUM(A1, A2, A3)                    or       =SUM(A1:A3)

and expect to get the same results.

Each function requires a function name and at least one argument:

                                                    =function name(argument)

Arguments, which appear in parentheses, can consist of numbers, cell references, ranges or text. Use commas when there
is more than one argument in a function.

Functions must begin with the equal sign (=) and must be followed by parentheses – even if no reference is required
between the parentheses. Some common functions are:

      Function           Result Displayed:                                 Example
     AVERAGE             The mean of the cells referred to in              =AVERAGE(A1:A3)
                         parentheses
      COUNT              The number of numerical values in the cells       =COUNT(A1:A3)
                         referred to in parentheses
      COUNTA             The number of non-empty cells among those         =COUNTA(A1:A3)
                         referred to in parentheses
        MAX              The highest value among the cells referred to     =MAX(A1:A3)
                         in parentheses
         MIN             The lowest value among the cells listed in        =MIN(A1:A3)
                         parentheses
       TODAY             Today’s date                                      =TODAY( )

Since it is so popular, the SUM function has a short-cut button on the Standard toolbar called AutoSum. It appears as a

Sigma character on the toolbar:      . You can use this to calculate numbers quickly across rows or down columns. For
instance, the function =SUM(A1:A3) can be typed manually in cell A4, or you can use the AutoSum tool. Opening the drop-
down menu next to the icon allows you to quickly apply Average, Count, Min or Max functions, and allows you to access the
Insert Function dialog box.


Try this now – AutoSum:

1. In your workbook, enter numbers of your choice in cells C1 through C5.
2. Click on insertion point in cell C6.

2. From the standard toolbar, click on the AutoSum button          .
3. Click on the green check mark in the Formula toolbar to accept the calculation (or, simply press the Enter key to accept
and apply the formula).




Excel Overview for Mac Users – UM University Libraries                                                                12
Try this now – other predefined functions:

1. Select cell C7.
2. Type an equal (=) sign in the formula bar and click on the drop-down arrow that appears next to the word SUM in the
name box.
3. Click on the word AVERAGE from the drop-down list that appears.
4. In the Function Arguments dialog box, note the cell/s identified in the Number1 field. Edit the displayed value so that it
reads C1:C5. Click OK.
5. Note the results in the formula bar and in cell C7.


Using the Insert Function for Multiple Calculations

You can also use the Insert Function command from your menu options as another way of incorporating functions into your
workbooks. To activate this dialog box, go to the INSERT menu and select FUNCTION… The following dialog box will
appear:
                                                                            The default listing is “Most Recently Used” –
                                                                            but you may choose from a wide array of
                                                                            functions by scrolling through the various
                                                                            lists – such as “Financial”, “Date & Time”, or
                                                                            “Math & Trig”.

                                                                                  If you do not understand how a particular
                                                                                  function works, highlight the function in
                                                                                  question (such as “Average”), and click on
                                                                                  “Click for Help on the selected function.”

                                                                                  Once you have found the function
                                                                                  appropriate for you, highlight the function
                                                                                  name and click OK.

                                                                                  You can use this feature to insert simple
                                                                                  functions (as we did in the previous
                                                                                  examples), or to identify non-contiguous
                                                                                  cells. (For instance, if you wanted to average
                                                                                  the total of several different cells within your
                                                                                  workbook.)


Try this now:

1. Select cell C9.
2. Select the Function command from the INSERT menu.
3. Select the SUM option from the list of selectable functions. Click on the OK button.
4. In the Function Arguments window, enter A1 in the Number 1 argument box, B2 in the Number 2 box, and C6 in the
Number 3 box. Notice how each time you enter a cell reference, another argument (number) box appears in the function
wizard.
5. Click on the OK button.
6. Select cell B9.

7. Click on the Paste Function symbol      on the standard toolbar.
8. Select the AVERAGE option from the list of selectable functions. Click on the OK button.
9. In the Function Arguments window, clear the contents of click the Number 1 box and then click on cell A1.

Excel Overview for Mac Users – UM University Libraries                                                                    13
10. Click an insertion point in the Number 2 argument box and click on cell B2.
11. Click an insertion point in the Number 3 argument box and click on cell C4.
12. Click on the OK button.


AutoFill

Excel can use initial values you enter to create a logical list of entries, like numbers following a pattern (2, 4, 6, etc.), words
(Monday, Tuesday, Wednesday, etc.) or alpha-numeric entries (like dates). AutoFill can also be used to apply formulas
down a column. To use AutoFill:

    1. Enter the item you would like to start the AutoFill list with in a cell and select the cell (or select a cell with the pre-
       existing information). (Note: If you want to enter a non-consecutive pattern -- such as 2, 4, 6… -- you must enter
       the first two numbers of the series and select both cells in order for AutoFill to work.)

    2. Click on the lower right-hand corner of the selection rectangle. The pointer will become a black cross.




    3. Holding down the mouse button (or the button below your touchpad), drag across or down – for as long as you
       want your list. Excel will fill in the data it assumes you want.




    The small box at the end of the range you filled is the AutoFill Options box       . Choosing the down arrow on this
    box will allow you to change the options – such as completing the AutoFill by month, weekday, or year.

Note: If you have gone too far with your list – keep holding the mouse button down, and go backwards (either up or across)
to the point you want to end your data. You will see the cells empty as you remove the AutoFill.


Helpful Hint: If you want only weekdays to appear in an AutoFill list, click Auto Fill Options             and then select Fill
Weekdays only.

Editing and Deleting Formulas or Functions

Once you have entered formulas into your worksheets, you may need to change them from time to time. No matter how you
entered the formula (by hand, using AutoSum, or by using the Function Wizard), you can easily make changes by doing the
following:

    1. Click on the cell that contains the formula or function.
    2. Change the appropriate argument and click the green checkmark                on the formula bar.

To delete a formula – click on the cell that contains the formula and press the Delete key. If you accidentally delete a
formula – go to Edit Undo (or press            + Z) to “undelete”. You may use Undo multiple times (up until the last point in
which you saved your worksheet).



Excel Overview for Mac Users – UM University Libraries                                                                       14
Copying formulas
When you copy a formula, the formula is automatically adjusted to fit the location of the cell to which it is copying. This is
because of the relative addressing system Excel uses. To copy a formula:

    1. Click on the cell that has the formula you want to copy. (This formula will appear in the formula bar).
    2. Go to the Edit menu and choose Copy.
    3. Click on the new cell where you want the formula to appear.
    4. Go to the Edit menu and choose Paste.

                   Helpful Hint: You may also use the shortcut keys –          -C to copy and        -V to paste.

Copying values
Sometimes, you may only wish to copy the result of a formula – instead of the formula
itself. To do this:

    1.   Click on the cell whose value you want to copy.
    2.   Go to the Edit menu and choose Copy.
    3.   Click on the new cell where you want the value to appear.
    4.   Go to the Edit menu and choose Paste Special.
    5.   Choose Values from the dialog box that appears. Click OK.




Pasting Formulas Between Worksheets

You may want to paste the results of a formula used in one worksheet into another
worksheet so that you can create a summary sheet, or otherwise refer to information on more than one worksheet. To do
this, you will first copy the cell you want as done above, and then use another feature of the Paste Special menu, called
Paste Link.

Try this now!

1. Select cell B9
2. Select the Copy command from the Edit menu
3. Click on the Sheet3 tab, and select cell A1.


4. Choose “Paste Special” from shortcut menu and then click on the “Paste Link”                            box from the
Paste Special dialog box that appears. Note the cell formula in the formula bar and the result in cell A1.
5. Return to Sheet1 and change the function argument from AVERAGE to SUM. Note the change in cell B9.
6. Click on SheetC and note the change in cell A1.

Adding or deleting cells, columns or rows
There will be times that you will need to add or delete individual cells in your worksheets.

To insert cells:

    1. Select the cell or range of cells where you want the new cell(s) to
       appear.
    2. Go to the Insert menu and choose Cells. The Insert dialog box will appear.

Excel Overview for Mac Users – UM University Libraries                                                                     15
    3. Choose the appropriate option from the dialog box. Note that you
       can insert an entire row or column, or simply add individual cells.
    4. Click OK. Check to be sure you have not changed any formulas as a result of the insertion.

To delete cells:

    1. Select the cell or range of cells you want to delete.
    2. Go to the Edit menu and choose Delete.
    3. Choose the appropriate option from the dialog box. Note that you can delete an entire row or
       column, as well as individual cells.
    4. Click OK.

NOTE: To change any formatting you may add to cells – instead of using the delete function, go to the Edit menu
and choose CLEAR ►Formats. This will preserve the contents of the cell while removing any formatting.


WARNING: You will not receive a warning when you go to delete an entire column or row! Be absolutely certain
you want to remove these contents permanently! If you made a mistake and deleted the wrong column – go to
EDIT ►Undo or use       + Z to reverse the deletion.

Merging cells
In Excel, you can merge the data in one cell with adjacent cells (that are blank) to form one large cell. This can be useful for
creating titles or other headings in your worksheet. To do this:

    1. Type your information in the left-most cell of a row. Hit Enter.
    2. Select the cells you want to merge by holding down the mouse and dragging
       across the appropriate columns or rows. (NOTE: make sure the pointer is
       the “puffy cross”         otherwise you may accidentally AutoFill the contents
       of the first cell into other cells).
    3. Go to Format and choose Cells. The format cells dialog box appears.
       (Hint: The shortcut is + 1).
    4. Choose the Alignment tab, and click on the Merge cells check box.
    5. Change the Horizontal Alignment setting to Center.
    6. Click OK. (NOTE: See page 17 for merging cells using the Format Palette.)


Changing column widths
As mentioned earlier, if a number entered is wider than the cell, ######## is displayed in the cell. In order to fix this, you
must change the column width. There are a number of ways to change the column width in Excel. One of the simplest is to
use AutoFit. To do this:

    1. Position the mouse pointer on the column bar (the gray bar at the top of the page with the letters in it) – between

       the two columns you want to adjust. The mouse pointer will change to a double-headed arrow          .
    2. Double-click the mouse button. Excel will automatically widen or narrow the column to suit the longest entry.

Helpful Hint: An alternative method to AutoFit is to drag the edge of the column – rather than double-clicking it. This will
allow you to set the width of the column yourself. You can use this same method to adjust row heights as well. When
adjusting row heights, your cursor will appear like this    .


Excel Overview for Mac Users – UM University Libraries                                                                   16
Forrmattttiing Textt
Fo ma ng Tex
There are two ways you can format text/cells after you have entered data. The first is to use the
formatting palette. This palette normally appears on your desktop near your Excel workbook window.
The functions of many of the features of the formatting palette work the same as in other Office
applications, such as Word. Note the font, number, and alignment and spacing options are open on the
palette to the right. (NOTE: If this palette is not visible, go to View Formatting Palette.)


You may also format text by selecting the cell, column, row, or range of cells you want to format and going
to Format Cells. You will see the Format Cells dialog box, which contains several tabs. In addition to
the Alignment tab (covered on page 16), two other features you may choose to edit are the cell Font or
Pattern (background color). Examples of these two menus appear below:




⇒ Try this now! Insert a row above row 1 in your workbook. Type the title “Excel Overview for Mac Users” and merge and
center the text. Change the font and pattern of the title you added. Try several options until you find one you like. If you
would really like your title to stand out, use the options found in the Border function in the Format Cells dialog box.

Wrapping Text

You can wrap text for long headings easily by doing the following:

    1. Type the text for your heading and press Enter.
    2. Select the cell again, and choose Wrap Text from the Alignment and Spacing
       drop-down section of the formatting palette.

You can also enter the contents of an entire cell (or go back to a cell you have already
completed), select the text, and do the following:

    1.   Open the Format menu.
    2.   Choose Cells to open the Format Cells dialog box.
    3.   Choose the Alignment tab, and check the Wrap text box.
    4.   Click OK when you are finished.

Excel Overview for Mac Users – UM University Libraries                                                                   17
Ottherr Forrmattttiing Trriicks
O he Fo ma ng T cks
Text Alignment

                                             After wrapping text, you may notice that the text is aligned to the bottom of the
                                             cell. To fix this, Formatting Palette, choose Alignment and Spacing, and center
                                             the text horizontally or vertically (or both). You may also change the orientation of
                                             the text – from horizontal to vertical to angled text.

                                             If the cell you want to format is not currently active, select it first, and perform the
                                             step above.

(NOTE: You may also go to Format          Cells, and select the Alignment tab. Change the horizontal or vertical alignment
as needed.)

Shrink to Fit

For text that has not been wrapped, but you want to fit in a cell regardless of the column width, you may choose the Shrink
text to Fit option from the Alignment and Spacing section of the Formatting Palette.
You cannot wrap text and shrink text at the same time – so you will need to decide which option you prefer.
(You can also turn this option on by going to Format       Cells (or choosing       +1), and checking the Shrink To Fit box in
the Alignment tab.)

         Note: If after using “Shrink to Fit”, you find the text is now too small to read, go to File Undo, or use         + Z!


Formatting Numbers

To change the format for a particular cell or range of cells, or to determine
what formatting may already be assigned to a cell, use the Numbers feature
in the Format Cells dialog box. Go to Format Cells (or     + 1), and
choose the Number tab. Select the category you want (for example,
Number or Currency). You will see a sample of how the number will appear
on your worksheet. Click OK when finished.

You can also use the Number section of the Formatting Palette to quickly
apply formatting changes, if you do not want to see a preview of how your
number will look. Use the drop-down arrow next to Format: for a list of
options to apply to the selected cell.




Adding and Naming Worksheets

Sometimes you may find that you need more than three worksheets for your project. To add a new worksheet to your
workbook, go to the Insert menu and select Worksheet.

Excel Overview for Mac Users – UM University Libraries                                                                        18
To change the name of your worksheet, go to the Format menu and select Sheet          Rename. The worksheet tab will be
highlighted. Type in the name you desire and click Enter.


If you make a mistake – return to Format       Sheet     Rename and repeat the process.

(NOTE: Tab names should be as short as possible, without spaces, to enable copying and pasting formulas between
worksheets and to allow tab names to be viewed at the bottom of your workbook.)

⇒ Try this now! Insert a new worksheet into your workbook, and rename the tab to one of your choice.

Deleting Worksheets

If you are absolutely certain you will not need a particular worksheet, you may permanently delete it. To do this:

    1. Click on the sheet tab of the worksheet you want to permanently delete.
    2. Go to Edit Delete Sheet. You will see the following warning:




    3. Click OK to confirm the deletion. If you change your mind, click Cancel.

Rearranging Worksheet Order and Copying Worksheets

To help keep your workbook organized, you may rearrange the order of your worksheets in one of
two ways:


    1. Click on the desired worksheet tab and drag it to the new location in the worksheet order,
       OR

    2. Click on the appropriate worksheet tab, and go to the Edit menu. Choose Move or Copy
       Sheet. Choose a location from the list, and click OK.

              a. You may also copy worksheets to another workbook in the same manner. Simply
                 have both workbooks open (the one you are copying from and the one you are
                 copying to), and select the workbook title in the “To book” drop-down menu. If you
                 wish to keep a copy of the worksheet in both workbooks – be sure to check
                 “Create a copy” and click OK.

    In the example below – the worksheet inserted earlier has been moved to the end of the workbook:




⇒ Try this now! Use the Move or Copy feature to move Sheet1 of your workbook to the end of the workbook.



Excel Overview for Mac Users – UM University Libraries                                                               19
Excell Poiintterrs – Forr Futturre Refferrence
Exce Po n e s – Fo Fu u e Re e ence
Finding, Sorting and Filtering Data
Finding and replacing data

Excel’s FIND command can be used in order to quickly locate the contents of a specific cell. To use find:

    •    Select the range of cells you wish to search – or click in any cell in the worksheet to search the entire worksheet.

    •    Go to the Edit menu and select Find (or press       + F). In the Find what box, type the text or numbers that you
         want to search for, and click Find next. If you choose the Replace tab, you can enter text to find in the Find what
         box, and text to substitute in the Replace with box. You can choose to replace one instance or all instances of an
         item.




    •     To further define your search, you may choose from the following options:

              o    In the Within box, select Sheet or Workbook to search a worksheet or an entire workbook.

              o    In the Search box, click By Rows or By Columns.

              o    In the Look in box (in the Find box only), click Formulas, Values, or Comments.

              o    To search for case-sensitive content, select the Match case check box.

              o    To search for cells that contain just the characters that you typed in the Find what box, select the Find
                   entire cell contents check box.

Helpful Hint: You can use wildcard characters such as an asterisk (*) or a question mark (?) in your search criteria:

* Use the asterisk * to find any string of characters. For example, s*d finds "sad" and "started".

* Use the question mark ? to find any single character. For example, s?t finds "sat" and "set".

You can find asterisks, question marks, and tilde characters (~) in worksheet data by preceding them with a tilde character in
the Find what box. For example, to find data that contains "?", you would type ~? as your search criteria.




⇒ Try this now! Find data in your worksheet:

Excel Overview for Mac Users – UM University Libraries                                                                  20
1. Type your name in a cell in Sheet 3 of your worksheet.
2. Go to EDIT ► Find.
3. At the Find what box that appears, enter the text you typed in step 1 above.
4. Click Find All.

Note: Find All lists every occurrence of the item you are searching for and allows you to make a cell active by selecting a
specific occurrence. You can sort the results of a Find All search by clicking a header.


Sorting data

Sorting can be done in single levels or multiple levels. Sorting is performed on lists. A list is an organized collection of
related information, such as names or dates. In Excel, a list must contain at least one column, which represents one
category of information. If the column includes a column label, the label should contain a font, data type, alignment,
formatting or other style that differs from the data contained in the rows beneath it so Excel can recognize the difference
between labels and data.

Sorting organizes sets of data in your list alphabetically, numerically, or chronologically. If you want to rearrange sets of data
in your list according to the information contained in just one column, you perform a Quick Sort:

    1. Select all cells to be sorted, including other columns/rows that will be affected.
    2. Go to the Data menu and select Sort.
    3. Choose the column to Sort by. If you do not want to sort on Column A, use the drop-down arrow to select a
       different column.
    4. Choose Ascending or Descending order (NOTE: To alphabetize text – choose Ascending order).
    5. Choose Header row or No header row – depending upon whether or not you are using headers on your
       worksheet.
    6. Click OK.

In the example below – notice how the entry for “Janet James” is moved from Row 9 to Row 4, and the entry for “Josie
James” goes from Row 7 to Row 9 after sorting on Column A:




Helpful Hint: To perform a simple sort, you can also use the buttons on the menu bar. The             button sorts in ascending

Excel Overview for Mac Users – UM University Libraries                                                                    21
order, while the        button sorts in descending order.

                                                         Sorting Order

In an ascending sort, Microsoft Excel uses the following order. (In a descending sort, this sort order is reversed except for
blank cells, which are always placed last.)

Numbers Numbers are sorted from the smallest negative number to the largest positive number.

Dates Dates are sorted from the earliest date to the latest date.

Alphanumeric sort When you sort alphanumeric text, Excel sorts left to right, character by character. For example, if a
cell contains the text "A100," Excel places the cell after a cell that contains the entry "A1" and before a cell that contains the
entry "A11."

Text and text that includes numbers are sorted in the following order:

0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V
WXYZ

Apostrophes (') and hyphens (-) are ignored, with one exception: If two text strings are the same except for a hyphen, the
text with the hyphen is sorted last.

Logical values In logical values, FALSE is placed before TRUE.

Error values All error values are equal.

Blanks Blanks are always placed last.

Filtering data

Some lists of data can be extremely lengthy and complex. Additionally, in the midst of a complex spreadsheet you may want
to view a very discreet selection of data. You can filter a subset of the data (a list) based upon a set of search conditions (or
criteria). Unlike sorting, filtering does not rearrange a range. Filtering temporarily hides rows you do not want displayed.
When Excel filters rows, you can edit, format, chart, and print your range subset without rearranging or moving it.

The AutoFilter command, found under the DATA Filter menu enables you to apply dropdown menus unique to each
column of data in your spreadsheet with which you can apply a filter criterion. When using AutoFilter, you can choose to
view the largest values or customize the list. To remove filtering, you would simply return to the DATA Filter menu and
unselect AutoFilter.

NOTE: Excel also has an Advanced Filter command, which can filter a range in place like the AutoFilter command, but it
does not display drop-down lists for the columns. Instead, you type the criteria you want to filter by in a separate criteria
range above the range. A criteria range allows for more complex criteria to be filtered. Advanced Filter is also available
under the DATA Filter menu.




Prriinttiing iin Excell
P n ng n Exce

Excel Overview for Mac Users – UM University Libraries                                                                       22
After you have completed your data entry and calculations, it is time to print your masterpiece! Although you can use the

Print button       on the standard toolbar to quickly print your workbook, there are some items to consider before printing,
including:

    * Setting the Print Area                                     * Hiding Columns and Rows
    * Headers and Footers                                        * Page Setup & Print Preview

Setting the print area
While you can print an entire worksheet – if you have data in a cell in Row 936, Column IV, you may end up printing multiple
blank (or otherwise unwanted) pages! To avoid this, get into the habit of setting your print area first:

    1. Click on the upper-left cell of the range you want to print.
    2. Drag downward and to the right until the range you want is selected.
    3. Go to FILE Print Area and select Set Print Area.

To remove a print area so you can print your entire worksheet, go to FILE      Print Area and choose Clear Print Area.

Headers and footers
Although the sheet name you selected for your worksheet (see pages 18-19) will automatically print, Excel also allows you
to insert headers and footers to print additional information on every page printed. These can include information about the
workbook author or subject, the date and time of printing, or anything else you want to appear. To add a header and/or
                                                     footer:

                                                         1. Go to VIEW Header and Footer. The Page Setup dialog box
                                                            will appear. (Hint: Going to FILE Page Setup does the same
                                                            thing, but you may need to click on the “Header/Footer” tab.)
                                                         2. Select one of the preset headers and/or footers using the drop-
                                                            down arrows.
                                                         3. If you do not see a header/footer that meets your needs, select
                                                            Custom Header… or Custom Footer… and create your own.
                                                         4. Click OK when finished. Your header/footer will not appear in
                                                            your workbook, but you can preview this feature by choosing
                                                            FILE Print Preview. (We will spend a bit more time on Print
                                                            Preview shortly.)




Hiding rows and columns
Ordinarily, if there is information you do not want printed on your worksheet, you can avoid that by selecting only the area
you want and printing that (see above). However, if there is information in specific cells or rows that you do not want printed
(such as social security numbers or salaries), it can be easier to hide the columns, rows or cells to prevent them from being
printed. To do this:

    1. Click on the row number or column heading to select it.
    2. Go to the FORMAT menu, and select either Row or Column, then select Hide.
    3. Your row/column will be hidden – and your only clue will be the absence of the header letter or number on your
       worksheet.

To redisplay the hidden data:
    1. Select the columns adjacent to the sides of the columns or rows you want to display.
    2. From the FORMAT menu, select either Row or Column and select Unhide. The column or row will reappear.

Excel Overview for Mac Users – UM University Libraries                                                                  23
Print Preview
To see how your worksheet will print – choose the Print Preview option. You can activate print preview in two ways:


    1. Select the Print Preview icon on your toolbar          , or
    2. Go to FILE Print Preview.

Choosing either of these options will allow you to see a full-page view of your workbook, with the following menu of choices
at the top of the window:




From here, you can decide if you need to change the margins (by choosing the Margins button – which looks like a ruler on
the toolbar), or change the amount of information that appears on each page by selecting the Page Break Preview option.
You can zoom in on any area of your worksheet by clicking on the small magnifying glass. You may also directly print your
work by clicking on the printer icon, or go to the Print Setup menu by selecting Setup. To return to the normal view for your
workbook, select Close.

Page setup options
By choosing either Setup from the Print Preview menu or by choosing FILE Page Setup, the Page Setup dialog box will
appear. There are four tabs in this box (Page, Margins, Header/Footer, and Sheet). (We already examined the
Header/Footer tab.) Some of the functions you can perform with the remaining tabs are:

                                                             Page -- You can change the orientation of your print job from
                                                             Portrait to Landscape, and also choose to Scale your print job
                                                             – to “force” it to fit on one sheet of paper. You can adjust the
                                                             size manually, or allow Excel to adjust it for you by choosing
                                                             the Fit to option. (NOTE: You can also select the scaling when
you activate the Print menu – see below.)

Sheet – The light gray gridlines you see in your Excel workbook
will not print unless you specify for them to do so. Choose the
Sheet tab, and select Gridlines. Likewise, if you want Row
numbers and Column headings to print, you may choose that
checkbox as well.

Helpful Hint: If you decide you do not want to print all of the gridlines on your workbook, you may choose to add
a border around a cell or series of cells. To do this, use the Cell Format feature and choose Border.


When you have finished setting up your workbook with the
header/footer and page options that suit you, choose Print from the File
menu. If you have multiple worksheets with data, you can choose to
print the Entire Workbook. However, in most cases, you will leave the
default at Active sheets. You can also choose to print only a selection
of text.




Excel Overview for Mac Users – UM University Libraries                                                                24
Many of these same features are also available from the Page Setup section of the Formatting Palette. As the illustration to
the right shows, you can choose to activate gridlines, change the orientation of your worksheet, adjust the scaling or
margins. Once these settings are to your satisfaction – you may then choose the FILE Print option.




Accessiing Excell Hellp
Access ng Exce He p
There are a number of ways to access help features within Excel.
                                                                                         To open the Excel Help task pane:

                                                                                                     o   Click on the Help


                                                                                                         button       on the
                                                                                                         Standard Toolbar,
                                                                                                         or

                                                                                                     o   Go to the Help
                                                                                                         menu and select
                                                                                                         Excel Help.




You can also double-click on the Office Assistant. A dialog box will appear for you to
enter your query. Click Search when ready.




Addiittiionall Resourrces
Add ona Resou ces
If you are interested in learning more about Excel, you may wish to consult these titles in the Learning Curriculum
Resource Library:

Frye, Curtis. Microsoft Office Excel 2003 Step-By-Step. Redmond, WA: Microsoft Press, 2004.
Perry, Greg. Teach Yourself Microsoft Office 2003 in 24 Hours. Indianapolis, IN: Sams, 2004.

                A complete listing of titles in the Learning Curriculum Resource Library is available at:
                                 http://www.lib.umd.edu/groups/learning/lcrlibrary.html.

You may also wish to consult these online Excel guides (although written for Excel for Windows – they may still provide
useful information for you):


Excel Overview for Mac Users – UM University Libraries                                                                25
Microsoft Excel 2003. University of Wisconsin – Eau Claire. http://www.uwec.edu/help/excel03.htm

What’s New In Microsoft Excel 2003. Wellesley College. http://www.wellesley.edu/Computing/Office03/Excel03/excel03.html

Microsoft Office Training Home Page. http://office.microsoft.com/training [Select “Excel” in “Browse Training Courses”.]

Credits:
Material in this handout packet adapted from the Perry title listed above, and from Microsoft Excel 2000 Manual – University
of Miami Department of Information Technology, October 2001. http://www.it.miami.edu/classes.

Handout packet created February, 2006.

  Notes:




Excel Overview for Mac Users – UM University Libraries                                                                26

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:102
posted:4/7/2011
language:English
pages:26