CASE STUDY ~ ACCOUNTING: Creating a P&L Pivot Table Report

MyExcelOnline John Michaloudis
A free video tutorial from MyExcelOnline John Michaloudis
Chief Inspirational Officer at MyExcelOnline
4.4 instructor rating • 10 courses • 77,256 students

Lecture description

Insert a Calculated Field in your Microsoft Excel Pivot Table - Create a Profit & Loss Pivot Table report

Learn more from the full course

Pivot Tables, Power Pivot Tables, Power Query & Power BI

5 Course Excel Bundle: Microsoft Excel, Pivot Tables, Power Pivot Tables, Power Query & Power BI (Excel 2007-2019, O365)

18:07:15 of on-demand video • Updated October 2020

  • Become more PRODUCTIVE at using Microsoft Excel which will SAVE YOU HOURS per day & ELIMINATE STRESS at work!
  • We teach you how to use Microsoft Excel with CONFIDENCE that will lead to greater opportunities like a HIGHER SALARY and PROMOTIONS!
  • Increase your Microsoft Excel SKILLS and KNOWLEDGE within HOURS which will GET YOU NOTICED by Top Management & prospective Employers!
  • COURSE #1: Learn ALL the Pivot Table features: Slicers, Pivot Charts, Filtering, Grouping, Sorting, Conditional Formatting, Macros, Dashboards, Data Analysis, GETPIVOTDATA, Calculated Fields, Formatting plus MORE!
  • COURSE #2: Learn Power Pivot, DAX Formulas, Measures, Calculated Columns & Table Relationships!
  • COURSE #3: Learn Power Query (Get & Transform), import & transform your data, consolidate from multiple workbooks, folders, worksheets plus MORE!
  • COURSE #4: Learn Power BI and create interactive Dashboards that tell a story about your data!
  • COURSE #5: 333 Excel Keyboard Shortcuts explained, covering Formatting, Ribbon, Formulas, VBA, Pivot Tables, Power BI plus MORE!
  • 7 COURSE BONUSES: Excel PDF Cheat Sheets, 101 Ready Made Excel Templates, plus MORE!!!
  • ADDED BONUS: This entire courses can be DOWNLOADED for offline viewing on the Udemy mobile app (Android & iOS)!
  • DOWNLOADABLE Excel workbooks so you can keep on your desktop & practice!
  • Test your skills with various real life Quizzes & Homework Practice Activities & Assignments!
  • Personal Excel support from Professional Excel Trainers with 30+ Years of combined Excel Training!
English [Auto] In this chapter when you create a peer now where we show you the revenue cogs gross profit expenses and then get our operating profit over the top months and we're going to do is add in a trend line by inserting some spotlights. And also we're going to put in a slicer to see the different years now will then include calculated items in here and the color items the gross profit. So the calculation will be revenue minus COGS. And then the second calculated item is down here which is operating profit which will be the calculation gross profit minus expenses. So we have our data in here and we have our months going down on the month column. We have our different year's we have a different plan all types separate cogs expenses and revenues and they separate into the different items. So we have different expense items as you can see in a normal business and we'll have the actual values and the planet values. So this is a typical pair now that you find in most businesses. Now I mean here we can create a pivot table. We go to insert and pivot table and we'll put into a new worksheet and press okay in the Row Labels we're gonna drop in the panel Time and then we can drop in the item at the bottom and then on the column labels we're putting our months and in the values area we're putting the actual dollars. Let's close these and we have a pair now taking shape. Let's just make a few design changes. We choose this pivot type of design get a view and get rid of the red lines so we can reduce this to about 80 percent. Now the grand total we don't want that we just click on Grand Total right click and then remove grand total. So we have our panel here. Now let's click on COGS and we can actually bring the revenue up there. But just typing in revenue are Ari vague. And you see it gives us the revenue option and then press tab. So you automatically move to revenue from the bottom to the top. We have our COGS and that's fine. Let's minimize that. And now we have the expenses that's what we do now is get the gross profit. So let's click anyway in our peer mail type item and then go to the options and field items and sets in here which is calculated item. Now the capital item I'm going to do is gonna be called gross profit. So it's going to be revenue minus COGS. So the name is of gross profit. And the formula we click that backs basically what to zero. And then we're gonna get the revenue double click the minus sign and then double click on COGS. I don't press okay. And you can see it's added it down here. Gross profit now is calculated all the different items that belong in the revenue. I wouldn't want that. So what we can do is the actual values are within the subtitles in here. So let's minimize gross profit let's go up here and then we can just click on that and grab it and just put it in there. Let's right click and show the full list. And from the values area let's just format the numbers so you put in there a comma. There's just no format and then No no there's more places. I use a thousand separator and put a negative red font there. OK. So we've entered our first calculated item called gross profit now going to add in our second calculated item and it's gonna be cold operating profit. And it's going to be the calculation of gross profit minus expenses. So firstly with putting our cursor in one of the items within the field name called penalty type anyway here in the blue area we can choose and then go to Options field items and said catered item never dropped any. So we have the gross profit to create a new ticket item I want to call it operating profit in the formula listed rid the zero. And then in the items we have the gross profit which was the calculate item that we created earlier this double click there and then press minus and then double click in expenses and then press. Okay. And we'll say we have the different calculations for the operating profit namely our operating profit. Here we have the expenses being deducted from the gross profit amount. Okay. So now let's go to the operating profit now. We don't actually need these numbers here that I mean anything to us. We just made the subtotal there. So let's minimize operating profit and then go all the way up. Now one thing is less clear where the grand total that will go to design Grant hurdles and off four rows and columns and let's select the months there and right click and column with plus choose 12. Now in here we're going to put in our trend. So we're going to put in some spotlights to say our months and how they're trending. So let's try being trend and we can click there and format the painter and just branding. Now one thing let's make this centered. Okay and we can just format that there. OK so Mel is going to insert and spotlight. Let's choose a column for our subtitles. The data range will be January to December. Press enter and then press okay. So we have this background this is a little bit bigger. Now let's choose a different color. We just go. I like Halladay. And then under the multicolor color for the high points one are red. So want to see the highest points. So we'll see here that our fifth month was the highest point. Now we're going to do is control copy and highlight this area holding the control key and then highlight the operating profit lack of control key. And now just press control of it and it feels in the spotlight for the respective subtitles. Now when I put in a spy in here so go into insert and let's put a line dot arrange gently December press enter and then okay. Now once again the color will go blue color and the high point will make it red again. Control copy select that the control key down and then select the other ranged like oh everything now control vague. So we have entered the different and say now. Last thing I want to do is put in a slice on the top here so controlling it so clicking on Pivot Table options insert slicer is cheesy here and press Okay so from in here we can actually now the selected choose the columns 2 3 and then we can just make it a little bit bigger. And right click in there and then slice of settings. Let's get rid of the display header and press. Okay. And we can bring that. And we put in the corner there. Okay. So we have our panel here with our sparkling friends and our year here. So if we choose a yes in 2012 the numbers change the spotlight's change the same for 2013 and 2014. And I think your boss will be pretty proud of the final product.