Pivot Nugget – Classic Layout & Subtotals

I have data as shown below and want to summarize the report with sales from few heads like region, product, salesman.

Image

I use Excel 2007 for work, the pivot on data will produce following result

Image

however i find it better to have a classic view as i feel it is better presented and less confusing

click inside the pivot table, goto PivotTable tools -> Options and follow instructions as below

Image

and you get the output data like this

Image

but there is still one thing that bugs me is Subtotals, i can get rid of them from  PivotTable tools -> Design -> Layout -> SubTotals

but it removes all the subtotals and directly gives you the Grand total whereas I want to keep region wise subtotals and get rid of product wise subtotals

So click inside the Pivot table goto field list, under the head Row labels Click on Products -> Field settings and select Subtotals None, you get the output

Image

VOILA…..

 

 

 

 

 

Paste Special Skip Blanks

Ever came across a problem, where you have two list with blanks in alternate row and asked to fill all the blanks in first list with the content of second list. When the list is of small size no problemo…can do it manually. As the list grows longer paste special and skip blanks are to help you.

Image

Copy the second list, goto first blank of first list press Ctrl+Alt+V, check skip blanks and done.

Image

You can play with the options under Paste head

I have purposefully used different color and done indentation in second list to see content from different list. you can chose to ignore it.

Financial Model Sample

Learning to make financial model may do well for your career. It requires basic knowledge of MS excel and good knowledge of finance.

The model I have made is a basic one , but here you learn to make profit & loss, balance sheet and cashflow statements. Profit & loss statement are easier to make, whereas Balance sheet and Cashflow statements are relatively difficult to make and understand as well.

As I said earlier the model is basic one, you can add as much as detail you want to make it work for you. It can be in terms of products, variable or fixed cost, tax rates, depreciation, working capital etc.

Image

I have wound up the Depreciation & Term loan in brief. Please refer to my earlier post on Depreciation and Term Loan schedule for details

https://prashant99.wordpress.com/2013/09/24/term-loan-schedule/

https://prashant99.wordpress.com/2013/10/06/depreciation-schedule/

the link to file :

https://drive.google.com/file/d/0B2FLZ9YjqpJTR3J0bHBJTUtuLVU/edit?usp=sharing

 

Excel Trick – Fill sporadic text in blanks in a list

When you have sporadic data in a list and want to fill the blank cells with the data preceding them F5 and Ctrl+Enter comes to your help

Image

for the data as shown above

select the entire data set (Alpha to Total), press F5 -> Special… -> Blanks

you will see all the blank cells are selected

then enter ‘= first text in the list’, in this case cell no. containing “Alpha”

press Ctrl + Enter

All the blank cell will be populated with the text preceding them

Godspeed