Different Data Analysis Functions In Excel

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

How XLOOKUP is Different from VLOOKUP and HLOOKUP

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

Excel XLOOKUP Function: Overview With Examples

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. Now Understand with the Examples --- Detail Example
Read More

Dynamic Array Functions And Formulas - Excel 365 Features

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 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 function-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 return a relative position of the specified item by performing vertical and horizontal lookups. Conclusion :- In 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

Excel Accounting Latest Tips For 2021

As per the report, 63 percent of U.S. companies rely on Excel, down slightly from a year ago. While the program is used by organizations of all sizes, smaller companies (69 percent) find accounting with Excel especially valuable. Even the American Institute of CPAs has replaced its generic spreadsheet with Excel beginning with the 2018 CPA exam.Excel is the preferred tool for budgeting and planning across accounting and finance functions, according to the Benchmarking report. As one of the CFOs surveyed said, “For anything that is innovative or creative or requires that you bring some gray matter to the table, the spreadsheet cannot be beaten."Here are 13 tips and features to bring more agility, functionality, and usability to your Excel spreadsheets. Share this with your team as you work on upskilling your employees and temporary workers.1. Fill cells quicklyExcel now has the power to finish your tedious info-typing session. Just start typing in the column to the right of your data.Excel will then gather suggestions to fill the rest of your cells after typing a few examples. To accept Microsoft’s suggestions, press enter.2. Format spreadsheets fasterWhen working on a spreadsheet, there are ways to add special formatting to different cells, columns, and rows with less effort.After applying any formatting to the spreadsheet — such as making a cell yellow or adding borders to a group of cells — you can use the format painter function to copy that formatting to other cells. If you double-click on the format painter icon, you can lock the function and single-click on any parts of the spreadsheet that you want to format. When you’re done, hit escape to unlock your cursor.For circumstances when you need to format a large amount of data, Excel offers time-saving shortcuts for many common formatting functions. Experiment with these handy ones:Format numbers to include two decimal places: Ctrl+Shift+1Format as time: Ctrl+Shift+2Format as a date: Ctrl+Shift+3Format as currency: Ctrl+Shift+4Format as a percentage: Ctrl+Shift+5Format in scientific/exponential form: Ctrl+Shift+63. Use Sparklines to display dataSparklines are a built-in feature of Excel that allows you to display small charts inside individual cells. These can be line charts, bar charts or simple win/loss charts. To create a Sparkline chart, select the range of numbers you’d like to include, click the “Insert” menu, then choose one of the chart options. Select a location range, which must be located along a single row or column in the same worksheet as your data range. Sparklines can help you easily display trends in your data in a compact format.4. Splice data easilySlicing allows you to filter data easily.To do so, select any range in a table or PivotTable, (refer to tip #5 for more information about PivotTables), and then go to Insert > “Slicer,” in the top right corner. Then, select the column you want to filter by.5. Manipulate data with pivot tablesWhen you have a large, detailed data set, pivot tables allow you to easily manipulate your data. These tables are interactive and can help you analyze data, detect patterns, and make comparisons. Creating a pivot table is as easy as using the built-in PivotTable and PivotChart Wizard, located in the “Data” drop-down menu. The wizard helps you choose the data to include in your PivotChart and format that information in a meaningful manner.6. Go formula-free Getting a total without an equation might just be one of Excel's most valuable features.To get your total formula, click anywhere inside the Excel table. Then press Ctrl+Shift+T, and Excel will add the total to your total row that you created.7. Move between formulas and resultsTo efficiently switch between the cell data and formula, use the Ctrl+tilde (~) keystroke. This allows you to rapidly check formulas when working in a large spreadsheet.8. Use the status bar without a formulaThe status bar shows counts, sums, and averages, without typing up any formulas.Just select the cells in your table and look to the bottom right of the Excel window. You’ll see instant stats about the cells you selected.9. Hide zero valuesHiding zero values can be helpful within large data sets by allowing you to see data more clearly. To hide zero values, you simply need to change the options in your Excel setup. Navigate to this function by clicking the “File” drop-down menu, and choose “Options.” Then choose “Advanced” from the left-hand menu and uncheck the box for “Show a zero in cells that have zero value.” (Mac users: Go to the “Excel” drop-down menu and choose “Preferences,” then uncheck “Show zero values.”)10. Use Excel templatesTemplates are available for any version of Excel. If you want to create an amortization table using a template, right-click on a blank worksheet and gain access through the “Insert” command. Under the “Spreadsheet Solutions” tab, you will find various templates, including an amortization schedule, which can then be inserted into the spreadsheet. You can also create your own templates by saving a worksheet as an Excel template.11. Chart your data quicklySelect any cells in your data range/ table and then press ALT+F1 on your keyboard. Now you have an instant chart.12. Save time looking for worksheetsIf you access the “File” menu or “Office Button” in Excel — depending on the version you are using — there’s a handy button called “Recent.” Using this function, you can quickly generate a list of the workbooks you recently worked on.In the “Advanced” section of the “Options” menu, you can expand your view and adjust the number of recent documents shown. You can change the number to 50 for better access to recent folders and files.13. Create tables quicklyYou can turn data cells into a table quickly!All you have to do is click within the parameters of your data and press Ctrl+T. Now you have an instant table displaying your data.Now that you have some helpful features to reboot your Excel skills and those of the accounting professionals you manage, you will get more done, and before you know it, you’ll be expanding your team!
Read More

Hiding and Unhiding Multiple Sheets without using Macros

One of the first “tricks” an Excel user learns is to hide and unhide a sheet. This is an exceptionally useful feature as it allows us to store data in a sheet, such as lists and tables, but keep the user of the workbook from seeing, manipulating, and more importantly, corrupting the information on the hidden sheet. As with most things in Excel, there is more than one way to hide a sheet or multiple sheets.  One of the easiest methods is to select a sheet (or select multiple sheets using standard Windows CTRL and Shift selection techniques), right-click the sheet tab then select “Hide”. You can hide a worksheet and the user would not see it when he/she opens the workbook. However, they can easily unhide the worksheet if they want (as we will see later in this tutorial). But what if you don’t want them to be able to unhide the worksheet(s).Let’s see how to hide a worksheet in Excel so that it can easily be unhidden, or can not be unhidden.
Read More

Connect with Us

Copyright © 2021 Excel Accountant. All Rights Reserved.