Performance Evaluation How would performance have been if we were dealing Have to manually drag it across the columns Shortcuts to Paste Formula Downside: won’t do the same horizontally across The cell This pastes the formula down the column, avoids the effort of dragging the formula down across rows Shortcuts to Paste Formula Double click on the square at the lower-right corner of Row (1) from the second reference F2 to illustrate the formula and the references (colored) Result of Fixing Fix the column (A) from the first reference, and the Think About It Focus on any single row: We are traversing through various columns, but want to fix the first term (A4), so fix the column letter (A) Focus on any single column: We are traversing through various rows, but want to fix the second term (E1), so fix the row number (1) Whatever (row and/or column) right after the $ sign isįixed If fixing BOTH row and column, press F4 while cursor is over the reference in the formula editing Created by George ZhaoĪlgorithm: Multiplication Table Multiply the row index by the column index Do this for each cell in the row, and then for each rowĮxample: Multiplication Table Focus again on cell E4: (12 = 3 x 4) All entries on row 4: product of 3 (A4) and _ All entries on column E: product of 4 (E1) and _ 3 9.9 -2 4 -1 #VALUE! 0 0 1 5 2 #VALUE! 3 9 Created by George Zhaoġ-Dimensional Fixed Reference Fix cell reference in cells A1, B2, BB32: $A$1, $B$2,ĭeeper Look into Fixing Reference A1: not fixing column A nor row 1 $A$1: fixing column A and row 1 $A1: fixing ONLY column A, not row 1 A$1: fixing ONLY row 1, not column A Show Formulas Formulas > Show Formulas Toggle on and off between showing / not showing
Where to Start Use fixed reference cellsġ-Dimensional Fixed Reference Example: y = (1+r)*x^2, given a fixed value r r= Paste Special with Shortcut Use arrow to move to cell B1 Can Right Click > Paste Special Or simply Alt + E + S Select Transpose (E)
Control + B for bold Control + C for copy Until an empty cell (or the end of capacity limit of the worksheet) is reached Shift + Control + (Up / Down / Left / Right) arrow all Shift + Control + Arrow Begin by selecting cell B2 Shift + Control + Down arrow to select all elements We need 1, 2, 3, 4, 5 to be filled up on the top row,īeginning in cell B1, going rightward We also want both sets of numbers to be bolded Multiplication Table Task: If given the following on an Excel worksheet,įirst Task: Building the Table Suppose we are only given one side of the table Tutorial 5: Userform Case 7: Subway Data All-Around Analysis Created by George Zhao Case 5: Loop through Data Tutorial 4: Recording Macro Case 6: Select, Pull, Display Case 3: Hello World Tutorial 2: Functions Tutorial 3: Loops and Decisions Case 4: Gradebook Tallying Workshop Resources All here: Lesson material: LearningSlides Exercises – Blank Exercises – Filled Self-assessments: Assignments Solutions Recorded sessions Created by George ZhaoĬontents Overview Case 1: Multiplication Table Case 2: Percentile Calculations Tutorial 1: Variables and Arrays Self-assessments: More relevant case study samples Encouraged to attempt them with resources Solutions will be posted Created by George Zhao Syntax to memorize, this workshop emphasizes case studies, through which the skills are utilized Cases: applicable situational tasks Tutorials: supplemental teaching material to understand Workshop Structure Instead of providing function and programming
Create dynamic algorithms to approach cases When data is changed, but retains its original format, the algorithm should be able to automatically handle the transition appropriately Created by George Zhao Programming, which interacts with the Excel interface No programming background required Goals of the Workshop Learn Excel tools by utilizing them in various cases Tools and materials covered here are merely a sample of Excel functionality Understand the logic and syntax behind Visual Basic
0 kommentar(er)
