Tuesday, March 26, 2013

Excel Tips for Formulas and Macros


Most of these can be found inside the Microsoft Excel Help file but here are some suggestions.

You may have an older or newer version of Excel but the versions usually build on eachother so most features will be the same:

Excel Formula Help:


OVERVIEW OF EXCEL FORMULAS
http://office.microsoft.com/en-us/excel-help/overview-of-formulas-HA102748997.aspx?CTT=1


Here are some additional examples of formulas that you can enter in a worksheet.
  • =A1+A2+A3    Adds the values in cells A1, A2, and A3.
  • =SQRT(A1)    Uses the SQRT function to return the square root of the value in A1.
  • =TODAY()    Returns the current date.
  • =UPPER("hello")     Converts the text "hello" to "HELLO" by using the UPPER worksheet function.
  • =IF(A1>0)    Tests the cell A1 to determine if it contains a value greater than 0.

vLookup Help
- Save time by join information from 2 spreadsheets.  Creates a simple relational database or combined file of 2 sources.   Each excel file must have on column or "field" that is unique and can be matched.  For example Social Security numbers or student IDs.

http://office.microsoft.com/en-us/excel-help/quick-reference-card-vlookup-refresher-HA101873816.aspx?CTT=1

Reference Card: http://officeimg.vo.msecnd.net/en-us/files/818/530/AF101984660.pdf

vLookup Excel Course and Lesson:
http://office.microsoft.com/en-us/excel-help/vlookup-what-it-is-and-when-to-use-it-RZ101862716.aspx?CTT=3

Excel Macro Help:
 - Show the developer tab to reveal the Macro Recording Tools.
 http://office.microsoft.com/en-us/excel-help/show-the-developer-tab-HA101819080.aspx

Marco may sound like a hard system to use, but once you try it you'll like it.   Try making a simple marcro first and then build on that.   Try making a macro to copy 1 cell over to another worksheet.

http://office.microsoft.com/en-us/excel-help/save-time-by-creating-and-running-macros-in-excel-2010-RZ102337714.aspx?CTT=1


Don't want to learn formulas?  Get an Excel Plug-In:
 AbleBits and excellent Excel Ad-Ins that can turn hours of time into seconds.

Add-ins for Microsoft Excel

For Microsoft Excel 2013 (32- and 64-bit), 2010 (32- and 64-bit), 2007, 2003
www.ablebits.com/excel-addins.php

www.ablebits.com