Search

Articles
23
Sep

Excel XLOOKUP Function: Overview With Examples
  • Lynn Fountain, CPA
  • 3589
Now Office 365 comes with modern and flexible replacement of older functions like VLOOKUP, HLOOKUP, and LOOKUP that is XLOOPUP function. Excel 2019 and other previous versions won’t ever get this new function. Here we are going to learn all about function, syntax, operational parameters with examples. You are familiar with VLOOPUP, HLOOKUP, INDEX+MATCH, LOOKUP, etc. Now you are introduced to the XLOOKUP function. It allows users to search for an item in a range and return matching results. In most of the way, it is similar to VLOOKUP but offers more functionality than that, (XLOOKUP vs VLOOKUP) .XLOOKUP function allows you to find values in a dataset (vertical or horizontal) and return the matching value in some other row/column. It means it supports approximate and exact matching, wildcards (*?) for partial matches and lookups in vertical or horizontal ranges.For example, if you have an exam scoreboard of students, you can use XLOOKUP to quickly check how much a student has scored by using the name of the student. If you go deep in the discussion, then here, you will find the power of this function through more examples. How to Access XLOOKUP Function in ExcelAs, this function is only available with the Office 365 (Home, Personal, or University edition). First you need to go to File tab and then click on Account. There you find an Office Insider program, click it, and join the Office Insider Program. This way you will get access to the XLOOKUP function. XLOOKUP BenefitsIt can lookup data to the right or left of lookup values. It can return multiple results. It can handle missing values with the inbuilt IFNA. It can work with vertical and horizontal data. Without sorting the table, it can find next smaller or next larger match. You can turn on wildcard. It looks for the exact match unlike VLOOKUP (defaults to approximate).It can perform reverse search means last to first. Not just one value but it can return entire rows or columns. To apply complex criteria, it can work with arrays natively. XLOOKUP Function Syntax =XLOOKUP (lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode]) XLOOKUP has three basic and three optional parameters total 61. lookup_value – That is the value we are looking up. 2. lookup_array - That is array in which you are looking for the lookup value. 3. Return_array - The range to return the value.These are required arguments, below are optional one. 1. [if_not_found] - It will return the specified value when the lookup value is not found. 2. [match_mode] - This is the match to return. Here you can specify the type of match you need. 0 = This is the default match mode. Here XLOOKUP will look for the exact match. If it is not found, then the return value will the no applicable error. -1. = An exact match is returned, but if when it is not found then the next smallest value is returned. 1. = An exact match is returned but if it is not found then the largest value is returned. 2= An exact match is returned but if special characters are used, it acts as wildcard characters in lookup_value and return partial matches. 3. [search_mode] – This is the type of search to perform by XLOOKUP. 1 – this is the default option. Search performed from first to last item in the lookup_array. -1 - here the search performed from last to first item in the lookup_array. 2. – perform a binary search from first to last item in the lookup_array being sorted in ascending order. If not, then an invalid result will be returned. -2 - perform a binary search from last to first item in the lookup_array being sorted in descending order. If not, then an invalid result will be returned.
  • Read More
17
Sep

Dynamic Array Functions And Formulas - Excel 365 Features
  • David Ringstrom
  • 1545
Excel Dynamic Array Formulas are a new and revolutionary office 365 update. It allows users to work with multiple values at the same time in formulas in an easy way. These dynamic Array formulas can remove duplicates, sort, filter, establish sequences and do lots more. Excel dynamic array update is not available with Excel 2016 and Excel 2019 but you get it with the Microsoft 365 subscriptions supported by Windows and Mac both. The main advantage of Dynamic Array formulas over the normal formulas is, normal formulas return the solutions in a single cell whereas Dynamic Array formulas result takes a range of cells i.e. spills which gives the dynamic effect. Now, any formula returns an array of values automatically spills into neighboring cells, without having to press Ctrl + Shift + Enter (CSE) or do any other moves. CSE formulas are complex ones, and we usually have to guess how many cells we need to copy them to. Example:Now understand the scenario with an example. Suppose you need to multiply two groups of numbers to calculate different percentages. Scenario 1 – In normal version, you get the result of the formula for the first cell only unless you place it in multiple cells and press Ctrl + Shift + Enter to make it an array formula. Scenario 2 – In this dynamic array formula, you need to type in one cell and press Enter key and you will get the whole rage filled with the results at once. Note : Populating multiple cells with a single formula is known as “ Spilling” and the populated range of cells is known as “Spill range”. Excel dynamic array functions6 new functions are introduced with the new array update. Now with the use of these functions users can solve problems in an easy manner that are hard to solve with the traditional formulas. They support the dynamic output means the result update automatically whenever there is any change in the source data. Here are the lists of functions –FILTERFilter a range of data based on criteriaRANDARRAY Returns an array of random numbersSEQUENCEReturns a list of sequential numbers in an arraySORT Sort range by specified columnSORTBY Sort range by another range or arrayUNIQUE Extract unique values from a list or rangeFILTER - This function will allow users to filter a range of data based on different criteria. Without changing the original data, it extracts the filtered record into the spill range. FILTER(array, include, [if_empty])Here in the example Filter returned the desired result and if does not match the specified criteria then show “No Results”. RANDARRAY – It returns an array of random numbers. You just need to mention number of rows and column to fill, minimum and maximum values and what need to return, whole numbers or decimal values. RANDARRAY([rows], [columns], [min], [max], [whole_number]) Here row is “6” to get result in 6 rowsColumn is “4” to get result in 4 columns.Min is 1, this is the minimum value to getMax is 100, this is the maximum value to getWhole_number is TRUE because we need integers.SEQUENCE -Function allows you to generate a list of sequential numbers in an array. Eg:1, 2, 3, 4. Here the result spilled into specified number of rows and columns automatically. SEQUENCE(rows, [columns], [start], [step]) Here we have created 5 rows tall by 3 columns wide. SORT -Function sorts the contents of a range or array either in ascending or descending order. Depending on the shape of the source array results spills to the next cells vertically or horizontally. SORT(array, [sort_index], [sort_order], [by_col]) Here Array is A2:B8sort_index is column or row to sort by 2sort_order is 1 that is ascending orderSORTBY -Function Sort range by another range or array (Sorting Conditions). SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…) Here Array is A2:A10by_array1 is B2:B10 – sort by valuesort_order1 -1 that is descending order. UNIQUE-Function returns list of unique values from a range of cells. Just you need to select the range and hit enter. This is faster And simpler solution. UNIQUE(array, [by_col], [exactly_once])There are additionally two more up-to-date excel functions-1. XLOOKUP - it is more powerful successor of HLOOKUP, VLOOKUP and LOOKUP that return multiple values by looking up both in columns and rows. 2. XMATCH – it is another useful successor of the MATCH function that returns a relative position of the specified item by performing vertical and horizontal lookups. Conclusion:- In the future, maybe more features will be added. But when you are using the existing one, remember to use the right syntax to get the desired result.
  • Read More
09
Sep

How XLOOKUP is Different from VLOOKUP and HLOOKUP
  • Larry Bin, CPA
  • 1622
XLOOKUP a new function recently launched with Microsoft Office 365 will be replacing the widely used VLOOKUP, HLOOKUP and INDEX/MATCH functions to run searches in a table of Excel data. XLOOKUP and VLOOKUP functions in Excel are similar in terms of use to find or ‘lookup’, a value from a table and list and then return a related result. But there are many things which keep them different from each other. Excel users always think that which one is a better function to use. The primary one is, XLOOKUP not supported by older versions of Excel. ( XLOOKUP Overview)How XLOOKUP different from VLOOKUP and HLOOKUP – XLOOKUP has Exact match mode by default but VLOOKUP doesn’t..VLOOKUP works from left to right, if there any value to lookup from right to left it will not work. XLOOKUP doesn’t have this limitation.lookup_array and retun_array are two separate arguments that are similar to how INDEX MATCH formulas work. But with the VLOOKUP you just have the single table_array that contains both the column to look in and the return column.HLOOKUP needs a separate function for horizontal lookup but XLOOKUP can perform horizontal lookup by referencing rows instead of columns for the lookup and return arrays.VLOOKUP formulas are not able to handle column insertions or deletions. You just need to adjust the column index number whenever you try to insert or delete column. XLOOKUP can perform searches from smallest to largest or from largest to smallest but VLOOKUP sort smallest to largest. VLOOKUP required you to input an entire data set, but XLOOKUP only requires you to reference the relevant columns or rows. This way it increases the calculation speed of the spreadsheet.XLOOKUP returns more than one value.XLOOKUP search from bottom to top but VLOOKUP doesn’t.You can set custom message if lookup value not found with XLOOKUP.
  • Read More
03
Sep

How to Create a Pivot Table
  • Fedrica Hanks, Accountant
  • 3547
One of the most powerful features of Excel is “Pivot Table”. You can still do most awesome things with it even if you are unfamiliar with excel. It is like dragging and dropping rows/columns headers to create reports. What is Pivot Table Pivot table is a tool in Microsoft Excel that help to make your worksheets more manageable by summarizing huge dataset with a few clicks in small amount of time. It allows you to extract the significance from a detailed large dataset. Now you understand why the Pivot table is important, so let’s go to see how to create it. How to Create / Insert Pivot Table in Excel Here are the steps which should be followed to create pivot table – 1. Click anywhere in the dataset. 2. Go to Insert –> Tables –> Pivot Table. 3. After that a dialog box appears explaining the data set and asking where you want to create the Pivot table. a) Table/Range : based on your data set, its filled in by default. Excel automatically detect the correct range but you can change it manually if needed. b) Specify the location where you want to create PivotTable. Else a new worksheet is created with the Pivot Table. 4. Click “OK”. 5. As soon as you click on “OK”, a new worksheet is created with the Pivot Table in it. On left, you would see the Pivot Table name with a single line instruction and on the right side there is Pivot Table fields. 6. Choose the field (column) which you want to summarize. After checking this, you will get the columns with the summary of data set. In the Pivot Tabe Fields section, you have the fields and the areas (as highlighted below): Based on the backend data used, Field are created in Pivot Table. Within the areas section, you place the fields and according to that your data updated in the Pivot Table. It’s just a drag and drop things. Drag the field to that area where you want and get the result. Sort You can also sort the data within the Pivot Table. 1. Click any cell inside the field which you want to sort. 2. Right click and click on Sort. Changing Summary Calculation – Either by summing or counting the items, excel summarize data by default. If you want to change the type of calculation that you want to use, follow these steps - 1. Click any cell inside the Sum column. Two-dimensional Pivot Table You can create a two- dimensional pivot table, just by dragging a field to the Row area and Columns area.
  • Read More
25
Aug

Different Data Analysis Functions In Excel
  • Lynn Fountain, CPA
  • 1568
Almost every big and small accounting firm uses Excel as a very powerful data analysis tool. There are at least hundreds of functions in excel which are used for data analysis. We should know how to use the right feature to get right data analysis. Here are some of the simple yet powerful features of excel to use to analyse data. Sort – This feature is used to sort excel data on one or multiple columns in ascending or descending order. Filter: if you only want to display record which meets with the certain criteria then you can use this feature. Conditional Formatting: It helps you to highlight cells with certain color, depending upon the value of cells. Charts: It is very easy to create chart to display more than a sheet full of numbers. Tables: Analyse excel data quickly and easily with the help of this feature. PivotTables: The most powerful feature of excel if PivotTable. It allows you to extract substantial data from large dataset. What-If Analysis – It allows users to try out different values for scenarios for formulas. Solver: It uses techniques from operations research to find optimal solutions for all kinds of decision problems. Analysis ToolPak : It is an add-in program that provides data analysis tool for statistical, financial, and engineering data analysis. Data Model : It is used to integrate data from multiple tables in the current workbook or from the imported data or from the data sources connected to the workbook through data connections. Formulas – Here are some list of formulas that an excel data analyst frequently uses. CONCATENATE LEN COUNTA DAYS/NETWORKDAYS SUMIFS AVERAGEIFS VLOOKUP FIND/SEARCH IFERROR COUNTIFS LEFT/RIGHT RANK MINIFS MAXIFS SUMPRODUCT
  • Read More
19
Aug

How To Do Payroll In Excel - Simple Steps
  • Fedrica Hanks, Accountant
  • 1966
There are several options to carry out payroll but when you are having small-medium business and you need a powerful and cost-effective tool then you should use excel for it. Using excel formulae and functions, it is easy to carry out the payroll process. The Payroll Process includes – Collecting employee data. Evaluating total hours worked. Computing pay and deductibles tax. Here you calculate employee payroll as well as each penny you spend on them. For Payroll management, excel is one of the best tool as it is versatile and easy to use. Excel comes up with several functions that allow for simple calculations within seconds. Why use Excel for your payroll process? Here are few reasons which make to use Excel for payroll. 1. Collecting and Tracking of Employee Data - Excel provides ease and many options in storing and managing employee data. You can store data in alphabetical order, by particular dates and other options. It provides control over how you manage and manipulate data. It has the advantage of not making mistakes in payroll information management 2. Visual presentation - You can represent data visually in excel and this is one of the biggest pros of doing payroll in excel. When you are dealing with figures and calculations, you can represent it in charts, diagrams, or graphs. 3. Accuracy - It’s been a complex process to deal with payroll figures and when you are having higher number of transaction then it also becomes difficult to track and analyze it, but data manipulation is simple with this tool just because of functions and formulas available with excel. It’s not only easy to insert numbers into specific locations but also excel performs automatic calculations. Steps to create Payroll in Excel – Step 1 – Launch Excel blank spreadsheet. Step 2 – In this newly created file, you will store employee payroll information then you need to create some field (column) that holds values. Create column name in the following hierarchy – Column A – Employee Name. Column B - Hourly pay – contains employee’s pay rate per hour without any currency symbol. Column C – Total number of hours worked by an employee in a day. Column D - Overtime rate per hour without any currency symbol. Column E - Total Number of Overtime by an employee in a day. Column F – Gross pay – Payable amount without any deductibles. Column G – Income Tax – Tax payable on Gross Pay. Column H - Other Deductibles - Deductibles other than Income Tax such as EPF, PT, TDS, etc. Column I – Net Pay – Payment received in hand by an employee. Step 3 – After deciding parameters relevant to your business, now it’s time to make entries. Now add the data according to the column with no formula.Step 4 – Gross pay calculation – it is easy to calculate it in excel simply by using SUM and PROD function. Step 5 – calculate Income Tax- in order to calculate it, first check how much percentage of tax your employee pays on the total gross pay. Income Tax is always calculated on Gross Pay. Step 6 – Compute deductions - Here mention deductibles for a particular employee (if any) like premium of health/life insurance, professional taxes etc. Step 7 – Now, finally it comes to Net Pay. It is the amount that comes after all the deductions from Gross Pay. This is how we create Payroll in Excel. If you are not so comfortable with the excel formulas and functions to make payroll management. You can learn to analyze payroll data in excel.
  • Read More

Connect with Us

Copyright © 2024 Excel Accountant. All Rights Reserved.