Excel Tips & Tutorial

Create The Journal and Journal Entry with Excel (Part 2)

2011-12-21T11:50:00.000-08:00

In part 1, you have study how to create a current date and dropdown list of posting reference identifying the number of the account posted. Now we will study how to link Account Tittle column with Posting Reference column. If you are beginner in excel, maybe you will need more times to practice this method.

I will show you how to use VLOOKUP function,based on excel help, VLOOKUP is is a function to Search for a value in the first column of a table array and returns a value in the same row from another column in the table array. OK lets start to begin.

1. Open your last project about….. and select a cell below Account Tittle (ie. The first row on account tittle column). Then type or copy this formula :

=VLOOKUP(B6,TABLE,3,TRUE)

Explanation : B6 is a cell name refers to the first cell on Posting Reference (R/F) column, in this example, the first cell below R/F is B6, so must adjust this function based on yours.

2. Copy down this formula as much as you want, but when you do this, you will see that all the cell will show “#N/A”. Don’t worry, it’s mean that there are no value at PR column. It will automatically filled after you change that value. You can delete it if you want to fill blank cell.

3. Type your Journal ID Number at “Journal ID “ Column. The Journal ID is a your transaction number.

4. Select Debit and Credit Column, change the format cell to Accounting, you can set the Accounting Format from Menu Home -> at format cell dropdown list, select Accounting.

5. To minimalize the mistake from typing a text (debit and credit column receive Number only), you can make Data Validation from Data – Data Validation.Activesettings tab and select Allow: Whole Number, choose Data:Greater than, Minimum : 0. This methods mean that the cell only receive number greater than zero, the text or negative value will unacceptable.

6. To make a warning text, select Error Allert tab. Type at Title: Data Validation, Error Message :"Please Enter Number Greater than 0".

7. Try to give different colour for every column to make it professional.

Create The Journal and Journal Entry with Excel (Part 1)

2011-12-20T12:16:00.000-08:00

A journal includes all accounting transactions and is considered the historical record for a business entity. All transactions must be recorded through a journal entry that provides specific instructions in a line-by-line sequence. In this post, I will show you How to create the Journal and journal entry with Excel.

1. Make a list of your accounts in excel on sheet1, rename this sheet with the name "Accounts". You can read my article about How to Label Your Account in Excel.

2. Open Sheet2 on Excel and rename it as "Journal". Make the format of your journal, a common format usually includes date, P/R: The posting reference identifying the number of the account posted, Account Titles, Debit, and Credit. You can alse add Journal Code Number if you need it.

3. Insert the current date in a "Date Column" or transaction date. to insert the current date, just press "Ctrl ;". You can type the transaction date by retyping the current date.

4. Click the first row on "R/F" column, at a ribbon menu (for Ms Excel 2007 or more), select Data - Data Validation. You will see a new dialog box. At a setting tab, select List and type "=Account_Num" at source dialog. Make sure that Ignore Blank and In-cell dropwdown are active.

Note that "Account_Num" is the name you have been prepared at the Account sheet for Account Number.

5. If you are correct in step 4, then the selected cell will appear the dropdown button and when clicked, it will show the entire list of account numbers that you have prepared earlier. Copy this cell down for the same rule.

Continue reading part 2.

How to Label Your Account in Excel

2011-12-18T15:20:00.000-08:00

In making financial reports, the first thing you should consider is how you manage your account groups. If you are wrong in a group set up an account, then in the making of general ledger or trial balance, you will have trouble. Grouping of accounts will accelerate and simplify the processing financial journal reports at any time.

In this post, I will introduce the basics of classifying and labeling an account with excel for beginners.

Consider that you have 5 different types of accounts, they are Assets, Liabilities, Equity, Income and Expense. In this tutorial, assume that all asset accounts beginning with number 1, so that each assets account will have a number 1xxxx. and liabilities that start with number 2, Equity start with number 3, Income start with number 4, and Expense start with number 5.

1. Type in all your accounts in the correct order, place the account number, account type and account name in different columns. See the picture above.

2. Select the entire account from the first row to the last account (Not including the label), at the upper left corner of your Excel program, by default the name of the table you selected is the first cell (in the example the name of the default is A4). Rename the entire selected table with the name you want, in this example I'm using"TABLE" name. then presss ENTER, See the picture below.

3. Now repeat steps Number : 2 but now you only Select and Rename Accounts Number. Rename it with "Account_Num" or other name that you can remember easily, then presss ENTER.

Note that the this labeling steps will be useful in the next tutorial, so you must familiar in the naming a column or a table in excel.

How to Measure the Area of the Polygon With Excel

2011-12-17T12:36:00.000-08:00

Measure the area of a polygon that is known only corner points is not an easy job. If you find that the actual polygon composed of some form, then you will try to divide the polygon into several small pieces to be a square or triangle so you can easily calculated and combines all the small parts area. Of course it is not a practical way.
For example in the picture above, the corner coordinates of the polygon are (1,1), (1,3), (3.5), (5,3) and (5.1). We can suppose that the polygon is composed of two forms. The first form is rectangular and other shapes are triangles. Therefore, the actual area of the polygon is

Total Area = Area of Rectangle + Area of triangle = 8 + 4 = 12

But what if the polygon is composed of many forms, Do you want to count one by one and then summing the breadth? Here I will show how to calculate area of polygon with excel (regardless of the number of vertex) if the corner coordinates of the polygon are available.

1. Open New Excel Document and type in all the coordinates of polygon corner with separate columns in X and Y. Note that you have to sort the coordinates in sequence clockwise or counterclockwise. You can choose which coordinates will be written fot the first, in this example I mark the lower left coordinates as the first coordinates.

2. Rewrite the first coordinates and place it in the bottom of your table. see the pictures below.

3. Multiply the first row of column X with the second row in column Y. The results are put in a new column next to it. Suppose this new column name is X1.Y2. Copy the formula in cell next to the bottom for a five row or the same as the number of polygon corner (I assume you can copy the formula in a cell).

4. Do the same as in step 3, but this time multiply the first row of column Y with the second row of column X. name the new column as Y1.X2. Look at the picture.

5. Sum all the number in each column. In this example, the sum in column X1.Y2 is 27 and in column Y1.X2 is 51.

6. Calculate the difference of two columns and divide the result by 2 (Area must be marked positive, neglect the negative value). In the form of a mathematical equation:
L = (51- 27) : 2 = 12

The result is the same with the manual way.

Tips: How to Copy a Column That Contains a Formula

2011-12-16T23:32:00.000-08:00

In working in Microsoft Excel, sometimes we want to copy a column that contains numbers, where the numbers are the result of a calculation of the other columns. When we tried to copy it to the other columns, it does not appear the original, but a row contains of zeros.
Look at the picture.

From the picture, column C (result) is the product of columns A (Num A) and column B (num B). Then when we try to copy the entire results of column C to column F, then emerges is a row of zeros, This happens because you are not just copying the numbers, but also copy a formula that is contained in each cell of column C, so the formula is brought into column F. So in column F, the formula is formed automatically, as an example for line 2 "= D2 * F2". So in column F, the formula is formed automatically, as an example for line 2 "= D2 * F2". Whereas in column D or column E does not contain the number that corresponds in columns A and B.

If you take a shortcut by re-typing all the numbers in column C one by one, of course it would be troublesome, or you try to copy all the numbers in columns A and B to equalize the condition, know that it's not a good solution.Follow the simple steps below. :

1. Copy the entire column that you want to copy (in the example above is a column C). Press Ctrl + C as a shortcut.

2. While copied column appear a dotted line, open the Microsoft Word program.

3. Now Paste the copied column to New Microsoft Word Document. Then all the data contained in Microsoft Excel is now in Ms Word.

4. Select all the data table in MS Word and then press the copy (Ctrl + C). To select it, press the plus arrow sign in the upper left corner of the table. see the picture below!