Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

A Bank Statement Reconciliation template

I would like to find a template for reconciling bank statements with the
formulas already hard-coded.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc


Post your answer or comment

comments powered by Disqus
I would like to find a template for reconciling bank statements with the
formulas already hard-coded.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Hi all,

I have a question about writing a formula. I will describe what i want to accomlish.

I have a bank statement excel sheet and I was to colums combined:

for example:

i have 4 coulmns

C1, C2, C3, and C4
C1 is transaction type (i.e atm, credit, swipe etc)
C2 is Descriptions of the trans type (I.e supermarket, credit card pymnt etc)
C3 is Debit
C4 is credit (range say from row2:row100)

what is want is to calculate only credit for Deposit transaction description which says "payment from work" for example.

how would i do that:

=sum(row2:row100, if c2 says 'payment from work')

basically i want to calculate only the rows that would say payment from work)

please help......

thank you so much!!!

how do i set up a spreadsheet so it works like a bank statement?

I was wondering if anyone knew of a place I can get a bank statement template
for Office 2003

can anyone help,i need to set up a bank statement style worksheet.i need either the formula or if there is a template available
thanks

I've downloade my bank statement to excel and I want to sort my purchases etc..

One column is the amount debited, the next is where the purchase was made. I want to sort all purchases made at grocery stores, or gas statiosn and sum the purchases for each. I can use sort and subtotal, but that doesnt work if I filled up at 4 different gas stations unless I add another function to sum all the totals of each. Im sure theres an easier way.

Im thinking something along the lines of conditional formatting, if cell contains "gas" then sum the cells of the associated column. But im not sure how to combine the conditional formatting with the formula.

Idealy, each month I would like to download my statement directly to an excel template that would easily/instantly sort all my purchase and sum them.

I've attached an example of purchases, perhaps someone could edit it and reattach it to the thread.

Thanks!

I need to pull a few cash receipt amounts out of an enormous list that will
be equal to a depost amount on a bank statement.

I am trying to find an easier way to match a bank statement with a general ledger detail report in order to make sure that the checks balance and clear. Basically I could put information on 1 sheet like the following:

Column A and B are from bank Statement
Col A = Check Number
Col B = Check Amount

Column C and D are from General ledger
Col C = Check Number
Col D = Check Amount

I would like to make it so that they would match side by side and if they did not it would leave a blank space next to it. I am a little familiar with ecel but this formula is a little above my head. Any help would be appreciated.

Thanks

Hi,
I would like to download my bank statement directly to a template. I can very easily download from the website to excel, but then I am stuck with a lot of time consuming formatting, sorting, summing, etc.

I notice there are a lot of options from the Data>Get External Data tab. I am thinking this may be where my answer is but I'm not very familiar with these options.

Thanks

Does anyone have a Temploate that can be used for a Reconcilaiton of Cash or
a Bank Reconciliation.

Hi

I can download statements from my bank (HSBC, for future Googlers) in the following format. Does anyone know what formula I can use to show a running balance in column D (i.e. to the right of 'amount')?
I've tried various things and the only way I've had success is to filter the credits and debits into seperate columns due to the way the formula interprets the '-' symbols...

Thank you!
date description amount 01.12.2011 supermarket -10 02.12.2012 wages 1000 04.12.2011 butcher -5 06.12.2011 child benefit 80

Hello,

I am trying to write VBA code or formulas that look at column A from a downloaded bank statement, and determine if there is either an invoice or customer number within it. The customer number and invoice number are always 8 digits. Invoice numbers always start with 11. Customer numbers 99% of the time start with 15 or 16. and if it find any of this data, to put it into column B... Customers sometimes include both invoice and customer number...

of course, there could be other date or bank data that has 11 or 15 or 16 like 20111510 (October 15, 2011) or a bank reference number like 15111B35492300 which means nothing to me except that it was sent on 15 11 11 (15 Nov 2011).

Bank details do not always have the customer number or invoice number. We are dealing with international wires, or other deposits.

There is no set way the data is received... sometimes the wire only has the number, sometimes the bank tells us their fees, sometimes the customer includes address, etc...

I have tried to search to see whether or not there is another post I could manipulate, but VBA is not my best suite, and I couldn't figure anything out in excel. Seems to be tooo complicated.

Hi all,

I am able to download our bank statement, but in a raw format.

This is the raw data straight from the bank.

1024957901 10/13/2010 11/9/2010 896 852.86
10/13/2010 $**** 4559 Recurring Check Card Autopay/******** 2.52E+09 DEBIT

After working on it “manually”, I can get it to look like this:

Date Description Withdrawal Deposit Balance

155.03
######## Direct Deposit - Payroll $0.00 #,###.68 $#,###.71
######## CHECK 572 REF. NO. 071547503 $30.00 $0.00 $#,###.71
######## POS Purchase Giant Eagle In Greensburg PA $47.61 $0.00 $1,490.10

I use the macro recorder to record my movements.

The number of columns (A1:E1) will always be the same, but the number of rows will vary

The beginning row with the data I need, will always begin with A4.

I can move the withdrawal and deposit amounts to the right columns with If statements. I do the balance with an =E3-E4+E4 statement.

Last, but least I copy all 3 columns down to the last row.

How do I get get the the AutoFill to stop at the right row? Where do I put the variable holding the LastRow? How do I use the variable?

Where/how do I arrange the “FindLastRow” macro? Can I run the "FindLastRow" macro first?

I ran the macro recorder and only performed the Withdrawal, Deposit and Balance.

Here is the end result of the AutoFill:

	VB:
	
Selection.Cut Destination:=Range("O2:O34") 
Range("F2:F34").Select 
Selection.Cut Destination:=Range("P1:P33") 
Range("P1:P33").Select 
Selection.Cut Destination:=Range("P2:P34") 
Range("C2:C34").Select 
Selection.Cut Destination:=Range("B2:B34") 
Range("B2:B34").Select 
Columns("B:B").EntireColumn.AutoFit 
Range("C2:E34").Select 
Selection.ClearContents 
Range("C2").Select 
ActiveCell.FormulaR1C1 = "=IF(RC[13]=""DEBIT"",RC[12],RC[1]=RC[12])" 
ActiveCell.FormulaR1C1 = "=IF(RC[13]=""CREDIT"",0,RC[1]=RC[12])" 
Range("D2").Select 
ActiveCell.FormulaR1C1 = "=IF(RC[12]=""DEBIT"",RC[11],0)" 
Range("D2").Select 
ActiveCell.FormulaR1C1 = "=IF(RC[12]=""CREDIT"",RC[11],0)" 
Range("E2").Select 
ActiveCell.FormulaR1C1 = "=R[-1]C-RC[-2]+RC[-1]" 
Range("C1:E2").Select 
Range("C2").Activate 
Selection.AutoFill Destination:=Range("C1:E30"), Type:=xlFillDefault 
Range("C1:E30").Select 
Range("C3:E30").Select 
ActiveCell.FormulaR1C1 = "=IF(RC[13]=""DEBIT"",0,RC[1]=RC[12])" 
Range("F6").Select 
Range("C2").Select 
ActiveCell.FormulaR1C1 = "=IF(RC[13]=""DEBIT"",RC[12],RC[1]=RC[12])" 
Range("C3").Select 
Range("C2").Select 
ActiveCell.FormulaR1C1 = "=IF(RC[13]=""DEBIT"",RC[12],RC[1]=0)" 
Range("C3").Select 
Range("C2").Select 
Selection.ClearContents 
Range("C2:E2").Select 
Range("C2").Select 
ActiveCell.FormulaR1C1 = "=IF(RC[13]=""DEBIT"",RC[12],0)" 
Range("C2:E2").Select 
Selection.AutoFill Destination:=Range("C2:E35"), Type:=xlFillDefaultSelection.AutoFill 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
How do I use this? "

	VB:
	
Range("C2:E35") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
"

Yes, I know too many questions. I'm sorry!

Please help!

Thanks in advance.

Respectfully,

pctutor@home

Hi,

Is there a formula for an account balance sheet the same as a normal bank statement?, I thought it would be something staight forward but i'm having trouble finding the answers in the forums...

eg.

column A is DATE, column B is DEBIT, column C is CREDIT, column D is BALANCE

And all you do is input the debits and credits and the balance progressively calculates in column D.

Is this possible?

Thank You and sorry if the question is ridiculously simple.

I've currently got a personal finances spreadsheet setup that downloads bank statements and then automatically categorises the balance to e.g. food, drink, travel etc.

The bank statement churns out descriptions in the following form:

'1234 23JAN08 3579 , TESCO METRO 2468

At the moment I have a matrix setup with each category assigned a column, which returns the balance to that category's column if Excel recognises any one of a number of buzzwords.

The below formula is in the supermarkets column. If the text description in D348 contains "Tesco" or "Somerfield" then it will return the balance from E348 otherwise it will return zero.

=-IF(ISNUMBER(SEARCH("SOMERFIELD",D348)),E348,IF(ISNUMBER(SEARCH("TESCO",D348)),E348,0))

It's a very cumbersome way of doing it and I would like to be able to have a much larger list of buzzwords for each category, which is not practical messing around with so many nested IF statements.

Would really appreciate any suggestions as to how I could incorporate a list of buzzwords for all my favourite bars, restaurants, shops etc so that I can automatically analyse my bank statements.

Thanks and apologies for the long post.

Hi,

I would like to know how to go about formulate a formula to lookup info.
with three of this statement and data list.

they all have
Date , Amount, and merchant no. I hard part is that the bank date are late
and there is a fee on the credit card that would link the bank statement and
the accounting data.

it look something like the following;

Bank statement:
amex 06/17/09 590.00
mc/v 06/18/09 490.00

credit card statement:
amex 06/16/09 600.00
amex 06/16/09 -10.00
amex 06/16/09 590.00
---
mc/v 06/17/09 500.00
mc/v 06/17/09 -10.00
mc/v 06/17/09 490.00

accounting data
amex 06/16/09 600.00
mc/v 06/17/09 500.00

best regards

Hi,

I want to extract specific entries from the bank statement to copy them and paste on a separate excel sheet. How can i do this? This I need to do for a specific bill payments has been made. Thanks for your kindly effort.

I am working in Excel 2003 (for compatibility purposes when switching between 2003 and 2007).

The idea to create a banking report in excel for upload into Sage 50 and post transactions based on the narratives in an excel bank statement.

My initial ideas:

Step one:

Bank statement is pasted into sheet one of the excel workbook (we’ll call ‘the importer’ for the purposes of this example). Using VBA I would like the importer to read each transaction line by line and prompt me to confirm the nature of each transaction (using userbox, combobox, or similar).

The combo box should display the transaction and prompt the user for the following: transaction type (bank payment/supplier payment,), VAT Rate (code), VAT amount, nominal account to post the transaction to (if transaction type is a bank payment), or supplier account (if transaction type is a supplier payment), and whether or not to save these selections for next time the same bank narrative is on the statement.

If the option is chosen to save the selections for next time, I want the importer to save the narrative and the chosen criteria to the questions on a separate tab, “Datatab”. This tab will be used to build a database of bank narratives and the corresponding criteria. The idea being that next time this transaction narrative appears on the bank statement, the importer recognises that this transaction type has previously been processed and uses the previous selections to allocate the transaction to the output sheet.

Which brings me to the 3 desired outputs of the programme:

Output 1 – Excel file containing transactions as confirmed by the user in step one for upload into Sage compatible format. This should also contain the transactions allocated automatically using the historic information on the data tab.

Output 2 – Excel file containing unallocated transactions for manual entry into Sage.

Output 3 – Excel file containing a list of all transactions allocated as supplier payments this month.

What I need to know, initially is this possible and what is the difficulty in creating this workbook. What kinds of functions would it entail from a technical perspective? Is there anything I should look out for, and any other advice would be brilliant!

Thank you.

Hello,

When I download my bank statement as a CSV file it doesn't download in an organized manner. Each cell in a particular column (the "Description" column) is full of information that I would like to organize but it would take forever to do it manually.

Here's a few examples of what I see in the "Description" column:

AMERICAN HONDA FINANCE Bill Payment
CHECKCARD 0302 CHICK-FIL-A #01293 HANOVER MD 10013823794
COSTCO WHSE #0 03/19 #000231162 PURCHASE COSTCO WHSE #0032

As you can see, this info is a bit garbled up. I want to make a program that will clean up unnecessary information and can split the contents into separate columns that show the type of transaction and the store/location of the payment like so:

Bill Payment American Honda Finance
Checkcard Chick-Fil-A
Debit Card Costco

Please help! Thank you.

I have only used excel to do a rudamentary personal budget, but I have a
business that uses online banking...can I import data from my online bank
statement into an excel spreadsheet everymonth and avoid having to purchase
something else?

I am trying to run a macro that will repeat several For statements within a For Statement. I have attached my macro. Maybe I shouldn't be using a For statement to repeat this macro. Any suggestions?

Thank you very much!

So here is my problem:

how do you repeat a write statement four time

form 1234 to 1234123412341234

thanks a lot

Hello all,

I have a register that contains historic data in rows. Users need to be able to select any row which will be copied to a separate sheet via a macro button.

I have written the macro to copy specific row data to the new sheet but do not know how to capture the value of the current activerow and use this data in a range statement i.e.


	VB:
	
Range("A4:G4") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This works great but in reality the row number changes depending on user requirements.

Can anyone tell me if it is possible to define a variable to hold the value of ActiveCell.row and then use this variable within a range statement? i.e.


	VB:
	
r=activeCell.row 
Range("Ar:Fr").Copy 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Many thanks in advance

Freddog

Hi,

If I have a column of monetery figures, how can I get the last figure in the column displayed elsewhere on the report. The 'last' figure could be either positive or negative. The report I am working on is very similar to a bank statement, where the balance figure on the bank statement is always displayed at the bottom of the statement in it's own box. This figure indicates if your account is in credit or in the red!!!

Thanks,

Dan


No luck finding an answer? You could always try Google.