Excel Formulas and Functions

Access this expert-led webinar instantly, available anytime on-demand.

Included in All-Access Membership
Live Webinar - no upcoming date
Customer Satisfaction Guarantee Learn with confidence. If you're not happy, we'll make it right. That's our guarantee.

Purchase Options

Select an attendee quantity to add to cart.

Recorded Webinar Only

$219.00
or

All Access Membership

The Aurora All Access Membership is designed to provide you with the training that you want when you want it. You will have 100% access to every live webinar, on demand webinar, professional alert, and podcast that Aurora Training Advantage offers with no additional cost.

Learn More About Our All Access Membership
$599.00
All Access Membership

Excel offers much more than rows, columns, and data entry. If you're not using formulas and functions, you're missing out on some of the most powerful tools available in the application. This 100-minute, hands-on session will introduce you to Excel’s most essential functions using practical, real-world examples. These functions are designed to save you time, reduce the risk of errors, and make your spreadsheets more dynamic and informative.

Whether you're new to Excel or looking to brush up on foundational skills, this session will deepen your understanding of how formulas and functions work. You’ll gain confidence in using LOOKUP functions, automate tasks using logical formulas like IF, and learn to handle text, date, and time data with ease. By breaking down complex functions and showing you how to build them step by step, this webinar equips you with the tools to create smarter, more efficient spreadsheets.

Your Benefits For Attending:
  • Learn the difference between formulas and functions
  • Understand multiple methods to perform a LOOKUP
  • Use functions to manipulate and clean text
  • Automate data entry with the IF function
  • Work with date and time functions more effectively
  • Break down complex functions into manageable parts

This session is perfect for those at a beginner to intermediate level who want to expand their Excel capabilities and become more efficient in their daily work.

Why this webinar is a benefit to attend:
Mastering these core Excel functions will help you streamline your workflow, boost productivity, and increase the accuracy of your spreadsheets—valuable skills for any professional working with data.

Level: Beginner/Intermediate
Format: Live webcast
Instructional Method: Group: Internet-based
NASBA Field of Study: Computer Software & Applications (2 hours)
Program Prerequisites: Prior experience with Microsoft Excel is recommended.
Advance Preparation: None
  1. Introduction
  2. Formula Basics and Cell References 00:01:55
  3. Relative and Absolute Cell References 00:05:11
  4. Named Cells and Ranges 00:08:16
  5. SUM and AVERAGE Functions 00:10:19
  6. SUBTOTAL Function 00:12:34
  7. Using Formulas with Excel Tables 00:14:17
  8. IF Function 00:16:05
  9. IFS Function 00:19:44
  10. IFERROR Function 00:22:37
  11. COUNTIFS Function 00:24:52
  12. Using Multiple Criteria with COUNTIFS 00:27:06
  13. SUMIFS Function 00:29:16
  14. Using Multiple Criteria with SUMIFS 00:31:38
  15. LEFT, RIGHT and MID Functions 00:34:10
  16. TRIM Function 00:36:25
  17. TEXT Function 00:38:53
  18. Text to Columns 00:41:13
  19. TEXTSPLIT Function 00:43:09
  20. TEXTBEFORE Function 00:45:17
  21. TEXTAFTER Function 00:47:01
  22. Lookup Functions 00:50:11
  23. Lookup Functions with Company Data 00:52:45
  24. MATCH Function 00:55:34
  25. INDEX and MATCH 00:58:02
  26. Date and Time Functions 01:05:00
  27. Working with Dates 01:07:05
  28. Database Functions and Criteria 01:10:30
  29. Conditional Calculations 01:15:08
  30. Additional Formula Techniques 01:18:03
  31. Logical Functions and Multiple Conditions 01:21:09
  32. Working with Lists and Criteria 01:27:02
  33. Statistical Functions 01:32:00
  34. Working with Sales Data 01:34:30
  35. Formula Review and Additional Examples 01:39:02
  36. Closing Review 01:41:00 
  • Mike Thomas

    Mike Thomas

    Mike Thomas has worked in the IT training business for 26 years. His expertise and experience covers designing and delivering training courses, creating written training materials (Quick Reference Guides and step-by-step tutorials), recording and editing video-based tutorials and providing support t [...]

    View Full Profile

CPE Credit

Continuing Professional Education

Aurora Training Advantage is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.

For more information regarding administrative policies such as complaint and refund, and cancellation please contact our offices at 407-542-4317 or [email protected].

You must answer all questions during the webinar, view the recording completely and pass the test at the end with 70% correct answers to receive CPE credit.

ATAAA Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in administrative.

ATATX Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in accounting.

ATAOP Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in operations.

Browse previous versions of this webinar series:

  • Excel Formulas and Functions
    Webinar Date: August 27, 2026
    VIEWING
  • Excel Formulas and Functions
    Webinar Date: January 1, 2026
    View
  • Absolute Reference 00:06:23, 00:10:01
  • AVERAGE 00:11:19, 00:11:19, 00:14:39
  • Cell 00:04:16, 00:04:16, 00:04:33, 00:05:12, 00:05:38, 00:05:38, 00:05:38, 00:05:38, 00:05:38, 00:05:38, 00:05:38, 00:05:38, 00:06:48, 00:06:48, 00:06:48, 00:06:48, 00:07:54, 00:07:54, 00:07:54, 00:08:48, 00:11:30, 00:17:07, 00:19:26, 00:19:49, 00:23:33, 00:23:33, 00:32:39, 00:32:57, 00:34:17, 00:36:21, 00:37:19, 00:45:13, 00:47:52, 00:54:00, 00:54:00, 00:55:26, 00:55:26, 00:55:26, 00:56:45, 00:57:29, 00:58:13, 00:59:33, 01:03:11, 01:06:39, 01:06:49, 01:07:16, 01:08:05, 01:15:34, 01:15:34, 01:16:09, 01:16:23, 01:16:23, 01:16:23, 01:16:33, 01:17:40, 01:17:51, 01:19:13, 01:19:22, 01:19:29, 01:19:48, 01:20:13, 01:20:53, 01:21:08, 01:21:08, 01:21:08, 01:21:08, 01:21:51, 01:22:17, 01:22:23, 01:22:41, 01:22:58, 01:23:00, 01:23:04, 01:23:34, 01:23:56, 01:31:49, 01:33:38, 01:33:56, 01:34:22, 01:34:22, 01:38:14, 01:38:20, 01:38:20, 01:38:41, 01:38:44
  • Cell Range 00:23:33, 00:23:33, 00:23:33, 00:26:06, 00:29:19, 00:29:24, 00:29:43, 00:32:35, 00:32:39, 01:03:39, 01:17:40, 01:19:13, 01:20:13, 01:23:56, 01:28:30, 01:30:13, 01:31:03, 01:33:56, 01:36:02, 01:36:02, 01:36:29, 01:37:25
  • Column 00:11:00, 00:17:01, 00:17:16, 00:17:16, 00:17:46, 00:23:33, 00:23:33, 00:23:33, 00:23:33, 00:23:33, 00:23:33, 00:23:33, 00:30:09, 00:33:09, 00:33:09, 00:34:03, 00:35:12, 00:40:35, 00:40:43, 00:40:59, 00:40:59, 00:41:03, 00:41:03, 00:41:13, 00:41:19, 00:41:19, 00:41:27, 00:41:27, 00:42:10, 00:42:10, 00:42:10, 00:44:31, 00:44:39, 00:44:43, 00:47:28, 00:47:52, 00:47:52, 00:47:52, 00:47:52, 00:47:52, 00:47:52, 00:47:52, 00:47:52, 00:47:52, 00:47:52, 00:47:52, 00:47:52, 00:47:52, 00:53:13, 00:53:27, 00:58:03, 01:07:33, 01:21:08, 01:22:05, 01:23:56, 01:24:26, 01:27:57, 01:29:00, 01:29:10, 01:29:18, 01:30:19, 01:30:29, 01:30:29, 01:31:03, 01:31:03, 01:31:37, 01:31:37, 01:33:38, 01:34:04, 01:35:08, 01:35:08, 01:35:46, 01:35:46, 01:35:50, 01:36:11, 01:36:29, 01:36:29, 01:37:26, 01:37:26
  • DAY Function 00:57:29, 01:00:46
  • Dynamic Array Formula 01:20:13, 01:20:51
  • Format 00:56:51, 00:56:51
  • Formula 00:02:01, 00:02:40, 00:03:02, 00:04:01, 00:04:05, 00:04:13, 00:04:16, 00:04:25, 00:04:25, 00:04:33, 00:05:00, 00:05:12, 00:05:12, 00:05:31, 00:05:38, 00:05:38, 00:05:38, 00:05:38, 00:05:38, 00:06:34, 00:06:48, 00:06:48, 00:06:48, 00:06:48, 00:07:33, 00:07:42, 00:07:54, 00:07:54, 00:07:54, 00:07:54, 00:07:54, 00:08:34, 00:09:01, 00:10:01, 00:10:01, 00:10:10, 00:10:10, 00:10:24, 00:10:28, 00:10:44, 00:13:20, 00:14:03, 00:21:20, 00:32:00, 00:32:04, 00:33:32, 00:36:02, 00:41:52, 00:41:52, 00:42:10, 00:42:10, 00:44:31, 00:45:42, 00:45:42, 00:45:42, 00:51:52, 00:53:41, 00:54:58, 00:55:26, 01:03:04, 01:16:13, 01:16:17, 01:16:40, 01:16:40, 01:17:11, 01:17:23, 01:18:33, 01:18:33, 01:18:48, 01:18:48, 01:18:53, 01:19:29, 01:19:29, 01:19:29, 01:20:13, 01:20:13, 01:20:13, 01:20:13, 01:20:13, 01:20:13, 01:20:13, 01:20:51, 01:22:53, 01:23:04, 01:23:04, 01:23:56, 01:24:26, 01:28:23, 01:29:44, 01:29:44, 01:29:44, 01:31:49
  • Formula Bar 00:14:03, 01:19:29
  • Function 00:02:01, 00:02:01, 00:02:01, 00:02:01, 00:02:40, 00:10:44, 00:10:52, 00:10:52, 00:11:00, 00:11:00, 00:11:00, 00:11:19, 00:11:30, 00:11:43, 00:11:57, 00:12:01, 00:12:09, 00:12:09, 00:12:09, 00:12:09, 00:12:09, 00:12:09, 00:13:08, 00:13:08, 00:13:08, 00:13:20, 00:13:26, 00:13:26, 00:13:35, 00:14:03, 00:14:03, 00:14:31, 00:14:39, 00:14:54, 00:15:24, 00:15:36, 00:15:53, 00:15:53, 00:15:53, 00:16:29, 00:17:59, 00:18:06, 00:18:11, 00:18:11, 00:18:11, 00:21:20, 00:21:43, 00:21:47, 00:23:20, 00:25:06, 00:25:06, 00:25:35, 00:25:35, 00:25:43, 00:28:56, 00:29:43, 00:30:41, 00:36:02, 00:37:13, 00:39:37, 00:39:41, 00:51:52, 00:53:41, 00:54:49, 00:54:49, 00:55:05, 00:55:26, 00:55:26, 00:56:36, 00:56:41, 00:57:29, 00:57:40, 00:57:40, 00:57:54, 00:58:13, 00:58:13, 00:58:13, 00:58:13, 00:58:13, 00:59:09, 01:00:46, 01:00:46, 01:00:46, 01:00:46, 01:00:53, 01:02:26, 01:02:33, 01:03:29, 01:03:39, 01:03:39, 01:03:39, 01:05:00, 01:05:00, 01:05:29, 01:05:29, 01:05:29, 01:05:29, 01:08:27, 01:09:24, 01:09:24, 01:09:24, 01:11:01, 01:11:20, 01:11:20, 01:11:26, 01:11:35, 01:13:20, 01:13:20, 01:13:20, 01:15:08, 01:15:34, 01:15:34, 01:16:40, 01:17:11, 01:17:40, 01:23:56, 01:23:56, 01:26:51, 01:26:51, 01:27:08, 01:27:08, 01:29:00, 01:29:18, 01:29:30, 01:29:44, 01:30:29, 01:32:37, 01:32:50, 01:32:50, 01:32:50, 01:32:50, 01:33:25, 01:33:33, 01:36:57, 01:37:20, 01:37:26
  • HLOOKUP 00:53:02, 00:53:20, 00:53:27, 00:54:31
  • IF Function 00:15:53, 00:15:53, 00:16:29, 00:17:59, 00:18:11, 00:18:11, 00:21:43
  • LEFT Function 01:09:24, 01:10:03, 01:10:46, 01:11:01, 01:11:20, 01:15:18
  • Logical Test 00:18:11, 00:18:52, 00:19:49
  • LOOKUP 00:02:01, 00:13:35, 00:13:35, 00:13:35, 00:13:54, 00:31:49, 00:31:51, 00:36:02, 00:36:21, 00:36:21, 00:36:21, 00:37:13, 00:37:19, 00:37:19, 00:37:43, 00:39:17, 00:39:17, 00:39:26, 00:39:26, 00:39:37, 00:39:37, 00:39:52, 00:39:52, 00:40:12, 00:40:12, 00:40:18, 00:40:28, 00:41:27, 00:42:10, 00:42:10, 00:47:20, 00:47:20, 00:47:28, 00:47:52, 00:47:52, 00:47:52, 00:47:52, 00:47:52, 00:47:52, 00:51:21, 00:51:21, 00:51:52, 00:51:52, 00:51:52, 00:51:52, 00:51:52, 00:51:52, 00:53:02, 00:53:09, 00:53:09, 00:53:13, 00:53:20, 00:53:20, 00:53:20, 00:54:31, 00:54:31, 00:54:31, 01:07:57, 01:07:57, 01:35:02, 01:35:08, 01:35:46, 01:37:26
  • MID Function 01:09:24, 01:10:03, 01:11:20, 01:11:35, 01:11:35, 01:15:18
  • MONTH Function 00:57:40, 01:00:46
  • NETWORKDAYS 00:11:43, 00:55:15, 01:00:53, 01:02:26, 01:03:29, 01:03:39, 01:03:39
  • Parameter 00:18:11, 00:18:11, 00:18:11, 00:19:49, 00:23:20, 00:23:33, 00:23:33, 00:23:33, 00:23:33, 00:25:19, 00:39:41, 00:40:00, 00:46:03, 00:46:03, 00:46:03, 00:58:13, 01:03:39, 01:27:57, 01:30:25, 01:36:26
  • Paste Special 00:32:57, 00:35:12, 00:35:30
  • RIGHT Function 01:09:24, 01:10:03, 01:11:20, 01:11:26, 01:11:35, 01:15:18
  • Row 00:05:38, 00:07:42, 00:11:00, 00:34:03, 00:40:43, 00:40:43, 00:40:43, 00:53:13, 00:53:27, 00:54:00, 01:29:00, 01:29:10, 01:29:18, 01:30:19, 01:30:25, 01:31:00, 01:31:00, 01:31:03, 01:31:03, 01:31:03, 01:31:49, 01:33:38, 01:34:04, 01:36:11, 01:36:11, 01:36:11, 01:36:26, 01:36:50, 01:37:26
  • SPILL Error 00:55:26, 00:55:26, 00:55:26, 00:55:26, 00:55:26, 00:55:26, 01:14:45, 01:21:57, 01:22:05, 01:22:14, 01:22:17, 01:22:21, 01:22:23, 01:22:25, 01:22:27, 01:23:04, 01:23:04, 01:23:04, 01:23:48, 01:23:56, 01:23:56, 01:24:26, 01:24:39
  • SUM 00:11:00, 00:11:00, 00:23:09, 00:26:06, 00:30:41, 00:30:41
  • SUMIF / SUMIFS 00:22:02, 00:22:02, 00:22:02, 00:22:02, 00:23:16, 00:23:20, 00:24:31, 00:28:56, 00:29:04, 00:29:58, 00:30:09, 00:32:13
  • Transpose 00:15:24, 00:34:17, 00:47:52
  • TRIM 00:12:01, 01:05:00, 01:05:29, 01:08:27, 01:08:31, 01:09:24, 01:14:57, 01:15:08, 01:15:18, 01:15:23, 01:17:23, 01:22:41, 01:23:04
  • VLOOKUP 00:13:35, 00:13:35, 00:13:54, 00:37:19, 00:37:30, 00:39:17, 00:40:31, 00:40:35, 00:41:27, 00:42:10, 00:44:12, 00:45:29, 00:47:28, 00:47:52, 00:47:52, 00:47:52, 00:51:52, 00:53:09, 00:53:20, 00:53:27, 00:54:31, 01:34:11, 01:37:26
  • XLOOKUP 00:36:21, 00:36:21, 00:37:13, 00:37:19, 00:37:30, 00:39:17, 00:39:26, 00:39:26, 00:39:37, 00:39:37, 00:39:41, 00:40:31, 00:40:43, 00:42:10, 00:44:12, 00:45:29, 00:47:52, 00:47:52, 00:51:52, 00:53:09, 00:53:13, 00:53:20, 00:53:27, 00:53:49, 00:54:31, 01:07:16, 01:07:33, 01:35:02, 01:35:08, 01:35:18, 01:37:26
  • YEAR Function 00:57:40, 00:58:13, 01:00:46

AVERAGE : Returns the average (arithmetic mean) of the arguments.

Absolute Reference : Absolute references in Excel are a direct link to a specific cell or range of cells that remain fixed if you copy or drag the formula. Absolute references are represented by $ symbols. A $ before a column letter freezes the column, while a $ before the row number freezes the row number. You can freeze the column letter and/or row number when needed.

Cell: In spreadsheet applications, a cell is a box in which you can enter a single piece of data. The data is usually text, a numeric value, or a formula. The entire spreadsheet is composed of rows and columns of cells.

Cell Range: A group of cells is known as a cell range. Rather than a single cell address, you will refer to a cell range using the cell addresses of the first and last cells in the cell range, separated by a colon. For example, a cell range that included cells A1, A2, A3, A4, and A5 would be written as A1:A5.

Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.

DAY Function: The DAY function in Microsoft Excel extracts the day of the month as an integer from 1 to 31 based on a given date.

Dynamic Array Function: Dynamic Arrays will make certain formulas much easier to write. You can now filter matching data, sort, and extract unique values easily with formulas. Dynamic Array formulas can be chained (nested) to do things like filter and sort. Formulas that return more than one value will automatically spill.

Format: When we format cells in Excel, we change the appearance of a number without changing the number itself. We can apply a number format (0.8, $0.80, 80%, etc) or other formatting (alignment, font, border, etc). By default, Excel uses the General format (no specific number format) for numbers.

Formula: A formula is an expression which calculates the value of a cell.

Formula Bar: A toolbar at the top of the Microsoft Excel spreadsheet window that you can use to enter or copy an existing formula into cells or charts. It is labeled with function symbol (fx). By clicking the Formula Bar, or when you type an equal (=) symbol in a cell, the Formula Bar will activate.

Function: Functions are predefined formulas and are already available in Excel.

HLOOKUP: HLOOKUP is an Excel function to lookup and retrieve data from a specific row in table. The "H" in HLOOKUP stands for "horizontal", where lookup values appear in the first row of the table, moving horizontally to the right. HLOOKUP supports approximate and exact matching, and wildcards (* ?) for finding partial matches.

IF Function: Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false. So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.

LEFT Function: The Microsoft Excel LEFT function is a function which allows you to extract a substring from a string and starts from the leftmost character. This is a built-in function in excel which has been categorized as a String/Text Function.

LOOKUP: The Microsoft Excel LOOKUP function returns a value from a range (one row or one column) or from an array. The LOOKUP function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function (WS) in Excel.

Logical Test: A logical test in Excel is a condition or expression that evaluates to either TRUE or FALSE, typically built using the IF function and comparative operators.

MID Function: The Excel MID function extracts a given number of characters from the middle of a supplied text string. For example, =MID("apple",2,3) returns "ppl". Extract text from inside a string. The characters extracted. =MID (text, start_num, num_chars)

MONTH Function: The MONTH function in Microsoft Excel extracts the month number from a date as an integer from 1 (January) to 12 (December)

NETWORKDAYS: Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.

Parameter: A parameter is a piece of information you supply to a query right as you run it. Parameters can be used by themselves or as part of a larger expression to form a criterion in the query. You can add parameters to any of the following types of queries: Select. Crosstab.

Paste Special : Paste special is a common feature in productivity software such as Microsoft Office and OpenOffice. It is very commonly used in Word, Excel, Writer, and Calc to provide special formatting or calculations when pasting content into a document. If you want to paste only a specific aspect of the copied data like its formatting or value, you would use one of the Paste Special options.

RIGHT Function : The Excel RIGHT function extracts a given number of characters from the right side of a supplied text string.

Row: A row is the range of cells that go across (horizontal) the spreadsheet/worksheet. Rows are identified by numbers e.g. row 1, row 5. Examples of use. A row might contain the headings of a table e.g. product ID, product name, price, number sold.

SPILL Error: The #SPILL! error in Excel happens when a dynamic array formula tries to output results into cells that are already blocked or occupied.

SUM: Microsoft Excel defines SUM as a formula that “Adds all the numbers in a range of cells”. This definition clearly points that Sum function has a job to add numbers and the arguments can be supplied using combinations of both numbers and range of cells. =SUM The SUM function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUM function can be entered as part of a formula in a cell of a worksheet

SUMIF: A look-up function in Excel that allows you to add up numbers based upon a criterion that you specify. Unlike VLOOKUP, the SUMIF function can add up two or more values and returns zero (instead of #N/A) if no match is found.

SUMIFS: A look-up function in Excel that allows you to add up numbers based upon up to 127 criteria that you specify. Unlike VLOOKUP, the SUMIFS function can add up two or more values and returns zero (instead of #N/A) if no match is found.

TRANSPOSE: The Microsoft Excel TRANSPOSE function returns a transposed range of cells. For example, a horizontal range of cells is returned if a vertical range is entered as a parameter. Or a vertical range of cells is returned if a horizontal range of cells is entered as a parameter.

TRIM Function : The TRIM function removes extraneous spaces from a cell or string of text once space is kept between each word.

VLOOKUP: An Excel worksheet function that allows you to look up data from a list by specifying criteria, cell coordinates for the list, column number from which to return data, and an indication as to whether you want an exact or approximate match.

XLOOKUP: The XLOOKUP function searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. Where a valid match is not found, return the [if_not_found] text you supply.

YEAR Function: Returns the year corresponding to a date.

Customer Satisfaction Guarantee
Invest in your future with confidence! Our Customer Satisfaction Guarantee eliminates all risk, letting you focus purely on mastering new skills and advancing your career. If you're not completely satisfied, we'll ensure you are. Your satisfaction is not just a promise; it's our guarantee.

Frequently Asked Questions

In Excel, a formula is any expression that begins with an equals sign (=) and performs a calculation to produce a result—for example, =A1+B1 adds two cells. A function is a predefined formula built into Excel that takes specific inputs (called arguments) and performs a standardized calculation—for example, =SUM(A1:A10) uses the SUM function. Functions are essentially shortcuts that replace complex manual formulas. You can combine functions and operators within a single formula—for example, =(SUM(A1:A10)/COUNT(A1:A10))*1.1 uses two functions together with arithmetic. Excel provides hundreds of functions organized into categories: Math & Trig, Statistical, Logical, Text, Lookup & Reference, Date & Time, Financial, and more. The fx button next to the formula bar opens the Function Wizard, where you can browse and search all available functions. Understanding the distinction between formulas and functions is the first step to building powerful, reliable spreadsheets. Mike Thomas covers this foundational concept in Aurora Training Advantage's Excel Formulas and Functions webinar.
Cell references in Excel formulas can be relative, absolute, or mixed, and understanding the difference is essential for correctly copying formulas across cells. A relative reference (e.g., A1) adjusts automatically when the formula is copied to another location—copying a formula one row down changes A1 to A2. An absolute reference (e.g., $A$1) remains fixed regardless of where the formula is copied—the dollar signs lock both the column letter and row number. A mixed reference locks either the column ($A1) or the row (A$1) but not both. Absolute references are essential when a formula refers to a fixed lookup table, a tax rate cell, or any constant value that all rows should reference. Press F4 while the cursor is on a cell reference in the formula bar to cycle through relative, absolute, and mixed modes. Getting references wrong is one of the most common sources of spreadsheet errors. Mike Thomas demonstrates relative and absolute references with practical copying examples in Aurora Training Advantage's Excel Formulas and Functions webinar.
Excel's text functions are invaluable for cleaning imported data and standardizing text formatting. LEFT(text, num_chars) extracts characters from the start of a string; RIGHT(text, num_chars) extracts from the end; MID(text, start, num_chars) extracts from the middle. LEN(text) returns the total character count, useful in combination with other functions. UPPER, LOWER, and PROPER convert text to all uppercase, all lowercase, or Title Case respectively. CONCATENATE (or the & operator and CONCAT in newer versions) joins multiple text strings into one. TRIM removes leading, trailing, and extra internal spaces—critical for fixing imported data where invisible spaces cause lookup mismatches. The Text to Columns wizard splits delimited data into separate columns without formulas. For Excel 365 users, TEXTBEFORE, TEXTAFTER, and TEXTSPLIT provide powerful new ways to parse strings. These functions together form a complete toolkit for data cleaning. Mike Thomas covers key text functions with real-world examples in Aurora Training Advantage's Excel Formulas and Functions webinar.
The IF function is Excel's most fundamental logic tool, enabling formulas to make decisions based on conditions. Its syntax is =IF(logical_test, value_if_true, value_if_false). For example, =IF(Score>=70,"Pass","Fail") returns Pass when the score is 70 or above and Fail otherwise. IF functions can be nested to handle multiple conditions—though for many conditions, IFS or SWITCH functions in Excel 2019 and later are more readable. Common uses include calculating bonuses (IF sales exceed target), flagging overdue items (IF days outstanding exceeds threshold), auto-populating status labels, and applying tiered pricing rules. Combining IF with AND or OR logical functions handles compound conditions: =IF(AND(Region="North",Sales>1000),"Eligible","Not Eligible"). The IF function effectively replaces manual judgment calls with automated, consistent formula logic—reducing errors and saving time in data processing workflows. Mike Thomas teaches the IF function alongside SUMIF in Aurora Training Advantage's Excel Formulas and Functions webinar, building from basic logic to practical automation.
Excel stores dates as serial numbers (days since January 1, 1900) and times as decimal fractions of a day, enabling arithmetic on both. Adding and subtracting dates calculates durations—for a timesheet, =End_Time - Start_Time gives the worked hours as a decimal, which you format as [h]:mm to display correctly. The TODAY() and NOW() functions return the current date and time dynamically. NETWORKDAYS(start_date, end_date) calculates working days between two dates, excluding weekends and optionally holidays. DATE(year, month, day), YEAR(), MONTH(), and DAY() extract or construct date components for scheduling formulas. EOMONTH(date, months) returns the last day of a month a specified number of months away—useful for billing cycles. A common pitfall is time values exceeding 24 hours: the standard time format caps at 24 hours, while the [h]:mm format displays accumulated hours correctly. Mike Thomas demonstrates date and time arithmetic for timesheets in Aurora Training Advantage's Excel Formulas and Functions webinar.