Basic excel Skills for Pharmacy Residents – Excel 2013 for PC



SkillPurposeSteps to do itComponents of the FunctionFormattingFreeze panesTo keep an area of an excel worksheet visible while scrolling to another area.View—Window: Freeze Panes icon select what want to freezeAlternate (for MAC 2016 only): ‘Window’ on top task bar freeze panes Dates vs. numbers vs. textDates – can change excel values to appear as a date and specify the type of date formatNumbers – 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)CustomUse 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 optionText wrapUse if want text to appear on multiple lines within a cell. To create a line break, enter ALT+ENTER. Primary: Home—Alignment: Wrap Text iconAlternate: Right click Format Cells Alignment Text controlVertical text Can be helpful if you are trying to conserve width in your spreadsheet. Primary: Home—Alignment: ab arrow icon select vertical text Alternate: Right click Format Cells Alignment Orientation change from 0 to 90 degreesConvert to a tableAdds certain functionality to the spreadsheet (such as shading alternate lines, easy sorting and filtering, auto-applying formulas to entire column, etc.)Primary: Home—Styles: Format as Table pick a color/style you likeAlternate: Insert—Tables: Table icon select area that you want to include in your tableConditional formattingAllows you to highlight certain cells in excel depending on their value. Can be used to quickly highlight important information in your spreadsheet. Step 1: Highlight cells you wish to formatStep 2: Home—Styles: Conditional Formatting New Rule “Format only cells that contain”Step 3: Select Cell Value, choose condition, insert value, choose format; go to fill and click colorData Validation ToolsDrop-down listUsed to restrict entries to pre-specified items (list). Can make data entry easier and more uniform in excel. Data – Data tools data Validation icon data validation Allow: List, Source: type optionsNOTE: Can add to the list later if needed, just check the box “Apply these changes to all other cells with the same settings”Only numbersUsed to restrict values input into excel to only numbers. Data—Data Tools data validation icon data validation Allow: Whole number OR decimalComment fieldsAllows you to provide a comment on a specific cell within your spreadsheet without typing into the spreadsheet. Useful if multiple people sharing the same spreadsheet to communicate issues, etc. Review – Comments new commentFunctionsSumAverageCountMaxMinAllows you to execute the specified functions on a defined range of cells. Sum: sum defined cellsAverage: average of defined cellsCount: counts the number of cells that contains numbers (within defined range)Max/min: reports the maximum or minimum value from a defined range of numbersPrimary: Formulas—Function Library: can find function categories in library or Insert Function icon dialog box with ALL Excel functionsAlternate: Home Editing ∑ drop down OR Home type in box “= average”Number1/value1: enter range of cells to use in the function Countif Allows you to count items based on ONE set criteria. Formulas—Function Library: Insert Function icon COUNTIF dialog box select range enter criteria in quotesRange: the range of values in which you want to look for the specific criteria (for example a whole column)Criteria: the specific condition that you want to count the number of CountifsSimilar to countif, but allows you to count based on MULTIPLE set criteria. Formulas—Function Library: Insert Function icon COUNTIFS dialog box select range enter criteria in quotesSame as countif, but can specify multiple ranges and criteria.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 boxNOTE: depict cells without values as “” Can embed IF statements within IF statementsLogical_test: enter statement that you wish to evaluate referring to specific cellValue_if_true: enter the value you want to appear if the statement from the logical test is trueValue_if_false: enter the value you want to appear if the statement from the logical test is falseVlookupTool 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 calculatedFormulas—Function Library: Insert Function icon VLOOKUP dialog boxSelect 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 matchStatistical testsMany 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 NOTE: the “data analysis” tool may not be active in your excel (it is an add in). To activate, go to: File options Add-ins select “go” button at bottom select “analysis toolpak”T-testTest for statistical differences in a continuous variable. Formulas—Function Library: Insert Function icon T.TEST dialog boxArray 1 = range of data for group 1Array 2 = range of data for group 2Tails = 1 for one-sided test, 2 for two-sided testType = 1 if paired data, 2 if equal variance, 3 if unequal varianceChi-squareTest for statistical difference in a categorical variable.Can perform in Excel (see class slides), but may be easier on the web. WEB: tablesA pivot table is a tool that allows you to reorganize/summarize specific information from your spreadsheet without actually changing the spreadsheet itself. Very useful to summarize large amounts of data or look for trends in your data. Insert—Tables: PivotTable icon PivotTable1. Select entire table or can specify columns2. Choose location as new or existing worksheet. If choose existing worksheet, highlight the area where you want the table to appear. 3. Drag desired fields to column labels, row labels, and values boxes. Can also use the filter function to sort by another feature within the created pivot table. NOTE: All data selected for the pivot table must have column title for the pivot table to work Filters: can use to filter out parts of the data in the pivot table Columns: groups of interestRows: variables of interestValues: data of interest ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download