Excel Tips

​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
  • LEFT, RIGHT, MID – take X characters from the left, middle, or right of a cell
  • 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.