At this week’s after school PD, we learned to use Excel to manipulate text and numbers with formulas and array formulas and look up information/fill in information automatically with VLOOKUP and LOOKUP.
For manipulating text and numbers, use these formulas:
- CONCATENATE, CONCAT, “”&”” – combine two or more cells, strings, or numbers
- AVERAGE, MEDIAN, MODE, SUM
- LEFT, RIGHT, MID – take X characters from the left, middle, or right of a cell
- SUMIF, AVERAGEIF
- SUMIFS, AVERAGEIFS
- UPPER, LOWER – convert a cell to upper or lower case
We also learned about these Excel features:
- Inserting columns and rows
- Filtering and sorting (under the Data tab) to make large spreadsheets easier to read
- Splitting into columns by delimiter use Text to Columns, also under the data tab
- Freeze panes to make a row and/or column always show, even when you scroll right or downHide columns/sheets
- Filling by dragging the square in the bottom-left of a cell
- Dollar sign use in formulas to use an absolute reference to a cell
- When you begin typing a formula, Excel will show a pop-up explaining the syntax you need to follow
Finally, we used VLOOKUP to automatically retrieve information about students based on a key value, such as their student ID number.
Contact Matt directly to get the practice workbook.