Excel Techniques, Shortcuts and Formulas by w8S0hO

VIEWS: 36 PAGES: 14

									This template was purchased by AuditNet from a third party under a work for hire
agreement. However, while we have attempted to provide accurate information no
representation is made or warranty given as to the completeness or accuracy of the
template. In particular, you should be aware that the template may be incomplete,
may contain errors, or may have become out of date. While every reasonable
precaution has been taken in the preparation of this template, neither the author nor
AuditNet assumes responsibility for errors or omissions, or for damages resulting
from the use of the information contained herein. The information contained in this
document is believed to be accurate. However, no guarantee is provided. Use this
information at your own risk.




Excel Techniques, Shortcuts and Formulas

The information in this file applies to MS Excel 2003. Menus may be
slightly different in other versions of Excel.
Excel Techniques, Shortcuts and Formulas

The information in this file applies to MS Excel 2003. Menus may be slightly
different in other versions of Excel.
Calculators
Print-Related
Dropdown Selection Menu
Notes List to Excel List
Combine Cells
Find Duplicates in One List
  Find Duplicates - Two lists
  Extract from Value
  Tips & Tricks
    Charts
  Miscellaneous
                                                                                  Print-Related      4c636814-ee3b-4ca9-9bc1-9e6a89f26df7.xls




                  Set which cells will print.
                      1 Drag to select the cells you want in the print area.
                      2 On the File menu, point to Print Area,
                        and click Set Print Area.

                  Clear a set print area
                  so the entire sheet will print.
                      1 On the File menu, point to Print Area,
                        and click Clear Print Area.
                        All data on the sheet will now print.




                  Force rows to, or constrain from, printing to the next page.
                     1 On the View menu, click Page Break Preview.
                     2 Drag the blue lines to force page breaks.




                  Print a selection in the sheet instead of the print area.
                      1 Drag to select the range of cells to print.
                      2 On the File menu, click Print.
                      3 Under the Print what section of the print menu, click on Selection.
                      4 Click OK button.




                  Constrain printout to one page.
                     1 On the File menu, click Page Setup.
                     2 Under the Scaling section of the Page tab
                       select Fit To and choose 1 for Wide and Tall.
                          This option can also be used to contrain to
                          1 page Wide ; use a large number, like 100,
                          in the Tall field.




                  Set rows or columns to repeat on each page printed.

                         1 On the File menu, click Page Setup.
                           Click on Sheet tab.

                         2 Click in the "Rows to repeat at top" field.
                           Select the row(s) in the worksheet to repeat
                           by clicking the number(s) to the left of the row(s).
                           Click OK button.

                         3 Click in the "Columns to repeat at left" field.
                           Select the column(s) in the worksheet to repeat
                           by clicking the number(s) at the top of
                           the column(s).
                           Click OK button.

                            Note: Hold down the left mouse key and drag
                            to select multiple rows or columns.



Caterpillar: Confidential Green                                                      Page 4 of 14.                                     7/27/2012
                                                                    4c636814-ee3b-4ca9-9bc1-9e6a89f26df7.xls
                                                Dropdown Selection Menu

 Turn a Lotus Notes E-mail or Calendar list into an Excel list.

 1 Create a list of the items to be available in the menu.
   Note: the list doesn't have to be on the same sheet as the dropdown menu.
 2 Create the Dropdown.
     Click on the cell where the dropdown menu will be.
     On the Data menu, select Validation.

       On the Validation menu:
       - Change the Allow option to to List.
       - Click in the Source field, then drag
          over the list created in step 1.
       Click OK button.




    Practice

          List of Items to be on menu.
          Option 1                                                 Click here and create the dropdown.
          Option 2
          Option 3
          Option 4
          Option 5




Caterpillar: Confidential Green                              Page 5 of 14.                               7/27/2012
                                                                                 4c636814-ee3b-4ca9-9bc1-9e6a89f26df7.xls
                                                           Notes List to Excel List

Turn a Lotus Notes E-mail or Calendar list into an Excel list.

1 Copy the distribution list from an e-mail and paste it
  into Excel.

2 Change the text to columns.
    Click on the cell with the names.
    Click Data > Text to Columns

    On the first screen:                                               On the second screen:
                 Select Deliminated - this will allow Excel to               Select Comma as the delimiter.
                        move each name to a separate cell                    Click Finish button.
                        based on the criteria we provide.
                 Click Next button.                                            Each employee in the list will now be in a separate column.




3 Transpose the list into one column.
    Select the entire row of values by clicking the row number
        to the left of the data.
    Select Edit (or right-click) > Copy or press "C" while holding down the Ctrl key.
    Click on the cell where the new list will be pasted.
    Select Edit (or right-click) > Paste Special

    On the Paste Special menu, select Transpose.
    Click OK button.




                   Original List Pasted As One Column
                   Adrian Navarro Alfaro/1T/Caterpillar
                   Aili J. Tran/0A/Caterpillar
                   Alexandra McDougall/1L/Caterpillar
                   Amanda.Vannaken@cat.com
                   Amy A. Barlow/0A/Caterpillar
                   Amy D. Chen/0A/Caterpillar



     Caterpillar: Confidential Green                                 Page 6 of 14.                                                       7/27/2012
                                                                Combine Cells 4c636814-ee3b-4ca9-9bc1-9e6a89f26df7.xls

                        Combine cells to create a new list

                     1 Click on the cell where you'll create the new list. (J2 in this example).

                     2 Select Insert > Function > Concatenate (or click the function button on the Formula Toolbar).
                       On the Insert Function menu:
                           Category - All.
                           Select CONCATENATE function.
                       Click OK button.




                        On the Concatenate menu:
                            Text 1 - select the cell with the data you'd like the new value to begin.
                            Text 2 - put a space in this field using the space bar.
                            Text 3 - select the cell with the data you'd like next in the new value.
                            Text 4 - put a space in this field using the space bar.
                            Text 5 - select the cell with the data you'd like the new value to end.
                              Continue alternating between spaces and text as needed.
                        Click OK button.




                     3 Copy/Paste the formula down to the last cell in the list of single instances.

                            Practice:

                            Last          First          Middle                          Full Name (new list)
                            Bush          George         W.                              George W. Bush
                            Last          First          M.




Caterpillar: Confidential Green                                        Page 7 of 14.                                   7/27/2012
                                                   Find Duplicates in One 4c636814-ee3b-4ca9-9bc1-9e6a89f26df7.xls
                                                                           List

  Count the number of times a value occurs in a list to find duplicates.

1 Click on the cell where you'll create the new list. (I7 in this example).                             Practice:
                                                                                                                        Number of
2 Select Insert > Function > Countif (or click the function button on the Formula Toolbar).             Original List   Instances
  - On the Insert Function menu:                                                                        Severson            1
      Category - All.                                                                                   McClintock
      Select COUNTIF function.                                                                          Hammond
      Click OK button.                                                                                  Carlson
                                                                                                        Johnson
                                                                                                        Johnson
                                                                                                        Kramer
                                                                                                        Jones
                                                                                                        Johnson
                                                                                                        Kramer
                                                                                                        Terrance
                                                                                                        Hamer
  - On the Countif menu:                                                                                Stevens
     Range - Click in the Range field, then drag over the list to be evaluated to select it             Mickelson
             or type in "column colon column" with no spaces (e.g., G:G) to select the entire column.   Cramer
     Criteria - type in the cell with the value to be compared against the range (e.g. G7).             Nichols
     Click OK button.                                                                                   Peterson
                                                                                                        Neaman
                                                                                                        Johnson
                                                                                                        Kramer
                                                                                                        Jones
                                                                                                        Carlson
                                                                                                        Johnson
                                                                                                        Kramer
                                                                                                        Jones



3 Copy/Paste the formula down to the last cell in the list of single instances.




Caterpillar: Confidential Green                                     Page 8 of 14.                                          7/27/2012
                                                                  Find Duplicates - Two lists4c636814-ee3b-4ca9-9bc1-9e6a89f26df7.xls

  STEP 1. Count the number of times values in a list are duplicated in another list.

1 Click on the cell where you'll create the new list. (G7 in this example).                                  Practice:
                                                                                                               List 1                                    List 2
2 Select Insert > Function > Countif (or click the function button on the Formula Toolbar).                  Duplicates      List 1         List 2     Duplicates
  - On the Insert Function menu:                                                                                 0      Ajis          Aldred               2
      Category - All.                                                                                                   Ajjour        Aldrighi
      Select COUNTIF function.                                                                                          Ajmeria       ALEXANDRE
      Click OK button.                                                                                                  Ake Ochoa     Alford
                                                                                                                        ALEXANDRE     Ali
                                                                                                                        Alain         Alla
                                                                                                                        Albasnali     Alladi
                                                                                                                        ALEXANDRE     Allan
                                                                                                                        Alberghini    Allen
                                                                                                                        Alberti       Alleyne
                                                                                                                        Albrecht      ALLOVON
                                                                                                                        Aldred        ALMEIDA
  - On the Countif menu:                                                                                                Aldred        Almengor
      Range - Drag over the original list to select it or type in "column colon column"                                 ALEXANDRE     ALONSO
              with no spaces (J:J in this example) to select the entire column.                                         Alford        Alotto
      Criteria - Click in the field, then click the cell with the value to be searched or type in the cell              Ali           Alphonse
              containing the value to be compared against the range (G7 in this example).                               Alla          Alsteen
      Click OK button.                                                                                                  Alladi        Altekar
                                                                                                                        Allan         Alvarado Abelin
                                                                                                                                      Alvarado Beltran




3 Copy/Paste the formula down to the last cell in the list of single instances.

  STEP 2. Repeat the process to compare values in the second list to the first list.

1 Click on the cell where you'll create the new list. (K7 in this example).

2 Select Insert > Function > Countif (or click the function button on the Formula Toolbar).
  - On the Insert Function menu:
      Category - All.
      Select COUNTIF function.
      Click OK button.

  - On the Countif menu:
      Range - Click in the Range field, then drag over the list to be evaluated to select it or type in "column colon column" with no spaces (H:H in this example)
              to select the entire column.
      Criteria - Click in the field, then click the cell with the value to be searched or type in the cell containing the value to be compared
              against the range (J7 in this example).
      Click OK button.

3 Copy/Paste the formula down to the last cell in the list of single instances.




         Caterpillar: Confidential Green                                           Page 9 of 14.                                                     7/27/2012
                                                           Extract from Value4c636814-ee3b-4ca9-9bc1-9e6a89f26df7.xls


                     Extract First, Middle and Last Names into Separate Cells

                     This contains advanced formulas.           Practice:

                     Suggest copy / paste these formulas       Full Name           First    Middle Last
                     into a new sheet with the list            George W. Bush      George   W.     Bush
                     of values.                                Mary R. Jones
                                                               Steven Nix




Caterpillar: Confidential Green                                  Page 10 of 14.                                7/27/2012
                                                                        Tips & Tricks                    4c636814-ee3b-4ca9-9bc1-9e6a89f26df7.xls




Quickly duplicate format.                                                                                     Practice:
   1 Select the cell in the format you want to duplicate.
   2 Double click the Format Painter to toggle it into Lock Mode.                                             Recreate this
        - Format painter is on the "Standard Toolbar" (View > Toolbars > click Standard)                      format, twice.
   3 Click each cell, or drag over multiple, to duplicate the format to that cell.
     Note: this works in all MS Office applications.




Autofill.                                                                                                     Practice:
   1 Select the list to continue (e.g. Monday, Tuesday; 1, 2, 3; etc.) by dragging
     while holding down the left mouse button.                                                                          Monday                       1
   2 Click on the bottom, right-hand corner to activate autofill and keep the button held down.                         Tuesday                      2
   3 Drag down to the last row you'd like autofilled while holding down the right mouse button.                        Wednesday                     3
   4 Release the button.
          - The Autofill options will appear so you can change the fill as needed.




Move selected cells by dragging the thick black line.                                                         Practice:
   1 First select a bunch of cells, then drag the thick boundary line
     to move the cells.                                                                                                 Monday
                                                                                                                        Tuesday




Jump to last row or column of data.                                                                           Practice:
   1 Select any cell with data and double click on the cell-border in the direction you want to go.
                                                                                                                          9              5           2
                                                                                                                          I              us          we
                                                                                                                         12.3            12         12.5




Create a Soft Return.                                                                                         Practice:
   1 When to the point at which the new line is to start, hold down the 'Alt' key and press Enter.
     A soft return creates an additional line of text within a cell.                                                           Type here and
                                                                                                                         create a second line of text.




Freeze Panes (to keep the colums at the top or rows at the left on the screen when scrolling).
    1 A. To lock rows, select the row below where you want the split to appear.
      B. To lock columns, select the column to the right of where you want the split to appear.
      C. To lock both rows and columns, click the cell below and to the right of where you want the split to appear.
    2 On the Window menu, click Freeze Panes.

   To unlock rows or columns, click Unfreeze Panes on the Window menu.




   Caterpillar: Confidential Green                                         Page 11 of 14.                                                          7/27/2012
                                                                       Tips & Tricks                  4c636814-ee3b-4ca9-9bc1-9e6a89f26df7.xls


Create or modify Excel Toolbars.
   1 View > Toolbars > Customize > Commands Tab
        - Toolbars Tab - New to create a new one.
        - Commands Tab - Drag commands from right-hand column
          to Exel toobar.
     Add tools used often or easily forgotten.




Sorting.
  Option 1 - Use if no blank columns or rows.
        1 Click on a cell in the column by which you'd like to sort.
        2 Click the A/Z button on the Standard toolbar to sort the data ascending or the Z/A button to sort decending.

   Option 2 - Use when there are blank columns or rows.                                                     Practice:

           1 Select the entire area to be sorted by dragging over it
             while holding down the left mouse button.                                                               Order             Name
           2 On the Data menu, click Sort.                                                                             1               Mary
           - My data range has - Select Header row or No header row first based on the selection.                      2               John
           - Sort by - select the first column by which to sort the data and and whether it should                     3                Sue
                     be sorted in asceding or descending order.
           - Then by - select the second column by which to sort the data if applicable and
                     whether it should be sorted in asceding or descending order.
           - Then by - select the third column by which to sort the data if applicable and
                     whether it should be sorted in asceding or descending order.




   Caterpillar: Confidential Green                                         Page 12 of 14.                                               7/27/2012

								
To top