In Excel, the default Currency ($ USD) setting is with the dollar sign and two decimal places. As a personal preference, I remove the dollar sign and take the decimals down to 0 places. Here is a quick macro in Visual Basic that will do both at once:
Selection.NumberFormat = "#,##0"
Alternatively, I have one that removes the $, and has two decimal places:
Selection.NumberFormat = "#,##0.00"
Feel free to paste these into VB.
Good tip in the June 2017, Journal of Accountancy By J. Carlton Collins, CPA
Microsoft Excel: How to reference vertical cells horizontally
You may have run across times when you have a sheet of paper with lots of numbers that you need in a spreadsheet.
The long way- hand type the numbers into a new spreadsheet. yuck!
The better way- scan the document (.pdf) and use OCR (optical character recognition) to pull out the numbers.
In my example, I have a Statement of Revenues, Expenses and Changes in Net Position from an audit. But, I only a paper copy.
Step 1- Scan the document. I use a copier with scanning capabilities.
Ideally, you want a copy that’s clean and straight. This will be important for the next step.
Step 2- Use an OCR conversion website. I prefer (Free Online OCR – convert scanned PDF and images to Word, JPEG to Word).
Step 3- Download the results in Excel:
Almost perfect! There is a little clean-up at this point, but it’s in pretty good shape. Look at row 19. There are two lines in one cell. That’s easy to fix and beats hand typing.
Note that I took a .pdf and converted to Excel. But there are many other combinations. Your original could be .jpeg (for ex.), and your output may be Word, Excel, or even .txt.
I’ve also had good success with this website as well:
Convert PDF to Excel Free Online – No email required
On many occasions, I export out reports in Excel. Maybe you do too. Depending on the platform, I get “numbers” are are actually text. You can’t total or really do any work with them in this text form.
Here is a small piece of a much larger document:
The “$” is actually part of the text. We need to remove it. If you only have a few numbers, the tedious way is to go into each cell and edit. Excel will know these are number and then format that way.
But what if you have hundreds? There are two ways that work in this instance. One, do Ctrl H. Search and replace the “$” with an empty field.
Excel will then convert the fields to numbers.
Off to the side type the number 1. Then format that number however you like. I like currency. Now copy the cell with the number 1 in it.
Here is where the magic happens.
You want to:
- Select the area with the text numbers.
Now you have numbers instead of text.
VLookups are valuable when you have data in two places, but need to link the two.
An example in our general ledger might be when you have to report by some alternate account numbers. In the government accounting world, you are sometimes asked to report to a funding agency using their prescribed numbers.
In our sample GL data:
To the right of our example (Column D), we want to put our new account number. In a separate tab named “New Accounts”, we will have all of our account numbers with their related new account numbers. See the layout:
Back in the tab with our GL data, in cell D2, type “=VLOOKUP(A2,’New Accounts’!A:D,3,FALSE)“. Let’s look at the pieces of the formula arguments:
- Lookup_value – This is the common part of both lists of data. In our case, it’s our account number.
- Table_array – This is where our new data is located. For us, it’s the tab “New Accounts”, and we have columns A through D (or A:D).
- Col_index_num – We have 3 here because we are choosing the third column of data from New Accounts. This is where our new account number is located.
- Range_lookup – I always put “FALSE” here. This means it must find an exact match in the new data.
I have copied the formula all the way down, here is the final product:
The next step might be grabbing the New Account Description (the forth column in our lookup data). In cell E2, that formula would just be “=VLOOKUP(A2,’New Accounts’!A:D,4,FALSE)“. The only difference in our formula above is the “4“, instead of “3“.
Once you have connected your existing GL data with the new account numbers, you could do some pivot tables to create income statements, balance sheets, etc.
One very important thing to know about this formula is the “Lookup_value” must have the common value (our account number) as the first field in the data we are connecting. This can sometimes require us to move our columns around.
Using the same example from Text to Columns.
Instead, we will use formulas to break out the different segments of the account string.
Assuming our string is in cell A2. Amount is in B2. We will put our first formula in C2.
The formula we will use is “=MID(A2,1,3)“. The function’s arguments are in three parts. First is the text (A2). Then the starting number, which is 1, then the number of characters. We want three characters because that’s how long our fist segment is.
The result is “100”, which is our first segment. We could do this again to get the next segment. In cell D2, enter “=MID(A2,5,6)“. The difference is we are starting at the fifth position, and we a selecting six digits.
The result is “401000”- our second segment. We could do this for the remaining three parts of the account string. So, why would we choose to do this vs Text to Column? Well, if your data is static, Text to Column is probably the best. But, you may be working with a query from a database that can be updated. In that case, formulas would be the best route.
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
- Paste special
Just that easy!
And an even faster way of doing this is to create a macro in Visual Basic.
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
Application.CutCopyMode = False
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.
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 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”.