Essential Excel Tools, Tips & Tricks

  • Published on

  • View

  • Download


Essential Excel Tools, Tips & Tricks. Nicole Soer Loras College. Why. Example 1 (demographic). Example 2 (financial). Quick Bar. Find a quick average, count or sum of selected cells. Also find maximum and minimum. Printing Column Headings on Each Page. - PowerPoint PPT Presentation


Presentation TitleEssential Excel Tools, Tips & TricksNicole SoerLoras CollegeDupsConvert formulas to #sColumn headings on every page1WhyFor each tool, tip or trick, I would like to explore when we should use this and why we should use this. Please share your experiences with some of these tools as well.2Example 1 (demographic)3Example 2 (financial)Quick BarFind a quick average, count or sum of selected cells. Also find maximum and minimum.Why?5Printing Column Headings on Each PagePage Layout tab > Print Titles buttonWhy?6TablesTables provide a quick way to look at certain attributes like 90 or more creditsWhy?7TablesStudents registered as graduate studentsWhy?8TablesStudents who are NOT graduate students but ARE registered for less than 12 creditsWhy?9Pivot TablesTool with reporting functionality that helps summarize and analyze data10Pivot TablesTool that helps dynamically summarize and analyze large amounts of data11Pivot TablesPivot tables are a powerful tool that gives important information about our data like12Pivot TablesCohort counts and outliers13Pivot TablesBreakdown cohort by state14Pivot TablesDetail in on 09/FA cohort who have SR status and are from MN15Pivot TablesDetermine sum and total average of student loan amounts and count of students16Duplicate EntriesExcel can color code duplicate entries with Conditional Formatting.Why?17Custom SortsHome tab > Sort & Filter > Custom SortSort by cell colorWhy?18Custom SortsSort by multiple fieldsWhy?19Removing DuplicatesTake care with this!Data tab > Remove DuplicatesWhy?20Function BasicsTo use functions, you must have = sign, the function (SUM in this case) and the arguments (F2:L2)Why?21Function BasicsTo enter a function, type it in the cellWhy?22Function Basicsor click the fx button to use the wizardWhy?23Filling in FunctionsClick and drag OR double click the box in the corner of the cell.Why?24Absolute vs. Relative Cell ReferencesAbsolute: the cell address stays same when fillingRelative: the cell address changes when fillingWhy?25VLOOKUPLooks up a set of data from another spreadsheetGrade book90 100 = A80-89 = BFinancial Aid CalculatorGPA 3.5 4.0 = 20,000GPA 3.3 3.49 = 15,000ID look upCombine information from demographic sheet to financial sheetThe first two use a range, the second requires an exact match26VLOOKUPThe first two use a range, the second requires an exact match27VLOOKUPThe first two use a range, the second requires an exact match28IFUsually compares 2 thingsIf something is true, do a calculation or show textIf its not, do this calculation or show textWhy?29CONCATENATECombine cells, text or numbersWhy?30