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

Kashflow logo
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.

About Simon Hurst

Simon Hurst

Simon Hurst is the founder of technology training consultancy The Knowledge Base and is a past chairman of the ICAEW's IT Faculty.



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)
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)
15th May 2013 16:29

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)
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)
16th May 2013 09:59

GetPivotData dates

Hi Simon,

Thanks for getting back to me, however ",E3," didn't work.  There must be a way of doing it in powerpivots, it works fine in with standard pivot tables.

Any other idea's?





Thanks (0)
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)
to penelope pitstop
17th May 2013 15:42



It's still not working, I get an error message stating 'The formula you typed contains an error'. Usually this is as a result of a missing comma "," or bracket ")" in a standard formula however when it comes to power pivot syntax I am totally lost, does not seem intuitive.

Thanks anyway Simon!



Thanks (0)
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)
to mustardtree6
20th May 2013 11:41

Thank you Simon!

Thank you! Thank you! Thank you!!

It's working perfectly now, you are a legend! :)



Thanks (0)
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)
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)