Sharing What I Find

Instructional Design and Technology in Education

Advanced Forumlas (Google Sheets) – 30 day challenge

A group of us are going through a 30-day Advanced Excel challenge. Here’s some things I learned:

Day 8

Match (goes along with Index)

  • this looks at your data and tells you the position of the item you want to match. It doesn’t tell you the row/column, just the position within your search range. I guess you would use this when sorting or using the find isn’t an option.
  • will likely use this most often with wildcards (“*” or “?”)

Day 7

  • Index and count functions to return data information for a summary or data update

Day 6

vLookups are pretty straight forward. Was able to use a named range for the look up table as learned from Day 1.

Day 5

whoa – custom number formats including

or format using Conditional formatting using formulas instead of default options

Day 4

Data Validation using formulas as the criteria

  • unique(filter…) to pull specific information related to a selection — only those options that apply (i.e. select Instructor name and only be given the classes that instructor teaches)
  • countif

Day 3

  • counta – for text entries along with */word/* as wildcard
  • countunique – counts unique values with ability to add additional values (not quite sure when you might use that unless you also have a default something you want to include)

Should be able to use the unique function to select the new to eLearning courses that are added each semester. Our combined list is over 800 courses and sometimes it is hard to tell which course/instructor combination is actually new.

Day 2

  • double click cell handle to copy down the column — so simple — how did I miss this!
  • if / ifs and/or – multiple ways to combine column information
  • sumif and sumifs – calculate a sum based on one or more criteria.

Day 1

  • names ranges – so if the the specific cell position changes, it doesn’t change your function
  • Paste special – values only – if you want to change values from calculated functions to actual numbers

Author: Heidi Olson

Heidi enjoys working with content experts in developing eLearning courses to provide alternatives for students. Her other interests include faculty training in best practices for eLearning and researching eLearning tools to help fulfill learning outcomes. Having worked in the distance education arena for over 20 years, she has a wide range of experiences in supporting students and faculty as technology and pedagogy evolve.

Comments are closed.