Collaborate on Excel Sheet Bill Format for Public Relations with Ease Using airSlate SignNow

Excel sheet bill format for Public Relations

[Music] welcome to Excel magic trick number 1493 Hey here is our amazing topic but we're gonna skip the title and just cut to the chase here it is we have an invoice level fact table or transaction table and we have an invoice line item transaction or fact table and guess what we need to take the shipping cost and invoice discount costs from the invoice level and allocate it down to the transaction line level now the reason we have to allocate it over here is because here's the products that means each line in the invoice has a product and we would like to create this report products as the conditions or criteria and discount and total shipping either created with a formula or with a pivot table now how in the world are we going to do that notice at the invoice or header level we have the total shipping and total invoice discount but somehow we need to allocate those amounts down to the line level so that we can use product condition or criteria either for our formulas or our pivot tables to slice the shipping cost an invoice discount amounts now if we go back to our title here's the problem we need to allocate invoice header amounts to the transaction line-item table and that means we have to transaction tables or to fact tables and each one has a different granularity and our end result is we need to be able to slice a report by product now what does granularity mean it is the size or level of the amounts the grain for this fact table is total invoice amount down here the grain is each individual lawn in the invoice anytime you have two different granularities like this things get complicated but no problem guess what over the next five videos we're going to see how to do it with excel formulas in a pivot table then we'll see how to use Dax and power pivot then we'll have an exciting example of how to use power query to create the final report then we'll see how to do it in power bi desktop and as we did last week we're gonna take all four videos in in video 1497 you'll get to vote and try and win a free book all right so this is the excel solution I'm going to go over to the seat excel solution here we have our invoice header fat table shipping and invoice discount over here we have our invoice line item fact table or transaction table and over here we have our product lookup table now we can think of the process as having three distinct steps we need to allocate the invoice discount amount from the header invoice level over to the line level then we need to take shipping cost and allocate it to the correct line item level once we have those two columns that means discount and shipping will be at the right granularity then we can simply use a pivot table or formulas to slice and dice those amounts by product now the way that we are going to allocate the discount and the shipping are quite different now we're gonna start with how in the world are we going to allocate invoice discount for this one invoice to these three lines well what is a discount in the first place it is a certain percentage of the invoice total but we have the invoice discount amount not the percentage if we have the percentage we could for each line in this line item fact able to multiply quantity times unit price times our percentage discount rate then for each line we'd have the recked granularity for our discount now the missing piece is if we need to calculate the percentage we actually need a column that shows the total sales for each invoice well the total for each invoice if we look at the line items here's three items for the invoice that ends in 4/7 we just need to multiply quantity times unit price for each one of those rows and then add them now we need to do that over here in the head our invoice table so we can make the division and calculate our percentage well the perfect function to take one column times another column given a particular condition is the sum product function so over here right next to our data set by the way this is an Excel table I've already converted it and named it but we simply type our new field name I'm going to call this invoice sales and when I hit enter I immediately incorporate a new column into our Excel table equals some product now some product is built to take array times array times array of the same size multiplied that's the product part and then add that's the sum part all right so I want the entire quantity column and look at this this is an Excel table so I'm going to use my downward pointing black arrow click there's the table name and the field name in square bracket comma second array I'm gonna get the unit price there's my black downward pointing arrow I have unit price comma now I need to pick out only the rows that match that invoice number so I click on invoice number and for that whole column I ask the question how many of you are equal to and this is going to be the invoice number for this row in the table now notice the table formula nomenclature which is the fancy word for what our ranges and references look like when we're using Excel tables the table formula nomenclature for a relative cell reference is square bracket for the field name and then the @ symbol inside of other square brackets that means as we copy down it will always know to get the correct invoice number now some product has a particular problem here that will yield an array of trues and falses and some product cannot handle trues and falses so we need to convert that whole array to ones and zeros and the way we do that is with any math operation the math operation I'm gonna choose this double negative now we have to open parentheses and close parentheses because we have to force that equal sign to calculate before the double negative now why double negative well a bunch of people throughout Excel history for decades have done tests and double negative tends to work the fastest because it's a unitary operator especially inside of some product but that's it I'm gonna close parentheses on some product and I want you to notice what does some product do it'll take column one times column 2 times column 3 which is now zeros and ones and of course the zeros will eliminate quantity times price when it's not the correct invoice now this is an Excel table column so when I hit enter the formula is automatically copied down now I'm gonna go down at least a few cells and hit the f2 key because I want to verify that all of the cells and ranges are correct and sure enough they are now I'm going to add a new column and I'm going to call this percent sales discount and enter equal sign the numerator is the invoice discount divided by invoice sales now I want you to notice something this is an Excel table and that's the table formula nomenclature for please get the item in this row when we get over to Dax and even over to power query the syntax is completely different all right I'm gonna hit enter and that copies all the way down and there it is for invoice one two five four seven seven that's our sales discount now we can come over to our line item fact table and add a new column and I'm gonna call this lying discount this over here was invoice discount this is the line discount enter and now I need to look up the correct invoice sales discount so I'm gonna use vlookup the lookup value I'm looking up the invoice number comma now table array now watch this inside the table I'm gonna highlight invoice number because remember vlookup needs the first column with it item to match and I'm gonna go all the way over to sales discount now I'm going to use the keyboard ctrl shift down arrow and then the keyboard ctrl backspace to jump back to the active cell now I want you to notice something it actually did some heavy lifting for us it put in the complicated table formula nomenclature for please lock that cell reference the table name and then in double square brackets actually field name colon field name in square brackets and then brackets on the outside sometimes in other videos we've had to type that out and it's a hassle nevertheless we have our table comma column index I'm counting on my fingers one two three four fifth column has the item I want to go and get and bring back to the cell so 5 comma we're definitely using exact match lookup I have this sorted but just in case this is sorted a different way I'm using exact match that means I want to find exactly that invoice number I can either put false or 0 close parentheses if I were to hit enter all it did was go and get the correct percentage sales discount for each line in this line transaction table f2 now I need to finish this off times quantity times unit price now when I hit enter there I have what I want now I could round this but really I don't want to round it and the reason why is this is an allocation this is not a dollar amount I'm paying out so I don't really need to worry about the pennies alright so we did our first allocation now we need to worry about shipping now think about shipping shipping is based on weight and we're selling products boomerang products that's the number of quantity for each one of the products in that first invoice but look at this we have weight over here so if we had total weight in this table for each one of these for example the first three invoices I could calculate the weight for each line divided by the total weight and that would be my allocation percent to allocate shipping invoice amount now this is different whereas for our sales discount we had the same sales percentage discount for each line over here for the invoice over here we're gonna have a different percentage because of course there's a different weight for each product and a different quantity now I'm actually gonna do this in two steps I'm first going to calculate the total weight for each line that means right here in that cell I need to look up for Sunbelt the weight of six point five ounces and then multiply it by that 21 so we're gonna start off here's our new column this is line weight and enter equals vlookup we're gonna look up the product name comma within this table right here now notice something about table formula nomenclature when I highlight the whole table it puts in just the table name over here we had not the whole table so of course that was different comma column index number one two three four so I put a four come on definitely doing an exact match so zero close parentheses and enter now that's not what I want up arrow f2 I need to multiply it times the quantity when I hit enter there's our line weight that will be the numerator for our percentage now we need to come over here and at the actual invoice level I want to calculate the total weight this actually won't be total of the invoice weight I'm just keeping with the same terms I used over here indicating the total and enter now let's see we have invoice number we have invoice number here and some weight so I simply need to add using some ifs the sum range line item over there on that table comma criteria range there's the invoice number comma criteria that's gonna be just the invoice so close parenthesis and enter and there it is 743 that's the total weight for that particular invoice now we can allocate right here I'm going to take numerator that's the weight for this line / vlookup to get the total invoice weight and then I'm going to multiply it by vlookup to get the invoice shipping this column is going to be called line shipping equals there's the line weight times vlookup there's the invoice number comma the table array we're gonna have to do the same thing inside our table control shift down out ctrl backspace there's our locked range comma column index number one two three four five the sixth column six comma zero close parenthesis now if I hit enter this gives me the totally wrong number f2 look at that that is not multiplication we need to divide now when I hit enter there's the correct decimal or percentage allocation rate per line and notice for the invoice that only had one line of course one is the correct allocation rate now up at the top times and at the end I'm simply gonna multiply now I need to vlookup same invoice number comma and watch this I'm gonna cheat I'm gonna highlight since we want just invoice shipping just the first two cells right there ctrl shift down arrow ctrl backspace comma that would make the column index number two comma 0 close parentheses and enter and there a half line shipping we have allocated shipping in our line discount so that we have the correct granularity and now we can make our product report now there's two ways we can make our product report formulas and pivot tables pivot tables are much easier I'm gonna make sure I'm clicking a single cell go up to insert pivot table or I can use my keyboard alt + V it got the table right I want to put it on this existing sheet location cell queue 23 click OK scroll over drag product down two rows instantly I get a sorted unique list line discount down to values line shipping down to values right click line discount number formatting something like currency enter right click no more formatting currency and enter and there we have at our product report for line discount in line shipping now if we're gonna do formulas we have always the problem of creating this unique list now I'm just gonna cheat I'm gonna highlight ctrl C come down a few rows right click and up here there's paste special values now I'm going to add some formatting all right now I'm gonna create the same calculation up here we need to add lime discount online shipping based on a single condition in the product column that's the perfect job for the sum ifs function now some range luckily I put line discount and line shipping right next to each other actually I didn't watch this I'm gonna cheat I'm gonna click in the top cell for line shipping I should have done this before when I set the table up ctrl shift down arrow ctrl backspace to jump back to the active cell now I want to move this so it's right next to line discount I'm simply gonna use my move cursor and since this is an Excel table when I click with my cursor to drag it actually knows to move the column if we were in the spreadsheet cells we'd have to hold shift and click alright so now we have line discount and line shipping right next to each other same order as here so I can use the sum ifs function and learn something for the sum range about table formula nomenclature now I'm going to click at the top with my black downward pointing arrow it puts table name and in square brackets the field name now the interesting thing about table formula nomenclature is when you have just a single column is when you copy it down it's locked but when you copy it to the side it automatically moves to the next column that means when you use just a straight column in table formula nomenclature it's actually a mixed range locked when you go down but free to move relatively as you move to the side and that's exactly what we want comma criteria range we need the product column and we need it to be locked now here's what we talked about earlier in this video how do we lock it we can't use the f4 key cuz it doesn't work so if I don't want it to move as a relative range I actually have to come and type open square brackets colon open square brackets and I'll just down arrow to get product close square bracket close square bracket that is crazy that's the difference between locked and mixed locked going down but not to the side now I comma and the criteria a regular cell reference there and I need it locked so I'm going to hit the f4 key one two three times it needs to be locked across the column but free to move when I copy it down close parentheses control enter copy it to the side double click and send it down go to the last cell hit f2 I'm verifying that all of the ranges are correct now I'm going to highlight control one currency and wow that was a lot of fun with taking different numbers at different granularities making sure that whatever condition or criteria we wanted have the numbers at the correct granularity and then of course we can make pivot table or formula reports now I want you to notice something about what we did here we actually did these columns in a few different steps so over here we had three calculations at the invoice or header level and then we made three calculations over here at the line level now the question is could it have been possible to skip over these three columns and these three columns here and actually just in one single formula for each allocation create our columns and our formulas well the answer is yes but they require some huge array formulas now that's what our next video is going to be about but really this solution is probably going to work more seamlessly because a it's not as complicated and B the calculation time for these helper columns and sub calculations are going to be faster than our array formula so in this video we saw how to use excel formulas in a pivot table we have some more exciting topics including the array formula one next but hey if you like that video be sure to click that thumbs up leave a comment and saw because there's always lots more videos to come from XLS fun we'll see you next video