Mike's Easy Excel Guide

-Group Classes Call 646-325-9094 -Onsite Training Essential Shortcuts Using these shortcuts will maximize your speed and efficiency. The mouse is a great tool for pointing at objects on the screen but it can be overused. Avoid the mouse when you’re trying to select cells or ranges that are not currently visible on the screen. There is no need to memorize every shortcut in Excel, only the ones that will be used frequently. These are the absolute essentials and should become automatic. CTRL + Scroll Wheel Zoom In/Out CTRL + Arrow keys Jump to the end of a range SHIFT + Arrow keys Select a range of cells CTRL + SHIFT + Arrows Jump and select to the end of a range CTRL + C Copy CTRL + V Paste CTRL + Z Undo

-Group Classes Call 646-325-9094 -Onsite Training More Absolutely Essential Shortcuts (Cont.) F2 Edit F4 Toggle Absolute/Relative CTRL + Plus Key Insert Column/Row CTRL + Minus Key Delete Column/Row CTRL + PageDown Go to next tab CTRL + Page Up Go to previous tab CTRL + A Select the whole page/range CTRL + 1 Open the Formatting Menu CTRL + S Save Workbook CTRL + N New Workbook CTRL + Y Redo CTRL + P Print CTRL + B/I/U Bold/Italic/Underline

-Group Classes Call 646-325-9094 -Onsite Training Working the Keyboard These keys will become your new best friends. These will be the most commonly pressed keys and your hands should naturally gravitate toward them. Arrow Keys Move the cursor up/down/left/right (Too often, people try to use the mouse instead of the arrows.) F2 Edit the contents of a cell (Avoid double-clicking to edit a cell. Also avoid the small formula bar to make changes. Pressing F2 will expose a cell’s contents and allow you to edit it) ENTER key Press after entering data in a cell (Too often, people forget to press ENTER after entering data. This causes problems.) ESC key Abort (If a formula becomes corrupted while editing use this to ABORT so the changes aren’t saved. Also use this to exit a menu. When in doubt, push ESC!)

-Group Classes Call 646-325-9094 -Onsite Training Values, Formats, Formulas, and Paste Special Values – any characters (numbers, letters, and symbols) within a cell Formats – how the cell looks. (font, size, number style, etc.) Formulas – always start with “=“. Pasting with CTRL + V will paste everything on the clipboard, while using Paste Special allows you to paste any combination of these elements above individually. Go to Edit>Paste Special Just the Just the Text is Pasted “Special” onto the Values Formats formatted area (or vice-versa)

-Group Classes Call 646-325-9094 -Onsite Training The Essential ALT shortcuts Pressing ALT activates the menus in all Windows Programs. Notice the underlined letters in the menu items. These ALT shortcuts are part of the ESSENTIAL shortcuts list. Memorize them. However there is no need to memorize every ALT shortcut in every menu! ALT + E + S + F Pastes only formulas ALT + E + S + V Pastes only values ALT + E + S + T Pastes only formats ALT + E + S + E Transpose

-Group Classes Call 646-325-9094 -Onsite Training Deleting Values, Formats, or BOTH Simply Pressing DEL will delete the values from a cell. To wipe a cell clear of all values and formats, we must use Edit>Clear>All. To delete just the formats, use Edit>Clear>Formats. Or.. DEL Clears Values. Do not confuse with BACKSPACE. ALT + E + A + A Clears everything from a cell (values AND formats) ALT + E + A + F Clears just the formats!

-Group Classes Call 646-325-9094 -Onsite Training Relative Cell References When a formula containing a cell reference is copied and pasted elsewhere, the new formula will point in the same relative direction as the original cell. In this example the formula =A3 was typed into cell C1. It was then copied and pasted 5 times below. Formulas in Column C: The formulas in column C will display their relative counterparts two spaces left, and two spaces down

-Group Classes Call 646-325-9094 -Onsite Training Using F2 to edit a cell and expose its formula. To edit a cell simply select it with the cursor and press F2 Notice that cell C6 is selected. By pressing F2 we can expose the contents of the cell. It also highlights the referenced cell with a matching color. We can always use this color coding to see where each reference is pointing.

-Group Classes Call 646-325-9094 -Onsite Training Absolute Cell References Sometimes we want a cell reference to point to a certain “fixed” location regardless of where it is copied. By adding “$” to the formula, we can freeze the reference to make it absolute. In this example the formula =$A$3 was typed into cell C1. It was then copied and pasted 5 times below. Formulas in Column C: column A is frozen, row 3 is frozen The formulas in column C will always display the contents of cell A3, no matter where they are copied.

-Group Classes Call 646-325-9094 -Onsite Training Mixed Cell References We may also want to only freeze a reference to just a row or just a column. By Pressing F4 we can toggle where the “$” is placed. To freeze just the column we place the $ in front of the column: =$A3 To freeze just the row we place the $ in front of the row: =A$3 In this example the formula =$A3 was typed into cell C1. It was then copied and pasted 5 times below and into the columns to the right. Notice how the formulas all refer to column A regardless of where they are copied. (Also notice how the rows still change relatively).

-Group Classes Call 646-325-9094 -Onsite Training Mixed Cell References (Cont.) The same concept can be applied for rows. In this example the formula =A$1 was typed into cell A3. It was then copied and pasted into the columns to the right and three times below. Notice how the formulas all refer to row 1 regardless of where they are copied. (Also notice how the columns still change relatively).

-Group Classes Call 646-325-9094 -Onsite Training Applying a relative formula A mathematical formula can be entered into a cell: =A1+B1 The result will add the values in those cells. =4 In this example the formula =A1+B1 was typed into cell D1. It was then copied and pasted 5 times below. Formulas in Column D: The formulas in column D will compute the addition in the relative cells. In this case, 2 cells to the left + 1 cell to the left.

-Group Classes Call 646-325-9094 -Onsite Training Applying a relative formula (Cont.) We can use this to calculate the % change on the day in our ticker data. In this case =(G11-D11)/D11 was entered into cell H1 and then copied down. To preserve our formatting colors we copied the formula using Paste Special>Formulas. Formulas in Column H: Notice the color coding. This can be used when you’re trying to see where each reference is pointing.

-Group Classes Call 646-325-9094 -Onsite Training Two Dimensional Tables We can use our knowledge of absolute and relative formulas to complete a two dimensional multiplication table. In this case =$D6*E$5 was entered into cell E6 and then copied over the whole array. Notice we froze column D and row 5. Formulas Exposed. To expose all formulas on the page like this, use CTRL- Tilde Key Tilde Key

-Group Classes Call 646-325-9094 -Onsite Training Using functions Excel allows functions to be written within formulas. For example, to get a sum for several consecutive cells, we could write: =A1+A2+A3+A4+A5 But it’s much easier to use the SUM function: =SUM(A1:A5) Of course the range is In this case, we wrote highlighted and color coded =SUM(B2:B9) into cell D4 when we push F2

-Group Classes Call 646-325-9094 -Onsite Training Using functions Cont. We can use the mouse the select the range once =SUM( has been written, or simply use the shortcuts. In situations where the ranges are very long/wide, it’s wiser to use the shortcuts to make these selections. In the edit mode we can also drag the edges of the range with the mouse to change it. Just This applies to all references in edit mode. Dragging the edge with the mouse

-Group Classes Call 646-325-9094 -Onsite Training Working with large sets of data Often we will encounter large sets of data. Usually the data is arranged, by type, in various columns with labels called “headers”. The following examples will deal with set of data representing a fictional trade blotter. Because the data is arranged in this fashion, we’ll be able to perform several types of analysis, sorting functions, and create pivot table reports.

-Group Classes Call 646-325-9094 -Onsite Training The IF function The IF function allows us to specify a condition and determine what to do whether the condition is “true” or “false”. =IF(logical test,value if true,value if false) The condition What to return if What to return if the condition is the condition is true false In this case we entered the following formula into the cells in column C: =IF(A2>40,”greater than 40”,”less”) If the cells two spaces to the left are greater than forty, it returns “greater than 40”. If not, it returns “less”. When the results are not expressed as numbers or formulas, they must be put in parenthesis as shown above.

-Group Classes Call 646-325-9094 -Onsite Training The VLOOKUP Sometimes a condition has so many possible “If” outcomes, it’s beyond the scope of linking several IFs together. Using VLOOKUP gives us the power to find a “match” in a table containing a virtually unlimited number of outcomes. Furthermore, the VLOOKUP (a vertical lookup) can pull data from any column of a table as long as we provide it with the right search information. =vlookup(lookup value,table array,column #,range lookup) Trying to find a In the leftmost In this column Can be TRUE or match to this column of this (where the FALSE. If you’re value table range leftmost column is looking for a # 1) perfect match, ALWAYS write FALSE). Careful! If you omit this argument it defaults to TRUE.

-Group Classes Call 646-325-9094 -Onsite Training The VLOOKUP Now we can match the account number to a trader name to populate a new column of trader names. Notice that we froze the table range as absolute. In this case we’re looking for MJ3939 in the table above and returning the result in the 2nd column. Of course this will work for each line as we copy the formula down.

-Group Classes Call 646-325-9094 -Onsite Training Creating a Pivot Table We start a Pivot Table in the same way we start a sort or filer. Select all of the data. Then go to Data>Pivot Table. Now we simply drag the desired column icons into the boxes in the table. To see a breakout of categories, drag the icon into the “Row Fields” or “Column Fields” boxes. To filter a certain criteria, drag it to the “Page Field” at the very top. Put the data being analyzed into the “Data” box in the middle. Page Fields Column Fields drag Row Fields Data

-Group Classes Call 646-325-9094 -Onsite Training Setting up a chart To avoid confusion while setting up a chart, simply think about what the different series are going to be and where that data is located. A series is a list values within a certain category that will be charted. To set up a simple bar chart with one series, first go to Insert>Chart and select a vertical bar chart. Go to the series tab. Add a series. Click the buttons and simply select the ranges on the page that represent the name, values, and x axis labels. labels name values

-Group Classes Call 646-325-9094 -Onsite Training Charts (cont). Perhaps we want each column to represent a different series so that we have three discreet lines in the chart. In case we can simply select the entire data range and select columns in the data range tab. This only works when the data is arranged in this convenient fashion. Usually it’s not, so we use the Series tab. series names x axis labels values

-Group Classes Call 646-325-9094 -Onsite Training Charts (cont). If the series are arranged in group as shown below, the amount of series can be manipulated by simply dragging the range markers with the mouse. Finally, we can right-click the areas of the chart to improve its aesthetic qualities by changing the formats. Dragging the edge with the mouse