Excel Tips & Tricks: December 2015 Insights
"Discover useful shortcuts for Excel 2011 on Mac, step-by-step guides on re-organizing spreadsheets with pivot tables, and insights into commonly used functions like absolute vs. relative formulas and conditional formatting."
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
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+ F2 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 =A2+B2 from cell C2 to D2, the formula in D2 adjusts downward by one row and becomes =A3+B3. An absolute cell reference is a cell address that contains a dollar sign ($) in the row ($A3) 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-reference- functions/ 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 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 KatieMerritt@wholesomewave.org Additional Resources: http://excelexposure.com/lesson-guide/ /wholesomewave @wholesomewave @wholesomewave www.wholesomewave.org
