Excelling!

Workbook v Worksheet

Workbook: The entire Excel File (google drive folder)

Worksheet: One tab inside of the file (Google doc inside folder)

Cell, Rows, Columns, Cell Reference

Cell: One box (A1)

Column: Vertical (A, B, C)

Row: Horizontal (1, 2, 3)

Cell Reference ex B4: Column B and Row 4!

AUM & Liquidity

AUM: Total $ Managed

Liquidity: How easily money can be accessed

Cleaning a Basic Sheet: Format Account Value & AUM

1. Go to Home and the % or Number format! Select what you want (curency in this example)

2. Make sure:

   • $ symbol

   • Commas

   • 2 decimal places

3. Go to Home and then % or Number Format, select percentage and make sure 2 decimals this is AUM

Format headers (Row 1)

1. Select Row 1

2. Make text:

   • Bold

   • Slightly larger font (11 or 12)

3. Add a bottom border

Freeze Panes!

Step 6: Freeze the top row

1. Click any cell (A1 is fine)

2. Go to View → Freeze Panes → Freeze Top Row

3. Scroll down and headers are still visible!

Column Width Formatting

1. select whole excel sheet on top left triangle

2. right click on a top cell (ABC)

3. select column width input 18 or whatever

4. boom!

Prevent excel’s “Helpful” reformatting

Check (don’t change yet, just locate):

• File → Options → Advanced

• Find Cut, Copy, and Paste

• Confirm:

  • Keep Source Formatting

  • Keep Text Only from other programs

Sorting

So if you have a spreadsheet that is set in place you can sort by largest amount or something like that. For example step by step you can

- click on any cell in the column you want to sort

- top menu click data

- click sort z-a (largest to smallest)

Filtering

click anywhere in table and go to data and click filter (little droptown arrows appear at the top of each column)

- click dropdown arrow on type

- uncheck everythign except equity and click ok

- remove the filter by clicking smae dropdown,s elect all and ok.

Pie Chart

This shows how a total is split so (equity v cash v bond)

Who gets what %

use it during a moment in time and you wnat percentages.

How to:

Click and drag to highlight your columns. Top menu click insert and look for pie chart icon

Line CHart

How to: 1. Highlight both columns ). Go to Insert, Click the Line Chart icon, Choose the first basic line