Speed Up Your Work With These Excel Formulas

Excel has been the most trusted software of accountants ever since it was released in 1987. Despite competition from Google Sheets, it continues to enjoy the title of ‘world’s most popular productivity tool’. Even though it is widely used, there are a lot of its functions that are not fully explored. To use it more efficiently as a tool for budgeting, planning and forecasting operations, you can master some formulas that will save you time and effort. Let’s take a look at this using two functions: IF and VLOOKUP. Conditional IF Formula "IF" (=IF) function of MS Excel allows you to test whether a condition is true or not, and returns different results accordingly. The syntax of this handy little function is as follows: =IF (condition to test, what to do if it’s true, what to do if it’s false) For example, suppose you are an accountant trying to work the US income tax for 2018 for a series of (unmarried) clients. Finding out the tax each client would have to pay becomes quicker and easier with this tool. Here, let’s assume people get taxed only for an income above $9,525 at 12%. You can use this to calculate each client’s income tax liability: “=IF(B2>=9525,B2*12%,0)”. For example, if the income in cell B2 is greater than or equal to $9,525, charge income tax at 12 percent of a person’s income; otherwise, charge $0.But as any professional dealing with tax and accounting would know, things are rarely this straightforward. When there’s more than one possibility, nesting IF functions is quite handy. Suppose now that the income tax regime is a bit more complicated: incomes above $9,525 attract tax at 12 percent, but properties above $38,700 attract tax at a higher rate of 22 percent. You could cope with this by nesting one IF function within another:The formula in cell C2, which has been copied down to the other cells, is: =IF(B2>=38700,22%,IF(B2>=9525,12%,0%)) * B2 What this means:If someone’s income is more than $38,700, take the income tax rate as 22 percent;Otherwise, if the house price is more than $9,525 (but less than $38,700, since we’ve already eliminated this), take the income tax rate as 12%;Otherwise, take the income tax rate as 0 percent.For a nested IF function like this, you have to take the values in order: either start with the highest value and work your way down to the lowest, or work from the lowest value to the highest. As you can see, it will become more complicated as you add more values and conditions. Thus, it is recommended to go for VLOOKUP formula. VLOOKUP Formula Suppose now that the income tax regime is more complicated and is charged as follows  That is: Incomes up to $9,525 are exempt from income tax. Incomes between $9,525 and $38,700 are charged tax at 12 percent. Nested IF Function can solve this but it would become complicated if the thresholds or tax rate values are changed. Instead, using the VLOOKUP function will make things smoother in this context. Let’s see how: =VLOOKUP(The value you’re looking up, the table you’re looking it up against, the column number you’re returning) Using this formula, you could calculate the income tax payable on each of your possible purchases as follows: =VLOOKUP(B11,$A$2:$B$8,2)*B11 This looks up the income of the client ($45,000, as held in cell B11) against the lookup table held in A2:B8, and returns the value of the second column in this table (in this instance, 22 percent), which it then multiplies by the client’s income. There are a few important aspects to notice: It’s vital that the income thresholds we’re looking up against are in the first column of the lookup table and are in ascending order. You must make the reference to the lookup table absolute (that’s what the $ signs are for), to make sure that when you copy the formula down it always refers to the fixed table A2:B8.

Top 15 Data Analysis Functions in Excel

If you’ve ever used Excel, then you’ve probably experienced the agony of choosing an incorrect formula to analyze a data set. Maybe you worked on it for hours, finally giving up because the data output was wrong or, the function was too complicated, and it seemed simpler to count the data yourself manually. If that sounds like you, then this Data Analysis in Excel top 15 is for you.There are hundreds of functions in Excel, and it can be overwhelming trying to match the right formula with the right kind of data analysis. The most useful functions don’t have to be complicated. Fifteen simple functions will improve your ability to analyze data, making you wonder how you ever lived without them.Whether you dabble in Excel or use it heavily at your job, there is a function for everyone in this list.1. CONCATENATECONCATENATE is one of the easiest to learn but most powerful formulas when conducting data analysis. Combine text, numbers, dates and more from multiple cells into one. This is an excellent function for creating API endpoints, product SKUs, and Java queries.Formula: =CONCATENATE(SELECT CELLS YOU WANT TO COMBINE)2. LEN=LEN quickly provides the number of characters in a given cell. As in the example above, you can identify two different kinds of product Stock Keeping Units (SKUs) using the =LEN formula to see how many characters the cell contains. LEN is especially useful when trying to determine the differences between different Unique Identifiers (UIDs), which are often lengthy and not in the right order.Formula: =LEN(SELECT CELL)3. COUNTA=COUNTA identifies whether a cell is empty or not. In the life of a data analyst, you’re going to run into incomplete data sets daily. COUNTA will allow you to evaluate any gaps the dataset might have without having to reorganize the data.Formula: =COUNTA(SELECT CELL)4. DAYS/NETWORKDAYS=DAYS is exactly what it implies. This function determines the number of calendar days between two dates. This is a useful tool for assessing the lifecycle of products, contracts, and run rating revenue depending on service length – a data analysis essential.NETWORKDAYS is slightly more robust and useful. This formula determines the number of “workdays” between two dates as well as an option to account for holidays. Even workaholics need a break now and then! Using these two formulas to compare time frames is especially helpful for project management.Formulas: =DAYS(SELECT CELL, SELECT CELL) OR =NETWORKDAYS(SELECT CELL, SELECT CELL,[numberofholidays])5. SUMIFS=SUMIFS is one of the “must-know” formulas for a data analyst. The common formula used is =SUM, but what if you need to sum values based on multiple criteria? SUMIFS is it. In the example below, SUMIFS is used to determine how much each product is contributing to top-line revenue.Formula: =SUMIF(RANGE,CRITERIA,[sum_range])6. AVERAGEIFSMuch like SUMIFS, AVERAGEIFS allows you to take an average based on one or more criteria.Formula: =AVERAGEIF(SELECT CELL, CRITERIA,[AVERAGE_RANGE])7. VLOOKUP=VLOOKUP is one of the most useful and recognizable data analysis functions. As an Excel user, you’ll probably need to “marry” data together at some point. For example, accounts receivable might know how much each product costs, but the shipping department can only provide units shipped. This is the perfect use case for VLOOKUP.In the image below we use reference data (A2) combined with the pricing table to have excel looking up matching criteria in the first column and returning an adjacent value.Formula: =VLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,COL_INDEX_NUM, [RANGE_LOOKUP])8. FIND/SEARCH=FIND/=SEARCH are powerful functions for isolating specific text within a data set. Both are listed here because =FIND will return a case-sensitive match, i.e. if you use FIND to query for “Big” you will only return Big=true results. But a =SEARCH for “Big” will match with Big or big, making the query a bit broader. This is particularly useful for looking for anomalies or unique identifiers.Formula: =FIND(TEXT,WITHIN_TEXT,[START_NUMBER]) OR =SEARCH(TEXT,WITHIN_TEXT,[START_NUMBER])9. IFERROR=IFERROR is something that any analyst who actively presents data should take advantage of. Using the previous example, looking for specific text/values in a dataset won’t return a match. This causes a #VALUE error, and while harmless, it is distracting and an eyesore.Use =IFERROR to replace the #VALUE errors with any text/value. In the example above, the cell is blank so that data consumers can easily pick out which rows returned a matching value.Formula: =IFERROR(FIND“VALUE”,SELECT CELL,VALUE_IF_ERROR)10. COUNTIFS=COUNTIFS is the easiest way to count the number of instances a dataset meets a set of criteria. In the example above the product name is used to determine which product was the best seller. COUNTIFS is powerful because of the limitless criteria you can input.Formula: =COUNTIFS(RANGE,CRITERIA)11. LEFT/RIGHT=LEFT, =RIGHT are efficient and straightforward methods for extracting static data out of cells. =LEFT will return the “x” number of characters from the beginning of the cell, while =right will return the “x” number of characters from the end of the cell. In the example below, =LEFT is used to extract the consumer’s area code from their phone number, while =RIGHT is used to extract the last four digits.Formula: =LEFT(SELECT CELL,NUMBER) OR =RIGHT(SELECT CELL,NUMBER)12. RANK=RANK is an ancient excel function, but that doesn’t downplay its effectiveness for data analysis. =RANK allows you to quickly denote how values rank in a dataset in ascending or descending order. In the example, RANK is being used to determine which clients order the most product.Formula: =RANK(SELECT CELL,RANGE_TO_RANK_AGAINST,[ORDER])13. MINIFS=MINIFS is very similar to the min function except it allows you to take the minimum of a set of values, and match on criteria as well. In the example, =MINIFS is used to find the lowest price each product sold for.Formula: =MINIFS(RANGE1,CRITERIA1,RANGE2)14. MAXIFS=MAXIFS, like its counterpart minifs, allows you to match on criteria, but this time it looks for the maximum number.Formula: =MAXIFS(RANGE1,CRITERIA1,RANGE2)15. SUMPRODUCT=SUMPRODUCT is an excellent function to calculate average returns, price points, and margins. SUMPRODUCT multiples one range of values by its corresponding row counterparts. It’s data analysis gold. In the example below, we calculate the average selling price of all our products by using sumproduct to times Price by Quantity and then divide by the total volume sold.Formula: =SUMPRODUCT(RANGE1,RANGE2)/SELECT CELL

How to Separate First Name & Last Name from Full Name in Excel

You will often receive spreadsheets with usernames. Sometimes these spreadsheets have the first name and the second name in different columns. However, spreadsheets usually contain the Full name in a single column. So what do you do then? Simply follow the options mentioned below to split the user name as first name and last name into two columns.Get First Name And Last Name Through The GUILevel of difficulty: LOWExcel’s Convert Text to ColumnExcel’s parsing concept helps you to split and spread text from one column to multiple columns. We can parse full name into first name and last name using Excel’s text to columns wizard. Follow the steps mentioned below to understand how Text to Columns works:Step 1: Select the column that contains the full name. Instead of selecting an entire column you can also select a range but make sure that you are not selecting text from multiple columns. Additionally, ensure that there are empty columns to the right of the selected range or column. This is an important step as text on adjacent columns will be overwritten if there are no empty columns.Step 2: On your Excel ribbon, select Data and then click the option “Text to Columns”. You will now see the Convert Text to Columns Wizard.Step 3: The first step in this wizard is to select the data type. You have two options. First is the Delimited and the second is Fixed Width. Select Delimited if your data is separated by characters such as commas,tabs, any other special characters or symbols. Select the option Fixed width if the data in your column is separated by fixed-width spaces.Step 4: If you have chosen Delimited, then you would be able to choose the delimiters on the next screen. Available options are as follows:1) Tab2) Semicolon3) Comma4) Space5) OtherIf you select option 5 ‘Other’ it would allow you to type a character in a text box.If you have chosen the option Fixed Width rather than Delimiter, then you would be able to set Column Breaks. To insert Columns breaks in the data preview window, click on the desired position. To delete a column break, double click on it. To move a column break, you can click and drag it to the new position.Step 5: The third screen would allow you to set the data type for the new column that would be populated with the data which is created at the end of this wizard. The default option is General which converts numeric values to numbers, date values to dates, and all remaining values to text. Other available options are Text and Date. You also have an option that does not import the data to a column.Step 6: Press Finish and you will be able to see the First Name and the Last Name in new columns.Get the First Name And Last Name Using FormulasLevel of difficulty: LOWExcel’s Convert Text to ColumnThere are no direct and specific formulas in Excel to convert the Full name into first name and last name. However, by combining multiple excel formulas we can achieve this.To get the first nameUsing LEFT and SEARCH formulas you can build a formula to extract the first name. Below is the syntaxSyntax : = LEFT(CELL ADDRESS, SEARCH("", CELL ADDRESS)-1)Do replace the CELL ADDRESS in the above syntax with the actual value as shown in the example below:=LEFT(CELL ADDRESS, SEARCH("",CELL ADDRESS)-1)According to this example, the full name is present in Cell B2. When we type this formula in the cell C2, it would generate the first name.To get the last nameUsing RIGHT, LEN, SEARCH formulas you can build a formula to extract the last name. Below is the syntaxSyntax : =RIGHT(CELL ADDRESS,LEN(ADDRESS) -SEARCH(" ",CELL ADDRESS))Do replace the CELL ADDRESS in the above syntax with the actual value as shown in the example below:=RIGHT(B2,LEN(B2)-SEARCH("",B2))According to this example, the full name is present in Cell B2. When we type this formula in Cell D2, it would generate the Last name.How does it work?The formula searches for space or comma in the cell’s text. If space or comma is found, all the text found on its left is extracted and displayed as the first name. The last name is displayed by extracting all text to the right of the space or comma.Get the First Name And Last Name Using MacroLevel of difficulty: MEDIUMUsing a macro, you can create user-defined functions. You can then use these functions in your Excel sheets. This will help you attain your goal using a single function. The cell containing the full name should be passed as the parameter to these user-defined functions.

Underrated Excel Functionality Which We May not Use Daily

Excel is incredibly adaptable and these prompt it to the most well-known software available in the market today. This inescapable use is all the more frequently than not prone to prompt circumstances where you need to discover some of the underrated functions used in Excel. In this article, we walk you through the top 5 underrated excel functionality which we may not use daily. Here are the top 5 underrated excel functionality1. Paste SpecialGrabbing (i.e copying) data from one cell and pasting it into another is one of the most common actions in Excel. But there’s a lot that gets copied in a regular grab (formatting, value, formula, comments, etc) and sometimes you don’t want to copy all of it. That’s where Paste Special comes in.To access Paste Special, go to the toolbar (top left on the Home tab under Clipboard), right-click or use the shortcut Ctrl-Alt-V.Once the Paste Special box is open you have loads of options to choose from. Here are my top three:Paste ValuesPaste Values and Number FormatsTransposePaste Values paste the value of the cell you have copied instead of copying the formula. By default Excel copies over formula, but sometimes you just want the end result. Paste Values does that for you.The shortcut for Paste Values is Ctrl-Alt-V-V (then hit enter).Paste Values and Number Formats is the same as Paste Values but this time it brings over the format of the original cell. This is particularly useful when you are copying over dates or a currency but you don’t want to copy the formula from the original cell.The shortcut for Paste Values and Number Formats is Ctrl-Alt-V-U (then hit enter).Transpose allows you to flip rows and columns around in seconds. Turn a row of numbers vertical or vice-versa by simply copying and then using Paste Special – Transpose.The shortcut for Transpose is Ctrl-Alt-V-E (then hit enter).2. Go To SpecialLike Paste Special, this hidden gem opens up a treasure trove of Excel goodies. Go To Special is located on the Home ribbon in the Editing section under “Find & Select” or you can use the shortcut – Ctrl-G followed by Alt-S.Go To Special allows you to select types of cell en-mass. You can use this function to select cells that have comments, formulas, numbers, text, errors, and many more. Once selected you can then apply to format, use it to find errors, or highlight certain things.Your options on Go To SpecialTo give a practical example, we’ll focus on Go To Special, Blanks.Go To Special Blanks allows you to select all the blank cells in a selected area. Say you have a huge column of data with intermittent blank cells that you want to format a different color or delete. You’d select that column, Go To Special, select Blanks and all the empty cells would be highlighted. Right-click on one and select “Delete” and the blank cells will disappear.The shortcut for Go To Special, Blanks is Ctrl-G followed by Alt-S-K (then just hit enter).3. Flash FillExcel developed a mind of its own in 2013. Say you have two columns of names (first and last) and you want to put them together. Type it in manually for the first row and Excel will work out what you mean and do it for the rest. That’s Flash Fill, or Excel black magic as some people say…Flash Fill in actionPlease be aware that this functionality was only introduced in Excel 2013, so is included in Excel 2013 and 2016. If it’s not working for you in those versions then make sure it is switched on in Advanced Options (File > Options > Advanced). Or you can activate it manually from the Data tab or by using Ctrl-E. 4. Use of ApostropheSometimes you type what looks to Excel like the start of a formula. If the first character is +, -, = etc, Excel treats the cell differently and starts looking for cells to refer to. This can be disorienting. Alternatively, you might be listing numbers that start with zero, like a stock-keeping unit – 0000345 – etc. In this case, Excel will remove all the leading zeros giving you 345. How annoying.That’s where our next Excel hidden gem comes in. The apostrophe (‘) is Excel’s solution to displaying data that Excel would otherwise treat differently.With and without an apostrophe – see the difference?One word of warning when using the apostrophe in this way: if you are entering a number, Excel will store that number as text so you won’t be able to run sums etc. on it.5. F2The last hidden gem is so simple you’ll be kicking yourself if you didn’t know it. F2 activates the cell you are in for editing. So no more double-clicking then setting the cursor to the end of the text. F2 takes you straight there.

Excel Formulas For Beginners

Excel formulas make calculating numbers and making sense of large amounts of data simple. By knowing a few key formulas, you can do a variety of actions in Excel that will increase your productivity and decrease the risk of making calculation mistakes. We’ve put together some Excel formulas you need to get started.There are a lot of complicated formulas out there, but a great formula doesn’t have to be complex. In fact, some of the simplest formulas are the most useful and will help you maximize the capabilities of Excel.1. SUM() Function=SUM is a great basic formula to know, especially because it allows you to add up numbers in different ways. Excel easily performs this formula for you, but there are a few tricks to =SUM that provide even more functionality for adding data.First, =SUM can add up entire rows of numbers or just certain cells within a row. Here is what that looks like:=SUM(A2:A9) adds up values in cells A2 through A9=SUM(A2, A9) adds up values in cells A2 and A9But you can take =SUM it one step further and combine it with other math functions too. In this example, =SUM (A2:A9)/5 adds up values in cells A2 through A9, then divides the sum by 5.2. MAX() & MIN() FunctionIf you have a spreadsheet with a lot of numbers, this is a useful formula. With =MAX you can immediately find the largest number in your data set, and with =MIN you can find the smallest.Use =MAX(SELECT CELLS:SELECT CELLS).=MAX(A2:A9)=MIN (A2:A9)3. IF() FunctionWith this formula, Excel will tell you if a certain condition is met. For example, you might need to know which values in column A are larger than 3. Using the =IF formula, you can get Excel to quickly auto-populate a “yes” for each cell that is larger than 3, and a “no” for each value that is smaller than 3. IF this one is not in your top 10 Excel formulas, you are missing out!=IF(A2>=3, “Yes”, “No”)4. TRIM() FunctionIf you copy and paste data into a spreadsheet, there is a chance that the pasted data will be messy. That means it might have extra spaces or hidden characters, and these will mess up formulas because Excel needs data to be clean, without those extra spaces.=TRIM cleans up pasted data so that it is Excel-friendly.In the example below, the animals in cells A3, A6, A8, A10 and A 12 have extra spaces at the beginning. Using =TRIM(SELECT A CELL) will fix it.You can see to the left how the formula has been used in Column D and the extra spaces have disappeared.5. CONCATENATE() Function=CONCATENATE is a useful formula that takes values from multiple cells and combines them into the same cell. This formula saves time and frustration when you need to combine the information in multiple cells into one cell. Instead of doing it manually, =CONCATENATE can do it, in half the time and half the clicks.In this example, Column A has first names and Column B has last names. Using =CONCATENATE(SELECT CELL, SELECT CELL) those cells can easily be combined, which is reflected in Column D, where the full names are now in the same cell. Think of all the copying/pasting this formula just saved!6. TODAY() FunctionYou might find yourself needing to time stamp a spreadsheet each time it is viewed.Instead of typing in the date manually, use =TODAY(). That’s right – you don’t even have to put a value in the parenthesis, and each time the spreadsheet is opened it will update with the current date.7. PROPER() FunctionExcel isn’t just for data analysis; it is also a good platform for organizing and sorting information. When typing large amounts of text into Excel, =PROPER is a great formula to have in your pocket because it converts a cell of text to proper case, where the first letter of each word is capitalized, and the rest of the letters are lowercase.In the example, quite a few names in Column A are not capitalized. Instead of clicking in each cell, deleting the first letter of each name, and typing in a capitalized letter (which is a lot of extra clicks and time), =PROPER does it instantly, as you can see in Column D.Use =Proper(SELECT A CELL) for this formula.8. ODD() FunctionIf you’re working with data that has a lot of decimals, this formula comes in handy. =EVEN rounds a number up to the nearest even number, and =ODD rounds a number up to the nearest odd number. If you’re working with negative numbers, these formulas still work, rounding down to the nearest even or odd number.In this example, Column D is using the =EVEN formula and column E is using the =ODD formula.Use =EVEN(SELECT A CELL) and =ODD(SELECT A CELL) for this formula.9. END OF MONTH() Function=EOMONTH can be used to find the last day of the current month or upcoming months. Instead of going back and forth between a calendar and the spreadsheet, use =EOMONTH(START DATE, 0) . Take this formula a step further and calculate the next month by adding =EOMONTH(start-date, 1).Notice in the example how future months can be calculated by increasing the number at the end of the formula.One more thing about this formula: when entering the start date, be sure to use the DATE function (2019,1,8) is January 8, 2019 so that the formula works currently. If the formula returns a #NUM! error, chances are that the date is not in the correct format.10. RANDOM NUMBER GENERATOR() FunctionA spreadsheet can easily be used to select random numbers using =RANDBETWEEN(SELECT VALUES).Use this formula to choose numbers within data in the spreadsheet, as shown in the example to the left.Another way to use this formula is to choose a winner from a list of 100 names, by having excel choose the winning row.Here are some Excel formulas you must know, including some simple formulas that don’t have anything to with data analysis, in case you’re using Excel to organize information too.

Nested IF Function - How to apply it in your Excel Worksheet

Excel is far more than a mere spreadsheet application that allows you to store and manipulate columns of data. When Microsoft launched their applications, they included the power of visual basic for applications, known as VBA, which allows a user access to a powerful set of visual basic programming commands within their office applications. The if statement within excel is an excellent example of how the power of VBA can transform a spreadsheet into a powerful application.This tutorial will show you how the if statement functions as was as to how to nest if statements to create powerful calculations within your Excel spreadsheet. This tutorial assumes that you have a solid foundation in using Excel spreadsheets. It assumes that you know how to create a spreadsheet, how to add columns of data. It also assumes you have a basic understanding of how to manipulate the data within an Excel spreadsheet. If you want to get a solid foundation you need to follow this tutorial then Excel Courses available on our website will teach all you need to know.Before we begin a practical example of the nested if statement in excel, you need to know how the if statement works, to begin with.How the if the function worksAs per the heading, we’ll refer to this as NESTED IF from here onwards. In brief summary, the Excel NESTED IF function performs a user instructed logical test, if that test is TRUE, it performs an action, if it's FALSE, it does another action.  Learning how to nest IFs will open up your spreadsheet to an advanced level and really expand your horizons!Syntax of this function explained=IF(logical_test,[value_if_true],[value_if_false])This is one of the most useful in Excel.  When harnessed and nested (using multiple IF functions in one), you can have one very powerful multi-levelled formula in ONE cell that performs an astonishing number of logical tests.logical_testAs per Excel, it defines this part of the syntax as “any value or expression that can be evaluated to TRUE or FALSE.”.  This is where you make your test of a cell, see below under Examples for more detail.[value_if_true]As per Excel, it defines this as “the value that’s returned if Logical_test is TRUE, if omitted, TRUE is returned.  You can nest up to seven IF functions”There is a limit of 7 nested functions, but only up to Excel 2007.  Since then, it’s been increased to 64 IF statements in ONE formula.  I’ve yet to get there!  If you start getting into 15 levels and above you might want to revisit your methods in how you create the logical tests.[value_if_false]As per Excel, it defines this as “the value that’s returned if Logical_test is FALSE, if omitted, FALSE is returned.It’s here where NESTING typically begins and we’ll make this clear by looking at a simple example and visualizing how the process tree works when following it through.The IF function is a function that allows you to compare a value and then to manipulate that value depending on the value. The easiest way to understand the IF function is to compare the function to a real-life if statement. If it rains we’re ordering in but if the weather is good we’ll go out. The if function in programming works exactly the same way as our real-life statement. The program evaluates the condition – is it raining – and depending on whether the answer is true or false, the if function then performs a set of instructions.A Nested IF statement is simply an if statement that is declared within another if statement. Using our raining example – let’s add another if by saying that if we are going out then if it’s the afternoon we’ll go for a picnic but if it's an evening we’ll go for pizza. Excel evaluates the statement in exactly the same as we would. It would first check if it’s raining. It would then move onto the next if statement and check whether it’s afternoon or evening. The most important thing about using an if statement in excel is to ensure that the program logic is indeed logical.So let us create an example in excel which we can use to demonstrate how the nested if statement works.

Connect with Us

Copyright © 2023 Excel Accountant. All Rights Reserved.