Pivot table borders

QuestionEdit

I'm on2003 and I'm using pivot tables to do things such as track expenses, and I'm seeking a solution to the problem of border formatting resetting upon refresh. Specifically, the behaviour I desire in my pivot tables is: 1. To have border lines separating each row of the pivot table, retained upon refresh of the pivot table; and for new row border lines to appear when new items are added to the source data. Note that in Figure 1, border lines are missing between the subitems, whereas Figure 2 has these border lines, exactly as I want. 2. For the border lines separating subitems to be a different colour than the border lines separating major items, as shown in Figure 2, where the row borders separating each item in the first two columns are black throughout the width of the table, while subitems are separated by grey row border lines.

I have looked around the net for solutions to the problem of borders resetting and found suggestions to play with pivot table settings, to "special-select" the column with enable selection on, etc., but the problem still occurs. Some say that it's impossible to keep border formatting, and instead suggest saving and using a macro of the desired formatting as a workaround. However, I've found that retaining borders is, in fact, possible - in some situations. Of the multiple pivot tables I have in a particular file, one of them (the one in Figure 2 of the attached screenshot) does exhibit my desired behaviour in that it retains its border lines even when the pivot table is refreshed, and adds new border lines as appropriate when new rows are added. (However, if I try to replace these borders with, for example, lines of a different colour, refreshing the table causes these new lines to be replaced by the old ones.) All in all, I'm not sure what about this pivot table is different from the other pivot tables, and I've been unable to duplicate my desired behaviour in any other pivot table (even new tables). I had suspected that something to do with cache sharing could be the cause of this difference, but after doing some testing, I think I could be mistaken.

As a side note, an oddity that I've noticed in all my pivot tables is that the pivot table will retain borders which are applied to one cell at a time. Unfortunately, this is not an acceptable solution because upon the addition of new items in the source data, correct cell border formatting isn't applied to the new rows in the pivot table.

I'd greatly appreciate a specific solution that will allow me to achieve my desired behaviour, but any advice you have would be welcome. Thank you for your time.

AnswerEdit

I have created a sample files for you, Click or Copy and paste the below link in web browser to download the Example file.

http://www.sendspace.com/file/mbvt9f The File Name is: Pivto - Example.xls

Scroll the mouse button to the bottom of the website and click Download

http://www.sendspace.com/file/8cub48 The File Name is: Example.JPG

Scroll the mouse button to the bottom of the website and click Download

Have a look in the downloaded files.

Don't select the each range manually and format it. Go through the sample file which i have attached for your eazy reference. Download the sample files and open the Pivot Examplefile and Take your mouse pointer to the starting character of A5 cell, that is 2652 Total, move your mouse pointer towards 26 and you will get a small Right Arrow in Black color just click it. Now all the rows that is subtotal rows of Numbers will be getting selected. Now apply the formatting as per your desire.

Like this select the other pivot area by moving the cursor and selecting the cell by single click and format it. If you apply the formatting in this way and if you add some more records in you master data and extend your pivot data source and refresh the pivot then the formatting which you have applied will not be getting changed. The Same formatting will be getting applied for new data also.

Hope it's clear to you!