Excel tips

This page was last edited on 1 December 2019, at 03:55.

Excel is a spreadsheet program developed by Microsoft.


1 Excel usage tips

1.1 How to unpivot data

In Excel 2007?

1.2 Frequency

=COUNTIF(Raw!B$2:Raw!B$94922,A2)

1.3 How to extract columns with information if a DB export has hundreds of columns that are empty

  1. Insert a row below the labels row
  2. =DATAROWS-COUNTBLANK(FULL COLUMN excluding the two top rows)
  3. copy & transpose paste in another spreadsheet
  4. delete the two rows
  5. select empty columns and delete them

=> didn't work in a spreadsheet with 35,000 entries

1.4 To use the numeric content of a cell to define the location of another cell within a formula

http://www.contextures.com/xlFunctions01.html#Offset

1.5 Last word in Cell

=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

from http://www.exceltip.com/st/Extract_the_last_word_in_a_cell_in_Microsoft_Excel/368.html

Retrieved from "https://femiwiki.com/index.php?title=Excel_tips&oldid=154244"