Tutorial: Create a flexible P&L in Excel 2010/2013

Kashflow logo
Simon Hurst
The Knowledge Base
Share this content


In the first part of this series we looked at how some of the changes in Excel 2010 and 2013 have given us new tools for working with accounting data. We showed how the inclusion of the PowerPivot ‘Data model’ within Excel 2013 itself could replace the use of Excel Lookup formulae to give easier, more automatic and potentially more reliable results. For inspiration, we used a six year old article by David Carter on working with accounting data in Excel entitled: Tutorial: Create a P&L from monthly balances with a pivot table.

Having processed our data through the Excel 2013 data model, we ended up with our profit and loss report. This time we’ll look at the various things we can do with our PivotTable in the latest versions of Excel...

Further reading and tutorials

Please Login or Register to read the full article

The full article is available to registered AccountingWEB.co.uk members only. To read the rest of this article you’ll need to login or register. Registration is FREE and allows you to view all content, ask questions, comment and much more.


Please login or register to join the discussion.

25th Apr 2013 13:07


Hi Simon

I really really like these excel tutorials,

but I wish they were all in one need book,  I'm still on 2007 ,

can we  a book, beyond the basics, with pivot tables and sage links,

I don't have time to trawl AW looking for the bits I like, give me a book to work through



Thanks (1)
27th Apr 2013 11:19

You wish has been fulfilled

Thanks for the comment and kind words, Jean. I think I can help with a few suggestions. First, if you look at the "Tags" section just below the end of the article and click tutorial or Simon Hurst, you should find some quite useful tutorial selections. 

There's also a tag for Excel 2007 material and PivotTables, while I should also recommend David Carter's tutorials, which go into Sage reporting in some detail.

But if it's a book you want (and I know what you mean about having a separate document to work through rather than working from a screen), Simon has already answered your request with Maximising the Impact of Accounting & Financial Spreadsheets for Finance Users, available from FSN for £24.99.

If that title doesn't grab you, try Advanced Excel Techniques for Accountants, an ebook available from Simon's website.

That lot should fuel your Excel learning for some time - and don't forget to get our monthly Excel Zone bulletin, as we'll be featuring more tutorials from Simon in the months to come.

Thanks (0)
By shurst
29th Apr 2013 08:43

Thanks Jean and John. Just back from a couple of days holiday, so apologies for not responding earlier. I'm glad you like the tutorials Jean, and hope you continue to enjoy them in the coming months. Thanks for the mention of the book (the title wasn't my choice - honest!) and the other resources John.

Thanks (0)

Getpivotdata - DATES



I am trying to insert a dynamic cell reference into my getpivotdata formula however I keep getting an error, it works find on a text field however when I try an link it to a date, I get an error. 


As per the example below, I have text referenced date highlighted in bold.


=GETPIVOTDATA("[Measures].[Sum of SALES VALUE]",PIVOT!$B$11,"[ACTULAS].[Month]","[ACTULAS].[Month].&[2013-02-01T00:00:00]","[MAPPING].[ACCOUNT]","[MAPPING].[ACCOUNT].&["&$A5&"]")


When I try and link it to a cell reference it errors out.  The cell reference is a date format   e.g.  01/01/2013.


=GETPIVOTDATA("[Measures].[Sum of SALES VALUE]",PIVOT!$B$11,"[ACTULAS].[Month]","[ACTULAS].[Month].&["&$E3&"]","[MAPPING].[ACCOUNT]","[MAPPING].[ACCOUNT].&["&$A5&"]")


Any idea how to comate this other than changing every text filed manually. 





Thanks (0)
By shurst
15th May 2013 17:59

GetPivotData dates

Hi Darryl

Assuming that E3 contains a valid date, would the following work?

=GETPIVOTDATA("[Measures].[Sum of SALES VALUE]",PIVOT!$B$11,"[ACTULAS].[Month]",E3,"[MAPPING].[ACCOUNT]","[MAPPING].[ACCOUNT].&["&$A5&"]")

Thanks (0)
By shurst
16th May 2013 15:51


Ah, sorry - I hadn't realised you were using PowerPivot - that would explain the strange syntax. I found this on a forum:

=GETPIVOTDATA("[Measures].[Sum of SALES VALUE]",PIVOT!$B$11,"[ACTULAS].[Month]","[ACTULAS].[Month].&["&text(e3,yyyy-mm-ddThh:mm:ss")&"]","[MAPPING].[ACCOUNT]","[MAPPING].[ACCOUNT].&["&$A5&"]")

I'm sure I found an easier way a few months ago but can't work it out now!


Thanks (0)
By shurst
17th May 2013 16:47

Missed a double-quote

Sorry Darryl, my fault - I should have checked more carefully, I think I missed a double-quote in the text function:

=GETPIVOTDATA("[Measures].[Sum of SALES VALUE]",PIVOT!$B$11,"[ACTULAS].[Month]","[ACTULAS].[Month].&["&text(e3,"yyyy-mm-ddThh:mm:ss")&"]","[MAPPING].[ACCOUNT]","[MAPPING].[ACCOUNT].&["&$A5&"]")

Thanks (1)
By shurst
20th May 2013 17:54


Really glad it worked, and thanks for letting me know, and for your comments.


I should have got it right first time.

Thanks (0)
02nd May 2014 17:39

Excel 2010 and Cube

Really enjoy reading your articles! Very useful. However, I cannot understand how to insert a "total" line under revenue, as shown in your "Cubes" section. I am using Excel 2010 with PowerPivot add-in. Thank you. 

Thanks (0)
By shurst
21st May 2014 10:53

Total rows

I'm sorry MariaMaria - I've only just spotted your question.

Thanks for the kind comments.

Once the PivotTable has been converted to Cube formulae the cells behave like any other Excel cells, rather than having the restrictions of a PivotTable, so you can insert rows and columns as you wish and put whatever formulae you like in them - such as a simple SUM() to create a total.

I hope it works for you.

Thanks (0)