This document is a tutorial on Microsoft Excel, covering fundamental and advanced features. It explains basic Excel operations like formatting, saving files, and creating custom lists, then moves on to formulas, functions (including the “Big Six”), and order of operations. The tutorial also explores advanced concepts such as absolute and relative referencing, named ranges, Excel Tables, and data manipulation techniques including sorting, filtering, and using Power Query to combine data from multiple files. Finally, it shows how to handle errors and use conditional formatting to enhance spreadsheets.

Excel Study Guide

Quiz

1. What is the advantage of saving an Excel template to the custom templates folder? Saving to the custom templates folder allows you to easily access your template from File > New under the personal tab, which makes it convenient to create new files based on that template.
2. What does ‘hardcoding’ mean in the context of Excel formulas and why should it be avoided? Hardcoding refers to directly typing numbers into a formula instead of using cell references and it should be avoided because if the original numbers change, hardcoded formulas won’t update automatically.
3. What is the difference between functions and formulas in Excel? Functions are built-in operations in Excel, while a formula is an expression that performs a calculation. Formulas can include one or more functions and other operations.
4. What is the BODMAS/PEMDAS rule and why is it important when using Excel formulas? BODMAS (Brackets, Orders, Division, Multiplication, Addition, Subtraction) or PEMDAS (Parentheses, Exponents, Multiplication, Division, Addition, Subtraction) is a rule outlining the order of operations in a calculation, and it is crucial because Excel uses this rule when evaluating formulas.
5. What does the SUM function do, and how can cell ranges be used within it? The SUM function adds up a range of numbers and instead of adding individual numbers, cell ranges, like C6:C25, can be used to easily add all the values in those cells to produce a total.
6. How do the COUNT and COUNTA functions differ? The COUNT function only counts cells containing numeric data, while COUNTA counts cells that contain any type of data—numbers or text.
7. Briefly explain what the MIN and MAX functions do. The MIN function returns the smallest numeric value in a given range of cells, whereas the MAX function returns the largest value.
8. Why is it useful to use cell styles in Excel? Cell styles help improve the readability of spreadsheets and create consistency, particularly when sharing with colleagues, by allowing you to highlight cells for calculations, inputs, titles, and other uses.
9. How does data validation with a drop-down list help ensure data accuracy? Data validation with a drop-down list allows users to select from a pre-defined list, which prevents spelling errors and other input mistakes, thus ensuring consistency.
10. What is a nested IF statement? A nested IF statement is one or more IF statements used inside another IF statement, which allows you to perform more complex conditional tests and actions.

Answer Key

1. Saving to the custom templates folder allows you to easily access your template from File > New under the personal tab, which makes it convenient to create new files based on that template.
2. Hardcoding refers to directly typing numbers into a formula instead of using cell references and it should be avoided because if the original numbers change, hardcoded formulas won’t update automatically.
3. Functions are built-in operations in Excel, while a formula is an expression that performs a calculation. Formulas can include one or more functions and other operations.
4. BODMAS (Brackets, Orders, Division, Multiplication, Addition, Subtraction) or PEMDAS (Parentheses, Exponents, Multiplication, Division, Addition, Subtraction) is a rule outlining the order of operations in a calculation, and it is crucial because Excel uses this rule when evaluating formulas.
5. The SUM function adds up a range of numbers and instead of adding individual numbers, cell ranges, like C6:C25, can be used to easily add all the values in those cells to produce a total.
6. The COUNT function only counts cells containing numeric data, while COUNTA counts cells that contain any type of data—numbers or text.
7. The MIN function returns the smallest numeric value in a given range of cells, whereas the MAX function returns the largest value.
8. Cell styles help improve the readability of spreadsheets and create consistency, particularly when sharing with colleagues, by allowing you to highlight cells for calculations, inputs, titles, and other uses.
9. Data validation with a drop-down list allows users to select from a pre-defined list, which prevents spelling errors and other input mistakes, thus ensuring consistency.
10. A nested IF statement is one or more IF statements used inside another IF statement, which allows you to perform more complex conditional tests and actions.

Essay Questions

1. Discuss the importance of data validation in Excel and provide specific examples of how it can be used to ensure accuracy and consistency in a spreadsheet.
2. Compare and contrast the use of nested IF statements with the IFS function in Excel. Discuss situations where one may be more beneficial than the other.
3. Explain how you can use logical functions like AND, OR, and NOT, in combination with other Excel formulas, and describe their impact on data analysis.
4. Discuss the differences between the COUNTIF, SUMIF, AVERAGEIF and the COUNTIFS, SUMIFS, AVERAGEIFS functions, and provide examples of scenarios where you might choose one over the other.
5. Explore the use of dynamic array functions in Excel and discuss how functions like UNIQUE, SORT, and FILTER can improve the analysis and presentation of data, compared to older approaches.

Glossary of Key Terms

Cell Reference: A reference to a cell or a range of cells on a worksheet that can be used in a formula.

Hardcoding: Directly typing numbers or text into a formula instead of using cell references.

Function: A built-in operation in Excel that performs a specific task, such as SUM, AVERAGE, or COUNT.

Formula: An expression in Excel that calculates a value, often using functions, cell references, and operators.

BODMAS/PEMDAS: The order of operations: Brackets, Orders, Division, Multiplication, Addition, Subtraction or Parentheses, Exponents, Multiplication, Division, Addition, Subtraction.

Cell Range: A group of two or more cells on a worksheet, usually specified by the first and last cell (e.g., A1:A10).

Cell Style: A predefined set of formatting attributes that can be applied to cells, for example a particular font size, border, and background color.

Data Validation: A feature that allows you to control the type of data that can be entered into a cell using lists, numbers, and other options.

Dynamic Array: A formula that returns results that spill into multiple cells, automatically updating as data changes (e.g., UNIQUE, SORT).

Nested IF Statement: One or more IF statements used within another IF statement to allow for complex conditional logic.

Logical Functions: Functions that perform tests and return a TRUE or FALSE result, (e.g., AND, OR, NOT).

Conditional IFs: Functions that perform calculations only if specific conditions are met, (e.g., COUNTIFS, SUMIFS, AVERAGEIFS).

Error Handling: Using functions to manage and correct errors in formulas (e.g., IFERROR, IFNA).

Array: A collection of data (values, text, etc.) that can be used in formulas.

Mean: The average value of a set of numbers.

Median: The middle value in a set of numbers when they are ordered.

Mode: The value that appears most frequently in a set of numbers.

Rounding: Adjusting the value of a number to a specified number of decimal places or nearest whole number.

Custom Formatting: Formatting that allows users to control how a value is displayed (e.g., currency, dates).

Variable: In formulas, a named entity that acts as a placeholder for value, range or text string.

Lambda: An Excel feature that allows users to create their own custom, reusable functions.

Advanced Excel Techniques

Okay, here’s a detailed briefing document summarizing the main themes and important ideas from the provided Excel training transcript:

Briefing Document: Advanced Excel Techniques

Overview:

This document summarizes key concepts and techniques from an extensive Excel training resource, focusing on advanced formulas, data manipulation, and automation. The training covers topics ranging from fundamental formula principles to sophisticated functions, custom formatting, and data analysis tools.

Key Themes and Concepts:

1. Mastering Formulas:

• Formula Fundamentals:
• The training emphasizes the crucial distinction between functions (pre-built tools) and formulas (expressions using functions and operators).
• It highlights the importance of using cell references (e.g., A1) instead of hardcoding numbers to ensure dynamic updates when data changes.
• Order of Operations (BODMAS/PIDMAS): The importance of understanding the order of operations (Brackets, Orders/Indices, Division, Multiplication, Addition, Subtraction) is explained using examples, highlighting how Excel follows this rule.
• Common Functions:
• The training introduces six core functions: SUM, COUNT, COUNTA, AVERAGE, MIN, and MAX.
• It emphasizes COUNT only works with numeric values while COUNTA counts text and numbers, showcasing their differences.
• Logical Functions:
• IF Statements: The core functionality of IF statements is explained, allowing for meaningful outputs based on logical tests, for example returning “yes” or “no” based on data.
• AND, OR: The training details how these function enable evaluating multiple logical tests using AND (both conditions must be true) or OR (at least one condition must be true).
• Nested IF Statements: It demonstrates how IF statements can be nested to handle multiple conditions and output appropriate results.
• IFS Function: It shows how the IFS function can be used as a more streamlined and modern alternative to nested IF statements, simplifying complex logical checks.
• Conditional Aggregations:
• The training explores COUNTIFS, SUMIFS, and AVERAGEIFS, which are powerful tools for performing calculations based on multiple criteria.
• Error Handling
• IFNA and IFERROR: The training illustrates using these functions to deal with errors in formulas and output blank cells if an error occurs.

1. Data Manipulation and Control:

• Data Validation: The training demonstrates using data validation drop-down lists to control input and prevent data entry errors.
• Quote: “…the method that I would use to to ensure that people are inputting the correct names every single time is to use a data validation drop-down list…”
• Cell Styles: The use of cell styles to improve spreadsheet readability is explained.
• Quote: ” …cell styles to improve the readability of your spreadsheets…particularly if you’re going to be sharing your spreadsheets with colleagues or other people…”
• Filter Function: The function is explored as a method for filtering data and outputting results in the spreadsheet.
• Quote: “The Filter function allows us to filter data sets in our worksheet and output results.”
• It demonstrates the use of AND, OR, and equals operators within a filter, providing versatile filtering options.

1. Advanced Data Analysis and Extraction:

• UNIQUE Function: The training emphasizes that this function is used to extract a unique list of items from a column. The lesson goes into more detail about it’s two key operations, distinct and unique and clarifies that distinct is the default of the function. It also highlights the functions ability to select rows or columns.
• SORT and SORTBY Functions: It demonstrates how to sort a data set or a single column using these functions.
• LARGE and SMALL Functions: These functions are shown to extract the largest or smallest values from a dataset based on given parameters.
• RANK.EQ and RANK.AVG Functions: The use of these functions are detailed and are shown to be effective when ranking data.
• MODE.MULT and MODE.SNGL Functions The lesson explains the use of these functions, clarifying the difference between them and when it is best to use either.
• SUBTOTAL and AGGREGATE Functions The use of these functions and their importance are explored. The key difference between them is explained, focusing on AGGREGATE and it’s ability to ignore errors.

1. Statistical Functions and Rounding

• The training touches on the fundamentals of statistical analysis, focusing on AVERAGE, MEDIAN and MODE.
• Rounding Functions: It covers ROUND, ROUNDUP, and ROUNDDOWN functions for general rounding, along with MROUND for rounding to multiples and CEILING and FLOOR for always rounding up or down.

1. Custom Formatting * The course touches on the power of custom formatting, highlighting it’s use in manipulating the look of numbers and text in the worksheet.
2. LET and LAMBDA Functions:

• LET: The training highlights the LET function’s ability to declare variables within a formula, improving readability and efficiency. It provides an example using a complex file name extraction calculation.
• Quote: “The LET function can simplify complex calculations in your worksheets by assigning names to calculation results or ranges.”
• LAMBDA: It demonstrates how LAMBDA can create reusable custom functions with named parameters, that you can use as you would any other Excel formula.
• Quote: “Lambda allows us to create our own functions that we can reuse throughout the workbook.”

1. Pivot Tables with Multiple Data Sources * The training highlights how to create pivot tables using data from multiple files, highlighting its use in analyzing larger data sets.

Key Quotes:

• “Formulas are the backbone of excel.”
• “The number one rule of creating formulas is to always make sure that wherever possible… you use the cell reference as opposed to hardcoding the number.”

Important Ideas/Facts:

• Excel provides a vast array of built-in functions categorized in the ‘Formulas’ tab.
• Dynamic array functions (like UNIQUE, SORT, FILTER) output results that automatically adjust based on the source data, impacting multiple cells.
• Custom formatting allows for extremely granular control over the display of numbers and text.
• LET and LAMBDA functions provide tools to enhance formula readability, efficiency, and reusability.

Conclusion:

This training material offers a comprehensive look at advanced Excel techniques. It covers core and more complex formulas, data control and error handling, and enhanced analysis tools, showcasing a holistic approach to improving Excel skills. This training aims to empower users to effectively manage, analyze, and automate their data using Excel.

Mastering Microsoft Excel

Excel FAQ

1. Why is it advantageous to save a template file in the custom templates folder? Saving a template file in the custom templates folder makes it easily accessible when creating a new file. Instead of navigating through different folders, you can go to File > New and find your template under the Personal tab, allowing for quick creation of new files based on that template. This is different from saving a template in a personal folder.

2. How do you create a custom list in Excel, and why is it useful? To create a custom list, go to File > Options > Advanced and scroll down to the Edit Custom Lists button. You can import a list from selected cells within your worksheet. Custom lists are useful for auto-filling cells with predefined sequences, like names of students or months of the year, by simply typing the first entry and dragging the autofill handle, saving you from typing the entire list each time.

3. What is the difference between a formula and a function in Excel? Formulas are calculations or expressions that perform operations in Excel. Functions are pre-built operations that are used within a formula to perform specific tasks, like SUM, AVERAGE, or IF. Functions are tools you use to build a formula.

4. What is the BODMAS/PEMDAS rule and why is it important in Excel? BODMAS (Brackets, Orders, Division, Multiplication, Addition, Subtraction) or PEMDAS (Parentheses, Exponents, Multiplication, Division, Addition, Subtraction) represents the order of operations that Excel follows when evaluating a formula. This rule ensures that calculations are performed in the correct sequence, giving you the accurate result you expect by prioritizing brackets (parentheses) first, followed by exponents, then division and multiplication (from left to right), and finally, addition and subtraction (from left to right).

5. What is the difference between COUNT and COUNTA functions in Excel? The COUNT function only counts cells that contain numerical data, while the COUNTA function counts all cells that are not empty, whether they contain numbers, text, dates, or other values. Thus, you would use COUNTA to count a range of text and number entries and COUNT only when a range contains purely numerical values.

6. How can cell styles improve the readability and usability of Excel spreadsheets, and how can these be applied? Cell styles enable you to apply a set of formatting options (font, color, number format, etc.) to cells with one click, enhancing readability and ensuring consistency. Cell styles can be applied to create headings, input cells, calculation cells and more. By creating distinct styles, users can easily understand the purpose of each cell (e.g., input cells have a particular color, while calculated cells are locked) and easily make changes in a consistent manner. Additionally, using styles allows you to automatically copy cell styles when adding new rows or columns.

7. How can Data Validation be used to prevent data entry errors? Data Validation allows you to restrict the type of data that can be entered into a cell, preventing errors and ensuring consistency. You can create drop-down lists to ensure people select from a predefined list of values (such as employee names) and you can also add restrictions on number or date format. This reduces spelling errors, data inconsistencies, and the chance that a formula will not run properly as it is relying on incorrect data.

8. How do IF, AND, OR, and nested IF statements work in Excel, and what are some practical uses?

• IF statements: Evaluate a logical test and return one value if true and another if false. They are used to apply logic to a cell’s content.
• AND function: Tests multiple conditions and returns TRUE only if all conditions are true.
• OR function: Tests multiple conditions and returns TRUE if at least one condition is true.
• Nested IF statements: Embed IF statements inside other IF statements, allowing for more complex, multi-layered logical evaluations, where multiple criteria require varying outcomes. These functions are crucial for performing conditional calculations and actions based on data in your spreadsheets, allowing for complex decision-making within formulas.

Microsoft Excel 365: A Comprehensive Guide

Microsoft Excel 365 is an updated version of Excel that is part of the Microsoft 365 subscription service [1]. It is an evergreen version, which means users always have the latest version with the newest features, without needing to purchase a new version [1].

Key aspects of Excel include:

• Online Access: Excel 365 can be accessed via an online portal using any browser with an internet connection, allowing users to work on files from any location [1].
• Interface:The Excel interface includes a start page that appears when the application is first opened, where users can create a new blank workbook, use a template, or access recent or pinned documents [2].
• The main interface contains a title bar, tabs and ribbons, a quick access toolbar, a name box, a formula bar, and the worksheet area [2].
• Commands are organized into logical groups within the ribbons [2].
• The worksheet itself is a grid of columns (labeled with letters) and rows (labeled with numbers), which creates cells where data is entered [2, 3].
• The bottom of the interface contains tabs for different worksheets, scroll bars, a status bar, view options, and a zoom slider [3].
• Workbooks and Worksheets:A worksheet is the grid structure within Excel, and a workbook is the file that contains one or more worksheets [2].
• Data Entry and Editing:Data can be entered directly into cells [3].
• Contextual menus appear when right-clicking on a cell, with options specific to the type of data selected [3].
• The autofill handle can be used to copy data or formulas down a column [4].
• Formulas:Formulas are used to perform calculations [5].
• Formulas must begin with an equals sign (=) [5].
• Cell references are used in formulas rather than hardcoding numbers directly [5].
• The order of operations, often remembered by the acronym BODMAS (or PEMDAS), dictates how calculations are performed in formulas, with operations in parentheses/brackets performed first [5, 6].
• Common mathematical operators include addition (+), subtraction (-), multiplication (*), and division (/) [6].
• Functions are pre-built formulas that can be used in calculations, and can be found in the formulas tab [5].
• The sum function is commonly used to add a range of numbers [6].
• Relative and Absolute Referencing:Relative referencing means that cell references in a formula will automatically adjust when the formula is copied to another cell [4].
• Absolute referencing locks a cell reference to a specific cell, preventing it from changing when the formula is copied, and is indicated by using a dollar sign ($) before the column letter and row number (e.g. $A$1) [4].
• Basic Functions:SUM: Adds up a range of numbers [7].
• COUNT: Counts the number of cells in a range that contain numerical data [7].
• COUNTA: Counts the number of non-blank cells in a range (including text and numbers) [7].
• AVERAGE: Calculates the average of a range of numbers [7].
• MIN: Returns the smallest value in a range of numbers [7].
• MAX: Returns the largest value in a range of numbers [7].
• Excel Tables:Tables are a way to format data in Excel that add structure to the data and allow for more efficient analysis [8].
• Tables have a table design contextual ribbon that provides options for formatting [8].
• When using formulas on data in a table, table references are used, which include the table name and column name, rather than cell references [9].
• Tables can be named [9].
• Total rows can be added to tables to quickly calculate totals for columns [10].
• Rows and Columns:The width of columns or the height of rows can be autofitted [10].
• Columns and rows can be inserted, deleted, and hidden [10].
• Cell Formatting
• Cell formatting can be changed using options on the home ribbon [8].
• You can use the format painter to copy formatting [8].
• Cell Styles can be used to apply specific formatting consistently and identify different types of cells (input, calculation, etc.) [11].
• Gridlines can be removed to create a cleaner looking spreadsheet [8].
• You can use merge and center to combine cells and center the text or the “center across selection” to center the text in a range of cells, but maintain the individual cells [12].
• ThemesExcel themes affect the overall look and feel of a spreadsheet, controlling the colors, fonts, and effects used [12].
• You can choose from predefined themes or customize your own theme [12].
• Data Input and ValidationData Validation can be used to create drop-down lists, limit the type of data entered, and create custom error messages to prevent errors [13].
• Worksheet protection can be used to prevent changes to formulas and other parts of the worksheet [13].
• NavigationHyperlinks can be used to link to other worksheets, websites, or locations within the current worksheet [14].
• A summary sheet provides instructions, keys, or legends to assist users in navigating and understanding a workbook [14].
• Forms can be used to simplify the data entry process and can be added to the quick access toolbar [15].
• Dynamic Array Functions:These functions allow for a single formula to generate multiple results [16].
• SEQUENCE: Generates a list of sequential numbers [16].
• RANDARRAY: Generates a list of random numbers [16].
• UNIQUE: Extracts a list of unique values from a range of cells [16].
• SORT: Sorts a range of cells [16].
• SORTBY: Sorts a range of cells based on another range of cells [16].
• FILTER: Filters a range of cells [16].
• XLOOKUP: Performs lookups across columns, can be used as an alternative to INDEX and MATCH [16].
• XMATCH: Returns the position of an item in a range of cells [16].
• Power QueryPower Query is a tool that is used to import and transform data from multiple sources [17].
• It uses an applied steps area to record all data transformations [17].

Mastering Excel Formulas

Excel formulas are a key component of the application, allowing users to perform calculations, analyze data, and manipulate information [1].

Key aspects of formulas include:

• Initiation: Formulas always begin with an equals sign (=) [1]. This tells Excel that the content of the cell is a calculation, not just text or numbers.
• Cell References: When creating formulas, cell references are used rather than directly typing in or “hardcoding” the numbers [1]. For example, instead of typing “=6+3”, a user would type “=A1+A2” if the numbers 6 and 3 were in cells A1 and A2 [1]. Using cell references allows a formula to update automatically if the values in those cells change [1].
• Order of Operations: Calculations in formulas follow a specific order, often remembered by the acronym BODMAS or PEMDAS, which dictates the order in which mathematical operations are performed [1]:
• Brackets (or Parentheses)
• Orders (or Exponents)
• Division
• Multiplication
• Addition
• Subtraction
• If the order of operations is not correct, the formula will give an incorrect result, but this can be corrected by using brackets [1].
• Mathematical Operators:
• Addition is represented by the plus sign (+) [1].
• Subtraction is represented by the dash (-) [1].
• Multiplication is represented by the asterisk (*) [1].
• Division is represented by the forward slash (/) [1].
• Functions: Functions are pre-built formulas that can be used to perform specific tasks [1].
• They can be found in the formulas tab of the ribbon [1].
• Functions are organized into categories such as financial, logical, text, date and time, lookup and reference, math and trig, and more [1].
• The insert function button, or the keyboard shortcut Shift + F3, can be used to search for and insert a function [1].
• A function’s arguments are the values or cell ranges that the function uses to perform its calculation [1].
• A function typically requires an open bracket after the function name, then the arguments separated by commas, and then a closing bracket [1].
• Excel’s Intellisense feature provides a list of functions that match what a user is typing, with a brief explanation of each [1].
• Common Functions
• SUM adds up a range of numbers [1, 2].
• COUNT counts the number of cells in a range that contain numerical data [2].
• COUNTA counts the number of non-blank cells in a range, including both numbers and text [2].
• AVERAGE calculates the average of a range of numbers [2].
• MIN returns the smallest value in a range of numbers [2].
• MAX returns the largest value in a range of numbers [2].
• Cell Referencing:
• Relative referencing is the default in Excel [3]. When a formula is copied to another cell, the cell references in the formula will automatically adjust based on their relative position [3].
• Absolute referencing locks a cell reference to a specific cell, which means when a formula with an absolute reference is copied to another cell, the reference will not change. An absolute reference is created by adding a dollar sign ($) before the column letter and before the row number (e.g., $A$1) [3].
• Table References: When using formulas with data in a table, table references are used instead of cell references [4]. Table references use the table name and column name in the formula (e.g., employee_data[salary]) [4]. This can make formulas easier to understand [5].
• Dynamic Array Formulas:
• These functions allow for a single formula to generate multiple results [6].
• Examples include SEQUENCE, RANDARRAY, UNIQUE, SORT, SORTBY, FILTER, XLOOKUP, and XMATCH [5, 6].
• Logical Functions: These functions perform tests on data, returning results of true or false [7].
• The IF function performs a test and returns one value if the result is true and another if the result is false [7].
• IFS allows for multiple logical tests in one function [8].
• AND returns true if all conditions are met, while OR returns true if at least one condition is met [7].
• IFERROR and IFNA handle errors in formulas. IFERROR will handle any type of error while IFNA will only handle #NA errors [9].
• Lookup Functions:
• VLOOKUP is a lookup function that searches for a value in the first column of a table and returns a corresponding value from another column in the same row [8]. It can do an exact match or an approximate match [8].
• XLOOKUP is a newer lookup function that is more versatile than VLOOKUP and does not have the same limitations [5, 8].
• SUMIFS, COUNTIFS, and AVERAGEIFS: These functions allow for calculations based on multiple criteria [9].
• LET allows users to define variables within a formula and use those variables in calculations. This can make complex formulas easier to read and more efficient [10].

Mastering Excel Functions

Excel functions are pre-built formulas that perform specific tasks, and they are a key component of using Excel for calculations and data analysis [1, 2]. Functions can be found in the Formulas tab of the ribbon, and are organized into categories such as financial, logical, text, date and time, lookup and reference, math and trig, and more [1, 2]. The Insert Function button, or the keyboard shortcut Shift + F3, can be used to search for and insert a function [1, 2].

Here’s a breakdown of key aspects of Excel functions:

• Structure: A function typically requires an open bracket after the function name, then the arguments separated by commas, and then a closing bracket [1, 2]. Arguments are the values or cell ranges that the function uses to perform its calculation [3, 4].
• Intellisense: Excel’s Intellisense feature provides a list of functions that match what a user is typing, with a brief explanation of each [1, 2].
• Common Functions
• SUM adds up a range of numbers [3, 4]. It is a math and trig function that can be used to add a single column or a range of cells [1, 3]. The sum function is often found under the “Recently Used” functions [3].
• COUNT counts the number of cells in a range that contain numerical data [4].
• COUNTA counts the number of non-blank cells in a range, including both numbers and text [4].
• AVERAGE calculates the average of a range of numbers [4].
• MIN returns the smallest value in a range of numbers [4].
• MAX returns the largest value in a range of numbers [4].
• Logical Functions: These functions perform tests on data, returning results of true or false [2, 5, 6].
• The IF function performs a test and returns one value if the result is true and another if the result is false [5].
• IFS allows for multiple logical tests in one function [6].
• AND returns true if all conditions are met, while OR returns true if at least one condition is met [6].
• IFERROR and IFNA handle errors in formulas. IFERROR will handle any type of error while IFNA will only handle #NA errors [7].
• Lookup Functions:
• VLOOKUP is a lookup function that searches for a value in the first column of a table and returns a corresponding value from another column in the same row. It can do an exact match or an approximate match [2].
• XLOOKUP is a newer lookup function that is more versatile than VLOOKUP and does not have the same limitations [2].
• SUMIFS, COUNTIFS, and AVERAGEIFS: These functions allow for calculations based on multiple criteria [6].
• SUMIFS sums values in a range that meet multiple criteria [6].
• COUNTIFS counts cells in a range that meet multiple criteria [6].
• AVERAGEIFS calculates the average of values in a range that meet multiple criteria [6].
• Dynamic Array Functions: These functions allow for a single formula to generate multiple results, and can be combined with other functions [7, 8].
• Examples include SEQUENCE, RANDARRAY, UNIQUE, SORT, SORTBY, FILTER, XLOOKUP, and XMATCH [7, 8]. UNIQUE extracts a list of unique values from a range of cells [8]. SORT sorts a range of cells [8]. SORTBY sorts a range of cells based on another range of cells [8].
• LET allows users to define variables within a formula and use those variables in calculations [2]. This can make complex formulas easier to read and more efficient.

When using functions, it is also important to keep in mind the following:

• Formulas must begin with an equals sign (=) [1, 2].
• Cell references are used in formulas rather than hardcoding numbers directly [2].
• The order of operations (BODMAS or PEMDAS) dictates how calculations are performed in formulas [1, 2].
• Relative and absolute referencing determine how cell references change when a formula is copied to another cell [1, 2].
• Table references are used when using formulas with data in a table, using the table and column name in the formula [1, 2, 9].

Functions are fundamental to using Excel for data management and analysis [1, 2].

Data Formatting in Excel

Data formatting in Excel involves how data is displayed in cells, which can greatly affect the readability and interpretation of the information. Formatting can be applied to text, numbers, and dates, and it can control aspects such as font, alignment, colors, and number styles [1].

Key aspects of data formatting in Excel include:

• Text vs. Numbers: Text in a cell is aligned to the left by default, while numbers are aligned to the right [1].
• Number Formatting:
• Excel has various number formats including General, Number, Currency, Accounting, Short Date, and Long Date [1].
• The General format has no specific format [1].
• The Currency format displays a currency symbol and two decimal places by default [1].
• Number formatting can be applied using the Number group under the Home tab [1].
• Dates are treated as numbers by Excel, with the date of January 1, 1900, being day zero. When a date is entered, it is actually a number with date formatting applied [1].
• If a date is typed into a cell, but it looks like a number, it means that the cell has the wrong number formatting applied [1]. This can be corrected by selecting the Short Date or Long Date format [1].
• When entering numbers that start with zero, Excel will remove the leading zeros [1]. To prevent this, an apostrophe can be entered before the number, which will turn the number into text [1].
• Cell Alignment: Text in a cell is aligned to the left by default, while numbers and dates are aligned to the right [1].
• Copying Formats:
• The Format Painter tool can be used to copy formatting from one cell or a range of cells to another [2].
• When using the format painter, the entire column of formatting can be copied to another column [2].
• Clearing Formats:
• Formatting can be cleared from selected cells using the Clear menu in the Editing group under the Home tab [2].
• Options include:
• Clear All, which removes everything from the cell, including text, numbers, and formatting [2].
• Clear Formats, which removes all formatting while keeping the content [2].
• Clear Contents, which removes the text and numbers from cells but retains the formatting [2].
• Clear Comments and Notes, which clears comments and notes [2]. This is grayed out if there are no comments or notes in the worksheet [2].
• Clear Hyperlinks, which removes hyperlinks from selected cells [2].
• Remove Hyperlinks, which removes hyperlinks from selected cells and removes the underline [2].
• Cell Styles:
• Cell styles are predefined sets of formatting that can be applied to cells [3].
• Cell styles can be found on the Home tab [3].
• Cell styles can be used to quickly and consistently apply formatting to a range of cells [3].
• When adding data to a table, the cell style formatting carries through, and it’s not necessary to do anything extra to apply it [3].
• Some styles include Normal, Bad, Good, Neutral, Calculation, Input, Heading, and Title [3].
• Adding a legend or a key is important to clarify what the cell styles mean [3].
• Custom Formatting:Custom formatting can be used to define how numbers, text, and dates are displayed [4].
• Custom formatting is divided into four parts, with each part separated by a semicolon [4].
• The first part defines how positive values are displayed.
• The second part defines how negative values are displayed.
• The third part defines how zero values are displayed.
• The fourth part defines how text is displayed.
• Placeholders are used to specify how numbers are displayed.
• The hash symbol (#) is a variable placeholder.
• The zero (0) is a fixed placeholder [4].
• Colors can be included in custom formats by using the color name in square brackets (e.g., [red]) [4, 5].
• Symbols can be included in custom formats, and these can be inserted using the keyboard shortcut Alt + 30 for an up arrow or Alt + 31 for a down arrow [5].

By using different formatting options, users can make their data more readable and understandable and can help to control and standardize the way information is presented in a worksheet [3].

Mastering Excel Tables

Excel tables are a way to format data in a structured manner, and they offer many benefits when it comes to managing and analyzing data [1]. They are different from just entering data into cells and can be identified by a contextual “Table Design” ribbon that appears when a cell within the table is selected [1].

Here’s a breakdown of key aspects of Excel tables:

• Creation:
• To create a table, select the data, go to the Insert tab, and click on Table, or use the keyboard shortcut Ctrl + T [2].
• Excel will attempt to identify the data range, and you must confirm that the selection is correct and indicate whether the table has headers [2].
• When using the keyboard shortcut Ctrl+T, the default table style will be applied, although this can be changed later [2].
• You can also create a table by going to the Home tab and choosing Format as Table [2].
• Table Styles:
• Once a table is created, various table styles can be applied from the Table Design ribbon [2].
• Table style options include:
• Banded rows, which alternate row colors to improve readability [2].
• Banded columns which alternate column colors [2].
• Header row, which can be toggled on or off [2].
• Filter buttons, which allow for filtering of data [2].
• Total row, which can quickly calculate totals, averages, and other functions [2].
• Formatting of the first column or last column [2].
• The theme of a table can be changed by changing the theme of the Excel workbook, which will then change the available table styles [2].
• Naming Tables:It is important to give tables a meaningful name, which can be done in the Properties group on the Table Design tab [3].
• Table names cannot contain spaces, so an underscore is used between words [3].
• Table References:When using formulas with data in a table, table references are used rather than cell references [4].
• Table references use the table name and column name in the formula rather than cell references [4].
• For example, instead of using “F4:F23” to sum a range of salaries in a table called “employee data”, the formula would be “=SUM(employee data[salary])” [4].
• When selecting data in a table for use in a formula, you can hover over the column header until you see a downward pointing arrow, and then click to select all the data in that column [4].
• You can also type the table name into a formula, followed by an open square bracket, and then a list of columns will appear [4].
• Table references make formulas easier to understand because they use meaningful labels rather than cell references [4].
• Adding Data:
• When data is added to the bottom of a table, the table will automatically expand to include the new data [5].
• Any formatting, such as cell styles, will carry through to the new data [5].
• Removing Tables:
• Tables can be converted back to a normal range by selecting Convert to Range on the Table Design tab [2].
• This will remove the table formatting and features but will keep the data and any formatting [2].

Excel tables are an effective way to manage data, and are an important feature to understand in order to use Excel effectively [1].

By Amjad Izhar
Contact: amjad.izhar@gmail.com
https://amjadizhar.blog

Affiliate Disclosure: This blog may contain affiliate links, which means I may earn a small commission if you click on the link and make a purchase. This comes at no additional cost to you. I only recommend products or services that I believe will add value to my readers. Your support helps keep this blog running and allows me to continue providing you with quality content. Thank you for your support!