Excel Tips Tricks December 2015 Commonly Used Shortcuts

Excel Tips & Tricks December 2015

Commonly Used Shortcuts (Mac, Excel 2011) Auto sum Cmd+ Shift+ T Enter Borders – outside area Cmd+ Option+ 0 Delete Ctrl+ (Minus/- ) Insert Ctrl+ I Fill right or Fill down Ctrl+ R or Ctrl+ D Font – change size Cmd+ Shift+ (Period/. ) or (Comma/, ) Go to end (contiguous range) Ctrl+ Arrow Keys Highlight a contiguous range Ctrl+ Shift+ Arrow Keys Group rows or columns Cmd+ Shift+ K Worksheets – toggle Ctrl+ Tab Worksheets – move between Fn+ Ctrl+ Down Arrow/Up Arrow Zoom – sizing or return to 100% Ctrl+ F 2 V Enter Z Enter or Cmd+ 1

Pivot Tables Step-by-Step To re-organize a spreadsheet to a new format: • Select all data in sheet, click on the tab Data, and click Pivot • From the builder, drag which items you want to be your rows, columns, and values. You can re-imagine any spreadsheet to be in the format you need it as long as there are numbers as values (ex: a count of the cells or doing a math formula with the values like summing or averaging)

Pivot Tables Step-by-Step • Choose rows and columns from the fields to populate your spreadsheet • Notice the nesting that occurs when fields are above/below each other within the label boxes • Be sure to update values from the default count by pressing on the info (“i”) icon to the right of the field name

Pivot Tables Step-by-Step • To reduce the number of columns, filter to select only the options you want to see in your table • Once the spreadsheet looks the way you want it to, select the cells including the table, copy, and Paste Values on a new sheet.

Commonly Used Functions with Descriptions Absolute vs. Relative Formulas: • A formula that contains a relative cell reference changes as you copy it from one cell to another. As an example, if you copy the formula =A 2+B 2 from cell C 2 to D 2, the formula in D 2 adjusts downward by one row and becomes =A 3+B 3. • An absolute cell reference is a cell address that contains a dollar sign ($) in the row ($A 3) or column (A$3) coordinate, or both ($A$3). When you enter a cell reference in a formula, Excel assumes it is a relative reference unless you change it to an absolute reference. • Cmd+ T to change selected cells from relative to absolute

Commonly Used Functions with Descriptions Conditional Formatting: • Conditional formatting allows you to highlight any columns or rows and color code them based on predetermined or custom-defined rules

VLOOKUP & HLOOKUP When you have two spreadsheets where one row or column is constant in both #1 and #2 (ex: market or partner name) and you want to pull data from #2 (ex: state or address) into #1 • VLOOKUP looks at a value in one column, and finds its corresponding value on the same row in another column. • HLOOKUP is the exact same function, but looks up data that has been formatted by rows instead of columns. • Formula = V or HLOOKUP (cell(s) that match on both spreadsheets on spreadsheet #1, cell(s) that you want to pull from spreadsheet #2, number of columns or rows between the two on spreadsheet #2, FALSE) More info: • http: //excelexposure. com/2011/10/25/level-1 -lookup-referencefunctions/ • http: //www. excelfunctions. net/Excel-Vlookup-Tutorial. html

Other Tips • Transpose cells when you want to convert rows into columns or columns into rows • Use Text to Columns to split up one cell by a symbol (ex: “, ”) • Add &” “& between two cells in order to combine two cells into one; and, replace the space with any other text or symbol if desired

Other Tips • Use =UPPER( ) or =LOWER( ) to quickly change case of cells • Press CMD+ALT+Return to create a second line of text within a cell • Add zeros in front of numbers by right-clicking the cell to “Format Cells” by Custom, entering “ 00000” into the field • Freeze Panes to make a big spreadsheet easier to read

Google Explore lets you export and customize charts after you upload and view a spreadsheet in Sheets in Google Drive • Because Google does this randomly, select the row you want analyzed • To edit the chart, click the graph icon and it will pop out

Google Explore Toggle between view and quick-edit mode using the eye and pencil icons to update any section of the chart (title, legend, axes)

Katie Merritt Katie. Merritt@wholesomewave. org Additional Resources: http: //excelexposure. com/lesson-guide/ /wholesomewave @wholesomewave www. wholesomewave. org