This is Volume 2 in our popular series of best excel tips and tricks for accountants and bookkeepers. Again, these are items that I find valuable in my every-day use of MS Excel.
The “End of Month” Formula
Us accountants like to deal in months. However, data comes to us in lots of different formats. If you run a detailed transaction report, each transaction would have a date. Running a report for a long period of time like a year might give you all the data, but then it isn’t broken out by month. How to solve this quickly? I like to use the “EOMONTH” formula. You basically feed this formula a date and then tell Excel if you want to add or subtract months, and it will give you the last day of the month as a result. Here are some examples:
This is a quick and easy way to find the month that a random set of dates fall into. From there you can use the…
This is probably my most used formula. I regularly use SUMIF to find a value or aggregate data from a dataset into months, quarters or years. In the prior section, we used EOMONTH to find the end of the month that a date falls into. Instead of manually adding up the months, or doing sum ranges, you can use SUMIF. The SUMIF formula says: Look for a value in this data range and return the sum of any values that match. There is a range, a range criteria and a sum range. Here is an example of sales by date aggregated into month using both the EOMONTH and SUMIF formulas:
The EOMONTH finds the end of the month for the date and then SUMIF totals that month based on the heading variable at the top of the sales by month section. This is a go-to formula for lots of reasons.
As accountants, we’re used to dealing with data that comes out of different systems. For example, your payroll system may have employee’s names formatted one way (like: first middle initial, last) while your equity compensation system might have the names in its reports formatted a different way (like last, first, middle initial). These two reports can be a real pain to reconcile if you don’t know a couple tricks. First, let’s use concatenate. The formula is =CONCAT(__, ___, ___) where the blanks are replaced by cells or static text. This joins the data together into one string. Here is an example:
As you can see, I’ve added a comma and space after the last name and added another space after the first name and before the middle initial. You can do this by putting whatever static text you want to join into the string between quotation marks. This formula is very good for names (think about data for mail merges, etc) but can be used for lots of other things as well.
You can actually use the “&” symbol as well. So =C2&”, “&A2&” “&B2 will give you the same result as =CONCAT(C2,”, “,A2,” “,B2)
Pro Tip #2:
Once you have the formulas set up, if you want to use the data string, you can copy and paste the values of the cells into a new column or row so you can use the data more freely without the formulas getting in the way.
Text to Columns
If you want to do the opposite of Concatenate, then the Text to Columns feature might come in handy. This isn’t a formula so much as it is a step that you can take to break up data into multiple columns based on different selections. Here is how it works:
First, you highlight a data set. We’ll use the concatenated names (last, first mi) from the last example. We will split this back out into multiple columns.
Then, click the “Text to Columns” button in the Data tab.
Then the wizard will appear and walk you through the steps. First you need to decide if your data is Delimited (the data should be separated into multiple columns based on a certain character (like a space or a comma) or whether the data has a fixed width (like a five digit zip code might be a fixed set of digits). In this case, our data is not fixed width, it is delimited.
Then, we can select what delimiters to set up – this is the “break” that excel will look for to start a new column with the data. In our case, we’re using space and comma, and we’re telling excel that if both are together, treat them as one delimiter (see the checkbox).
Most of the time, I hit “Finish” button on step 2, but Step 3 allows you to format the data in each column if you want to. I usually just end up doing any formatting I want after the text to columns works.
After you press finish and the function is completed, the text is separated out into multiple columns.
This is great to help with data manipulation, or if you have to copy something into excel from a PDF file and it puts it all into one column rather than a table of data.
Try these tricks on your next excel file – I hope you find them helpful. If you are looking for other ways to automate your processes and take your team to the next level, try AP genie. Our automated system will save your team time and money, while improving controls and efficiencies within your whole company. We have a 30-day no-risk free trial – sign up here.