Excel 2019/365 Certification Tips

I recently attended the 2021 NBEA Hybrid Convention and wanted to share some of my “takeaways” pertaining to certifying in both the Excel 2019/365 and Excel Expert 2019/365.  I admit that even though I certified in Excel 2019/365 in August 2020, I forgot about some of these items that Chadwick Springer and Carrie Leffler from Auburn University shared.

I also learned about a few new features with Excel such as the formula SWITCH, funnel chart, box and whisker chart, maps, and 3D map. Maps were fun to play with and I’m going to incorporate this into my International Business assignment this year.

Before I list some of my takeaways, I do want to say that if you can ever attend the NBEA convention either face to face or virtually it is worth it!  I hope these items help you and your students excel in the certification test!

Anyone trying to certify in the Excel content must know:

• Manage worksheets and workbooks – import .txt and .csv
• Customize headers/footers
• Freeze and unfreeze row/column
• Change/apply print settings
• Define name ranges
• Name a table
• Remove (or apply) conditional formatting
• Insert relative, absolute, and mixed references
  • Use/know sparklines
  • Create chart sheets
• Need to know formulas:

• • • Count – this is just numbers.
    • CountA – which has letters and/or numbers – any type of information
    • Countblank – counts empty/open cells as well as those with numbers/text

• Format text but using formula:   Upper; Lower; LEN
• Formatting text using Concat – must use Concat not Concatenate. 2016 used Concatenate and in 2019 you will get it wrong if you use this.

• • • Concat…this formula allows last name & first name to be in same cell when they have been entered into separate cells in the spreadsheet.

• TextJoin – ignores empty values for example joining the phone number and extension into one new column. (Use this since some people don’t have an extension.)
  • IFS; MAXIFS

Excel Expert need to know:

• Manage comments
• Configure editing and display language
• Use language-specific features
• Manage and format data
• Group and ungroup
• Calculate data by inserting subtotals and totals
• Remove duplicate records
• Advanced Formulas:
  • IF vs. IFS; Switch; SUMIF; SUMIFS; MAXIFS; MINIFS
  • Calculate dates by using Weekday or Workday
  • Forecasting using IF, AND, NPER
• Macros – record, name, and edit simple macros

Again, I hope this information I learned will help you and your students. One more suggestion, if you don’t already use the Gmetrix platform to help your students prepare, I highly suggest purchasing it.  The skill reviews and practice exams truly have been a game-changer for my student’s success in certifying the first try verses multiple attempts.

Hope everyone has a restful Summer!!!

-Treasa