{"id":420,"date":"2026-09-08T11:56:59","date_gmt":"2026-09-08T16:56:59","guid":{"rendered":"https:\/\/sts.doit.wisc.edu\/training-materials\/?post_type=manual&p=420"},"modified":"2026-09-10T11:12:15","modified_gmt":"2026-09-10T16:12:15","slug":"excel-2-boost-your-excel-iq-mastering-functions","status":"publish","type":"manual","link":"https:\/\/sts.doit.wisc.edu\/training-materials\/manual\/excel-2-boost-your-excel-iq-mastering-functions\/","title":{"rendered":"Excel 2: Boost Your Excel IQ: Mastering Functions"},"content":{"rendered":"\n
\n

Introduction<\/h2>\n\n\n\n

Welcome to Excel 2: Functions<\/strong>! This course will build upon the basic skills you learned in the first Excel class. After this class, you will be able to create, manipulate, and analyze your own spreadsheets in multiple ways.<\/p>\n\n\n\n

About this class<\/h3>\n\n\n\n

Excel 2: Functions<\/strong><\/em> is one of the three intermediate Excel classes offered by Software Training for Students<\/strong>. These three classes (Functions, Data Visualization, and Analysis<\/em>) are designed so you can take them independently in any order. During this class, you will be introduced to some of Excel’s built-in functions, the methods used to access all of its powerful functions, and how to utilize its calculation abilities to your advantage.<\/p>\n\n\n\n

As of 2023<\/strong>, Microsoft Office has combined its PC and Mac versions to provide the same user experience to all users. However, one major difference remains, that it, the use of the Control and Command keys on Windows and Mac respectively for keyboard shortcuts. These keys essentially peform the same function and are used in conjunction with other keys to perform various tasks quicker. Your instructor will mention these interchangeably.<\/p>\n<\/section>\n\n\n\n

\n

Topics<\/h2>\n\n\n\n

The following topics will be covered in this class:<\/p>\n\n\n\n

    \n
  • Workbooks, worksheets, and linking data<\/li>\n\n\n\n
  • Logical functions<\/li>\n\n\n\n
  • Lookup, array and summary functions<\/li>\n\n\n\n
  • Formulas tab<\/li>\n\n\n\n
  • Keyboard shortcuts (Appendix)<\/li>\n<\/ul>\n<\/section>\n\n\n\n
    \n

    Workbooks, Worksheets, and Linking Data<\/strong><\/h2>\n\n\n\n

    In Excel 1, there was a brief introduction to Excel files, workbooks, and worksheets. In this course, you will learn how to manipulate and organize your worksheets using various options provided to you by Excel. These options can help to make your worksheets more efficient and reduce errors when making changes.<\/p>\n\n\n\n

    Workbooks vs. Worksheets<\/strong><\/h3>\n\n\n\n

    A single Excel file (usually of .xls or .xlsx format) is called a “workbook”, and by default contains three “worksheets”. Each worksheet is a unique workspace in which data can be stored and manipulated. If desired, data can be linked between worksheets. All worksheets in a workbook can be viewed near the bottom of the workbook in an area called the “Worksheet Tabs”.<\/p>\n\n\n\n

    Working with Worksheets<\/strong><\/h3>\n\n\n\n

    To create a new Excel file, locate and open Excel, and select Blank Workbook<\/strong>. If Excel is already running, you can create a new workbook by navigating to the File<\/strong> tab, selecting New<\/strong> from the options on the left, and selecting Blank Workbook.<\/p>\n\n\n\n

    Find the worksheet tabs by navigating to the bottom-left of the Excel window. Click the encircled +<\/strong> icon to create new worksheets.<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    Worksheet options<\/em><\/strong><\/h4>\n\n\n\n
    \"\"<\/figure>\n\n\n\n

    Right-clicking on a worksheet tab gives you access to several options:<\/p>\n\n\n\n

      \n
    • Insert Sheet<\/strong> adds a new worksheet, in the same way that clicking on the Insert Worksheet icon works.<\/li>\n\n\n\n
    • Delete<\/strong> permanently removes the worksheet from the file. If done accidentally, exit the file without saving to reopen to the last saved state.<\/li>\n\n\n\n
    • Rename<\/strong> allows you to change the worksheet’s name.<\/li>\n\n\n\n
    • Move<\/strong> or Copy<\/strong> allows you to reorder the tabs. Alternatively, you may also click and drag to move them.<\/li>\n\n\n\n
    • View Code<\/strong> is covered in Excel 2: Analysis and Excel 3: Macros and VBA.<\/li>\n\n\n\n
    • Protect Sheet<\/strong> allows the user to password-protect a sheet from accidental or unauthorized editing.<\/li>\n\n\n\n
    • Tab Color<\/strong> provides the option to color-code different tabs. This is useful to organize worksheets into different categories.<\/li>\n\n\n\n
    • Hide<\/strong> removes a worksheet from being visible in the Worksheet Tabs area.<\/li>\n\n\n\n
    • Unhide<\/strong> reverses the effects of hide and displays any previously hidden worksheets.<\/li>\n\n\n\n
    • Select All Sheets<\/strong> makes all worksheets in the workbook active so actions can be applied to all worksheets at once.<\/li>\n\n\n\n
    • Autofill<\/strong>: Autofill in Excel rapidly populates cells with data patterns or series, saving time by extending sequences across adjacent cells with a drag or the Autofill option.<\/li>\n\n\n\n
    • Services:<\/strong> Services in Excel encompass external functionalities like Microsoft 365 or third-party add-ins, integrating cloud-based tools for enhanced data analysis, visualization, collaboration, or automation within the spreadsheet software.<\/li>\n<\/ul>\n\n\n\n

      Referencing Data in Another Worksheet<\/strong><\/h3>\n\n\n\n

      Sometimes, it may be useful to take data from one worksheet and use it on a different worksheet. For example, you may want to have all your data on one sheet but your calculations on a different sheet, as shown below.<\/p>\n\n\n\n

      Display Data from Another Worksheet<\/em><\/strong><\/h4>\n\n\n\n

      ***HI NATY\/PETE if possible! Please make the lines under each number a bulleted list if possible!***<\/p>\n\n\n\n

        \n
      1. Understand the Worksheets<\/strong>\n
          \n
        1. Open the Excel file and note the following:<\/li>\n\n\n\n
        2. Sheet1<\/strong> contains the data.<\/li>\n\n\n\n
        3. Sheet2<\/strong> will be used to reference data from Sheet1.<\/li>\n<\/ol>\n<\/li>\n\n\n\n
        4. Insert a Value from Another Worksheet<\/strong>
          Switch to Sheet2<\/strong>.
          In cell A2<\/strong>, type the following formula: =Sheet1!A5<\/strong>
          Press Enter<\/strong>. The value from cell A5 of Sheet1 will now appear in A2 of Sheet2.<\/li>\n\n\n\n
        5. See How Changes are Reflected<\/strong>
          Go to Sheet1<\/strong> and change the value of A5 (e.g., from 6 to 4).
          Return to Sheet2<\/strong>. Notice that the value in A2 updates automatically.<\/li>\n\n\n\n
        6. Insert Another Linked Value Using the Click Method<\/strong>
          In Sheet2<\/strong>, click cell C2<\/strong>.
          Type = but don\u2019t press Enter yet.
          Switch to Sheet1<\/strong>, click cell B8<\/strong>, and press Enter<\/strong>.
          Return to Sheet2<\/strong>. You\u2019ll see that C2 now displays the value from B8 in Sheet1.<\/li>\n\n\n\n
        7. Review the Formula Syntax<\/strong>
          The formulas follow the syntax: =<Worksheet_Name>!<Cell_Address><\/em>
          This creates a dynamic link to the referenced worksheet, ensuring updates in Sheet1<\/strong> are automatically reflected in Sheet2<\/strong>.<\/li>\n<\/ol>\n\n\n\n

          Using Worksheet References in Formulas or Functions<\/em><\/strong><\/h4>\n\n\n\n

          ***HI NATY\/PETE if possible! Please make the lines under each number a bulleted list if possible!***<\/p>\n\n\n\n

            \n
          1. Use Worksheet References in Formulas<\/strong>
            In any cell of your current worksheet, enter the following formula to add values from two different worksheets: =Sheet1!A6 + Sheet1!B3<\/strong>
            In another cell, use the SUM function to calculate the sum of a range of values from another worksheet: =SUM(Sheet1!A2:A11)<\/strong><\/li>\n\n\n\n
          2. Understanding Cell Range References<\/strong>
            Notice that when using the SUM function, you don’t need to reference the worksheet for A11<\/strong> because Excel automatically assumes that the entire range from A2:A11<\/strong> belongs to the same worksheet.<\/li>\n\n\n\n
          3. Linking Multiple Cells Using Copy and Paste<\/strong>
            On the first worksheet, select the range of cells (e.g., B2:B11).
            Copy the selected cells by pressing Ctrl + C (or Cmd + C on Mac).
            Switch to your other worksheet and click on the cell where you want to start pasting (e.g., B15).
            Click the small arrow beneath the Paste button on the Home tab and select Paste Link from the options.
            Alternatively, you can use Paste Special and choose Paste Link.<\/li>\n\n\n\n
          4. Verify the Linked Data<\/strong>
            After pasting, the cells in the target worksheet will be linked to the corresponding cells in the source worksheet.
            Click on one of the linked cells, and in the formula bar, you\u2019ll see the linked formula (e.g., =Sheet1!B2).<\/li>\n<\/ol>\n<\/section>\n\n\n\n
            \n

            Logical Functions<\/strong><\/h2>\n\n\n\n

            Objective<\/strong><\/p>\n\n\n\n

            Excel 1 covered basic functions such as sum, min, max<\/em>, and average<\/em>, but Excel has many more powerful functions that do more than basic mathematical calculations. We will now practice with several of these functions and see how Excel can save us time when dealing with large amounts of data. All of these functions utilize Excel’s “live updating,” so as the date reference changes, the functions will recalculate.<\/p>\n\n\n\n

            COUNTA Function<\/strong><\/h3>\n\n\n\n

            The COUNTA function counts the number of non-empty cells in a given range. Non-empty cells can include numbers, letters, or symbols.<\/p>\n\n\n\n

              \n
            • To count the number of non-empty cells in a range, type the following formula: =COUNTA(B5:B10)<\/strong><\/li>\n\n\n\n
            •  This formula will return the number of non-empty cells in the range B5:B10.<\/li>\n\n\n\n
            •  If you type “yes” or another value in any of the cells in the range, notice that COUNTA still counts all non-empty cells.<\/li>\n<\/ul>\n\n\n\n

              COUNTIF Function<\/h3>\n\n\n\n

              The COUNTIF<\/strong> function in Excel is used to count the number of cells in a range that meet a specific condition (or criteria). For example, you can count how many cells contain a certain number, word, or other value.<\/p>\n\n\n\n

              Normally, when you use COUNTIF<\/strong>, you specify the criteria directly in the formula. However, you can make this criteria more flexible by using cell references<\/strong> instead of typing fixed values. This allows the condition to change automatically whenever the value in the referenced cell changes.<\/p>\n\n\n\n

              How It Works:<\/strong><\/p>\n\n\n\n

                \n
              1. Basic COUNTIF Usage:<\/strong> Normally, you would write a formula like: =COUNTIF(range, “>10”)
                This counts how many cells in the given range have a value greater than 10.<\/li>\n\n\n\n
              2. Using a Cell Reference:<\/strong> Instead of writing a specific number like 10, you can use a cell reference<\/strong> that contains the value you want to compare against.
                ***HI NATY\/PETE if possible! Please make these lines under number 2 a bulleted list if possible!***

                For example: =COUNTIF(range, “>” & A1)<\/strong>

                Here, A1 is the cell that contains the value you want to compare to. If the value in A1 changes, the formula will automatically update based on the new value.<\/li>\n<\/ol>\n\n\n\n

                Why this is useful:
                <\/strong>If you want to count how many numbers are greater than a target number, you can put that target in a cell (like A1<\/strong>) and change it anytime. The formula doesn’t need to be adjusted.<\/p>\n\n\n\n

                Example of Dynamic Criteria:<\/strong><\/p>\n\n\n\n

                  \n
                1.  If you have a value (e.g., 50) in a cell (say A1) and you want to count how many cells in a range are greater than this value, your formula would look like: =COUNTIF(range, “>” & A1)<\/strong><\/li>\n\n\n\n
                2.  If you want to count cells less than the value in A1<\/strong>, you would write: =COUNTIF(range, “<” & A1)<\/strong><\/li>\n<\/ol>\n\n\n\n

                  Why Use Cell References?<\/strong><\/p>\n\n\n\n

                    \n
                  • Flexibility<\/strong>: You don\u2019t need to update the formula each time you want to change the condition. Just change the value in the referenced cell.<\/li>\n\n\n\n
                  • Simplicity<\/strong>: It makes formulas easier to manage, especially if you need to use the same formula with different conditions. <\/strong><\/li>\n<\/ul>\n\n\n\n

                    Key Takeaways<\/strong><\/p>\n\n\n\n

                      \n
                    • COUNTA<\/strong> counts all non-empty cells, including those with numbers, text, or symbols.<\/li>\n\n\n\n
                    • COUNTIF <\/strong>counts cells that meet a specified condition<\/li>\n\n\n\n
                    • Cell references<\/strong> allow you to make the condition dynamic, so it updates automatically when the referenced value changes.<\/li>\n<\/ul>\n\n\n\n

                      OR and AND Functions<\/h3>\n\n\n\n

                      Let’s learn how to use the OR<\/strong> and AND<\/strong> functions to evaluate multiple conditions and return TRUE<\/strong> or FALSE<\/strong> results.<\/p>\n\n\n\n

                      Excel provides two logical functions called OR<\/strong> and AND<\/strong>. These functions help you evaluate multiple conditions (criteria) at once and return a TRUE<\/strong> or FALSE<\/strong> result based on whether the conditions are met. They are useful for testing multiple conditions in a more flexible way.<\/p>\n\n\n\n

                      OR Function<\/strong><\/h4>\n\n\n\n
                        \n
                      • The OR<\/strong> function checks multiple conditions and returns TRUE<\/strong> if any one of the conditions is true<\/strong>.<\/li>\n\n\n\n
                      • If all conditions are false<\/strong>, it will return FALSE<\/strong>.<\/li>\n<\/ul>\n\n\n\n

                        Example:<\/strong><\/p>\n\n\n\n

                        You want to check if at least one condition is met, such as:<\/p>\n\n\n\n

                          \n
                        • Is the total sales greater than 3500?<\/li>\n\n\n\n
                        • Or<\/strong>, is the total number of items sold greater than 300?<\/li>\n<\/ul>\n\n\n\n

                          If either condition is true, the OR<\/strong> function will return TRUE<\/strong>. If neither condition is true, it will return FALSE<\/strong>.<\/p>\n\n\n\n

                          Syntax: <\/strong>=OR(condition1, condition2, …)<\/em><\/p>\n\n\n\n

                          AND Function<\/h4>\n\n\n\n
                            \n
                          • The AND<\/strong> function checks multiple conditions and only returns TRUE<\/strong> if all conditions are true<\/strong>.<\/li>\n\n\n\n
                          • If any one condition is false<\/strong>, it will return FALSE<\/strong>.<\/li>\n<\/ul>\n\n\n\n

                            Example:<\/strong><\/p>\n\n\n\n

                            You want to check if both conditions are met, such as:<\/p>\n\n\n\n

                              \n
                            • Is the total sales greater than 3500?<\/li>\n\n\n\n
                            • And<\/strong> is the total number of items sold greater than 300?<\/li>\n<\/ul>\n\n\n\n

                              Only if both<\/strong> conditions are true, the AND<\/strong> function will return TRUE<\/strong>. If either of the conditions is false, it will return FALSE<\/strong>.<\/p>\n\n\n\n

                              Syntax:<\/strong> =AND(condition1, condition2, …)<\/em><\/p>\n\n\n\n

                              Key Differences Between OR and AND<\/strong><\/h4>\n\n\n\n
                                \n
                              • OR: <\/strong>Returns TRUE if at least one condition is true. It\u2019s used when you want to pass if one of the multiple conditions is met.<\/li>\n\n\n\n
                              • AND: <\/strong>Returns TRUE only if all conditions are true. It\u2019s used when you want to pass only if every condition is met.<\/li>\n<\/ul>\n\n\n\n

                                Why Use OR and AND?<\/strong><\/h4>\n\n\n\n
                                  \n
                                • Flexibility:<\/strong> These functions allow you to evaluate multiple conditions at once, making your data analysis more powerful.<\/li>\n\n\n\n
                                • Efficiency: <\/strong>Instead of checking each condition separately, you can check all conditions with a single function.<\/li>\n\n\n\n
                                • Dynamic Decision Making:<\/strong> You can combine them with other functions to make decisions based on complex criteria.<\/li>\n<\/ul>\n\n\n\n

                                  IF Functions and Nested Functions<\/h3>\n\n\n\n

                                  Let’s learn how to use the IF function and combine it with other functions (nested functions) to create logical tests and return custom results.<\/p>\n\n\n\n

                                  The IF Function<\/h4>\n\n\n\n

                                  The IF<\/strong> function allows you to perform a logical test and return specific results based on whether that test is TRUE<\/strong> or FALSE<\/strong>. It\u2019s a way to make decisions in Excel.<\/p>\n\n\n\n

                                    \n
                                  • Syntax<\/strong>: =IF(logical_test, value_if_true, value_if_false)<\/em><\/li>\n\n\n\n
                                  • logical_test: <\/strong>The condition that you want to check.<\/li>\n\n\n\n
                                  • value_if_true:<\/strong> The result if the condition is TRUE.<\/li>\n\n\n\n
                                  • value_if_false:<\/strong> The result if the condition is FALSE.<\/li>\n<\/ul>\n\n\n\n

                                    Example:<\/strong> Basic IF Function<\/h4>\n\n\n\n

                                    Let\u2019s say you want to check if someone is eligible for a bonus based on their total sales:<\/p>\n\n\n\n

                                      \n
                                    • If the total sales are greater than $3500, return “$500 Bonus”<\/strong>.<\/li>\n\n\n\n
                                    • If the total sales are less than or equal to $3500, return “No Bonus”<\/strong>.<\/li>\n<\/ul>\n\n\n\n

                                      The formula will look like this: =IF(total_sales > 3500, “$500 Bonus”, “No Bonus”)<\/em><\/p>\n\n\n\n

                                      Using IF with Other Functions (Nested Functions)<\/strong><\/strong><\/h4>\n\n\n\n

                                      You can nest<\/strong> functions within the IF<\/strong> function to perform more complex logic. This means using another function (like OR<\/strong>or another IF<\/strong>) as part of the logical test or the result.<\/p>\n\n\n\n

                                      Nested Functions: OR inside IF<\/strong><\/h4>\n\n\n\n

                                      You can combine the OR<\/strong> function inside an IF<\/strong> function. For example:<\/p>\n\n\n\n

                                        \n
                                      • If total sales<\/strong> are greater than $3500 OR<\/strong> if total items sold<\/strong> are greater than 300, the sales team gets a $500 bonus.<\/li>\n<\/ul>\n\n\n\n

                                        This will look like: =IF(OR(total_sales > 3500, items_sold > 300), “$500 Bonus”, “No Bonus”)<\/em><\/p>\n\n\n\n

                                        If either condition is TRUE<\/strong>, the result will be “$500 Bonus”<\/strong>.<\/p>\n\n\n\n

                                        Nested IF Functions<\/h4>\n\n\n\n

                                        You can also nest IF<\/strong> functions inside one another to check for multiple conditions. For example:<\/p>\n\n\n\n

                                          \n
                                        • If total sales<\/strong> are greater than $3500, return “$500 Bonus”<\/strong>.<\/li>\n\n\n\n
                                        • If total sales<\/strong> are between $3000 and $3500, return “$100 Bonus”<\/strong>.<\/li>\n\n\n\n
                                        • If neither condition is met, return “No Bonus”<\/strong>.<\/li>\n<\/ul>\n\n\n\n

                                          This would look like: =IF(total_sales > 3500, “$500 Bonus”, IF(total_sales > 3000, “$100 Bonus”, “No Bonus”))<\/em><\/p>\n\n\n\n

                                          In this case, Excel first checks if the total sales are above $3500. If TRUE<\/strong>, it gives the $500 bonus. If FALSE<\/strong>, it checks if the total sales are above $3000. If this condition is TRUE<\/strong>, it gives the $100 bonus. If neither condition is met, it returns “No Bonus”<\/strong>.<\/p>\n\n\n\n

                                          Key Points to Remember<\/h4>\n\n\n\n
                                            \n
                                          • IF<\/strong> allows you to return different values based on whether a condition is TRUE<\/strong> or FALSE<\/strong>.<\/li>\n\n\n\n
                                          • Nested IF<\/strong> allows you to evaluate multiple conditions in sequence.<\/li>\n\n\n\n
                                          • Logical functions like OR and AND<\/strong> can be used inside IF<\/strong> to create more complex conditions.<\/li>\n\n\n\n
                                          • Excel automatically recalculates the result when the conditions or referenced data change.<\/li>\n<\/ul>\n<\/section>\n\n\n\n
                                            \n

                                            Lookup, Array, and Summary Functions<\/strong><\/h2>\n\n\n\n

                                            We have explored some of Excel’s logical functions, but there are many other categories that we haven’t used yet. We will now learn how to lookup values, change data’s orientation, and summarize sorted data.<\/p>\n\n\n\n

                                            XLOOKUP<\/strong><\/h3>\n\n\n\n

                                            The XLOOKUP function in Excel allows you to search for a value in one range (called the lookup array) and return a corresponding value from another range (called the return array). It’s a powerful tool that can replace older functions like VLOOKUP, HLOOKUP, and LOOKUP, making it more versatile because it can search in any direction (vertical or horizontal) and handle missing values efficiently.<\/p>\n\n\n\n

                                            Syntax of XLOOKUP<\/h4>\n\n\n\n

                                            = XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])<\/em><\/p>\n\n\n\n

                                              \n
                                            • lookup_value<\/strong>: The value you want to search for.<\/li>\n\n\n\n
                                            • lookup_array<\/strong>: The range where the function will search for the lookup_value<\/strong>.<\/li>\n\n\n\n
                                            • return_array<\/strong>: The range from which to return the corresponding value.<\/li>\n\n\n\n
                                            • [if_not_found]<\/strong> (optional): The value to return if no match is found. This is optional and you can choose to leave it blank or specify a custom message (e.g., “Not found”).<\/li>\n\n\n\n
                                            • [match_mode]<\/strong> (optional): Determines how the function matches the lookup_value<\/strong>. It can be:\n
                                                \n
                                              • 0<\/strong>: Exact match.<\/li>\n\n\n\n
                                              • -1<\/strong>: Exact match or next smaller item.<\/li>\n\n\n\n
                                              • 1<\/strong>: Exact match or next larger item.<\/li>\n\n\n\n
                                              • 2<\/strong>: Wildcard match (useful for searching with partial matches).<\/li>\n<\/ul>\n<\/li>\n\n\n\n
                                              • [search_mode]<\/strong> (optional): Determines the search direction.\n
                                                  \n
                                                • 1<\/strong>: Search from first to last (default).<\/li>\n\n\n\n
                                                • -1<\/strong>: Search from last to first.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n

                                                  How XLOOKUP Works<\/h4>\n\n\n\n
                                                    \n
                                                  1. Specify the Value to Search For<\/strong>: The first part of the function is the lookup_value<\/strong>, which is the value you want to search for. This could be a number, text, or a reference to another cell.<\/li>\n\n\n\n
                                                  2. Select the Range to Search<\/strong>: The lookup_array<\/strong> is the range where Excel will search for the lookup_value<\/strong>. This could be a single column or row of data.<\/li>\n\n\n\n
                                                  3. Select the Range to Return the Value From<\/strong>: The return_array<\/strong> is where Excel will pull the result from. This range must be the same size as the lookup_array<\/strong>.<\/li>\n\n\n\n
                                                  4. Match Mode<\/strong>: You can choose how Excel matches the lookup_value<\/strong>. The most common use is 0<\/strong> for an exact match, but other options like -1<\/strong> for the next smaller value or 1<\/strong> for the next larger value can also be useful, depending on your data.<\/li>\n<\/ol>\n\n\n\n

                                                    Example Scenario:<\/strong><\/p>\n\n\n\n

                                                    Let\u2019s say you have a list of employee sales figures, and you want to find out the corresponding bonus each employee is eligible for based on their sales.<\/p>\n\n\n\n

                                                      \n
                                                    • lookup_value<\/strong>: The sales number for which you need the bonus.<\/li>\n\n\n\n
                                                    • lookup_array<\/strong>: The range of sales numbers where the bonus data can be found.<\/li>\n\n\n\n
                                                    • return_array<\/strong>: The range where the bonus values are listed.<\/li>\n<\/ul>\n\n\n\n

                                                      You would use XLOOKUP<\/strong> to search for the sales figure and return the corresponding bonus from the return_array<\/strong>.<\/p>\n\n\n\n

                                                      Key Benefits of XLOOKUP<\/h4>\n\n\n\n
                                                        \n
                                                      • Search in any direction<\/strong>: Unlike VLOOKUP<\/strong> and HLOOKUP<\/strong>, XLOOKUP<\/strong> works both vertically and horizontally.<\/li>\n\n\n\n
                                                      • Handle missing values<\/strong>: You can specify a custom message or value for when no match is found.<\/li>\n\n\n\n
                                                      • Flexible matching options<\/strong>: You can choose how to match values (exact match, next smaller or larger, or wildcard match).<\/li>\n\n\n\n
                                                      • Simplifies formulas<\/strong>: XLOOKUP<\/strong> replaces multiple older functions and simplifies formula writing.<\/li>\n<\/ul>\n\n\n\n

                                                        XLOOKUP: Two Inputs<\/em><\/strong><\/h4>\n\n\n\n

                                                        The XLOOKUP<\/em> function can also handle cases where the lookup value is determined by two different inputs. For example, in a scenario where bonuses depend on both sales and employee evaluation scores, XLOOKUP<\/em> can be used to search for values based on both criteria.<\/p>\n\n\n\n

                                                        How to Use XLOOKUP with Two Inputs<\/strong><\/p>\n\n\n\n

                                                          \n
                                                        1. Specify the Lookup Values<\/strong>: You need to define both lookup values for your XLOOKUP<\/strong> function. For instance, one value could be the sales amount, and the second value could be the employee evaluation score.<\/li>\n\n\n\n
                                                        2. Use Both Values in the Lookup Array<\/strong>: In this case, XLOOKUP<\/strong> will need to look through two criteria in your data table: one for sales and one for the evaluation score.<\/li>\n\n\n\n
                                                        3. Adjust for the Column Index<\/strong>: The column index in your table may not directly correspond to the input values. Often, one of the inputs (like the evaluation score) will need to be adjusted to find the right column number where the bonus is listed. For example, the bonus may be in a column that\u2019s offset by a certain number from the evaluation score. This can be handled by modifying the column number used in the XLOOKUP<\/strong>.<\/li>\n<\/ol>\n\n\n\n

                                                          Example Scenario: Employee Bonus Calculation<\/strong><\/p>\n\n\n\n

                                                            \n
                                                          • Input 1 (Sales)<\/strong>: The first value to search for could be the employee\u2019s total sales.<\/li>\n\n\n\n
                                                          • Input 2 (Evaluation Score)<\/strong>: The second input could be the employee’s evaluation score.<\/li>\n<\/ul>\n\n\n\n

                                                            Steps<\/strong>:<\/p>\n\n\n\n

                                                              \n
                                                            1. Sales as the First Lookup<\/strong>: The function will first search for the sales figure in the data table.<\/li>\n\n\n\n
                                                            2. Evaluation Score as the Second Lookup<\/strong>: The second input (evaluation score) is used to adjust the column index in the data table, determining which bonus column to pull from.<\/li>\n<\/ol>\n\n\n\n

                                                              Key Points to Consider:<\/strong><\/p>\n\n\n\n

                                                                \n
                                                              • Lookup Array<\/strong>: This is the range of data where you\u2019ll search for both values (sales and evaluation score).<\/li>\n\n\n\n
                                                              • Return Array<\/strong>: This is where the corresponding value (e.g., bonus) will be returned from based on the matched inputs.<\/li>\n\n\n\n
                                                              • Dynamic Column Index<\/strong>: Sometimes, the column you want to pull data from may not correspond directly to the score or value you’re looking up. You can adjust the column index dynamically, such as adding an offset, to match the correct column.<\/li>\n<\/ul>\n\n\n\n

                                                                Benefits of Using XLOOKUP with Two Inputs:<\/strong><\/p>\n\n\n\n

                                                                  \n
                                                                • Versatility<\/strong>: XLOOKUP<\/strong> allows you to combine multiple criteria to search and return a result.<\/li>\n\n\n\n
                                                                • Dynamic Column Adjustment<\/strong>: You can adjust for data tables where the exact column position isn\u2019t fixed, making it easier to work with complex datasets.<\/li>\n\n\n\n
                                                                • Simplified Formula<\/strong>: Unlike older functions (e.g., VLOOKUP<\/strong>), XLOOKUP<\/strong> can handle both criteria in one formula, without needing to set up complex lookups or table references.<\/li>\n<\/ul>\n<\/section>\n\n\n\n
                                                                  \n

                                                                  Transpose<\/h2>\n\n\n\n

                                                                  When you have data in a spreadsheet, it\u2019s often arranged in either rows (horizontal) or columns (vertical). The Transpose<\/strong> function allows you to switch between these orientations quickly. For example, if your data is in a row and you want to display it in a column, or vice versa, you can transpose it.<\/p>\n\n\n\n

                                                                  Methods to Transpose Data<\/strong><\/p>\n\n\n\n

                                                                  There are two primary ways to transpose data in Excel: using Copy-Paste<\/strong> or the Transpose function<\/strong>.<\/p>\n\n\n\n

                                                                  Method 1: Transpose Using Copy-Paste<\/strong><\/h3>\n\n\n\n

                                                                  This is a quick and easy way to switch the data from rows to columns or columns to rows.<\/p>\n\n\n\n

                                                                  Steps<\/strong>:<\/p>\n\n\n\n

                                                                    \n
                                                                  1. Copy the Data<\/strong>: Select the data you want to transpose (e.g., a range of cells) and copy it (Ctrl + C).<\/li>\n\n\n\n
                                                                  2. Select the New Location<\/strong>: Click the cell where you want the transposed data to appear.<\/li>\n\n\n\n
                                                                  3. Paste and Transpose<\/strong>:\n
                                                                      \n
                                                                    • Go to the Home<\/strong> tab.<\/li>\n\n\n\n
                                                                    • In the Paste<\/strong> dropdown, choose Transpose<\/strong>.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n

                                                                      This operation switches the rows and columns of your data, but there\u2019s a limitation: the transposed data won\u2019t update if the original data changes.<\/p>\n\n\n\n

                                                                      Method 2: Transpose Using the Built-In Function<\/strong><\/h3>\n\n\n\n

                                                                      For more dynamic transposition where the data updates automatically when the original data changes, you can use the Transpose function<\/strong>.<\/p>\n\n\n\n

                                                                      Steps<\/strong>:<\/p>\n\n\n\n

                                                                        \n
                                                                      1. Select the Area for Transposition<\/strong>: Choose the number of rows and columns that match the new transposed data.\n
                                                                          \n
                                                                        • For example, if you want to transpose a 6-column by 7-row range, select a 7-column by 6-row area.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
                                                                        • Enter the Transpose Formula<\/strong>:\n
                                                                            \n
                                                                          • While the area is selected, type =TRANSPOSE(<\/strong><\/li>\n\n\n\n
                                                                          • Then, select the original range of cells you want to transpose.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
                                                                          • Apply the Formula<\/strong>: Instead of just pressing Enter, press Ctrl + Shift + Enter<\/strong> (or Command + Shift + Enter on a Mac). This creates an array formula that fills the selected cells with the transposed data.<\/li>\n<\/ol>\n\n\n\n

                                                                            Key Points<\/h3>\n\n\n\n
                                                                              \n
                                                                            • Copy-Paste Transpose<\/strong>: Quick and simple but doesn’t update if the original data changes.<\/li>\n\n\n\n
                                                                            • Transpose Function<\/strong>: More flexible and updates automatically when the original data is changed.<\/li>\n<\/ul>\n<\/section>\n\n\n\n
                                                                              \n

                                                                              Text Formatting<\/h2>\n\n\n\n

                                                                              Strings of text in Excel can be formatted to add decimal places and points, commas and currency signs. We can also concatenate text, that is, join different text elements into singular strings. This is useful to output sentences in Excel.<\/p>\n\n\n\n

                                                                              TEXT function<\/strong><\/h3>\n\n\n\n

                                                                              The TEXT function<\/strong> in Excel allows you to format numeric values as text in a more readable way. This function is helpful for displaying numbers with specific formats, such as adding commas, decimal places, or currency symbols, or when you want to combine numbers with text.<\/p>\n\n\n\n

                                                                              TEXT Function Syntax<\/strong><\/h4>\n\n\n\n

                                                                              The syntax for the TEXT function is: TEXT(value, format_text)<\/em><\/p>\n\n\n\n

                                                                                \n
                                                                              • value<\/strong>: This is the number, formula result, or cell reference that you want to format.<\/li>\n\n\n\n
                                                                              • format_text<\/strong>: This is the format you want to apply to the value, written as a text string. For example, “#,##0.00” to display numbers with commas and two decimal places.<\/li>\n<\/ul>\n\n\n\n

                                                                                Examples of using the TEXT Function<\/h4>\n\n\n\n
                                                                                  \n
                                                                                1. Formatting Decimal Places: <\/strong>You can control how many decimal places are visible using the TEXT function.\n
                                                                                    \n
                                                                                  • Using the “#” symbol<\/strong>: This shows a number if present; if there’s no number, it leaves the space blank. =TEXT(A4,”#.##”)<\/em><\/li>\n\n\n\n
                                                                                  • Using the “#” symbol<\/strong>: This shows a number if present; if there’s no number, it leaves the space blank. =TEXT(A4,”#.##”)<\/em><\/li>\n\n\n\n
                                                                                  • In this example, both formulas might look similar, but the difference is that the 0.00 format will always show two decimal places, even if they are zeros.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
                                                                                  • Formatting as Currency: <\/strong>To format a number as currency, you can use the TEXT function to add commas and currency symbols.\n
                                                                                      \n
                                                                                    • Number with commas (thousands separator)<\/strong>: =TEXT(A12,”#,###”)<\/em><\/li>\n\n\n\n
                                                                                    • Number with commas and two decimal places<\/strong>: =TEXT(A14,”#,###.00″)<\/em><\/li>\n\n\n\n
                                                                                    • Number with a dollar sign and two decimal places<\/strong>: =TEXT(A16,”$#,###.00″)<\/em><\/li>\n<\/ul>\n<\/li>\n\n\n\n
                                                                                    • Formatting Dates: <\/strong>The TEXT function is also useful for formatting dates in different ways.\n
                                                                                        \n
                                                                                      • Date as month-day-year<\/strong>:  =TEXT(A18,”mm-dd-yyyy”)<\/em><\/li>\n\n\n\n
                                                                                      • Day of the week<\/strong> (e.g., Monday, Tuesday): =TEXT(A20,”dddd”)<\/em><\/li>\n\n\n\n
                                                                                      • Full month name and date<\/strong>:  =TEXT(A22,”mmmm dd, yyyy”)<\/em><\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n

                                                                                        Key Takeaways<\/h4>\n\n\n\n
                                                                                          \n
                                                                                        • The TEXT function<\/strong> allows you to convert numbers into more readable formats.<\/li>\n\n\n\n
                                                                                        • You can use the #<\/strong> symbol to show numbers when present or leave blank if not.<\/li>\n\n\n\n
                                                                                        • The 0<\/strong> symbol ensures that zeros are shown when there is no number.<\/li>\n\n\n\n
                                                                                        • Currency formatting<\/strong> can be done with commas<\/strong> and symbols like $<\/strong>.<\/li>\n\n\n\n
                                                                                        • You can also format dates<\/strong> to show them in various ways.<\/li>\n<\/ul>\n\n\n\n

                                                                                          Concatenating text using “&”<\/strong><\/h3>\n\n\n\n

                                                                                          To combine text in Excel, you can use the ampersand (&) operator. This operator allows you to join different text strings into a single, readable string. For example, if you want to combine different pieces of information, you can use & to join them together.<\/p>\n\n\n\n

                                                                                          Example:<\/strong><\/p>\n\n\n\n

                                                                                          To concatenate text in Excel, write the following formula: =A1 & ” ” & B1 & ” ” & C1<\/strong>
                                                                                          This formula will combine the contents of cells A1, B1, and C1, separating each with a space. For example, if A1 contains “Hello”, B1 contains “World”, and C1 contains “!”, the result will be “Hello World !”.<\/p>\n\n\n\n

                                                                                          Steps:<\/strong><\/p>\n\n\n\n

                                                                                            \n
                                                                                          1. Select a cell where you want to output the concatenated text.<\/li>\n\n\n\n
                                                                                          2. Use the & operator to combine the text. You can insert spaces or punctuation between the text by enclosing them in quotation marks.\n
                                                                                              \n
                                                                                            • Example: =A30 & ” ” & B30 & ” ” & C30 & ” ” & D30 & “.”<\/strong><\/li>\n\n\n\n
                                                                                            • This will create a sentence, separating each word with a space and ending with a period.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n

                                                                                              Concatenating Formatted Text Using “&” and TEXT()<\/strong><\/h4>\n\n\n\n

                                                                                              You can also use the TEXT<\/strong> function within the concatenation to format numbers, dates, or other values in a specific way.<\/p>\n\n\n\n

                                                                                              To combine formatted text: =A1 & ” ” & B1 & ” earned ” & TEXT(C1, “$#,###.00″) & ” on ” & TEXT(D1, “mmmm dd, yyyy”)<\/em><\/p>\n\n\n\n

                                                                                                \n
                                                                                              • TEXT(C1, “$#,###.00”)<\/strong>: This formats the number in C1 as currency with two decimal places and commas for thousands.<\/li>\n\n\n\n
                                                                                              • TEXT(D1, “mmmm dd, yyyy”)<\/strong>: This formats the date in D1 as “Month day, year” (e.g., “January 01, 2024”).<\/li>\n\n\n\n
                                                                                              • This formula would result in something like: John Doe earned $2,500.00 on January 01, 2024<\/li>\n<\/ul>\n\n\n\n

                                                                                                Steps:<\/strong><\/p>\n\n\n\n

                                                                                                  \n
                                                                                                1. Select the cell where you want the formatted, concatenated result.<\/li>\n\n\n\n
                                                                                                2. Use the TEXT function to format numbers or dates and concatenate them into a single string. (Example: =A40 & ” ” & B40 & ” earned ” & TEXT(C40, “$#,###.00″) & ” on ” & TEXT(D40, “mmmm dd, yyyy”<\/em>)<\/li>\n<\/ol>\n<\/section>\n\n\n\n
                                                                                                  \n

                                                                                                  Formulas Tab<\/strong><\/h2>\n\n\n\n

                                                                                                  As has been seen up to this point, Excel is an incredibly powerful tool for organizing, analyzing, creating, and manipulating numeric data. We have already used multiple methods to access these functions: typing it in ourselves, using the insert function button, and using the drop-down menus when typing. The last method we\u2019ll cover to insert functions is on the Formulas tab.<\/p>\n\n\n\n

                                                                                                  The Formulas tab gives us access to Excel\u2019s entire Function Library, much like the Insert Function button. Additionally, it can help us understand what our functions are doing, and fix errors that may be causing them to behave incorrectly.<\/p>\n\n\n\n

                                                                                                  Additional Relevant Functions<\/strong><\/h3>\n\n\n\n

                                                                                                  Excel offers a variety of additional functions that are useful for tasks such as ranking data, rounding numbers, converting units, and finding the largest or smallest values in a dataset. These functions can be used to analyze and manipulate your data more efficiently.<\/p>\n\n\n\n

                                                                                                  Ranking Data with RANK.EQ<\/strong><\/h4>\n\n\n\n

                                                                                                  The RANK.EQ function is used to determine the rank of a specific value within a dataset. It will return the position of a number in a list of numbers.<\/p>\n\n\n\n

                                                                                                  How to Use RANK.EQ:<\/strong><\/p>\n\n\n\n

                                                                                                    \n
                                                                                                  1. Select the cell where you want the rank to appear.<\/li>\n\n\n\n
                                                                                                  2. Go to the Formulas<\/strong> tab and choose More Functions<\/strong> > Statistical<\/strong>.<\/li>\n\n\n\n
                                                                                                  3. Find and select RANK.EQ<\/strong> from the list.<\/li>\n\n\n\n
                                                                                                  4. In the Function Arguments window, fill in:\n
                                                                                                      \n
                                                                                                    • Number<\/strong>: Select the cell with the value you want to rank.<\/li>\n\n\n\n
                                                                                                    • Ref<\/strong>: Select the entire range of data you want to rank against (make sure to use absolute references if necessary).<\/li>\n\n\n\n
                                                                                                    • Order<\/strong>: Leave blank to default to descending order (or specify ascending if needed).<\/li>\n<\/ul>\n<\/li>\n\n\n\n
                                                                                                    • Click OK<\/strong>.<\/li>\n<\/ol>\n\n\n\n

                                                                                                      You can then autofill the formula down the column to rank all the values in your dataset.<\/p>\n\n\n\n

                                                                                                      Rounding Numbers with ROUND<\/strong><\/h4>\n\n\n\n

                                                                                                      The ROUND function rounds a number to a specified number of decimal places. This is useful for cleaning up data, especially when you only need a certain level of precision.<\/p>\n\n\n\n

                                                                                                      How to Use ROUND:<\/strong><\/p>\n\n\n\n

                                                                                                        \n
                                                                                                      1. Select the cell where you want the rounded number to appear.<\/li>\n\n\n\n
                                                                                                      2. Go to the Formulas<\/strong> tab, choose Math & Trig<\/strong>, and select ROUND<\/strong>.<\/li>\n\n\n\n
                                                                                                      3. In the Function Arguments window:\n
                                                                                                          \n
                                                                                                        • Number<\/strong>: Select the cell containing the value you want to round.<\/li>\n\n\n\n
                                                                                                        • Num_digits<\/strong>: Enter the number of decimal places you want to keep.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
                                                                                                        • Click OK<\/strong>.<\/li>\n<\/ol>\n\n\n\n

                                                                                                          Autofill the formula down the column to apply rounding to other values. You can use the Decrease Decimal<\/strong> button on the Home<\/strong> tab if you just want to display fewer decimals without actually changing the value in the cell.<\/p>\n\n\n\n

                                                                                                          Unit Conversions with CONVERT<\/strong><\/h4>\n\n\n\n

                                                                                                          The CONVERT function allows you to convert measurements between different units (e.g., temperature, length, weight, etc.).<\/p>\n\n\n\n

                                                                                                          How to Use CONVERT:<\/strong><\/p>\n\n\n\n

                                                                                                            \n
                                                                                                          1. Select the cell where you want the converted value to appear.<\/li>\n\n\n\n
                                                                                                          2. In the formula bar, type: =CONVERT(value, “from_unit”, “to_unit”)\n
                                                                                                              \n
                                                                                                            • Replace value with the cell reference for the value you want to convert, and “from_unit” and “to_unit” with the units you’re converting between (e.g., “F” for Fahrenheit, “C” for Celsius).<\/li>\n<\/ul>\n<\/li>\n\n\n\n
                                                                                                            • Press Enter<\/strong>.<\/li>\n<\/ol>\n\n\n\n

                                                                                                              Autofill the formula down the column to convert multiple values.<\/p>\n\n\n\n

                                                                                                              Finding the Kth Largest or Smallest Value<\/strong><\/h4>\n\n\n\n

                                                                                                              To find the Kth largest or Kth smallest value in a dataset, use the LARGE or SMALL function. This allows you to pinpoint specific values in your data without needing to sort it.<\/p>\n\n\n\n

                                                                                                              How to Use LARGE and SMALL:<\/strong><\/p>\n\n\n\n

                                                                                                                \n
                                                                                                              1. Select the cell where you want the result to appear.<\/li>\n\n\n\n
                                                                                                              2. For the Kth largest value, use the LARGE function: =LARGE(range, K)\n
                                                                                                                  \n
                                                                                                                • For the Kth smallest value, use the SMALL function: =SMALL(range, K)<\/li>\n\n\n\n
                                                                                                                • range<\/strong>: Select the entire dataset.<\/li>\n\n\n\n
                                                                                                                • K<\/strong>: Enter the position of the value you want (e.g., 10 for the 10th largest or smallest).<\/li>\n<\/ul>\n<\/li>\n\n\n\n
                                                                                                                • Press Enter<\/strong>.\n
                                                                                                                    \n
                                                                                                                  • Autofill the formula down the column to find other Kth largest or smallest values in your dataset<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n

                                                                                                                    In Summary:<\/h4>\n\n\n\n

                                                                                                                    These advanced Excel functions\u2014RANK.EQ, ROUND, CONVERT, LARGE, and SMALL\u2014can be used to efficiently manipulate and analyze data without having to manually sort, adjust, or convert values. They help streamline the process of ranking, rounding, converting units, and finding specific values within a dataset.<\/p>\n<\/section>\n","protected":false},"author":19,"featured_media":0,"template":"","meta":{"_acf_changed":false,"_uw_seo_meta_title":"","_uw_seo_meta_description":"","_uw_seo_twitter_card_type":"summary_large_image","_uw_seo_meta_image":"","_uw_seo_meta_image_url":"","_uw_seo_meta_image_sizes":[],"_uw_seo_custom_meta_tags":[],"footnotes":""},"categories":[],"class_list":["post-420","manual","type-manual","status-publish","hentry"],"acf":[],"_links":{"self":[{"href":"https:\/\/sts.doit.wisc.edu\/training-materials\/wp-json\/wp\/v2\/manual\/420","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sts.doit.wisc.edu\/training-materials\/wp-json\/wp\/v2\/manual"}],"about":[{"href":"https:\/\/sts.doit.wisc.edu\/training-materials\/wp-json\/wp\/v2\/types\/manual"}],"author":[{"embeddable":true,"href":"https:\/\/sts.doit.wisc.edu\/training-materials\/wp-json\/wp\/v2\/users\/19"}],"version-history":[{"count":4,"href":"https:\/\/sts.doit.wisc.edu\/training-materials\/wp-json\/wp\/v2\/manual\/420\/revisions"}],"predecessor-version":[{"id":1368,"href":"https:\/\/sts.doit.wisc.edu\/training-materials\/wp-json\/wp\/v2\/manual\/420\/revisions\/1368"}],"wp:attachment":[{"href":"https:\/\/sts.doit.wisc.edu\/training-materials\/wp-json\/wp\/v2\/media?parent=420"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sts.doit.wisc.edu\/training-materials\/wp-json\/wp\/v2\/categories?post=420"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}