Formulas for Success

As a long-time Excel user, I have learned a few tricks that have helped me in my career.

Excel is one of the best resources available to summarize any type of numerical information, and being proficient in Excel will greatly increase your productivity and get you noticed at work. Knowledge of Excel formulas translates to a quicker turnaround time in preparing, auditing and reviewing spreadsheets, and more time for you.

Below are some of the best Excel formulas for analyzing data that will impress your colleagues (and your supervisors).

 

EXTRACTING DATA

Have you ever downloaded data into a spreadsheet and been unable to add the amounts or sort the data by invoice number, customer number or date? Learn how to separate, sort and add data by checking out the examples below.

Extracting elements of a cell is most often used when one has a data file that needs to be “manipulated” to obtain the relevant information. An example of data within a cell is “1511INV120329 134555.34,” where the first part represents the customer number “1511”, the second part the invoice number, “INV120329” and the last part the invoice amount, “134555.34”.

Left and right. The “Left” and “Right” formula extracts the customer number to the left and the amount to the right. With the customer number and amount separated into individual cells, you will be able to provide your audit supervisor with the total amount due from a customer. Why not impress your supervisor and calculate the accounts receivable total at month end?

Use the formula “=LEFT(A5,4)” (where “A5” represents the cell containing the data and “4” the number of characters to extract from the left). This will get the customer number “1511” (in this case, always four digits).

Now use this formula — “=RIGHT(A5,9)” (where “A5” represents the cell containing the data and “9” the number of characters to extract from the right). This will get the invoice amount “134555.34” (assuming in this case that the invoice cannot be greater than $999,999.99).

Mid. The “Mid” formula will help you separate the invoice number from the customer number and amount. Next time a customer contacts you with information about their invoice, you will be able to find the amount due within seconds. This will help turn a messy spreadsheet into a well-ordered and valuable report.

Use the following formula — “=MID(A5,8,6)” (where “A5” represents the cell containing the data, “8” is the position of the first character to be extracted and “6” the number of characters to extract). This will get the invoice number “120329” (in this case, the invoice number is always six digits).

 

JOINING DATA

Joining data in Excel is useful for combining two cells in order to have all the data in one cell. The formula is really handy, especially if you have a first and last name in two cells and want to combine them into just one cell.

“Concatenate” joins several text strings into one text string. Use the formula “=CONCATENATE(A5,B5,C5)” (where “A5” represents the first cell containing the data “Accounting”, “B5” the second cell “Today” and “C5” the third cell “Rules”). This will join the words together to get “AccountingTodayRules” in one cell. Don’t let the fact that it’s a function make you nervous; it’s not as complicated as it sounds. A shorter formula is “=A5&B5&C5”.

 

SUMMING DATA

The “Sumif” formula is used by many people who need to sum information based on a certain criteria. The formula will look for the specific criteria and then sum the requested column. The formula is a little tricky but incredibly useful as illustrated in this example.

Sumif adds numbers in a range of cells based on given criteria. Using the formula “=SUMIF(B2:B18,B21,C2:C18)” where “B2:B18” represents the range of cells you want evaluated (in yellow in the accompanying graphic), “B21” represents the condition that defines which cells are going to be added (the category number in the graphic) and “C2:C18” represents the range of cells that will be added (in green).

Building proficiency with Excel formulas and functionality takes time. I recommend you practice the examples above several times and you’ll see how quickly you’ll improve your productivity.

Shannon Davimes, CPA, is the founder of Excelabc.com and Excel911.com. Follow him on Twitter at @ExcelAbc.

For reprint and licensing requests for this article, click here.
Audit Financial reporting
MORE FROM ACCOUNTING TODAY