I have data as shown below and want to summarize the report with sales from few heads like region, product, salesman.
I use Excel 2007 for work, the pivot on data will produce following result
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
and you get the output data like this
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
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.
Copy the second list, goto first blank of first list press Ctrl+Alt+V, check skip blanks and done.
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.
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.
I have wound up the Depreciation & Term loan in brief. Please refer to my earlier post on Depreciation and Term Loan schedule for details
the link to file :
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
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