Excel Formular Help - DOC - DOC

Document Sample
Excel Formular Help - DOC - DOC Powered By Docstoc
                                            Mercer County Library System
                                              Brian M. Hughes, County Executive

Enter Data
Action            Technique

1. Find the       Look for the cell with the dark border around it. This is the active cell and it is
active cell       ready for you to type in it. The cell address of the active cell appears at the
                  left-hand side of the formula bar. The cell address is always column-letter
                  first then row number. For example, cell A1 is the first cell in the
                  spreadsheet. Cell addresses are used in formulas to identify specific cells.

2. Highlight or   To highlight one cell, place the pointer in the middle of the cell and click. To
select a cell     highlight a range of cells, click in the middle of the first cell, hold your index
                  finger down and drag over the remaining cells.

                      The thick plus sign is the pointer icon for working with cells.

3. Move the           Click in the middle of a cell and it will become the active cell.
active cell           Use the arrow keys to move the active cell in the arrow‟s direction.
                      Page Up/Page Down move the active cell one full screen height up or
                      Home moves the active cell to Column A, keeping it in the same row.
                      Ctrl+Home moves the active cell to A1.

4. Enter          1. Click once to highlight/select the cell where you want to enter data.
information or    2. Type the number or word(s). Press the Enter key to complete the data
numbers                 entry and move down one row. Type the next number or word and hit
                        Enter. This will build a column of information. It is the fastest way to
                        enter data.
                  3. To enter data moving from left to right, press the Tab key to accept the
                        data into the cell and move to the next cell to the right. Repeat these
                        steps to enter a row of data.

                  Note: Text will be left-aligned by default, while values (numbers) will be right-
                  aligned. You can change the alignment by highlighting cell(s) and clicking on
                  an alignment tool or command. If you want to enter a number or date and
                  have Excel “see” it as non-numeric, first type the apostrophe ( „)

6. Print             You can print a worksheet or an entire workbook. Click on File then Print.
                     If you do not change any print settings, Excel will print the worksheet
                     currently open. To print the entire workbook (more than one sheet), click on
                     the Entire Workbook setting in the Print Dialog Box. Click OK.

7. Print by          To print some, but not all, of a worksheet, highlight the desired data. Click
selection or print   on File then Print then the Selection setting. Only the print area selected
area                 will print. You can also set the print area after you highlight the data-- click
                     on the File menu then Print Area and Set print area.

8. Page setup        The Page setup command on the file menu includes margins, landscape
                     vs. portrait and header and footer choices. You can center the data
                     horizontally and/or vertically from the Margins Tab.
                     The Page Tab allows the data scale to be adjusted to X% so the data may
                     be enlarged or shrunk. Further, the data may be printed to fit to X pages
                     wide by X pages tall.

9. Switch            Each worksheet has a tab at the bottom with its name on it. Click on a tab to
between              make that worksheet active. If you have many worksheets, use the
worksheets           sideways triangle symbols to move right and left among the worksheets.

10. Name a           To name a sheet, double click on its tab and type a new name. Another way
worksheet            is to right-click on the tab and click on Rename.

11. Insert a         You may have as many worksheets as you want. Click on Insert then
worksheet            Worksheet.

12. Delete a         Open the worksheet to be deleted. Click on the Edit menu then Delete
worksheet            sheet.

13. Change           To remove the contents of the active cell, press the Delete key.
data already         To replace a cell‟s contents with new information, click on the cell and begin
entered              typing.
                     To edit data in a cell without erasing the cell‟s contents, double click in the
                     cell to enter edit mode. The cell now contains an insertion point. Make the
                     desired changes then press Enter.

14. Highlight a     Hold the pointer over the center of the first cell you want to highlight or
group of cells or   select. Press the left mouse button and hold it down. Now drag the mouse
range               to highlight all the cells you want then release the button. This is called
                    “Click „n Drag.”
                    A group of cells is called a range. The range is described as “the first cell to
                    the last cell.” The range is represented in Excel language as “B3:F3.” The
                    colon means “to.”

15. Highlight       Click on the grey row number or the grey column letter. The entire row or
entire rows or      column will be highlighted. To highlight several rows or columns that are
columns             next to one another, click „n drag from the first to last column in the grey
                    area. To highlight several rows or columns that are not next to one another,
                    hold down the Ctrl key and click each row or column header.

16. Change the      To change column width, place the mouse pointer at the junction between
height of a         any two column letters at the top of the columns. When you see the double
column              arrow, click „n drag the column‟s vertical border to the desired column width.
                    To make the column automatically fit its largest entry, double click while you
                    see the above double arrow. To change a group of columns, highlight them;
                    click Format then Columns then AutoFit Selection.

17. Change the      To change the row height of one row, drag the boundary below the row
row height          heading until the row is the height that you want.
                    You can also select the row or rows that you want to change. Then from the
                    Format menu, choose Row and then click AutoFit.

18. Insert a row    Select the row above which you want to insert the new row. Click on the
or column           Insert menu then Rows. The new row will appear above the original row.

                    To insert a single column, select the column or a cell in the column
                    immediately to the right of where you want to insert the new column. Click
                    on the Insert menu then Columns.

19. Delete rows/    Highlight the rows or columns to be deleted. Click on the Edit menu, then
columns             Delete.

20. Cut & paste     Highlight the cells to be moved beginning with the uppermost left cell, which
                    will become the anchor cell.
                    Click on the Cut tool on the toolbar.
                    Click where you would like the upper left cell (anchor cell) to appear.
                    Click the Paste tool on the toolbar.

21. Copy &          Highlight the cells to be copied beginning with the uppermost left cell, which
paste               will become the anchor cell
                    Click on the Copy tool on the toolbar.
                    Click where you would like the upper left cell (anchor cell) to appear.
                    4. Click the Paste tool on the toolbar.

22. Insert a cell   Click where you need a new cell to appear. Click on the Insert menu then
                    Cell. You‟ll be asked how to move the data adjacent to the new cell.

22. Delete a cell   Click where you need to delete a redundant cell. Click on the Edit menu
                    then Delete. Choose how to move the data adjacent to the new cell and
                    click OK.

23. Copy and        Click on the cell containing the formula. Click the Copy icon on the toolbar.
paste a formula     Click the destination cell and click the Paste icon on the toolbar.
                    In the pasted formula, the cell references have automatically changed to
                    refer to cells in the same relative positions as in the original formula. This is
                    called "relative cell referencing".

24. Copy a          When Excel copies a formula, it automatically changes the column and row
Formula with the    references. For example, an autosum formula copied from column A to
Fill Handle         column B will changed as follows. The cell range will change from
                    Sum(a1:a14) to Sum(b1:b14).

                    The Fill Handle is faster than using the Copy and Paste method. Click on
                    the cell containing the formula to be copied. Position your pointer on the
                    little square or handle in the lower right corner of the active cell.

                    When you see the “thin plus” symbol (shown at left), click and drag
                    vertically or horizontally to fill additional cells with a formula.

                    Note: This only works when the cells are contiguous.

25. Cut and         Click on the cell containing the formula.
paste a formula     Click the Cut icon on the toolbar.
                    Click the destination cell and then click the Paste icon on the toolbar.
                    The formula will be removed from its original location and placed in the new
                    With this command, relative cell referencing does not apply, and the formula
                    still refers to the original cells. Edit the formula to refer to the new column
                    and row.

26. Paste           When you copy and paste a cell‟s contents, you may paste the formula
Special             (paste command) or the value that was calculated by the formula (paste
                    special command). To paste the values, highlight and copy the desired
                    results and click where you want them to be pasted. Click on Edit then
                    Paste Special. Click on Values then OK.

27. Move data       Copy the data in one or more columns or rows.
between rows        Before you paste the copied data, right-click your first destination cell (the
and columns         first cell of the row or column into which you want to paste your data), and
                    then click Paste Special.
                    In the Paste Special dialog box, select Transpose, and then click OK.

28. Locking a         “Locking a cell” is also called an “absolute” reference. Sometimes you
cell reference        will need to refer to the same specific cell on the worksheet in every copy of
into a formula        a formula. In this case, use an absolute reference. An absolute reference
                      is fixed and never changes even if you move or copy the formula. Absolute
                      references are denoted with dollar signs ($) before the column and row
                      address, such as $A$1.

Excel‟s Order of Calculation
Action           Technique

                 Operation           Symbol        Example                Order

                 Parentheses             ()        =(4+2)*8               1st

                 Exponents               ^         =3^4 (3 to the         2nd
                                                   fourth power)
29. Math
                 Division                 /        =8/2                   3rd
                 Multiplication           *        =4*6                   3rd

                 Subtraction              -        =3-1                   4th

                 Addition                +         =5+5                   4th

30. Type a        1. Highlight the desired cell and type the equals sign (=). This is the signal to
formula from         Excel that what comes after is a formula or function.
                  2. Type the first cell name

                  3. Type the arithmetical operator (+, -, *, /, ^)

                  4. Type the next cell name and the arithmetical operator, etc…

                  5. When done writing formula, press Enter. The result is displayed in the cell.

                  Reminder: When writing your formulas, remember the order of operations!

                  Note: After entering the formula, the formula will appear only in the “formula
                  bar.” The solution to the formula will show in the cell itself.

31. Type        Rather than typing “A4” into a formula, you can click on A4 and it will appear
and “click” a   in the formula. This can be a time saver.
                1. Highlight the desired cell and type the equals sign (=). This is the signal to
                   Excel to prepare to write a formula or function

                2. Click on the first cell in the formula

                3. Type the arithmetical operator (+, -, *, /, ^)

                4. Click on the next cell in the formula and type the arithmetical operator…

                5. When done, press Enter. The calculated result is displayed in the cell.

32. Why         Operations enclosed in parentheses will be performed before other
parentheses     operations. For example, (4+3)*8 is not the same as 4+3*8.
are critical
                A) (4+3)*8 =56: Excel performs operations in parentheses ( ) first, so Excel
                first adds 4+3=7 and then performs the multiplication of 7*8=56.

                 B) 4+3*8=28: Excel looks first for ( )‟s, and because it does not find any, the
                program performs the multiplication first, so 3*8=24, then Excel performs the
                addition, so 4+24=28.

33. Function        Excel can perform many functions that are predetermined mathematical
basics              tasks including sums, averages and counting. Like formulas, functions
                    start with the equals sign and use cell ranges to refer to groups of cells.
                    For example, A1:A3 stands for "The cells from A1 through A3."

                    The basic structure of a function is: =Function Name (Cell Range). For
                    example, =SUM(A1:A3) will produce the same numerical result as
                    =A1+A2+A3. Insert or type functions in the cells where you want the
                    answers to appear.

                    All functions are available through the Insert menu and the function
                    command. You can also access all the functions by clicking on the function

34. Insert a     Click on the Insert menu then the function command. Search for a
function         function using the search box or select a function from those showing in
                 the selection box. If the function you need is not visible, type the name in
                 the search box or choose a Category of functions to explore.

                 Name and            Description
35. Commonly     Syntax
used functions
                 =SUM(A1:A3)         Gives the sum of all cells found within the given range.

                 =AVERAGE(A1:        Gives the average of all cells found within the given
                 A3)                 range.

                 =MAX(A1:A3)         Gives the maximum value found within the given

                 =MIN(A1:A3)         Gives the minimum value found within the given

                 =COUNT(A1:A3        Counts the cells within the range which contain
                 )                   numerical values

                 =COUNTA(A1:A        Counts the cells within the range which contain any
                 3)                  data

36. Autosum      The addition of a column or a row of numbers is simple enough for Excel
Function         to make the calculation automatically. Most often, this is done at the
                 bottom of a column or at the right of a row of continuous numbers.

                 Highlight the cell where you want the sum of two or more numbers to
                 appear. On the toolbar, click on the Autosum icon on the toolbar.
                 Hit Enter.

                 Note: If you highlight a row of numbers without pre-selecting the cell for
                 your answer, Excel will place it in the first open cell

Cells must be highlighted prior to formatting!
37. Bold and     Highlight the cells that label the contents of each column. Click on the
center column
labels           Bold     and Center tools     .

38. Merge and       To center your title over all the data, type your title, highlight the cells in
center several      one row which cover all the columns of data. Click on the Merge and
cells               Center tool on the formatting toolbar.

39. Format          First highlight the numbers to be formatted. Click on the Format
numbers             menu then the Cells command. Click on the Number tab then the
                    Number category and you‟ll see that the number of decimals is already
                    set to two. Set the number of decimals you want then click OK.
                    On the Number tab, you can also show negative numbers in red or with
                    minus signs.

40. Change font     Highlight the data. Click on the Format menu then Cells. Click on the
                    Font tab to make changes including font size and style and bold or italic.

41. Hide or         Select the rows or columns you want to hide. On the Format menu, point
display rows and    to Row or Column, and then click Hide.
                    To display hidden rows, select the row above and below the rows you
                    want to display.
                    To display hidden columns, select the column adjacent to either side of
                    the columns you want to display.
                    On the Format menu, point to Row or Column, and then click Unhide.

42. Split merged    Make the merged cell the active cell. Click on Format, then Cells, then
cells apart         the Alignment tab. Under Text Control, uncheck the box called "Merge
                    Cells". Click OK. The cells will now be split apart and the cell contents will
                    appear in the upper left hand cell of the group.

43. Allow text to   Highlight the cells, rows, or columns you wish to affect. Click on Format,
wrap to a 2nd       then Cells, then the Alignment tab. Under Text Control, click in the
line in a cell      check box called Wrap Text. Click OK. When you type in these cells, they
                    will expand in height to contain whatever text is typed. To remove this
                    effect, uncheck the check box.

44. Place text      This technique is often used to accommodate long labels for columns.
into cells at an    Highlight the cells, rows, or columns you wish to affect. Click on Format,
angle               then Cells, then the Alignment tab. In the orientation section, click „n
                    drag the hand of the compass to set the angle. Click OK.

45. Place           Highlight the group of cells, then click on Format. Choose Cells and the
borders around      Border tab. Click on the desired line style from the Style box to the right.
a group of cells    Click on a color, if desired. Click the borders of the sample box on the left,
                    only in those locations where you would like a line. You can use multiple
                    line styles, for example have a heavy outline around the whole table and
                    lighter horizontal lines between the cells. Click OK when finished.

46. Change text     Highlight the cells, rows, or columns you wish to affect. Click on the
color               Format menu then Cells.

                    To change the font color, choose the Font color tool and click the drop-
                    down triangle. Click on the desired color.

47. Change          To change the color that fills the background of the cell, click on the
filling color       Filling tool icon and choose a color.

48. Quickly         Highlight the area to format then click the Format menu and AutoFormat.
apply multiple      Choose the desired table format from the list. The chosen style will appear
formats to a data   in the Sample box. To apply only certain styles (such as font, borders, or
table               colors), choose the Options button and make sure only the desired
                    formats are checked.

49. Insert          Click the Insert menu, then Picture, then WordArt. Click on the basic
WordArt             style you prefer from the WordArt gallery. Type your text then click OK.

                    Drag the double arrow ↔ on the square handles to resize. To change fill
                    colors and patterns on your WordArt, click the Format WordArt tool on the
                    WordArt toolbar. Then:

                          Click the downward pointing triangle beside Color, and then choose
                           your desired color from the choices available.

                          If you want a color that is not available, click More colors and pick
                           your color. Custom colors are available on the Custom Colors tab.

                          To change the color pattern/arrangement, click Fill Effects and
                           proceed from there!

50. Insert Clip     Click the Insert menu, then Picture, then Clip Art. Click on the desired
Art                 graphic to insert it. Drag the square handles to change size; drag the
                    entire graphic to move it to a new location.

51. Make a            1. Highlight the data range you want to graph
graph from a
data table            2. Click the Chart Wizard tool      on the Standard toolbar

                      3. Highlight the desired chart type then click Next

                      4. Make sure the range (data chosen) is correct then click Next

                      5. Fill in the title and other information boxes. Click the Legend and
                         Data Labels tabs for additional options.

                      6. Choose a location for the chart. You can place the chart in the
                         same sheet as the data or you place the chart on a new sheet.
                         Click Finish.

52. Turn on the    Click on View then Toolbars then Chart.
Chart Toolbar

53. Re-size the    Click on the background of the chart. Black handles will appear around the
chart              edges. Drag a corner handle with the double-arrow pointer to increase
                   chart size and keep the chart in proportion.

54. Change font    Click on the element whose text size you wish to change. That element
size of labels     will now be defined with handles.
                   Change the font size using the font size box on the formatting toolbar.
                   You can also right-click and choose from the menu.

55. Change         Click on the element you wish to affect. That element will now be defined
color, shading &   with handles. Click the Format button on the Chart toolbar. A dialog box
pattern            will appear giving formatting options for the highlighted element.

                   You can also double click on the desired element. The Format dialog box
                   will appear.

56. Remove a       Click on the data series you wish to remove. Click the Edit menu, then
series from the    Clear, then Series. Or you can right click on the data series you wish to
chart              remove, then left click the word Clear on the pop-up menu.

57. Print a        Click on the graph so you can see the black handles. Click the Print
graph on its own   Preview button on the Standard toolbar, and confirm that the graph is
page               sized to fit the whole page. Click Print.

58. Print a        This only works when you have placed the graph on the same page as the
graph & data on    data.
the same page

                    Click on any cell in the data table, making sure that the graph does not
                    have the square black handles visible. Click the Print Preview button on
                    the Standard toolbar, and confirm that the graph and source data both
                    appear on the page. Click Print.

59. Selecting all   Two or more adjacent sheets
worksheets          Click the tab for the first sheet, and then hold down SHIFT and click the
                    tab for the last sheet.
                    Two or more nonadjacent sheets
                    Click the tab for the first sheet, and then hold down CTRL and click the
                    tabs for the other sheets.
                    All sheets in a workbook
                    Right-click a sheet tab, and then click Select All Sheets on the shortcut

                    Tip: To cancel a selection of multiple sheets in a workbook, click any
                    unselected sheet.
                    If no unselected sheet is visible, right-click the tab of a selected sheet.
                    Then click Ungroup Sheets on the shortcut menu.

60. Add a           Comments are notes that you enter for a cell.
comment to a            1. Click the cell you want to comment on.
cell                    2. On the Insert menu, click Comment.
                        3. In the box, type the comment text.
                    If you don't want your name in the comment, select and delete the name.
                        4. When you finish typing the text, click outside the comment box.
                    To delete a comment:
                        1. Select the cells containing the comments you want to delete.
                        2. On the Edit menu, point to Clear, and then click Comments.
                    To display a Comment:
                    Right-click its cell and then click Show Comment on the shortcut menu.
                    To display all comments, click Comments on the View menu.
                    To print a comment:
                        1. Click the worksheet.
                        2. If you want to print the comments in place on the worksheet, display
                            the comments you want to print.
                         3. On the File menu, click Page Setup, and then click the Sheet tab.
                        3. Do one of the following:
                    To print the comments where they appear on the worksheet, click As
                    displayed on sheet in the Comments box.
                    To print the comments at the end of the sheet, click At end of sheet in the
                    Comments box.
                    Click Print.

61. Show or           Gridlines are displayed by default on a worksheet, but you can hide and show
hide Gridlines        them as needed.
on a                  Select one or all the worksheets.
worksheet             On the Tools menu, click Options.
                      On the View tab, under Window options, clear or select the Gridlines check
                      box to hide or show the gridlines.

62. Sort a            Enter your data and include a label for each column. Decide which column you
data table            want to rearrange or sort. Highlight the labels and all the information on either
                      side of the column you want to sort. Click on the Data menu then the Sort
                      command. Click on      under “Sort by” and click on the desired column. Click
                      on the Ascending button to sort from low to high. The Descending button will
                      sort from high to low.

63. Freeze or         You can view two areas of a worksheet and lock rows or columns in one area by
lock rows and         splitting or freezing panes. When you split panes, you'll be able to scroll in both
columns               areas of the worksheet, while rows or columns in the non-scrolled area remain
                      When you freeze panes, you select specific rows or columns that remain visible
                      when scrolling in the worksheet. For example, you would freeze panes to keep
                      row and column labels visible as you scroll.
                      To lock rows, select the row below where you want the split to appear.
                      To lock columns, select the column to the right of where you want the split to
                      To lock both rows and columns, click the cell below and to the right of where you
                      want the split to appear.
                          1. On the Window menu, click Split.
                          2. To remove the split, click Remove Split on the Window menu.
                      To Freeze columns or rows
                          1. On the Window menu, click Freeze Panes.
                          2. To unlock rows, click Unfreeze Panes on the Window menu.

64. F2 key            Click on a cell containing a formula. Hit the F2 key and notice how colors are
(Edit)                used to demonstrate which cells are being used in the formula. The color of the
                      element in the formula corresponds to the color around the cell.
65. Help              Click on the Help menu then Microsoft Excel Help. There are two ways to
                      search: type a word or phrase in the Search Box. If the first word doesn‟t work,
                      try again!
                      Another option is to click on the blue Table of Contents link.

Source: Stow-Munroe Falls Public Library, Ohio at www.smfpl.org.
Microsoft Office Online at http://office.microsoft.com
January 2007


Description: Excel Formular Help document sample