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
- adding colors (use the name in the font column)
- adding symbols (like arrow heads)
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
