Skill Purpose Steps to do it Components of the Function Formatting Freeze panes To keep an area of an excel worksheet visible while scrolling to another area. View—Window: Freeze Panes icon select what want to freeze Alternate (for MAC 2016 only): ‘Window’ on top task bar freeze panes Dates vs. numbers vs. text Dates – can change excel values to appear as a date and specify the type of date format Numbers – can specify type of format (percentage, currency, etc.) Text – so that information (including numbers) in the cell will NOT be treated as a value that can be calculated. Primary: Home—Cells: Format Format Cells select type of formatting Alternate: Right click Format Cells Date (or number or % or whatever) Custom Use if want alternative number format not preselected in excel. Primary: Home—Cells: Format Format Cells select custom customize option Alternate: Right click Format Cells select custom customize option Text wrap Use if want text to appear on multiple lines within a cell. To create a line break, enter ALT+ENTER. Primary: Home—Alignment: Wrap Text icon Alternate: Right click Format Cells Alignment Text control
If (if/then) A logic statement in excel that can be used to check a certain criteria. If the criteria is found to be TRUE, then it returns a predefined value; however, if it is false it returns a different value. Functionally, it can be used to create categories or identify values that are or are not in a specified range. Formulas—Function Library: Insert Function icon OR Logical icon IF dialog box NOTE: depict cells without values as “” Can embed IF statements within IF statements Logical_test: enter statement that you wish to evaluate referring to specific cell Value_if_true: enter the value you want to appear if the statement from the logical test is true Value_if_false: enter the value you want to appear if the statement from the logical test is false Vlookup Tool that allows you to search one row or column or multiple rows and columns; almost like a phone book where you start with a piece of information you know. Functionally, allows you to merge data sets via a linking variable (such as patient medical record number). Also helpful when you need to find information in a very large spreadsheet. Insert new column/select the cell where you want the Vlookup formula to be calculated Formulas—Function Library: Insert Function icon VLOOKUP dialog box Select the function components as indicated NOTE: Index/linking variable (e.g. MRN) must be in the 1st column of the named range Lookup_value: is the linking variable in your current dataset (or the cells in which you will enter the value whose data you’re looking for) Table_array: is the dataset you are looking in that has the linking variable in the first column (or the data you want vlookup to use for its search) Col_index_number: specify which column within the table_array you would like to look in (number of columns over from the index) Range_lookup: specify FALSE for exact match and TRUE for approximate match Statistical tests Many basic statistical tests are available in excel. These can be helpful to do quick statistical calculations, but probably would not be your primary data analysis tool. Two options to locate different tests: Formulas – function library more functions statistical view available functions Data – Analysis Data analysis view and select desired test