Article Listing | Excel Accountant

13

Apr

Excel Formulas For Beginners

David Ringstrom
1238

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.

Read More

06

Apr

How to become an Excel Pro?

Larry Bin, CPA
2513

Without a doubt, an Excel spreadsheet is one of the most advanced tools for working with raw data—and one of the most feared.  The application looks complicated, way too advanced, and like something that would take hours to figure out.I wouldn’t be surprised if upon hearing that you had to start using MS Excel, your heart started to pound. Is there any way to make Microsoft Excel less scary and intimidating? Yes. By learning a few spreadsheet tricks, you can bring Excel down to your level and start looking at the application in a different light.  We rounded up some of the simplest yet powerful MS Excel spreadsheet tips you can start using on your data.Use MS Excel Format PainterSelect the cell with the formatting you wish to replicateGo to the Home menu and click on the Format Painter. Excel will display a paintbrush next to the cursor.Using Excel Format Painter While that paintbrush is visible, click to apply all of the attributes from that cell to any other.To format a range of cells, double-click the Format Painter during step 1. This will keep the formatting active indefinitely. Use the ESC button to deactivate it when you’re done.Select Entire Spreadsheet Columns or RowsAnother quick tip– use the CTRL and SHIFT buttons to select entire rows and columns.Click on the first cell of the data sequence you want to select.Hold down CTRL + SHIFTThen use the arrow keys to get all the data either above, below, or adjacent to the cell you’re in.You can also use CTRL + SHIFT + *   to select your entire data set.Import Data Into Excel CorrectlyThe benefit of using is Excel is that you can combine different types of data from all kinds of sources.  The trick is importing that data properly so you can create Excel drop-down lists or pivot tables from it.Don’t copy-paste complex data sets. Instead, use the options from the Get External Data option under the Data tab. There are specific options for different sources. So use the appropriate option for your data:Importing Data to ExcelEnter The Same Data Into Multiple CellsAt one point, you may find yourself needing to enter the same data into a number of different cells. Your natural instinct would be to copy-paste over and over again. But there’s a quicker way:Select all the cells where you need the same data filled in (use CTRL + click to select individual cells that are spread across the worksheet)In the very last cell, you select, type in your dataUse CTRL+ENTER.  The data will be filled in for each cell you selected.Display Excel Spreadsheet FormulasJumping into a spreadsheet created by someone else? Don’t worry. You can easily orient yourself and find out which formulas were used. To do this, use the Show Formulas button. Or you can use CTRL + `  on your keyboard. This will give you a view of all formulas used in the workbook.Freeze Excel Rows And ColumnsThis is a personal favorite of mine when it comes to viewing lengthy spreadsheets. Once you scroll past the first 20 rows, the first row with the column labels annoyingly disappear from view and you begin to lose track of how the data was organized. Freezing Excel Columns RowsTo keep them visible, use the Freeze Panes feature under the View menu. You can opt to freeze the top row or if you have a spreadsheet with numerous columns, you can opt to freeze the first column.Enter Data Patterns InstantlyOne great feature of Excel is that it can automatically recognize data patterns. But what’s even better is that Excel will let you enter those data patterns into other cells. Simply enter your information in two cells to establish your pattern.Highlight the cells. There will be a small square in the bottom right-hand corner of the last cell. Excel Data PatternsPlace your cursor over this square until it becomes a black cross. Then click and drag it with your mouse down to populate the cells within a column entering Spreadsheet Data PatternsHide Spreadsheet Rows and ColumnsIn some cases, you may have information in rows or columns that are for your eyes only and no one else’s. Isolate these cells from your work area (and prying eyes) by hiding them:Select the first column or row in the range you want to hide.Go to Format under the Home menu.Select Hide & Unhide>Hide Rows or Hide Columns. Hiding Excel Rows ColumnsTo unhide them, click on the first row or column that occurs just before and after the hidden range. Repeat steps 2 and 3, but select Unhide Rows or Unhide Columns.

Read More

01

Apr

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

Larry Bin, CPA
2295

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.

Read More

26

Mar

Repairing Damaged Excel Workbooks

David H. Ringstrom, CPA
3457

There is never a good time to encounter the prompt that informs you that the workbook you’ve opened is damaged and cannot be opened. When this happens, the first thing to do is stop and take a breath. All may not be lost with your file. In this article I’ll lay out a couple things to try before you give up. Y If you see this prompt, the first thing to do is to press Enter to accept the default choice of Repair, or else click Repair with your mouse. This often clears up many issues with Excel workbooks. In fact, I often use this process on files that are not necessarily outright corrupted, but feel like something isn’t right:Within Excel’s Open dialog box click on the workbook that you wish to repair.Click the arrow at the right of the Open button. Choose Open and Repair from the menu that appears.Click Repair from the prompt. 

Read More

18

Mar

Commonly Used Data Analysis Functions in Excel

Allen Smith, CPA
2260

On the off chance that you've ever utilized Excel, at that point you've presumably encountered the distress of picking an erroneous formula to dissect a data set. Perhaps you chipped away at it for quite a long time, at last surrendering in light of the fact that the data yield wasn't right or, the function was excessively muddled, and it appeared to be easier to tally the data yourself physically. In the event that that seems like you, at that point this Data Analysis in Excel top 15 is for you.There are many functions in Excel, and it tends to be overpowering attempting to coordinate the correct formula with the correct sort of data analysis. The most helpful functions don't need to be muddled. Fifteen straightforward functions will improve your capacity to dissect data, making you can't help thinking about how you ever lived without them.Regardless of whether you fiddle with Excel or use it vigorously at your specific employment, there is a function for everybody in this rundown.1. CONCATENATE() FUNCTION=CONCATENATE 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)=CONCATENATE(A2,B2)2. LEN() FUNCTION=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)=LEN(A2)3. COUNTA() FUNTION=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)=COUNTA(A10)4. DAYS/NETWORKDAYS() FUNCTION=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])note: [numberofholidays] is optional=DAYS(C8,B8)OR=NETWORKDAYS(B7,C7,3)5. SUMIFS() FUNCTION=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])note: [sum_range] is optional=SUMIF($B$2:$B$28,$A$2:$A$28,$F2) 6. AVERAGEIFS() FUNCTIONMuch like SUMIFS, AVERAGEIFS allows you to take an average based on one or more criteria.Formula: =AVERAGEIF(SELECT CELL, CRITERIA,[AVERAGE_RANGE])note: [average_range] is optional=AVERAGEIF($C:$C,$A:$A,$F2)7. VLOOKUP() FUNCTIONVLOOKUP 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 this example 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])=VLOOKUP($A2,$G$1:$H$5,2,0)8. FIND/SEARCH() FUNCTION=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])note: [start_number] is optional and is used to indicate the starting cell in the text to search=(FIND(“Big”, A2,1)””)9. IFERROR() FUNCTION=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)=IFERROR(FIND“BIG”,A6,1),“”)10. COUNTIFS() FUNCTION=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)=COUNTIFS($A:$A,$F9)11. LEFT/RIGHT() FUNCTION=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)=LEFT(A6, 3)OR=RIGHT(SELECT CELL,NUMBER)=RIGHT(A6,4)12. RANK() FUNCTION=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])note: [order] is optional=RANK($B7,$B$2:$B$7,0)note: 0 returns the largest value ranked #113. MINIFS() FUNCTION=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)=MINIFS($B$B,$A:$A,$E5)14. MAXIFS() FUNCTION=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)=MAXIFS($B$B,$A:$A,$E5)15. SUMPRODUCT() FUNCTION=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=SUMPRODUCT(B2:B9,C2:C9)/C10We hope you found that useful. If you’re interested in Data Analysis in Excel, take a look at the Excel course that has helped hundreds of thousands of people master Excel.

Read More

11

Mar

Clearing Numeric Input from an Excel Spreadsheet

Allen Smith, CPA
2319

Erasing manual inputs from a spreadsheet that you want to reuse can be a tedious task. Despite our best intentions, entries often get saved into our master copies of spreadsheets that we reuse. Fortunately, you don’t have to go cell by cell to erase inputs. Specifically, we’re going to look at erasing numeric inputs, although you may be able to alter the technique to replace text-based inputs:Select the cells that contain the data you want to remove, which can include numeric inputs, text, and formulas. To select an entire worksheet, click the top left corner of the worksheet frame, otherwise manually select two or more cells.Activate Excel’s Home menu.Choose Find & Select.Choose Go To Special. Choose Constants.Clear the checkboxes for Text, Logical, and Errors, leaving only Numbers selected. Click OK. Press the Delete key to clear the cells but keep the formatting intact. If you wish to completely clear the cells, choose Clear and then Clear All on the Home menu instead.

Read More

Copyright © 2024 Excel Accountant. All Rights Reserved.