You may wonder why I have not written any office related articles recently. Even though there is a special category dedicated to Office Tips on this site, you can see only few articles there. It is not due to time constraint, dearth of inspiration or anything like that. I intentionally opt out of writing office related articles because office related tips and tricks are floating on the web. So I decide, when I write something it should be useful and unique. That was the reason to the long silence from my side.
So here is an Office related article where I am detailing how to create Flexible statements or formats in Excel. Let us begin.
Different types of Office Templates that meet your different needs are available for download online from Microsoft and some other sites. But all time you can’t depend online forms. Sometimes you can’t find a template for specific purpose, some times there is no internet access for search on the web.
But if you follow some simple tips, you can create perfect statement forms in Excel. You can either prepare new statement forms or alter the available template with you.
Before jump to the tutorial first look at the below two sites who provide free templates for your different needs.
Download few templates and play with it. You will surely get an idea how they have prepared these templates. Further you can alter these templates to befit your needs by following my tutorial.
In this tutorial I am going to tell you how to prepare a Statement of Account template or form in Microsoft Excel 2007. If you are using any other version of Excel don’t worry, it will work on them too.
Note : Users of Excel 2007 and 2010, who are new to Microsoft Office Ribbon Interface, can install Microsoft Office Labs’ Search Commands.
Now let us start.
First see the screenshot of a Template which I have downloaded from Office.Microsoft.Com for illustration and tutorial purpose. We are going to prepare a clone of the same in the below tutorial. The advantage, you will learn how to prepare complex statement or templates in Microsoft Excel 2003, 2007 or 2010.
At first look, it seems very easy to prepare and it is right at some extent. But I am following a different method to prepare this statement in order to make them flexible. For example see the limited column width I chose in the second screenshot. It is important to choose limited column width on any statement you prepare to make them perfectly match to your needs.
Steps :
- Open Excel. If there is no start up page already set, definitely you will land on a blank worksheet. Otherwise click Ctrl+N to open a blank work book)
On sheet one that is on the landing page, hit Ctrl+A to select the entire worksheet or entire cell (again shortcut key?, yep it is for version compatibility 
). Now click and drag border or boundary of any column header to left to reduce the total column width. The preferable column width is 2.50 to 3.00.- Type your company name in the first cell (A1). Preferred font size is 20.
- Now go to print preview mode in order to get the print margin and hit Esc. (use Ctrl+P, Preview. If you want to change the default paper size set it too. I use A4 paper size).
- Now you can see the dotted line on your worksheet indicating print border. In the left adjoining cell of the print border type STATEMENT in capital letters and align it right. Preferred font size is 20. Apply Background color and font color of your choice.
- Press Ctrl+A to select the entire cells and then Ctrl+1 to open Format Cell Dialogue box. From Alignment tab set the vertical align to Center.
- Now select cells A4:L8 (Click on cell A4, now press and hold Shift Key and click on cell L8). Keeping the selection, open format cells (Ctrl+1). There from the border tab select a style and click outline. Similarly you can complete the statement form.
- Now see DATE, DESCRIPTION, CHARGES etc. column labels in the second and third screenshot. In the third screenshot I’ve aligned it to center by merging. Merging is one important aspect you need to know to create better statements or forms. Select the cells you want to merge, here for example to merge Date you need to select cells A12 : D13, and go to Format Cells (Ctrl+1)->Alignment->Merge Cells.
Now remove Gridlines (View-> Gridlines), save the file and Voila. The final statement will look like similar to the one below. You can create new template from this. To do this click Office Button->New->New from Existing and select the file from the location you saved.
Recent Comments