Saturday, October 31, 2015

Excel General Ledger Part 3: Dropdown boxes

Utilising the data validation tool in excel, we can create dropdown boxes for our ledger. This can save time keying manually and increases accuracy.

Create a new sheet, name it "chartOfAccounts"

Populate the sheet with account names

Go back to your ledger sheet.

And select the accounts column.

Click on Data > Data Tools > Data Validation

Change the fields as follows:
Allow: List
Source: =chartOfAccounts!A:A

You might want to disable error alert by clicking on Error Alert and unchecking "Show error alert after invalid data is entered"

Now you can select any cell in the account column and a dropdown box will appear.

You can also do something similar for the date column to save time entering today's date.

Simply by creating a new sheet with the formula "=now()" and using data validation steps above.

Excel General Ledger Part 2: Filters


Select your table

Then click on Home > Sort & Filter >Filter

Alternatively, the shortcut is: Ctrl + A + A , then Alt, then A, then T


Triangle buttons will now appear on the first row of your table.

Click on any triangle and you'll see a list of check boxes to filter.

eg. If you want to filter by food, just uncheck "(Select All)" and check "Food".

Excel General Ledger Part 1: Pivot

Populate your ledger as follows:

Date Account Description Amount

Select the 4 Columns and click Insert>Pivot Table>Ok

Look at the PivotTable Field list on the right

Click and drag the "Account" field to the row labels box.

Click and drag the "Amount" field to the values box.

Click and drag the "Date" to the columns field

It should look something like this:

Look at the pivot table and right click on one of the dates

Then click on group and OK.

Tata, the dates should now group by months


Whenever you update entries in the ledger, remember to update the pivot table as follows:

Select any cell on the pivot table, click on options

And click on refresh.

If you want to zoom into an account, just double click the numbers in the pivot table and it will show a breakdown of that account