Rich McCue

Excel Workshop Activities for COM 247

If you have any questions or get stuck, please ask Rich for some help, as you’re probably not the only person with the question.

Some of the links below contain more than one activity (the first link being an excellent example of this). Please complete only the activity described in the description if there are multiple activities on the linked page:

  1. Enter date data into rows & columns: https://uviclibraries.github.io/excel/basics-data-cleaning.html
  2. Name and identify rows & columns: https://smallbusiness.chron.com/give-name-columns-excel-73447.html
  3. Sort data, using the filter button: http://bit.ly/2I3qI6N
  4. Change categorical data (e.g., male, female) into numerical data (e.g., 1, 2) – Scroll down to activity #9: https://uviclibraries.github.io/excel/basics-data-cleaning.html
  5. Use the following functions: SUM, SUMIF, INT, COUNTA, COUNTIF, VLOOKUP, CONCATENATE, VALUE, LEN: https://uviclibraries.github.io/excel/intro-functions.html
  6. Track changes in Excel: https://www.ablebits.com/office-addins-blog/track-changes-excel/
  7. Use these statistical functions: AVERAGE (Mean), Standard Deviation, MEDIAN, MODE, COUNT, Standardize, Sample Variance, Kurtosis, Skew, MIN, MAX, Range: https://uviclibraries.github.io/excel/statistical-functions.html
  8. Produce basic graphs, histograms and scatterplots and edit them (e.g., add title, change axes names, colours, sizing, etc): https://uviclibraries.github.io/excel/charting-graphs.html

If you’ve finished all the above activities before the end of class, I strongly encourage you to tackle these useful and interesting activities as they are very useful skills for data analysis (and you can earn the “Intermediate” DSC Excel badge by doing so):

  1. OPTIONAL – Pivot Tables… a very cool, powerful, & useful tool for summarizing data: https://uviclibraries.github.io/excel/pivot-tables-charts.html
  2. OPTIONAL – Digital Dashboard… Combining Pivot Tables & Charts: https://uviclibraries.github.io/excel/digital-dashboard.html

Live Demonstration:

  • Copy and paste a bunch of rows and columns into another worksheet (maybe the same as #1 on the list above?)
  • With 2 open workbooks, move a tab/worksheet from one workbook to another (they will export a file and then add it to a template workbook – they should understand copying a worksheet/tab versus/and moving a worksheet/tab), move a tab within a workbook, rename a tab
  • Create a new tab/worksheet
  • Set up formulas that cross tabs/worksheets, i.e. link to another sheet using “=”
  • Understand how an “IFS” statement works. They will not need to set one up, but they will use a template that has an IFS function: https://uviclibraries.github.io/excel/intro-functions.html

Leave a Reply