Excel - A Beginner’s Guide to Formulas and Functions

1. Introduction
2. Basic Formulas 00:03:36
3. How To Calculate Sales & Profit 00:03:52
4. How To Calculate Total Income 00:07:01
5. How To Calculate Total Profit & Income 00:08:12
6. How To Calculate Total Income 00:10:24
7. How To Calculate Bonus Share 00:10:58
8. Copying Formulas 00:11:49
9. Copying Formulas Down A Column 00:15:45
10. Keeping The Cell Reference Fixed 00:18:05
11. How To Use Dollar Signs In A Formula 00:20:27
12. Functions 00:24:31
13. How to Find A Full List Of Functions 00:29:04
14. Exceljet 00:31:59
15. Percentages 00:33:30
16. The SUM Function 00:37:54
17. The AVERAGE Function 00:45:45:58
18. How To Include Cells That Are Not Linear 00:50:46
19. The COUNT Function 00:52:53
20. Dates 00:55:37
21. The NETWORKDAYS Function 00:58:06
22. Percentages - What-If Analysis 01:06:15
23. Dates 01:09:43
24. Functions And Texts 01:11:14
25. The CONCAT Function 01:14:49
26. Mixing Functions 01:17:28
27. The SUBTOTAL Function 01:24:41
28. VLOOKUP 01:31:40
29. Presenter Closing 01:39:40
30. Presentation Closing 01:40:50

• 

  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

• Absolute Reference 00:20:57
• AVERAGE 00:26:24
• Cell  00:05:23, 00:13:15, 00:20:52, 00:26:47, 00:29:08, 00:42:41, 00:54:24, 01:09:52, 01:31:02
• Cell Reference 00:05:01, 00:19:03
• Column 00:26:02, 00:42:45, 01:03:08, 01:17:34, 01:20:32, 01:39:33
• CONCAT Function 01:14:49
• COUNT 00:52:53, 01:05:57
• COUNTA Function 00:26:36, 01:05:58
• Filter  01:26:19, 01:31:04
• Formula 00:02:24, 00:03:37, 00:05:26, 00:16:06, 00:20:40, 00:52:27, 01:09:24
• Formula Bar 00:05:27, 00:11:49, 00:29:16
• Function 00:22:26, 00:31:00, 00:52:27, 01:11:38
• Fx Button 00:29:10
• LOWER 00:27:47
• NETWORKDAYS 00:28:31, 00:59:39, 01:05:09
• PROPER 00:27:52
• ROMAN 00:28:14
• Row 00:15:23, 00:26:04, 00:42:41, 00:48:11, 01:26:29
• Spreadsheet 00:03:46, 00:14:49
• SUBTOTAL 01:24:41
• SUM 00:25:50, 00:43:00
• TEXT Function 01:21:49
• TODAY Function 00:26:56
• UPPER 00:27:17, 01:13:24
• VLOOKUP 00:27:04, 01:31:40
• What-If Analysis 01:08:46
• XLOOKUP 00:27:07

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.

CONCAT Function: The CONCAT function was introduced in the Office 365 version of Excel 2016. It's not available to users of perpetual licensed versions of Excel 2016 or earlier versions of Excel. This function supersedes the CONCATENATION function and is used to combine multiple pieces of text into one. An alternative to CONCAT and CONCATENATE is using ampersands to join pieces of text together into one.

COUNT: Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers.

COUNTA Function: The COUNTA function returns the number of blank cells within a given range of cells.

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 Reference: A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate. There are three types: Relative, Absolute, and Mixed

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

Filter: The Filter feature in Excel allows you to show or hide rows within a list of data by making selections from drop-down lists. The Filter feature is available on the Data tab of all versions of Excel as well under the Sort & Filter command on the Home menu.

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.

Fx Button: Excel Functions (fx) Excel has prewritten formulas called functions to help simplify making complicated calculations. A function takes a value or values, performs an operation, and returns a result to a cell. The values that you use with a function are called arguments.

LOWER : =LOWER The Microsoft Excel LOWER function converts all letters in the specified string to lowercase. If there are characters in the string that are not letters, they are unaffected by this function. The LOWER function is a built-in function in Excel that is categorized as a String/Text Function.

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.

ROMAN : The ROMAN function in Excel converts a positive integer into its Roman numeral equivalent, represented as text. It's useful for displaying numbers in a traditional or decorative way, such as in outlines or legal documents.

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.

SUBTOTAL: A worksheet function that allows you to sum, average, count, and other otherwise analyze data on just the visible cells within a given range.

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.

Spreadsheet: Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation or computation capabilities, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. Excel forms part of the Microsoft Office suite of software.

TEXT Function: The TEXT function enables you to convert a number in Excel to any number of text formats. For instance, the format code mmmm d, yyyy would transform the date 1/1/2018 into January 1, 2018.

TODAY Function: The TODAY function is useful when you need to have the current date displayed on a worksheet, regardless of when you open the workbook. It is also useful for calculating intervals.

UPPER: =UPPER The Microsoft Excel UPPER function allows you to convert text to all uppercase. The UPPER function is a built-in function in Excel that is categorized as a String/Text Function. It can be used as a worksheet function (WS) in Excel.

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.

What-If Analysis: What-If Analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. Three kinds of What-If Analysis tools come with Excel: Scenarios, Goal Seek, and Data Tables. Scenarios and Data tables take sets of input values and determine possible results.

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.