Excel for the Law School

Class Files

Finished files

Day 1

1. Copying and Naming a Worksheet
   1. Right Click on the worksheet tab, select “Move or Copy”
   2. Check the “Create a copy” box
   3. Click OK
   4. Right Click on the worksheet tab, select Rename
   5. To do:
      1. Make a copy of the first worksheet (No. and Dividend)
      2. Name the new, copied worksheet “Master”
2. Number Formatting
   1. Select the Numbers column
   2. Select Number Format Dropdown and click currency
   3. Click the reduce decimal points button underneath
   4. To do:
      1. Add a dollar sign to the beginning of the dividends numbers
      2. Remove decimal point or numbers
3. Adding new columns
   1. Select the column to the right of where you want to add
   2. Right click the column header, click Insert
      1. Insert a new column between Name (Column B) and Shares (Column C)
      2. Name the new column (Column C) “Brokerage”
4. Removing duplicates
   1. Select all data
   2. Go to Data>Remove Duplicates, then OK
   3. To do:
      1. Remove all the duplicates in the Master worksheet.
5. Filling information with VLOOKUP (Two methods)
   1. 1. Sort the master list, then the List with data
      2. Filter out null shares in data sheet
      3. Copy data into master sheet
   2. 1. Use VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
      2. =VLOOKUP($A2,’No., Name, Brokerage, Shares’!$A$2:$D$949,COLUMN(B2),FALSE)
   3. To do:
      1. Fill in columns B:D
6. Basic Functions
   1. Insert a basic math equation based on cells
   2. Different ways of entering a cell into a function
      1. Click + drag
      2. Arrow keys + shift
      3. Typing a range
   3. To do:
      1. Fill in Dividend per Share column
7. Changing cell sizes
   1. Double click column dividers to auto-size
   2. Make everything visible and on one line
   3. To do:
      1. Change cells in Master so that each cell:
         1. Is completely visible
         2. Occupies a single line
8. MAX() and MIN()
   1. Find max and min values of dividends per share
   2. To do:
      1. Fill in K3 and K4
9. COUNTIF()
   1. Count number of investors who received the high price and low price
   2. Add them together in overall
   3. To do:
      1. Fill in I3:I5. I5 is total investors
10. SUMIF()
    1. Add total number of shares held at the high price and low price
    2. To do:
       1. Fill in J3:J5. J5 is total shares held

Day 2

11. Basic Formula Syntax
    1. Fill Total Dividend Paid column
    2. L5 is sum of all dividends paid
12. Basic Formula Syntax
    1. Total Dividend Paid divided by Overall Shares held to find average for K5
13. Basic Formula Syntax
    1. Compute and input in Column M the difference between (a) what the investors who received lower dividend per share would have been paid had they actually received the average dividend per share and (b) what the investors who received a lower dividend were actually paid
    2. Calculating damages
14. Printing
    1. Print area
       1. View>Page Break Preview
       2. Exclude summary table
    2. Running Headers
       1. File>Page Setup>Sheet, then Set Rows to repeat at top
    3. Page Orientation
       1. File>Page Setup>Page
       2. Page Layout>Orientation
    4. Margins
       1. Page Layout>Margins>Narrow
    5. Limit Width
       1. Page Layout>Width – set to 1 page
15. Multilevel Sorting
    1. Select Data you want to sort
    2. Home>Sort & Filter>Custom Sort…
    3. Add, edit, and remove sorting rules
    4. To do:
       1. Sort Master
          1. First by dividend per share (Largest to Smallest)
          2. Then by brokerage (A to Z)
          3. Then by investor number (Smallest to Largest)
16. Pivot Tables
    1. Select all data
    2. Insert>PivotTable
    3. Check boxes at the top if you want to include that data
    4. Drag fields between the four boxes to organize
    5. Right click a field, click Field Settings, then Number to change number formats
17. Pivot Charts
    1. Select a cell in your pivot table
    2. Insert>Pivot Chart