Excel Functions I sometimes use
Ever create a spreadsheet where numbers need to be converted to names to identify something? I do, but not often enough so I’m putting the links here so I can use this to quickly recall how I’ve converted it to names, versions, etc..
- Convert numbers to text using ‘TEXT’ function
- https://www.ablebits.com/office-addins-blog/2014/10/10/excel-convert-number-text/
- Concatenate
- https://support.office.com/en-us/article/CONCATENATE-function-8f8ae884-2ca8-4f7a-b093-75d702bea31d
- Count cells that have text in them
- =COUNTIF(L3:T3,”*”) Reference: https://exceljet.net/formula/count-cells-that-contain-text
- Insert carriage return in cells
- hit alt + enter
- Conditional format based on dates
- https://www.ablebits.com/office-addins-blog/2014/06/17/excel-conditional-formatting-dates/#conditional-formatting-rules-dates
- Forcing date via Data > Data Validation
- https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_winother/forcing-a-particular-format-for-date-data-entry-in/fe2d68c5-12e2-45c5-afb7-a5d1be732bfe
- Convert column number to Column letters
- = SUBSTITUTE(ADDRESS(1, col_number, 4), “1”, “”)
- source: https://www.ablebits.com/office-addins-blog/convert-excel-column-number-to-letter/
- Automatically insert time in cell B when cell A is filled:
- =IF(A1<>””,IF(B1<>””,B1,NOW()),””)
- Source: https://www.extendoffice.com/documents/excel/5894-excel-insert-date-and-time-stamp.html
- Ensure the following option is selected “Enable Iterative calculation“
