1 / 0

Useful Excel Tips

Useful Excel Tips. J. D. White Sr. Lecturer – Texas A&M University March 2014. Overview. Quick Keyboard Shortcuts Using Visual Basic Excel Steam Tables & Interpolate Array / Matrix Notes Curve Fitting Linest () and Logest () Solver fitting Range Names & Large Data Management.

vaughn
Télécharger la présentation

Useful Excel Tips

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Useful Excel Tips J. D. White Sr. Lecturer – Texas A&M University March 2014
  2. Overview Quick Keyboard Shortcuts Using Visual Basic Excel Steam Tables & Interpolate Array / Matrix Notes Curve Fitting Linest() and Logest() Solver fitting Range Names & Large Data Management
  3. Background Using Excel 2010 Most of these tips work in other versions but I have not verified Sample spreadsheet and tip sheets available for download from TAMU AIChE Regional Conference Website Most tips learned from others Attempted to mention credits but some references were not recorded
  4. Keyboard Shortcuts Important for speed Review of favorites
  5. Visual Basic Add to a Ribbon Menu (e.g. View) Visual Basic Window Modules Add to a blank worksheet Custom functions Or use Alt + F11 to open VB window directly
  6. Excel Steam Tables Downloaded at http://www.mycheme.com/category/spreadsheets/ See credits inside VB code Added English unit function calls Other useful spreadsheets available from the same site
  7. Interpolate( ) Included with steam table spreadsheet Only key “restriction” is that the lookup data must be in ascending order Code includes a warning if you forget
  8. Curve Fitting (Function Call) Typical curve fit is using graph and adding a trend line Not very flexible for calculations Get direct access to equation coefficients using Excel functions Will need to use array functions in Excel Or understand Index( ) function { } designates an array formula
  9. Curve Fitting (Solver Solution) Curve fitting using least squares sum Estimate equation coefficients Calculate predicted value Square the error between predicted and measured Let Solver minimize the sum of the errors by changing the coefficients Should be careful of how error is calculated
  10. Cool Reference A great Excel reference site for engineering solutions http://newtonexcelbach.wordpress.com/downloads-by-category/ My Solver curve fit examples are from this site
  11. Matrix Equations Solving simultaneous linear equations 1x + 3y - 1z = 8 3x - 1y - 2z = 2 0x - 2y + 1z = 10 Use matrix algebra functions in Excel MINVERSE() MMULT() Remember to use Ctrl-Shift-Enter
  12. Managing Large Data Sheets 1000+ rows of data Common for process data analysis Organization is key Create a Sequence column Use “Flag” columns to select data Pivot Tables are GREAT Range Names help with organization
  13. Range Names Allows easy reference to common data One or many columns of data Fixed range vs Dynamic range
  14. Dynamic Range Name OFFSET(reference, rows, cols, [height], [width]) Example Range Name Formula =OFFSET($A$7,0,0,COUNTA($A:$A),COUNTA($7:$7)) Note how the range expands automatically to include new rows and columns Need to be careful with headings and blank columns
  15. Pivot Tables Incredibly easy organization of huge databases Selection criteria Sorting order Data extraction using double-click
  16. Summary Hopefully you picked up a couple of hints that were new to you and makes you more efficient using Excel Comments and Questions are welcomed
More Related