Information and Links for Excel
- 1900 Date System vs. 1904 Date System--Microsoft support article 180162 tells why Excel date in Windows may differ from date in Macintosh by a year and a day and tells how to set the date system. (added 2/5/10)
- Copy and paste subtotals, from MrExcel.com
- Copy Excel Subtotals
- Use the Goto - Special command to select just subtotals to copy them to a new area. (added 10/27/08)
- Copy and paste visible cells only (not hidden columns or hidden rows)
- From several sources found by a search for "Excel paste only visible cells"
- Select area to paste, then Edit...Go To... Special... Visible cells only... OK, then copy, then paste. (added 3/31/09)
- CSE formulas, from MrExcel.com
- Control-Shift-Enter (CSE) formulas
- CSE formulas go further than countif or sumif functions, and can be used instead of the conditional sum wizard or to create an averageif or otherfunctionif formula. (added 7/26/02) Note 12/18/12: Now Excel's built in multiple-criterion functions such as AVERAGEIFS, COUNTIFS, and SUMIFS do these operations much more easily. See this syntax hint =SUMIF(A1:A2,">"&A3 for using greater than (or less than, greater than or equal to, etc.) to compare to a variable in a cell in a SUMIF (or COUNTIF, SUMIFS, etc.) formula.
- Two-condition countif formula
- Not really a countif formula, it uses a CSE formula with the sum function to add 1 if both of two conditions are true and 0 if not. (added 7/26/02). Note 12/18/12: This is very tricky but now COUNTIFS makes multiple-condition countifs easy.
- Dates and times, from Erlandsen Data Consulting (url updated 7/31/15)
- Calculating with Dates
- (From Excel help) On Macintosh Excel, dates are expressed as a serial number starting with the first day of January 2, 1904 with serial number 1. (added 7/31/02)
- Calculating with Times
- In Excel, time is expressed by a decimal "calculated by dividing the hour by 24 hours." As a formula,
decimal=(hours/24)+((minutes/60)/24)+((seconds/360)/24).
(added 7/31/02)
- Enter a line break within a cell on a Mac by typing Control-Command-Enter (Control-Apple-Return) and on Windows with Alt-Enter. (added 3/10/10)
- How to undo number formatted as text: Microsoft Knowledge Base article kb291047 "Convert Text To Numbers in Excel 2002 and 2003." (Method 1, click on exclamation mark to show "convert to number" worked for multiple cells in Excel 2008 for Mac.)
- Keyboard Shortcuts in Excel 2011 | Excel Semi-Pro. Excel for Mac 2011 didn't seem to repeat font color. I used these hints to assign Format...Font color to Control-y. (5/1/12)
- Pearson Software Consulting Excel Help Topics (added 7/31/15)
- Solved: SUMIF with criteria greater than a variable (added 10/13/14. Hint from 25-Feb-2009)
- The last possible cell in an Excel 2011 worksheet is XFD1048576. The maximum size of an Excel 2011 worksheet is 16,384 columns by 1,048,576 rows.
(From trial and error and Specifications and limits in Excel 2011) 16,384 is 128 squared and 1,048,576 is 1,024 squared. (added 10/13/14)
- The last possible cell in an Excel 2004 worksheet is IV65536. The maximum size of the worksheet is 256 columns by 65,536 rows.
(From Excel 2004 for the Macintosh OS X at Macalester College's Computing and Information Technology) 256 is 16 squared and 65,536 is 256 squared.
- View formulas in Excel by pressing Control + Tilde keys (Ctrl ~). To return to the normal view, press Ctrl ~ again.
(Note on Page 1 of 5 from Introduction to Excel at UW Tacoma's Center for Teaching, Learning, and Technology)
- Visual Basic Microsoft Knowledge Base Article - 189895 - MacXL: Macro with Find Method Results in Compile Error
(found 8/27/04 by Googling error message "Compile error: Named argument not found")
Page done by David Miles, dmiles@u.washington.edu
