Sample Sheet:

– Here, i have shared the sample excel sheet for practice. I guess, you will see the similar sheet in your exam. As, i have blogged these steps after listening the experience of students of CGL CPT 2012, 2013 and 2014. This process has been repeated since 3 years. So, practice before appearing for CPT test.

Steps to create Excel Sheet:

Important Note: I advise you to select ‘Page Orientation‘ in Page Layout tab. It will help you analyze that your print won’t come in 2 pages. This also can deduct 10 marks, if print of your Excel sheet comes in 2 pages.

2. In 4th row, Write ‘Title‘ with font-size of 18 and ‘Merge and Center‘ the cells from A4 to E4 by selecting cells from A4 to E4 and then, go to Home tab and select ‘Merge & Center‘ option as shown in the above figure.

3. Write the contents with Sub-titles as shown in the Sample Slide.– While writing you will need to Wrapping of the text(requires when text exceeds the single cell). For this, go to Home tab and Select the ‘Wrap Text‘ option as shown in the above image.4. Use ‘Wrap Text‘ option whenever the text exceeds the cell. If you don’t do Wrapping of the text, your content may exceeds the Print Out area horizontally.

5. Now, you have add Absolute Function and for this, use formula in fx column as shown in the image above. In this type,

=ABS(B6-C6)

‘=’ sign is important to add before the function.

6. Now, you have to repeat the same function other cells too. For this, just copy the cell (as i have selected the cell D6 written with ’55’) and paste in other cells below. (Refer image for more info).7. For, Percentage formula use

=(C6-B6)*100/B6.

And Repeat the same for other cells too.

8. To use ‘Auto Sum‘ option. First, select the cells to add and then, Go to ‘Formula‘ tab and click on ‘Auto Sum‘ option. Auto Sum will be done automatically with answer in the Blank cell selected.

9.Now, Align the titles of the text to top. For this, go to ‘Home‘ tab and select the ‘Top Align‘ option as shown in the above image.

10. Now, you have border the text all around. This is also important and should be added carefully. Select whole text below the main title (from A6-A11 to E6-E11) as shown in the figure. Then, go to Home tab and select ‘All Border‘ option as i did in the above screenshot.

11.Now, Grey color the rows and column of only Total, Absolute Inc/Dec and Percentage. So, select color the column and rows accordingly.

12. This is the important point. Here, you have to Header and Footer. For this, go to Insert tab and Click on Header & Footer option. You will header and footer is divided into 3 column automatically.– Now, add File path (as shown in the figure) in the left column of header.

13. Then, click on ‘Go to Footer‘ option as shown in the above figure. You will be automatically shift to the footer.14. Now, add ‘Date and Time‘ ,Name and Roll no in the respective Footer columns. (Refer the image above 13th point).

15. I advise in the last must have a Print Preview for your Excel sheet. And make sure, your printout will come accordingly and not on 2 pages or something is not missing.

Get Free Email Updates!

Signup now and receive an email once I publish new content.

I agree to have my personal information transfered to MailChimp ( more information )

I will never give away, trade or sell your email address. You can unsubscribe at any time.