Excel Notes
-
Automatic Backup - this is a per file thing, under save-as look for keep a backup
-
Interesting Excel site. "The Excel Page". Lots of tips and demos, including VBA - http://www.j-walk.com/ss/excel/index.htm
-
Lots of examples of spread sheets gone horribly wrong... computers suck. Maybe that should be my blog - http://www.louisepryor.com/showTheme.do?theme=13
-
Conditional format that will apply if cell is less than the cell to the left =OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)
-
Copy page settings - header and footer - from one sheet to another:
-
Select the existing sheet, the source sheet, that contains the settings you want to transfer.
-
Select the target sheet(s) — the sheets you want to update – by pressing [Ctrl] and clicking each sheet tab.
-
Select Page Setup from the File menu and click OK.
-
From: http://www.techrepublic.com/blog/msoffice/copy-excel-page-setup-settings-from-one-sheet-to-another/2922
-
Day of week: =TEXT(A2, "dddd") - Calculates the day of the week for the date and returns the full name of the day of the week (Monday)
-
Month - custom field format = mmmm - then copy and paste values to a new column
-
Month in Word to number: =MONTH(DATEVALUE("01-" & A21))
-
Zero pad: =TEXT(E11,"0000")
-
Sort by email domain name - use text-to-columns on @
-
Hackish way to flag values, but, human readable: =if(FIND(A1,$B$1),"TRUE","FALSE") - where B1 is a long string "apple candy tuesday", and A has a column of words to test.
-
SUMIF - check range of 1rst term. if it meets criteria of 2nd term, sum the related value in range of 3rd term (B2:B5) -- =SUMIF(A2:A5,">160000",B2:B5)
-
Weird boxes that can't be selected for deletion?
-
Tools > Customize > Toolbars - add "Exit Design Mode" toolbar. (AKA VBA in older versions)
-
it is in fact a toggle switch for Design Mode - from http://zzoomforward.blogspot.co.uk/2013/06/how-to-remove-text-box-or-any.html
-
"Converting" SI notation field to a text field... try a new column with formula like: =B2 & ""
-
Strict XSLX are Excel 2013 and newer. "If you do not have a version of Excel other than 2007, rename your file to ZIP and open the app.xml file part in a viewer. The two tell-tale signs are: The namespace declaration for the Strict XLSX variant is http://purl.oclc.org. In addition, the workbook should have been created in Excel 2013 or later. Look at the attribute. Strict XSLX workbooks cannot be saved in Excel 2010 or earlier versions." - http://www.spreadsheet1.com/strict-spreadsheet-or-corrupted-excel-file.html
-
Formatting not copying - Go to the Format drop down menu then select "Style". Deselect all the boxes and apply.
tags: excel, !exel, !xl, xls, spreadsheet