Converting formulas to values

In many cases when working with your general ledger, it becomes necessary to move data around that have formulas attached. Sometimes, those get broken in the process. In order to “lock” in those formulas values, they must be hard coded. A easy way to do this is by using the copy/paste special feature in Excel.

To do this, select the cells with the formula

  • Right click
  • Copy
  • Paste special

Just that easy!

And an even faster way of doing this is to create a macro in Visual Basic.

Sub Copy_PasteSpecial()
 Selection.Copy
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False
 Application.CutCopyMode = False
End Sub

Deleting rows with no values/or unwanted data

When exporting out a transaction detail or summary from the general ledger, no matter the reporting system, there are usually lots and lots of extra lines that need to be removed.

 

First step is to find a field in your data that you can sort on. In my example, I use the JENumber (journal entry #). I know that sorting on this field will put the total fields up top and at the bottom. The raw transaction detail I need is in the middle.

I delete the yellow lines and I am left with only the transaction details. At this point, I could do a pivot table or subtotals.

Power of SumIf’s (Part 3) – Wild cards

Let’s say that you have list of account numbers and amounts.

Let’s assume that all the accounts that start with 4190 are “Admin Expenses”, and all the accounts that start with 4570 are “Maint. Supplies”. There is a way to summarize these with the sumif formula, and wild cards. Here’s how.

Below our list add 4190??, 4570??, and 4580??. The question mark character is our wild card.

In the C column…to the right of our category descriptions, enter the formula “=SUMIF($A$2:$A$17,A20,$C$2:$C17)

Remember to use the $ in the formula so that when you copy it, it keeps the ranges intact.

The final result…with total added to match our total above.

 

Pivot table – Data in monthly columns

Pivot tables are an amazing tool for understanding large amounts of data…like transaction or summary data from your general ledger.

In this example, we use general ledger data (account number, description, amount, and month). Here is a sample of the data:

Create our pivot table

  • Insert tab> Pivot table
  • Select columns A:D. Your data my be columns and rows like A1:D100 for example.
  • Right click inside of blank pivot table > Pivot table options
  • Display tab. > Classic PivotTable (my preference. Gives you drag and drop).
  • Now you are able to drag and drop the fields into the blank table.
  • Drag “Major” to the far left.
  • Drag “Description” to the right of Major.

  • This adds a total line. We want to remove this. Double click on the “Major”
  • Subtotals & Filters > Check “None”.
  • Now drag “Amount” into the columns field.
  • If it defaults to “Count”, then double-click the “Count of Amount“.
  • Change to Sum. From here you can fix the formatting to currency.
  • To get the months. Drag over the “Month” column to the top of “Total”. Highlighted here in yellow.

After you drop the “Month”, it should look like this:

The finished product. In doing this, you might get rows and columns listed as “Blank”. To remove these, just use the drop down and un-select “Blank”.

 

 

Convert transaction posting date to month

When your transaction data comes out of the general ledger it may be in a pretty raw format. You might get something called a posting date (in a format like 06/30/2016). Let’s say you want to work with the data in a pivot table where you have a column for month.

The month can be easily pulled out this this formula.

06/30/2016 is in cell A1 for example. in A2 you put “=month(A1)”. The result returns 6. This can also be used with day and year too.

6/30/2016 =MONTH(A1) 6
7/1/2016 =DAY(A2) 1
7/2/2016 =YEAR(A3) 2016

Thanks!

Converting GL transaction data – populating lines with account numbers

Let’s say you export out transaction detail from the general ledger. You may want to work with it in a pivot table. But the individual lines are missing the account numbers. They might be above or below the data. Here is a trick to populate the lines.

Here is a sample of our data. Highlighted in yellow is where we want our account numbers.

In cell A3 type “=A2”. Now we want to copy this formula but only in the yellow cells. Not on top of the remaining header account numbers.

  • Copy cell A3.
  • Select the range where you want to paste.
  • Press F5 to launch the GoTo box.
  • Click the “Special…”
  • Click “Blanks”
  • Now you selected just the blank cells.
  • Ctrl V (or paste from the menu).

I’ve changed the area from yellow to blue to show how it should look with account numbers populated.

Text to Columns

In our sample general ledger detail, we get the full string account number. But, we are wanting to work with the individual components of the string (fund, major, etc.). A useful tool for this is Text to Columns.

Our example shows the full string account number and the amount.

In Excel, go to “Data” tab. Highlight just the three cells containing the string. Then Text to Column button. Then choose radius button “Delimited” because we want to use the “-” character to break apart our string.

Put a “-” in the Other.

Then Next>

At this point, you are able to choose which new columns to keep. I have entered $D$3 for the new data. That way it places it to the side of my original list.

I have added the labels above our new columns.

Note- I could have chosen “Text”, instead of “General”. This would have kept the formatting of “001” vs 1.

Power of SumIf’s (Part 2)

In our first post, we looked at the power of SumIf’s. Here, we will look at another way to summarize the same data, but by location code.

We add our three location codes from the data above in Col. A. In Col. B the formula “=SUMIF($A$2:$A$37,A41,$B$2:$B$37)“.  In this example, cell A41 has our location code “001”. Copy/paste the formula.  We add a total to check against the sum of all data.

 

Power of SumIf’s (Part 1)

In Excel, there exists a powerful formula for summarizing data in your general ledger. It’s “sumif”. Here’s a few ways that I use it.

In our sample data, we have a three digit location code, an amount, and the month.

 

Let’s say we want to summarize the transactions by month. Below the data, I will put the months in column C.

 

In column B, beside the month. We add the formula “=SUMIF($C$2:$C$37,C41,$B$2:$B$37)”  The $ are to lock in the columns and rows so that they stay fixed as we copy and paste. The formula is in three parts. The first is the data with the months, second is the month we want summarized,and the third has the amounts.  Then copy and paste down to all 12 months.

Add a total formula to check that the totals match our total of all the data.