XLOOKUP

XLOOKUP allows you to streamline data retrieval from an array, making it easier to find information, and present your work with a polished edge.

Next level → include a dropdown list to reference as the lookup value in your function!

Continue to enhance the professionalism and efficiency of your spreadsheets with the XLOOKUP function. Use the syntax below ↓

=XLOOKUP(lookup value, lookup array, return array)

FILTER

FILTER was added to Excel in 2021 and is so cool because it can return a dynamic array. No SPILL errors here! If the source data is updated, so are your formula outputs. This makes it simpler to find information and present your work with precision.

Continue to enhance the professionalism and efficiency of your spreadsheets with the FILTER function. Use the syntax below ↓

=FILTER(entire array to filter, criteria for what to include, [optional: value you want to show up if no results are found])

SUMIFS

SUMIFS allows you to calculate the sum of data with criteria that you decide upon. This allows me to customize the summary tables of my spreadsheets more than a pivot table. Use one criteria like I did here in this example or create as many as you’d like!

Continue to enhance the professionalism and efficiency of your spreadsheets with the SUMIFS function. Use the syntax below ↓

=SUMIFS(column you want to sum, column you want to evaluate, criteria to look for in the evaluate column, [optional: add as many evaluation and criteria columns])

NETWORKDAYS

NETWORKDAYS allows you to calculate the difference between two dates while only counting the working days. You can even include a list of holidays that aren’t counted.

A couple of my favorite ways to use this function:

• Project timelines
• Automating calendar events for 30/60/90 reviews
• Calculate benefits/bonuses based on # of days worked

Next level → Multiply your function by 8 and you have # of hours worked on those workdays!

Continue to enhance the professionalism and efficiency of your spreadsheets with the NETWORKDAYS function. Use the syntax below ↓

=NETWORKDAYS(start date, end date, [optional: add a list of holidays])

COPILOT BONUS!

• Use Copilot to generate that table for you by using the prompt pictured here
• Copy + Paste that data into your spreadsheet
• Reference the list or table of holidays in your functions

PRO TIP: save this to your Custom Lists so you only have to generate this table once.

TRIM & PROPER

If you’ve ever had a list of names that weren’t uniformly formatted, you know how frustrating it can be to try to fix them manually in Excel. Enter another one of my favorite functions (it’s actually 2 but don’t tell anyone)! TRIM & PROPER

Use TRIM & PROPER nested together to easily fix a list of names that have varying cases and numbers of spaces.

Continue to enhance the professionalism and efficiency of your spreadsheets with the TRIM & PROPER function. Use the syntax below ↓

=TRIM(PROPER(cell you’d like to fix))