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.


1
Create a new sheet, name it "chartOfAccounts"


2
Populate the sheet with account names

3
Go back to your ledger sheet.

And select the accounts column.

4
Click on Data > Data Tools > Data Validation


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

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


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

8
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

1

Select your table

Then click on Home > Sort & Filter >Filter

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


2

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

1
Populate your ledger as follows:

Date Account Description Amount

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

3
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:

4
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

5

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.

6
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

How to download Youtube thumbnail picture

To download Youtube thumbnail image- Google Chrome Users: 

1
Ctrl+U 

2
Ctrl+F 

3
type og:image 

4
then copy link in Url Bar.

Merge and combine CSV files using command prompt

Just need to share this out, it lets you combine many csv files into one instead of manually copy and paste in Excel.


1
Open command prompt

2
Navigate to your folder using the following commands:

cd "<folder>"

eg.

cd C:\folder\

3
Then enter the following command which will combine all the csv files in the folder into one giant csv file:

copy *.csv combine.csv

* symbol means all

combine.csv is your destination file and you can name it anything you want.

Batch file renaming using power shell

About batch renaming of files, this code enables you to change things like eg. all spaces to underscore "_", or even roll forwarding years such as changing all "2014" to "2015".

1
Open windows power shell

2
Navigate to your folder using the following commands:

cd "<folder>"

eg.

cd C:\folder\

3
Then enter the command which replaces all the space with underscore "_"

Dir | Rename-Item –NewName { $_.name –replace “ “,”_” }

You can also customise accordingly. The following replaces all 2014 with 2015:

Dir | Rename-Item –NewName { $_.name –replace “2014“,”2015” }