Outline—NWRA class, January 31, 2022
01_ExcelNavigation
- Configure Excel—Stop autosave, clear default directory, trusted locations, show developer tab in ribbon.
- Keyboard navigation and blocks of data, function keys, F2, F4, F8, F9, F11, and heavily used keyboard shortcuts such as, Ctrl+1.
- Status bar –Quick sampling functions—01_PRISM_4.0km.NV.xlsx.
- Paste special—Values, math, transpose—02_NamedRanges.xlsx.
- Named ranges and data validation—02_NamedRanges.xlsx.
02_Plotting
- Create stacked bar and lines—01_BarLine.xlsx.
- Bar charts differentiate positive & negative—02_Departure.xlsx.
- XY Adding elements to charts –
Copy-paste, Direct entry in formula bar—03_XYmap.xlsx. - Specify labels from ranges—04_FC_isotope_PAIRS.xlsx.
03_Text+Logic_Functions
- Introduce LEFT, VALUE, LEN, FIND, MID, RIGHT, IF, and ISNUMBER functions— 01_TextManipulate.xlsx.
- Introduce SUBSTITUTE, TEXT, and HYPERLINK functions—01_TextManipulate.xlsx.
- If statements and logic— 02_IFplus.xlsx.
Present 03_TransmissivityDV3.xlsx as example of categorizing data.
04_ConditionalFormatting
- Shade PRISM with generic flood—01_PRISM_4.0km.NV.xlsx.
- Controlled shading of PRISM—01_PRISM_4.0km.NV.xlsx.
05_Regression
- Analyze specific conductance-chloride from Death Valley—
Regress in chart and duplicate with equations—01_linearQW.xlsx. - Analyze log-transformed specific capacity-transmissivity relations and
demonstrate limitations of regression equations—
02_Example_T-SC-DeathValley+OtherSCeqs.xlsx.
06_Solver
- Explain weighted sum-of-squares objective functions and parameter transforms necessary for using the Solver add-in— 00_ParameterEstimation+SOLVER.pptx.
- 2-parameter Theis example—01_Theis_Solution+Sensitivity.xlsm.
07_UsingVBAfunctions
- Show examples of user-defined functions and moving modules with functions to new workbooks